This post walks the reader through building option chains in Excel. The CQG RTD Toolkit offers this functionality, however, the same feature can be implemented using Excel's CONCATENATE function. This post details both approaches.
The CQG RTD Toolkit is installed with CQG IC and QTrader. To create a list of Options Symbols click Derivative List.
This dialog box opens.
You can enter in the contract symbol or a cell reference with the symbol. The latter is recommended because the symbol list is an "array" in Excel which cannot be edited. By using a cell reference you can easily change the symbol. The next image is an example of the call options list of ten call symbols, five above the at-the-money (ATM) and five below the ATM.
Notice above in the formula bar there is not a request for a specific option symbol with a strike price. The way the formula works is it pulls in the ATM in the center. What can happen is the ATM can change. Then the list of symbols will change. If you want a fixed set of symbols, then copy the symbols list and paste as "Values." In the next image the symbols were pasted as values in column F and the symbols are fixed.
With a list of symbols, the CQG Quote Spreadsheet V2 can be used to write RTD formulas for market data and option Greeks. Select Options and use the CQG Quote Spreadsheet V2 to build a single row of data.
Add the data requirements, right-click in the box to the left of the symbol and select "Copy to Excel."
Paste the formulas next to the first symbol. The data brought over from the CQG Quote Spreadsheet V2 will include the symbol. Delete that cell and shift the formulas to the left. Next, replace the symbols in each RTD formula with the cell reference using Excel's "Find and Replace" feature.
The image below shows the sample downloadable spreadsheet with the put option symbols added and column names.
Sheet2 of the sample downloadable spreadsheet does not use the CQG RTD Toolkit. It uses Excel's CONCATENATE function to build the symbol.
For the first symbol in cell C9:
=CONCATENATE($C$5,$D$5,$E$5,$F$5)
The next symbol includes the Step value added to the strike:
=CONCATENATE($C$5,$D$5,$E$5,($F$5+$G$5))
The final symbol is 10 time the step added to the strike:
=CONCATENATE($C$5,$D$5,$E$5,($F$5+(10*$G$5)))
The image above is the CONCATENATE version of the Options data.
Bringing options data into Excel can lead to creating custom displays that meet your exact requirements. You can use Excel features, such as heat mapping the contract volume column to highlight high volume situations.
Requirements: CQG Integrated Client or QTrader, and Excel 2016 (locally installed, not in the Cloud) or more recent.