How to Create a MACD Chart

The MACD line and chart is a popular tool for technical analysts who buy and sell stocks. And in this post, I’ll show you how you can create it from start to finish. In my example, I’ve downloaded Apple’s stock price history for the past year from Yahoo Finance, and I’ll use that to calculate its MACD line. Here’s a sample of what I’m starting with:

Calculating the exponential moving averages

To calculate the MACD line, I’ll need to create multiple exponential moving averages (EMAs). One for 9 days, 12 days, and for 26 days. The logic will be the same so I can start with creating a formula for the 9-day EMA and then apply that to the others.

I’m going to create a couple of variables. The first being the n for the number of days. And the second one is for the weighting that you’ll apply to more recent values, and thus, turning it from a simple moving average into an exponential one. The weighting is calculated as follows:

=2/(1+n)

I’ll start my formulas to calculate the 9-day EMA by first checking to see if I have at least 10 data points. If I don’t, then I’m only calculating a simple moving average. Here’s how the start of that formula looks, assuming my closing stock prices start from cell B5 and my variable n is in cell C1:

IF(COUNTA($B$5:$B5)<=C$1,AVERAGE($B$5:$B5)

A key part of the formula is freezing cells properly. Cell $B$5 won’t move, but $B5 will as I drag it down. And this allows me to calculate the cumulative number of data points, and the corresponding average. The next part of the formula is what happens if I have more than nine data points. In that case, I will take the weighting (this is cell C2) on my sheet, and multiply that by the difference between the most recent stock price and the previous EMA. This will then get added to the previous day’s EMA:

C$2*($B5-$C4)+$C4

Column C is the one that contains the EMAs. My complete formula is as follows:

=IF(COUNTA($B$5:$B5)<=C$1,AVERAGE($B$5:$B5),C$2*($B5-$C4)+$C4)

I can now copy this logic across multiple columns to calculate the 12 and 26 day EMAs as well:

Now, I’ll set up the calculations for the final three columns:

• MACD: This involves taking the 12-day EMA and subtracting the 26-day EMA from that.
• Signal Line: This is a 9-day EMA of the MACD line.
• Histogram: This is calculated as the difference between the MACD line and the Signal line.

With all those columns set up, here is my completed table:

Creating the charts

With all the columns set up, the next part is to put the key data into a chart to illustrate the MACD line, Signal line, and Histogram. To make the chart look like a typical MACD chart, I’ll need to set the MACD line and Signal line to be line charts, and for the Histogram to be a column chart.

Initially, when the chart is created, there’s too much data in there since the data set is bigger than it needs to be:

To fix this, I right-click on the chart and click Select Data. Then, I remove all the series except for the last three: MACD line, Signal line, and Histogram. My updated chart looks as follows:

There are still a few more changes that I am going to make here. The first is to fix the axis, as there are gaps between the column charts. That’s because Excel is recognizing the axis as a date axis. And while that’s correct, that means there will be gaps since stocks don’t trade every day of the week, and thus, those gaps, are weekends. To fix this, right-click on the axis and click Format Axis. And then, change the Axis Type so that it is a Text axis:

And then, under the Labels section, I set the position so that it is Low and at the bottom of the chart. My updated chart looks a bit better:

The last change you may want to consider is adjusting the column chart gap, to shrink it so the chart looks more like a histogram. If you right-click on them and click Format Data Series, there’s an option to change the Gap Width. I find that setting this to 50% normally results in a good gap size::

And now we’ve got a chart that resembles what you might find on major finance sites when looking at MACD. If you want to follow along with the sheet that I’ve created, you can download my MACD chart template here.

If you liked this post on How to Create a MACD Chart, please give this site a like on Facebook and also be sure to check out some of the many templates that we have available for download. You can also follow us on Twitter and YouTube.