Weighted Moving Averages Using Google Apps Script

wma-google-apps.js

/**
 * 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;
}

Can you explain how do you calculate the @param alpha The geometric progression ratio.?

Thanks in advance!

Could you please provide a demo link to Google sheets?
I am not clear about arguments to this function...

The original article is here:
https://eeandcs.blogspot.com/2014/10/moving-averages-using-google-apps.html

The link to the spreadsheet is here:
https://docs.google.com/spreadsheets/d/17VDGSihdRVeNGB8jQGw0iOhyVIQ5vMcFEFeM3v0fd98/edit?usp=sharing

Hi, have you thought about a moving median alternative for your script? this would be a nice option to clean outliers/spikes.

Hi fnorrmann,

Yes, but that you can do with AVERAGE or AVERAGE.WEIGHTED. The purpose of the script is to provide a functionality that google sheets does not have native.

Regards!

Hi Marcelo, your script is fantastic, great implementation. The reason I like to use a median instead of a mean, is that it is better for filtering out spikes in noisy data series. The good thing is that your script can be adapted to add another function called "Median", I guess. I suppose that adapting the math algorithm to add the median function should not be too difficult (for a non coder that understands a bit of Javascript!)? Cheers, thank you and nice work. *Frank Norrmann* *Norcotek* Phone +(5622) 745-1820 │Mobile +(5699) 225-0041 <+(5699)+225-0041> frank@norcotek.cl │ www.norcotek.cl │ www.isensor.cl El mar., 5 de may. de 2020 a la(s) 09:11, Marcelo Roberto Jimenez ( notifications@github.com) escribió:

…

***@***.**** commented on this gist. ------------------------------ Hi fnorrmann, Yes, but that you can do with AVERAGE or AVERAGE.WEIGHTED. The purpose of the script is to provide a functionality that google sheets does not have native. Regards! — You are receiving this because you commented. Reply to this email directly, view it on GitHub <https://gist.github.com/bceea54fdf66a9aec7b2#gistcomment-3290600>, or unsubscribe <https://github.com/notifications/unsubscribe-auth/ABZ4OWUQ64HG7SUZHL3A4I3RQAGBVANCNFSM4HYRVINQ> .

Hi Frank,

Thank you for your kind comment.

In fact, I misread median for mean. My apologies. Now I understand your point.

I have never tried doing median smoothing before. And it would certainly be easy to adapt the code above to do it.

Regards,
Marcelo.

Great! From a theoretical perspective, can you point me in the right direction for the median code in the script? I am happy to test it out and send you the finished code so you can incorporate into your Git repo. I am not a coder but I can do some Javascript in Sheets, so I am willing to give it a try if I get an idea of how to handle the median logic in javascript. thank you! *Frank Norrmann* *Norcotek* Phone +(5622) 745-1820 │Mobile +(5699) 225-0041 <+(5699)+225-0041> frank@norcotek.cl │ www.norcotek.cl │ www.isensor.cl El mar., 5 de may. de 2020 a la(s) 09:41, Frank Norrmann (frank@norcotek.cl) escribió:

…

Hi Marcelo, your script is fantastic, great implementation. The reason I like to use a median instead of a mean, is that it is better for filtering out spikes in noisy data series. The good thing is that your script can be adapted to add another function called "Median", I guess. I suppose that adapting the math algorithm to add the median function should not be too difficult (for a non coder that understands a bit of Javascript!)? Cheers, thank you and nice work. *Frank Norrmann* *Norcotek* Phone +(5622) 745-1820 │Mobile +(5699) 225-0041 <+(5699)+225-0041> ***@***.*** │ www.norcotek.cl │ www.isensor.cl El mar., 5 de may. de 2020 a la(s) 09:11, Marcelo Roberto Jimenez ( ***@***.***) escribió: > ***@***.**** commented on this gist. > ------------------------------ > > Hi fnorrmann, > > Yes, but that you can do with AVERAGE or AVERAGE.WEIGHTED. The purpose of > the script is to provide a functionality that google sheets does not have > native. > > Regards! > > — > You are receiving this because you commented. > Reply to this email directly, view it on GitHub > <https://gist.github.com/bceea54fdf66a9aec7b2#gistcomment-3290600>, or > unsubscribe > <https://github.com/notifications/unsubscribe-auth/ABZ4OWUQ64HG7SUZHL3A4I3RQAGBVANCNFSM4HYRVINQ> > . >

Hi Frank,

You can probably do it inspired on the code for WeightedMovingAverage(), but you will throw away the part that deals with weights. Also the way you calculate the variable "average" should be changed to a median calculation on the same window.

From the wikipedia: "Formally, a median of a population is any value such that at most half of the population is less than the proposed median and at most half is greater than the proposed median." https://en.wikipedia.org/wiki/Median.

I don't want to spoil your fun, so I will only say that it is possible to calculate the median in O(n).

Regards,
Marcelo.

Hi!

I would like use your WMA for stocks but I don't have clear how to use it because I don't have the "weights column", I would like calculate a WMA of 30 periods, like other trading platform, so I have a list of prices of one stock.

Could you explain me how I can use it?

WeightedMovingAverage(weights, values)

I don't understand how I can define the windows size to 30 periods... or how to call the function

04/05/2014 | 69,8
05/05/2014 | 70,3
06/05/2014 | 69,8
07/05/2014 | 69,4
....

Thank you very much

Hi davidoceans,

In this article I tried to explain how it works and I have shared a link to a worksheet that uses that function. Try to replicate it, there should be no problems.

Basically what you need to do is go to Tools->Script Editor and put the code in there. Here is a screenshot of how to call it:

Regards,
Marcelo.

I've copied the sheet (you can edit mine) https://docs.google.com/spreadsheets/d/1M9sBXD8NQtixpCLLOAIsK-Enc9V5uVT1Rea9HRm5Z6E/edit?usp=sharing

How is weighted supposed to work? I have to put as many 1 as the period I want? 6 times 1 is for period 6?

Also, I don't understand the blank spaces in those columns. Maybe I have not ordered correctly or I do not know, but it is not easy for me to understand

I only want know how calculate a WMA of 30 periods of this series of prices.

Thank you for your time
David

Hi David,

Nevermind the empty spaces. The function operates with two ranges, the weights and the values. You want the values range to be larger than the weights range. The resulting series will be shown at and below the cell where you have placed the formula.

The result is a weighted sum of the values, as I have explained here: https://eeandcs.blogspot.com/2014/09/weighted-moving-averages-on-google.html

For stock prices, you typically want to use an exponential moving average, which is much simpler to implement, as I showed in this previous post: https://eeandcs.blogspot.com/2014/09/exponential-moving-averages-on-google.html

For an average with constant weights, the spreadsheet formula =AVERAGE() already works fine as a moving average.

Regards,
Marcelo.