Moving Average Convergence Divergence (MACD)

Are you an investor looking to gain a competitive edge in the stock market? If so, you need to be able to analyze market trends and make informed decisions based on real time data. That’s where the MACD (Moving Average Convergence Divergence) indicator comes in. MACD is a powerful tool for analyzing market trends and identifying potential buying or selling opportunities. And with this step-by-step guide in Excel, you can easily calculate MACD in Excel and start using this valuable indicator to enhance your trading strategy.

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.

How to explain MACD to a 5 year old?

The MACD is a tool that helps us look at a graph of something like a stock or a cryptocurrency to try to figure out what might happen in the future. It’s kind of like a magic wand that helps us see into the future of the graph!

To use the MACD, we need to look at two different lines that are moving around on the graph. One line is called the “fast line” and the other line is called the “slow line.” When the fast line is above the slow line, it means that the thing we’re looking at might go up in the future. When the fast line is below the slow line, it means that the thing we’re looking at might go down in the future.

But that’s not all! The MACD also has something called a “zero line.” When the fast line is above the zero line, it means that the thing we’re looking at is doing pretty well. When the fast line is below the zero line, it means that the thing we’re looking at might not be doing so well.

MACD Step by Step

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 Virgin Galactic or ticker SPCE. We will explore it by using Excel VBA so you have all the necessary tools to get started.

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 example, Virgin Galactic which goes by SPCE , start date was set to Sep-13-2022, and end date to Mar-12-2023.

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 –

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.

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.

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:

where Period is 9.

The chart below is the plot of 12 and 26 day EMA for Virgin Galactic. MACD is about convergence and divergence of two moving averages. As shown below, the 12-day moving average is faster and dominates the overall movement of the MACD. In contrast, the 26-day moving average is less responsive to changes in stock prices.

Conclusion

In conclusion, the MACD is a versatile tool that can be used by traders to identify potential trend reversals and momentum shifts in financial markets. It is important to use it in conjunction with other technical indicators and fundamental analysis to make informed trading decisions.

Usage Instructions

Please refer to Import Stock Prices and Financial Data Directly into Excel post. I have discussed Excel and Alpha Vantage API integration with an example in length.

Down the VBA enabled MACD in Excel