Simple Moving Average in Google Sheets

The simple moving average, SMA, is one of the most basic financial calculations used in stock analysis. Calculating this in Google Sheets is not as straightforward as you might expect. Several web sites, for example sheetaki.com and nextlevel.finance contain instructions on how to calculate this value. These are good, but they work by pulling historical data onto the spreadsheet and then running calculations over these values. It would be preferable to also have a technique to more directly calculate this value. That is, to have a self contained calculation that doesn't need to output the historical data onto the sheet. This allows a current trailing SMA to be directly entered onto your stock analysis sheets.

The following composition of functions does just that (where A2 holds a ticker and $C$17 holds the number of days SMA you desire):

Breaking this down from the inside out:

• The WORKDAY function gets the date $C$17 days ago. Note the use of the named range "holidays" to eliminate days the market was not open.
• The GOOGLEFINANCE function pulls in the data for the closing price in the last $C$17 days the market was open
• The INDEX function reduces the two dimensional array (date, price) returned by the GOOGLEFINANCE function into a one-dimensional array of closing prices.
• The AVERAGE function, well yeah it computes the average of those closing prices.

With this value in hand you can easily compute stats such as the current price's relationship to this value. Many trading strategies, such as the Ivy Portfolio, are concerned with such signals.

The Ivy Portfolio: How to Invest Like the Top Endowments and Avoid Bear Markets