CQG has added the CFTC Commitment of Traders data to the data feed. You can read about working with COT symbols here.
If you are interested in pulling the latest COT updates and historical data using RTD formulas there are a couple of things to know. The data is published by the CFTC late Friday afternoon with a report date of the previous Tuesday (unless there is a holiday). Therefore, this data is weekly data. You cannot see it in a CQG Quote Spreadsheet as the Quote Spreadsheet displays only today and yesterday’s data. RTD formulas that pull data that we see in the Quote Spreadsheet have "ContractData" in the RTD formula. Those RTD formulas using COT symbols will display blank values.
RTD formulas that have "StudyData" are pulling the same data we see on charts, in other words historical data. To display in Excel the latest COT data we need to pull the data using RTD weekly bar chart formulas. As detailed, the COT data is updated Friday afternoon using Tuesday for the report date. Therefore, to see the most recent COT data the Excel formula needs to check for this week (let’s say today is Wednesday) and if there is no data then pull last week’s data. This RTD formulas delivers that data:
=IF(RTD("cqg.rtd",,"StudyData","X.US.ZSENCDIFF", "Bar",, "Close","W","0","All",,,,"T")="", RTD("cqg.rtd",,"StudyData","X.US.ZSENCDIFF", "Bar",, "Close","W","-1","All",,,,"T"),RTD("cqg.rtd",,"StudyData","X.US.ZSENCDIFF", "Bar",, "Close","W","0","All",,,,"T"))
If you wanted to pull in historical data then you use the standard RTD StudyData formulas for Weekly bars with lookbacks of 0, -1, -2, -3, etc. However, if for some reason you wanted to pull daily bar COT data then there will be a blank cell for all cells except Tuesdays. When an Excel chart sees a blank cell it treats the value as zero. Consequently, the chart is visually unappealing as the line falls to zero between valid data points.
There is an Excel trick: If the RTD formula returns a blank cell then substitute the #N/A error, which is done by using NA() in the Excel formula. The Excel chart will simply skip over cells with the value being #N/A.
The sample spreadsheet below has all of the RTD formulas discussed above.
Available for CQG IC and CQG QTrader version 2019 and higher.
Please contact CQG and request the free CFTC Commitment of Traders enablement.