This post details the different techniques for pulling Depth-of-Market (DOM) data into Excel.
There are three ways:
- C&P RTD formulas for Level 1 DOM data from the various quote dashboards
- Use the CQG RTD Toolkit
- Use the XL Toolkit
Copy & Paste RTD Formulas from the Quote Spreadsheet 2.0. Right-click on the cell and select "Copy to Excel."
Select the cell in the Excel sheet and hit key combination CTRL+V.
This RTD formula is pasted into the Excel cell.
=RTD("CQG.RTD", ,"ContractData", "EP", "Bid",, "T")An entire row of formulas can be copied & pasted over. Select the cell with the symbol, then right-click and select "Copy to Excel" and the entire row of RTD formulas are on the clipboard.
The list of RTD formulas below is on the clipboard.
=RTD("CQG.RTD", ,"ContractData", "EP", "LastQuoteToday",, "T")
=RTD("CQG.RTD", ,"ContractData", "EP", "NetLastQuoteToday",, "T")
=RTD("CQG.RTD", ,"ContractData", "EP", "PerCentNetLastTrade",, "T")
=RTD("CQG.RTD", ,"ContractData", "EP", "PerCentNetLastTrade",, "T")
=RTD("CQG.RTD", ,"ContractData", "EP", "MT_LastBidVolume",, "T")
=RTD("CQG.RTD", ,"ContractData", "EP", "Bid",, "T")
=RTD("CQG.RTD", ,"ContractData", "EP", "Ask",, "T")
=RTD("CQG.RTD", ,"ContractData", "EP", "MT_LastAskVolume",, "T")Select cell B1 and hit key combination CTRL+V. All the RTD formulas are pasted into their respective cells.
Notice all the RTD formula are using the symbol "EP". Changing the RTD formulas to use a cell reference is simple. On the Excel ribbon go to Home>Find & Select>Replace.
Find "EP" and replace with cell A1 (no quotes). Select "Replace All". The image below shows the RTD formulas now reference cell A1 for the formulas. You can copy and paste this row down and then enter the appropriate symbols in column A.
Next, the CQG RTD Toolkit enables you to copy and paste the Exchange DOM order book. The CQG RTD Toolkit add-in is installed in Excel when either Integrated Client or QTrader is installed.
Select DOM data.
This dialog below opens. Enter a contract symbol or a cell reference. Select the fields: Price, Time, Type, and Volume. Then choose the number of rows. Choose the price format and then, the location of the first output.
The output looks like this image below. A trick: select the cells, then Home>Format>Autofit Column width.
Notice, the curly brackets {} are used. This indicates the data is returned as an array and individual cells cannot be edited.
CQG One and Desktop users can use the XL Toolkit to pull in DOM data.
This requires an enablement to be turned on by your FCM. There may be a fee. Using your credentials from the FCM log on and then select DOM Data.
Similar to the CQG RTD Toolkit you can enter a symbol or a cell reference.
The time is Greenwich Mean Time (GMT) and includes milliseconds.
Two samples are provided, one uses CQG RTD Toolkit formulas, and one uses XL Toolkit formulas.
CQG RTD Toolkit requires CQG Integrated Client or CQG QTrader, and Excel 2016 or more recent. XL Toolkit requires an FCM enablement download.