Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to recognize changes in slope of data?
Not exactly an Excel question, but I am hoping some kind soul will
share his/her knowledge in this area. I'll ask my question by example. The problem is much more general, involving over 20,000 data points. When I look at the DJIA closing indexes from Sep through Nov 2008, I see the following trends. 9/2 thru 10/1: shallow downward slope; 10/1 thru 10/10: steep downward slope; 10/10 thru 10/27: shallow downward slope [1]; 10/27 thru 11/4: upward slope; and 11/4 thru 11/21: downward slope. Is there an algorithm that would recognize those trends? What I had hoped might work is a "moving SLOPE" algorithm. That is, for each date, calculate the slope of the N days ending on that date, and recognize trends by counting the sequence with the same sign [2]. As you might imagine, that did not work. First, the change in the sign of the SLOPE trails the points of inflection that I observe empirically. Second, I found myself fudging N to get something close to what I observed for __that__ sample data. I am uncomfortable doing that; but then again, I don't know what N would be reasonable. Before I started working with a "moving SLOPE", I did try simpler things, none of which worked satisfactorily. The simplest approach is a "moving N-day yield" -- that is, y[i-N]/y [i] - 1 -- and again, counting the sequence with the same sign. That has many anomalies, of course. Another approach that I considered but have not tried yet: applying either of those two approaches to an N-day moving average of DJIA indexes. But again, I think the "moving SLOPE" approach will trial the inflection points that we observe empirically. I am not above (or below?) developing VBA code to do this. But I'm still at loss to figure out a general algorithm, one that makes sense theoretically, not just tailored to the sample data and hope for the best. Any thoughts? Endnotes: [1] Actually, I perceive a horizontal slope from 10/10 thru 10/22, and a downward slope from 10/20 thru 10/27. [2] I would probably recognize horizontal trends by some range check on the slope as a percentage of the data. But I never got that far along. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to recognize changes in slope of data?
I think a good algoritm would be to look for highs and and lows rather the
try to look at the slope. I would ignore any highs and lows if they last less than 5 days from previous highs and lows. " wrote: [i] Not exactly an Excel question, but I am hoping some kind soul will share his/her knowledge in this area. I'll ask my question by example. The problem is much more general, involving over 20,000 data points. When I look at the DJIA closing indexes from Sep through Nov 2008, I see the following trends. 9/2 thru 10/1: shallow downward slope; 10/1 thru 10/10: steep downward slope; 10/10 thru 10/27: shallow downward slope [1]; 10/27 thru 11/4: upward slope; and 11/4 thru 11/21: downward slope. Is there an algorithm that would recognize those trends? What I had hoped might work is a "moving SLOPE" algorithm. That is, for each date, calculate the slope of the N days ending on that date, and recognize trends by counting the sequence with the same sign [2]. As you might imagine, that did not work. First, the change in the sign of the SLOPE trails the points of inflection that I observe empirically. Second, I found myself fudging N to get something close to what I observed for __that__ sample data. I am uncomfortable doing that; but then again, I don't know what N would be reasonable. Before I started working with a "moving SLOPE", I did try simpler things, none of which worked satisfactorily. The simplest approach is a "moving N-day yield" -- that is, y[i-N]/y - 1 -- and again, counting the sequence with the same sign. That has many anomalies, of course. Another approach that I considered but have not tried yet: applying either of those two approaches to an N-day moving average of DJIA indexes. But again, I think the "moving SLOPE" approach will trial the inflection points that we observe empirically. I am not above (or below?) developing VBA code to do this. But I'm still at loss to figure out a general algorithm, one that makes sense theoretically, not just tailored to the sample data and hope for the best. Any thoughts? Endnotes: [1] Actually, I perceive a horizontal slope from 10/10 thru 10/22, and a downward slope from 10/20 thru 10/27. [2] I would probably recognize horizontal trends by some range check on the slope as a percentage of the data. But I never got that far along. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to recognize changes in slope of data?
Hi,
In general for this kind of data one uses a moving average and then changes the number of date in the average. If you plot your data on a Stock Market chart in Excel, you will find that you can add a Moving Average Trendline. To add a trendline you select the series on the chart and choose Chart, Add Trendline, the 6th trendline type is Moving Average. Also, what you are talking about is taking the first derivative of the line at a point to determine its slope. So in effect you are talking calculus. Excel doesn't have a function to find derivatives, however it does have a SLOPE function, which you might want to look at. If this helps, please click the Yes button Cheers, Shane Devenshire " wrote: [i] Not exactly an Excel question, but I am hoping some kind soul will share his/her knowledge in this area. I'll ask my question by example. The problem is much more general, involving over 20,000 data points. When I look at the DJIA closing indexes from Sep through Nov 2008, I see the following trends. 9/2 thru 10/1: shallow downward slope; 10/1 thru 10/10: steep downward slope; 10/10 thru 10/27: shallow downward slope [1]; 10/27 thru 11/4: upward slope; and 11/4 thru 11/21: downward slope. Is there an algorithm that would recognize those trends? What I had hoped might work is a "moving SLOPE" algorithm. That is, for each date, calculate the slope of the N days ending on that date, and recognize trends by counting the sequence with the same sign [2]. As you might imagine, that did not work. First, the change in the sign of the SLOPE trails the points of inflection that I observe empirically. Second, I found myself fudging N to get something close to what I observed for __that__ sample data. I am uncomfortable doing that; but then again, I don't know what N would be reasonable. Before I started working with a "moving SLOPE", I did try simpler things, none of which worked satisfactorily. The simplest approach is a "moving N-day yield" -- that is, y[i-N]/y - 1 -- and again, counting the sequence with the same sign. That has many anomalies, of course. Another approach that I considered but have not tried yet: applying either of those two approaches to an N-day moving average of DJIA indexes. But again, I think the "moving SLOPE" approach will trial the inflection points that we observe empirically. I am not above (or below?) developing VBA code to do this. But I'm still at loss to figure out a general algorithm, one that makes sense theoretically, not just tailored to the sample data and hope for the best. Any thoughts? Endnotes: [1] Actually, I perceive a horizontal slope from 10/10 thru 10/22, and a downward slope from 10/20 thru 10/27. [2] I would probably recognize horizontal trends by some range check on the slope as a percentage of the data. But I never got that far along. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I determine the slope of a logarithmic scale data plot? | Charts and Charting in Excel | |||
slope of data with the same y values | Excel Discussion (Misc queries) | |||
Linest/slope functions with with different data ranges | Excel Worksheet Functions | |||
plot 10 data pts in least square fit to find slope and intercept | Excel Worksheet Functions | |||
how can i get the slope function to ignore missing data? | Excel Discussion (Misc queries) |