Charting Stock Trendline

[pwinter]

One method of doing stock analysis has to do with identifying a
trendline. The trendline needs to be a straight line that crosses
three or more points of the low stock price. (see example below). Can
anyone think of a way of doing this in Excel? The trendline that
exists within Excel is a based on a regression so it's not straight.

Thanks for looking. - Paul

[image:
http://i20.photobucket.com/albums/b217/pwinter303/Excel_Example.jpg]

Here's the data I used to create the graph above (I drew the red line)
High Low Close
1-Jul 7.5 5 6
2-Jul 16.5 11 13.2
3-Jul 15 10 12
4-Jul 21 14 16.8
5-Jul 22.5 15 18
6-Jul 28.5 19 22.8

 

[Jon Peltier]

I added a series to the chart using an extra column:

High Low Close Low'
1-Jul 7.5 5 6 5
2-Jul 16.5 11 13.2
3-Jul 15 10 12 10
4-Jul 21 14 16.8
5-Jul 22.5 15 18 15
6-Jul 28.5 19 22.8

I had manually deleted the points the trendline doesn't pass through.
Since the values were already in the chart, I didn't have to use any
special techniques to avoid messing up the high low lines (my normal
stock charts don't use Excel's stock chart type, but error bars and
such). Then I added a trendline to this incomplete data series.

Somebody smart could probably develop an algorithm to progarmmatically
determine the points to use in the trendline.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______