Enter your keyword(s) below and then hit enter

Close search dropdown

Exporting DOM Data Into Excel

January 19, 2018 in

There is an RTD call for depth-of-market (DOM) data for futures markets. You call the DOM data by symbol and queue number. You can pull the bid or ask price, bid or ask volume, total traded volume for each price and the time of the last update.

Getting the Depth of Market (DOM) data into excel allows you to do custom calculations based on the data and refine your edge in the markets.

If this interests you, there are many other RTD calls worth checking out. Learn more here. Additionally, here is a CQG video explaining the RTD add-in for Excel.

The best ask price for the symbol EP (the E-mini S&P 500) is queue 1:

=RTD("cqg.rtd",,"DOMData","EP","Price","1","T")

For the amount of contracts offered at the best ask, use:

=RTD("cqg.rtd",,"DOMData","EP","Volume","1","D")

And for the time of the latest update, use:

=RTD("cqg.rtd",,"DOMData","EP","Time","1","T")

You must format the cells in Excel to use time. This sample spreadsheet formats the time column using minutes, seconds, and tenths of a second.

The best bid for symbol EP (the E-mini S&P 500) is queue -1:

=RTD("cqg.rtd",,"DOMData","EP","Price","-1","T")

For the amount of contracts bid at the best bid, use:

=RTD("cqg.rtd",,"DOMData","EP","Volume","-1","T")

And for the time of the latest update, use:

=RTD("cqg.rtd",,"DOMData","EP","Time","-1","T")

CME limits the number of DOM data queue levels for the E-mini S&P to the best ask and nine queues above the best ask (1 through 10):

=RTD("cqg.rtd",,"DOMData","EP","Price","10","T"

and nine queues below the best bid (-1 through -10):

=RTD("cqg.rtd",,"DOMData","EP","Price","-10","T")

Other markets may not have a limit on the number of queue levels.

This sample spreadsheet has two versions of the RTD calls for DOM data. The first version is hard coded for the symbol and the queue level, such as:

=RTD("cqg.rtd",,"DOMData","EP","Price","5","T")

The second version is linked to cell H2 for the symbols and to column H for the queue levels:

=RTD("cqg.rtd",,"DOMData",$H$2,"Price",H9,"T")

You can change the symbols and add additional queue levels.

The second version also includes a RTD call for the Footprint Operator, which calls the current traded volume by price. The price data is the data in column I:

=IFERROR(RTD("cqg.rtd",,"StudyData","FPVol(FootprintOp ("&$H$2&", 0),"&I12&")", "Bar",, "Close","D","0","all",,,,"T"),"")

The RTD call is wrapped with an IFERROR function. If the data is momentarily unavailable, the cell is blank instead of displaying an #NA error.

Thanks to Thom Hartle who is Director of Application Services at CQG for creating this article and spreadsheet. You can reach him at thartle [at] cqg.com if you have questions about it.

CQG posts additional Excel workspaces that might interest you on their blog. There are lots of really cool Excel worksheets they have created and they greatly extend the power of the platform.