Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 85
Default 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   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 9,101
Default 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   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 3,346
Default 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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
How do I determine the slope of a logarithmic scale data plot? ginger Charts and Charting in Excel 1 August 31st 06 08:47 AM
slope of data with the same y values Rosa Excel Discussion (Misc queries) 2 January 2nd 06 02:32 PM
Linest/slope functions with with different data ranges Pat Excel Worksheet Functions 1 August 8th 05 01:42 PM
plot 10 data pts in least square fit to find slope and intercept engineeringdoll Excel Worksheet Functions 1 April 14th 05 01:56 PM
how can i get the slope function to ignore missing data? Delmar Excel Discussion (Misc queries) 0 December 2nd 04 06:55 PM


All times are GMT +1. The time now is 10:29 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"