Learn how to use MACD to generate reliable buy-sell signals, and get a web-connected spreadsheet to automatically plot technical trading charts from a stock ticker.

In the previous article in this series, we learned how to calculate and plot MACD in Excel.

Read on to discover how traders know when to buy and sell using MACD as a critical part of their technical arsenal.

You can also **download a bonus Excel tool to download historical stock prices and automatically plot MACD, RSI, EMA and ATR** – the link is at the bottom of this article.

MACD is constructed from the following elements.

- a 12-day exponential moving average of the stock price,
- a 26-day exponential moving average of the stock price,
- the MACD line, which is the 12 day moving average minus the 26 day moving average,
- and a 9-day moving average of the MACD, also known as the signal.

You can learn how to calculate these quantities in Excel here.

Traders sometimes alter the times for the moving averages to s, but 12-26-9 are typical.

A histogram (or more correctly a bar-chart) is the MACD line minus the signal line; this signals whether the MACD is greater or smaller than the signal line.

An MACD chart usually consists of the MACD, signal and histogram. This is a typical example.

This type of chart is easy to automatically generate from a ticker symbol with the spreadsheet available at the bottom of this article.

The 12- and 26-day moving averages follow the *trend of the stock price*.

- In a rapidly-moving market the, 12-day EMA dips or rises more steeply than the 26-day EMA.
- In a steady market, the difference between the two, the MACD, will oscillate in a range

The MACD is hence a *momentum-oscillator* that peaks and troughs over time.

## Trading the Crossover

MACD is greater than zero if the 12-day EMA is larger than the 26-day EMA. If MACD grows larger and larger, upside momentum is increasing. If the MACD is negative, and falling further, downside momentum is increasing.

At its simplest level, traders watch for crossovers as the MACD and signal lines develop

- A buy signal is generated when the MACD rises above the signal (aka going long),
- and a sell signal is generated when the MACD falls bellow the signal (aka going short).

If the MACD and signal line cross-over

- above zero, that indicates potentially bullish market behavior
- below zero, that indicates potentially rather more bearish sentiments

MACD, is unbounded; this means that you can’t necessarily judge whether a stock is overbought or oversold with MACD. However, over a longer-term, you may notice that MACD peaks at around the same level for many stocks; this means that as the MACD reaches a previous high, sentiment may soon change.

The MACD-signal crossover will potentially produce many trading signals; some of these may not always be the right time to change your market position. Hence traders usually interpret MACD in the context of other technical indicators, including the relative strength index and average true range.

## Trading Divergence in the Histogram

The MACD histogram isn’t a “histogram” in the statistical sense of the word. It’s simply a bar chart of the MACD minus the signal line. The bars of the histogram grow progressively larger as the price accelerates, and vice-versa.

The histogram, in essence, measures the strength of the price velocity, or momentum.

For example, consider this chart of the MACD and histogram of Exxon Mobile (ticker: XOM) for the year to date. It was generated with the free technical charting spreadsheet available at the bottom of this article, simply by entering a ticker symbol, two dates and clicking a button.

Prices reach a peak in both May and July, as marked on the chart. The peak in July is higher than the earlier peak. However, the histogram fails to break its earlier peak in July.

In essence, prices start to diverge from the histogram. This is known as divergence and signposts weakness in price movement, and a possible reversal. Prices, accordingly, fall steeply in August.

Unfortunately, this signal can often be a false-positive; hence divergence is usually interpreted with signals from other indicators.

## Using MACD and RSI to To Generate Buy-Sell Signals

Let’s use the principles discussed earlier to study a trading strategy for Exxon Mobil (ticker: XOM) for the year to date. The following plot gives the MACD, signal line, RSI (and close price) of XOM from 3^{rd} January 2013 to 2^{nd} September 2013.

As you can see MACD (in red) crosses over the signal line (in blue) on several clearly defined occasions. This gives strong buy/sell signals. A couple of observations can be made.

- The MACD/signal crossovers occur when the RSI is usually between 50 and 60
- The MACD/signal crossovers happen some time after the peaks and troughs in RSI. Eyeballing the plot gives a lag of around a week.

The crossover of the MACD and signal line at the end of July/beginning of August gives a big sell signal. This is preceded by a peak in RSI of 70 about ten days before that – that’s a sign of an overbought market.

These two indicators strongly signpost XOM’s falling price that occurs in August 2013.

**Plot Technical Trading Signals in Excel – Automatically!**

This Excel spreadsheet

- automatically downloads historical daily stock prices for a user-specified ticker, between two user-specified dates
- plots the MACD, RSI, ATR, 12- and 26-day exponential moving averages, and historical volatility.

Watch this video for a sneak peak of the spreadsheet.

All you do is enter the stock ticker, two dates, time windows for RSI, ATR and the volatility, and click a button. Behind the scenes, Excel connects to Yahoo finance, downloads the data, performs some calculations and plots the charts!

If you like this spreadsheet, please consider linking to http://investexcel.net from your social networking account or website.

**Download Excel Spreadsheet to Plot RSI, MACD, ATR, EMA and Historic Volatility from Ticker**

Can’t get the program to work this one gets a mod error winapp32… can you repost one with the correction in the code or send me one that is workable?

Are you using a Mac?

using win 7 cannot get it to run either has same error mdlwinapp32api would like to try it out thanks

john

What version of Excel are you using?

Hello Samir,

Please could you provide the password or the code,

PS: I cannot find my previous requests.

Thank you in advance,

J.

Have been using this speadsheet for pass month or so and still learning how the market swings etc. Today I note that Yahoo finance website did not have the previous’s day’s data (i.e. 28/10/2013) today 29/10/2013 Australian date. I can access this data after 9.30am each day. Could it be possible to have the option to select another data provider such as Google’s? (e.g. The Ebay data link for Google is: (http://www.google.com/finance/historical?q=NASDAQ%3AEBAY&ei=emJvUqiWEInHkgWoIw) or another website that you may know more of. Thanks

This was working great for a week. I just tried to run in and I get a runtime error 1004 everytime now. The data table is blank as well. Please help.

Try the updated version, and consider donating ðŸ™‚

Hello Samir

Can you kindly provide me with the password to this spreadsheet please.

I am not a programmer but I am keen to develop my Vba skills.

I am intersdted in applying mathematics, and financial computing to real worid challenges of developing countires.

I am more than happy to make a donation.

I have tried the version listed above with the same results. I’d be more than happy to donate, however I am not clear how that gives me the piece of mind that the tools will work without error.

Brett

I’ve just tried the worksheet, and it operates without error for me (on two different computers, both with Windows 7, Excel 2010).

Could you send me the updated error message please, or a screengrab.

Samir

I just tried it on my personal computer and it works perfectly fine. There must have been something put in place on my corporate computer to block the source data. I’ll capture the error message for you when I’m back in the office.

Thank you.

Samir,

Excellent work,

Could you please also integrate Elder Ray Bear and Bull data in your data sheet.

MayI also mention, that there is a little hic up with your date line at the MACD & Signal chart, should be on the bottom.

Again Excellent work!

Regards

Alf

Hi Samir,

I love your work and have just tried your spreadsheet. And your worksheet is great. May I know if I would like to change the parameters (say from MACD(12,26,9) to MACD(5,35,5)? How can I work this out? Thanks a lot.

Regards,

Keynes

Your RSI calculation doesn’t agree with yahoo finance

Hi Samir,

Great job.

Want more. Do you have RSI calculator for multiple stocks in one workbook?

Regards,

It appears that your spreadsheet is no longer working. When I tried to download statistical data I get the below error.

Run-time error ‘1004’:

Unable to open http://ichart.finace.yahoo.com/table …….(etc.)

The site reports that the item you requested could not be found.