Publication number: ELQ-16787-1
View all versions & Certificate
MACD Indicator Excel Template – Technical Analysis
This free Excel template can be used to calculate MACD (Moving Average Convergence Divergence) indicator for any stock you are interested in.
What is the MACD indicator?
MACD is used by a lot of traders since it captures multiple aspects of stock performance. It not only helps identify trends, but it can also measure momentum. It involves a lot more components than the technical indicators we have seen before.
How to calculate MACD?
First, we calculate two different Moving Averages (one fast and one slow). Then, we take the difference in values between those two Moving Averages, called the MACD Line.
We then calculate an EMA of the MACD line and that is called the Signal line. The signal line is thus an indicator of an indicator.
Finally, we calculate the difference between MACD and the Signal line and display it as a MACD histogram that oscillates above and below zero.
Three different elements are shown on the chart typically when you view the MACD indicator.
MACD Line = (12-day EMA – 26-day EMA)
Signal Line = 9-day EMA of MACD Line
MACD Histogram = MACD Line – Signal Line
The MACD histogram values are plotted as red and green bars. A bar is green when the MACD histogram value is greater than 0. A red bar indicates that its value is lesser than 0.
A general interpretation of MACD is that when MACD is positive and the histogram value is increasing, then upside momentum is increasing. When MACD is negative and the histogram value is decreasing, downside momentum increases.
Some key signals observed using the MACD indicator are below.
Bullish Signal Line Crossovers occur when MACD Line crosses above Signal Line.
Bearish Signal Line Crossovers occur when MACD Line crosses below Signal Line.
Bullish Zero Line Crossovers occur when MACD Line crosses above the Zero Line and go from negative to positive.
Bearish Zero Line Crossovers occur when MACD Line crosses below the Zero Line and go from positive to negative.
MACD Excel Template
The template will help you automatically create the MACD indicator and calculate the trading signals instantly for any stock.
The Dashed light blue line is the Source (by default the Closing price) of the Stock. The Blue line is the MACD signal, and the Yellow line is the Signal Line.
To pull live Stock data from the services you would need either Microsoft 365 Subscription Plans for PC and Web
How to create MACD indicator in Excel?
Type in a stock sticker symbol on the top left.
If Excel does not recognize your stock symbol, it will pop up this data selector box.
You can select the stock you are interested in.
The template can pull up to 5 years of price history by default. You can choose how much history to display on the chart easily using the slicer buttons.
The options are 5 Days, Month Till Date, 1 Month, 3 Months, 6 Months, Year Till Date, 1 Year, 5 Years.
The template allows price history at 3 different intervals.
Daily/Weekly/Monthly are the options.
1. Source: Closing price is the one that is used for calculations typically. However, you can choose from Open, High, Low, Close or Volume.
2. Fast EMA Length: You can type in a length in periods. The commonly used is 12.
3. Slow EMA Length: You can type in a length in periods. The commonly used is 26.
4. Signal Smoothing: You can type in a length in periods. The commonly used is 9.
The chart will update automatically based on all the above inputs.
The template shows two types of signals (Signal Line Crossovers, Zero Line Crossovers) on the chart.
They are represented as 4 different markers on the Chart.
Bullish Signal Line Crossover
Bearish Signal Line Crossover
Bullish Zero Line Crossover
Bearish Zero Line crossover
In addition, if the latest day has a signal, it will be displayed below the current price.
On the top right you can see the maximum of the last trade time of these currencies.
The default time that Microsoft provides is UTC. The offset is set to -7 hrs, to convert to pacific time zone.
You can modify this if you need the time in a different time zone.
This Best Practice includes
1 Excel Template
Indzara offers you this Best Practice for free!
download for free
Add to bookmarks