How to Buy Shares - Calculating Average Directional Movement Using Excel - ADX Formula

Updated on February 7, 2018
ngureco profile image

Ngureco has trained in Mathematics and Natural Sciences. She is a researcher with a passion for stock trading.

Average Directional Movement Indicator

If you plan to enter the business of trading shares or stocks for a living, then you are likely to come across Average Directional Movement (ADX). ADX has become a widely-used indicator by traders throughout the world. The Average Directional Index, ADX, was created in 1978 by J. Welles Wilder for determining the strength of a trend.

We start by defining an exponential moving average as a moving average with weighing factors which decreases exponentially, giving much more importance to recent prices while still not discarding older prices entirely. The exponential moving average is also called exponentially weighted moving average.

Calculating Average Directional Movement Using Excel

Here are the steps you need to take to compute Welles Wilder’s Average Directional Movement (ADX) using excel.

  1. If you decide to use a 14-day period, then insert the numbers 1 to 14 in column “A”. You need the sum total of the fourteen numbers which you will get by using the formula “=Sum(A1:A14)” which is equal to 105.
  2. Insert the stock’s high, low, and closing prices in each of columns “B”, “C” and “D” respectively.
  3. Compute Up-Move by inserting “= (B1-B2)” in column “E”.
  4. Compute Down-Move by inserting “= (C1-C2)” in column “F”.
  5. Check if Up-Move is greater than Down-Move by inserting in column “G” the formula “=if(abs(E1)>abs(F1),1,-1)”
  6. Check if Up-Move is greater than 0 by inserting in column “H” the formula “=if(E1>0,1,-1)”
  7. Check if Down-Move is greater than 0 by inserting in column “I” the formula “=if(F1>0,-1,0)”
  8. Compute +DM by inserting in column “J” the formula “= if(G1+H1=2,E1,0)”
  9. Compute -DM by inserting in column “K” the formula “= if((G1+I1)=-2,F1,0)”

True Range

Define “True-Range” as the largest of:

(a) Today's High - Today's Low

(b) Today's High - Yesterday's Close

(c) Yesterday's Close - Today's Low

10. Compute absolute (Today's High - Today's Low) by inserting in column “L” the formula “=abs(B1-C1)”

11. Compute absolute (Today's High - Yesterday's Close) by inserting in column “M” the formula “=abs(B1-D2)”

12. Compute absolute (Yesterday's Close - Today's Low) by inserting in column “N” the formula “=abs(D2-C1)”

13. Compute True-Range (TR) by inserting in column “O” the formula “=Max(L1:N1)”

Exponential Moving Average

Next, compute the following:

14. Exponential moving average (+DM14) of +DM by inserting in column “P” the formula “=((14*J1+13*J2+12*J3+11*J4+10*J5+9*J6+8*J7+7*J8+6*J9+5*J10+4*J11+3*J12+2*J13+1*J14))/105”

15. Exponential moving average (-DM14) of -DM by inserting in column “Q” the formula “=((14*K1+13*K2+12*K3+11*K4+10*K5+9*K6+8*K7+7*K8+6*K9+5*K10+4*K11+3*K12+2*K13+1*K14))/105”

16. Exponential moving average (TR14) of TR by inserting in column “R” the formula “=((14*O1+13*O2+12*O3+11*O4+10*O5+9*O6+8*O7+7*O8+6*O9+5*O10+4*O11+3*O12+2*O13+1*O14))/105”

Directional Indicators

Next, calculate the Directional Indicators as follows:

17. Directional Indicator (+DI14) = +DM14 divided by TR14 by inserting in column “S” the formula “=P1/R1”

18. Directional Indicator (-DI14) = -DM14 divided by TR14 by inserting in column “T” the formula “=Q1/R1”

Next, calculate the components of the Average Directional Movement Index (ADX)

19. Calculate the absolute DI Difference (+DI14 - -DI14) by inserting in column “U” the formula “=abs(S1-T1)”

20. Calculate DX = DI Difference divided by the sum of +DI14 and -DI14 by inserting in column “V” the formula “=U1/(sum(S1: T1))”

21. Calculate ADX = the exponential moving average of DX by inserting in column “W” the formula “=((14*V1+13*V2+12*V3+11*V4+10*V5+9*V6+8*V7+7*V8+6*V9+5*V10+4*V11+3*V12+2*V13+1*V14))/105”

Draw or Insert a Chart Using Excel

Hold and drag your formulas from columns “E” to column “W” downward in as much as you would want to go into the past.

Now select column “S”, ”T” and “W” data and draw or insert a chart using Excel. You will now have The Directional Movement System as developed by Welles Wilder and as shown in the image below which you can use for entering and exiting your trades. Once you learn how to use Directional Movement System in excel then you can later manipulate it to your liking and the sky will be the limit to what you can do with it.

Trades:

Buy shares or go long the stocks when +DI is above -DI and at least one of the following has happened:

1. ADX rises while +DI and ADX are above -DI

2. ADX turns up from below +DI and –DI

Close or exit your positions when +DI crosses below -DI

Short stocks or trade in downside direction when -DI is above +DI and at least one of the following have happened:

1. ADX rises while -DI and ADX are above +DI

2. ADX turns up from below +DI and –DI

Close or exit your positions when -DI crosses below +DI

Important: Always use automatic stop-loss orders which should preferably be with your stockbroker.Average Directional Movement, ADX, does not indicate trend direction. ADX indicates only the trend strength. ADX is a lagging indicator and traders must be aware of this. It is only after a trend has established that ADX will generate a signal. Average Directional Movement, ADX, will range between 0 and 1. Values below 0.20 indicate a weak trend and values above 0.40 indicates a strong trend.

ADX has become a widely-used indicator by traders through out the world
ADX has become a widely-used indicator by traders through out the world

Questions & Answers

    © 2009 ngureco

    Comments

      0 of 8192 characters used
      Post Comment

      • ngureco profile imageAUTHOR

        ngureco 

        6 years ago

        Arun,

        The most recent data is at the top of the column.

      • profile image

        Arun V 

        6 years ago

        Are you formulas based on the most decent data being at the top?

      • monicamelendez profile image

        monicamelendez 

        6 years ago from Salt Lake City

        I've been looking through a few of your hubs and I am super impressed. I'm learning a ton by reading your stuff. I've been trying to get really good at Excel for a while now.

      • profile image

        MUKESH 

        7 years ago

        THANKS A LOT,

        PLEASE SAVE EXCEL SHEET ALSO FOR DOLWLOADING

      • chamilj profile image

        chamilj 

        7 years ago from Sri Lanka

        Very useful article for stock traders. Thanks!

      • kjetil profile image

        kjetil 

        7 years ago

        I love trading patterns so I really enjoyed this hub.

        Thanks ngureco!

      • profile image

        david bhatti 

        8 years ago

        my first reaction-the above tutorial provided an introduction to understanding ADM. Understanding what charts on a broker's site aim to do has been a problem. I believe this tutorial and perhaps other like this will meet that requirement. Many thanks.

      • profile image

        harsh435 

        8 years ago

        I m searching Excelsheet for W.D. Gann Tracement in the same pettern.Can anybady provide.

      • Rismayanti profile image

        Rismayanti 

        8 years ago from Tropical Island

        informative

      • profile image

        commoditytrader 

        8 years ago

        Great Post! Thanks for the tips.

      • profile image

        vasu 

        8 years ago

        excellent

      • Securityace profile image

        Securityace 

        8 years ago from United States

        What a great explaination on how to trade using the ADX. I will give the spead sheet a try.

      • docrehab profile image

        docrehab 

        8 years ago from MIAMI, FL

        Nice!

      • profile image

        HASAN 

        8 years ago

        SOME ONE HAS TO UNDERSTAND MATHS VERY WELL!

      • profile image

        sam 

        8 years ago

        nice hub

      • xunlei profile image

        xunlei 

        8 years ago

        it is a nice hub , Thanks ?

      • profile image

        Heavensgates 

        8 years ago

        Ngureco, I'll get this one day! Thanks for the helpful hub, even if I haven't grasped it all yet.

      • profile image

        subirkrdatta 

        8 years ago

        A nice piece of information.

      • profile image

        giimasterone 

        8 years ago

        thank you it is useful.

      • Douglas45 profile image

        Douglas45 

        8 years ago from Winston-Salem, NC

        Great site. Thanks for the info.

      • vinner profile image

        vinner 

        8 years ago from India

        thanks for this wonderful piece of information

      • JYOTI KOTHARI profile image

        Jyoti Kothari 

        8 years ago from Jaipur

        very god descption with mathematical calculations.

        You may also like to visit some of mine about stock market.

        Thanks

        Jyoti Kothari

      • tim-tim profile image

        Priscilla Chan 

        8 years ago from Normal, Illinois

        It is so complicated to learn but it is good information. Thanks for sharing.

      • ediddy5 profile image

        ediddy5 

        8 years ago

        Very nice to know.

      • articleposter profile image

        articleposter 

        8 years ago

        Thanks for your time, always good read

      • mfresh7 profile image

        mfresh7 

        8 years ago from India

        This is cool. Can you give a downloadable example of the Excel sheet that you are building in this tutorial?

      • ForexCashBack profile image

        ForexCashBack 

        8 years ago from Corvallis, Oregon

        I created an automated trading system that was based on the ADX. Orders are placed when the +Di and -Di line reach a certain difference in value, and the position is placed according to what line is on top. J Welles Wilder also recommended in his book that the PSAR be used with the ADX to determine when to exit the market. I experimented with a 28 day period, mixed results.

      • StevenCavendish profile image

        StevenCavendish 

        8 years ago

        This is definitely a cut above the usual stock hubs. Thanks very much ngureco.

      • sudamaprasad profile image

        sudamaprasad 

        8 years ago

        good idea

      • profile image

        hybridway 

        9 years ago

        Great insight into penny stock. Info will assist novice interested in jumping into stock market investing. Thanks.

      • emdi profile image

        emdi 

        9 years ago

        Thanks for the nice hub.

      • Mitch King profile image

        Mitch King 

        9 years ago from Wilsoville, OR, USA

        Wow, very technical hub. For those looking to take over their own investments understanding these formulas is essential.

      working

      This website uses cookies

      As a user in the EEA, your approval is needed on a few things. To provide a better website experience, toughnickel.com uses cookies (and other similar technologies) and may collect, process, and share personal data. Please choose which areas of our service you consent to our doing so.

      For more information on managing or withdrawing consents and how we handle data, visit our Privacy Policy at: https://toughnickel.com/privacy-policy#gdpr

      Show Details
      Necessary
      HubPages Device IDThis is used to identify particular browsers or devices when the access the service, and is used for security reasons.
      LoginThis is necessary to sign in to the HubPages Service.
      Google RecaptchaThis is used to prevent bots and spam. (Privacy Policy)
      AkismetThis is used to detect comment spam. (Privacy Policy)
      HubPages Google AnalyticsThis is used to provide data on traffic to our website, all personally identifyable data is anonymized. (Privacy Policy)
      HubPages Traffic PixelThis is used to collect data on traffic to articles and other pages on our site. Unless you are signed in to a HubPages account, all personally identifiable information is anonymized.
      Amazon Web ServicesThis is a cloud services platform that we used to host our service. (Privacy Policy)
      CloudflareThis is a cloud CDN service that we use to efficiently deliver files required for our service to operate such as javascript, cascading style sheets, images, and videos. (Privacy Policy)
      Google Hosted LibrariesJavascript software libraries such as jQuery are loaded at endpoints on the googleapis.com or gstatic.com domains, for performance and efficiency reasons. (Privacy Policy)
      Features
      Google Custom SearchThis is feature allows you to search the site. (Privacy Policy)
      Google MapsSome articles have Google Maps embedded in them. (Privacy Policy)
      Google ChartsThis is used to display charts and graphs on articles and the author center. (Privacy Policy)
      Google AdSense Host APIThis service allows you to sign up for or associate a Google AdSense account with HubPages, so that you can earn money from ads on your articles. No data is shared unless you engage with this feature. (Privacy Policy)
      Google YouTubeSome articles have YouTube videos embedded in them. (Privacy Policy)
      VimeoSome articles have Vimeo videos embedded in them. (Privacy Policy)
      PaypalThis is used for a registered author who enrolls in the HubPages Earnings program and requests to be paid via PayPal. No data is shared with Paypal unless you engage with this feature. (Privacy Policy)
      Facebook LoginYou can use this to streamline signing up for, or signing in to your Hubpages account. No data is shared with Facebook unless you engage with this feature. (Privacy Policy)
      MavenThis supports the Maven widget and search functionality. (Privacy Policy)
      Marketing
      Google AdSenseThis is an ad network. (Privacy Policy)
      Google DoubleClickGoogle provides ad serving technology and runs an ad network. (Privacy Policy)
      Index ExchangeThis is an ad network. (Privacy Policy)
      SovrnThis is an ad network. (Privacy Policy)
      Facebook AdsThis is an ad network. (Privacy Policy)
      Amazon Unified Ad MarketplaceThis is an ad network. (Privacy Policy)
      AppNexusThis is an ad network. (Privacy Policy)
      OpenxThis is an ad network. (Privacy Policy)
      Rubicon ProjectThis is an ad network. (Privacy Policy)
      TripleLiftThis is an ad network. (Privacy Policy)
      Say MediaWe partner with Say Media to deliver ad campaigns on our sites. (Privacy Policy)
      Remarketing PixelsWe may use remarketing pixels from advertising networks such as Google AdWords, Bing Ads, and Facebook in order to advertise the HubPages Service to people that have visited our sites.
      Conversion Tracking PixelsWe may use conversion tracking pixels from advertising networks such as Google AdWords, Bing Ads, and Facebook in order to identify when an advertisement has successfully resulted in the desired action, such as signing up for the HubPages Service or publishing an article on the HubPages Service.
      Statistics
      Author Google AnalyticsThis is used to provide traffic data and reports to the authors of articles on the HubPages Service. (Privacy Policy)
      ComscoreComScore is a media measurement and analytics company providing marketing data and analytics to enterprises, media and advertising agencies, and publishers. Non-consent will result in ComScore only processing obfuscated personal data. (Privacy Policy)
      Amazon Tracking PixelSome articles display amazon products as part of the Amazon Affiliate program, this pixel provides traffic statistics for those products (Privacy Policy)