How to Beat the market using Gann square of 9 Calculator ?

Square of 9 from WD Gann is an unconventional method in Technical analysis which uses angular and geometric relationships among numbers to predict the stock moves. This is a very popular method among intraday traders owing to its accuracy in equities as well as commodities. The square of 9 is actually a spiral or wheel of numbers. The center of the square contains the initial value which is increased clockwise in spiral form. Each number in the square represents an angle relative to the center. In this post, we would understand in detail how to use Gann square of 9 Calculator for intraday trading. There is an excel sheet attached with the post which would help you to calculate support and resistance levels based on Gann theory.

Fine some of our very popular Excel based trading systems in the below link:

Excel Based Trading Systems

History of Gann Square

At one point in his trading career, W.D.Gann took three year’s leave to go to the U.K to study the past history of selling prices of Wheat. During his meticulous studies, he developed some very powerful and innovative techniques, none more so than his use of the tool we call the Square of 9. Gann closely guarded his secrets whilst he was alive, but almost certainly the Square of 9 calculations was pivotal in his trading methods. The Square of 9 was not created by Gann.  He actually found it inscribed in a temple in India and on the walls of the Great pyramid in Egypt, but he was certainly the first trader to recognize its importance and relevance in market trading.

Gann held a trading record which has been unsurpassed by anyone since.  In front of a customs inspector he turned $130 into $12,000 in less than 1 month. Again, in the presence of an independent witness, he made 286 trades, 264 winners and 22 were losses.

Intraday Trading using Gann square of 9 Calculator

Gann square of 9 calculator is used to generate support and resistance levels for intraday trading. Breakouts of thee levels would be used to enter trade. The support/resistance levels are generated using the values in Gann square. In order to generate these levels, we need to input the latest trade price or weighed average price in Gann calculator (attached with the post) . LTP should be taken after at least half an hour of trade for better results , as there is lot of volatility in market during the first 30 minutes. Once you input this price the Gann levels would be automatically generated. See below:

Also, the buy/sell price and respective targets would be generated in the Excel sheet. In this particular example, we need to enter buy if the price crosses above 8418.06 and enter short if price crosses below 8395.14.

Lastly there are support resistance levels based on which targets are calculated above:

How Gann values are calculated?

Every value in the Gann square is related to the center value. Below are the steps used to calculate these values:

1. Calculate the square root of LTP. Let’s suppose LTP=8400, then SQRT(8400)=91.65
2. Take two integers below and above the square root calculated. In this case they would be 90,91,92 and 93.
3. Square the lowest values out of the 4 calculated above. SQR(90)=8100. Put this value at the center of Gann square.
4. Now, increase 90 by 45 degrees. In numeric terms 45 degrees is equal to 0.125 (360 degree=1). So our required value is 90.125.
5. Calculate square of 90.125, and place it in adjacent block left of center block. SQR(90.125)=8122.52.
6. Increase the value further by 45 degrees. 90.125+0.125=90.25. Find out square of this value and place it at the top of where you placed 8122.52. SQR(90.25)=8145.06
7. Continue this process by increasing the value by 0.125 for each iteration.
8. Once you complete one level of square, start with the next level with values 91.125,91.25….

Resistance values are the next higher values in the Gann square above the Buy Price. While support values are the next lower values in the Gann square below Sell price. Targets are calculated based on support and resistance.

Download link for Gann square of 9 calculator

Please see the below link to download Gann square of 9 calculator Excel Sheet. You simply need to input the current market price and the excel sheet would do rest of calculation itself.  Please let us know if you have any queries. Also, send us your feedback on how to improve this sheet.

Gann Square of 9

Liked the article? Please share it with your friends!

Risk Reward Ratio,Profitablity and Success Rate: Excel Sheet

I hope you have already read our article on Risk Reward Ratio. If not, please find it here.

Many of our readers have requested for the excel sheet to calculate the profitability based on Risk Reward Ratio. You can find the excel sheet as an attachment with this post. And yes, we have not kept any formulas hidden in the excel sheet

                             

The Excel sheet basically contain two worksheets outlined below:-

1) Risk Reward Ratio and Profitability:- This worksheet will calculate the profitability based on your risk-reward ratio. In this worksheet, you just have to enter the Risk and Reward values in column A and B respectively. We have kept Success Rate as 40 for all calculations, however this is editable and can be changed as per your strategy. Risk is the maximum amount you can lose in a trade(This might be equal to your stop loss value). While Reward is the Target Profit expected. Different strategies have different risk-reward ratio. Profit is calculated considering 100 trades, with Risk and Reward as input in designated columns.

We have already pre-filled excel sheet with some standard values, which can be edited as per requirement. Also, an Area graph is created based on the values in the table. Please see the below screen-shot.

2) Risk Reward Ratio vs Success Rate: This worksheet will calculate required Success Rate for the given Risk Reward Ratio, and vice versa. In this worksheet too, you  have to enter the Risk and Reward values in column A and B respectively. Success Rate is calculated for a break-even trade (no profit no loss).

We have already pre-filled excel sheet with some standard values, which can be edited as per requirement. Also, a line graph is created based on the values in the table. Please see the below screen-shot.

Please note that we haven’t considered brokerage or any other charges while formulating this excel sheet.

Download the Excel sheet here. Post your queries in the comment section below.

Moving Average Trading System in Excel Sheet

Probably Moving Average based trading systems are most popular among the traders across the globe. These trading systems work very well in Trending markets.

                         

Most of the beginners might not have subscription to any broker with continuous data feed. So here  we have provided semi-automated  Moving Average trading system in Excel. The only manual task would be to copy closing price for the security selected in this excel.

Strategy Overview

Paramter	Value
Preferred Timeframe	Daily
Indicators Used	3 Days EMA, 5 Days EMA
Buy Condition	3 Days EMA is above 5 Days EMA
Sell Condition	3 Days EMA is below 5 Days EMA
Stop Loss	No fixed target, Stop and reverse when excel gives opposite signal
Targets	No fixed target, Stop and reverse when excel gives opposite signal

Excel Sheet

Parameter		Description
Worksheet Name		MA Trading System
Inputs	Date (Column A)	Calender Date
	Close Price (Column B)	Closing price  on specified Calender Date.
	Faster EMA Period (Cell C2)	Faster Exponential Moving Average. Default is 3.
	Slower EMA Period (Cell D2)	Slower Exponential Moving Average. Default is 5.
Outputs	Faster EMA Values	Faster EMA values based on period defined at input.
	Slower EMA Values	Faster EMA values based on period defined at input.
	Crossover Price	The exact price at which EMA crossover will take place.
	Signal	Buy/Sell signal based on conditions mentioned at strategy overview.

Screenshot

Download Link

Moving Average Trading System

Dynamic RSI calculation in Excel Sheet with charts

RSI or Relative Strength Index is one of the most popular momentum indicator in Technical Analysis. It’s value fluctuates between 0 to 100 and indicates the strength and velocity of price move. RSI indicator is mostly calculated on 14 period timeframe and any value above 70 indicates over-brought level and value below 30  indicates over-sold level. However, there are many variations of this indicator and the manner in which the RSI levels are interpreted varies with your trading style. This posts intends to explain RSI calculation in Excel sheet. Also the calculation steps would be described.

                        

The relative strength index was developed by J. Welles Wilder and published in a 1978 book, New Concepts in Technical Trading Systems, and in Commodities magazine (now Futures magazine) in the June 1978 issue. It has become one of the most popular oscillator indices.

How RSI is calculated?

RSI calculation is based on average gains and losses for the specific time period.  For instance, if you want to calculate the 14-day RSI and the stock went up on nine days and fell on five days. The absolute gains (stock’s closing price on a given day — closing price on the previous day) on each of these nine days are added up and divided by 14 to get the average gains. Similarly, the absolute losses on each of the five days are added up and divided by 14 to get the average losses. The ratio between these values (average gains / average losses) is known as relative strength (RS). To make sure that the RSI always moves between 0 and 100, the indicator is normalised later by using the formula given below:

RSI = 100 – 100 / (1+RS*) * RS = Average gains / Average losses

Excel Sheet Overview

The Excel sheet would dynamically calculate the RSI based on the periods entered. Also you have to manually enter the Open, High,Low,Close data for the selected stock or index. The calculation formula can be found in Excel sheet itself. The price chart and RSI chart is embedded into the excel sheet which will update accordingly.

Parameter		Description
Worksheet Name		Dynamic RSI
Inputs	Column A,B,C,D,E	Date,Open,High,Low and Close for the selected stock/index
	Column L3	The periods for which RSI should be calculated. Default is 5.
Outputs	Change (Column F)	Change in stock price from previous day. It may be positive or negative.
	Advance/Decline (Column G,H)	If Change (Column F) is positive, then Advance is equal to change else it is zero. If Change (Column F) is negative, then Decline is equal to -change else it is zero.
	Average Gain/Loss (Column I,J)	Average of Advance/Decline for the RSI period specified.
	Relative Strength (Column K)	Ratio of Average Gain and Average Loss
	RSI (Column L)	The final value of RSI for the given time period.

Screenshots:

Download Link

Please download the Excel sheet from the below link. Let us know if you have any questions or comments.

Dynmaic RSI Excel Sheet

Liked the article? Please share it with your friends!

Golden Cross and Death Cross Trading System: Excel Sheet

Golden Cross and Death Cross are very popular strategies for any Trend following Trading system. You might have heard about these terms if you have subscribed for any of the Technical analysis course or tutorial. Even if you haven’t, we would help you understand these terms and its application. In this post, we would also provide you an Excel sheet where you can backtest Golden Cross and Death Cross on any of the Stock or Index. Please read through below.

Read our other Excel based Trading strategies here.

                             

What is Golden Cross and Death Cross?

Golden Cross and Death Cross are important Trend reversal points on a Technical chart of a Stock or Index. Its is based on Moving Average Crossover. It is a general rule that Stock gives a Buy signal when faster moving average crosses above the slower moving average, while its gives a Sell signal when faster moving average crosses below the slower moving average. Golden Cross is indicated when 50 Days simple moving average crosses above the 200 day moving average. And Death Cross is indicated when 50 Days moving average crosses below the 200 day moving average. Either crossover is considered more significant when accompanied by high trading volume.As simple as that! It works perfectly on majority of indices and stocks in a long term.

Strategy Overview

Paramter	Value
Preferred Timeframe	Daily
Indicators Used	50 Days SMA, 200 Days SMA
Buy Condition	50 Days SMA is above 200 Days SMA
Short Condition	50 Days SMA is below 200 Days SMA
Stop Loss	No fixed stop loss, Stop and reverse when excel gives opposite signal
Targets	No fixed target, Stop and reverse when excel gives opposite signal

Excel Sheet

Parameter		Description
Worksheet Name		Golden Cross and Death Cross
Inputs	Stock Name (B4)	Name of the Stock or Index. Ex: NSE:NIFTY, NASDAQ:AAPL
	Quantity (B5)	Number of shares to Buy when Golden Cross or Death Cross happens
	Initial Capital (B6)	Your initial Trading capital
	Number of Days (B7)	Number of days to backtest this system
Outputs	Final Capital (E4)	Final capital after backtest period.
	Profit Points (E5)	Total points earned
	Total Profit/Loss (E6)	Total Profit/Loss in Exact terms
	Total Profit/Loss % (E7)	Total Profit/Loss in Percentage terms

Screenshot

How to use this Excel Sheet

Step 1: Open the sheet from the link provided at the end of this post.

Step 2: Provide inputs in Cells B4 to B7. Make sure that you are connected to the internet.

Step 3: Wait for around 30 seconds. The sheet will update automatically depending on the inputs you provided.

Step 4: Check the outputs in Cell E4 to E7.

Step 5: Take Buy/Short position based on the signal at Position column.

Download Link

Below is the link to this sheet from Google Docs. You can edit it online. There is no need to download it in your local machine.

Golden Cross and Death Cross: Google Sheets

Fibonacci Levels Calculator: Excel Sheet

Fibonacci Levels is one of the most popular tools in the worlds of Technical Analysis. These levels are predictive in nature and helps to identify pullbacks or breakouts. People have been using it successfully since years especially in Forex market. Using Fibonacci retracements in your trading will not guarantee you overnight success. But if used in conjunction with other technical analysis indicators like RSI, MACD, moving averages, candlestick patterns, etc it can be very valuable. In this post, we have shared Fibonacci Levels Calculator Excel sheet. Please read through to understand how to use it.

Check out the other popular Excel sheets posted in this blog here.

What is Fibonacci Sequence?

The Fibonacci Sequence is a series of numbers where the each number in the sequence is the sum of previous two numbers. The first ten numbers in the Fibonacci Sequence are: 0, 1, 1, 2, 3, 5, 8, 13, 21, 34. Fibonacci numbers also appear in many aspects of nature such as the arrangement of leaves on a stem and the branching of trees. Off lately, this sequence has gathered huge popularity in Financial markets too.

From the Fibonacci Sequence comes a series of ratios, and  these ratios are of special significance to traders as they predict possible reversal or breakout. The most important Fibonacci ratio is 61.8% – it is sometimes referred to as the “golden ratio” or “golden mean” and is accepted as the most “reliable” retracement ratio. The Golden Ratio is arrived at by dividing any number in the sequence by the number that immediately follows it. No matter which number you choose, the answer will always be very close to the mean average of 0.618, or 61.8%. For example:

• 8 divided by 13 = 0.615 = 61.5%
• 13 divided by 21 = 0.619 = 61.9%
• 21 divided by 34 = 0.617 = 61.7%

The other two Fibonacci Ratios that forex traders use are 38.2% and 23.6%. These two ratios seem to have a lower level of success but are still included for analysis purposes.

The 38.2% ratio is derived by dividing any number in the sequence by the number found two places to the right. For example:

• 8 divided by 21 = 0.380 = 38.0%
• 144 divided by 377 = 0.381 = 38.1%
• 6765 divided by 17,716 = 0.381 = 38.1%

In a similar fashion, the 23.6% ratio consists of any number in the sequence divided by the number that is three places to the right:

• 5 divided by 21 = 0.238 = 23.8%
• 34 divided by 144 = 0.236 = 23.6%
• 6765 divided by 28,667 = 0.235 = 23.5%

In addition to these three ratios, most trading systems also show retracement levels at 50% and 100%.

How to use Fibonacci Levels?

Fibonacci Levels are broadly classified into two: Retracement levels and Extension levels. Retracement levels represent the percentage of Pullback or retrace before reversing into the original trend. While the Extension levels represent the price target. In general terms, the retracement levels give you an idea of where to put Stop Loss, while Extension levels help you identify the profit targets.

Here is the graphical representation of retracement:

After a stock makes a move to the upside (A), it can then retrace a part of that move (B), before moving on again in the desired direction (C). These retracements or pullbacks are what you as a swing trader want to watch for when initiating long or short positions.

Excel Sheet for Fibonacci Levels Calculator

Parameter		Description
Worksheet Name		Fibonacci Levels Calculator
Inputs	Cell B5,B6,B20,B21	High and Low values of the selected Stock for Uptrend and Downtrend respectively. It can be weekly High and Low for Swing Trading, or Hourly High and Low for Intraday Trading.
Outputs	Cells E5:H16	Retracement and Extension levels for Uptrend
	Cells E20:H31	Retracement and Extension levels for Downtrend

Screenshot

Download Link

Please download the Excel sheet from the below link. Let us know in the comments section if you have any queries.

Fibonacci Levels Excel

Liked the article? Please share it with your friends!

Nifty Option Greeks Calculator: Live Excel Sheet

People who practice Options trading know very well how important ‘Option Greeks’ are. As per Investopedia, Trading options without an understanding of the Greeks – the essential risk measures and profit/loss guideposts in options strategies – is synonymous to flying a plane without the ability to read instruments. Option Greeks, denoted by certain Greek alphabets, are the parameters which determine how Option price varies with the change in external factors like Time, Volatility and underlying Stock Price. These Greeks are calculated based on the Black and Scholes options pricing model, which was first published by Fisher Black and Myron Scholes (hence the name Black & Scholes) in 1973. In this post, we’ll go through a Nifty Option Greeks Calculator which updates real-time and calculate Greek values for all the strike prices of Nifty Put and Call options.

                                

Options Greeks definition

Image Source: http://optionspedia.blogspot.in/2012/10/option-greeks.html

1. Delta – Measures the exposure of option price to movement of underlying stock price
2. Gamma – Measures the exposure of the option delta to the movement of the underlying stock price
3. Theta – Measures the exposure of the option price to the passage of time
4. Vega – Measures the exposure of the option price to changes in volatility of the underlying
5. Rho– Measures the exposure of the option price to changes in interest rate

Nifty Option Greeks Calculator: Excel Sheet overview

Parameter		Description
Worksheet Name		Greeks
Inputs	Expiry Date (Cell E3)	Expiration date of current Nifty Option series. Generally it is the last Thursday of the month.
	Risk free interest rate (Cell E6)	This is risk free rate prevailing in the economy. Use the RBI 91 day Treasury bill rate for this purpose. You can get the rate from the RBI website, RBI has made it available on their landing page here.
	Dividend Yield (Cell E8)	This is the dividend per share expected in the stock, provided the stock goes ex dividend within the expiry period.
Outputs	Cells B11:R31	Option Greeks values for each strike price.

Nifty Option Greeks Calculator: Screenshot

How to use this Excel Sheet

Below are the simple steps to use Nifty Option Greeks Calculator excel sheet. There is very little manual intervention required for this.

Step 1: Download the Excel file from the end of this post.

Step 2: Open this Excel file and make sure you are connected to internet. Please accept if it asks to enable Macros and Data connections.

Step 3: Input the required fields, Expiry Date, Risk free interest rate and Dividend yield.

Step 4: The Greek values would automatically get updated. The sheet refreshes every five minutes.

Download link for Nifty Option Greeks Calculator

Please see the below link to download Nifty Option Greeks Calculator excel sheet. Mostly everything is self explanatory, but please let us know if you have any queries. Also, send us your feedback on how to improve this sheet.

Nifty Option Greeks: Download Link

Liked the article? Please share it with your friends!

Options Max Pain Calculator: Excel Sheet

Max Pain theory in Options Trading is probably one of the newest bust most popular strategies for Options trading. This theory is backed upon the fact that about 95% of option buyers lose money. We have personally traded based on max pain and the success ratio is pretty cool. This article intends to explain max pain theory of options and how to profit from it. Also, we would go through Options max pain calculator excel sheet which can be downloaded from the end of this post.

Max Pain Theory: Definition and Usage

The idea behind Max Pain theory is the fact that Option writers tend to manipulate the expiry price of stock, index or commodity so that they benefit the most out of their positions. Although there is no authentic proof that how they manipulate the prices, yet this theory is widely accepted. As 95% of the options expire worthless and options is a zero sum game, this theory seems very realistic and logical. By definition, Max Pain is the strike price where if the underlying settles on expiry will cause maximum loss or maximum pain to the option buyers. Guess who would benefit from their loss? Yes, its Option sellers. Most of these Option Sellers are large investors / institutions or hedge funds that will hedge their positions and have the ability to manipulate the stock price to suit their open Options position.

There are enormous ways in which you can benefit from Option pain. Basically, max pain tells you the approximate level at which the contract is going to expire. If you know know this level, you can simply write Call options slightly above this level and pocket the premiums on expiry. Similarly, you can write Put options below this level. If you are a conservative option trader you can employ different hedging strategies like Bull Spreads or Bear Spreads.

Options Max Pain Calculator

Max pain is calculated using the Open Interest of options. The calculation is fairly simple and is summarized below:

1. List down the different strikes of the option chain along with their open interests.
2. For each strike, calculate the profit/loss incurred to the option writers if the underlying expires at that level. This calculation should sum up the profit/loss for put as well as call option at that strike price.
3. The strike price at the the loss is minimum is termed as Max Pain strike price.

Automated Excel sheet for Max Pain calculation

We have created Options max pain calculator excel sheet which does all the background calculations and shows the Max Pain strike price. This excel sheet is automated and you need not to manually enter any data into the sheet. Currently this excel sheet supports both Nifty and Banknifty from NSE. When you open the sheet it would throw some warnings which can be safely ignored. The sheet would refresh every 5 minutes.

Below are few screenshots:

Download link for Options Max Pain Calculator

Please see the below link to download Options max pain calculator excel sheet. Mostly everything is self explanatory, but please let us know if you have any queries. Also, send us your feedback on how to improve this sheet.

Max Pain Calculator: Download Link

Liked the article? Please share it with your friends!

VWAP Trading Strategy: Excel Sheet

VWAP or Volume weighted average price is an indispensable tool for intraday traders to forecast the price move of stocks. It does not necessarily give trading signals but it helps in buying low and selling high. When used with other trading indicators it can definitely help in increasing the accuracy of your trading strategy. It is also used by large institutional investors or hedge funds to buy/sell at a point which would not cause sudden movement is stock prices. VWAP factors in the volume of stock and hence indicates the current momentum as well as true average price. In this post, we would go through a VWAP Trading Strategy Excel sheet. This is an automated excel sheet which would calculate absolute VWAP value for various intraday timeframe.

You can also refer some of our very popular Excel based trading systems in the below link:

Excel Based Trading Systems

VWAP Calculation

As the name suggests, VWAP is the weighted average of stock price over a specified time period. The stock price is weighted based on the volume for that specified price candle. Below is the formula used to calculate VWAP:

∑ Number of Shares Purchased x Price of the Shares ÷ Total Shares Bought During the Period

As you can see, by multiplying the number of shares by the price, then dividing it by the total number of shares, you can easily find out the volume weighted average price of the stock. Since VWAP takes volume into consideration, you can rely on this more than the simple moving average.

Below are the steps in calculating VWAP:-

1. Calculate the average or typical price movement of stock in specified time period. (H+L+C)/3
2. Multiply Volume of the period with typical price computed in Step 1 above.
3. Calculate the cumulative total of values computed in Step 2.
4. Calculate the cumulative total of Volume.
5. Find the ratio of values calculated in Step 3 and Step 4. This ratio is termed as VWAP.

VWAP Applications

VWAP has numerous application in the trading world. It is helpful for both institutional investors and retail intraday traders. Below are some well known applications of VWAP:-

• It helps in Buying low and Selling High. If the price is below VWAP, it is considered as undervalued, while price above VWAP is considered as overvalued.
• Crossing of prices above/below VWAP line in chart indicates momentum shift or change of trend.
• VWAP is also used as a trading benchmark by institutional investors who are not worried about the timing of the trade, but who are concerned about the adverse impact of their trades on the price of the security.
• VWAP serves as a reference point for prices for one day. As such, it is best suited for intraday analysis. Chartists can compare current prices with the VWAP values to determine the intraday trend.
• VWAP indicator can be used as a dynamic support/resistance line during sideways market.

VWAP Trading Strategy Excel Sheet

Below are the steps to use VWAP Trading Strategy Excel Sheet:

Step 1: Download the Excel file from the end of this post.

Step 2: Open this Excel file and make sure you are connected to internet. Please accept if it asks to enable Macros and Data connections.

Step 3: Input the Symbol Name, Exchange Name, Interval and Number of Days.

Step 4: Click on Get Data button. The data would be automatically downloaded and chart would be refreshed. The last columns indicates VWAP value.

Step 5: Green cell indicates VWAP is below closing price (undervalued), Red cell indicates VWAP is above closing price (overvalued).

Download link for VWAP Trading Strategy Excel Sheet

Please see the below link to download VWAP Trading Strategy Excel Sheet. Please let us know if you have any queries. Also, send us your feedback on how to improve this sheet.

VWAP Trading Strategy

Liked the article? Please share it with your friends!