Does the Excel Stock data type have a method of calculating Moving Averages?

[Mr. Snrub]

I am using the Stock Data Type to gather information about my portfolio. Does Excel have a built-in way of returning the n-day simple or exponential moving averages from the Stock Data Type? If not, then in order to calculate these moving averages I would need to use the Excel Stock Data Type to return the closing price on N trading days in the past. Is that possible?

 

[AhoyNC]

If you have a current version of Excel 365 there is a STOCKHISTORY function. For older versions you would probably need to download history from something like YAHOO finance.

 

[offthelip]

One real advantage of using exponential moving average is that to calculate a daily update you only need yesterdays value of the moving average and todays price. So if you down load twice the number of days you need to start with as history to get your exponential moving average started, you can then just use the daily update of price to calculatie your moving average. the function i use to do it is this:

Function EMA(Period, Lasttime, Drive)
If Not (IsNumeric(Period)) Then
  EMA = "This functions calculates the Expontial Moving average, it has three parameters: Period (days), The last time Value of the EMA, the latest value of variable"
Else
    TC = 2 / (Period + 1)
    EMA = Lasttime + TC * (Drive - Lasttime)
End If
End Function