ArtsAutosBooksBusinessEducationEntertainmentFamilyFashionFoodGamesGenderHealthHolidaysHomeHubPagesPersonal FinancePetsPoliticsReligionSportsTechnologyTravel
  • »
  • Personal Finance»
  • Investing in Stocks, Bonds, Real Estate, More

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

Updated on June 6, 2012

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. Exponential moving average is also called exponentially weighted moving average.

Calculating Average Directional Movement Using Excel

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

1. If you decide to use a 14-days 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 exciting your trades. Once you learn how to use Directional Movement System in excel then you can latter 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

If you have liked this article, and you would want this page to keep up and improved, you can help by purchasing some great items from Amazon by following Amazon links and widgets on this page. A free way to help would be to link back to this webpage from your web page, blog, or discussion forums.

The Author’s page is designed to help beginners and average readers make some money as an extra income to supplement what they may be earning elsewhere - details of which you can find in My Page, if you will.

Comments

    0 of 8192 characters used
    Post Comment

    • Mitch King profile image

      Mitch King 7 years ago from Wilsoville, OR, USA

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

    • emdi profile image

      emdi 7 years ago

      Thanks for the nice hub.

    • profile image

      hybridway 7 years ago

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

    • sudamaprasad profile image

      sudamaprasad 7 years ago

      good idea

    • StevenCavendish profile image

      StevenCavendish 7 years ago

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

    • ForexCashBack profile image

      ForexCashBack 7 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.

    • mfresh7 profile image

      mfresh7 7 years ago from India

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

    • articleposter profile image

      articleposter 7 years ago

      Thanks for your time, always good read

    • ediddy5 profile image

      ediddy5 7 years ago

      Very nice to know.

    • tim-tim profile image

      Priscilla Chan 7 years ago from Normal, Illinois

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

    • JYOTI KOTHARI profile image

      Jyoti Kothari 7 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

    • vinner profile image

      vinner 7 years ago from India

      thanks for this wonderful piece of information

    • Douglas45 profile image

      Douglas45 7 years ago from Winston-Salem, NC

      Great site. Thanks for the info.

    • profile image

      giimasterone 7 years ago

      thank you it is useful.

    • profile image

      subirkrdatta 7 years ago

      A nice piece of information.

    • profile image

      Heavensgates 7 years ago

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

    • xunlei profile image

      xunlei 7 years ago

      it is a nice hub , Thanks ?

    • profile image

      sam 7 years ago

      nice hub

    • profile image

      HASAN 7 years ago

      SOME ONE HAS TO UNDERSTAND MATHS VERY WELL!

    • docrehab profile image

      docrehab 7 years ago from MIAMI, FL

      Nice!

    • Securityace profile image

      Securityace 7 years ago from United States

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

    • profile image

      vasu 7 years ago

      excellent

    • profile image

      commoditytrader 7 years ago

      Great Post! Thanks for the tips.

    • Rismayanti profile image

      Rismayanti 7 years ago from Tropical Island

      informative

    • profile image

      harsh435 6 years ago

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

    • profile image

      david bhatti 6 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.

    • kjetil profile image

      kjetil 6 years ago

      I love trading patterns so I really enjoyed this hub.

      Thanks ngureco!

    • chamilj profile image

      chamilj 6 years ago from Sri Lanka

      Very useful article for stock traders. Thanks!

    • profile image

      MUKESH 6 years ago

      THANKS A LOT,

      PLEASE SAVE EXCEL SHEET ALSO FOR DOLWLOADING

    • monicamelendez profile image

      monicamelendez 4 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

      Arun V 4 years ago

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

    • ngureco profile image
      Author

      ngureco 4 years ago

      Arun,

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

    Click to Rate This Article