Moving Average Convergence/Divergence (MACD)

Introduction

Developed by Gerald Appel, Moving Average Convergence/Divergence (MACD) is one of the simplest and most reliable indicators available. MACD uses moving averages, which are lagging indicators, to include some trend-following characteristics.

These lagging indicators are turned into a momentum oscillator by subtracting the longer moving average from the shorter moving average. The resulting plot forms a line that oscillates above and below zero, without any upper or lower limits.

MACD Formula

The most popular formula for the MACD is the difference between a security's 26-day and 12-day Exponential Moving Averages(EMAs).

Of the two moving averages that make up MACD, the 12-day EMA is the faster and the 26-day EMA is the slower. Closing prices are used to form the moving averages. Usually, a 9-day EMA of MACD is plotted along side to act as a trigger line. A bullish crossover occurs when MACD moves above its 9-day EMA, and a bearish crossover occurs when MACD moves below its 9-day EMA.

The histogram is positive when MACD is above its 9-day EMA and negative when MACD is below its 9-day EMA.

Interpretation

MACD is a trend following indicator, and is designed to identify trend changes. It's generally not recommended for use in ranging market conditions. Three types of trading signals are generated,

* MACD line crossing the signal line.

* MACD line crossing zero

* Divergence between price and MACD levels

The signal line crossing is the usual trading rule. This is to buy when the MACD crosses up through the signal line, or sell when it crosses down through the signal line.

When the MACD line crosses through zero on the histogram it is said that the MACD line has crossed the signal line.

The histogram can also help visualizing when the two lines are coming together.

A crossing of the MACD line up through zero is interpreted as bullish, or down through zero as bearish.

Positive divergence between MACD and price arises when price makes a new selloff low, but the MACD doesn't make a new low(i.e. it remains above where it fell to on that previous price low). This is bullish, suggesting the downtrend may be nearly over.

Negative divergence is when price makes a new rally high, but MACD doesn't rise as high as before, this is bearish.

Trading Signals

First check whether price is trending. If MACD is flat or stays close to the zero line, the market is ranging and signals are unreliable.

Go long when the MACD line crosses the signal line from below.

Go short when the MACD line crosses the signal line from above.

Signals are far stronger if there is either:

a divergence on the MACD line; or

a large swing above or below the zero line.

To Calculate MACD in MS Excel:.

Column | Description | Formula to enter

A = Company Name/date

B = Open

C = High

D = Low

E = LTP/close

F = Volumes

G (12DAY EMA) = first we have to calculate 12-Day Ema:

For 12 day Ema,we have to calculate 11-day Simple mov.aver(SMA).

Formula= Sum(E2:E12)/11 , enter formula in G12

In 13 row of G column we have to calculate ema

EMA formula = price today * k + EMA yesterday's * (1-k) where N is number of days in your ema

OR

X(EMA) = (K x (C - P)) + P

X = Current EMA,

C = Current Price,

P = Previous period's EMA*,

K = Smoothing constant

(*A SMA is used for first period's calculation),K = 2/(1+N),N = Number of periods for EMA

calculation

K = 2/(1+N)

12day=2/(12+1)=0.15

26day=2/(26+1)=0.074

so formula for 12day ema would be =E13*0.15+G12*(1-0.15) OR (0.15*(E13-G12))+G12, enter formula in G13, copy the formula till latest closing price.

H (26DAY EMA) = now we have to calculate 26 day ema,so u have to go thru the Mov.Aver.formula steps once again:

we have to calculate 25-day Simple mov.aver(SMA).Formula= =Sum(E2:E26)/25 enter formula in H26

so formula for 26day ema would be =E27*0.074+H26*(1-0.074) OR (0.074*(E27-H27))+H27,enter formula in H27,copy the formula till latest closing price.

I (MACD[FAST LINE])= Subtract 26-day ema from 12-day ema.so formula =H27-G27, enter in I27,copy the formula till latest closing price.

J (9-day ema[Slow Line]) = first we have to calculate 9-Day Ema of MACD(trigger/signal line)

calculate 8-day Simple mov.aver(SMA).Formula =SUM(I27:I34)/8,enter the formula in J34

then,Ema kicks in

K = 2/(1+N), so 2/(9+1)=0.2

=I35*0.2+J34*(1-0.2),enter the formula in J35,copy the formula till latest closing price.

K (MACD Histogram)= we have subtract Slow line(J34)from Fast line(I34) so formula =J34-I34,enter in K34,copy the formula till latest closing price.

## 62 comments:

Hello bro

I am in serious problem please reply me as soon u read my message, I tired ur farmula to calculate 12 day EMA on Reliance industries for on 30th may 2008 data and ur calculation is giving me 12EMA = 2542 while icharts and on other software its coming 2528, u won't believe but have devoted more than12 hours to rectfiy this problem and tried atleast 50 -60 times to get correct values but unable to find so please tell me what to do , urgent reply is heartly requested

hey maahir,

while am i getting same correct values as icharts....

i think u messed it up somewhere...

send me mail id so tat i can send u my excel sheet,for u to chk it out where u went wrong.

Brother

my email id is maahir.com@gmail.com , please send me calculation of reliance, Please i will be highly thankful to you, if you send me as early as possible , my last 5-7 days were most horrible days of my life due to this MACD Calculation.

Main Problem is coming in calculation of EMA , i think this concept is not clear to me , I have downloaded that pic of ur excel sheet, but calculation varies because of worng EMA.

I am making some application where I have to calculate EMA and MACD of Reliance, So please tell me how many days of data of Reliance is required to calculate correct MACD valueas it appearing in ICHART.IN

PLEASE REPLY FAST ,my email id is maahir.com@gmail.com please

I have been doing some comparisons on how to calculate the Exp Moving Average. I have found 3 different ways to calculate it. The major difference appears to be in setting the initial value. In one method the first value is set by getting the average of the last n prices and putting this value in (bar), your calculation is setting the value by getting the average of the last (n-1) prices, putting this value in (bar -1) and then calculating the EMA on this value. Excel follows the method outlined by Kaufman by setting the initial value as the first price in the series and using that. I have put together a spreadsheet demonstrating the differences which I can send if you like. Do you have any thoughts on which is the best one to use and why? This is really important given the importance of the MACD in many trading systems!!

Thanks a lot for your helpful information. But isn't the histogramme value reversed?

The calculation of the MACD is incorrect. According to Kaufman (page 129) the MACD is the smoothed average (12) - smoothed average (26). Your formula has this reversed.

Dear visitors,

From so many days everyone is feeling quite bore while working in a href="http://www.sharetipsinfo.com" title="Indian stock market" Indian stock market

as very less movement is there. NSE and BSE graphs are not moving which are forcing people to just sit back but now market is expected to witness a breakout.

just enjoy it.

Moreover now you can have free access to a href=" http://sharetipsinfo.com/weekly-precition_1.html "title="Weekly Newsletter" Weekly Newsletter and Stock research reports on our website.

For any doubt please feel free to ask us.

Thanks

Regards

a href="http://www.sharetipsinfo.com" title="SHARETIPSINFO TEAM"SHARETIPSINFO TEAM

I hope you can make the excel file downloadable instead of screenshot. It would be very useful. Thanks

Typo here:

"so formula for 12day ema would be =E13*0.15+G12*(1-0.15) OR (0.15*(E12-G12))+G12"

should be...

"so formula for 12day ema would be =E13*0.15+G12*(1-0.15) OR (0.15*(E13-G12))+G12"

(changed E12 to E13 in second formula)

thanks tom,

corrected the typo.

Hello Maahir,

I am a small investor of DSE. I always trade very carefully based on MACD line form a website. Recently I am facing a great trouble because website is unaccessible now. It will be better for me if I can form a MACD line for my personal analysis.

Please, send me a full project of MACD so that I can justify the market.

Heartly waiting for your reply.

Mohammad Tanvir

hi Mohammad Tanvir,

send me your e-mail id so tat i can send u my excel sheet.

regards,

rajeev

hai,

can you please send me a copy of MACD excel sheet.

Hi Maahir,

I am also very interested in getting a copy of your excel sheet for the MACD calculation.

Does this spreadsheet provide data for a graph, or just values to compare?

I will be inputting many stock symbols and their data.

Thank you for your help and patience.

Rascal_Ron

Hi Geniusjaggu,

can you please send me a copy of MACD excel sheet.

PLEASE REPLY FAST ,my email id is ali71a@gmail.com

hi,

genius jaggu,

i trade in reliance using macd. but my macd on reliance do not match the market trend.

pls send me the reliance macd calculation from 1.1.2009

hi....

i neeed macd calculated chart for reliance for the month of july 09 to sept.09

my mail id is jais.ajay.k@gmail.com

Please send me an excel sheet for MACD calculation. my mail :

mahbub73@hotmail.com

hi mr.

you r geniuos. your calculate is very helpful to me.Please send me an excel sheet for MACD calculation. my mail :

smsolaiman_143@yahoo.com

khasru

Hai Bro

U r Realy Genius

if Possible Pls send me Excel Sheet.

chetangupta3985@gmail.com

Thanks Alot

thanks alot

u r realy genius

if possible pls send me excel sheet.

mail id. chetangupta3985@gmail.com

Hai Bro

U r Realy Genius

if Possible Pls send me Excel Sheet.

Thanks Alot

Hi Maahir and Geniusjaggu,

Today I tried to work on MACD calculations and saw this website. I tried working on MACD many a times but failed. Seeing your work and the posts made, i too am interested in knowing about MACD calculations. Can you please provide me also the excel sheet worked by you.

forgot to give my mail id:

vishnubhotla.uday@gmail.com

please send to this mail id.

Thanks in Advance

uday

Thanks bruv. These excellent posts are proving very useful for me!

pls mail me MACD working sheet for nifty.......thanks....

pls mail me MACD working sheet of nifty....my id.....jais.ajay.k@gmail.com

hi

anyone get nifty macd excel sheet please mail me on jigneshparekh27@gmail.com

thx

hi

anyone get nifty macd excel sheet please mail me on jigneshparekh27@gmail.com

thx

i am having hard time to calculate MACD

could you please send me your excel sheet so that i can see what i am doing wrong

I appreciate it very much

my email id

josephyoung74@yahoo.com

or

josephyoung69@gmail.com

Thanks again

I am investing DSE. I always trade very carefully based on MACD line form a website. Recently I am facing a great trouble because website is unaccessible now. It will be better for me if I can form a MACD line for my personal analysis.

Please, send me a full project of MACD so that I can justify the market.

Heartly waiting for your reply.

josephyoung69@gmail.com

Hello

plz send me MACD excel sheet.

My e-mail id is simit.shah@yahoo.co.in

Thanx in advance

Hi

Thanks for the your article on MACD calculation, but you wrote for column H the following:

H (26DAY EMA) = now we have to calculate 26 day ema,so u have to go thru the Mov.Aver. formula steps once again:

we have to calculate 25-day Simple mov.aver(SMA).Formula= =Sum(E2:E26)/25 enter formula in G26

however, the formula should be entered in H26 not G26, am I right?

Regards.

Mohamed Saidy

Can you please proceed from your EXCEL sheet to a grphical presentation and elaborate on crossover, convergence, devergence, etc.

Regards

Hi sir, I am nagaraj. I am doing MBA (Finance). I need to calculate MACD for completing the project. I need example worksheet(MS-EXCEL) with calculation. My e-mail id is snagarajanmpec@gmail.com. please send it as soon as possible.

hi rajeev..

first of all thanx for such a helpfull infomation.. can u pls send the excel sheed of macd.. and pls provide me some infomation about technical analysis.. i want to do that course.. where n how i can do ? my mail id ankit_kaknani@yahoo.co.in

sir,

i want to calculate 5 day low ema for nifty in exel. please tell me how i can get it using a particular formula. what will be the formula.

thanks and regards

sanjay kaushik

sanjay_peoples@yahoo.com

Sirfpaisa.com is the amazing site which provide excellent Share Market Tips.The best part of this site is they also provide Share Tips on mobile too.so You just visit the site http://www.sirfpaisa.com/

Good job.... and very clearly explained... defntly it will help to many students.

Wall Street opened lower on Monday as shares fell globally following a surprise interest rate increase from China’s central bank.

The Dow Jones industrial average was down 32.54 points, or 0.28 percent, at 11,540.95. The Standard & Poor’s 500 Index was down 3.14 points, or 0.25 percent, at 1,253.63. The Nasdaq Composite Index was down 8.92 points, or 0.33 percent, at 2,656.68.

It is the second time in three months that China has taken steps to slow the pace of its economic expansion. Inflation jumped to its highest levels in two years in November. Rising prices have led poor families to spend more than half of their incomes on food.for more details please visit to:-http://www puntercalls.com

Wall Street opened lower on Monday as shares fell globally following a surprise interest rate increase from China’s central bank.

The Dow Jones industrial average was down 32.54 points, or 0.28 percent, at 11,540.95. The Standard & Poor’s 500 Index was down 3.14 points, or 0.25 percent, at 1,253.63. The Nasdaq Composite Index was down 8.92 points, or 0.33 percent, at 2,656.68.

It is the second time in three months that China has taken steps to slow the pace of its economic expansion. Inflation jumped to its highest levels in two years in November. Rising prices have led poor families to spend more than half of their incomes on food.for more details please visit to:-http://www puntercalls.com

hey

m a small investor and wanted full update of MACD and how it works/calculated .

plz provide me details my mail id is : sehzadaa@gmail.com

Hi sir, can you send me a full project of MACD so that I can justify the market. Final graphical representation would be appreciated. Thanks.

my email id is:

ola_toks2000@yahoo.com

hi jaggu,

Can you please mail me an excel sheet showing the calculation of MACD & EMA. I'll be thankful if you could please help me out. My mail id is shubhneet.sethi@gmail.com

Hey,

Indian stock market is losing its shine now a days but its still not over for our NSE and BSE. Current market correction will urge many new investors to join the Indian stock market

Its wrong to consider correction as bad for the stock market. Stock market correction is rather healthy for genuine investors.

Could you please e-mail me the copy of your MACD calculation Excel sheet?

My e-mail ID is ramabhat.thim@gmail.com

Thanking you in advance !!!

Sharemarketzone is a network of various top stock market analyst who are posting their research at one place for the various stock market traders and investors for free

can anyone please please please please post the MACD and EMA excel sheet to "balu.murki@gmail.com"

I will be a lot thankfull.

thanks

hi, i am trying to use the formula but i couldnt get it working. is it possible for me to have a copy of your working file too?

shermund@gmail.com

geniusjaggu,plz send me your excel sheet for the calculation of MACD

My email ID is -

nareshchaudhary20@gmail.com

Thanks in advance

Hi geiusgaggu

please do send me the MACD calculation excel sheet ASAP and oblige.Thanksalot. My email id is sudarshancs2409@gmail.com

Dear Mr. Geiusgaggu

please do send me the MACD calculation excel sheet jeyarasu@gmail.com.

Way2profit provides accurate future stock tips for trading in NSE and commodity tips for commodity trading in MCX and NCDEX.

90% accurate commodity tips, equity tips, nifty tips, option trading tips, future tips for sure profit can be availed for free from way2profit. http://www.way2profit.in

Took me time to read all of the feedback, but I really loved the article. It proved to be very helpful to me and I am sure to all the commenters right here! Its always good when you can not solely be informed, but in addition engaged! Im positive you had pleasure this article. Anyway, in my language, there are a lot good supply like this.

Stock Tips

Well explained

Keep sharing

your blog suggestion is knowledge full information keep continue provide information very helpful .

mcx tips with 100 accuracy

A very informative post regarding MACD. I was performing this on excel and was having some doubts. This post helped me in clarifying my doubts. Follow good stock tips while trading to earn good profit.

I read each line of article, well informed. Looking for more useful post and really appreciate for this wonderful work. Can you provide Share Market Tips service.

I had use MarketXLS. It works great for me.

Technical Analysis in Excel

Post a Comment