This post details steps using the Excel CQG RTD Toolkit Add-in to pull in open interest data for an options series based on the same expiration. These same steps can be used to pull in other options related data. The Excel CQG RTD Toolkit Add-in is installed with CQG IC or QTrader.
To start, a symbol list is built for Call Options and Put Options using the Live Cattle (Symbol: GLE). Select the CQG RTD Toolkit tab on the Excel ribbon and then select Derivative List.
This dialog opens. Enter the root symbol, here "GLE". Then select Options, and as of this writing the first expiration series is December. Next, decide on the number of strikes "less than at the money (ATM)" and the number of strikes "greater than at the money (ATM).
The rules for listing options from the CME website for Live Cattle:
"Strikes listed for 50% of the underlying settlement price above and below the at-the-money strike at $0.02 per pound increment plus dynamic strikes at $0.02 per pound increment above and below the highest and lowest pre-listed strikes.
Additional strikes listed for 25% of the underlying settlement price above and below the at-the-money strike at $0.01 per pound increment in the nearest 3 standard monthly contracts and 1 serial contract." In other words, there is a section near the ATM that the strikes are $0.01 apart and then the strikes are $0.02 apart.
If you want to see all the options symbols available, one simple way is to review CQG's "All Contracts (AllCon)" display and determine the lowest and highest strike prices for an Expiry.
From the image below: C.US.GLEZ258800 is the lowest and C.US.GLEZ2537200 is the highest.
Through trial and error, the number of strikes less than the ATM was set to 100 and the number of strikes greater than the ATM was set to 125. This included padding the list of symbols with extra requests. The add-in returns a blank cell if there is no symbol.
Also, the list of symbols pulled into Excel is listed as an array in Excel and cannot be edited. The symbols can be copied & pasted as values.
The call options symbol list was entered into cell B1.
The put options symbol list was entered into cell E1.
The blank cells in the next image is an example that actual symbols less than the ATM were 95.
Next, the RTD formulas for contract open interest are added.
The Excel formula is modified to display a blank cell if there is no symbol. This formula is entered into cell C1 and copied down.
=IF(B1="","",CQGContractData(B1, "COI", "-1", "T"))
This formula is entered into cell F1 and copied down:
=IF(E1="","",CQGContractData(E1, "COI", "-1", "T"))
To sum the total open interest for the calls in cell H2 is this formula:
=SUM(C.:.C)
To sum the total open interest for the puts in cell H4 is this formula:
=SUM(F.:.F)
These two Excel functions use Excel's Trim Range function.
In cell H5 this RTD formulas is entered.
=RTD("cqg.rtd", ,"ContractData", "C.US.GLE", "LongDescription",, "T")As there is no strike, the formula will always return the at-the-money option.
As a check, the put options data open interest matches the CME Daily Bulletin.
This post illustrated how to use the CQG RTD Toolkit to build a series of option symbols based on an expiration date and as an example, the individual contract's open interest was pulled into Excel using RTD formulas. Other options data, such as implied volatility, can also be pulled into Excel.
Requires CQG Integrated Client or CQG QTrader, and Excel 365 or more recent locally installed, not in the cloud.