# Excel spreadsheet that predicts RSI



## cudderbean (19 August 2010)

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.

Thank you for your help.


----------



## Garpal Gumnut (19 August 2010)

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


----------



## cudderbean (20 August 2010)

Sorry, didn't see attachment icon.


----------



## Timmy (13 September 2010)

cudderbean, did you ever figure this out?


----------



## cudderbean (17 September 2010)

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!


----------



## tinkland (9 November 2010)

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?


----------



## tinkland (9 November 2010)

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...


----------



## cudderbean (15 November 2010)

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.


----------



## wayneL (15 November 2010)

This presumes the RSI has predictive power.

It does not.

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


----------

