Using VBA to calculate Relative Strenght Index

[jonashvid]

Hello guys

I would like to know if anybody could help me with some VBA code.

I have a sheet where I have to calculate the Relative strenght Index, right now I am doing it like this.....

In column A I have the "dates",
In column B I have the closing stock price on the day given in column A. In column C I calculate the daily return as todays closing price minus the previus day closing prices.
In column D I have this =IF(OR(C4>0;C4=0);C4;0), wich puts the days with positive returns in this column.
In column E I have this =IF(C4<0;ABS(C4);0), wich puts the days with negative returns in this colunm.
In column F I have =AVERAGE(D4:D15)/AVERAGE(E4:E15)
In column G I have =100-(100/(1+F15))

Oh my god, there has to be an easyer way to do this!!!!!!!

To read more on how its is calculatet, go here: http://www.investopedia.com/terms/r/rsi.asp

the formula is very simpel but Excel makes it very difficult to use, I was wondering if someone could make it as a function????


Jonas Hvid

 

[Seti]

This is still cell formula driven but I think it gets at what the link suggested and is a little simpler.

Book1
	A	B	C	D	E	F	G
1	Date	Close	Return	Ups	Downs	RS	RSI
2	4/20/2006	10.5
3	4/21/2006	10.6	0.1	0.1
4	4/22/2006	10.71	0.11	0.11
5	4/23/2006	10.44	-0.27		0.27	0.388889	28
6	4/24/2006	10.47	0.03	0.03		0.296296	22.85714
7	4/25/2006	10.33	-0.14		0.14	0.390244	28.07018
8	4/26/2006	10.65	0.32	0.32		0.682927	40.57971
9	4/27/2006	10.77	0.12	0.12		0.663415	39.8827
10	4/28/2006	10.82	0.05	0.05		0.593496	37.2449
11	4/29/2006	10.58	-0.24		0.24	0.561538	35.96059
12	4/30/2006	10.5	-0.08		0.08	0.666667	40
13	5/1/2006	10.61	0.11	0.11		0.657534	39.66942
Sheet2

 

[jonashvid]

THX 4 your repl

but the way that you have shown it in the sheet, is the way that I am doing it now. I find it to much trouble to use 5 columns to get one number. The formula is fairly simpel:

RSI = 100 - (100/1+RS)

RS = Average of x days' up closes / Average of x days' down closes

It should be something like the code below. But it dosent work.

Function RSI(Periods As Integer)
Dim up_day
Dim down_day
Dim average_up
Dim average_down
Dim RS
Dim Myarray(Periods)

Range("N1411").Select 'starting position'

If Range("N1411") >= Range("N1412") Then up_day 1
If Range("N1411") <= Range("N1412") Then down_day 1

average_up = up_day / Periods
average_down = down_day / Periods

RS = average_up / average_down

RSI = 100 - (100 / RS)

End Function

 

[Seti]

OK. Here a new function. I made a change though as I assumed that the average of the ups should only use the up days rather than all days. Same for the downs. The code assumes that you will put a range in the function like this: =RSI(B10:B200)

Function RSI(MyCells As Range)
Dim up_day, down_day
Dim average_up, average_down
Dim RS, cellcount As Long
Dim cll As Range
ups = 0
up_day = 0
downs = 0
down_day = 0
cellcount = 0
For Each cll In MyCells
    cellcount = cellcount + 1
    If cellcount = MyCells.Count Then Exit For
    If cll.Value > cll.Offset(1, 0).Value Then
        downs = downs + cll - cll.Offset(1, 0).Value
        down_day = down_day + 1
    ElseIf cll.Value < cll.Offset(1, 0).Value Then
        ups = ups + cll.Offset(1, 0).Value - cll.Value
        up_day = up_day + 1
    End If
Next cll
average_up = ups / up_day
average_down = downs / down_day

RS = average_up / average_down

RSI = 100 - (100 / (1 + RS))

End Function

 

[jonashvid]

Thx for your reply it works, but.......

You wrote:

"I made a change though as I assumed that the average of the ups should only use the up days rather than all days."

It should actually be all the days, can u tell me what to change..

 

[jonashvid]

Fixed it my self the final code for calculating Relative Strenght Index, or RSI is:


Function RSI(MyCells As Range)
Dim up_day, down_day, ups, downs
Dim average_up, average_down
Dim RS, cellcount As Long
Dim cll As Range
ups = 0
up_day = 0
downs = 0
down_day = 0
cellcount = 0
For Each cll In MyCells
cellcount = cellcount + 1
If cellcount = MyCells.Count Then Exit For
If cll.Value >= cll.Offset(1, 0).Value Then
downs = downs + cll - cll.Offset(1, 0).Value
ElseIf cll.Value < cll.Offset(1, 0).Value Then
ups = ups + cll.Offset(1, 0).Value - cll.Value
End If
Next cll
average_up = ups / cellcount
average_down = downs / cellcount

RS = average_up / average_down

RSI = 100 - (100 / (1 + RS))

End Function


Thx for the help

 

[matthewye]

Function RSIv(Mycell As Range, k As Integer)



add_r = Mycell.Address
UpValue = 0
DownValue = 0



For i = 1 To (k - 1)
j = k - i
If Range(add_r).Offset(-j, 0) < Range(add_r).Offset(-j + 1, 0) Then
UpValue = (UpValue + Range(add_r).Offset(-j + 1, 0) - Range(add_r).Offset(-j, 0))
End If
If Range(add_r).Offset(-j, 0) > Range(add_r).Offset(-j + 1, 0) Then
DownValue = (DownValue + Range(add_r).Offset(-j + 1, 0) - Range(add_r).Offset(-j, 0))
End If
Next i


UpValue = UpValue / k
DownValue = Abs(DownValue) / k






CheckValue = 0
If WorksheetFunction.IsNumber(Range(add_r).Offset(-(k - 1), 0)) = True Then
CheckValue = 1
End If




If UpValue > 0 And DownValue > 0 Then
RSIv = 100 - (100 / (1 + (UpValue / DownValue)))
End If


If UpValue = 0 And CheckValue = 1 Then
RSIv = 0.001
End If


If DownValue = 0 And CheckValue = 1 Then
RSIv = 99.999
End If


End Function

 

[Chrisi_j]

Hi,
can someone please upload the Excel Sheet
Thx!