GoogleFinance function to calculate Exponential moving average in google sheets

Question

I am using google sheets and GoogleFinance functions to get stock data. I am able to calculate a simple moving average with the below formula. I am trying to get an Exponential moving average for lengths 8,13,21,55 for each stock. Any suggestion on the formula for an Exponential moving average

=AVERAGE(INDEX(GoogleFinance("MSFT","all",WORKDAY(TODAY(),-8),TODAY()),,3))

Edit: Adding my google sheet experince

Answer 1

Giving back some improvements to Jonathan K's work.

• improve accuracy for short inputs (range.length = 1 or 2)

• bounds check n

• specify expected order of input array

• give 1 alternative definition for a

• add a test function

  /**
   * Calculates the EMA of the range.
   *
   * @param {range} range The range of cells to calculate. New values (higher weight) should be on the left.
   * @param {number} n The number of trailing samples to give higher weight to, e.g. 30.
   *                   Values below 1 are treated as 1, which will just return the most recent value.
   * @return The EMA of the range.
   * @customfunction
   */
  function EMA(range, n) {
    if (!range.reduce) {
      // can't run ema - bail
      return range;
    } else if (range.flat && range.reverse) {
      // flatten the array if possible & reorganize w/ new values on right (last)
      // NOTE: remove .reverse if the rightmost elements of input range are most recent / need higher weight
      range = range.flat().reverse();
    }
  
    n = Math.max(n,1); // n has lower bound of 1
    var a = 1-(2/(n+1));
    // with the existing a setup, 86% of the total EMA weight will belong to the first n values.
    // an alternate definition for a where n would be the desired number of intervals to keep (𝜏)
    // ex: n = 4 -> quarterly values would get a "mean lifetime" of 1 year.
    // var a = 1-Math.exp(-1 / n);
  
    return range.reduce(function(accumulator, currentValue, index, array) {
      if (currentValue != "") {
        if (accumulator == null) {
          return currentValue;
        } else {
          return currentValue * a + accumulator * (1-a);
        }
      } else {
        return accumulator;
      }
    }, null);
  }
  
  function test() {
    var emaValue = EMA([[13,14],[15,16]], 4);
    Logger.log(emaValue);
  }
  

Answer 2

The simplest way is, given a column X2:X... and the weight coefficient a in the cell Z1, the exponential moving average can be calculated in the rows Y2:Y... recursively as follows:

EMA
=X2
=$Z$1*X3+(1-$Z$1)*(Y2)
=$Z$1*X4+(1-$Z$1)*(Y3)
=$Z$1*X5+(1-$Z$1)*(Y4)
=... # the pattern will repeat itself properly using the drag function 

The recursive formula is described here: https://en.wikipedia.org/wiki/Moving_average#Exponential_moving_average

Of course, this only works, if the interval between the data points is always the same.

Answer 3

The above formula is technically not the same as an exponential moving average. The exponential decay function is as follows. https://en.wikipedia.org/wiki/Exponential_decay

It is usually used to model decaying influence over time that more heavily weights closer entries (as above). That being said, the above formula is not the same.

The actual form of the EWMA/EMA is somewhat different and can be found here: https://en.wikipedia.org/wiki/Moving_average#Exponential_moving_average

The above might be considered a variant of exponential smoothing, but it isn't really even that: https://en.wikipedia.org/wiki/Exponential_smoothing

Personally, I prefer weighting my averages using the e^-ax function. I can control the "a" parameter for amount of weighting of earlier and later items for better adjustment of weighting.

currentValueComponent_i=currentValue * e^-time_i/Tau Add as many currentValueComponents for however many items you want. Tau is the time constant for 63% decay of the elements that would be added to each iteration depending on the number of items included in your weighted average.

Source: I'm an engineer. I do this on a regular basis.

Answer 4

the following formula is used to calculate the current Exponential Moving Average (EMA):

EMA = Closing price x decay_multiplayer + EMA (previous day) x (1-decay_multiplayer)

The EMA gives a higher weight to recent prices, while the regular moving average assigns equal weight to all values.

The decay_multiplayer should be chosen bigger than 0 and smaller than 1.

if you select a bigger number that is like short term moving average (faster decay) and if you select a smaller number that is like long term moving average.

to implement this in google sheet you have to create a new column that represents EMV value for every day. you have to fill the first EMV value (with first close price) and then use the function above to calculate every new EMV from the current closing price and previous EMV values.

I have created a google sheet here as an example: https://docs.google.com/spreadsheets/d/1ITfRfwQCBV-0amWpZzae2PbKBPAL_8YluUEmU_vbsCI/edit?usp=sharing

Answer 5

I have found this with credit to this user "Jonathan K 2806".

try this instead, simpler and probably enough:

​​​​/**
 * Calculates the EMA of the range.
 *
 * @param {range} range The range of cells to calculate.
 * @param {number} n The number of trailing samples to give higer weight to, e.g. 30.
 * @return The EMA of the range.
 * @customfunction
 */
function EMA(range, n) {
  if (!range.reduce) {
    return range;
  }

  n = Math.min(n, range.length);
  var a = 2 / (n + 1);

  return range.reduce(function(accumulator, currentValue, index, array) {
    return currentValue != "" ? (currentValue * a + accumulator * (1-a)) : accumulator;
  }, 0);
}

​go to tools -> script editor, paste this there and hit save than go back to your spreadsheet and type into a cell =EMA($A2:$A100, 10)​ or however you want to use it.