Simple Moving Average Using STOCKHISTORY

[Data123]

Hi is there a way to create a simple moving average (20 days) for stocks using stockhistory, workday and today in the formula? Thanks!

 

[AhoyNC]

See if this works for you.
I used the starting date in the STOCKHISTORY as TODAY - 40 days. This gets the last 20 days plus sum extra days of closing prices.
I then used the TAKE function to get the most current 20 days and then took the AVERAGE.

Book1
	A	B
1	Stock	Average last 20 days
2	MSFT	409.58
Sheet2

Cell Formulas
Range	Formula
B2	B2	=AVERAGE(TAKE(STOCKHISTORY(A2,TODAY()-40,TODAY(),0,,1),-20))

 

[Scott Huish]

Didn't know about this function. This is very helpful. Thanks for asking it!

 

[Data123]

See if this works for you.
I used the starting date in the STOCKHISTORY as TODAY - 40 days. This gets the last 20 days plus sum extra days of closing prices.
I then used the TAKE function to get the most current 20 days and then took the AVERAGE.

Book1
	A	B
1	Stock	Average last 20 days
2	MSFT	409.58
Sheet2

Cell Formulas
Range	Formula
B2	B2	=AVERAGE(TAKE(STOCKHISTORY(A2,TODAY()-40,TODAY(),0,,1),-20))

Thanks AhoyNC, that should work!

 

[AhoyNC]

You're welcome. Thanks for the feedback.