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

Mr. Snrub

Board Regular
Joined
May 22, 2005
Messages
141
Office Version
  1. 365
Platform
  1. Windows
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?
 

Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.

AhoyNC

Well-known Member
Joined
Oct 10, 2011
Messages
4,862
Office Version
  1. 365
Platform
  1. Windows
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

Well-known Member
Joined
Dec 23, 2017
Messages
2,039
Office Version
  1. 2010
Platform
  1. Windows
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:
VBA Code:
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
 

Forum statistics

Threads
1,144,673
Messages
5,725,701
Members
422,636
Latest member
GatorsBucs

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top