How to recognize changes in slope of data?

[curiousgeorge408]

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.

 

[Joel]

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.

 

[Shane Devenshire]

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

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.