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

=AVERAGE(INDEX(GOOGLEFINANCE(A2 , "close" , WORKDAY(TODAY(), -$C$17, holidays) , TODAY()), 0, 2))

view raw sma hosted with ❤ by GitHub

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:

=VALUE(SUBSTITUTE(SUBSTITUTE(INDEX(IMPORTHTML("http://finviz.com/quote.ashx?t="&A3, "table", 8), 12, 6), "*", ""), "%", ""))

view raw gistfile1.txt hosted with ❤ by GitHub

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

	/**
	* Returns a ticker's numeric data from finviz.com. If the (row, col)
	* contains a "-", 0.0 is returned. Note that this is somewhat equivalent
	* to the following composition of built in sheet functions:
	*
	* =VALUE(SUBSTITUTE(SUBSTITUTE(INDEX(IMPORTHTML("http://finviz.com/quote.ashx?t="&A3, "table", 8), 12, 6), "*", ""), "%", ""))
	*
	* @param {string} ticker - The security's ticker.
	* @param {number} row - The row to return data from.
	* @param {number} col - The column to return data from.
	* @returns {number}
	*/
	const FINVIZ = (ticker, row, col) => {
	const url = `http://finviz.com/quote.ashx?t=${ticker}`;
	let html = UrlFetchApp.fetch(url).getContentText();
	// Pull out just the table html.
	let i = html.search(/<table.*class="snapshot-table2">/);
	html = html.substring(i);
	i = html.search("</table>");
	html = html.substring(0, i + "</table>".length);
	// Clean it up, so that it will parse.
	html = html.replace(/S&P/g, "SnP");
	html = html.replace(/<td[^>]*>/g, "<td>");
	html = html.replace(/<tr[^>]*>/g, "<tr>");
	html = html.replace(/<table[^>]*>/g, "<table>");
	html = html.replace(/<br>/g, "");
	// Finally, parse into XML doc and pull out the (row, col).
	const doc = XmlService.parse(html);
	const table = doc.getRootElement();
	const val = table.getChildren()[row].getChildren()[col].getValue();
	return val === "-" ? 0.0 : parseFloat(val);
	}

view raw finviz.js hosted with ❤ by GitHub

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:

	#!/usr/bin/env bash
	if [[ $# -eq 0 ]]; then
	exec google-chrome &
	exit 0
	elif [[ $1 == "incognito" ]]; then
	exec google-chrome --incognito &
	exit 0
	else
	for preffile in ~/.config/google-chrome/{Default*,Profile*}/Preferences; do
	[[ -e "$preffile" ]] || continue;
	profname=$(jq ".profile.name" "$preffile")
	echo "Found profile $profname in $preffile."
	if [[ "$profname" == "\"$1\"" ]]; then
	shift
	profdir=$(echo "$preffile" | sed -e 's|^.*google-chrome/\([^/]*\)/Preferences$|\1|')
	echo "Going to launch profile $profname with profile directory $profdir."
	exec google-chrome --profile-directory="$profdir" "$@" &
	exit 0
	fi
	done
	echo "Profile with name $1 not found!"
	exit 1
	fi

view raw gchrome hosted with ❤ by GitHub

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:

	#!/usr/bin/env bash
	if [ $# -eq 0 ]; then
	exec google-chrome &
	exit 0
	else
	for preffile in ~/.config/google-chrome/{Default,Profile*}/Preferences; do
	# The profile name appears to always be the last "name:" in the json
	# of the Preferences file. Yes, fragile, but appears to work for now.
	profname=$(grep \"name\" "$preffile" | tail -1 | sed -e 's/.*"\([^"]*\)",\?$/\1/')
	if [ "$1" == "$profname" ]; then
	profdir=$(echo "$preffile" | sed -e 's|^.*google-chrome/\([^/]*\)/Preferences$|\1|')
	echo "Going to launch with profile directory: $profdir"
	exec google-chrome --profile-directory="$profdir" &
	exit 0
	fi
	done
	# didn't find that profile name
	echo "Profile named $1 not found!"
	exit 1
	fi

view raw gchrome hosted with ❤ by GitHub

and next, the bash completion code. Throw this in .bashrc or somewhere:

	function _gchrome_profile() {
	local IFS=$'\n'
	local param=${COMP_WORDS[COMP_CWORD]}
	local profiles=""
	local preffile
	for preffile in ~/.config/google-chrome/{Default,Profile*}/Preferences; do
	# The profile name appears to always be the last "name:" in the json
	# of the Preferences file. Yes, fragile, but appears to work for now.
	local name=$(grep \"name\" "$preffile" | tail -1 | sed -e 's/.*"\([^"]*\)",\?$/\1/')
	profiles="$profiles
	$name"
	done
	COMPREPLY=( $(compgen -W "$profiles" "$param") )
	}
	complete -F _gchrome_profile gchrome

view raw .bashrc hosted with ❤ by GitHub

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:

	UPDATE
	Task t INNER JOIN
	TaskDescriptor td ON t.TaskDescriptorId = td.Id
	SET
	t.StartTime = NOW()
	WHERE
	td.Type = 'nefarious plan' AND
	t.Id % 10 = 0;

view raw gistfile1.sql hosted with ❤ by GitHub

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:

	/*
	array implementation of a binary heap, example usage:
	// can optionally provide a comparison function, a function for a max
	// heap is the default if no comparison function is provided
	var bh = binaryHeap();
	bh.push(5);
	bh.push(34);
	bh.push(16);
	var max = bh.pop(); // 34
	print("number in heap: " + bh.size()) // 2
	*/
	var binaryHeap = function(comp) {
	// default to max heap if comparator not provided
	comp = comp || function(a, b) {
	return a > b;
	};
	var arr = [];
	var swap = function(a, b) {
	var temp = arr[a];
	arr[a] = arr[b];
	arr[b] = temp;
	};
	var bubbleDown = function(pos) {
	var left = 2 * pos + 1;
	var right = left + 1;
	var largest = pos;
	if (left < arr.length && comp(arr[left], arr[largest])) {
	largest = left;
	}
	if (right < arr.length && comp(arr[right], arr[largest])) {
	largest = right;
	}
	if (largest != pos) {
	swap(largest, pos);
	bubbleDown(largest);
	}
	};
	var bubbleUp = function(pos) {
	if (pos <= 0) {
	return;
	}
	var parent = Math.floor((pos - 1) / 2);
	if (comp(arr[pos], arr[parent])) {
	swap(pos, parent);
	bubbleUp(parent);
	}
	};
	var that = {};
	that.pop = function() {
	if (arr.length === 0) {
	throw new Error("pop() called on emtpy binary heap");
	}
	var value = arr[0];
	var last = arr.length - 1;
	arr[0] = arr[last];
	arr.length = last;
	if (last > 0) {
	bubbleDown(0);
	}
	return value;
	};
	that.push = function(value) {
	arr.push(value);
	bubbleUp(arr.length - 1);
	};
	that.size = function() {
	return arr.length;
	};
	return that;
	};

view raw gistfile1.js hosted with ❤ by GitHub

	/*
	tree implementation of a binary heap, example usage:
	// can optionally provide a comparison function, a function for a max
	// heap is the default if no comparison function is provided
	var bh = binaryHeap();
	bh.push(5);
	bh.push(34);
	bh.push(16);
	var max = bh.pop(); // 34
	print("number in heap: " + bh.size()) // 2
	*/
	var binaryHeap = function(comp) {
	// default to max heap if comparator not provided
	comp = comp || function(a, b) {
	return a > b;
	};
	var node = function(value, parent, left, right) {
	var that = {};
	that.value = value;
	that.parent = parent;
	that.left = left;
	that.right = right;
	return that;
	};
	var that = {};
	var root = null;
	var last = null;
	var size = 0;
	var bubbleUp = function(node) {
	if (node === root) {
	return;
	}
	if (comp(node.value, node.parent.value)) {
	var temp = node.parent.value;
	node.parent.value = node.value;
	node.value = temp;
	node = node.parent;
	bubbleUp(node);
	}
	};
	var bubbleDown = function(node) {
	if (!node) {
	return;
	}
	var largest = node;
	if (node.left && comp(node.left.value, largest.value)) {
	largest = node.left;
	}
	if (node.right && comp(node.right.value, largest.value)) {
	largest = node.right;
	}
	if (largest !== node) {
	var temp = node.value;
	node.value = largest.value;
	largest.value = temp;
	bubbleDown(largest);
	}
	};
	that.push = function(value) {
	if (!root) {
	root = last = node(value, null, null, null);
	} else if (root === last) {
	root.left = node(value, root, null, null);
	last = root.left;
	} else if (last.parent.left === last) {
	last.parent.right = node(value, last.parent, null, null);
	last = last.parent.right;
	} else {
	var hops = 0;
	var temp = last;
	while (temp.parent && temp.parent.right === temp) {
	temp = temp.parent;
	hops++;
	}
	if (temp !== root) {
	temp = temp.parent.right;
	hops--;
	}
	while (hops-- > 0) {
	temp = temp.left;
	}
	temp.left = node(value, temp, null, null);
	last = temp.left;
	}
	size++;
	bubbleUp(last);
	};
	that.pop = function() {
	if (size === 0) {
	throw new Error("binary heap empty");
	}
	var value = root.value;
	root.value = last.value;
	if (root === last) {
	root = last = null;
	} else if (last.parent.right === last) {
	last.parent.right = null;
	last = last.parent.left;
	} else {
	var hops = 0;
	var temp = last;
	while (temp.parent && temp.parent.left === temp) {
	temp = temp.parent;
	hops++;
	}
	if (temp !== root) {
	temp = temp.parent.left;
	} else {
	hops--;
	}
	while (hops-- > 0) {
	temp = temp.right;
	}
	last.parent.left = null;
	last = temp;
	}
	size--;
	bubbleDown(root);
	return value;
	};
	that.size = function() {
	return size;
	};
	return that;
	};

view raw gistfile1.js hosted with ❤ by GitHub

Emacs Batch Edit Example

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

	(defun c-indent-file (filename)
	(interactive)
	(find-file filename)
	(mark-whole-buffer)
	(c-indent-line-or-region)
	(save-buffer))

view raw gistfile1.sls hosted with ❤ by GitHub

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

emacs --batch -l '/home/dburger/.emacs' --eval '(c-indent-file "/home/dburger/test.c")'

view raw gistfile1.sh hosted with ❤ by GitHub

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.