Exponential Moving Average (EMA)

The Exponential Moving Average (EMA) is a type of moving average that places greater weight and significance on the most recent data points. Like all moving averages, its purpose is to smooth price data to help identify the direction of the underlying trend. Because of its weighting, the EMA reacts more quickly to recent price changes than a Simple Moving Average (SMA), making it a popular tool for traders to confirm trend direction and gauge short-term momentum.

EMA

=EMA(data, period)

Example Usage

=EMA(A2:F500, 20)

Parameters

Parameter Type Description Status
data
Range
Range of columns containing the date, Open, high, Low, close, volume data.
 Required
period
Number
Number of (periods) days over which to calculate the EMA.
 Required

Returns

A two-column array of dates and their corresponding EMA values.

EMA Formula Result in Google Sheets

Source Code

Copy the following code into your Apps Script editor (Extensions > Apps Script) to use the EMA function in your spreadsheet.

ema.js 
/**
 * Calculates the Exponential Moving Average (EMA) for a given dataset and period.
 *
 * @param {array} data - An array of historical stock data. Can be a single column of prices or a multi-column array from GOOGLEFINANCE.
 * @param {number} period The number of periods to calculate the EMA, e.g., 14 for a 14-day EMA.
 * @returns {array} A 2D array with headers: Date and EMA.
 * @customfunction
 */
function EMA(data, period) {
  // Argument validation
  if (arguments.length !== 2) {
    throw new Error(`Wrong number of arguments. Expected 2, but got ${arguments.length}.`);
  }
  if (typeof period !== 'number' || period <= 0 || !Number.isInteger(period)) {
    throw new Error(`Invalid period. The period must be a positive integer. Got: ${period}`);
  }

  const processedData = getData(data);

  // --- NEW: Function-level validation ---
  const columnCount = processedData[0].length;
  if (columnCount > 2 && columnCount < 5) {
      throw new Error(`Invalid data structure for EMA. For multi-column data, expected at least 5 columns (Date, O, H, L, C), but got ${columnCount}. For simple data, expected 2 columns (Date, Value).`);
  }
  // --- END of validation ---

  const dates = processedData.slice(1).map(row => row[0]);
  const values = getValues(processedData);

  if (period > values.length) {
    throw new Error(`Invalid period. The period (${period}) cannot be greater than the number of data points (${values.length}).`);
  }

  let results = [["Date", `EMA(${period})`]];
  const multiplier = 2 / (period + 1);

  // Calculate SMA for the first period as the initial EMA
  let sum = 0;
  for (let i = 0; i < period; i++) {
    sum += values[i];
  }
  let initialEMA = sum / period;
  results.push([dates[period - 1], initialEMA]);

  // Calculate EMA for the rest of the data
  for (let i = period; i < values.length; i++) {
    const currentValue = values[i];
    const prevEMA = results[results.length - 1][1];
    const currentEMA = (currentValue - prevEMA) * multiplier + prevEMA;
    results.push([dates[i], currentEMA]);
  }

  return results;
}