How to Calculate and use the MACD Indicator in MS Excel



This video shows how the MACD Indicator can be easily programmed into Microsoft Excel. The spreadsheet uses data from the S&P 500 index. The next video in the sequence will show how we can use Excel to backtest a MACD trading strategy.

How to calculate this and other popular indicators are included in my latest eBook, now available in the Amazon Kindle Store: http://amzn.to/1smFKHr

My ebook course on building backtest models in Excel is also available in the Amazon Kindle Store: …

12 thoughts on “How to Calculate and use the MACD Indicator in MS Excel

  1. Hi mark

    I'm coding the indicator with Python using the values of your spreadsheet but my results are different. I find there's an error in the EMA 12 periods of this video:

    You started to calculate the EMA 12 periods from cell H:13 (10 periods)
    It should start from cell H:15 (12 periods back) otherwise the factor H:3 need to be changed to 0.18 for a 10 periods EMA.

    Thanks for your great work and ebooks.

  2. Thanks a lot Mark to share this video, this is really very interesting video very simple to understand, Even for those who are not interested in market strategy or understanding chart 😉

  3. Mark, If we take the formulas and run it against live stock data the MACD results are different from what major websites iike Yahoo finance/Morning star provide

    For example, If we look at stock = AAON for date = 5/20/2015 the values from formulas computed using the video
    MACD = -0.1119 and SIGNAL = -0.1442

    Compare that to Yahoo finance or Morning star for the same date (http://quote.morningstar.com/Stock/chart.aspx?t=AAON)   MACD = -0.08 and SIGNAL = 0.03

    Can you please validate

  4. Hi there, thanks for watching the video. I have seen different ways to calculate the MACD and the EMA but I believe the method in this video is the most commonly used. The signal line is an average of the difference and so will always be slower. When the current price is strong the difference will be greater than the signal, therefore by subtracting the signal we get a positive number in the histogram. When prices are weak we get the opposite.

  5. Thanks for the comment Greg. Well spotted about the fast ema. There was no particular reason for using 9 observations. I normally prefer to start the ema with a simple average of the same length. I noticed the same thing when I compared the video spreadsheet to an earlier version. The ema quickly adapts to the correct number of periods so it does not affect the results.

  6. Great videos, Mark. Thanks for posting. One question I had for you concerns the way you calculated the EMA for the "fast line" of the MACD stochastic. It looks like you began the simple average after only 9 observations, not 11. any reason for this or am I missing something?

Leave a Reply

Your email address will not be published. Required fields are marked *