The CQG XL Toolkit Add-In for Microsoft Excel® pulls today's market data, orders data and open positions data into Excel via the Internet. Historical data is not available. CQG products do not need to be running. However, you cannot simultaneously pull market data from the XL Toolkit and a CQG product, such as IC or QTrader. The exchange requires separate fees.
The Add-In does require you to have an account with a CQG FCM partner, as this is an FCM enablement. Please contact your FCM.
One of the more popular features is the ability to pull in the current session’s orders data, such as fill prices, quantity filled, remaining quantity, time of placement and more into Excel. This feature is the topic of this article.
There are two issues using this feature: One, the data comes in as an array, which makes working with the data difficult, such as you cannot sort a column. Second, the data does not come in by time. The purpose of this article is to present steps in Excel so the order data can be automatically sorted by time. The latest orders are displayed at the top down to the oldest orders are at the bottom on the Main tab. There is a downloadable Excel sample available.
To start, let’s review the Order’s Data features in the XL Toolkit. Once, you have installed the XL Toolkit then click on the XL Toolkit displayed on the Excel ribbon, you log in using your trade routing credentials, not your CQG user ID and password. Then select the Orders Data icon.
The Orders Data parameters window allows you to select Publication Type. The dropdown allows you to select a specific Account, SalesSeries, Brokerage, or AllAuthorized. If you select a choice other than AllAuthorized, such as Account, then you need to enter in the ID of the account with quotation marks or you can use a cell reference displaying the ID number. You can get the IDs by choosing the Account Data icon.
You can set the spreadsheet to filter by Order Side: Buy orders or Sell orders. If you leave it blank then both Buy and Sell Orders data is pulled in.
The next choice is Order Status. This data details the order’s current status, such as “In Transit” to the exchange, Working (“AckPlace” or Acknowledged Placed), Filled Orders and others. Here, you could set the spreadsheet to display only Filled orders. If you choose to “Include all transactions” then a lot of rows will be used because every order will have a status of “In Transit” and then the status will change to AckPlace and once filled a new row is added for Filled. If you leave it blank then the Status column displays the latest status.
The Select Data fields includes the information listed in the table below.
| Count | Total number of Rows used |
| AccountName | Name of the Account |
| FCMNumber | FCM Number of the Account |
| EnteredByUser | Trader |
| OriginalOrderID | Order ID |
| OrderID | CQG Order ID |
| Side | Bur or Sell |
| Qty | Quantity of the order |
| FillQty | Quantity filled |
| RmngQty | Remaining working quantity |
| Instrument | Symbol |
| Type | Limit, Stop or Market order |
| Status | Latest Status updated |
| LimitPrice | Price if limit order |
| StopPrice | Price if stop order |
| AvgFillPrice | Average fill price |
| Duration | Order duration (GTC or Day) |
| GTD | Good until Date |
| GTT | Good until Time |
| PlaceTime | Date and Time order was placed (milliseconds) |
| StatusTime | Date and time of the latest update to the Status |
| Comment | Latest comment |
The sample Excel spreadsheet provided includes all of the data listed in this table. If you want to use the sample, but do not want to see all of the columns then on the “Main” tab, select the column, right-click and choose “Hide” near the bottom of the menu.
The second tab is named Data. The Main tab is pulling orders information from the Data tab using Excel’s VLOOKUP function. The Data tab uses columns A through V for pulling the orders’ data using the XL Toolkit. Column U is the Status Time. This is time the order’s status was updated. For example, a working limit order is filled and the status time details the time of the fill.
There is a problem with the Date and Time displayed by the XL Toolkit for Placed and Status Time, it is text: 2020-May-07 19:40:30.233000 and is not a value.
To illustrate, the Excel function =Today() gives you today’s date, which today happens to be 5/11/2020. If you selected that cell and modified it to display the value you would see 43962. Again, the XL Toolkit does not return a value for the Date and Time. You cannot rank text.
On the data tab columns W through AA converts column U to a value which can then be ranked. The latest Date and Time will be the largest and the oldest Date and Time will be the lowest ranked value.
Column U: Status Time (this is not a date and time that Excel will recognize)
Column W: Separates out the date from Column U
Column X: Converts the date in Column W to a date value Excel will recognize
Column Y: Separates out the Time from Column U. Custom formatting is used for the column to show hours, minutes, seconds and milliseconds. To do this use Format Cells/Number/Custom and enter [h]:mm:ss.000
Column Z: Merges the date and the time, such as 439580.819794363. Values to the left of the decimal is the date and values to the right of the decimal is time.
Column AA: Ranks the date and time using an Excel function that will not produce ties, which is a problem for VLOOKUP.
Excel’s IFERROR function is used to display empty cells and not #N/A or #VALUE.
With the data ranked, then columns AB through AV is pulling the data from Columns B through V and this table is used on the Main tab for looking up data ranked from 1 to 50 (Main tab, Column A). The most recent is first.
If you want the oldest to be first then on the Main tab, cell A2 enter: =Data!A2. This is the Count value. Next, in cell A3 enter: =IFERROR(IF(A2-1=0,"",A2-1),""). Copy and paste it down
This will pull data that is the oldest at the top and stop it at 0.
Download the XL Toolkit, 32-bit or 64-bit Excel: https://news.cqg.com/workspaces/main/2015/09/cqg-toolkit-for-excel-add-in.html
Download the sample Excel Orders Ranked by Time.