40

Supertrend Indicator Excel Sheet with Realtime Buy Sell Signals

Posted on by admin

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.

Also Read: Options Trading Courses Online: A well reserached recommendation

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:

Supertrend Indicator Excel Sheet

Also Read: ADX Trend Strength Indicator: Realtime Excel Sheet

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.

Download link for Supertrend Indicator Excel Sheet

Please see the below link to download Supertrend indicator excel sheet. Let us know if you have any feedback or comments.

Supertrend Intraday v1.2

Spreadsheets for Success

Related Posts

40 Comments

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

  3. 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.

    Can you please help ?

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

  8. 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. It has some error because mostly only one direction is appearing mainly of Sell.

  11. 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. 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.

      • 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. Thank you so much Sir, for your kind attitude, your hard work and determination.

  14. 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. 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.

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

  17. 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.

    • 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.

    • 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.

  20. Sir can you please help with creating a screeners where stocks opened gapup or gapdown with open=high or open= low via excel sheet please

  21. Dear Admin,
    “I read google unfortunately discontinued its intraday data API.”
    Did you already find an alternative ?
    In the mean time is it possible to load end of day data?

  22. Any updates on this? Waiting eagerly for this to be updated and working as expected. TIA

    • Not yet, we are trying hard to find a reliable alternative to Google finance

Leave a Reply

Your email address will not be published.