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