line of best fit, linear regression, angle of line of best fit - VBA

[mhwolog]

Hello,I would greatly appreciate some help with this problem. I am using excel 2003, windows 7.I have been trying to work out which worksheet or vba function to use to calculate the following - but I just can't get the right answer. There are multiple facets to the vba sub I am trying to write. Vba is ideal so that I can use the values for other purposes. For starters, let's say I have 3 points which have an R value close to 1/line quite closely:x yweek 75 (date 14/2/1992) 6.81week 83 (date 10/4/1992) 6.95week 95 (date 3/7/1992) 7.08I can use either the week number or the date as the x-axis.I would like to work out the line of best fit which should pass through the 1st point. Then I want to work out the slope & intercept of the line of best fit, and the angle of the slope. As far as I can tell from manually plotting the data, the angle should be about 17.8 degrees {I keep getting angle values like 1 degree, 1.8 degrees and 4 degrees}.I have tried using the functions slope, linest, intercept, degrees(ATAN(slope) but I don't seem to get the correct angle. And I'm not sure whether I need to make the first x and/or y equal zero for the formulae to work. And I know that with these formulae, I am currently not getting the line of best fit to pass through the first point.----Overall what I am trying to achieve is to automate drawing a trend line on a stock price chart, I have the following data:x yweek 75 (date 14/2/1992) 6.81week 80 (date 20/3/1992) 7.2week 83 (date 10/4/1992) 6.95week 88 (date 15/5/1992) 7.52week 95 (date 3/7/1992) 7.08week 98 (date 24/7/1992) 7.02I want to use the 1st point as my reference/anchor point for lines of best fit. (But the reference point can be 1-2% (y-axis) lower than the 1st point, if this creates a better line of fit).Starting with the first three points, I would calculate a line of best fit going through the first point. If the R value is to far away from 1 (i.e. the points do not line up as close to possible in a straight line) then I would only keep the either the 2nd or third point (whichever is below the line of best fit) and I would retest for a line of best fit for points 1, 3, and 4. This process would repeat until the R value is close to 1.Up to here would be a great starting point if anyone can help.Just for argument's sake (if anyone really likes math/vba); the additional rules for this line of best fit would be:-If there is no acceptable line of best fit found with 3 points, it is possible to draw a line connecting the 1st and second points, and then use the second point as the reference/anchor point - to search from this point forward for 3 points that have a good line of fit (usually used in cases where the future line of best fit/stock prices, have started to move in a steeper angle up the y-axis. - This acceleration can/should occur for future points, even after an initial 3 point line of best fit is established. The new reference/anchor point in this case would be the 3rd of the initial 3 points.

 

[mhwolog]

Hello,
I would greatly appreciate some help with this problem. I am using excel 2003, windows 7.
I have been trying to work out which worksheet or vba function to use to calculate the following - but I just can't get the right answer. There are multiple facets to the vba sub I am trying to write. Vba is ideal so that I can use the values for other purposes. For starters, let's say I have 3 points which have an R value close to 1/line quite closely:

x y
week 75 (date 14/2/1992) 6.81
week 83 (date 10/4/1992) 6.95
week 95 (date 3/7/1992) 7.08

I can use either the week number or the date as the x-axis.I would like to work out the line of best fit which should pass through the 1st point. Then I want to work out the slope & intercept of the line of best fit, and the angle of the slope. As far as I can tell from manually plotting the data, the angle should be about 17.8 degrees {I keep getting angle values like 1 degree, 1.8 degrees and 4 degrees}.I have tried using the functions slope, linest, intercept, degrees(ATAN(slope) but I don't seem to get the correct angle. And I'm not sure whether I need to make the first x and/or y equal zero for the formulae to work. And I know that with these formulae, I am currently not getting the line of best fit to pass through the first point.

----
Overall what I am trying to achieve is to automate drawing a trend line on a stock price chart, I have the following data:

x y
week 75 (date 14/2/1992) 6.81
week 80 (date 20/3/1992) 7.2
week 83 (date 10/4/1992) 6.95
week 88 (date 15/5/1992) 7.52
week 95 (date 3/7/1992) 7.08
week 98 (date 24/7/1992) 7.02

I want to use the 1st point as my reference/anchor point for lines of best fit. (But the reference point can be 1-2% (y-axis) lower than the 1st point, if this creates a better line of fit).
Starting with the first three points, I would calculate a line of best fit going through the first point. If the R value is to far away from 1 (i.e. the points do not line up as close to possible in a straight line) then I would only keep the either the 2nd or third point (whichever is below the line of best fit) and I would retest for a line of best fit for points 1, 3, and 4. This process would repeat until the R value is close to 1.

Up to here would be a great starting point if anyone can help.
Just for argument's sake (if anyone really likes math/vba); the additional rules for this line of best fit would be:
-If there is no acceptable line of best fit found with 3 points, it is possible to draw a line connecting the 1st and second points, and then use the second point as the reference/anchor point - to search from this point forward for 3 points that have a good line of fit (usually used in cases where the future line of best fit/stock prices, have started to move in a steeper angle up the y-axis.
- This acceleration can/should occur for future points, even after an initial 3 point line of best fit is established. The new reference/anchor point in this case would be the 3rd of the initial 3 points.