Tricks to Building Excel Spreadsheets from CQG

When designing Excel® spreadsheets like the example below, there are a few shortcuts you can use to make the process easier. Here, we will use the CQG Symbol Search and Quote SpreadSheet to add RealTimeData (RTD) formulas for displaying market values in a spreadsheet. This sample spreadsheet tracks equity index futures contracts.

The first step is to identify the most actively-traded equity index markets. To do this, use the following steps:

1. Open the CQG Symbol Search by clicking the Symbols toolbar button.
2. Click the Type tab.
3. Click Futures.
4. Click Indices.
5. Select Indices and Equity by clicking the boxes.
6. To see the most actively-traded contracts, click the title of the Volume column to sort by volume.

To add symbols to the Quote SpreadSheet, select the symbols individually or drag your mouse over the group of symbols, right-click, and then click Copy.

A great feature in the CQG Symbol Search is the ability to paste selected symbols directly into a CQG Quote SpreadSheet for displaying market data.

Use these steps to paste symbols into a Quote SpreadSheet:

1. Open a new Quote SpreadSheet by clicking the Quote toolbar button and then clicking Quote SpreadSheet.
2. Right-click the first cell.
3. Hover over Edit. A menu list will display.
4. Click Paste.

Alternatively, you can select the first cell and press Ctrl + V.

To configure the Quote SpreadSheet for the market data you want to display in Excel, do the following:

1. Click the Setup toolbar button.
2. Click Customize Columns to display the dialog box.
3. Select the market data values to be added to the Quote SpreadSheet.

The displayed market values in the Quote SpreadSheet can be copied as RTD formulas and pasted into Excel.

First, make sure that your settings will paste as RTD formulas. Here are the steps to check your settings:

1. Click the Setup toolbar button.
2. Click System Preferences.
3. Click the Misc tab.
4. Uncheck the "Copy DDE Links to Excel" box.

Any market data values copied from the Quote SpreadSheet will be pasted into Excel using the RTD formula.

Use the following steps to add the RTD formulas to the Excel spreadsheet:

1. Right-click the cell in the Quote SpreadSheet.
2. Click Copy to Excel.
3. Paste the RTD formula into Excel by clicking the Excel cell and then pressing Ctrl + V.

For example, here is the "Last Quote Today" for the symbol EP:

=RTD("CQG.RTD", ,"ContractData", "EP", "LastQuoteToday")

Another useful step is to select one column to be the symbol column and then reference that column for the RTD formulas. For example, in the spreadsheet above, the C column is used for symbols. Cell C4 is the symbol EP. After pasting the RTD formula into the Excel cell, we need to change EP in the RTD formula above to a cell location, such as C4 (note: do not use quotes around the cell location in the RTD Formula):

=RTD("CQG.RTD", ,"ContractData", C4, "LastQuoteToday")

Now, you can enter symbols into column C, then copy and paste this RTD formula, and it will automatically check the C column cell for the symbol.

Following steps like these will make designing Excel spreadsheets a fast operation.