CQG charts have a setting called the BATS Charts Preferences. Here, you select what data to use to build the chart. You can use the default setting where the chart is built using trade and settlement data. You can uncheck Use default and elect to build the chart using best bid and best ask data. This is helpful when you are charting deferred contracts that do not trade very often but new bids and asks are being placed in the exchange’s order matching engine. For example, to see just the bids and asks for data, uncheck Use default and select Bid and Ask.
The next two charts are 1-second sub-minute bar charts. The chart on the left is displaying just trade data and the chart on the right is displaying bid and ask data and has many more data points. Notice that the bid and ask chart still has gaps when the bid or ask price is not updated during the building of the 1-second bar chart. Also, both bids and asks are building the bars.
Excel users can pull in this same bid and ask data using RTD formulas for the custom function Filter Tick Bate:
FILTERTICKBATE(HOE?2,2) pulls in the ask price for the second heating oil contract.
FILTERTICKBATE(HOE?2,1) pulls in the bid price for the second heating oil contract.
The Filter Tick Bate function can be used in an RTD formula for a study based on a time bar chart, such as a 1-minute chart.
Ask: = RTD("cqg.rtd",,"StudyData", "FILTERTICKBATE(HOE?2,2)", "Bar", "", "Close","1","0", "All", "", "","False","T")
Bid: = RTD("cqg.rtd",,"StudyData", "FILTERTICKBATE(HOE?2,1)", "Bar", "", "Close","1","0", "All", "", "","False","T")
You can pull in the open, high, and low bid and ask values in the 1-minute bar by replacing “Close” with either open, high, or low. You can pull the same data into Excel using a sub-minute chart type.
Ask: = RTD("cqg.rtd",,"StudyData", "FILTERTICKBATE(SUBMINUTE(HOE?2,10,Regular),2)", "Bar", "", "Close",,"0", "all", "", "","False","T")
Bid: = RTD("cqg.rtd",,"StudyData", "FILTERTICKBATE(SUBMINUTE(HOE?2,10,Regular),1)", "Bar", "", "Close",,"0", "all", "", "","False","T")
The above two RTD formulas are using a 10-second time frame. To use a different time, such as 1 second, replace the 10 right after the symbol (SUBMINUTE(HOE?2,1,Regular). To pull in historical data, replace the "0" with "-1", "-2", "-3", etc.
The sample spreadsheet provides you with RTD formulas for pulling in open, high, low, and close bid and ask data using 10-second sub-minute and 1-minute time frames.