Excel spreadsheet that predicts RSI - Aussie Stock Forums

1. ## Excel spreadsheet that predicts RSI

In Excel I have written an RSI predictor based on a 14 day RSI on the close. It's very useful on end of day data for predicting such things as "What price must the close be tomorrow (or at any stage in tomorrow's trading) to trigger say an RSI of <70% after a stock is just peaking above 70%?"
Maybe traders are losing faith and the bears are moving in. Good chance to sell before the RSI figure is finalized with eod data.

Col A is close, Col B is RSI, Col C are up days figures, Col D are down day figures

Sorry, preview won't allow columns I formatted. The formulae row3 begins a new cell on the "equals sign"

910 40.5344307688661 60.077550324875 88.1360774116211
911 40.9633919016267 56.786296730241 81.8406433107911
=(((45/(100-45))*((13/14)*D2))-((13/14)*C2))+A2 =100-(100/(1+C3/D3)) =(C2/14)*13+(IF(A3>A2,A3-A2,0)) =(D2/14)*13+(IF(A3<A2,A2-A3,0))

910.00 40.53 60.08 88.14
911.00 40.96 56.79 81.84
920.45 45.00 62.18 75.99

Put all the figures into your Excel spreadsheet over 3 rows, 4 columns.. No need to do it to quite so many decimal places (not sure ???). I was just trying to show its not number format that may be causing the problem.

A1 and A2 are yesterday's and today's close. The formula in A3 is trying to predict what price "tomorrow" will trigger a 45% RSI. Answer as shown 920.45.
The formula works a treat all the way up to 99%RSI prediction.
Looking for lower RSI predicts, try substituting 42, then 41 for 45 in the formula. Still works perfectly.
BUT,
Try anything less than 40.96 i.e. the previous RSI confirmed in B2 and suddenly figures become distorted e.g. try 40 in the formula A3 and you get a predicted close of 908.93 which gives an RSI of 40.32 not pure 40 as it should be.

The lower the RSI number below 40 the greater the distortion.

Can anyone please help me to correct the formula in A3. I think I should be using 2 different formulae. e.g. IF its an up close use Formula A, otherwise Formula B.

This could be a very useful little spreadsheet if I could iron out the flaw.

2. ## Re: Excel spreadsheet that predicts RSI

It sounds like something out of the Torah mate, can you put it in an excel sheet and upload it for nongs such as me.

gg

3. ## Re: Excel spreadsheet that predicts RSI

Sorry, didn't see attachment icon.

4. ## Re: Excel spreadsheet that predicts RSI

cudderbean, did you ever figure this out?

5. ## Re: Excel spreadsheet that predicts RSI

No, I didn't work out the problem.

I settled in the end for the rather cumbersome Excel Solver from the function library. I think it quickly runs through various values in different parameters until it hits the one that gives the correct answer. OK to run manually on a particular stock, but I would rather incorporate it in a macro to scan multiple companies. It doesn't seem to like being incorporated in a macro.

Such is life!

6. ## Re: Excel spreadsheet that predicts RSI

Cudderbean, Taking a quick look at the spreadsheet, you can't calculate a new 14 day average by dividing yesterday's average by 14 and then multiplying by 13 and adding today's figure (as you've tried to do in cells D4 and E4). You have to drop (or subtract) the value of 14 days ago (so you need your entire 14 day history in your spreadsheet).

Do you have a more detailed spreadsheet you can attach?

7. ## Re: Excel spreadsheet that predicts RSI

I've played around a little more and created this version. Change the green cell to your 'target' RSI value and see how it works...

8. ## Re: Excel spreadsheet that predicts RSI

Thanks for your kind help, Tinkland

Apologies for not replying sooner. I have been travelling for the last month in remote parts of Indonesia... very limited internet.

9. ## Re: Excel spreadsheet that predicts RSI

This presumes the RSI has predictive power.

It does not.

An interesting mathematical exercise but unlikely to deliver any sort of edge.

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•
Aussie Stock Forums