Moving Average Custom Function for Google Spreadsheets

Moving Averages can be calculated in Google Spreadsheets using standard functions, however the formulas can get pretty complex when dealing with different durations, and it also requires the formula to be included in each field. The first time I tried to solve this by writing a custom function that would calculate the moving average per cell, however I quickly ran into an error stating that I was running too many scripts per second for Google’s liking.

I am working with rows of time series data, so I really want to add a new column with a moving average in it. It would also be nice to show the average of the available data if the full set isn’t available for convenience. To accomplish both of these I created a function that can be placed in the header and returns an array of results, including the header itself, of a moving average of the value from another column.

Custom function via Google Script

Open Google Scripts by selecting Tools  > Script editor... to open the browser based IDE. A sample file will open by default, you can look it over or just remove all of the contents. Once the file is cleared, enter the following JavaScript to define a custom function named MOVING_AVERAGE.

function MOVING_AVERAGE(column, days, dateColumn){
	// defaults
	days = ( typeof days == 'undefined' )? 30 : days;
	dateColumn = ( typeof dateColumn == 'undefined' )? 'A' : dateColumn;
	var now = (new Date()).getTime();
	
	var sheet = SpreadsheetApp.getActiveSpreadsheet();
	var lastRow = SpreadsheetApp.getActiveSheet().getMaxRows()-1;
	var dates = sheet.getRange(dateColumn+"2:"+dateColumn).getValues();
	var values = sheet.getRange(column+"2:"+column).getValues();
	for (; values[lastRow - 1] == "" && lastRow > 0; lastRow--) {}
	var title = sheet.getRange(column+"1").getValue();
	var results = [ days + " days " + title ];
	for ( var rowIndex = 0; rowIndex < lastRow; rowIndex++ ){
		// we are after "today" 
		var inTheFuture = (now+3600 <= (new Date(dates[rowIndex])).getTime());
		
		if (inTheFuture){
			results.push(0);
		} else {			
			var lookback = ( rowIndex < days )? rowIndex : days-1;
			var startRow = rowIndex - lookback;
			
			var total = 0;
			for ( var i=startRow; i<=rowIndex; i++ ){
				total += parseFloat( values[i] );
			}
			
			results.push(parseFloat(total/((rowIndex-startRow)+1)));
		}
	}
	
	return results;
}

Use the custom function in your spreadsheet

You can now use this function by adding the following into the formula field of a column header. Keep in mind that this won’t update on demand so you will need to remove/re-add the formula to recalculate.

=MOVING_AVERAGE( "COLUMN", DAYS_INT )

Or if your date column isn’t “A”, then you can specify it like so.

=MOVING_AVERAGE( "COLUMN_DATA", DAYS_INT, "COLUMN_DATE" )

 

You may also like...

3 Responses

  1. Jesse Vonk says:

    Hey Justin,
    I’m currently using your function for a stat arb model and i really like the convenience and simplicity of it. I noticed however that if your dataset is organized from most recent date in the first row to latest date in the last – like my data set is – the MA suffers from lookahead bias. I made a few changes and thought I would share it for anyone with the similarly ordered dataset as I have.

    Much thanks for your work!

    Code:

    function MOVING_AVERAGE(column, days, dateColumn){
      // defaults
      days = ( typeof days == 'undefined' )? 30 : days;
      dateColumn = ( typeof dateColumn == 'undefined' )? 'A' : dateColumn;
      column = ( typeof column == 'undefined' )? 'B' : column;
      var now = (new Date()).getTime();
       
      var sheet = SpreadsheetApp.getActiveSpreadsheet();
      var lastRow = SpreadsheetApp.getActiveSheet().getMaxRows()-1;
      var dates = sheet.getRange(dateColumn+"2:"+dateColumn).getValues();
      var values = sheet.getRange(column+"2:"+column).getValues();
      for (; values[lastRow - 1] == "" &amp;&amp; lastRow &gt; 0; lastRow--) {}
      var title = sheet.getRange(column+"1").getValue();
      var results = [ days + " period MA " + title ];
      for ( var rowIndex = 0; rowIndex &lt; lastRow; rowIndex++ ){
        // we are after &quot;today&quot; 
        var inTheFuture = (now+3600 &lt;= (new Date(dates[rowIndex])).getTime());
         
        if (inTheFuture){
          results.push(0);
        } else { 
          var altLookback = lastRow - rowIndex - 1;
          var lookback = ( altLookback &lt; days )? altLookback : days-1;
          var startRow = rowIndex;
          var endRow = rowIndex + lookback;
           
          var total = 0;
          for ( var i=startRow; i&lt;=endRow; i++ ){
            total += parseFloat( values[i] );
          }
           
          results.push(parseFloat(total/((endRow - startRow)+1)));
        }
      }
       
      return results;
    }
    
  2. Sunil Y says:

    hi Justin,
    I tried to use your code to get moving average of a stock. But am not able to make it work. I am not a software engineer and don’t know any programming languages. Could you please help me on how to use your code to get the moving average of a stock for past so many days (where I can change the number of days). Your help would be very much appreciated.

    • Yuriy says:

      Hi couldn’t get this to work for a while (I kept passing it a Range of cells) the author does not give any practical examples…

      A . | B . | C
      ———————————————————–
      Timestamp . Series1 . Series2
      2015-05-18 . 2 4
      2015-05-25 . 3 1
      2015-06-01 . 4 4
      2015-06-08 . 2 5

      In output cell use Function =MOVING_AVERAGE(“B”, 2, “A”)

      The parameters are 1st (“B”) is a character representing the column to pick if you look at the code he skips cell B1 and starts from B2,

      Parameter 2 is the window size of the moving average in our case it’s 2

      Parameter 3 is the column representing the dates in our case its column A, passed as “A” in the function.

Leave a Reply

Your email address will not be published. Required fields are marked *