AmazonH

Calculating RSI 2 in excel

Okay so here's a post on RSI on request left by an anonymous user in comments to my post on RSI calculation. He was facing an issue with the calculation of RSI 2.

First What is RSI 2 ? In normal RSI (Wilder's RSI) we use data from past 14 days to calculate RSI. In RSI 2 we use just the data for past 2 days to calculate RSI.

So now the issue is with the computation of RSI 2 in Excel. The Average gain or avg loss often turns out to be zero as there could be two continuous days of gains or losses. The formula for RS is avg gain/avg loss. So in case avg loss is zero it so happens that then this value is not computable, or rather infinite.

What does one do in that case? Cause excel cannot do division by zero or infinite has on meaning for it. In reality if one looks at the formula for RSI = 100- 100/(1+RS). So the larger the value of RS, larger is the value for RSI. For value of RS = infinite, the value for RSI is 100.

So to be able to do the same calculation in excel what we will do is simply add a if statement before the formula for RS, such that if avg loss were equal to zero, RS would be a very high number, which in turn would mean that RSI would be 100 in all such cases. This should solve our problem. Look at the excel sheet to understand the concept better.Leave a comment if you have any doubts or issues


10 comments:

Anonymous said...

thanks for posting. for RSI 2, not sure why all the charting programs get different value. I got same as you, but you are better at this than me. Take a look at stockcharts.com for example. Use the SPX index and set RSI to 2. What they get is same as other charting programs. can you figure it out?

Pi said...

maybe they work with 1 day lag.. just check if their RSI2 figure for today is equivalent of our figure for yesterday's RSI2

danimal said...

full of siy were dont download

Anonymous said...

They probably use a running average which is calculated something like this:

New average = (Old average * (# days -1) + new value) / # days

In the case of RSI 2, if we have nothing but up days, then the average loss gets cut in half each day. It doesn't reach zero. Same with average gain if we have just down days.

Hope this helps with your calculation.

Anonymous said...

Dear Sir,
Can we set this RSI 2 in Amibroker ? If yes then can you please suggest the possible code for this ? I mean AFL ?

Thanks & Regards

Pi said...

Hi,

Yes i guess it should work. You just need to change the settings for calculation of RSI from 14 days to 2 days. Can you send me across the original code for RSI and I will do the reqd edit and send it back to you.

Anonymous said...

the reason why some programs would not agree with this way of computing it is because once RSI is calculated for any given 2 days with change (wwwwwwwway in the past), you can then extrapolate the future results the Wilder way:

100
RSI = 100 - --------
1 + RS

RS = Average Gain / Average Loss

Average Gain = [(previous Average Gain) x 1 + current Gain] / 2
First Average Gain = Total of Gains during past 2 periods / 2

Average Loss = [(previous Average Loss) x 1 + current Loss] / 2
First Average Loss = Total of Losses during past 2 periods / 2

Note: "Losses" are reported as positive values.

No division over 0 - voila!

Something else to think about - Wilder used this method of computation in the time when computers were ancient and practicaly were not used for this matter, so he had to do a lot of number crunching by hand. All of his signals were developed using this sequential method. So would the value of the "PROPERLY" computed method be more "accurate"? Or would it be mathematically correct, but for signals' sake, one should use the simplified method to see what Wilder saw?

Anonymous said...

Dear Sir, Thank you very much for your reply. I tried to find out the code for RSI in Amibroker Language. But I could not find it anywher, even in Help Section of Amibroker as there is in built function for RSI indicator and NOT any code. So pls help and guide, how to write AFL for RSI 2 in Amibroker.

Thanks & Regards

Anonymous said...

Dear Sir,
I tried to find the code but in Amibroker RSI, is an Inbuilt function and there is no code as such. So can you can you make this RSI - 2 converted into Amibroker compatible formula ?

Thanks & Regards

joy said...

hi i m trying to download the link of rsi2 excel ..but everytime it shows error message like, page not found. i need this badly.. tell me if u still have this excel sheet.

feel free to contact me on joy_julep@yahoo.com

AmazonH

Adsense