Hello this is Mark from Tradinformed.com
and welcome to this video on how to calculate
the RSI Indicator. The RSI is the relative
strength index and it is a type
of momentum indicator the RSI literally
calculates the strength of the most recent close
compared to a set number a previous periods.
And we use the RSI to measure
and identify changes in the market trend
Now we're going to calculate the RSI indicator
and in the spreadsheet on the screen and I've got some historic data
already loaded onto the spreadsheet. I'm going to start here in this first column
and call it upward movement and the next column
I'm going to call it downward movement. And in the upward movement column what I want to identify
is that if the current close is greater than the previous close
then measure the difference between them. And likewise in
downward movement column I want to identify if
the current close is lower than the previous close
then again identify the difference and we do this by using an IF Statement
And I'm going to say = IF the current Close
greater than the previous close then
calculate the difference between these prices
and if not then do nothing
And likewise in here
we will then say = IF the current Close is
lower than the previous close and again
calculate the difference between them and if not
then do nothing. So we can drag these down to the
cells below. And the next two columns I'm going to work out the
average of these 2 movements. I'm going to put it here
the average upward movement and here the average downward movement
And I'm going to set a set number of
periods, this is a variable of the RSI Indicator and I'm
going to start off with 14 so I'm going to measure down 14 cells
and put in here =
Average and we take the average of these 14
periods. And I'm going to do the same
for the downward movement here. The cell below I'm going to use
a slightly different formula to calculate the next average
and this is previous average multiplied
by the number of periods -1
plus the current upward movement. And I take
all of that and divide it by the number of periods
periods. And go back into this
cell and because I specified cell U2
is an absolute reference cell. And I press F4
to make it a fixed cell
I do the same here, I take the previous average
multiply it by the number of periods
-1 and I'm going to add
to it the current downward movement and divide all of it
by the number period and again I'll go back into this
press F4
OK so these can now be copied down to the cells below.
This next column we're
going to work out the relative strength and this is simply the
current average upward movement divided by the current average downward
movement. And in our
final cell we're going to work out the RSI
the formula we use for this is =100
- 100
divided by the relative strength
+ 1. We put brackets around
this sum here
And then we can copy them down to the cells below by clicking on the bottom right hand corner
okay so there we have the RSI indicator
calculated in Excel if you're interested
in using indicators, technical indicators to
backtest your own trading strategies you may want to have a look
at my eBook course "How to Backtest
a Trading Strategy in Excel" there is a link on the screen to
get more information about this. Thank you very much
for more information about trading and backtesting using
Excel please visit:
www.Tradinformed.com