This Microsoft Excel® dashboard tracks four average true range study values for seven markets. On the Parameters tab you can enter the symbol, the number of decimals for prices, and the session parameter - either the entire session (enter All) or the primary session (enter PrimaryOnly). The time frame for the charts is entered on the main display tab.
For each market you can track four Average True Range (ATR) study values, such as 5-, 15-, or 60-minute, and daily ATR studies. On the Parameters tab you enter the look-back period and the time frame for each ATR study calculation.
The main display shows the current daily open, high, low, last, and net change as well as the best bid and best ask. The ATR portion for each market lists the four ATR study parameters. For example, 20 : 5 indicates the ATR study is using a twenty-bar look-back applied to a five-minute chart. Next is a ten-bar sparkline histogram bar chart showing the changes in the ATR study. Finally, the NC/ATR column is the net change divided by the ATR study. If it is a twenty-bar ATR study applied to a five-minute chart, the current net change of the five-minute chart is divided by the current ATR study.
The bottom-right section displays a heat-mapped-by-row summary table of each market’s ATR studies. Finally, there is a one-day percent change candlestick chart of the seven markets.
To call for the ATR values in Excel use these RTD formulas (one is for a simple moving average and the other is for an exponential moving average):
= RTD("cqg.rtd",,"StudyData", "EP", "ATR", "MAType=Sim,Period=20", "ATR","5","0","ALL",,,"FALSE","T") = RTD("cqg.rtd",,"StudyData", "EP", "ATR", "MAType=Exp,Period=20", "ATR","5","0","ALL",,,"FALSE","T")
Make sure to lower your Excel RealTimeData (RTD) throttle to 0 milliseconds. Learn how to do that here.
Requires CQG Integrated Client or CQG QTrader, data enablements for all symbols displayed in this spreadsheet, and Excel 2010 or more recent.