CODEWITHSUNDEEP
javascriptgoogle-apps-scriptgoogle-sheetsexcel-formulagoogle-sheets-formula
EagleEye
EagleEye

Reputation: 510

How to Calculate Exponential Moving Average (EMA) of last 200 days in Google sheets?

I am trying to calculate the Exponential Moving Average (EMA) of stock. I found this formula:

=AVERAGE(INDEX(GoogleFinance("GOOG","all",WORKDAY(TODAY(),-200),TODAY()),,3))

But it is a function just to calculate Simple Moving Average. I researched more and found a script from this thread that was supposed to be the solution:

​​​​

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

But when I run it, it is no way near the actual value. Kindly can you guide me on how to calculate accurate EMA of stock after 200 days with a script or formula without iterating each previous day's value or as a next option can we calculate EMA from the Simple Moving Average of the last 200 days?

Here is the link to Sample Data. In Column U, I am calculating EMA step by step, while Column B is the Simple Moving Average of 'GOOG' stock, can we get EMA using the data of Column B by creating some formula or script?

Upvotes: -1

Views: 2442

Answers (1)

Iamblichus
Iamblichus

Reputation: 19339

Issue:

I think the referenced formula is not handling the first element in the series properly. The value of the EMA at time period 0 matches the value of the series itself. And since all the rest of the values depend on that one, the end result is wrong (ref):

enter image description here

Solution:

Therefore, the reduce function should take the first value in the series as the initial value:

function EMA(range, n) {
  if (!range.reduce) {
    return range;
  }
  n = Math.min(n, range.length);
  const a = 2 / (n + 1);
  const first = range.shift();
  return range.reduce((accumulator, currentValue) => {
    return currentValue != "" ? (currentValue * a + accumulator * (1-a)) : accumulator;
  }, first);
}

enter image description here

Upvotes: 1

Related Questions