Page tree
Skip to end of metadata
Go to start of metadata

Database Schema

ICE-to-Database mapping

Tables description

Table: ICEReports

Usage: Table holds summary of reported trades.

Fields

Name

FIX tag

Req’d

Description

TradeReportID


571 (TradeReportID)

Y

Key field. Unique ID of Trade Capture Report

SendingTime

52 (SendingTime)


Y

Key field. Date and time when report was received.

Format: YYYYMMDD-HH:MM:SS

UniqueTradeID-Y

Key field. Together with TradeReportID and SendingTime identifies unique trade appearance.

Format: ExecID + "." + Symbol + "." + Side + "." + OrdStatus

TradeReportTransType                       


487 (TradeReportTransType)

Y

Identifies Trade Report message transaction type.

Valid value:

0 = New

TradeReportType                               


856 (TradeReportType)

N

Type of Trade Report.

Valid value:

0  = Submit

TrdType                                 


828 (TrdType)

N

Type of trade.

Valid values:

0 = Regular Trade
K = Block Trade
E = EFP Trade
S = EFS Trade
V = Bilateral Off-Exchange Trade
O = NG EFP/EFS Trade
9 = CCX EFP Trade
J = EFR Trade
T = Contra Trade
Y = Cross Contra Trade
F = EFS/EFP Contra Trade
A = Other Clearing Venue
I = EFM Trade
Q = EOO Trade
D = N2EX
G = EEX
U = CPBLK
2 = ICEEFRP
3 = ICEBLK
4 = Basis
5 = Guaranteed Cross
6 = Volatility Contingent Trade
7 = Stock Contingent Trade
AA = Asset Allocation


ExecType       

150 (ExecType)

N

ExecType will be equal to ‘NULL’ in case of live updates sent from ICE.

Valid values:

5 = Replace

F =  Trade (partial fill or fill)

H =  Trade Cancel

Please note:  it is possible to get a duplicate trade capture report with an ExecType of 5 even if nothing has changed on your side of the trade!

ExecID                                   


17 (ExecID)

Y

Exchange Deal ID

OrigTradeID

1126 (OrigTradeID)

N

Original trade ID present only on the trades resulting from bust/adjust and containing the DealID of the original trade that was bust/adjusted

GroupIndicator

9820 (GroupIndicator)

N

Used to identify legs of spread deals brokered in WebICE TR

TradeLinkMktID

9414 (TradeLinkMktID)

N

Market id that links the futures market to the OTC market

OrdStatus                               


39 (OrdStatus)

N

Identifies current status of order.

Valid values:

2 = Filled

4 = Cancelled

PreviouslyReported                           


570 (PreviouslyReported)

Y

Indicates if the trade capture report was previously reported to the counterparty.

Valid values: N, Y

Symbol           


55 (Symbol)

Y

MarketID (Symbol) needed to submit orders

SecurityID                             


48 (SecurityID)

N

Contract identifier/symbol.


SecurityIDSource                  


22 (SecurityIDSource )

C

Exist only if SecurityID exists.

Valid value: 8

SecurityExchange

207 (SecurityExchange)

N

Exchange MIC Code

TradeLinkID

820 (TradeLinkID)

N

Deal id that links the futures market trade to the OTC market trade.

NOTE: The TransactTime (tag 60) for both the futures and OTC trade will be the same.

CFICode

                                   


461 (CFICode)

Y

Valid values

FXXXXX – Futures

OPXXXX – Put

OCXXXX – Call

OMXXXX – Other -> This value will be present for UDS trade Capture Reports.

OptionsSymbol          

9403 (OptionsSymbol)

N

Market id of the predefined option market. This tag is useful because for option trades, tag 55 will still contain the underlying Futures market id. This tag is optional because option markets hosted on our older trading engines don’t have any pre-configured marketIDs.

StrikePrice


202 (StrikePrice)

N

Strike price of the option.

Required if  CFICode = ‘OCXXXX’ or ‘OPXXXX’

StartDate

916 (StartDate)

N

In format, YYYYMMDD, used to report custom strips on bilateral trades. Please note for Endex Spot markets use DeliveryStartDate


EndDate


917 (EndDate)

N

In format, YYYYMMDD, used to report custom strips on bilateral trades. Please note for Endex Spot markets use DeliveryEndDate

DeliveryStartDate

9520 (DeliveryStartDate)

N

Format will be based on UTCTimestamp with variation possible based on product. Accepted values include YYYYMMDD-HH:MM:SS and YYYMMDD-HH:MM:SS.sss

DeliveryEndDate


9521 (DeliveryEndDate)

N

Format will be based on UTCTimestamp with variation possible based on product. Accepted values include YYYYMMDD-HH:MM:SS and YYYMMDD-HH:MM:SS.sss

LocationCode

9522 (LocationCode)

N

Endex specific: Only populated for OCM Locational markets

MeterNumber

9523 (MeterNumber)

N

Endex specific: Only populated for OCM Locational markets

LeadTime

9524 (LeadTime)

N

Endex specific: Only populated for OCM Locational markets

ReasonCode

9525 (ReasonCode)

N

Endex specific: Only populated for OCM Locational markets

LastQty          

32 (LastQty)

Y

Value of trade quantity

LastPx            

31 (LastPx)

Y

Price of this (last) fill

TradeDate

75 (TradeDate)

Y

Indicates date of trade occurred in YYYYMMDD format

TransactTime                          

60 (TransactTime)

Y

Indicates time of trade occurred,

Format: HH:MI:SS.000

NumOfLots   

9018 (NumOfLots)

Y

Trade quantity represented in number of lots

NumOfCycles

9022 (NumOfCycles)

Y

Remaining days left to a flow contract

NoSides         

552 (NoSides)

Y

Total number of sides.
Will always be equal to 1

NoLegs

555 (NoLegs)

N

Total number of trade spread legs added into this report

ClientAppType

9413 (ClientAppType)

N

Identifies the source which generated the current FIXTC report.

Valid values:

0 - WEBICE

1 - FIXOS

2 - FIXML

3 - ICEBLOCK

4 - OTHER

5 - FPML

6 - UPS

7 - MOBILE

8 - POF

9 - YJISV

10 – FIX HEx

11 - IOA

ExchangeSilo

9064 (ExchangeSilo)

N

Identifies the Exchange Silo of a market.

Valid Values:

0= ICE

1= Endex

2= Liffe

SecuritySubType

762 (SecuritySubType)

N

Reserved for future use. Contains the strategy code for the UDS where applicable.

Valid Values:

https://www.theice.com/publicdocs/technology/ICE_Strategy_Code_Reference_Manual.pdf

TermsQualityComments

9510 (TermsQualityComments)

N

Terms of Platts deal

Table: ICEReportsSides

Usage: Table holds information about side of a reported trade. For each Trade Capture Report there will be one record in ICEReportsSides table.

Fields

Name

FIX tag

Req’d

Description

TradeReportID


571 (TradeReportID)

Y

Key field. Unique ID of Trade Capture Report.

SendingTime

52 (SendingTime)


Y

Key field. Date and time when report was received.

Format: YYYYMMDD-HH:MM:SS

SideEntry


-

Y

Key field. Together with TradeReportID and SendingTime identifies unique side of each report

UniqueTradeID-Y

Key field. Together with TradeReportID and SendingTime identifies unique trade appearance.

Format: ExecID + "." + Symbol + "." + Side + "." + OrdStatus

Side

                                   


54 (Side)

Y

Trade side.

Valid values:

1 = Buy

2 = Sell

OrderID

37 (OrderID)

Y

Exchange OrderID

ClOrdID


11 (ClOrdID)

N

Client OrderID

Text

58 (Text)

N


CustOrderHandlingInst

1031 (CustOrderHandlingInst)

N

The CustOrderHandlingInst is used to mark deals with a value that determines how counterparty will be billed. This is only applicable to Griffin market types. Earlier Billing Code

ComplianceID

376 (ComplianceID)

N

Unique Swap Identifier

NoParties


453 (NoParties)

N

Number of Parties

NoAllocs

78 (NoAllocs)

N

Number of allocation instructions

AllocAccount

79(AllocAccount)

N

Allocation Account

Only if 78>0

PositionEffect

77 (PositionEffect)

N

Valid Values:

O (Open)

C (Close)

MemoField

9121 (MemoField)

N


TransactDetails

9123 (TransactDetails)

N


Table: ICEReportsSidesParties

Usage: Table holds parties associated with certain side of reported trade. For each Trade Capture Report number of records in ICEReportdSidesParties table will be equal to value of NoParties in the correspondent record in ICEReportdSides table.

Fields

Name

FIX tag

Req’d

Description

TradeReportID


571 (TradeReportID)

Y

Key field. Unique ID of Trade Capture Report.

SendingTime

52 (SendingTime)


Y

Key field. Date and time when report was received.

Format: YYYYMMDD-HH:MM:SS

SideEntry

-

Y

Key field. Together with TradeReportID and SendingTime identifies unique side entry of each report

PartyEntry

-

Y

Key field. Together with TradeReportID, SendingTime and SideEntry identifies unique party of each report

UniqueTradeID-Y

Key field. Together with TradeReportID and SendingTime identifies unique trade appearance.

Format: ExecID + "." + Symbol + "." + Side + "." + OrdStatus

PartyID

448 (PartyID)

Y

PartyID

PartyIDSource


447 (PartyIDSource)

Y

Valid value: D (Proprietary/Custom code)

PartyRole

452 (PartyRole)

Y

Valid values:

1 = Executing Firm

4 = Clearing Firm

11 = Order Origination

Trader

12 = Executing Trader

13 = Order Origination

Firm

17 = Contra Firm

21 = Clearing Organization

35 = Liquidity Provider

37 = Contra Trader

50 = Contra Firm ID

51 = Clearing Account ID

52 = CTI Code

53 = House Number

54 = Account Code

55 = Account ID

56 = Order Origination

Firm ID

57 = OnBehalfOfCompID

58 = OnBehalfOfSubID

59 = OnBehalfOfLocationID

60 = Clearing Firm Name

61 = ExecutingFirmID

63 = Clearing Firm Mnemonic

Table: ICEReportsSidesAllocsPty

Usage: Table is used for allocation party details associated with certain side of reported trade. For each Trade Capture Report number of records in ICEReportdSidesAllocsPty table will be equal to value of NoNested2PartyIDS in correspondent record in ICEReportdSides table.

Fields

Name

FIX tag

Req’d

Description

TradeReportID


571 (TradeReportID)

Y

Key field. Unique ID of Trade Capture Report.

SendingTime

52 (SendingTime)


Y

Key field. Date and time when report was received.

Format: YYYYMMDD-HH:MM:SS

SideEntry

-

Y

Key field. Together with TradeReportID and SendingTime identifies unique side entry of each report

UniqueTradeID-Y

Key field. Together with TradeReportID and SendingTime identifies unique trade appearance.

Format: ExecID + "." + Symbol + "." + Side + "." + OrdStatus

Nested2PartyEntry

-

Y

Key field. Together with TradeReportID, SendingTime and SideEntry identifies unique nested party of each report

Nested2PartyID

757 (Nested2PartyID)

Y

Nested Party ID

Nested2PartyIDSource


758 (Nested2PartyIDSource)

Y

Valid value: D (Proprietary/Custom code)

Nested2PartyRole

759 (Nested2PartyRole)

Y

Clearing firm = 4

Give up type = 62

Only if 756>0

Table: ICEReportsLegs

Usage: Table holds details of individual leg of multileg instrument (user defined strategy) associated with reported trade. For each Trade Capture Report number of records in ICEReportsLegs table will be equal to value of NoLegs in correspondent record in ICEReports table.

Fields

Name

FIX tag

Req’d

Description

TradeReportID


571 (TradeReportID)


Key field. Unique ID of Trade Capture Report.

SendingTime

52 (SendingTime)


Y

Key field. Date and time when report was received.
Format: YYYYMMDD-HH:MM:SS

LegsEntry

-


Key field. Together with TradeReportID and SendingTime identifies unique leg of each report

UniqueTradeID-Y

Key field. Together with TradeReportID and SendingTime identifies unique trade appearance.

Format: ExecID + "." + Symbol + "." + Side + "." + OrdStatus

LegSymbol     


600 (LegSymbol)

Y

Leg MarketID (Symbol)

LegSecurityID                                   


602 (LegSecurityID)

N

Contract identifier/symbol

LegSecurityIDSource


603 (LegSecurityIDSource)

N


LegCFICode

608 (LegCFICode)

Y

Valid values

FXXXXX = Futures

OPXXXX =  Option Put

OCXXXX = Option Call

LegStrikePrice                       


612 (LegStrikePrice)

N

Strike price of option leg

Required if LegCFICode = ‘OPXXXX’ or ‘OCXXXX’

LegSide                                  


624 (LegSide)

Y

Side of a leg. Valid values:

1 = Buy

2 = Sell

LegLastPx


637 (LegLastPx)

Y

Value of leg trade quantity

LegQty           

687 (LegQty)

Y

Price of this (last) leg fill

LegOptionRatio

1017 (LegOptionRatio)

N

Contains the hedge ratio value in percentage

LegRefID

654 (LegRefID)

Y

Leg TradeID

LegNumOfLots


9019 (LegNumOfLots)

Y

Spread leg quantity represented in number of lots

LegNumofCycles

9023 (LegNumofCycles)

Y


LegStartDate

9020 (LegStartDate)


Y

Start date for the leg markets

LegEndDate


9021 (LegEndDate)

Y

End date for the leg markets

LegComplianceID

9376 (LegComplianceID)

N

Unique Swap Identifier or Unique Trade Identifier

NoNestedParties

539 (NoNestedParties)

N

Number of nested parties for the leg.

LegSecurityExchange

616 (LegSecurityExchange)

N

Exchange MIC

LegOptionSymbol

9404 (LegOptionSymbol)

N

Exist only if tis leg is option leg

LegCustOrderHandlingInst

9426 (LegCustOrderHandlingInst)


N

Valid Values:

S (Sleeve)

M (Originator)

T (Aggressor)


LegMemoField

9122 (LegMemoField)

N


Table: ICEReportsLegsNestedPty

Usage: Table holds parties associated with individual leg of reported multileg trade. For each Trade Capture Report number of records in ICEReportsLegsNestedPty table will be equal to value of NoNestedParties in correspondent record in ICEReportsLegs table.

Fields

Name

FIX tag

Req’d

Description

TradeReportID


571 (TradeReportID)

Y

Key field. Unique ID of Trade Capture Report.

SendingTime

52 (SendingTime)


Y

Key field. Date and time when report was received.
Format: YYYYMMDD-HH:MM:SS

LegsEntry                              


-

Y

Key field. Together with TradeReportID and SendingTime identifies unique nested party of each report

NestedPartyEntry

-

Y

Key field. Together with TradeReportID and SendingTime identifies unique nested party of each report

NestedPartyID

524 (NestedPartyID)

Y

Nested PartyId

NestedPartyIDSource

525 (NestedPartyIDSource)

Y

Valid value: D (Proprietary/Custom code)

NestedPartyRole

538 (NestedPartyRole)

Y

Valid values:

1 = Executing Firm

4 = Clearing Firm

11 = Order Origination

Trader

12 = Executing Trader

13 = Order Origination

Firm

17 = Contra Firm

21 = Clearing Organization

35 = Liquidity Provider

37 = Contra Trader

50 = Contra Firm ID

51 = Clearing Account ID

52 = CTI Code

53 = House Number

54 = Account Code

55 = Account ID

56 = Order Origination Firm ID

57 = OnBehalfOfCompID

58 = OnBehalfOfSubID

59 = OnBehalfOfLocationID

60 = Clearing Firm Name

61 = ExecutingFirmID

63 = Clearing Firm Mnemonic

Table: Sent_Messages_ICE

Usage: Table is used to exclude potential duplicates and build triggers for further data base messages processing        

Fields

Name

FIX tag

Req’d

Description

SendingTime

52 (SendingTime)



Y

Key field. Date and time when report was received.

Format: YYYYMMDD-HH:MM:SS

OrdStatus

39 (OrdStatus)

Y

Key field. Identifies current status of order.

Valid values:

2 = Filled

4 = Cancelled

ExecID

17 (ExecID)

Y

Key field. Exchange Deal ID

Symbol

55 (Symbol)

Y

Key field.

Contains the MarketID (Symbol) needed to submit orders

Side

54 (Side)

Y

Key field. Trade side.

Valid values:

1 = Buy

2 = Sell

ExecType150 (ExecType)Y

Key field. Describes the reason the trade capture report is sent. ExecType will be equal to ‘NULL’ in case of live updates sent from ICE.

5 = Replace
F = Trade (partial fill or fill)
H = Trade Cancel

TradeReportID


571 (TradeReportID)

N

Unique ID of Trade Capture Report

UniqueTradeID-N

Together with TradeReportID and SendingTime identifies unique trade appearance.

Format: ExecID + "." + Symbol + "." + Side + "." + OrdStatus

Table: ICESecurityDefinitions

Usage: Table holds summary data for requested security definitions on certain ICE Market. Total Number of securities available at the ICE Market is provided in  TotNoRelatedSym column.

Fields

Name

FIX tag

Req’d

Description

SecurityResponseID

322 (SecurityResponseID)

Y

Key field. Unique ID of Security Definition

CurrentDate

52 (SendingTime)

Y

Key field. Date when Security Definition was received

Format: YYYYMMDD

ICESessionTargetCompID

56 (ICESessionTargetCompID)

Y

Key field. Always set to ICE

SecurityReqID

320 (SecurityReqID)

Y

ID of Security Definitions Request

SecurityResponseType

393 (SecurityResponseType)

Y

Valid value: 4

TotNoRelatedSym

393 (TotNoRelatedSym)

Y

Total number of securities in this security type

NoRpts

82 (NoRpts)

Y

Total number of messages sent as a response of the request

ListSeqNo

67 (ListSeqNo)

Y

Sequence number of the message in a list

NoUnderlyings

711 (NoUnderlyings)

Y

Total number of securities in this security definition response message

ExchangeSilo

9064 (ExchangeSilo)

N

Identifies the Exchange Silo of a market.

Valid Values:

0= ICE

1= Endex

2= Liffe

Table: ICESecurityDefUnderlyings

Usage: Table is used to store description of individual securities available on  certain ICE Market. For each Security Definition number of records in ICESecurityDefUnderlyings table will be equal to value of NoUnderlyings in correspondent record in ICESecurityDefinitions table.

Fields

Name

FIX tag

Req’d

Description

UnderlyingSymbol                             

311 (UnderlyingSymbol)

Y

Key field. Contains the MarketID (Symbol) needed to submit orders

CurrentDate

52 (SendingTime)

Y

Key field. Date when Security Definition was received

Format: YYYYMMDD

UnderlyingCFICode                          

463 (UnderlyingCFICode)

N

Key field.

Valid Values:

FXXXXX = Futures

OXXXXX = Options

ICESessionTargetCompID

56 (ICESessionTargetCompID)

Y

Always set to ICE

SecurityResponseID

322 (SecurityResponseID)

Y

Unique ID of Security Definition

UnderlyingSecurityID                                                                       

                       

309 (UnderlyingSecurityID)

Y

Contract symbol


UnderlyingSecurityIDSource                        

305 (UnderlyingSecurityIDSource)

Y

Contract symbol source

UnderlyingSecurityDesc

307 (UnderlyingSecurityDesc)

Y

Security description

UnderlyingMaturityDate

542 (UnderlyingMaturityDate)

Y

Maturity date of a security

UnderlyingContractMultiplier                       

436 (UnderlyingContractMultiplier)

Y

Contract multiplier

StartDate

916 (StartDate)

Y

Start Date of the market strip

EndDate                     

917 (EndDate)

Y

End Date of the market strip

IncrementPrice

9013 (IncrementPrice)

Y

Increment Price for a security

IncrementQty             

9014 (IncrementQty)

Y

Increment quantity for a security

LotSize                       

9017 (LotSize)

Y


LotSizeMultiplier

9024 (LotSizeMultiplier)

N

 Clearable products only

StripID                       

9201 (StripID)

Y


StripType                               

9200 (StripType)

Y

Valid values:

0- Hourly

1- Spot

2- Same Day

3- Within Day

4- Next Day

5- Day Ahead

6- Bal Week

7- Next Saturday

8- Next Sunday

9- Weekend

10- Next Week

11- Bal Month

12- Month

13- Custom

14- Period

15- Quarter

16- Year

17- Spread

18- Cash

19- Bal Quarter

20- Bal Year

21- Half Month

22- Today

23- Tplus

24- Yesterday

25- Bundle

26- Barge

27- Laycan

28- customBalMonth

29- Week

30- balDay

31- udsPack

32- udsBundle

70- endex0608

71- endex0820

72- endex2006

73- endex2008

74- endexBalWeek

75- nextHour

76- endexSaturday

77- endexSunday

78- endexWeekend

79- endexWithinDay

80- endexDayAhead

81- endexWDNW

82- endexDays

83- endexDaysGIS

84- endexDaysINJ

94- griffinBalWeek

95- griffinBM

96- griffinDays

97- griffinGY

98- griffinWDNW

99- griffinWeekend

999- unknown

100- Rolling

StripName      

9202 (StripName)

N


HubID            

9300 (HubID)

Y


HubName                   

9301 (HubName)

N


HubAlias                    

9302 (HubAlias)

N


UnderlyingUnitOfMeasure

998 (UnderlyingUnitOfMeasure)

Y

Physical unit of measure for derivative products


Granularity     

9085 (Granularity)

N


PriceDenomination    

9100 (PriceDenomination)

Y

Trading denomination the contract trades in e.g., USD, GB pence, USD cents

NOTE: For OXXFXX– unique Option marketIDs this tag will be absent

PriceUnit        

9101 (PriceUnit)

Y

NOTE: For OXXFXX– unique Option marketIDs this tag will be absent

NumOfDecimalPrice 

9083 (NumOfDecimalPrice)

Y


ProductID      

9061 (ProductID)

N


ProductName             

9062 (ProductName)

N


NumOfDecimalQty   

9084 (NumOfDecimalQty)

Y


Clearable        

9025 (Clearable)

N

Valid values:

Y = Clearable

N = Not Clearable

ProductDescription    

9063 (ProductDescription)

N


TickValue       

9032 (TickValue)


N


IncrementStrike         

9400 (IncrementStrike)

N

Exists for options

MinStrike                               

9401 (MinStrike)

N

Exists for options

MaxStrike                              

                                   

9402 (MaxStrike)

N

Exists for options

ImpliedType   

9002 (ImpliedType)

N

Valid Value:

F – Full Spread

PrimaryLegSymbol

9004 (PrimaryLegSymbol)

N

Exists for spread products.

SecondaryLegSymbol

9005 (SecondaryLegSymbol)

N

Exists for spread products.

NumOfCycles

9022 (NumOfCycles)

N

Remaining days left to a flow contract

PhysicalCode

9303 (PhysicalCode)

N

Always exist if Tag 711 value > 0

UnderlyingSecurityExchange

308 (UnderlyingSecurityExchange)

N

Supports Market Identifier Code (MIC) values. Examples of possible values are “IFEU”, “IFUS”, “IFCA”, “IFED”, etc. This will not be sent for options markets.

OffExchangeIncrementPrice


9040 (OffExchangeIncrementPrice)

N

The minimum increment price for Off Exchange markets

OffExchangeIncrementQty

9041 (OffExchangeIncrementQty)

N

The minimum increment quantity for Off Exchange markets

UnderlyingStrikePrice

316 (UnderlyingStrikePrice)

N


SecurityTradingStat

326 (SecurityTradingStat)

N

Valid Values:

2 - Trading Halt (Suspended)

4 – No Open/No Resume (Pre-Close)

17 - Ready to Trade (Open)

18 - Not Available for Trading (Close)

20 – Unknown or Invalid (Expired)

21- Pre-Open

100 – Open for trading as Hedge Leg Only

UnderlyingSecuritySubTyp

763 (UnderlyingSecuritySubTyp)

N

Reserved for future use. Contains the strategy code for a defined market where applicable.

https://www.theice.com/publicdocs/technology/ICE_Strategy_Code_Reference_Manual.pdf

  • No labels