mathjax + gtag

Monday, October 6, 2014

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

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.


5 comments:

  1. Hello Marcelo,

    Can you explain how do you calcualte @param alpha?

    In your examples, we see 0.9 for a window of 20 days. What happens if we use a different window size?

    I have seen in other sites that the muliplier is calculated like that: Multiplicador: (2 / (Períodos de tiempo + 1)) = (2 / (10 + 1)) = 0,1818 (18,18%)

    ReplyDelete
  2. Hi Fernando,

    The alpha parameter can be any number you want. If it is less than one, the average's weight is greater for larger values of the index. If it is greater than one, the opposite. If it is equal to one, you get a weightless arithmetic average.

    There is no explicit rule to calculate the parameter, it will depend upon the desired use of the average.

    Regards,
    Marcelo.

    ReplyDelete
  3. Muchas gracias Marcelo por tu rapida respuesta!

    ReplyDelete
  4. Hi Marcelo, very nice script. I am confused about the following: for the WeightedMovingAverage(weights, values) function, when I change the weights from 20 ones to 10 ones and modify the arrayformula reference value in cell E2=10 intead of 20, the resulting average is not the same? Am I using the function in a wrong way?

    Thank you!

    ReplyDelete