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

Launch Chrome by Profile Name (Updated)

Now for my once a decade update... Chrome eliminated white space from the JSON format used to store profile preferences. This broke the simple file parsing of the previous script. This update switches to the jq command-line JSON parser to fix the parsing. Enjoy: Send peer bonus, please :-)

Launch Chrome by Profile Name

Chrome allows you to launch it under a given profile if you give it an argument of --profile-directory="Directory Name". The directory names are "Default", "Profile 1", "Profile 2", etc.. Obviously this is not super convenient as you need to know the profile directory that goes with a given profile name. Here is a script that allows you to launch Chrome by profile name instead, followed by some bash code to allow bash auto-completion of profile names. First the gchrome script: and next, the bash completion code. Throw this in .bashrc or somewhere: Note that this relies on a certain layout of the Preferences file json which I have found to hold so far, however, it wouldn't be too surprising to find json that breaks it. This should work for Chrome on most Linux installs. This could probably be adjusted to work on OS X but the config file is located elsewhere. Enjoy.

Use MySQL's Modulo Operator to Update in Batches

Sometimes you need to roll out your nefarious plan slowly and MySQL's modulo operator can help.  Here we do the first of ten batches:

Javascript Binary Heap

Saturday morning javascript exercise - a binary heap in array and tree implementations. The tree implementation is faster as expected when you shove a lot of data as it as the array implementation must occasionally re-size the backing array. A demo or visualization would be nice but that takes more time than this Saturday morning allows. Code:

Emacs Batch Edit Example

Say the following function is in your .emacs file and lets you indent your C source code to your liking:



But you don't want to manually open each file into an Emacs buffer to execute this script. Emacs batch editing to the rescue:



The parameters load up my .emacs file as normal and runs the given eval. Apply a little command line magic and you should be able to exploit Emacs from the command line in a very efficient way.