Most of our articles are centered across ‘Trend Following’ and its variations. We strongly believe that Trend is the best friend of every trader and would continue to be the same down the line. This article would focus on the most versatile Trend strength indicator used by Technical analysts know as Average Directional Index (ADX). This indicator aids in determining the strength of ongoing trend, and when combined with two supplementary indicators (+DI & -DI) it also helps in interpreting the direction of trend. We would learn how to trade with the help of these 3 indicators and always stay with the trend in correct direction. There is a downloadable Excel sheet at the end of the post where you can download real time stock quotes with ADX values.
Check out some of our very popular Excel based trading systems in the below link:
What is ADX?
Average Direction Index also known as ADX is a non-directional trend strength indicator. Its values oscillate between 0 to 100, higher the value higher is the strength of trend. The default look-back period off ADX to calculate trend strength is 14 periods, but various other periods are used depending on the relative volatility of stock or index. It is often used in conjunction with two other indicators, +DI and -DI which determines the direction of trend. ADX was developed in 1978 by J. Welles Wilder.
ADX is normally plotted as a single line in a stock chart. Below is the price chart with ADX.
ADX Calculation
In order to accurately calculate ADX, you would need at-least 150 periods of High, Low and Close values of the stock or index. Higher the period, more accurate is the ADX value as it depends on Wilder’s smoothing techniques. Below are the detailed steps for calculation:
- Calculate the True Range (TR). True range is the Max of:
- Current High minus Current Low
- Current High minus Previous Close
- Current Low minus Previous Close
- Calculate the directional movement +DM1 and -DM1. Directional movement is positive when the current high minus the previous high is greater than the previous low minus the current low. This so-called Plus Directional Movement (+DM) then equals the current high minus the prior high, provided it is positive. A negative value would simply be entered as zero. Directional movement is negative (minus) when the previous low minus the current low is greater than the current high minus the previous high. This so-called Minus Directional Movement (-DM) equals the prior low minus the current low, provided it is positive. A negative value would simply be entered as zero.
- Calculate 14 period moving average of True range (TR14). Here the period 14 corresponds to the ADX period 14.
- Calculate 14 period moving average of +DM1 and -DM1. It would be called +DM14 and -DM14.
- Calculate +DI14 and -DI14.
- +DI14 is the ratio of +DM14 and TR14 expressed in % terms.
- -DI14 is the ratio of -DM14 and TR14 expressed in % terms.
- Calculate Directional Movement Index (DX). It equals the absolute value of +DI14 minus -DI14 divided by the sum of +DI14 and – DI14. It is also expressed in % terms.
- Calculate Average directional index (ADX). It is the 14 period moving average of DX.
Finally, don’t worry about the complex nature of these calculations as we have everything covered in the excel sheet which can be downloaded from the end of this post.
ADX Interpretation
ADX is a true Trend strength indicator and its values oscillate between 0 to 100. Below tables from Investopedia shows the interpretation of various ADX ranges:
| ADX Value | Trend Strength |
| 0-25 | Absent or Weak Trend |
| 25-50 | Strong Trend |
| 50-75 | Very Strong Trend |
| 75-100 | Extremely Strong Trend |
Trend Strength Indicator Excel Sheet Overview
This is an semi-automated Excel sheet which updates by itself on a single click. You would just need to enter the stock name, exchange and timeframe and the Excel sheet would do the job for you. Apart from calculating the ADX and DI values, this excel sheet also indicates Trend strength and direction. ADX value below 30 is interpreted as weak trend, while ADX value above 30 is interpreted as strong trend. The direction of the trend is calculated based on +DI and -DI values.
See the below screenshots:
How to use this Excel Sheet
Step 1: Download the Excel file from the end of this post.
Step 2: Open this Excel file and make sure you are connected to internet. Please accept if it asks to enable Macros and Data connections.
Step 3: Input the Symbol Name, Exchange Name, Interval and Number of Days.
Step 4: Click on Get Data button. The data would be automatically downloaded and ADX values would be caluclated. This data can be downloaded for max 15 days and the minimum interval is 1 minute.
Download link for Trend Strength Indicator Excel Sheet
Please see the below link to download ADX Trend strength indicator excel Sheet. Please let us know if you have any feedback or comments.
Thank you so much sir…….
sir why is the adx period in the excel sheet maxi to 15 days, you had previously mentioned it should be at least 150 days & further is the adx calculation in the excel sheet is for 14 days
Hi Manoj,
We meant that it should be minimum 150 periods. 1 Day has close to 400 periods in 1 minute timeframe.
sir minimum time frame is 6 hours in excel sheet, if we want ADX reading for daily candle i.e. 360 minutes is it possible to get
Hi Manoj,
This Excel sheet is only for Intraday timeframe. We would create another one for daily TF.
sir if i change script name not update how can i solve this
\
Hi Kumar,
What is the error you are facing?
Admin Sir , this excel sheet can used in commodity trading?
Hi Gerald,
Yes ADX can be used for commodity trading, but currently MCX exchange is not supported in the excel sheet.
Dear Sir,
How to add script.
Thanks
chetan
Hi Chethan,
Please enterit in the ‘Symbol Name’ input
Dear sir,
the exel sheet it’s realy helpful..
is adx values changed automatically intraday like “OHL Strategy” excel sheet.. or we want refresh ..
Hi Lokesh,
In this Excel sheet, you would need to manually refresh the values by clicking on ‘Get Data’ button.
Dear Sir,
Request you to please share “Supertrend scanner” based on EOD on excel sheet (automatically update) so that we can see active buying in filtered stock list and select stocks for further trading.
Regards,
VinDiCode
Dear Sir,
May I know how to modify it to support Hong Kong stock? Thank you!
Best regards,
Destiny
Try entering Stock Symbol and Exchange Code in the space provided in Excel sheet.
Dear Sir,
I have tried it but failed. For example, Symbol: 5 and Exchange: HKG. Thank you!
Best regards,
Destiny
Dear Sir,
I tried the following symbol: https://www.google.com/finance/getprices?q=1288&x=HKG&i=300&p=1d&f=d,o,h,l,c,v
and found that it failed at: timeStamp = (Mid(timeStampRaw, 2, Len(timeStampRaw) – 1))
timeStamp value is 0 and it failed at here. Thank you!
Best regards,
Destiny
Dear All,
Since HKG stocks return format has another additional row “DATA_SESSIONS”, hence the program need to adjust a little bit to support HKG stocks. I have tried and it works, but it would be better to letter author to confirm. Thank you!
Best regards,
Destiny
Dear Sir,
Can you help with the list of Symbols to be used in the ADX sheet. Thank you!
Hi Maneesh,
You can input any symbol traded in the respective exchange.
Hi. This is a great indicators of trend . As you mentioned this is for intraday. Question 1. Are the time period used are 1 minute interval. Question 2. Has this strategy been back tested, if yes then could you please publish results.
Hi Dhiraj,
You have to input time period in the Excel sheet (See interval input). No, this strategy has not been backtested yet, but I have used it along with some trend following systems and it works well.
Whether it can be used for NSE F&O Scrips, if so what is format that one should use for Symbol Code of the Scrip
Currently it only support cash market scrips
Sir, The sheet has stopped updating today 14th Sep 2017. I think the Google URL might have changed. Kindly teach me how to fix this.
Hi Nitesh,
Google recently stopped providing API access to the live data. We are looking for alternate free methods to update this sheet.
Hi Sir, I changed the url in the macro to https://finance.google.com/finance/getprices? now it seems to be working fine so far.
Hi NITESH in this link https://finance.google.com/finance/getprices? didn’t work for me can u guide how?
Open the excel sheet and click on the View tab, then click on Macros, then a dialog box will open, so click on Edit, then in the code there will be qurl written followed by a url in double quotation marks. Just copy the url I have provided and paste it in between the double qoutation marks replacing the old url which was already there. now, the paragraph of code should look something like this:
qurl = “https://finance.google.com/finance/getprices?” & _
“q=” & ticker & _
“&x=” & exchange & _
“&i=” & interval & _
“&p=” & numPastTradingDays & “d” & _
“&f=d,o,h,l,c,v”
Now, just close the macro page and save the excel sheet. it should work fine now.
Hi Nitesh,
Thanks a ton for this correction! You saved us a lot of time. I have updated the sheet in the post.
You’re very welcome, sir!
Nitesh murti & Admin both are thanks a lot sir late reply any way thank you again
Sir, pls send me all the excel for investment horizon of short term as well as long term and of course intraday too.
Hi Sunil,
Please find all our excel sheet at the below link:
https://tradingtuitions.com/category/categories/trading-excel-sheets/
It seems, that the formula is derived from the stockcharts, I downloaded the excel sheet, but getting wrong data, for example take hdil, I am getting different values with 14 day period than in live data. Also may I know where you posted for daily ADX, I want to use it.
Hi Kiran,
Formula for ADX is universal everywhere. Values may vary slightly depending on the data accuracy and number of candles tested.
Hey guys,
This is great, but can you help. I’m having trouble pulling data from the London Stock Exchange. Is the symbol LSE? FTSE100?
Hi James,
Try with LON
Sir
Excel sheet get blank by clicking on get data tav
Hi Jignesh,
Its working at my end. Can you please re-check if you have enabled macros.
M GETTING ERROR
.Refresh BackgroundQuery:=False
WHAT TO DO??
HI! Will mcx exchange support this excel sheet?
Unable to open.
http://finance.google.com/finance/getprices?q=infy&x=nse&i=600&p
Cannot download the information you requested
As NITESH MURTI advise
I have changed to this (below), but some error showing as ” Compile error.
qurl = “https://finance.google.com/finance/getprices?” & _
“q=” & ticker & _
“&x=” & exchange & _
“&i=” & interval & _
“&p=” & numPastTradingDays & “d” & _
“&f=d,o,h,l,c,v”
Respected Sir,
As you’re also aware that google finance is not able to pick the data now days so this sheet it not working.