Moving Average Convergence Divergence (MACD)

/**

 * Calculates the Moving Average Convergence Divergence (MACD) for a given dataset and periods.

 *

 * @param {array} data - an array where the first column is the  dates, and subsequent columns for  open, high, low, close and volume.

 * @param {number} shortPeriod The number of periods for the short EMA, e.g., 12 for a 12-day EMA.

 * @param {number} longPeriod The number of periods for the long EMA. e.g., 26 for a 26-day EMA.

 * @param {number} signalPeriod The number of periods for the signal line EMA. e.g., 9 for a 9-day EMA.

 * @returns {Array} The MACD values along with corresponding dates.

 * @customfunction

 */

  

function MACD(data, shortPeriod, longPeriod, signalPeriod) {

  

  //Check number of arguments

  if (arguments.length !== MACD.length) {

    throw new Error(`Wrong number of arguments. Expected ${MACD.length} arguments, but got ${arguments.length} arguments.`);

  }

  

  const processedData = getData(data);

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

  

  // Calculate short and long EMAs from the processed data

  const shortEMA = EMA(data, shortPeriod).slice(1); // Remove headers

  const longEMA = EMA(data, longPeriod).slice(1); // Remove headers

  

  // Align the lengths of short and long EMAs

  const [alignedShortEMA, alignedLongEMA] = alignLENGTHS(shortEMA, longEMA);

  

  // Calculate the MACD line

  const macdLine = alignedShortEMA.map((row, i) => {

    const date = row[0];

    const value = row[1] - alignedLongEMA[i][1];

    return [date, value];

  });

  

  // Calculate the signal line from the MACD line

  const signalLine = EMA(macdLine.map(item => [item[0], item[1]]), signalPeriod).slice(1); // Remove headers

  

  // Align the lengths of the MACD line and signal line

  const [alignedMacdLine, alignedSignalLine] = alignLENGTHS(macdLine, signalLine);

  

  // Calculate the histogram

  const histogram = alignedMacdLine.map((item, i) => {

    return [item[0], item[1], alignedSignalLine[i][1], item[1] - alignedSignalLine[i][1]];

  });

  

  // Prepare the final MACD data structure

  return [["Date", `MACD Line (${shortPeriod}-${longPeriod})`, `Signal Line (${signalPeriod})`, "Histogram"], ...histogram];

  

}

  

/**

 *

 * @param {Array<Array>} data - A 2D array of cell values to process. The first row should contain headers.

 * @returns {Array<Array>} - A 2D array where the first row contains headers and subsequent rows contain processed data.

 *

 * @customfunction

 *

 */

  

function getData(data) {

  if (!Array.isArray(data) || data.length === 0) {

    throw new Error("Input data must be a non-empty array.");

  }

  
  

  // Check if the first row contains headers

  const headers = Array.isArray(data[0]) && data[0].every(item => typeof item === "string")

    ? data[0]

    : null;

  

  if (!headers) {

    // If no headers, assume the first column is the date column and process data

    return data.map(row => row.map((value, index) => {

      return index === 0 ? new Date(value) : parseFloat(value);

    }));

  }

  

  // Process rows with headers

  return [headers, ...data.slice(1).map(row => headers.map((header, index) => {

    const key = header.toLowerCase();

    return key === "date" ? new Date(row[index]) : parseFloat(row[index]) || row[index];

  }))];

}

  
  

/**

 * Calculates the Exponential Moving Average (EMA) for a given dataset and period

 *

 * @param {array} data - an array where the first column is the  dates, and subsequent columns for  open, high, low, close and volume.  

 * @param {number} period The number of periods to calculate the EMA, e.g., 14 for a 14-day EMA.

 * @returns {array} the EMA values along with corresponding dates.

 * @customfunction

 */

  

function EMA(data, period) {

  

  //Check number of arguments

  if (arguments.length !== EMA.length) {

    throw new Error(`Wrong number of arguments. Expected ${EMA.length} arguments, but got ${arguments.length} arguments.`);

  }

  

  const processedData = getData(data);

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

  const closePrices = processedData[0].length === 2

    ? processedData.slice(1).map(row => row[1]) // For 2-column arrays, use the second column

    : processedData.slice(1).map(row => row[4]); // Default: use index 4 for close price

  

  // Initialize an array to store EMA values with corresponding dates

  let results = [["Date", `EMA(${period})`]]; // Include headers

  

  // Calculate the multiplier for EMA calculation

  const multiplier = 2 / (period + 1);

  

  // Calculate SMA for the first n data points

  let sum = 0;

  for (let i = 0; i < period; i++) {

    sum += closePrices[i];

  }

  let sma = sum / period;

  

  // The first EMA will have the date corresponding to the nth entry (0-indexed, hence n-1)

  results.push([dates[period - 1], sma]);

  

  // Calculate EMA for the remaining data points

  for (let i = period; i < closePrices.length; i++) {

    const currentClose = closePrices[i];

    const prevEMA = results[results.length - 1][1];

    const currentEMA = (currentClose - prevEMA) * multiplier + prevEMA;

  

    // Append the current date and EMA to the results array

    results.push([dates[i], currentEMA]);

  }

  

  return results;

}

  
  

/**

 * Aligns the lengths of two arrays by trimming the longer array to match the shorter one.

 *

 * @param {Array} arr1 - The first array.

 * @param {Array} arr2 - The second array.

 * @returns {Array} - An array containing two aligned arrays.

 */

  

function alignLENGTHS(arr1, arr2) {

  const lengthDiff = arr1.length - arr2.length;

  if (lengthDiff > 0) {

    return [arr1.slice(lengthDiff), arr2];

  } else if (lengthDiff < 0) {

    return [arr1, arr2.slice(-lengthDiff)];

  }

  return [arr1, arr2];

}