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.



55 comments:

Unknown 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

niftyxl 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.

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

Leo said...

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

Jeff 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.

TheLandlord said...

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

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

niftyxl said...

thanks tom,
corrected the typo.

Unknown 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

niftyxl 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.

Unknown 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

Product Technicals 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

Product Technicals 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

Unknown 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

CAPS said...

Hai Bro

U r Realy Genius

if Possible Pls send me Excel Sheet.

chetangupta3985@gmail.com

Thanks Alot

CAPS said...

thanks alot


u r realy genius


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

CAPS said...

Hai Bro

U r Realy Genius

if Possible Pls send me Excel Sheet.

Thanks Alot

Unknown 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.

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

Product Technicals said...

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

Product Technicals said...

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

Market Analysis said...

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

thx

Market Analysis 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

Unknown said...

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

Unknown 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

Unknown said...

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

Regards

Unknown 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 Kaknani 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

Unknown said...

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

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

Commodity Tips | Stock MarketTips 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

Unknown 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

Unknown 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

Dr Sudarshan CS said...

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

Unknown said...

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

Unknown said...

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

Siksha Chouhan said...

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

Yash said...

Well explained
Keep sharing

aditi said...

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.

Unknown said...

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.