Last Updated on 22/04/2024 by Ndanileka

What is a MACD?

The Moving Average Convergence Divergence (MACD) is a trend-following momentum indicator that shows the correlation between two moving averages and was created by Gerald Appel in the late 1970s. Its purpose is to help traders quickly identify the direction of the trend and its momentum. Since then, it has become one of the most popular technical analysis tools due to its simplicity, versatility, and depth of information.
The MACD consists of three components:

MACD Line:

The MACD line, or Moving Average Convergence Divergence line, is a fundamental component of the MACD indicator. It represents the difference between two exponential moving averages (EMAs), typically a short-term 12-day EMA and a long-term 26-day EMA. The MACD line is calculated by subtracting the long-term EMA from the short-term EMA, providing insights into short-term price momentum relative to the long-term trend.

MACD Line = (Short-term EMA) – (Long-Term EMA) = EMA(close,12) – EMA(close,26)

Signal Line:

The signal line is another essential element of the MACD indicator, designed to smooth out fluctuations in the MACD line and generate trading signals. Typically, the signal line is a 9-day EMA of the MACD line. By applying this exponential moving average to the MACD line, traders can identify potential buy and sell signals. Crossovers between the MACD line and the signal line often indicate shifts in market sentiment and potential trend reversals.

Signal Line = EMA(MACD Line, 9)

Histogram:

The histogram in the MACD indicator serves as a visual representation of the difference between the MACD line and the signal line. It provides valuable insights into changes in momentum and the strength of price movements. When the histogram bars are above the zero line, it indicates that the MACD line is above the signal line, suggesting bullish momentum. Conversely, when the bars are below the zero line, it suggests bearish momentum. Traders often look for divergences between price movements and the histogram to identify potential trend reversals or confirm existing trends.

Historgram = MACD Line – Singal Line

Interpretation

The MACD is typically used to, Indicate buy and sell signals, Show trend direction, and Determine momentum strength. Traders often look for certain patterns, such as crosses above or below the signal line, or divergences between the MACD and price as indications of potential market moves.

• Indicating Buy and Sell Signals: Traders rely on the MACD to identify potential buy and sell signals based on crossovers between the MACD line and the signal line. When the MACD falls below the signal line, it signals a bearish trend, suggesting it may be time to sell. Conversely, when the MACD rises above the signal line, it indicates a bullish trend, suggesting it might be time to buy.

• Showing Trend Direction: By tracking the relationship between the MACD line and the signal line, traders can discern the direction of the prevailing trend in the market.

• Determining Momentum Strength: The MACD assists in assessing the strength of market momentum, with higher MACD values indicating stronger momentum.

Additionally, the MACD provides insights through specific phenomena:

• Divergence: Divergence occurs when the MACD moves in the opposite direction of the underlying price, signaling a potential end to the current trend.

• Dramatic Rise: A dramatic rise in the MACD, where the shorter moving average pulls away from the longer-term moving average, suggests that the security is overbought. This rise indicates that the security price may soon return to normal levels.

• Convergence: Convergence refers to the phenomenon where the short-term and long-term moving averages in the MACD indicator come closer together. This convergence often indicates a period of consolidation or a potential change in trend direction. When the MACD line converges with the signal line, it suggests that momentum in the price action is slowing down, possibly signaling a shift in market sentiment. Traders closely monitor convergence patterns as they may precede significant market moves or reversals.

MACD Custom Function

Data Preparation

We will format the close price data as we did for the EMA function, by removing the header rows.

QUERY(QUERY(GOOGLEFINANCE(G1,"close",TODAY()-250,TODAY()), "SELECT *", 1),"SELECT* OFFSET 1",0)

Function Parameters.

The function is structured to calculate the MACD, utilizing four parameters: range, shortPeriod, longPeriod and signalPeriod.

• range: The “range” parameter represents the date and close price data required for the calculation.

• shortPeriod: This parameter specifies the period of the short-term Exponential Moving Average (EMA)

• longPeriod: This parameter specifies the period of the long-term Exponential Moving Average (EMA).

• signalPeriod: This parameter specifies the period of the signal line EMA.

Apps Script Code

/**
 * Calculates the Moving Average Convergence Divergence (MACD) based on short and long EMAs.
 * 
 * @param {Array} range A range containting date and close price data from Googlefinance(), with no header row.
 * Range example,QUERY(QUERY(GOOGLEFINANCE(B1,"close",TODAY()-((14*6)/5)*7,TODAY()), "SELECT *", 1),"SELECT* OFFSET 1",0)
 * @param {number} The number of periods for the short EMA.
 * @param {number} The number of periods for the long EMA.
 * @return The MACD value.
 * @customfunction
 */

function MACD_GOOGLEFINANCE(range, shortPeriod, longPeriod, signalPeriod) {
  // Calculate short and long EMAs from the provided data range
  const shortEMA = EMA_GOOGLEFINANCE(range, shortPeriod);
  const longEMA = EMA_GOOGLEFINANCE(range, longPeriod);

  // Align the lengths of short and long EMAs
  const [alignedShortEMA, alignedLongEMA] = alignEMALengths(shortEMA, longEMA);

  // Calculate the MACD line
  const macdLine = alignedShortEMA.map((row, i) => {
    const date = new Date(row[0]); // Assuming the date is the first element
    const value = parseFloat(row[1]) - parseFloat(alignedLongEMA[i][1]);
    return [date, value];
  });

  // Calculate the signal line from the MACD line
  const signalLine = EMA_GOOGLEFINANCE(macdLine.map(item => [item[0].toISOString(), item[1]]), signalPeriod);

  // Align the lengths of the MACD line and signal line
  const [alignedMacdLine, alignedSignalLine] = alignEMALengths(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
  const macdData = [['Date', 'MACD Line', 'Signal Line', 'Histogram'], ...histogram];

  return macdData;
}

/**
 * Aligns the lengths of two EMA arrays by trimming the longer array to match the length of the shorter one.
 * Assumes both arrays are structured with date-value pairs and trims based on the starting elements of the longer array.
 * @param {Array} ema1 - The first EMA array.
 * @param {Array} ema2 - The second EMA array.
 * @returns {Array[]} An array containing two aligned EMA arrays.
 */
function alignEMALengths(ema1, ema2) {
  // Assign lengths to variables
  const ema1Length = ema1.length;
  const ema2Length = ema2.length;

  // Calculate the length difference between the two arrays
  const lengthDiff = Math.abs(ema1Length - ema2Length);

  // Determine which array is longer and trim the longer array at the beginning
  if (ema1Length > ema2Length) {
    // If ema1 is longer, slice it to match the length of ema2
    return [ema1.slice(lengthDiff), ema2];
  } else if (ema2Length > ema1Length) {
    // If ema2 is longer, slice it to match the length of ema1
    return [ema1, ema2.slice(lengthDiff)];
  }
  // If lengths are equal, return the arrays as they are
  return [ema1, ema2];
}

How to use

1. In Google Sheets open the “Extensions” menu and select “Apps Scripts”.
2. Paste the EMA_GOOGLEFINANCE code into the script editor and save it as “emaGoogleFinance.gs”.
   The MACD function requires the EMA function to calculate the EMA values.
3. In the same Appscript project in a new file, paste the MACD_GOOGLEFINANCE code to the script editor and save it as “macdGoogleFinance.gs”.
4. Go back to your Google Sheets document.
5. Enter “=MACD_GOOGLEFINANCE()” in a cell to use the MACD function.

Spreadsheet Template

Chart

The MACD chart shows the previous 250 days of data.

Watchlist

The watchlist uses the query function to return the last value for the MACD line, signal line, and histogram for each stock symbol.

Happy Trading!