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

Google Sheets Stock Scripts

Google Sheets comes with an impressive assortment of stock functions in its GOOGLEFINANCE function. These are great. They get you a long way towards building a custom stock tracker / dashboard. There are some shortcomings, however. For example:

• Some functions don't work for certain kinds of securites. =GOOGLEFINANCE("VTI", "expenseratio") does not work. Apparently this function does not work with ETFs such as VTI.
• There is a lot of financial information available that these functions cannot provide. 200 day SMA, insider ownership, etc., etc..

Thankfully Google Sheets comes with the ability to augment these abilities with import functions and custom scripts that pull data from other sources. finviz is a great financial website that contains a wealth of information. A stock lookup on finviz for TSLA shows a table with more than 50 different data points.

The following gist shows how to pull in the 50 day SMA from finviz using built in Google Sheets functions:

Some of the non-obvious key parameters seen in the call are fetch from the 8th table on the page, indexing into row 12 and column 6. The function is a bit large and unwieldy due to the clean ups needed to convert the result into a numeric value provided by the SUBSTITUTE function.

Custom functions written in Google Apps Script provide a much more concise and potentially more flexible way to fetch such information for your sheets. The following functions makes it possible to do the above fetch with a much more concise =FINVIZ("TSLA", 11, 5):