|
PureBytes Links
Trading Reference Links
|
Columns
a=date
b=open
c=high
d=low
e=close
f=vol
g=up momentum
h=down momentum
i=up average
j=down average
k=RSI
- 0 -
Stocks & Commodities V. 11:7 (292-297): SIDEBAR: CALCULATING THE RSI
CALCULATING THE RSI
The RSI classic
The first step in calculating a 14-period relative strength index (RSI) in a
Microsoft Excel spreadsheet is
determining the up momentum and the down momentum. This is performed in
columns G and H in
sidebar Figure 1. The formula for up momentum beginning with cell G3 is:
=IF (E3>E2, E3-E2, 0)
The formula for down momentum beginning with cell H3 is:
=IF(E3<E2, ABS (E3-E2), 0)
Next, the average up momentum is calculated in column I. Only the first
day's calculation uses a simple
moving average. The formula for cell I16 is:
=AVERAGE(G3:G16)
The first day's average down momentum in column J uses a simple moving
average. The formula for cell
J16 is:
=AVERAGE(H3:H16)
>From this point on, the original RSI formula uses a different smoothing
method for calculating the
average up and down momentum. The formula uses yesterday's average up
momentum multiplied by 13
plus today's up momentum and then divides by 14. This formula is used for
the remainder of column I.
The formula beginning with cell I17 is:
=(I16*13+G17)/14
The same formula is used for calculating the average down momentum in column
J. The formula
beginning with cell J17 is:
= (J16*13+H17)/14
The RSI is calculated in column K. The formula beginning with cell K16 is:
=100-(100/(1+(I16/J16)))
RSI modifications
The first RSI modification uses a simple moving average for smoothing all
the up and down momentum.
Instead of entering the smoothing formulas in cells I17 and J17 (as
discussed above), simply use the same
formulas in I16 and J16 for all of columns I and J, respectively.
Another smoothing method is to use an exponential moving average for
smoothing the up and down
momentum. Use the simple moving average formula for cells I16 and J16 to
start and then substitute the
following formula in column I, beginning with cell I17:
=(0.133*G16+(1-0.133)*I16)
The new formula for column J beginning with cell J17 is:
=(0.133*H16+(1-0.133)*J16)
RSI plus volume
Volume was included in the next R SI version. The new formula multiplies the
one-day up or down
momentum by the volume and then calculates the simple moving average of the
volume adjusted up and
down momentum. Volume is listed in column F. The new formula for column G
beginning with cell G3
is:
=IF(E3>E2,(E3-E2)*F3,0)
The new formula for column H beginning with cell H3 is:
=IF(E3<E2,ABS(E3-E2)*F3,0)
Then use a simple moving average method to smooth the up and down momentum
in columns I and J.
RSI with average price (H+L)/2
Instead of using the closing difference to calculate up and down momentum,
the average of the day's
range is used. For columns G and H, substitute the following formulas
beginning with cell G3:
=IF((C3+D3)/2>(C2+D2)/2,((C3+D3)/2-(C2+D2)/2),0)
The new formula for column H beginning with cell H3 is:
=IF((C3+D3)/2<(C2+D2)/ 2,ABS((C3+D3)/2-(C2+D2)/2),0)
Use the simple moving average method to calculate the average up and average
down momentum in
columns I and J.
RSI with yesterday's open
The RSI that compares today's close with yesterday 's open uses the
following formulas for columns G
and H beginning with cell G3:
=IF(E3>B2,E3-B2,0)
The new formula for column H beginning with cell H3 is:
=IF(E3<B2,ABS(E3-B2),0)
Use the simple moving average method to smooth the up and down momentum in
columns I and J.
Best regards
Walter
----- Original Message -----
From: michael gilbert <tradermike@xxxxxxxxxxxxxxxx>
To: <metastock@xxxxxxxxxxxxx>
Sent: Monday, April 19, 1999 2:06 AM
Subject: excel-r.s.i.
> can anyone help me configure the cells in excel to calculate the
> rsi value?
> Thanks in advance
> Michael
>
|