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