RSI: 100 - (100/1+RS), whereby RS = an exponential moving average (14 day average is common, although if I will be using this inlay with extremely volatile markets, something less like a 10-day-EMA may be more approproate). This will return a number between 0-100. A figure of 30 or less shows undersold and 70 or above shows overpriced so we have two triggers. Alternatively, gains/losses were recommended as follows:
100
RSI = 100 - --------
1 + RS
RS = Average Gain / Average Loss
To simplify the calculation explanation, RSI has been broken down into its basic components: RS, Average Gain and Average Loss. This RSI calculation is based on 14 periods, which is the default suggested by Wilder in his book. Losses are expressed as positive values, not negative values.
The very first calculations for average gain and average loss are simple 14-period averages.
RSI (Horse 1):
=100-(100/12EMA)
Your description and equations for RSI are not the Welles wilder calculations, or anything like them.
Firstly the way the average gain is calculated is using a exponential moving average, not using the standard Excel Average function. Your equations are not calculating the average gain or the average loss. you just seem to be using a moving average of the price.
I have programmed the Welles Wilder RSI equations into VBA functions, there are three of them.
One thing to realised about exponential moving averages is that in theory you need an infinitely long set of data to get the correct value. In practice that values will settle down within about two times the time constant you are using, unless the values are changing very rapidly .
I have written functions to calcualate the average gains (UPS) the average loss (Downs) and the RSI (RSI)
the functions are :
Function RSI(Pricearr As Range)
Dim Rarr As Variant
If Pricearr.Columns.Count > 1 Then
' Note this is the welles wilder calculation for the ongoing period
If Pricearr.Columns.Count > 2 Then
RSI = "Error More than one column in range"
Else
Browcount = Pricearr.Rows.Count
If Browcount = 1 Then
Upssum = Pricearr.Value2(1, 1)
Downssum = Pricearr.Value2(1, 2)
rs = Upssum / Downssum
RSI = 100 - 100 / (1 + rs)
Else
RSI = "Error More than one row in range"
End If
End If
Exit Function
End If
' Note this is the welles wilder calculation for the initial period
Browcount = Pricearr.Rows.Count
startrow = Pricearr.Row
startcol = Pricearr.Column
ReDim Rarr(1 To Browcount, 1 To 2) As Variant
For mm = 1 To Browcount
kk = 1
Rarr(mm, kk) = Pricearr.Value2(mm, kk)
Next mm
'Rarr = Range(Cells(startrow, startcol), Cells(startrow + browcount, startcol)).Value
gain = 0
loss = 0
rs = 0
If Browcount > 2 Then
' Use this for the start value of RSI
For ii = 2 To Browcount Step 1
Change = Rarr(ii, 1) - Rarr(ii - 1, 1)
If Change > 0 Then
'up day
gain = gain + Change
Else
'down day
loss = loss - Change
End If
Next ii
avgain = gain / Browcount
avloss = loss / Browcount
If avloss > 0 Then
rs = avgain / avloss
RSI = 100 - 100 / (1 + rs)
Else
RSI = 100
End If
Else
If Browcount = 1 Then
RSI = "FORMAT: 'RSI(RANGE) where RANGE must be a single column of numbers with at least two rows or two columns (ups then downs)with a single row '"
Else
RSI = " Error Less than three rows in range"
End If
End If
End Function
Function ups(Pricearr As Range, Lastime As Variant, TC As Variant)
If Pricearr.Columns.Count <> 1 Or Pricearr.Rows.Count <> 2 Then
ups = "'ups(RANGE, Lastime, TC) where RANGE must be 1 columns of numbers in 2 rows, Last Time value , Time Constant"
Exit Function
End If
ReDim Rarr(1 To 2, 1) As Variant
For kk = 1 To 2
Rarr(kk, 1) = Pricearr.Value2(kk, 1)
Next kk
up = Rarr(2, 1) - Rarr(1, 1)
If up < 0 Then
up = 0
End If
ups = (up + (TC - 1) * Lastime) / TC
End Function
Function downs(Pricearr As Range, Lastime As Variant, TC As Variant)
If Pricearr.Columns.Count <> 1 Or Pricearr.Rows.Count <> 2 Then
downs = "'Downs(RANGE, Lastime, TC) where RANGE must be 1 columns of numbers in 2 rows, Last Time value , Time Constant"
Exit Function
End If
ReDim Rarr(1 To 2, 1) As Variant
For kk = 1 To 2
Rarr(kk, 1) = Pricearr.Value2(kk, 1)
Next kk
down = Rarr(1, 1) - Rarr(2, 1)
If down < 0 Then
down = 0
End If
downs = (down + (TC - 1) * Lastime) / TC
End Function
To illustrate how to use them I have used them in this worksheet . Notice in particular the the first value for the RSI is calculated directly from the prices not the ups and downs.
RSIdemo.JPG