Least Square Moving Average

I am trying to create a least square moving average of the closing price of a stock. I tried using the TREND function in Excel and it is not giving me the proper results. Instead of using the built in function I would like to do it manually in Excel. I have enclosed the stock price data with what the result of a least square moving average should be. I got these results from my stock charting software, TradeStation. I have looked everywhere for this excel formula but can not find it. It looks like the main formula is Y=m*x+b, but how do you apply this in formula in columns in Excel.

Can you please help me in creating the proper formulas to create this least square moving average. The average would be based on the look back period. In my example I have it set to 34 period look back.

Thanks, Steven

Hi Steven,

Your question is a little confusing. A Least Squares Regression Line is truely a straight line with an equation y=mx+b. The Least Squares part is they try to draw a straight line through your data so the line is as close to all the points as possible. The criteria for "close as possible" is taking each point and finding out how far it is from the line. You do this a few times and realize some distances are positive and some negative. Adding them up doesn't work too well. So they invented Squaring all these distances to make the number always positive and then add them together. The Least part is that you move the line around a little and add all these distances squared until you come up with the smallest Sum of these distances. It is still a line though. The do the same with Log and Power and Polynomial curves. That is sum the squares of the distances and move the curve around to get the miminum sum.

Your question is a little different it wants a moving average with 34 points added together to do the calculations. You curve starts and ends on the first point and this confuses me. It should start on the 34rd point. See my graph attached.

Whoever asked this questio must have wanted you to do a 34 day cumulative average, get a bunch of points and do a LSRL on those points. Does that make sense? See attached. I believe this is what you want.

Thanks for the reply. Let me clarify what I am looking for. A least square moving average is also called an end point moving average where by the end point of a certain length linear regression line is the plot for the least square moving average. For example, If I was looking for a 34 period end point moving average I would look at my current stock price and draw a linear regression line from the current price to the price 34 periods ago. Where the end point of the 34 period linear regress line lands on the current price would by my first value for my end point moving average. When the stock advances to the next price the same time would happen all over again looking back 34 periods to draw a regression line and plot the end point on the current stock price.

I have a link that shows better of what I need. It is:
http://trader.online.pl/MSZ/e-w-End_...g_Average.html

I hope this makes things clearer of what I am looking for.
Thanks, Steven

YES!...Thanks so much. I now see where I made my mistake originally. I did not put the 3rd part of the TREND function in 'A36'.

I now have another problem with a Parabolic Regression (Quadratic Spline) and how to program it into Excel. I have a description of how it works with the equation and a sample output. Should I start a new tread or can I continue with this one? Please let me know.

Thanks, Steven