# Supertrend Indicator Excel Sheet with Realtime Buy Sell Signals

Supertrend is a popular trend following indicator which works particularly well in Intraday timeframe. As a technical analyst, you might have across this indicator quite often during your studies. It is known for its preciseness and accuracy in all the timeframes. It works surprisingly well for all the instruments i.e. equities, commodities and Forex. As it is not readily available in all trading platforms, we have created a macro based Supertrend Indicator excel sheet which calculates the indicator values and buy/sell signal in real-time. The excel sheet is parameterized and works for all major exchanges. Read through to understand how supertrend is calculated and download the excel sheet from the end of this post.

# Supertrend Indicator Calculation

Supertrend is a trend following indicator like moving averages. It is calculated based on Average true range (ATR) and a multiplier value. ATR measures the degree of volatility of the market.

A buy signal is generated when Supertrend indicator closes above the price and a sell signal is generated when it closes below the closing price.

Below are the mathematical formulas involved in calculation of supertrend indicator:
• True Range (TR) = Max of (High-Low, High-Close, Low-Close)
• Average True Range (ATR) = Average of last n TR values (n is the first parameter in Supertrend indicator. 10 is default)
• Basic Upperband  =  (High + Low) / 2 + Multiplier * ATR
• Basic Lowerband =  (High + Low) / 2 – Multiplier * ATR
• Final Upperband = IF( (Current Basic Upperband  < Previous Final Upperband) and (Previous Close > Previous Final Upperband)) Then (Current Basic Upperband) ELSE Previous Final Upperband)
• Final Lowerband = IF( (Current Basic Lowerband  > Previous Final Lowerband) and (Previous Close < Previous Final Lowerband)) Then (Current Basic Lowerband) ELSE Previous Final Lowerband)
• SUPERTREND = IF(Current Close <= Current Final Upperband ) Then Current Final Upperband ELSE Current  Final Lowerband
Looks complex? Don’t worry, we have created automated excel sheet which calculates all these values for real-time stock market data.

# Supertrend Indicator Excel Sheet Overview

This is an automated macro enabled spreadsheet which calculates Supertrend value and Buy/Sell signals for the symbol entered. You need not to do anything manually.

See the below screenshots:

# How to use this 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, Number of Days, ATR Periods, Multiplier calculation method

Step 4: Click on Get Data button. The data would be automatically downloaded and supertrend values would be calculated. This data can be downloaded for max 15 days and the minimum interval is 1 minute.

1. srinivasa a p

what exchange name used for commodity ?
I tried using MCX and ZINC for script name… it did not work…
pl help

Hi Srinivasa,

The sheet does not support Commodities currently.

2. sumit

can this also work for the individual stock

Yes Sumit, just enter the symbol name in the input section

3. David

Hi Team,
I have downloaded the Excel Sheet. Just backtesting I have entered PNB with default value but it has given incorrect signal buy though the stock is in bearish trend.

4. sunil kumar

hi i want this excel formula

5. Nishant Sheth

Sir,when is signal generated to trade?

6. Samir

How did you predict the future values

7. Prabu

This Excel is belongs to NSE Cash or NSE Futures? and i think BUY SELL is showing Wrongly.

8. pri

is the signal generated real time and what is the time delay… I use an alert in my brokerage that is 3 candles late. so for ex: on daily it give buy signal after it see 3 green days or hourly it gives buy after seeing 3 hourly candles etc…Let me know the delay so I know what I am going to use

There is no delay as such. Data is fetched directly from Google and signal is generated on realtime.

9. Pawan

It has some error because mostly only one direction is appearing mainly of Sell.

10. AJAY

SIR MADE ALL FNO STOCK SUPERTREND EXCEL SHEET

11. Roy

Hi Couple of questions
what will be the code for (1) Near month bank nifty futures .. say April end (2) Start time is from 9.30 AM whereas in the sheet its specfied as 9.15

Hi Roy,
1) The sheet works only on equities, not future quotes
2) Indian market (NSE) starts at 9:15 AM

12. Harsh

Thanks admin for giving your precious time in sharing such valuable resources. But I think there are few mistakes in excel sheet, particularly in the formula in supertrend and signal column. I have modified it and it is working perfect now. I would like to share the correct modified sheet if you want.

Hi Harsh,

Sorry to hear that you found mistakes in the sheet. Please share the corrected sheet at support@tradingtuitions.com so that we can review, also if you can briefly describe the corrections that you have made.

• Harsh

Sir, I hope you have checked out the sheets sent by me. Please provide your valuable feedback to it. And if you found them useful,kindly update the correct version so that others can also benefit from it.

Hi Harsh,
Thanks a lot for correcting the excel sheet and adding ATR calculation option. We have updated the same in the post.

13. Shailendra Saxena

Thank you so much Sir, for your kind attitude, your hard work and determination.

14. Shailendra Saxena

Just now I tried it with markets are closed being a Sunday, it returned – run time error ‘6’. what would be the probable reason ? is it supposed to work with live markets hours ?

15. Sandeep

Just now I tried it with markets are closed being a Sunday, it returned – run time error ‘6’. what would be the probable reason ? is it supposed to work with live markets hours ? Even today (monday) the same error sir

Hi Sandeep,

Can you please try again. I guess the reported error was fixed.

16. RK

Can we use your sheets for US markets.

Yes RK, all the strategies listed in this website is independent of any exchange

17. Arpit J Patel

Sir, I m not getting the data, says Runtime error ’13’: Type mismatch. I did enable macro and data connections on my excel 2010. i m getting the same error in ‘live updating excel’ sheet. previously, it used to run perfectly in march or so…. kindly tell us the reason

Hi Arpit,

Unfortunately google has discontinued its intraday data API since Aug 1st, due to which the excel sheet is unable to fetch realtime data. We are working on a alternate solution and will keep you updated. Thanks for your patience.

18. HARI

Getting Run Time error 13

Please tell me how to run

Hi Hari,

Unfortunately google has discontinued its intraday data API since Aug 1st, due to which the excel sheet is unable to fetch realtime data. We are working on a alternate solution and will keep you updated. Thanks for your patience.

19. jobin

Getting Run time error

Hi Jobin,

Unfortunately google has discontinued its intraday data API since Aug 1st, due to which the excel sheet is unable to fetch realtime data. We are working on a alternate solution and will keep you updated. Thanks for your patience.