You are here: CME Direct Excel Integration > Excel Integration > Inserting Functions

/*]]>*/

Inserting Functions

Use the Excel fx (function) field to insert functions that retrieve data from the CME Direct Trading Client. This includes contract data, market data, fills data and trade data.

Note: You can also look up contract code via the Instrument Detail view.

CME Direct functions include the following:

 

  1. To insert a function:
  2. From the Excel toolbar, click the fx button.

The Insert Function window appears.

  1. From the Or select a category drop-down, select CME Direct.

The available CME Direct functions appear in the window.

  1. Select a function and click OK.

A Function Arguments window appears (see CMED.C function and CMED.MD function).

 

Instrument Details

Use the Instrument Details view to locate a CMED Contract Code for use in Excel.

 

  1. To find the correct code for a contract:
  2. In the Trading Grid, click on a Product cell.
  3. From the main toolbar, click Menu, and select Add View.
  4. Click Instrument Details.

The Instrument Details view displays the code for the product you selected

.

 

CMED.C

Use the CMED.C user-defined lookup function to find the CME Direct code for a contract.

Tip: This code can be used in other Excel functions (e.g. market data).

Contract lookup can be performed in one of two ways:

Note: You can enter a code using either the product code (e.g. CL) or the product name (WTI Future).

Note: If no matching CME Globex code is found, an error message appears (e.g. #VALUE).

 

  1. To look up a contract code using the CMED.C function:
  2. From the Insert Function window (illustrated above), select CMED.C and click OK.

A Function Arguments window appears.

  1. In the Search text field, enter a product and period code (e.g. CL201412) or product name, and click OK.

The CME Direct contract code appears in Excel, in the last cell you highlighted.

  1. Click in the cell to display your search text in the fx field.

Only certain tradable items are applicable when looking up contracts. They are as follows:

  • Future Outrights
  • Instrument Spreads
  • Period Spreads
  • FX Period Spreads
  • Butterfly
  • Condor
  • Quarter Strip
  • Year Strip
  • Strip Spread

 

 

 

 

Using the Concatenate Function

Use Excel's concatenate function in conjunction with the CMED.C function to combine cells and look up a contract using different Product and Term cells than those currently displayed.

  1. To use the CMED.C and concatenate functions together to look up a contract:
  2. Add a row to your Excel grid.

  1. Enter a new Product and Term in the appropriate cells.

  1. In an empty cell (e.g. C5), enter the following function using the applicable column and row designations: =CMED.C(CONCATENATE(A5, " ",B5)).

  1. Click Enter.

The Contract Code field populates and the grid displays any related orders.

 

CMED.CD

Use the CMED.CD function to look up contract details. The CMED.CD function allows for the return of any contract information accessible via the Instrument Details view. Using this function, Traders and Brokers can automatically retrieve static data in a spreadsheet and implement the logic of their choosing (e.g. retrieve and then sort order expirations).

 

  1. To look up contract details:
  2. Populate a spreadsheet using drag-and-drop or some other applicable CME Direct procedure.
  3. From the Insert Function window, select CMED.CD and click OK.

A Function Arguments window appears.

  1. In the Contract Code field, enter the column and row of the contract code for which you want details (e.g. D9).

Note: The Contract Code is also accessible from the CMED.C function.

  1. In the Name of contract details field, enter the field name for which you want details (e.g. "Term").

The contract data displays directly in the Functions Arguments window.

The following fields are available for both futures and options contracts:

  • ContractDescription
  • GlobexInstrumentCode
  • Exchange
  • ProductCategory
  • ClearportProductCode
  • GlobexProductCode
  • Term
  • Strategy
  • ExpirationDate
  • ActivationDate
  • TickSize

 

For options contracts, the following additional fields are available:

  • UnderlyingFuture
  • Strike
   

 

CMED.F

Use the CMED.F function to populate a table of fills in Excel. The table will be created in the cell below the one in which the function is entered, and will grow (optionally up to a specified maximum number of rows) as fills arrive.

Important: This function will overwrite existing data in the cells below where it is entered. It is recommended that you use this function on its own, blank, worksheet.

 

  1. To generate fills:
  2. Populate a range of cells with the columns you want in your table. Possible values include:
  • ContractCode
  • Side
  • Slot
  • Source
  • Token
  • OrderId
  • OrderSide
  • OrderContractCode
  • OrderOwner
  • OrderOwnership
  • Price
  • FilledQuantity
  • TradeDate
  • IsLeg
  • IsStrategy
  • Filled
  • Account
  • Notes
   
  1. From the Insert Function window, select CMED.F and click OK.

A Function Arguments window appears.

  1. Optional: In the Columns field, enter the range containing the column names, or click and drag in your spreadsheet to select.

  1. Optional: Enter dates in the Start date and End date fields in your normal date format (e.g. 03/05/2015) – this limits the date range for returned fills, allowing you also to perform a historical query.
  2. Optional: Enter a maximum number of rows in the Records limit field.
  3. Optional: Enter FALSE in the Headers field to suppress the display of column headers.

Note: Only the text “Function is valid” displays in the Function Arguments window.

  1. To display fills, click OK.

 

CMED.MD

Use the CMED.MD function to subscribe to and display real-time market data from CME Direct. For Traders and Brokers, this function subscribes a single cell to a single market data statistic and can then be used to drive calculations and formulae in Excel.

The following market data attributes are available for subscription:

  • BidQuantity
  • BidPrice
  • OfferPrice
  • OfferQuantity
  • BestBidQuantity
  • BestBidPrice
  • BestOfferPrice
  • BestOfferQuantity
  • LastTradePrice
  • SettlementPrice
  • HighestTradePrice
  • LowestTradePrice
  • HighestBid
  • LowestOffer
  • OpenTradePrice
  • ClosePrice
  • LastTradeTime
  • NetChangePercentage
  • TheoreticalSettlementPrice
 

 

  1. To subscribe to market data:
  2. From the Insert Function window, select CMED.MD and click OK.

A Function Arguments window appears.

  1. Enter values for the following three market depth parameters, and click OK.

Note: The Contract Code is the output from the CMED.C function.

The Function is applied to the cell in which you last clicked, and displays in the fx field .

 

CMED.S

Use the CMED.S function to display real-time status information about the CMED add-in.

The CMED.S function displays values for the following status fields:

  • Status
  • CurrentSubscriptions
  • ContractLookupQueueLength
  • NameLookupQueueLength
  • SubscriptionQueueLength
  • MarketDataEventsPerSeconde
  • ClientVersion
  • ExcelAddInVersion
  • UserName
  • SenderSubID
  • ApiInstances
  • ActiveApiInstances
  • MaximumSubscriptions
   

Note: This list includes the code for returning individual values. Use the {=CMED.S("ALL")} array (Ctrl-Shift-Enter or CSE) formula to return all values at once.

 

  1. To use the CMED.S function:

In the following example, you will use the CMED.S function to check your connection status.

  1. Open Excel, and click to highlight a cell.
  2. From the Insert Function window, open a CMED.S Function Argument window.
  3. In the Name of status field, type "status".

  1. Click OK.

The applicable status appears in Excel.

 

CMED.T

Use the CMED.T function to populate a table of OTC trades in Excel. The table will be created in the cell below the one in which the function is entered, and will grow (optionally up to a specified maximum number of rows) as fills arrive.

Important: This function will overwrite existing data in the cells below where it is entered. It is recommended that you use this function on its own, blank, worksheet.

 

  1. To generate trades:
  2. Populate a range of cells with the columns you want in your table. Possible values are:
  • ContactCode
  • DealId
  • TradeId
  • ParentTradeId
  • Price
  • TradedQuantity
  • TradeDate
  • CreatedDate
  • UpdatedDate
  • ExecTime
  • OrderTime
  • MatchTime
  • IsLeg
  • IsStrategy
  • IsStrip
  • Cancelled
  • Buyer
  • BuyerCompany
  • BuyerAccount
  • BuyerClearer
  • IsBuyerAggressor
  • BuyerNotes
  • Seller
  • SellerCompany
  • SellerAccount
  • SellerClearer
  • IsSellerAggressor
  • SellerNotes
  • DealNotes
  • Status
  • StatusMsg
 
  1. From the Insert Function window, select CMED.T and click OK.

A Function Arguments window appears.

  1. Optional: In the Columns field, enter the range containing the column names, or click and drag in your spreadsheet to select.

  1. Optional: Enter dates in the Start date and End date fields in your normal date format (e.g. 03/05/2015) – this limits the date range for returned fills, allowing you also to perform a historical query.
  2. Optional: Enter a maximum number of rows in the Records limit field.
  3. Optional: Enter FALSE in the Headers field to suppress the display of column headers.

Note: Only the text “Function is valid” displays in the Function Arguments window.

  1. To see fills, click OK.

 

CMED.P

Use the CMED.P function to find the CME Direct code for a product. The CMED.P function finds products across both CME Globex and CME ClearPort venues.

 

CMED.PD

Use the CMED.PD function to return details about a CME Direct product.

 

CMED.MA

Use the CMED.MA function to retrieve Market Activity for a specific product in Excel. The table, created in the cell below the one in which the function is entered, grows (optionally up to a specified maximum number of rows) as Market Activity data arrives.

Note: The CMED.MA function does not retrieve historical Market Activity.

 

Error Types

When entering a function, the following errors may appear:


Copyright CME Group. All rights reserved.