ICE Private Order Feed to Database

ICE Private Order Feed to Database

Database Schema

Execution Report (MsgType = ''8") 

Allocation Report (MsgType = ''J")

ICE-to-Database mapping

Execution Report (MsgType = ''8")

Table: ICEPOFReports

Usage: Table holds the summary of reported orders.

Fields

Name

FIX tag

Req’d

Description

ExecID

17 (ExecID)

Y

Key field. Unique execution ID for this Execution Report.

NOTE: The ExecID is only unique per side per market per trading session.

Symbol

55 (Symbol)

Y

Key field. Market ID.

Side

54 (Side)

Y

Key field. Side of order.

Supported value(s):

1 = Buy

2 = Sell

SourceFeed

-

Y

Key field. Always set to ‘ICE POF’.

ExecType

150 (ExecType)

Y

Key fieldIndicates the reason for the Execution Report.

Supported value(s):

0 = New

1 = Partial Fill

2 = Fill

4 = Canceled

5 = Replace

8 = Rejected

D = Restated

G = Trade Correct (Adapted from FIX 4.3)

H = Trade Cancel (Adapted from FIX 4.3)

TransactTime

60 (TransactTime)

Y

Key field.

Time the transaction represented by this Execution Report occurred. The precision is controlled by the TimeStampPreference (Tag 9007) on theLogonRequest Message.
Time/date combination represented in UTC (Universal Time Coordinated, also known as “GMT”) in either YYYYMMDD-HH:MM:SS(whole seconds) or YYYYMMDD-HH:MM:SS.sss* format, colons, dash, and period required.

Valid values: YYYY = 0000-9999, MM = 01-12, DD = 01-31, HH = 00-23, MM = 00-59, SS = 00-60 (60 only if UTC leap second), sss* = fractions of seconds. The fractions of seconds may be empty when no fractions of seconds are conveyed (in such a case the period is not conveyed), it may include 3 digits to convey milliseconds, 6 digits to convey microseconds, 9 digits to convey nanoseconds, 12 digits to convey picoseconds.

If TransactTime(60) tag isn't specified in the Execution Report, then the current time is inserted

TransactTimeWasNULL

-

N

N - the TransactTime field value was specified in the Execution Report

Y - the TransactTime field value wasn't specified in the Execution Report (it has been generated by JS)

 

PossDupFlag

43(PossDupFlag)

N

Indicates possible retransmission of message with this sequence number

PossResend

97(PossResend)

N

Indicates that message may contain information that has been sent under another sequence number.

SendingTime

52 (SendingTime)

Y

Date and time whenreportwas received.

OrderID

37 (OrderID)

Y

Unique identifier for Order as assigned by sell-side (broker, exchange, ECN).

NOTE: The OrderID is only unique per side per market per trading session.

ClOrdID

11 (ClOrdID)

C

Unique identifier for Order as assigned by the buy-side (institution, broker, intermediary etc.)

OrigClOrdID

41 (OrigClOrdID)

C

ClOrdID (11) of the previous order as assigned by the institution, used to identify the previous order in cancel and cancel/replace requests.

ClientID

109 (ClientID)

N

Company ID or Child Company ID if Multi-Company Trading.

TargetCompId

56 (TargetCompId)

Y

Identify receiving firm.

ExecInst

18 (ExecInst)

N

Please note that the data type is Char, not MultipleValueString.

Supported value(s):

G = All or None (AON)

OnBehalfOfCompId

115 (OnBehalfOfCompId)

N

Identifies firm originating message if the message was delivered by a third party.

Optional for all orders for regulatory purposes. Alphanumeric characters only with “|” delimiter.

OnBehalfOfSubId

116 (OnBehalfOfSubId)

N

Identifies specific message originator.

Required for all orders for regulatory purposes. Alphanumeric characters only with “|” delimiter.

OnBehalfOfLocationId

144 (OnBehalfOfLocationId)

N

Identifies specific message originator's location.

Required for all orders for regulatory purposes. Alphanumeric characters only with “|” delimiter.

HistoryRequestId

9415 (RequestId)

C

Provide on Execution Reports that are in response to a History Request.

OriginatorUserID

9139 (OriginatorUserID)

N

Trader ID that is registered in the ICE system and logged into the Trading System.

ExecTransType

20 (ExecTransType)

Y

Identifies transaction type.

Supported value(s):

0 = New

1 = Cancel

2 = Correct

ExecRefID

19 (ExecRefID)

N

Reference identifier.

OrdStatus

39 (OrdStatus)

Y

Indicates overall status of order.

Supported value(s):

0 = New

1 = Partial Fill

2 = Fill

4 = Canceled

5 = Replace

8 = Rejected

OrderState

9175 (OrderState)

N

Exchange defined order state. Please note that OrdStatus (Tag 39) should be used for the status of the order.

Supported value(s):

0 = Active

1 = Inactive

2 = Withdrawn,

3 = Pending

4 = Consummated

5 = Pre-Open

6 = Elected

OrdRejReason

103 (OrdRejReason)

C

Code to identify reason for order rejection.

Supported value(s):

1 = Unknown symbol

2 = Exchange closed (or Market Closed)

3 = Order exceeds limit

5 = Unknown Order

6 = Duplicate Order (e.g. duplicate ClOrdID)

9 = User Defined (more info in Text field)

11 = Unsupported order characteristic

OrderQty

38 (OrderQty)

N

Quantity ordered.

SecurityType

167 (SecurityType)

N

Indicates type of security.

PutOrCall

201 (PutOrCall)

C

Required if SecurityType (Tag 167) = ‘OPT’.

StrikePrice

202 (StrikePrice)

C

Strike Price for an Option.

Required if SecurityType (Tag 167) = ‘OPT’.

MaxShow

210 (MaxShow)

N

Maximum visible quantity of an order to market participants.

OrdType

40 (OrdType)

N

Order type.

Supported value(s):

1 = Market

2 = Limit

3 = Stop (with Protection)

4 = Stop Limit

Price

44 (Price)

C

Price.

ExecRestatementReason

378 (ExecRestatementReason)

C

The reason for restatement.

Required if ExecType (Tag 150) = ‘D’.

Supported value(s):

3 = Repricing of order

CustOrderHandlingInst

1031 (CustOrderHandlingInst)

N

Adapted from FIX 5.0. Please note the data type of String and not MultipleStringValue.

Supported value(s):

A = Phone simple

B = Phone complex

C = Vendor-provided Platform billed by Executing Broker (earlier FCM-provided screen)

D = Other, including Other-provided Screen (earlier Other-provided screen)

E = Client-provided platform controlled by FCM

F = Client-provided platform direct to exchange

G = Sponsored Access via Exchange API or FIX provided by Executing Broker (earlier FCM API or FIX)

H = Premium Algorithmic Trading Provider billed by Executing Broker (earlier Algo Engine)

J = Price at Execution (price added at Initial order entry, trading, middle office or time of give-up)

K = Zero Commission

W = Desk - Electronic

X = Desk - Pit

Y = Electronic (Preferred default value as per FIA usage guidelines. Not set as a default if not sent, earlier Client - Electronic)

Z = Client - Pit

LiquidityIndicator

9120 (LiquidityIndicator)

C

Required if ExecType (Tag 150) = ‘1’ or ‘2’.

Supported value(s):

A = Added Liquidity

R = Remove Liquidity

MemoField

9121 (MemoField)

N

-

TransactDetails

9123 (TransactDetails)

N

Contains reference information when provided.

For Basis Trades the format is: <SecurityID>|<SecurityIDSource>|<Price>|<Reference>

NumberOfCycles

9022 (NumofCycles)

N

-

SequenceWithinMillis

9028 (SequenceWithinMillis)

N

Sequence of the message within the millisecond. SequenceWithinMillis / 1000 = # of microseconds. Please note that the last 3 digits are sequence numbers within the microsecond (not nanoseconds).

ClientAppType

9413 (ClientAppType)

N

Used to indicate the source application on the request. Can be used to differentiate WebICE deals from FIXOS deals.

Supported value(s):

0 = WebICE

1 = FIX OS

2 = FIXml

3 = ICEBlock

4 = Other

5 = FPML

6 = UPS

7 = Mobile

8 = FIX POF

9 = YJ ISV

11 = IOA

CustClearingVenue

9209 (CustClearingVenue)

N

Supported value(s):

ICE

BIL

ICEEFRP

OTHER

TrdType

828 (TrdType)

N

Adapted from FIX.4.4. Please note type of String and not Int.

Supported value(s):

0 = Regular Trade

2 = ICE EFRP

3 = ICEBLK

4 = Basis Trade

5 = Guaranteed Cross

6 = Volatility Contingent Trade

7 = Stock Contingent Trade

9 = CCX EFP Trade

A = Other Clearing Venue

D = N2EX

E = EFP Trade/Against Actual

G = EEX

F = EFS/EFP Contra Trade

I = EFM Trade

J = EFR Trade

K = Block Trade

O = NG EFP/EFS Trade

Q = EOO Trade

S = EFS Trade

T = Contra Trade

U = CPBLK

V = Bilateral Off-Exchange Trade

Y = Cross Contra Trade

AA = Asset Allocation

TimeInForce

59 (TimeInForce)

N

Specifies how long the order remains in effect. Absence of this field is interpreted as Day.

Supported value(s):

0 = Day

1 = Good Till Cancel (GTC)

3 = Immediate or Cancel (IOC) Fill & Kill

4 = Fill or Kill (FOK)

6 = Good Till Date (GTD)

10 = Good After Logout (GAL) - WebICE Only

PositionEffect

77 (PositionEffect)

N

Supported value(s):

O = Open

C = Close

LeavesQty

151 (LeavesQty)

Y

The quantity remaining open for further execution.

CumQty

14 (CumQty)

Y

Total quantity filled.

AvgPx

6 (AvgPx)

Y

Calculated average price of all fills for order.

LastQty

32 (LastQty)

C

Quantity executedoffill. Required if ExecType (Tag 150) = ‘1’ or ‘2’.

LastPx

31 (LastPx)

C

Trade price of fill. Required if ExecType (Tag 150) = ‘1’ or ‘2’.

LastParPx

669 (LastParPx)

C

CDS and IRS Futures Price: The CDS or IRS Futures price of this fill.