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):
