Calculate MACD in Excel

      7 Comments on Calculate MACD in Excel

Moving Average Convergence Divergence (MACD)

MACD is a popular trend-following momentum indicator. The MACD turns two trend-following indicators into a momentum oscillator by subtracting the longer moving average from the shorter moving average. MACD is calculated by taking difference between 12 day Exponential Moving Average (EMA) and 26 day EMA. A positive MACD means the 12-period EMA is above the 26-period EMA. This post guides you to calculate MACD in Excel.

A trigger for a buy or sell signal occurs when 9 day EMA called the “signal line” is plotted over MACD. Traders typically use the MACD as a simple crossover. When a MACD crosses the signal line, traders tend to buy or sell based on which way the crossover appears. When MACD turns up and crosses over the signal line, bullish crossover occurs. Bearish crossover occurs when MACD turns down below the signal line. The difference between those two values can be plotted through a Histogram.

MACD Steps

MACD line = 12 day EMA Minus 26 day EMA

Signal line = 9 day EMA of MACD line

MACD Histogram = MACD line Minus Signal line

The following are the steps to calculate the MACD of Macy’s. We will explore it by using Excel VBA so you have all the necessary tools to get started. The spreadsheet with the accompanied VBA is available for download.

1) Get the historical closing stock prices. The accompanied VBA in the spreadsheet does it for you. Simply key in the Stock Symbol, Begin and End dates. In this demo, M was ticker for Macy’s, Start Date as Jul-01-2014, and End Date as Sep-30-2104.

2) EMA of the closing stock prices

There are two common setups for the MACD. The first is based on calculations using three-time periods : a 12-day, 26-day, and a 9-day time frames. The second is based on calculations using three different time frames: 8-day, 17-day, and a 9-day time frames. The time frames are basically trailing day averages. The MACD for the longer time frame is less volatile as compared to the MACD for shorter time frames. In this demo,  MACD is calculated through 12-26-9 trailing day averages.

Calculate MACD in Excel

The equation for calculating a trailing 12-day average is

Calculate MACD in Excel

where the Period is 12. From the Excel screen grab, columns A and B contain date and closing stock prices. Column C contains 12 day EMA. Cell C13 contains trailing 12-day average. Cells C17 onward contains EMA values based on the above equation. Notice that the EMA is a function of previous day’s EMA and today’s closing price. The calculations are shown from the screen grab.

Calculate-MACD-in-Excel-12day-ema

The same formula holds true for 26 day trailing average, with period being 26 trading days. Column D shows the calculations behind 26-day EMA. The first value or cell D27 is average of the past 26 day’s closing prices, while cells D28 onward are in the formula above.

Calculate-MACD-in-Excel-26day

4) MACD is the difference between 12-day EMA and 26-day EMA as shown in column F.

A 9-day EMA of the MACD which is often called the “signal line”, is then plotted on top of the MACD. This serves as the trigger for buy and sell signals. The equation for calculating Signal Line is:

Calculate-MACD-in-Excel-equation

where Period is 9.

Calculate-MACD-in-Excel-formula-sheet

The chart below is the plot of 12 and 26 day EMA for Macy’s. MACD is about convergence and divergence of two moving averages. As you can see below that the shorter moving average (12-day) is faster and drives the overall MACD movement. The longer moving average (26-day) is less reactive to stock price changes.

calculate-macd-in-excel-12-26-EMA

 

calculate-macd-in-excel

Usage Instructions

Trading models that were designed earlier using Yahoo! Finance API have been rewritten to retrieve quotes from Bloomberg Open Markets. At this time only US stocks and ETFs can be retrieved from the MACD model. See setup instructions

The spreadsheet model retrieves quote information from Bloomberg Markets API through a lightweight data-interchange format called JSON or JavaScript Object Notification. If this is your first time programming JSON in Excel, you must add certain libraries before using Bloomberg Markets Web Service.

Under Developer->Visual Basic->Tools->References, add the highlighted objects.

VBA-JSON-References

7 thoughts on “Calculate MACD in Excel

  1. Trevor Chan

    I am new to world of trading and finance. What are the main sources of bullish signals in MACD?
    -Trevor

    Reply
    1. invest88766 Post author

      A bullish divergence happens when the MA Convergence/Divergence indicator is making new highs while prices fail to reach new highs….

      Reply
  2. Danny Dwyer - Australia

    This excel VBA computes and plots Moving Average Convergence and Divergence (MACD), using Yahoo Finance Web Service – Please be advised that this Yahoo website has recently closed down. I am requesting that this Excel VBA be changed to Google’s Finance website to download the ticker data for both USA and Australian stocks.

    It would be very appreciated if this can be done. Let me know via my email dwyerfam@outlook.com

    Thanks

    Reply
    1. Jish Bhattacharya Post author

      Thanks for bring that one up. I’ll soon migrate the spreadsheet models to pull from Google Web Service.

      Reply

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.