Hi all,
I write a RSI indicator function:
rsi:{100*0.5*1+{mavg[x;y]%mavg[x;abs y]}[x;y-prev y]};
According to the calculation of RSI:
RSI = 100*(1-1/(1+RS))
RS = Average Gain / Average Loss
However, the result of this function is different from this page:
http://stockcharts.com/help/doku.php?id=chart_school:technical_indicators:relative_strength_in
Anyone has ever written RSI indicator? Do you have a better method to implement RSI?
Cheers,
Roy
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
?
Terry,
Thank you. Your solution is exactly correct. It works.
HOWEVER,
============== What I mean ===============
1.
Let A: Average Gain
Let B: Average Loss
So, RS = A/B
sma[x;y] = A-B
sma[x;abs y] = A+B
So,
F={sma[x;y]%sma[x;abs y]}[x;y-prev y]} = (A-B)/(A+B)=(RS-1)/(RS+1)=1-2/(RS+1)
RSI = 100*0.5*(1+1-2/(RS+1))=100*0.5*(1+F)
So, my function include the derivative as above all.
rsi:{100*0.5*1+{sma[x;y]%sma[x;abs y]}[x;y-prev y]};
I can not find the error. just different result. feel confused.
Roy.
I am not entirely sure what you are getting at with your second post, but if this is any way a help:
The problem you were seeing in the first function is due to mavg, which uses mcount. Mcount ignores null as part of its count, and so were not truly getting a 14 day average. As you can see in the excel sheet it divides by 14, but in the first 14-day RSI the mcount of the avg loss would have been 9 for example. Hence giving differing values of RS and subsequently the wrong value of RSI.
To obtain the first 14-day RSI in the excel sheet you can change the mavg def to use the x day average you are passing in.
q)mavg1:{msum[x;0.0^y]%x}
q)rsi2:{100*0.5*1+{mavg1[x;y]%mavg1[x;abs y]}[x;y-prev y]}
q)1# reverse rsi2[14;lst]
,70.532789483695 //first 14-day RSI
P.S. This doesn’t include the logic for the subsequent calcs as Terry’s does, but hopefully helps with the confusion in obtaining the correct figure. If not post your sma definition and I am sure we can help.
Thanks,
Sean
Hey, Sean
I agree with you. mavg1 and mavg is indeed not equal.
My sma function is the same with mavg.
q)sma:mavg
q)rsi:{100*0.5*1+{sma[x;y]%sma[x;abs y]}[x;y-prev y]};
your function is:
q)mavg1:{msum[x;0.0^y]%x}
q)rsi2:{100*0.5*1+{mavg1[x;y]%mavg1[x;abs y]}[x;y-prev y]}
and i have a try with the lst (From excel _ QQQQ Close _ column)
q)rsi[14;lst] ~ rsi2[14;lst]
q)1b
But, they?re with the same result.
How to get this number?
,70.532789483695 //first 14-day RSI
Thanks,
Roy
It is the 14th element of the list, i.e. index into 13.
q)rsi[14;lst]13
70.532789483695 // I was just taking the last
For further proof you can copy the functions(H17:K17) within the excel sheet to H3:K16 so that you have a full sheet. P.S. You will need to hard reference the F4 field in some of the functions.
This gives the exact same results as the excel sheet(first 14 fields)
q)rsi[14;lst]
0n 0 19.3056456846204 7.03725606150205 49.6444359023641 61.835396941005 66.2458129348106 70.4244431065623 74.4506566116239 76.2945984256718 72.169918977519 73.2365771351751 65.6889042006053 70.532789483695
HTH,
Sean
On Saturday, March 29, 2014 2:34:42 PM UTC, Roy wrote:
Hi all,
I write a RSI indicator function:
rsi:{100*0.5*1+{mavg[x;y]%mavg[x;abs y]}[x;y-prev y]};
According to the calculation of RSI:
RSI = 100*(1-1/(1+RS))
RS = Average Gain / Average Loss
However, the result of this function is different from this page:
http://stockcharts.com/help/doku.php?id=chart_school:technical_indicators:relative_strength_in
Anyone has ever written RSI indicator? Do you have a better method to implement RSI?
Cheers,
Roy
Hey Sean,
It’s very weird.
same function and input, but different result.
BTW
q)lst:data[`close]
You have the lst defined as 4 significant figures from excel, and you have your precision in q set at 7.
To obtain the most accurate results format your excel sheet to a higher number of decimal places(I chose 6), copy and paste into q.
Redefine \P to what you want, and rerun. You will get the correct results.
Here is my duplication of what you were seeing:
q)lst2:44.34 44.09 44.15 43.61 44.33 44.83 45.10 45.42 45.84 46.08 45.89 46.03 45.61 46.28 //4 sig fig.
q)\P 7
q)rsi2[14;lst2]13
70.46414
Thanks,
Sean
Hey Sean
You?re right!
I changed decimal precise of Excel, and get correct lst2.
q)rsi2[14;lst2] 13
70.532789483695// same with excel
However, after 14th element of result, it?s totally different with the excel RSI column.
Do you have the same result with me?
q)14 _rsi2[14;lst2]
70.532789483695 70.081054974592 69.894563357069 80.597700144287 73.3980534558..
Thanks,
Roy
Hey Roy
I think the answer from the Excel spreadsheet is not very accurate. If you look at the formulas in column H and I, the first one is correct but the rest, I disagree with the calculation:
H17 -> =(SUM(F4:F17)/14)
H18 -> =((H17*13)+F18)/14
…
…
I think you should have something like:
| Avg Gain | Avg Loss | RS | 14-day RSI |
| 0.238386 | 0.099593 | 2.39 | 70.53 |
| 0.238386 | 0.101771 | 2.34 | 70.08 |
| 0.236279 | 0.101771 | 2.32 | 69.89 |
| 0.263336 | 0.063393 | 4.15 | 80.60 |
| 0.212236 | 0.076921 | 2.76 | 73.40 |
| 0.176621 | 0.118229 | 1.49 | 59.90 |
| 0.197993 | 0.118229 | 1.67 | 62.61 |
| 0.177343 | 0.118229 | 1.50 | 60.00 |
| 0.147429 | 0.156686 | 0.94 | 48.48 |
| 0.183036 | 0.156686 | 1.17 | 53.88 |
| 0.183036 | 0.190864 | 0.96 | 48.95 |
| 0.173057 | 0.221486 | 0.78 | 43.86 |
| 0.173057 | 0.286286 | 0.60 | 37.67 |
| 0.136021 | 0.286286 | 0.48 | 32.21 |
| 0.138864 | 0.286286 | 0.49 | 32.66 |
| 0.1638 | 0.266343 | 0.61 | 38.08 |
| 0.161657 | 0.34825 | 0.46 | 31.70 |
| 0.1346 | 0.402371 | 0.33 | 25.07 |
| 0.168071 | 0.388843 | 0.43 | 30.18 |
I adjusted the formula in the spreadsheet, so I think both yours and Sean’s answer is correct.
The differing values is due to the subsequent calcs needed for the smoothing effect…
“”“The second, and subsequent, calculations are based on the prior averages and the current gain loss:”“”
“”“Average Loss = [(previous Average Loss) x 13 + current Loss] / 14.”“”
Using the standard 14 day calc would give these values but I think the smoothing effect needs to be considered and thus Terry’s handles this. I was only giving the first value.
Thanks,
Sean
On Saturday, March 29, 2014 2:34:42 PM UTC, Roy wrote:
Hi all,
I write a RSI indicator function:
rsi:{100*0.5*1+{mavg[x;y]%mavg[x;abs y]}[x;y-prev y]};
According to the calculation of RSI:
RSI = 100*(1-1/(1+RS))
RS = Average Gain / Average Loss
However, the result of this function is different from this page:
http://stockcharts.com/help/doku.php?id=chart_school:technical_indicators:relative_strength_in
Anyone has ever written RSI indicator? Do you have a better method to implement RSI?
Cheers,
Roy
Hey WooiKent
You are right. Our method are more accurate than the Excel which is a rough method.
Thank you!!
One false step will make a great difference.
Hey WooiKent
You are right. We are more accurate than the Excel.
Thanks!
Hey Sean,
I got it. Thank you for your kindly patience!
Roy