Learn how to calculate and plot MACD in Excel, and start making better trading decisions. This article is the first of a two-part series. This part offers a step-by-step forex calculation formulas to calculating and charting MACD in Excel.
The second part explores how market technicians use MACD to make better trading decisions. An MACD chart consists of three elements. The third is simply the MCAD minus the signal, and is known as the histogram. The chart below, for example, is the MACD and signal line for Apple between two dates. Other nuances will be explored in the next article in this series. Developed by Gerald Appel in the 1970s, MACD is now widely used by traders to generate forecast price trends, and generate buy and sell signals.
In the following step-by-step guide, we’ll calculate the MACD of Apple, giving you all the tools you need to recreate the chart above. You can download the complete spreadsheet at the bottom of this article. You can get historical stock quotes using this bulk stock data downloader spreadsheet. Column A and prices in Column C. All other values are given by this formula.
The screengrab below illustrates what the spreadsheet should look like, and how the formulas are entered. The MACD is simply the 12 day EMA minus the 26 day EMA. This is a 9-day EMA of the MACD. The first value is simply a 9-day trailing average. All other values are given by this equation, where the time period is 9. You now have all the data you need!
With Excel’s charting tools, you can now plot the 12- and 26-day EMA, MACD and signal data. Yahoo Finance should give the same MACD. Hi Samir, many thanks for this excellent spreadsheet. Please tell me how to use the chart function in Excel to make my chart look like the one in this article. It’s not difficult if you’re motivated. I’ve tried using your tutorial and the bulk downloading spreadsheet. When I open your tutorial the numbers all look like they are supposed to but when I type in the formula or even cut and paste the numbers all become identical.