Moving Averages Using Google Apps Scripts

Abstract

A moving average (MA) process, also known as weighted moving average (WMA) is a type of signal filtering that consists in performing a weighted average over a finite sequence of past samples of the original signal. Implementing such scheme in a worksheet is not always straightforward due to the handling of missing values. This article proposes an implementation of such filters on Google Sheets using Google App Scripts, a Javascript like language. Exponential Moving Average (EMA) filters are an important special case of WMA, so they have been implemented on top of WMA.

Keywords

Moving Average, MA, Weighted Moving Average, WMA, Exponential Moving Average, EMA, Noise Removal, Filtering, Google Sheets, Spreadsheets, Google App Scripts, Javascript.

Introduction

In previous articles [1][2], implementations of WMA and EMA have been proposed using the normal spreadsheet function infrastructure. These implementations try to be compatible with existing spreadsheet standard functions so that they can be easily ported to other spreadsheet e.g., OpenOffice.

The inconvenient in this solution is that the formulas are large and as a consequence, hard to read and maintain, making it easy to slip subtle errors. Also, the formula must be called once for each line to be calculated, and this process has a big overhead. The ideal solution should make a single call to a function that would return an array of processed data. The following implementation addresses both issues.

Implementation

	/**
	* Performs an exponential moving average of the specified range,
	* handling missing data in a way that does not distort the average.
	*
	* If the spcified window size is less than the range size, an array
	* is returned, so there is no need to call this function serveral
	* times in a row.
	*
	* @param alpha The geometric progression ratio.
	* @param windowSize The size of the moving average window.
	* @param values The range on which we will calculate the average.
	* @return The exponential moving average of the specified range.
	* @customfunction
	*/
	function ExponentialMovingAverage(alpha, windowSize, values) {
	var weights = [];
	for (var i = 0; i < windowSize; i++) {
	var w = Math.pow(alpha, windowSize - 1 - i);
	weights[weights.length] = w;
	}
	var average = WeightedMovingAverage(weights, values);
	return average;
	}
	/**
	* Performs a weighted moving average of the specified range,
	* handling missing data in a way that does not distort the average.
	*
	* @param weights An array with the weights to be applied.
	* @param values The range on which we will calculate the average.
	* @return The weighted moving average of the specified range.
	* @customfunction
	*/
	function WeightedMovingAverage(weights, values) {
	// If values is a scalar or weights is a scalar or
	// weights has length 1, return values.
	if (!values.map || !weights.map || weights.length === 1) {
	return values;
	}
	var windowSize = weights.length;
	var rows = values.length;
	if (windowSize > rows) {
	weights.splice(0, windowSize - rows);
	windowSize = weights.length;
	Logger.log("WARNING: windowSize greater than number of rows" +
	" in the range, truncating the weitghts to " +
	"the number of rows and using the last weights.");
	}
	/* If this function is called from the spreadsheet with a range,
	* weights will be array of columns, lets remove this extra
	* indirection. */
	if (weights[0].map) {
	for (var i = 0; i < weights.length; i++) {
	weights[i] = weights[i][0];
	}
	}
	var average = [];
	for (var i = 0; i < rows - windowSize + 1; i++) {
	var sumValues = 0;
	var sumWeigths = 0;
	for (var j = 0; j < windowSize; j++) {
	var x = values[i + j][0];
	if (typeof x === "number") {
	var w = weights[j];
	if (typeof w === "number") {
	sumWeigths += w;
	sumValues += w * x;
	}
	}
	}
	var avg = sumValues / sumWeigths;
	average[average.length] = avg;
	}
	return average;
	}

view raw wma-google-apps.js hosted with ❤ by GitHub

Results

The resulting spreadsheet shows a comparison of the previously posted methods with this scripted based one.

Conclusion

Two javascript functions have been developed to implement the missing data weighted moving averages of previous articles [1][2]. The results have been shown to be identical. The javascript based method has the advantage of being much cleaner to maintain, typically requiring a single cell on the spreadsheet.