Excel spreadsheet that predicts RSI - Aussie Stock Forums

Results 1 to 9 of 9
  1. #1
    cudderbean's Avatar
    Join Date
    Feb 2010
    Perth and Thailand

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

  2. #2
    Garpal Gumnut's Avatar
    Join Date
    Jan 2006
    Ross Island Hotel, South Townsville, Citizen.
    Blog Entries

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

    "I refuse to join any club that would have me as a member." Quote Groucho Marx
    Je suis Charlie

  3. #3

    Default Re: Excel spreadsheet that predicts RSI

    Sorry, didn't see attachment icon.
    Attached Files

  4. #4
    white swans need love too Timmy's Avatar
    Join Date
    Sep 2007
    Between the lines

    Default Re: Excel spreadsheet that predicts RSI

    cudderbean, did you ever figure this out?
    The contents of this post were tested, ruthlessly, on small, cute, furry animals. Most of them were fatally harmed. Hence, if this post causes irritation, please discontinue reading immediately.

  5. #5

    Default 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. #6

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

    Default 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...
    Attached Files

  8. #8

    Default 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. #9
    Rotaredom wayneL's Avatar
    Join Date
    Jul 2004

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

Similar Threads

  1. Live feed for Excel spreadsheet?
    By wandering in forum Software and Data
    Replies: 7
    Last Post: 29th-September-2013, 11:09 PM
  2. Excel Spreadsheet
    By gregcourageous in forum Trading/Investing Resources
    Replies: 57
    Last Post: 6th-March-2013, 09:58 PM
  3. Excel 2007 spreadsheet active watchlist?
    By camel96 in forum Trading/Investing Resources
    Replies: 1
    Last Post: 29th-March-2010, 06:29 PM
  4. Excel spreadsheet code help
    By Family_Guy in forum Software and Data
    Replies: 2
    Last Post: 1st-December-2008, 08:07 AM
  5. Comsec contract notes -> Excel spreadsheet
    By MattB in forum Brokers
    Replies: 3
    Last Post: 28th-September-2007, 01:16 PM


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