HOT TOPICS LIST
- MACD
- Fibonacci
- RSI
- Gann
- ADXR
- Stochastics
- Volume
- Triangles
- Futures
- Cycles
- Volatility
- ZIGZAG
- MESA
- Retracement
- Aroon
INDICATORS LIST
LIST OF TOPICS
PRINT THIS ARTICLE
by Dennis D. Peterson
Constructing an Excel spreadsheet requires that each step of the ADX computation be stated. Shown are each of the calculations.
Position: N/A
Dennis D. Peterson
Market index trading on a daily basis.
PRINT THIS ARTICLE
ADXR
ADX Excel Spreadsheet
08/11/00 08:03:34 AMby Dennis D. Peterson
Constructing an Excel spreadsheet requires that each step of the ADX computation be stated. Shown are each of the calculations.
Position: N/A
| As promised, this posting gives the Excel spreadsheet formulas for the ADX calculations along with a more detailed explanation of the five possible ADX cases that can occur. This is reprinted in part from Volume 11, Issue 4,of the STOCKS & COMMODITIES article, "The ADX" by Thom Hartle. Figure 1 shows the five ADX cases that have to be resolved. In example A, the second day's trading range is higher than the first day's trading range, indicating positive directional movement. In example B, the second day's trading range is below the first day's trading range, an indication of negative directional movement. Example C is more complicated because the second day's range is both lower and higher than the first day's range. Directional movement is only considered to be up, down or not present. Therefore, the larger part of the day's range extending beyond the previous day's range is used to identify directional movement. In example C, the largest part of the second day is higher; consequently, the directional movement is positive. In example D, the largest part of the second day's range is lower so that the directional movement is negative. In example E, the second day's range is within the first day's range so the directional movement is zero. |
| In the Excel 4.0 spreadsheet (Figure 2), the first calculation for ADX is the true range value (TR). This is performed in column E. The formula for cell E3 is: =MAX(B3-C3,ABS(B3-D2),ABS(C3-D2)) Next, column F determines the positive directional movement or returns zero if there is no positive directional movement. The formula for cell F3 is: =IF(B3-B2>C2-C3,MAX(B3-B2,0),0) Column G calculates the negative directional movement or returns zero if there is no negative directional movement. The formula for cell G3 is: =IF(C2-C3>B3-B2,MAX(C2-C3,0),0) The daily calculations are volatile and so the data needs to be smoothed. First, sum the last 14 periods for TR, +DM and - DM. The formula for summing the TR is in cell H16: =SUM(E3:E16) |
|
| Figure 2: ADX spreadsheet. Here is the output for the S&P 500 from January 1 to February 11, 1992. |
| |
| The formula for summing the +DM is in cell I16: =SUM(F3:F16) The formula for summing the -DM is in cell J16: =SUM(G3:G16) The smoothing formula for the TR14 column begins at cell H17: =TRUNC((H16-(H16/14)+E17),3) The smoothing formula subtracts 1/14th of yesterday's TR14 from yesterday's TR14 and then adds today's TR value. The truncating function is used to calculate the indicator as close as possible to the developer of the ADX's original form of calculation (which was done by hand). The smoothing formula for the +DM14 column begins at cell I17: =TRUNC((I16-(I16/14)+F17),3) The smoothing formula subtracts 1/14th of yesterday's +DM14 from yesterday's +DM14 and then adds today's +DM value. The smoothing formula for the -DM14 column begins at cell J17: =TRUNC((J16-(J16/14)+G17),3) The smoothing formula subtracts 1/14th of yesterday's -DM14 value from yesterday's -DM14 and then adds today's -DM value. Now we have a 14-day smoothed sum of TR, +DM and -DM. The next step is to calculate the ratios of +DM and -DM to TR. The ratios are called the +directional indicator (+DI) and -directional indicator (-DI). The formula for the +DI column begins at cell K16: =INT(100*(I16/H16)) |
| The formula for the +DI column begins at cell L16: =INT(100*(J16/H16)) The integer function (INT) is used because the original developer dropped the values after the decimal in the original work on the ADX indicator. The next step is to calculate the absolute value of the difference between the +DI and the -DI. This is done in column M and the formula for cell M16: =ABS(K16-L16) [Please note that in last week's article I needed to mention that DX is the ratio of the absolute value of the difference between +DI and -DI, since DX is positive.] The next column calculates the sum of the +DI and -DI. The formula for cell N16: =K16+L16 The next step is to calculate the DX, which is the ratio of the absolute value of the difference between the +DI and the -DI divided by the sum of the +DI and the -DI. This is done in column O. The formula for cell O16: =INT(100*(M16/N16)) The final step is smoothing the DX to arrive at the value of the ADX. First, average the last 14 days of DX values. The formula for cell P28: =AVERAGE(O15:O28) The smoothing process uses yesterday's ADX value multiplied by 13, and then add today's DX value. Finally, divide this sum by 14. The formula for cell P29: =INT(((P28*13)+O29)/14) Additional reading Wilder, J. Welles [1978]. New Concepts in Technical Trading Systems, Trend Research. |
Market index trading on a daily basis.
| Title: | Staff Writer |
| Company: | Technical Analysis, Inc. |
| Address: | 4757 California Ave SW |
| Seattle, WA 98116-4499 | |
| Phone # for sales: | 206 938 0570 |
| Fax: | 206 938 1307 |
| Website: | www.traders.com |
| E-mail address: | dpeterson@traders.com |
Traders' Resource Links | |
| Charting the Stock Market: The Wyckoff Method -- Books | |
| Working-Money.com -- Online Trading Services | |
| Traders.com Advantage -- Online Trading Services | |
| Technical Analysis of Stocks & Commodities -- Publications and Newsletters | |
| Working Money, at Working-Money.com -- Publications and Newsletters | |
| Traders.com Advantage -- Publications and Newsletters | |
| Professional Traders Starter Kit -- Software | |
Click here for more information about our publications!
Comments
Date: / /Rank: 4Comment: Please, put it directly in an spreadsheet we could download
Rgds,
Phil
Date: 03/16/10Rank: 5Comment: Thank you Mr. Dennis Peterson for this example. I have been able to follow it through have sucessfully embedded this model in my software. Great stuff.
Kishor. Mistry
Date: 05/31/17Rank: 5Comment:
|
Request Information From Our Sponsors
- StockCharts.com, Inc.
- Candle Patterns
- Candlestick Charting Explained
- Intermarket Technical Analysis
- John Murphy on Chart Analysis
- John Murphy's Chart Pattern Recognition
- John Murphy's Market Message
- MurphyExplainsMarketAnalysis-Intermarket Analysis
- MurphyExplainsMarketAnalysis-Visual Analysis
- StockCharts.com
- Technical Analysis of the Financial Markets
- The Visual Investor
- VectorVest, Inc.
- Executive Premier Workshop
- One-Day Options Course
- OptionsPro
- Retirement Income Workshop
- Sure-Fire Trading Systems (VectorVest, Inc.)
- Trading as a Business Workshop
- VectorVest 7 EOD
- VectorVest 7 RealTime/IntraDay
- VectorVest AutoTester
- VectorVest Educational Services
- VectorVest OnLine
- VectorVest Options Analyzer
- VectorVest ProGraphics v6.0
- VectorVest ProTrader 7
- VectorVest RealTime Derby Tool
- VectorVest Simulator
- VectorVest Variator
- VectorVest Watchdog