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