Indzara

MACD Indicator Excel Template – Technical Analysis

In this article, we will be discussing the MACD (Moving Average Convergence Divergence) technical indicator that is used to analyze stock performance.  MACD is one of the popular technical indicators used by traders.

I will provide

  • A brief introduction to the theory or concept behind MACD indicator
  • A demo of the template and how you can use it to calculate MACD for any stock you are interested in.
MACD Technical Indicator in Excel

What is 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?

  1. First, we calculate two different Moving Averages (one fast and one slow). Then, we take the difference in values between those two Moving Averages and that is called the MACD Line.
  2. We then calculate an EMA of the MACD line and that is called Signal line. Signal line is thus an indicator of an indicator.
  3. Finally, we calculate difference between MACD and Signal line and display as a MACD histogram which oscillates above and below zero.

Three different elements are shown on the chart typically when you view 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.

How Donchian Channels indicator is calculated

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, then downside momentum is increasing.

Trading Signals

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.

TradingView

MACD Excel Template

The template will help you create the MACD indicator automatically and also calculate the trading signals instantly for any stock.

MACD Indicator with trading signals

The Dashed light blue line is the Source (by default the Closing price) of the Stock. The Blue line is the MACD signal, the Yellow line the Signal Line.

Download

Video Demo

Requirements

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.

MACD Technical Indicator Sample in Excel

If Excel does not recognize your stock symbol, it will pop up this data selector box.

Select the Stock

You can select the stock you are interested in.

Period

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.

Period-for-the-Stock-price-history
Period-for-the-Stock-price-history

The options are 5 Days, Month Till Date, 1 Month, 3 Months, 6 Months, Year Till Date, 1 Year, 5 Years.

Interval

The template allows price history at 3 different intervals.

Interval-of-Stock-price-history
Interval-of-Stock-price-history

Daily/Weekly/Monthly are the options.

Input Parameters

MACD Input parameters
  • Source: Closing price is the one that is used for calculations typically. However, you can choose from Open, High, Low, Close or Volume.
  • Fast EMA Length: You can type in a length in periods. The commonly used is 12.
  • Slow EMA Length: You can type in a length in periods. The commonly used is 26.
  • 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.

Signals

The template shows two types of signals (Signal Line Crossovers, Zero Line Crossovers) on the chart.

MACD Indicator with Trading Signals

They are represented as 4 different markers on the Chart.

  1. Bullish Signal Line Crossover
  2. Bearish Signal Line Crossover
  3. Bullish Zero Line Crossover
  4. Bearish Zero Line crossover

In addition, if the latest day has a signal, it will be displayed below the current price.

MACD

Timezone

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.

Visit https://savvytime.com/converter/utc to view offset.

How to refresh data?

How to refresh Stocks data
How to refresh Stocks data

Resources

MACD – TradingView

For more Excel Templates for Stock Market, visit indzara.com/stock-market-templates

Feedback

Please share your feedback on this template in the Comments below. If you find this useful, please share with your friends who may benefit from this content.

Related Templates

6 Comments

  • It says i need a password to unprotect and use – what is the password?

    Reply
  • Please how can i calculate this MACD on smaller time frames, lets say 30mins, 1hr and 4hrs time frame. the reason i am asking is because you said 12 period means 12 days. if i want to calculate trades on 30mins time frame does it mean i should get the closing price for the past 12 30mins instead of past 12 days?

    Please help me to understand how to apply in smaller time frames

    Reply
    • Thank you for showing interest in our template.

      We use excel STOCKHISTORY function to get the stock data from Microsoft Stock database and currently, we do not have an option to choose 30 mins or 1hr or 4 hrs interval. We will implement the same if the option is available in future.

      Best wishes.

      Reply
  • Very impressed with your MACD indicator. How can I obtain input data for the Australian market for use with your MACD technical indicator in excel?

    Reply
    • We are glad that you are impressed with our template.

      Currently, the stock data’s are retrieved from Microsoft data base and I have tried the following stock:
      ASX LIMITED (XASX:ASX) – Returns all the output
      BRAMBLES LIMITED (XASX:BXB) – All stock information is not available, since the Microsoft stock data type is at initial launch and all stock ticker symbols are not yet available in Microsoft Database.

      Best wishes.

      Reply

Leave a Reply

Your email address will not be published. Required fields are marked *