Hey Roy,
I’m a bit rusty on the math and the q code but your solution doesn’t seem to isolate the gains/losses as far as I can tell, just takes absolute value and considers them all.
It is generally possible to replicate the steps taken by excel and to port them over to q, though it wouldn’t necessarily be best practice. For example, keeping it in a tablular format…
If I load in the data into a table as follows:
q)data:(“*F”; enlist “,”) 0: `:C:/Users/tlynch/Desktop/Q_RSI/date_close.csv
?q)data
Date ? ? ? ?Close
“14-Dec-09” 44.34
“15-Dec-09” 44.09
“16-Dec-09” 44.15
“17-Dec-09” 43.61
“18-Dec-09” 44.33
“21-Dec-09” 44.83
…
Then you can just replicate each column of excel
update change:Close-prev Close from `data
update gain:abs change*change>0, loss:abs change*change<0 from `data
update avg_gain:((14#0Nf),((sum 14#gain)%14),{(y+x*13)%14}[(sum 14#gain)%14;15_gain]) from `data
update avg_loss:((14#0Nf),((sum 14#loss)%14),{(y+x*13)%14}[(sum 14#loss)%14;15_loss]) from `data
update RS:avg_gain%avg_loss from `data
update RSI:?[avg_loss=0;100;100*RS%(1+RS)] from `data
This gives the same numbers as the excel example.
To put it into a single function call, it would first be useful to create a customised mavg function, then apply as follows:
mavg1:{a,{(y+x*z-1)%z}[;;x][a:sum[x#y]%x;(x+1)_y]}
rsi:{100*rs%1+rs:mavg1[x;y*y>0]%mavg1[x;abs y*(y:y-prev y)<0]}
q)data:(“*F”; enlist “,”) 0: `:C:/Users/tlynch/Desktop/Q_RSI/date_close.csv
?q)data
Date ? ? ? ?Close
“14-Dec-09” 44.34
“15-Dec-09” 44.09
“16-Dec-09” 44.15
“17-Dec-09” 43.61
“18-Dec-09” 44.33
“21-Dec-09” 44.83
…
update newcol:((14#0Nf),rsi[14;Close]) from `data
That’ll also do the trick. Note that I’ve left out the error handling and checking for division by zero etc. You can add that as necessary.
Terry
?