How to Calculate the SuperTrend Indicator using Excel

Last Updated on October 20, 2020 by Mark Ursell

This article shows you how to calculate the SuperTrend Indicator. Watch the video to see me demonstrate the calculations.

The article also explains why the indicator works, how to identify the best markets to use and gives you a link to a SuperTrend trading strategy.

Video – How to Calculate the SuperTrend Indicator in Excel

Watch the video to see me demonstrating how to calculate the SuperTrend Indicator. The formulas used in the video are shown at the bottom of this article: Formulas.

Read the Transcript

How to calculate the SuperTrend Indicator using Excel. Also learn about the indicator, the best markets to trade and a SuperTrend strategy.

Hello this is Mark from Tradinformed.com. Welcome to this video on How to Calculate

the SuperTrend Indicator in Excel. Now before I start talking about the SuperTrend Indicator

I would like to briefly mention that my new eBook course, How to Backtest a Trading Strategy

in Excel is now available in the Amazon Kindle Bookstore. For those of you who are interested

in programming indicators in Excel and then using those indicators to build backtest models,

whether Long Only or Long-Short and then to use those models to optimize the trading strategy.

Then there is a link on the screen at the moment for more information about this course.

Okay, so looking at the SuperTrend we have got an example on the screen at the moment.

The most distinguishing feature about the SuperTrend, we can see here, starting from

the left, is that when the price is in a downtrend, the SuperTrend indicator is a red line above

the price action. And when the trend changes direction the SuperTrend indicator flips to

below the price action and turns green. This is a very nice indicator for those of you

who like trading along with the trend and it is a useful way, when building models and

automatic trading, to identify the trend. But how do we calculate it? Okay, I’m going

to use the spreadsheet from the eBook course. You can use this on any historic data, on

any market, or any timeframe. The first thing we need to do to calculate the SuperTrend

is calculate the ATR. Or the average true range. I have done a previous showing how

to calculate the ATR. And there is a link on the screen if you are interested. Okay,

the second thing we are going to do to calculate the SuperTrend is to do our Upper Band basic

calculation. The SuperTrend is made up of an Upper Band and a Lower Band. And the basic

calculation for the Upper Band is quite simple. Take the High plus the Low and divide it by

2. And we get an average price, and then to this we add a multiple, a multiplier that

I’m going to put here. And we use F4 to make this an absolute reference cell and we multiply

this by the ATR. The multiple that we are going to use here is 2. This is one of the

two variables in the SuperTrend indicator. The other one is the length of the ATR. So

for the Lower Band we calculate this in the same way. We calculate our Average Price of

the High plus the Low divided by 2. And we take this and we subtract from it. The multiplier,

absolute reference F4, times the ATR. So we have our basic calculation for the Upper Band

and the Lower Band. The next two columns and actually going to be our Upper Band and our

Lower Band. The reason we can’t use the basic calculation we can see by having a look at

the model. And the other distinguishing thing is that, once we are in an uptrend, once we

have turned green, the price or the indicator rather, only moves in the direction of the

trend. Upward in this case and Downward in this case. So we are going to tell Excel how

to account for this by using an IF Statement. Which is to say, IF the Upper Band is lower

than the previous period then we use this basic calculation. If not then we use the

previous period. It is the same from the lower band. If our Lower Band Basic, is greater

than the previous period, then we use that value. IF not then we use the previous period’s

value. Okay we are almost there with our Upper Band and our Lower Band now, but we need to

account for the fact that these bands are not visible all the time. And we don’t want

it to do exactly the same thing when the upper band, when we are in an uptrend, the Upper

Band, we always want it to use the basic calculation. We do this by using an OR statement. I’ll

put it in at the front here. We are going to define it by saying that IF the previous

period’s Close is greater than the previous period’s Upper Band then always use the basic

calculation. Here, we put in an IF OR, if the

close of the previous period, this time is less than the previous period. Then always

use the lower band basic calculation. Copy these down a few rows. Now we have our correct

Upper Band and our Lower Band. What we need to do now is tell Excel when to use the Upper

Band and when to use the Lower Band. In other words so we have the actual SuperTrend indicator.

And we are going to do this again by using an IF statement, we are going to use a series

of four IF AND statements to account for all the possible scenarios. We are going to start

out by saying IF AND the previous period SuperTrend is equal to our Upper Band and our current

close is lower than our Lower Band, in brackets, then take that Upper Band. We are still in

a Downtrend. IF false, IF AND the SuperTrend previous period again equals our Upper Band,

but this time our Closing Price is greater than our Upper Band. In brackets. Then we

have to switch to the Lower Band. Again IF AND. Now I’m going to look at if we are in

an Uptrend. Our SuperTrend equals the Lower Band. Then look for whether the Close is greater

than the Lower Band. If so then use that Lower Band. Again, IF AND final time this time.

If our SuperTrend equals our Lower Band and our Close is less than our Lower Band then

switch to using the Upper Band. If not then do nothing. Okay, so there we have the SuperTrend

indicator. Again we can just highlight these cells and click on this square at the bottom

right hand corner and copy these down. The next video I am going to do in this sequence

will show how we can use the SuperTrend Indicator to create a trading strategy and then we’ll

backtest this trading strategy. For more information about trading and using Excel to backtest

trading strategies, please go to www.tradinformed.com

How to calculate the SuperTrend Indicator using Excel. Also learn about the indicator, the best markets to trade and a SuperTrend strategy.

What is the SuperTrend Indicator?

SuperTrend Indicator - Super Hero Icon

‘Super’ + ‘Trend’

The SuperTrend follows the dominant trend until the price closes below (for long trends) or above (for short trends) the indicator.

SuperTrend Contrast Image

What Else Does It Do?

The SuperTrend is calculated using the ATR (Average True Range). This means that it adjusts to recent market volatility.

The indicator only moves in the direction of the trend and it only changes direction on the close (which is why you can sometimes see price spikes through the indicator)

What Markets Should I Trade the SuperTrend On?

“Disregarding the big swing and trying to jump in and out was fatal to me. Nobody can catch all the fluctuations. In a bull market your game is to buy and hold until you believe that the bull market is near its end.”

Lefèvre, Edwin. Reminiscences of a Stock Operator

The SuperTrend indicator works better on markets that tend to trend. You will get better results by focusing on these markets. You can find them by focusing on:

  • Markets that have trended in the past – these have a much higher tendency to trend in the future.
  • Markets that have a strong identity among traders and investors.

Markets That Have Trended In The Past

There are many ways to identify these markets.

The simplest approach is to visually scan through charts showing a long time-frame. You are looking for markets that have tended to trend smoothly over the years and have not been too volatile or spent too long stuck in ranges.

To process more data you can use an online screener to look for markets that meet the criteria. You want to find markets that have moved up and down regularly without experiencing large amounts of volatility.

Markets That Have a Strong Identity with Traders and Investors

To use the example of the tech-heavy Nasdaq 100. This has shown an amazing tendency to trend over the years. This is because investors and traders understand that buying this index (or the underlying stocks) is a clear bet on ‘risk-off’ technology stocks.

By comparison, the FTSE 100 index of largest UK listed comapnies contains a disparate bunch. It has a heavy weighting of banks, oil producers and miners. It is unusual for all the elements to be performing well (or badly) at the same time and it tends to trend more slowly and have frequent retracements.

These days it is easy to select ETFs with a coherent identity with the expectation that they will trend in the future.

Excel Formulas to Calculate the SuperTrend

The SuperTrend indicator is calculated by using the ATR to offset the indicator from the average price. When the price touches the SuperTrend line it turns over to the other direction.

The formulas below can be copied directly into Excel. Watch the video above to see me demonstrate.

Step 1: Calculate the Upper and Lower SuperTrend Basic Bands

SuperTrend Calculation Step 1

In this step you calculate the basic bands.

Cell O3 is your SuperTrend multiplier.

Calculate the Average Price (High+Low)/2 and then offset this by the ATR * Multiplier.

Upper Band Basic O24 =((D24+E24)/2)+($O$3N24)

Lower Band Basic P24=((D24+E24)/2)-($P$3N24)

Step 2: Calculate the Final Upper and Lower Bands

SuperTrend Calculation Step 2

Now you need to make sure that the SuperTrend always moves in the direction of the trend.

You will do this by using an IF Statement.

Upper Band Q24 =IF(OR(O24<Q23,F23>Q23),O24,Q23)
Lower Band R24 =IF(OR(P24>R23,F23<R23),P24,R23)

Step 3: Get the Final SuperTrend Indicator

SuperTrend Calculation Step 3

You now need to complete the indicator. Remeber: the indicator only shows either the upper or lower band and changes direction when the price closes beyond the indicator.

This IF Statement checks when the closing price has crossed and the indicator changes direction.

SuperTrend S24 =IF(AND(S23=Q23,F24<=Q24),Q24,IF(AND(S23=Q23,F24>=Q24),R24,IF(AND(S23=R23,F24>=R24),R24,IF(AND(S23=R23,F24<=R24),Q24,””))))

Get Extraordinary Trading Results

The power to backtest strategies will transform your trading:

  • You can test new trading ideas as they occur to you.
  • You can choose only the best and most profitable strategies.
  • You can trade with confidence, knowing that your strategy has performed well.

The analysis on this page was carried out using a Tradinformed Backtest Model. The models are created in Excel and allow you to test different markets, try different indicators and entry conditions. To see the latest models check out the Tradinformed Shop.