Pages

Sunday, November 25, 2007

MACD:Meaning &Calculation

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.



52 comments:

Maahir said...

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

geniusjaggu said...

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.

Maahir said...

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

OzzyMiner said...

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!!

Bao said...

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

SableFreelancer said...

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.

www.ShareTipsInfo.com Team said...

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

TheLandlord said...

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

Tom said...

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)

geniusjaggu said...

thanks tom,
corrected the typo.

Md. said...

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

geniusjaggu said...

hi Mohammad Tanvir,
send me your e-mail id so tat i can send u my excel sheet.
regards,
rajeev

ram said...

hai,

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

Ronald said...

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

Ali said...

Hi Geniusjaggu,

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

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

ajay said...

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

ajay said...

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

Mahbub said...

Please send me an excel sheet for MACD calculation. my mail :
mahbub73@hotmail.com

s.m.alis-bin- said...

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

chetu said...

Hai Bro

U r Realy Genius

if Possible Pls send me Excel Sheet.

chetangupta3985@gmail.com

Thanks Alot

chetu said...

thanks alot


u r realy genius


if possible pls send me excel sheet.
mail id. chetangupta3985@gmail.com

chetu said...

Hai Bro

U r Realy Genius

if Possible Pls send me Excel Sheet.

Thanks Alot

sailu said...

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.

sailu said...

forgot to give my mail id:
vishnubhotla.uday@gmail.com

please send to this mail id.

Thanks in Advance
uday

Fungus Humungus said...

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

ajay said...

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

ajay said...

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

Jignesh said...

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

thx

Jignesh said...

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

thx

Joseph said...

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

Joseph said...

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

Sam said...

Hello
plz send me MACD excel sheet.
My e-mail id is simit.shah@yahoo.co.in
Thanx in advance

Alsaidy said...

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

Alsaidy said...

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

Regards

nagarajan said...

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.

Latesh said...

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

sanju said...

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

rohit.seo.blogspot.com said...

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/

Dinesh said...

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

santoshi prasad said...

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

santoshi prasad said...

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

sehzadaa said...

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

adebayo said...

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

Shubhneet Sethi said...

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

SHARETIPSINFO said...

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.

ramabhat said...

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 !!!

Stock market trading said...

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

Balu Murki said...

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

sher said...

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

Kartik Singh said...
This comment has been removed by a blog administrator.
SimCon said...
This comment has been removed by a blog administrator.
naresh chaudhry said...

geniusjaggu,plz send me your excel sheet for the calculation of MACD
My email ID is -
nareshchaudhary20@gmail.com
Thanks in advance