How to do Average Directional Index (ADX) in Excel

Introduction

The Average Directional Index (ADX) in combination with Minus Directional Indicator (-DI) and Plus Directional Indicator represent a powerful group of directional movement indicators. Welles Wilder had originally developed these trading indicators for commodities and currencies in mind, but the concept can easily be extended to stock trading. The detailed calculations are complex and not meant for the faint hearted. Don’t worry, the fully automated ADX in Excel model takes care of the heavy lifting calculations behind the scenes!

Directional Movement Basics

Plus and minus directional movement are the pillars of a Directional Movement System. The Directional Movement Index or DMI is a technical indicator which is calculated by comparing the current price with the previous price range. DMI then shows the result as an upward directional index (+DI) or a downward directional index (-DI). DMI also calculates the strength of the upward or downward movement and shows the trend srength line also called as Average Directional Index (ADX). ADX in Excel model precisely calculates and plots both the DMI and ADX lines.

+DI and -DI are displayed as two distinct lines above or below the stock price. +DI line is colored as green, whereas +DI is colored as red. When the red line is above the green line, it means a downtrend in price. When the green is above the red line, it indicates uptrend in price. When there are frequent crossovers between red and green lines, there likely isn’t a price trend going on, and the price is moving sideways.

ADX trend line is the third line on the chart, and it displays the strength of the trend. Alongwith ADX, +DI and -DI form a powerful tool for the traders. These three indicators can be separated into separate charts to reduce clutter. Some traders may only choose to view the ADX for trend strength, while others may prefer +DI and -DI to confirm price trend.

ADX Calculation Step by Step

ADX in Excel calculations are based on Wilder recommended 14-period indicator setting:

  1. Calculate Real Range (RR), Positive Directional Movement (+DM) and Minus Directional Movement (-DM) for each trading period.
  2. Smooth each period’s +DM, -DM, and RR prices over 14 day periods. This technique is quite similar to Exponential Moving Average (EMA). The calculation step starts with the sum of first 14 periods. Smoothing starts at second 14-period calculation step and continues till the end.
  3. Take the ratio of 14-day smoothed +DM with 14-day smoothed Real Range (RR) to find the 14-day Plus Directional Indicator or +DI14. The +DI14 is the green Plus Directional Indicator line +DI that is plotted against the Stock Price and ADX line.
  4. Take the ratio of 14-day smoothed Minus Directional Movement -DM with 14-day smoothed Real Range RR to find the 14-day Minus Directional Indicator -DI14. -DI14 is the red Minus Directional Indicator line -DI that is plotted against the Stock Price and ADX line.
  5. Directional Index DX then becomes the absolute value of the ratio of +DI14 less -DI14 with sum of +DI14 and -DI14.
  6. Now it is time to calculate ADX line as a last step. The first ADX value is a simple average of 14-day DX. Second ADX value onwards are smoothed by taking the product of 14-day ADX value with 13, and adding the most recent DX value and dividing this total by 14.

Trend Indicators and Crossovers

Average Directional Index (ADX) is used to measure the magnitude of a trend, not the actual direction. Direction movements are defined by +DI and -DI indicators. A bullish signal occurs when +DI is greater than -DI, while a bearish signal occurs when -DI is greater. Crossovers of these directional indicators can be combined with ADX metric for a complete trading analysis.

Wilder smoothing techniques can be applied in both commodity and currency trading. The examples in Wilder’s book are based on assets, and not just stocks. The movement indicators can be applied towards stocks as well. Few stocks have price characteristics which are similar to commodities. Blue chip stocks and stocks with low volatility will not generate enough signals based on Wilder’s metrics. Traders may need to adjust the indicator settings and the signal metrics according to the type of the stock.

Conclusion

Directional Movement shows whether downside or upside movement is stronger, and ADX shows the strength or weakness of that movement. The ADX line shows the strength of the price move. An asset is considered to be trending when the ADX line is above 25, and ranging when the ADX line is below 25.

Many traders also consider an ADX reading above 20 as trending, and below 20 as non-trending. When used in combination with Directional Movement, ADX can confirm trade signals. For upward trending strategies, the ADX should ideally be above 20 (or 25) for taking trades in potential uptrends or downtrends. For trading strategies that trade on sideways movement (ranges) then ADX should be below 20 (or 25).

The spreadsheet model retrieves quote information from a Web Service through a lightweight data-interchange format called JSON or JavaScript Object Notification. If this is your first time programming JSON in Excel, you must add certain excel libraries to start utilizing Bloomberg Markets Web Service through VBA.

Under Developer->Visual Basic->Tools->References, add the highlighted objects.

VBA-JSON-References

2 thoughts on “How to do Average Directional Index (ADX) in Excel

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.