Excel for Options-on-Futures Information

Options offer a wide variety of strategies for traders. CQG provides a powerful options analytics package. A number of the analytics tools, such as theoretical value, implied volatility, and the Greeks, provide market information that can be brought from CQG into Microsoft Excel® using RTD formulas.

CQG's Options Window and Quote SpreadSheet (QSS) include a feature that allows you to right-click and select Copy to Excel to paste the correct RTD formula directly into Excel. For example, this is what I paste into Excel if I select the last price cell in the QSS version 2.0:

=RTD("cqg.rtd", ,"ContractData", "C.CLE", "LastQuoteToday",, "T")

Let's say you have added a lot of options data columns to the QSS 2.0. If you select a symbol, rightclick, and copy to Excel, you can paste in a collection of RTD formulas like those in the attached downloadable spreadsheet. I added cell titles and formatting, such as time values, after I pasted the RTD formulas. These RTD calls will use the options model you have selected in CQG.

If you right-click in the CQG Options Window and select Copy to Excel, you will see a list of choices. For example, here is the RTD formula for the Option Price:

=RTD("cqg.rtd", ,"ContractData", "C.US.CLEK155050", "OptionPrice",, "T")

Notice that it is not a wild card symbol but the full option symbol. This is an important point. You are strongly encouraged to use this feature to get the correct CQG option symbol. Many times people build their own CQG Excel dashboards using incorrect options symbols, which will lead to problems with Excel updating. One example is using the correct digits for the year, which is two digits, i.e., 15.

We offer this RTD Contract Labels RTD spreadsheet:

http://news.cqg.com/workspaces/main/2012/09/cqg-to-rtd-excel-syntax-for-...

There is a tab titled Options. You will see the same RTD formulas that are available from the copy and paste feature from the Options Window or the Quote SpreadSheet. There is an additional set of formulas that allow you to choose the model. For example, here is the RTD formula for implied volatility using the Black-Scholes model:

=RTD("cqg.rtd", , "ContractData", "OptVal(C.CLE,ImpliedVolatility,Black-Scholes)", "Close")

You can enter a different model for the calculations. For model choices, visit:

http://help.cqg.com/cqgic/16/#!Documents/modelpreferences1.htm

Using Excel with CQG data provides new ways of monitoring market data that can increase your workflow efficiency.

Requires CQG Integrated Client or CQG QTrader, data enablements for all symbols used in this spreadsheet, and Excel 2010 or higher.

Downloads

Disclaimer

Trading and investment carry a high level of risk, and CQG, Inc. does not make any recommendations for buying or selling any financial instruments. We offer educational information on ways to use our sophisticated CQG trading tools, but it is up to our customers and other readers to make their own trading and investment decisions or to consult with a registered investment advisor. The opinions expressed here are solely those of the author and do not reflect the opinions of CQG, Inc. or its affiliates.