SpreadsheetMonkey
New member
- Joined
- Jun 28, 2012
- Messages
- 1
It's been 20 years since I took calculus so to be honest I'm not sure that calculus is what this is. So bear with me. This is somewhat long and theoretical, but feel free to build out your own spreadsheet and follow along if you see fit. Within this post are 3 questions and if anyone could help me I would certainly appreciate it.
I started with the question "Is it going up?" And then "Which is going up more?"
Those seem like easy questions, but then you have to ask "over what time frame" and they become decidedly less easy. I decided to go with the last 3 months (approximately 65 trading days), and attempt to weight toward the present. I made a spreadsheet. Yahoo is an easy source of historical data, here it is for SPY the basic S&P 500 Index ETF:
http://finance.yahoo.com/q/hp?s=SPY+Historical+Prices
So I downloaded 3 years worth, filter it with Excel and flip it upside down so it's oldest to newest. Then I had to decide how to weight it, which comes to the first place I am unsure. I created two basic formulas in Excel:
=SUM(((G67-G66)/G67)+((G67-G64)/G67)+((G67-G61)/G67)+((G67-G57)/G67)+((G67-G52)/G67)+((G67-G47)/G67)+((G67-G42)/G67)+((G67-G34)/G67)+((G67-G18)/G67)+((G67-G2)/G67))
(ignore the cell values - the Yahoo download goes through Column G, so I build this into Column I)
That one takes the percent change from today to yesterday, 3 days ago, 6 days ago, 10 days ago, 15 days ago, 20 days ago, 25 days ago, 33 days ago, 49 days ago, and 65 days ago and weights them equally. It seems correct to me that if one takes more data points from closer to the present, then the overall value will be weighted toward the present. Is that logical?
So then I tried a second formula:
=SUM((((G67-G66)/G67)*50)+(((G67-G61)/G67)*20)+(((G67-G52)/G67)*15)+(((G67-G34)/G67)*10)+(((G67-G2)/G2)*5))
That takes the percent change from yesterday and gives it a 50% weighting, the change from 6 days ago with a 20% weighting, the change from 15 days ago with a 15% weighting, the change from 33 days ago with a 10% weighting, and the change from 65 days ago with a 5% weighting. Is this logical? Does it also give a realistic, weighted toward the present, value?
If one builds this out in Excel the result is a relatively (sorry) meaningless number. So when reading this link about something called the Relative Strength Index, a widely used indicator in finance, I chose to use a similar system to try to achieve a more meaningful number which could then be ranked:
http://stockcharts.com/school/doku....ool:technical_indicators:relative_strength_in
So being of sound mind, I used the exact same formula:
=(100-(1/(1+I67)))
So at first I was excited - everything was coming up with a number between 99 and 100. I have a paid tool that gives a "relative strength" ranking and my fast and dirty (and free ... would be nice not to have to pay for the tool!) Excel job was giving extremely similar results. So I used "fill down" in Excel to copy the formulas down through 3 years of data. What I found was that for the most part the numbers stayed between about 98 and 102 and everything was just dandy. But then I found that my second formula was breaking down, badly. It would spike to 111, then 137, then the next day drop down to 99 again. Or here is another example - a day where the first formula goes from 97.95 up to 98.45. But the second formula goes from 100 all the way down to 88.
My brain is telling me that it almost knows what is going on here, and that the reason that the second formula is super inconsistent and the first isn't has something to do with the way I tried to weight it. But I cannot figure it out. Probably the formulas moving in opposite directions also reflects the attempt to weight them. If someone who really understands math could explain this I would appreciate it. And if anyone could offer more consistent, "better" way to write either of the first 2 formulas, or a better way to convert the results into a useful number like they do in the RSI link, I would really appreciate any thoughts on that also.
Thank you for your time.
I started with the question "Is it going up?" And then "Which is going up more?"
Those seem like easy questions, but then you have to ask "over what time frame" and they become decidedly less easy. I decided to go with the last 3 months (approximately 65 trading days), and attempt to weight toward the present. I made a spreadsheet. Yahoo is an easy source of historical data, here it is for SPY the basic S&P 500 Index ETF:
http://finance.yahoo.com/q/hp?s=SPY+Historical+Prices
So I downloaded 3 years worth, filter it with Excel and flip it upside down so it's oldest to newest. Then I had to decide how to weight it, which comes to the first place I am unsure. I created two basic formulas in Excel:
=SUM(((G67-G66)/G67)+((G67-G64)/G67)+((G67-G61)/G67)+((G67-G57)/G67)+((G67-G52)/G67)+((G67-G47)/G67)+((G67-G42)/G67)+((G67-G34)/G67)+((G67-G18)/G67)+((G67-G2)/G67))
(ignore the cell values - the Yahoo download goes through Column G, so I build this into Column I)
That one takes the percent change from today to yesterday, 3 days ago, 6 days ago, 10 days ago, 15 days ago, 20 days ago, 25 days ago, 33 days ago, 49 days ago, and 65 days ago and weights them equally. It seems correct to me that if one takes more data points from closer to the present, then the overall value will be weighted toward the present. Is that logical?
So then I tried a second formula:
=SUM((((G67-G66)/G67)*50)+(((G67-G61)/G67)*20)+(((G67-G52)/G67)*15)+(((G67-G34)/G67)*10)+(((G67-G2)/G2)*5))
That takes the percent change from yesterday and gives it a 50% weighting, the change from 6 days ago with a 20% weighting, the change from 15 days ago with a 15% weighting, the change from 33 days ago with a 10% weighting, and the change from 65 days ago with a 5% weighting. Is this logical? Does it also give a realistic, weighted toward the present, value?
If one builds this out in Excel the result is a relatively (sorry) meaningless number. So when reading this link about something called the Relative Strength Index, a widely used indicator in finance, I chose to use a similar system to try to achieve a more meaningful number which could then be ranked:
http://stockcharts.com/school/doku....ool:technical_indicators:relative_strength_in
So being of sound mind, I used the exact same formula:
=(100-(1/(1+I67)))
So at first I was excited - everything was coming up with a number between 99 and 100. I have a paid tool that gives a "relative strength" ranking and my fast and dirty (and free ... would be nice not to have to pay for the tool!) Excel job was giving extremely similar results. So I used "fill down" in Excel to copy the formulas down through 3 years of data. What I found was that for the most part the numbers stayed between about 98 and 102 and everything was just dandy. But then I found that my second formula was breaking down, badly. It would spike to 111, then 137, then the next day drop down to 99 again. Or here is another example - a day where the first formula goes from 97.95 up to 98.45. But the second formula goes from 100 all the way down to 88.
My brain is telling me that it almost knows what is going on here, and that the reason that the second formula is super inconsistent and the first isn't has something to do with the way I tried to weight it. But I cannot figure it out. Probably the formulas moving in opposite directions also reflects the attempt to weight them. If someone who really understands math could explain this I would appreciate it. And if anyone could offer more consistent, "better" way to write either of the first 2 formulas, or a better way to convert the results into a useful number like they do in the RSI link, I would really appreciate any thoughts on that also.
Thank you for your time.