[FREE DOWNLOAD] Position Size Calculator Forex, Stocks And Commodity Trading Using Microsoft Excel

One of the mistakes people commit in trading is to completely disregard the amount of risk to trading account. This is especially the case with fixed lot size in futures and options or otherwise when they take fixed number of shares per trade i.e 100, 200 shares.
Lets take an example, if we buy 100 units of 5$ stock our total value of trade will be 100*5 = 500$. If we buy 100 units of 500$ stock our total value of trade will be 100*500 = 50000$. Profits and losses arising with fixed lots (100 units, in this example) will result in huge swings in the trading account.

Risk Management
Trading is all about preserving capital first and then capital appreciation. Risk management is key to survival in stock trading. One of the golden rules of trading is "cut your losses short but let your profits run". When we say "cut your losses short", it means that we should always be aware of the maximum amount of dollar value that we are willing to risk. This can be for example 1% of total trading capital.

Trading Plan
Coming to the second part. When we take the trades based on technical analysis, we plan them based on support and resistance that we observer on the charts. Basic rule of trading is that we should have an entry, exit and stop-loss (trading plan) before we execute our trade. The difference between our entry and stop is not fixed, it changes with every trade depending on the chart structure.

Position Size
Therefore, we don't  have any control over the above two parameters. Both are to some degree "fixed", based on certain rules, to keep overall losses in check. The only thing that is variable is position size. Depending on our entry and stop this will and should change with each trade. With every trade we will have a different number of shares to buy or sell so that our maximum risk per trade remains constant unlike the case with fixed lots.

You can download the position sizing calculator file, free from here.

Screenshot of  the position sizing calculator below.

How to use the position sizing calculator for online forex, stocks and commodity trading ?
As always, you can modify the "grey cells" rest will get calculated by the excel file. You need to input, your trading account size (will change with every trade), percentage of risk you want to take per trade (will remain fixed depending on your risk profile) and your trade plan (entry, stop-loss and exit level).
You will get the number of shares that you should ideally trade along with reward to risk ratio (how many times the reward is compared to risk) and other details of the trade.

What you should do is experiment with different entries and exits to see how your trade size changes. Tighter stops will allow you to trade more shares hence your "total trade value" will increase. Increasing the risk percentage from default 1% to 2% will also increase the total trade value.

I hope you will find the position sizing calculator useful in your trading, trade and money management.
Good luck with your trading :-)

How to filter stocks data, to get the data only for your watchlist or portfolio using Microsoft Excel

While maintaining the 10 day's cash market data for "Inside day Narrow Range Seven Setup Screener" I came across this problem of huge file size (Microsoft Excel not responding) as well as getting unnecessary results (illiquid stock show narrow range quite often). Therefore, I decided to reduce my screening result to only top 600 stocks ( sorted by rupee volume i.e total trade value)

You can download the stocks data filter excel file, free from here

Screenshot of  stocks data filter excel file below

How to use the file?
1) Copy the stock data in the first sheet "Downloaded Data", in this case I have downloaded stock bhavcopy data from NSE website.

2) Now in the second sheet "Filtered Data" I have used VLOOKUP function of Microsoft Excel to filter data, the stock list (600 stocks) for which I want the data, is added in the cells A700 to A1299. The filtered stock data is available between A2 to A601 cells with their respective price information.

That's all you need to do with this file. Before using the raw data from NSE bhavcopy, you can put that data in this file, it will filter out the excess data. You can change the stocks list as per your requirements, simply change the stock list (in the cells A700 to A1299) from 600 stocks to the stocks you want to track i.e the stocks in your portfolio or watchlist.

Hope you find it useful, please share if you like it.    

Download - Stock screener using Microsoft Excel based on comparative relative strength indicator method

Comparative Relative Strength study compares two stocks to show how the stocks are performing relative to each other. Comparative Relative Strength Study should not be confused with the Relative Strength Index of J. Welles Wilder Jr. It is one of the most simplest and powerful way to analyse the stock markets.

I have created a simple Excel file where I have taken four different “base dates” and compared it with current market price of stocks.  Just for ease of calculation, I have taken previous 3, 6, 9, 12 month’s prices as “base date”, to see the rate of change (momentum) during these timeframes. Therefore we can easily compare stocks across the market, to find an outperforming or underperforming stock.

You can download the relative performance rank calculation file, free from here.

Screenshot of screener below

How to update the data?
You need to follow the steps given under to update the data, to reach the desired results.

1) Update the data in "Grey cells" only, which you will find in all the sheets except the first sheet “Screener”

2) Download the end of day (EOD) data from National Stock Exchange site, for this sheet I have downloaded 3, 6, 9, 12 month’s data. Alternatively one can choose specific day’s data as base date for example a significant high (05-11-2010) or an important low (26-08-2011). It’s up to you to select different time frames. The minimum timeframe to compare,  that I have used is 3 month’s data, some people use 1 week and 1 month’s data as well to gauge short term performance.

3) Copy the data in the same sequence as I have done in different worksheets i.e. “Latest” “3 month” “6 month” “9 month” “12 month”. In case you choose different data set, keep the latest data first and oldest in the last sheet otherwise “Gain %” in first worksheet will be calculated erroneously.

4) That’s all about updating data. Once all the data is copied everything will be updated automatically in the first sheet “Screener”. Now you can apply excel filter by selecting top row. You can check the performance of stocks across different timeframes by using “Sort largest to smallest” feature. On top you will get the outperforming stocks (part of “buy only” watch list) and the bottom of the table will give you the list of underperforming stocks (part of “sell only” watch list). To further arrange the data for your convenience you can uncheck the "#N/A" when you sort the data.


A Few things to take care of:
1) Updating this stock screener is a little tricky, at least for someone who is new to Microsoft Excel. People familiar with Microsoft Excel will find it very easy. It’s just a simple copy paste task in the appropriate sheets.

2) The data that we download from NSE is not reliable at times. Old data is not split adjusted so you will get erroneous result with this screener, especially with the stock that had a split, bonus or rights issue in the past one year. If you can import data from a reliable software or a website, it will work like a charm.

3) Always verify the results of the screener with a chart. That’s what I do to verify the results of any kind of screener.

4) You need to copy the formulas in the “Screener” worksheet if new data is added in second work sheet “Latest”. This can be done easily by selecting the last row in the first worksheet “Screener” and dragging it down.


Screening and trading
This excel file will just help you to find an outperforming or underperforming stock. It tells you, where you should allocate your money. It doesn’t ensures that you will be profitable trading a particular stock. Profitability will depend on your trading system and trading discipline.

This screener essentially helps you in finding a momentum stock. Momentum trading is not easy for everyone.  It is based on “buy high, sell higher” (greater fool theory) way of trading in other words breakout trading.  One can always wait for some kind of a retracement to enter a momentum trade. But the stock chart will always give an impression that the stock has moved “significantly” from a base. Most of the stocks that you will find using this screener will be “fundamentally over valued”.

If you face any problems using this screener you can send me a mail or put your query below in the comments section (I prefer comments as it helps everyone). Hope that you find the screener useful in your trading. Good luck :)

How to calculate Compound Annual Growth Rate - CAGR in Microsoft Excel?

What Does Compound Annual Growth Rate - CAGR Mean?

The CAGR is a smoothed rate of return because it calculates the growth of an investment as if it had grown at a constant rate on an annually compounded basis.

Investopedia explains Compound Annual Growth Rate

CAGR isn't the actual return in reality. It's an imaginary number that describes the rate at which an investment would have grown if it grew at a steady rate. You can think of CAGR as a way to smooth out the returns.

CAGR is one of those terms best defined by example. Suppose you invested $10,000 in a portfolio on Jan 1, 2005. Let's say by Jan 1, 2006, your portfolio had grown to $13,000, then $14,000 by 2007, and finally ended up at $19,500 by 2008.
Your CAGR would be the ratio of your ending value to beginning value ($19,500 / $10,000 = 1.95) raised to the power of 1/3 (since 1/number of years = 1/3), then subtracting 1 from the resulting number:

1.95 raised to 1/3 power = 1.2493. (This could be written as 1.95^0.3333).
1.2493 - 1 = 0.2493
Another way of writing 0.2493 is 24.93%.

Thus, your CAGR for your three-year investment is equal to 24.93%, representing the smoothed annualized gain you earned over your investment time horizon.

Mathematically the CAGR formula is written as:

How to calculate Compound Annual Growth Rate - CAGR in Microsoft Excel?

In Microsoft Excel you can use the function of "Power" to calculate CAGR. I am uploading a Compound Annual Growth Rate - CAGR Calculator. You can download it free from here. You can edit the information  presented in the "grey cells" to calculate the rate of return on your investments.

Application of CAGR in finance.
1) To calculate the average returns of investment funds and money managers.
2) Comparing the historical returns of various financial instruments like stocks with precious metals or fixed income products.
3) Analyzing and forecasting the value of sales and costs to company based on the CAGR of past data.

Download - Stock Screener based on Narrow Range Seven (NR7) Strategy

Hi,
Continuing with the Narrow Range Seven (NR7) Setup, I have made this simple stock futures screener.


You can download it from here


How to update the data?
You need to follow the steps given under to update the data, to reach the desired results.


1) Download the end of day (EOD) data from National Stock Exchange site (http://www.nse-india.com/)
2)Copy the Stock Symbol, High,Low, and Close data from the downloaded file to the stock screener file, under the respective columns at the end of the file (select the appropriate cell the press Ctrl and Down Arrow to reach the end of file)
3)Then update the Date for current data
4)Once you have copied all the data with date at the end of screener file.You need to use the Filter at the top to sort the data
5)First sort the "Date" column, by using the drop down arrow and select "Sort Newest to Oldest"
6)Then sort the "Symbol" column by selecting "Sort A to Z" option
7)That will complete the process, the screener will show the stock futures which made the NR7 price bar today


To further arrange the data for your convenience you can uncheck  previous dates now and select only today's date
Similarly you can uncheck the "Blanks" option, withing NR7 column filter to get specific screened stocks only


Updating this stock screener is a little tricky, at least for someone who is new to Microsoft Excel.People familiar with Microsoft Excel will find it very easy.Once you get used to updating the data, it will not take more than 2 minutes to find stocks that are making NR7 price bar today.


A Few things to take care of :
1)Make sure that you always have at least 10 days data in this scanner because the scanner needs that much data to work properly.As you get the new data you can remove the old data so that the file size remains small.
2)Be very careful that date and stocks are arranged as per above steps
3)To check whether you have done all the things correctly.Always refer the stocks range for today it should be the least as compared to past seven days (including today).This should be done with the help of a chart.
4)As always ensure that the high, low data is not erroneous.Ignore the first minute data if you are updating the data manually.
5)The screener currently contains stock futures data as I follow futures only.You can make you own stock price database or track your watchlist only for NR7 Setup


If you face any problems using this screener you can send me a mail or put your query below in the comments section.Hope that you find it useful in your trading.Good luck
Cheers


Related Posts
Narrow Range Calculator
Narrow Range Seven (NR7) Setup Calculator based on Welles Wilder's True Range

Download - Narrow Range Seven (NR7) Setup Calculator based on Welles Wilder's True Range

Hi 
A few days ago I had uploaded Narrow Range Seven(NR7) Calculator.While the original strategy of NR7 is based on daily range calculation(calculated by deducting low from high price) and comparing it with the past seven days range(including today).I feel that using true range instead of daily range will lead to better screening of this setup.


I am uploading Narrow Range Seven (NR7) Setup Calculator based on Welles Wilder's True Range.You can download it from here


Apart from the above calculations you will also find an Inside Day and Outside Day Calculator attached.If the day has formed an Inside Day as well as NR7 or NR4 bar, it is considered to be a high probablity setup


As always the "Grey cells" in the Excel File can be changed as you get new data everyday.If you want a modifiable version of this calculator you can download the link under "related file" section at the end of this post.


I will upload a stock screener based on this setup shortly.However that will be a little difficult for people who are new to Excel.
Hope you find it useful in your trading.If there are any problems related to this calculator you can mention that in the comments section or mail me.
Cheers


Related File
Download - Narrow Range Seven (NR7) Setup Calculator based on Welles Wilder's True Range Modifiable (Research Version)
Related Post
Narrow Range Calculator
Download - Stock Screener based on Narrow Range Seven (NR7) Strategy

Download - Average True Range (ATR) Indicator Calculator In Microsoft Excel

Hi,
The concept of Average True Range was developed by J. Welles Wilder Jr and introduced in his book, New Concepts in Technical Trading Systems (1978), the Average True Range (ATR) indicator measures the volatility of a stock or index.

I am uploading the Average True Range (ATR) indicator calculator.You can download it from here.
It's fairly simple to use, all you need to do is update the data in "Grey Cells" Just be careful when you copy the high, low,close data for the day,quite often due to "freak quotes" you get incorrect opening values (happens mostly with illiquid stocks).So I would say ignore a few initial ticks, if that is cumbersome ignore the first minute data.

True Range is defined as the greatest of the following:
1)Today's High less Today's Low (D1)
2)Today's High less Yesterday's Close (D2)
3)Yesterday's Close less Today's Low (D3)

The calculator will be of help if you have a trading system based on a range.Using Average True Range (ATR) indicator or True Range instead of just high and low for the day gives a better feel of the markets.This is specially useful to calculate volatility in a stock or a commodity making limit moves

A few days back I had posted Narrow Range Calculator,this calculator can be combined with NR7 calculator to give a better overall system.I will post a new NR7 calculator based on true range shortly
Cheers

Related File
Download - Average True Range (ATR) Indicator  Calculator In Microsoft Excel Modifiable (Research Version)


Other Posts of Interest
Free Online Forex Futures Trading Strategy
Free Gold And Crude Oil Futures Tend Update

Download - Narrow Range 7 (NR7) Calculator

One of the generally accepted views about the market is that markets move from period of range contraction to period of range expansion and vice versa.Or period of high volatility are preceded by period of low volatility.Many indicators and setups like Bollinger bands ,Average true range (ATR) etc are based on this premise.Narrow Range 7 or NR7 as it is commonly known is one such setup.

I am uploading Narrow Range 7 (NR7) setup calculator.You can download it from here.

The calculator will help you in finding right stocks at right time which may experience rise in volatility soon (mostly it's the next day).In other words you will move a little closer to "timing the markets",which is often said to be an impossible thing

How to use the calculator?

It's very simple all you need to do is copy the data in grey cells.You need to copy the date ,high and low prices on end of the day (EOD) basis.Just be careful when you copy the high and low data for the day,quite often due to "freak quotes" you get inappropriate opening values (happens mostly with illiquid stocks).So I would say ignore a few initial ticks if that is cumbersome ignore the first minute data.

That's all about the calculator.Personally I strongly believe that markets indeed move from low volatility to high volatility period.I made this calculator a long time ago but never worked on this idea in detail.I will study this in detail and will come up with other forms of calculators based on this.Shortly I will write a post on who can benefit from this and some related trading strategies

Good luck with your trading.

Related Files

Download - Narrow Range 7 (NR7) Calculator Modifiable (Research Version)

Related Post

Narrow Range Seven (NR7) Setup Calculator based on Welles Wilder's True Range

Download - Stock Screener based on Narrow Range Seven (NR7) Strategy

All the links updated under the label "Download"

Hi,
I have updated all the links under the label "Download", click here to access all the files and various calculators.

All the files in future will be presented in two versions.First "the calculator version" which should be used by people who have limited knowledge about excel with default indicator values.Second "Modifiable (Research Version)" ,formulas can be modified in this version, it will be useful for people who are interested in market research.If your face any problem with downloads just let me know in the comments section below.

Good luck with your trades.

[FREE DOWNLOAD] Elliott Wave Calculator

The Elliott Wave Principle helps in describing how financial markets behave. As per the principle, markets create specific wave patterns in price. These wave patterns are often found to be in particular Fibonacci ratios.

I am uploading Elliott Wave Calculator Excel Spreadsheet. You can download it from here.

The calculator will help to broadly classify various waves associated with the Elliott Wave principle as per the Fibonacci ratios
Some of the commonly accepted ratios and rules have been taken into account while making the calculator.

Following assumptions related to price aspect are made while making the calculator.
1) You need to first establish Wave 1, based on that rest of the waves have been calculated .Once Wave 1 is identified, insert the values in grey cells under “FROM” and “TO” Cells
2) Wave 2 is 0.618 times Wave 1
3) Wave 3 is 1.618 times Wave 1
4) Wave 4 is 0.382 times Wave 3
5) Wave 5 is equal to Wave 1
6) Wave A is 0.382 times Wave 5
7) Wave B is 0.618 times Wave A
8) Wave C is equal to Wave A

Following assumptions related to time aspect are made while making the calculator.
1) Once Wave 1 is identified calculate the number of price bars between the start and end of the wave. Insert this in calculator (grey cell under the column “Time Bars”)
2) Wave 2 is 0.618 times Wave 1
3) Wave 3 is equal to the sum of time between Wave 1 and Wave 2
4) Wave 4 is 1.382 times Wave 2
5) Wave 5 is 1.382 times Wave 4
6) ABC waves is half in length to 12345 waves
7) Wave A and Wave C are of same time duration.
8) Wave B is 0.618 times Wave A

The above points are commonly accepted ratios in Elliott wave principle and thus are not absolute values
I feel if Elliott Wave price projection is used with oscillator divergence and trend channels, it can give quite reliable price projections. With the help of the calculator we can quickly form an opinion about the current trend in any time frame from 1 min to 1 month or more, in any stock or index
The calculator is still under development stage, price calculation have been fully developed, some aspects regarding time  need to be modified if required

Lastly I would like to thank Ilango of  "Just Nifty"  for his valuable inputs in conceptualizing this calculator.For detailed information about Elliott wave ,please visit his blog.
Please post your feedback , suggestions and strategies about how this can be use in trading.
Good luck
Cheers

Apart from the above you may find these calculators useful while working with Elliott Wave principle:
Fibonacci Retracement calculator
Fibonacci Extension Calculator
Download - Elliott Wave Calculator Modifiable (Research Version)

Other Posts of Interest 
Elliott wave pattern - Impulse (IM), Internal structure, Rules and Guidelines
Elliott wave pattern - Diagonal [Leading (LD) and Ending (ED)], Internal structure, Rules and Guidelines
Elliott wave pattern - Zigzag (ZZ), Double Zigzag (DZ), Triple Zigzag (TZ), Internal structure, Rules and Guidelines
Elliott wave pattern - Flat (FL), Double Sideways (D3), Triple Sideways (T3), Internal structure, Rules and Guidelines
Elliott wave pattern - Triangle [Contracting (CT) and Expanding (ET)], Internal structure, Rules and Guidelines
Free Online Forex Futures Trading Strategy
Free Gold And Crude Oil Futures Tend Update

[FREE DOWNLOAD] Pivot Point Calculator for Intraday Trading With Support and Resistance Levels

Hi all,
Pivot point, support and resistance levels are closely followed by day traders/swing traders to initiate and exit trades
I am uploading a Pivot point calculator.

You can download pivot point, support and resistance calculator for intraday trading from here

You can change the values in grey cells and it will calculate the Pivot Point, Support levels(S1,S2,S3) and Resistance levels (R1,R2,R3)

I will discuss the strategies and problems associated with pivot points some time in future.
Cheers

Related File
Download : Pivot Point Calculator Modifiable (Research Version)

[FREE DOWNLOAD] RSI Indicator - Relative Strength Index Calculator

Hi all,
Relative Strength Index (RSI) is one of the most commonly used technical indicator in trading.
I am uploading Relative Strength Index (RSI) Calculator Microsoft Excel Spreadsheet below.

You can download it from here

Although all the technical charting softwares provide various indicators, but taking a closer look at how an indicator is constructed gives us important insights into the merits and demerits of that particular technical indicator.
Good luck
Cheers.

Related File
Download :  Relative Strength Index (RSI) Calculator Modifiable (Research Version)

Download : Fibonacci Extension Calculator

I have uploaded a Fibonacci Extension Calculator 

You can download it from here

You can change the values in Orange Cells (to calculate the extension of down move) and Green Cells (to calculate the extension of up move)

Hope you find it useful,although most charting softwares come with fibonacci retracement /projection tool ,therefore are better than this excel calculator.

Related File - Download : Fibonacci Extension Calculator Modifiable (Research Version)

[FREE DOWNLOAD] Fibonacci Retracement Calculator

Hi all,
I have uploaded a Fibonacci retracement calculator.


You can download it from here


You can change the values in Orange Cells (to calculate the retracement of down move) and Green Cells (to calculate the retracement of up move)


I will up load the Fibonacci Projection Calculator tomorrow.
Hope you find it useful,although most charting softwares come with fibonacci retracement /projection tool therefore are better than this excel calculator.
Cheers


Related File
Download : Fibonacci Retracement Calculator Modifiable (Research Version)

Download : Compounding Calculator

Hi
As we are approaching the financial year end (31 March), quite a few people will be contacting their financial advisors for investments (or financial advisors contacting you....to lure you into some attractive tax saving/pension/investing idea for future).
One of the marketing gimmick they will use is telling you how much a particular policy will fetch in so many years provided you pay a regular monthly/year sum.
I am uploading a compounding calculator,you can download it by clicking here

You can change the values of "amount" and "rate" in the table (Orange cells on right hand side)
Although the returns looks very attractive,how much practical they are I don't know.So be cautious,read the terms carefully and make a wise financial decision for yourself and your family
Good luck.



Related File
Download : Compounding Calculator Modifiable (Research Version)

Free Download - Moving Averages Calculator Using Microsoft Excel

hi,

Following are the links to download the calculator for moving averages

Simple moving average - click here

Exponential moving average - click here

Article to follow by evening.

Cheers
***********************************

Related Files
Download Simple Moving Average Modifiable (Research Version)
Download Exponential Moving Average Modifiable (Research Version)

Related Post
Indicators Made Easy –Moving Averages Part 1

The Holy Grail of Trading: a roadmap to trading and investing

Hi all,
Well this is my first post regarding trading and this is my first blog as well.I have been trading / investing in Indian Stock Market for about three years. So speaking purely in terms of years I am quite new, but I have been trading, following the market daily and have put in tremendous amount of time on daily basis close to 10 hrs and sometimes even more.


Over the years my way of trading has changed immensely, I started with fundamental analysis then moved to pure technical analysis, but now I am using both fundamental and technical analysis in conjunction. However my short term analysis and trading decisions are based on technical approach.


Enough of information about me (that’s not important anyways) now let’s come  to the main topic “the holy grail”. No I am not going to tell about any magic indicator or any specific parameter to use or a time frame for consistent trade (Sorry to disappoint a few people who were expecting that but trust me what I am highlight here is better than indicators).Instead of that I am going to present a checklist of various experiences that you will have in your quest for becoming a consistent and a successful trader


The credit for highlighting these points goes to a person by the name “jaydaraniya” in icharts forum. I have just made the points in the form of a checklist so that readers can find it easy to assess  where they currently stand in their  development as a professional trader. I have added a few pointers here and there but tried to retain the content as it is.

You can download the Excel file from this link. 

How to use the checklist?

Six types of traders or we can say six board stages of development as a trader are mentioned. You need to find first where you actually are, once that is known you will know what challenges you will face in coming time, knowing that you are better prepared to face the obstacles. It will be like what a map is to a sailor. Once things become clear, you will enjoy your voyage around the trading world

I would like to say thanks to “jaydaraniya”, his article completely changed the way I approached trading and I am quite certain that it will have an immense impact on others who will follow the checklist and work towards the final goal of being a consistent trader / investor.

Good Luck.
************************************


Related Post
Holy grail in text format