Try TT Now

Trade Tracker – Database Schema

Parent Table: tblInstrumentsChild Tables: tblOrderActions (link InstrumentID), tblFills (link: InstrumentID

  1. The Trade Tracker database will be comprised of four tables: tblInstruments, tblOrderActions, tblFills, and tblAppStatus.  The tblInstruments table serves as a parent table to the tblOrderActions and tblFills table.  They are linked based on the InstrumentID field.
  2. The tblInstruments table contains information pertaining to each contract for which orders existed.  There will be one row in this table for each contract, which is gateway specific.
  3. The tblOrderActions table contains a record for each action that happens to an order (i.e., add, change, cancel, expiration, etc.).
  4. The tblFills table will contain information pertaining to each fill.  There will be one record for every partial fill.  Exchange traded spreads will have 3 fills for each partial fill, 1 fill for the spread and 1 fill for each underlying leg.
  5. The AppStatus table contains a row for each time Trade Tracker was started.  Each row contains the last time Trade Tracker was detected as running.  This information may assist in trouble shooting in the event that the application unexpectedly goes down.
  6. Below are the fields which comprise each of the four tables.

 

Table: tblInstruments

Field Name

Field Type

Required

Definition

Example: Add Order

InstrumentID

Numeric

Yes

Unique ID generated by Trade Tracker for each Instrument (gateway specific)

123

SeriesKey

Varchar

Yes

TT specific ID generated by the TT Gateway for each instrument (not gateway specific)

Add

Gateway

Varchar

Yes

TT Gateway

CME-A

Expiry

Varchar

Yes

Expiry of the contract

Sep13

ExpirationDate

Varchar

No

Expiration Date of the contract

9/20/2013

ProductType

Varchar

Yes

Product Type.  Possible values include: Future, Option, Spread, Stock, Warrant, Strategy, and Energy.

 

See the X_TRADER API Class Reference guide for a complete list of its possible ProductType values.

Future

Instrument

Varchar

No

Full Instrument Name

ES Sep13

Exchange

Varchar

No

Exchange

CME

Product

Varchar

No

Product

ES

CallPut

Varchar(1)

No

Call | Put

C

Strike

float

No

If option, strike price of Option

110

Currency

Char(3)

No

Currency abbreviation for Instrument

USD

 

Table: tblOrderActions

Field Name

Field Type

Required

Definition

Example: Add Order

TT Site Order Key

Varchar

Yes

Unique TT key of each TT order

986510676514

 

Action

Varchar

Yes

Possible values are:

  • Add
  • Change
  • Cancel
  • Existed on Startup
  • Existed on Startup – Hold
  • Submit
  • Hold
  • Expired
  • Rejected

Add

InstrumentID

Numeric

Yes

Unique identifier for a given instrument.  Links to the InstrumentID field in the tblInstruments table.

23

Gateway

Varchar

No

Gateway where action happened

CME-A

OrderNo

Varchar

Yes

Order Number assigned by Exchange

35405

TIF

Varchar

Yes

Time in Force.  Possible values are GTD, GTC, and <Date>.

GTD

BuySell

Varchar(2)

Yes

Buy |Sell |XB= Spread Buy |XS= Spread Sell

B

OrderType

Char(1)

Yes

Order Type. Possible values include:

  • B= OCO
  • C= CMO
  • L = Limit
  • M = Market
  • O= Batch
  • Q = Quote
  • S= Best Limit
  • T= Market To Limit
  • X= Cross

See the X_TRADER API Class Referenceguidefor a complete list of its possible OrderType values.

L

Quantity

Float

Yes

Order Quantity

150

MinQty

Float

No

Minimum qty restriction on an order

50

TradeDate

Datetime

No

Date at the exchange when the order action occurred.

8/10/2005

ExchangeTime

Varchar

No

Time at the exchange when the action occurred

10:15:25 AM

GWTime

Datetime

Yes

Date/time the gateway received the order

10/14/2003 10:51:05 AM

IPAddress

Varchar

Yes

IP Address of the workstation submitting the order

172.178.1.2

Account

Varchar

No

Account number

85454

Account Type

Char(2)

No

Code indicating the account type.  Possible values include:A1,A2, A3, A4, A5, A6, A7, A8, A9, G1,G2,G3,M1,M2, M3, P1, P2, P3,U1, U2,and U3.

 

See the X_TRADER API Class Referenceguidefor a complete list of its possible AccountType values.

A1

GiveUpID

Varchar

No

Give-up member's clearing ID, when the fill is assigned to another member

 

FFT2

Varchar

No

A free form text field #2 usually used by the broker to store customer information

 

FFT3

Varchar

No

Free Form Text Field #3

 

Price

Float

No

Limit Price

114425

Price Ticks

Float

No

Limit Price in number of ticks

4577

Stop Price

Float

No

Stop Price as a float

114465

Stop Price Ticks

Float

No

Stop Price in ticks

4585

PriceString

String

No

Price in X_TRADER display format

114425

StopPriceString

String

No

Stop Price in X_TRADER display format

114400

ExMember

Varchar

No

Direct Trader  - Member ID

ABC

ExGroup

Varchar

No

Direct Trader  - Group ID

XYZ

ExTrader

Varchar

No

Direct Trader  - Trader ID

001

UserName

Varchar

No

Username Login

JDoe

TraderMember

Varchar

No

Trader  - Member ID

TTORDAA

TraderGroup

Varchar

No

Trader  - Group ID

BBB

TraderTrader

Varchar

No

Trader  - Trader ID

001

Timestamp

Datetime

Yes

Local date and time when record was added to table.

03/14/2005 10:00:00 AM

ClrMember

String

No

Clearing Member ID

 

Destination

String

No

Exchange-specific order destination information

 

OrderSource

Long

No

Where the order originated.Possible values include:

  • 0 = X_TRADER
  • 1= Autospreader Desktop
  • 2 = Autotrader
  • 3 = X_TRADER API
  • 4 = Ext Source (Exchange)
  • 5 = Licensed FIX Adapter
  • 6 = Unlicensed FIX Adapter
  • 7 = X_TRADER Lite
  • 8 = TT_TRADER
  • 9 = Autospreader Engine
  • 10 = Synthetic SE Engine
  • 11 = TT API in X_TRADER Mode
  • 12 = Algo SE
  • 21 = TT API in Universal Login Mode
  • 22 = ADL

See the X_TRADER API Class Reference guidefor a complete list of its possible OrderSource values.

0

OldOrderNo

String

No

Immediately previous order number if the order number was changed

35400

OnHold

String

No

Held Order; Null if not Held

HELD

OpenClose

String

No

Whether the order opens or closes a position.  Possible values include:

  • O = Open
  • C = Close
  • F = FIFO

See the X_TRADER API Class Reference guidefor a complete listofits possible OpenClose values.

O

OrderRestr

String

No

Order Restriction as a character.  Possible values include:

  • A = Auction
  • B = Block
  • E = Iceberg
  • F = Fill or Kill
  • H = Fill and Save
  • I = Immediate or Cancel
  • M = Market on Close
  • N = All or None
  • O = Market on Open
  • S = Stop Order
  • T = Trigger Order
  • V = Minimum Volume
  • X = Flex Option
  • Y = Volatility
  • Z = Basis
  • a = Limit on Open
  • b = Limit on Close
  • c = Limit to Market on Close

See the X_TRADER API Class Reference guidefor a complete list of its possible OrderRestr values.

F

Source

String

No

Where the order originated. Possible values include:

  • 0 = X_TRADER
  • 1 = Autospreader Desktop
  • 2 = Autotrader
  • 3 = X_TRADER API)
  • 4 = Ext Source
  • 5 = Licensed FIX Adapter
  • 6 = Unlicensed FIX Adapter
  • 7 = X_TRADER Lite
  • 8 = TT_TRADER
  • 9 = Autospreader Engine
  • 10 = Synthetic SE Engine
  • 11 = TT API in X_TRADER Mode
  • 12 = Algo SE
  • 21 = TT API in Universal Login Mode
  • 22 = ADL

See the X_TRADER API Class Reference guidefor a complete list of its possible Source values.

0

Username

String

No

The Username on the order

UserTag

String

No

User-definable text containing user-defined information.

OrderTag

String

No

User-definable text containing user-defined information.

IsAutomated

Numeric

No

Whether the matching order originated from an automated program.  Possible values include:

0= False

1= True

IsPositionReserve

Numeric

No

Whether the order is a Position Reserve order.  Possible values include:

  • 0 = False
  • 1 = True

BrokerName

String

No

The Name of the Broker on the order

BrokerID

Numeric

No

The ID associated with the Broker on the order

CompanyName

String

No

The Name of the Company on the order

CompanyID

Numeric

No

The ID associated with the Company on the Order

Location

String

No

The country / region code of the order originator

ParentKey

String

No

The SiteOrderKey of the parent order

LinkType

String

No

The kind of link identifying the relationship between orders.  Possible values include:

  • AlgoC = Algo-Cancel order
  • AlgoL = Algo-Leave order
  • ASQ = Spread Quoting
  • ASH = Spread Hedge
  • ASR = Spread Position Reserve
  • SSEC = Synthetic child
  • SSER = Synthetic child position reserve

See the X_TRADER API Class Reference guidefor a complete list of its possible LinkType values.

ExchangeCredential

String

No

The Routing Exchange Credential of the order

Message

String

No

Exchange or gateway message describing the reason for an

order reject or order delete

TimeProcessed

String

No

Time on the TT Gateway at which the order was processed and sent to the exchange

10:15:24 AM

 

 

 

Table: tblFills

Field Name

Field Type

Required

Definition

Example: Add Order

TT Site Order Key

Varchar

Yes

Unique key of each TT order

986510676514

FillKey

Varchar

Yes

Unique Identifier of a fill per exchange per day.

12345678

InstrumentID

Numeric

Yes

Unique identifier for a given instrument.  Links to the InstrumentID field in the tblInstruments table.

10

OrderNo

Varchar

Yes

Order Number Assigned by Exchange

35405

BuySell 

Char(1)

Yes

Buy |Sell |XB= Spread Buy |XS= Spread Sell

B

FillType

Char(1)

Yes

Partial or Full

P

FillQty

Numeric

Yes

Number of contracts filled

25

Price

Float

Yes

Execution price

114425

Price Ticks

Float

Yes

Execution price in number of ticks

4577

PriceString

String

No

Price in X_TRADER display format

114425

TradeDate

Datetime

Yes

Date at the exchange when the fill occurred.

10/14/2004

ExchangeTime

Varchar

Yes

Time at the exchange when the fill occurred

10:15:25 AM

GWTime

Datetime

No

Date/time the gateway received the fill callback

9:15:25 AM

Account

Varchar

No

Account Number

85454

Account Type

Char(2)

No

Code indicating the account type.  Possible values include:A1,A2, A3, A4, A5, A6, A7, A8, A9, G1,G2,G3,M1,M2, M3, P1, P2, P3,U1, U2,and U3.

 

See the X_TRADER API Class Referenceguidefor a complete list of its possible AccountType values.

A1

GiveUpID

Varchar

No

Give-up member's clearing ID, when the fill is assigned to another member

54321

ComboCode

Char(1)

No

Combination code for the fill.  Possible values include:

 

“”(empty string)=outright order

C=Calendar Spread Fill

F=All other spread fills

 

See the X_TRADER API Class Referenceguidefor a complete list of its possible ComboCode values.

C

Fee

Varchar

No

Exchange specific fee

$1.00

FFT2

Varchar

No

A free form text field #2 usually used by the broker to store customer information

 

FFT3

Varchar

No

Free Form Text Field #3

 

ExMember

Varchar

No

Direct Trader  - Member ID

ABC

ExGroup

Varchar

No

Direct Trader  - Group ID

XYZ

ExTrader

Varchar

No

Direct Trader  - Trader ID

001

UserName

Varchar

No

Username Login

JDoe

TraderMember

Varchar

No

Trader  - Member ID

TTORDAA

TraderGroup

Varchar

No

Trader  - Group ID

BBB

TraderTrader

Varchar

No

Trader  - Trader ID

001

IsAutomated

Numeric

No

Whether the matching order originated from an automated program.  Possible values include:

0= False

1= True

1 (One = True)

Timestamp

Datetime

Yes

Local date and time when record was added to table.

03/14/2005 10:00:00 AM

ClrMember

String

No

Clearing Member ID

 

ClearingDate

Datetime

No

Clearing date for the fill as specified by an Exchange, when provided by the TT Gateway.

7/25/2012

CntrClearing

String

No

Counter party’s clearing Firm ID

 

CntrMember

String

No

Counter party trader’s Member ID

 

ExRecNo

String

No

Record number for this fill provided by the exchange

 

OrderType

String

No

Order Type.  Possible values include:

  • B = OCO
  • C = CMO
  • U = Limit
  • M = Market
  • O = Batch
  • Q = Quote
  • S = Best Limit
  • T = Market To Limit
  • X = Cross

See the X_TRADER API Class Referenceguidefor a complete list of its possible OrderType values.

L

OpenClose

String

No

For market fills, whether the fill opened or closed a position; for injected fills, the source of an injected fill.  Possible values include:

  • A = X_RISK Admin
  • C = Close
  • D = Detailed SOD
  • F = FIFO
  • M = Manual Fill
  • O = Open
  • R = Rollover
  • S = SOD

See the X_TRADER API Class Reference guidefor a complete list of its possible OpenClose values.

O

OrderRestr

String

No

Order Restriction as a character.  Possible values include:

  • A = Auction
  • B = Block
  • E = Iceberg
  • F = Fill or Kill
  • H = Fill and Save
  • I = Immediate or Cancel
  • M = Market on Close
  • N = All or None
  • O = Market on Open
  • S = Stop Order
  • T = Trigger Order
  • V = Minimum Volume
  • X = Flex Option
  • Y = Volatility
  • Z = Basis
  • a = Limit on Open
  • b = Limit on Close
  • c = Limit to Market on Close

See the X_TRADER API Class Reference guidefor a complete list of its possible OrderRestr values.

F

Source

String

No

Source of the fill. For normal fills, the value contains a unique ID for the Fill Server. For manual fills generated by an XTAPIapplication,the value contains an ID based on the systemrunning the application.

ICE

Username

String

No

The Username on the fill.

JDOE

UserTag

String

No

User-definable text containing user-defined information.

AlgoTrade#1

OrderTag

String

No

User-definable text containing user-defined information.

Strategy#2

BrokerName

String

No

The Name of the Broker on the fill.

BROKER#1

BrokerID

Numeric

No

The ID associated with the Broker on the fill.

38

CompanyName

String

No

The Name of the Company on the fill.

COMPANY#4

CompanyID

Numeric

No

The ID associated with the Company on the fill.

26

Location

String

No

The country / region code of the order originator.

US_NY

ParentKey

String

No

The SiteOrderKey of the parent order.

766544676324

LinkType

String

No

The kind of link identifying the relationship between orders.  Possible values include:

  • AlgoC = Algo-Cancel order
  • AlgoL = Algo-Leave order
  • ASQ = Spread Quoting
  • ASH = Spread Hedge
  • ASR = Spread Position Reserve
  • SSEC = Synthetic child
  • SSER = Synthetic child position reserve

See the X_TRADER API Class Reference guidefor a complete list of its possible LinkType values.

ASQ

ExchangeCredential

String

No

The Routing Exchange Credential of the order

XTHGES

 

Table: tblAppStatus

Field Name

Field Type

Required

Definition

Example: Add Order

ID

Numeric

Yes

Unique ID generated by Trade Tracker for each record

1234

AppStartDateTime

datetime

Yes

Time the app was last started

7/1/2004 5:00:01 AM

AppLastRunning

datetime

Yes

Time field that gets updated every 15 seconds as long as the application is running.

7/1/2004 11:00:01 PM