How to Calculate and use the MACD Indicator in MS Excel

[@MarkUrsell]

The latest version of my 21 Technical Indicators Spreadsheet (new formulas & descriptions) is now available for instant download in my Spring Store: teespring.com/stores/tradinformed

[@sarathwickramarathne5092]

very good. easily understandable

[@zenryan4689]

Awesome...thans a lot Mark for this tutorial

[@MarkUrsell]

Glad it was helpful!

[@dn6127]

Thank you! Just what i was looking for.

[@MarkUrsell]

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.

[@sixsigmastuff]

Thanks for this; very helpful

[@MarkUrsell]

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.

[@Nab_Suid]

Hi Mark, would you send me all formula of that or your documents excel of that to my email? It would be helpfull for me to learning it. Thank you.

[@MalcolmSwaine]

Thanks Mark, that was very useful :)

[@MarkUrsell]

Yes, I've noticed the same thing. The original video is clear but it is probably the connection speed at the time. If you let the video buffer for a bit it might help.

[@borg3575]

As Mark said to another poster, you have to start your EMA calcs way back. You have to start at least 90 rows back to get your numbers to line up with something like Yahoo. The reason for this is that it takes about that long for the EMA to get rid of the influence of the simple average it started from. Yahoo does not go back 26 days for it's MACD data, it's more like 5 years.

[@preciouswogu6637]

please how is this calculate4d on a particular time frame, lets say you want to analyze the MACD on 30mins time frame, how do you apply it. the calculation for that time frame

[@MarkUrsell]

Hi Ken, the calculation is exactly the same. Replace the daily price data with 30 minute data

[@ne0ne0]

This is a very interesting tutorial. Thanks a lot! What would you advise to avoid stock splits triggering false trading signals?

[@MarkUrsell]

lucmelnitz Thanks for the feedback. Regarding the stock splits. For a backtest I would want to adjust for splits before starting. Some data providers will have an adjusted price that accounts for splits. Using the adjusted price will keep the trading signals accurate. If you mean using a spreadsheet for live trading then you could use a formula that gives an alert if the price changes by more than a certain percentage. This could be useful in any case to know if there is significant market moving information to be aware of.

[@farhanhafeez9909]

Wonderful, thx alot.

[@MarkUrsell]

Glad you liked it!

[@gmanger7]

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?

[@Sky-lm1gu]

I think he had an error when calculating the first value of EMA(12) and EMA(26).

[@philippecolin151]

Why on the fast EMA you only use 9 cells instead of 12 while you use 26 on the slow EMA? is it an unwanted mistake or should it be that way?

[@hungpham-ek3md]

Same question as yours, is it a mistake by the author?

[@timothychristian6701]

mark, can i ask? i curious why in fast EMA, u put the average in bar 12? i think it should be bar 14?

[@Sky-lm1gu]

I think he had an error when calculating the first value of EMA(12) and EMA(26).

[@jianwenzhang3655]

extremely useful. thanks a lot.

[@abradolflinncler1627]

But the Macd usually keeps oscilating between -200 and 200, whereas MAs aren t bound. Is there another claculation to plot it within those limits?

[@MarkUrsell]

Hi, great question. Try the PPO, it is identical to the MACD except the indicator is expressed as a percentage. Have a look at this chart to compare the MACD and PPO: schrts.co/WtHSYAjp

[@SnowHunter44]

Mark, love your videos, great job. I have a problem with the MACD though. I have been through the video several times to make sure no errors, but calculation does not match charts (TradingView). Had similar with ATR at first but found out I made an error. Now that works great, trying to use for a journal and then "backtest" paper trades. Would streamline significantly if I could get this to agree with charting software... any ideas??? BTW I am using Google Sheets, if that makes any difference.

[@MarkUrsell]

Hi Arvin, you will definitely get this to match! There are a few reasons why the MACD values are different. Firstly, you need to make sure the data in your spreadsheet is identical to the data used in your chart. If you are using stock data you will often find slightly different prices from different data sources. When you are certain the data is identical, you need to calculate the MACD on a large data set. The EMA (unlike the SMA) uses the value of the previous period as part of the calculation. I suggest calculating the two EMAs over (at least) a few hundred bars of data. Then check whether the 12 and 26 EMAs match up exactly with your charts. When they match exactly you are almost there. The MACD is the difference between the 2 EMAs. Now, to get the signal line and histogram you will need to do the same thing to make sure that the EMA has enough data to calculate correctly. I hope this helps!

[@SnowHunter44]

@@MarkUrsell Thanks for the reply, I have only been working with a small data set, I will try this solution!!

[@SnowHunter44]

@@MarkUrsell I was finally able to get results to "agree", My mistake was that I was referring to the 12 ema column (calculating 26 ema) when I should have been pointing to the close price column. After several days of "fighting " with it I saw where I went wrong. Thanks again for your support, someone else may need this clarification, which is why I posted this, all on me though, by the way.

[@renatogyuris9569]

avarage at 2:13 is just the previous 9 days average

[@nikleshraut5310]

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 ;)

[@MarkUrsell]

+Niklesh Raut Thank you Niklesh.

[@malikaftab6170]

hello mark m Aftab from Pakistan i need ur help mark There is one line in my defaulted MACD indicator which is called Signal line but i need MACD LINE also it says that MACD LINE is the subtraction of 2 EMAs which r 12 days EMA and 26 days EMA so plz tell mee how and which method i may get that MACD LINE plzzzzzzzzzzzz m waiting

[@MarkUrsell]

Hello, the MACD line is calculated as the Fast EMA - Slow EMA. The signal line is an EMA of the MACD line. And the histogram is the MACD line - histogram. Hope this makes sense.

[@arslanishaq3373]

same problem bro

[@craigmarais5816]

thanks!

[@asdasdasd505]

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.

[@peterlilley6819]

+1 Picked up the same thing...

[@vitamink3]

For EMA 12 (Fast EMA), he is using a 9 week period, rather than a 12 week period. To correct this just move down the starting point of EMA 12 to a 12 week period.

[@8ksmiff502]

Can you share the python file?

[@rafalvarezsevilla]

should the signal really be an EMA or rather an SMA? every youtuber says something different, i get better results with SMA...

[@MarkUrsell]

Use whichever you prefer. I compared SMA, EMA and Smoothed Moving Average last year and the results are pretty similar. The SMA moves a bit slower than the other two. I expect that more people (and more money) are watching the SMA on the big round numbers such as 200 SMA which can affect how the price reacts

[@hdsouza100]

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 (quote.morningstar.com/Stock/chart.aspx?t=AAON) MACD = -0.08 and SIGNAL = 0.03 Can you please validate

[@RickACG]

Why weekly and not daily?

[@bb2866]

you made a mistake in column H - you need to average the first 12 closes - not the first 8

[@TendySentosa]

Sorry..it was slow internet causing the screen blurry

[@billmietelski]

Bill was here.

[@TendySentosa]

blurry