ProductDefinitionV2

V8 Message Definiton

SpiderRock normalized exchange product definitions. Includes future, option, and spread definitions from a number of exchanges. TickerDefinitions, RootDefinitions and CCodeDefinitions are consistent with these records.

METADATA

Attribute	Value
Topic	4335-product-definition
MLink Token	FutureDefinition
Product	SRAnalytics
accessType	SELECT

Table Definition

Field	Type	Key	Default Value	Comment
secKey_at	enum - AssetType	PRI	'None'	SR Security Key can be partially filled in look at secType
secKey_ts	enum - TickerSrc	PRI	'None'	SR Security Key can be partially filled in look at secType
secKey_tk	VARCHAR(12)	PRI	''	SR Security Key can be partially filled in look at secType
secKey_yr	SMALLINT UNSIGNED	PRI	0	SR Security Key can be partially filled in look at secType
secKey_mn	TINYINT UNSIGNED	PRI	0	SR Security Key can be partially filled in look at secType
secKey_dy	TINYINT UNSIGNED	PRI	0	SR Security Key can be partially filled in look at secType
secKey_xx	DOUBLE	PRI	0	SR Security Key can be partially filled in look at secType
secKey_cp	enum - CallPut	PRI	'Call'	SR Security Key can be partially filled in look at secType
secType	enum - SpdrKeyType	PRI	'None'	Security Type Stock Future Option
securityID	TINYTEXT		''	unique exchange id exch assigned
ticker_at	enum - AssetType		'None'	master underlier
ticker_ts	enum - TickerSrc		'None'	master underlier
ticker_tk	VARCHAR(12)		''	master underlier
SRspreadID	BIGINT		0
productClass	enum - ProductClass		'None'
underlierID	BIGINT		0	underlier product id option only securityID of undKeyundType product
undKey_at	enum - AssetType		'None'	SR Underlier Security Key can be partially filled in look at undType option only
undKey_ts	enum - TickerSrc		'None'	SR Underlier Security Key can be partially filled in look at undType option only
undKey_tk	VARCHAR(12)		''	SR Underlier Security Key can be partially filled in look at undType option only
undKey_yr	SMALLINT UNSIGNED		0	SR Underlier Security Key can be partially filled in look at undType option only
undKey_mn	TINYINT UNSIGNED		0	SR Underlier Security Key can be partially filled in look at undType option only
undKey_dy	TINYINT UNSIGNED		0	SR Underlier Security Key can be partially filled in look at undType option only
undType	enum - SpdrKeyType		'None'	Underlier Security Type Stock Future option only
productGroup	VARCHAR(6)		''	Underlying product code IE All GE Eurodollar spreads options futures will be in the same productGroup This is the Asset field from the SecurityDefinition message
securityGroup	VARCHAR(6)		''	Exchange specific code for a group of related securities that are all affected by market events IE All Emini weekly options EW This is SecurityGroup field from the SecurityDefinition messages
marketSegmentID	INT		0	Exchange specific market segment identifier
ricCode	VARCHAR(32)		''	Full RIC Code only provided for nonuser defined instruments
securityDesc	TINYTEXT		''	full exchange symbol
exchange	VARCHAR(8)		''	listing exchange
productType	enum - ProductType		'None'
productTerm	enum - ProductTerm		'None'
productIndexType	enum - ProductIndexType		'None'
productRate	FLOAT		0
contractSize	FLOAT		0
contractUnit	enum - ContractUnit		'None'
priceFormat	enum - PriceFormat		'None'
minTickSize	DOUBLE		0
displayFactor	DOUBLE		0
strikeScale	DOUBLE		0	manual strike price adjustment multiplier used for some CME products if set otherwise displayFactor is used okeyxx strikePrice manualStrikeScale
minLotSize	SMALLINT		0	minimum lot size
bookDepth	SMALLINT		0	levels in the Globex quote book
impliedBookDepth	SMALLINT		0	levels in the globex implied quote book 0 if no implied depth
impMarketInd	SMALLINT		0	implied market type 0 no implied 1 implied in 2 implied out 3 implied in out
minPriceIncrementAmount	FLOAT		0	depricate minimum price amount points per handle
parValue	FLOAT		0	per contract par value
contMultiplier	FLOAT		0	contract deliverable multipler
cabPrice	DOUBLE		0	depricate cabinet price minimum closing price for OOM options
tradeCurr	enum - Currency		'None'
settleCurr	enum - Currency		'None'
strikeCurr	enum - Currency		'None'
expiration	DATETIME(6)		'1900-01-01 00:00:00.000000'	future expiration or option expiration if product is an option we use the last TRADING day as the expiration date
maturity	DATE		'1900-01-01'	future maturity date or option maturity date this is the delivery month
exerciseType	enum - ExerciseType		'None'	depricate in RootDefinition Exercise style
userDefined	enum - YesNo		'None'
decayStartYear	SMALLINT		0
decayStartMonth	TINYINT UNSIGNED		0
decayStartDay	TINYINT UNSIGNED		0
decayQty	INT		0	daily decay quantity
priceRatio	DOUBLE		0	price ratio for interest rate intercommodity spreads
timestamp	DATETIME(6)		'1900-01-01 00:00:00.000000'
LegsList	JSON		'JSON_ARRAY()'

PRIMARY KEY DEFINITION (Unique)

Field	Sequence
secKey_tk	1
secKey_yr	2
secKey_mn	3
secKey_dy	4
secKey_xx	5
secKey_cp	6
secKey_at	7
secKey_ts	8
secType	9

JSON Block (LegsList)

Field	Type	Comment
legID	enum - legID	leg SecurityId exch assigned
secKey	enum - secKey
secType	enum - SpdrKeyType
side	enum - BuySell
ratio	enum - ratio
refDelta	enum - refDelta
refPrc	enum - refPrc

CREATE TABLE EXAMPLE QUERY

CREATE TABLE `SRAnalytics`.`MsgProductDefinitionV2` (
    `secKey_at` ENUM('None','EQT','IDX','BND','CUR','COM','FUT','SYN','WAR','FLX','MUT','SPD','MM','MF','COIN','TOKEN','ANY') NOT NULL DEFAULT 'None' COMMENT 'SR Security Key [can be partially filled in (look at secType)]',
    `secKey_ts` ENUM('None','SR','NMS','CME','ICE','CFE','CBOT','NYMEX','COMEX','RUT','CIDX','ARCA','NYSE','OTC','NSDQ','MFQS','MIAX','DJI','CUSIP','ISIN','BXE','EUX','ANY','CXE','DXE','NXAM','NXBR','NXLS','NXML','NXOS','NXP','EUREX','CEDX','ICEFE') NOT NULL DEFAULT 'None' COMMENT 'SR Security Key [can be partially filled in (look at secType)]',
    `secKey_tk` VARCHAR(12) NOT NULL DEFAULT '' COMMENT 'SR Security Key [can be partially filled in (look at secType)]',
    `secKey_yr` SMALLINT UNSIGNED NOT NULL DEFAULT 0 COMMENT 'SR Security Key [can be partially filled in (look at secType)]',
    `secKey_mn` TINYINT UNSIGNED NOT NULL DEFAULT 0 COMMENT 'SR Security Key [can be partially filled in (look at secType)]',
    `secKey_dy` TINYINT UNSIGNED NOT NULL DEFAULT 0 COMMENT 'SR Security Key [can be partially filled in (look at secType)]',
    `secKey_xx` DOUBLE NOT NULL DEFAULT 0 COMMENT 'SR Security Key [can be partially filled in (look at secType)]',
    `secKey_cp` ENUM('Call','Put','Pair') NOT NULL DEFAULT 'Call' COMMENT 'SR Security Key [can be partially filled in (look at secType)]',
    `secType` ENUM('None','Stock','Future','Option','MLeg') NOT NULL DEFAULT 'None' COMMENT 'Security Type [Stock, Future, Option]',
    `securityID` TINYTEXT NOT NULL DEFAULT '' COMMENT 'unique exchange id (exch assigned)',
    `ticker_at` ENUM('None','EQT','IDX','BND','CUR','COM','FUT','SYN','WAR','FLX','MUT','SPD','MM','MF','COIN','TOKEN','ANY') NOT NULL DEFAULT 'None' COMMENT 'master underlier',
    `ticker_ts` ENUM('None','SR','NMS','CME','ICE','CFE','CBOT','NYMEX','COMEX','RUT','CIDX','ARCA','NYSE','OTC','NSDQ','MFQS','MIAX','DJI','CUSIP','ISIN','BXE','EUX','ANY','CXE','DXE','NXAM','NXBR','NXLS','NXML','NXOS','NXP','EUREX','CEDX','ICEFE') NOT NULL DEFAULT 'None' COMMENT 'master underlier',
    `ticker_tk` VARCHAR(12) NOT NULL DEFAULT '' COMMENT 'master underlier',
    `SRspreadID` BIGINT NOT NULL DEFAULT 0,
    `productClass` ENUM('None','Equity','Index','Future','Option','Spread') NOT NULL DEFAULT 'None',
    `underlierID` BIGINT NOT NULL DEFAULT 0 COMMENT 'underlier product id (option only) [securityID of undKey/undType product]',
    `undKey_at` ENUM('None','EQT','IDX','BND','CUR','COM','FUT','SYN','WAR','FLX','MUT','SPD','MM','MF','COIN','TOKEN','ANY') NOT NULL DEFAULT 'None' COMMENT 'SR Underlier Security Key [can be partially filled in (look at undType)] (option only)',
    `undKey_ts` ENUM('None','SR','NMS','CME','ICE','CFE','CBOT','NYMEX','COMEX','RUT','CIDX','ARCA','NYSE','OTC','NSDQ','MFQS','MIAX','DJI','CUSIP','ISIN','BXE','EUX','ANY','CXE','DXE','NXAM','NXBR','NXLS','NXML','NXOS','NXP','EUREX','CEDX','ICEFE') NOT NULL DEFAULT 'None' COMMENT 'SR Underlier Security Key [can be partially filled in (look at undType)] (option only)',
    `undKey_tk` VARCHAR(12) NOT NULL DEFAULT '' COMMENT 'SR Underlier Security Key [can be partially filled in (look at undType)] (option only)',
    `undKey_yr` SMALLINT UNSIGNED NOT NULL DEFAULT 0 COMMENT 'SR Underlier Security Key [can be partially filled in (look at undType)] (option only)',
    `undKey_mn` TINYINT UNSIGNED NOT NULL DEFAULT 0 COMMENT 'SR Underlier Security Key [can be partially filled in (look at undType)] (option only)',
    `undKey_dy` TINYINT UNSIGNED NOT NULL DEFAULT 0 COMMENT 'SR Underlier Security Key [can be partially filled in (look at undType)] (option only)',
    `undType` ENUM('None','Stock','Future','Option','MLeg') NOT NULL DEFAULT 'None' COMMENT 'Underlier Security Type [Stock, Future] (option only)',
    `productGroup` VARCHAR(6) NOT NULL DEFAULT '' COMMENT 'Underlying product code.  I.E. All GE (Eurodollar) spreads, options, futures will be in the same productGroup - This is the Asset field from the SecurityDefinition message',
    `securityGroup` VARCHAR(6) NOT NULL DEFAULT '' COMMENT 'Exchange specific code for a group of related securities that are all affected by market events.  I.E. All E-mini weekly options (EW) - This is SecurityGroup field from the SecurityDefinition messages',
    `marketSegmentID` INT NOT NULL DEFAULT 0 COMMENT 'Exchange specific market segment identifier',
    `ricCode` VARCHAR(32) NOT NULL DEFAULT '' COMMENT 'Full RIC Code - only provided for non-user defined instruments',
    `securityDesc` TINYTEXT NOT NULL DEFAULT '' COMMENT 'full exchange symbol',
    `exchange` VARCHAR(8) NOT NULL DEFAULT '' COMMENT 'listing exchange',
    `productType` ENUM('None','Outright','CalSpr','EqCalSpr','FXCalSpr','RedTick','BFly','Condor','Strip','InterCmd','Pack','MnPack','PackBFly','DblBFly','PackSpr','Crck','Bndl','BndlSpr','EnrStrp','BalStrp','UnbalStrp','EnICStrp','IRICStrp','ITRICSpr','UserDef','Combo','TAS','TASCalSpr','TAA','TIC','BIC','TAP','Index') NOT NULL DEFAULT 'None',
    `productTerm` ENUM('None','Month','Day','Week','BalanceOfMonth','Quarter','Season','BalanceOfWeek','CalendarYear','Variable','Custom','SameDay','NextDay','Weekly','Pack','Bundle','IRSAndCDSTenor','Year') NOT NULL DEFAULT 'None',
    `productIndexType` ENUM('None','NextDay','FirstOfMonth','VWA','Russel') NOT NULL DEFAULT 'None',
    `productRate` FLOAT NOT NULL DEFAULT 0,
    `contractSize` FLOAT NOT NULL DEFAULT 0,
    `contractUnit` ENUM('None','AUD','BBL','BDFT','BRL','BU','CAD','CHF','CTRCT','CUR','CWT','CZK','EUR','GAL','GBP','HUF','ILS','IPNT','JPY','KRW','LBS','MMBTU','MWH','MXN','MYR','NOK','NZD','PLN','RMB','RUR','SEK','TON','TRY','TRYOZ','USD','ZAR') NOT NULL DEFAULT 'None',
    `priceFormat` ENUM('None','N0','N1','N2','N3','N4','N5','N6','N7','F4','F8','Q8','F16','F32','H32','Q32','F64','H64','FullPenny','PartPenny','PartNickle','EQT','V1','V2','V3','V4','V5','V6','V7','V8','V9','V10','V11','V12','V13','V14','V15','A0','A1','A2','A3','A4','A5','A6','A7','E32') NOT NULL DEFAULT 'None',
    `minTickSize` DOUBLE NOT NULL DEFAULT 0,
    `displayFactor` DOUBLE NOT NULL DEFAULT 0,
    `strikeScale` DOUBLE NOT NULL DEFAULT 0 COMMENT 'manual strike price adjustment multiplier (used for some CME products if set, otherwise displayFactor is used) (okey_xx = strikePrice * manualStrikeScale)',
    `minLotSize` SMALLINT NOT NULL DEFAULT 0 COMMENT 'minimum lot size',
    `bookDepth` SMALLINT NOT NULL DEFAULT 0 COMMENT 'levels in the Globex quote book',
    `impliedBookDepth` SMALLINT NOT NULL DEFAULT 0 COMMENT 'levels in the globex implied quote book (0 if no implied depth)',
    `impMarketInd` SMALLINT NOT NULL DEFAULT 0 COMMENT 'implied market type (0 = no implied, 1 = implied in, 2 = implied out, 3 = implied in & out)',
    `minPriceIncrementAmount` FLOAT NOT NULL DEFAULT 0 COMMENT '(depricate) minimum price amount (points per handle)',
    `parValue` FLOAT NOT NULL DEFAULT 0 COMMENT 'per contract par value',
    `contMultiplier` FLOAT NOT NULL DEFAULT 0 COMMENT 'contract deliverable multipler',
    `cabPrice` DOUBLE NOT NULL DEFAULT 0 COMMENT '(depricate) cabinet price (minimum closing price for OOM options)',
    `tradeCurr` ENUM('None','AUD','BRL','CAD','CHF','CNH','CNY','EUR','GBP','JPY','KRW','MXN','MYR','NOK','NZD','SEK','TRY','USD','USDCents','CZK','ZAR','HUF','USX','GBX') NOT NULL DEFAULT 'None',
    `settleCurr` ENUM('None','AUD','BRL','CAD','CHF','CNH','CNY','EUR','GBP','JPY','KRW','MXN','MYR','NOK','NZD','SEK','TRY','USD','USDCents','CZK','ZAR','HUF','USX','GBX') NOT NULL DEFAULT 'None',
    `strikeCurr` ENUM('None','AUD','BRL','CAD','CHF','CNH','CNY','EUR','GBP','JPY','KRW','MXN','MYR','NOK','NZD','SEK','TRY','USD','USDCents','CZK','ZAR','HUF','USX','GBX') NOT NULL DEFAULT 'None',
    `expiration` DATETIME(6) NOT NULL DEFAULT '1900-01-01 00:00:00.000000' COMMENT 'future expiration or option expiration (if product is an option). we use the last TRADING day as the expiration date.',
    `maturity` DATE NOT NULL DEFAULT '1900-01-01' COMMENT 'future maturity date or option maturity date.  this is the delivery month.',
    `exerciseType` ENUM('None','American','European','Asian','Cliquet') NOT NULL DEFAULT 'None' COMMENT '(depricate; in RootDefinition) Exercise style',
    `userDefined` ENUM('None','Yes','No') NOT NULL DEFAULT 'None',
    `decayStartYear` SMALLINT NOT NULL DEFAULT 0,
    `decayStartMonth` TINYINT UNSIGNED NOT NULL DEFAULT 0,
    `decayStartDay` TINYINT UNSIGNED NOT NULL DEFAULT 0,
    `decayQty` INT NOT NULL DEFAULT 0 COMMENT 'daily decay quantity',
    `priceRatio` DOUBLE NOT NULL DEFAULT 0 COMMENT 'price ratio for interest rate intercommodity spreads',
    `timestamp` DATETIME(6) NOT NULL DEFAULT '1900-01-01 00:00:00.000000',
    `LegsList` JSON NOT NULL DEFAULT JSON_ARRAY() CHECK(JSON_VALID(LegsList)),
    PRIMARY KEY USING HASH (`secKey_tk`,`secKey_yr`,`secKey_mn`,`secKey_dy`,`secKey_xx`,`secKey_cp`,`secKey_at`,`secKey_ts`,`secType`)
) ENGINE=SRSE DEFAULT CHARSET=LATIN1 COMMENT='SpiderRock normalized exchange product definitions.  Includes future, option, and spread definitions from a number of exchanges.  TickerDefinitions, RootDefinitions and CCodeDefinitions are consistent with these records.';

SELECT TABLE EXAMPLE QUERY

SELECT
    `secKey_at`,
    `secKey_ts`,
    `secKey_tk`,
    `secKey_yr`,
    `secKey_mn`,
    `secKey_dy`,
    `secKey_xx`,
    `secKey_cp`,
    `secType`,
    `securityID`,
    `ticker_at`,
    `ticker_ts`,
    `ticker_tk`,
    `SRspreadID`,
    `productClass`,
    `underlierID`,
    `undKey_at`,
    `undKey_ts`,
    `undKey_tk`,
    `undKey_yr`,
    `undKey_mn`,
    `undKey_dy`,
    `undType`,
    `productGroup`,
    `securityGroup`,
    `marketSegmentID`,
    `ricCode`,
    `securityDesc`,
    `exchange`,
    `productType`,
    `productTerm`,
    `productIndexType`,
    `productRate`,
    `contractSize`,
    `contractUnit`,
    `priceFormat`,
    `minTickSize`,
    `displayFactor`,
    `strikeScale`,
    `minLotSize`,
    `bookDepth`,
    `impliedBookDepth`,
    `impMarketInd`,
    `minPriceIncrementAmount`,
    `parValue`,
    `contMultiplier`,
    `cabPrice`,
    `tradeCurr`,
    `settleCurr`,
    `strikeCurr`,
    `expiration`,
    `maturity`,
    `exerciseType`,
    `userDefined`,
    `decayStartYear`,
    `decayStartMonth`,
    `decayStartDay`,
    `decayQty`,
    `priceRatio`,
    `timestamp`,
    `LegsList`
FROM `SRAnalytics`.`MsgProductDefinitionV2`
WHERE 
    /* Replace with a ENUM('None','EQT','IDX','BND','CUR','COM','FUT','SYN','WAR','FLX','MUT','SPD','MM','MF','COIN','TOKEN','ANY') */ 
    `secKey_at` = 'None'
  AND
    /* Replace with a ENUM('None','SR','NMS','CME','ICE','CFE','CBOT','NYMEX','COMEX','RUT','CIDX','ARCA','NYSE','OTC','NSDQ','MFQS','MIAX','DJI','CUSIP','ISIN','BXE','EUX','ANY','CXE','DXE','NXAM','NXBR','NXLS','NXML','NXOS','NXP','EUREX','CEDX','ICEFE') */ 
    `secKey_ts` = 'None'
  AND
    /* Replace with a VARCHAR(12) */ 
    `secKey_tk` = 'Example_secKey_tk'
  AND
    /* Replace with a SMALLINT UNSIGNED */ 
    `secKey_yr` = 123
  AND
    /* Replace with a TINYINT UNSIGNED */ 
    `secKey_mn` = 1
  AND
    /* Replace with a TINYINT UNSIGNED */ 
    `secKey_dy` = 1
  AND
    /* Replace with a DOUBLE */ 
    `secKey_xx` = 4.56
  AND
    /* Replace with a ENUM('Call','Put','Pair') */ 
    `secKey_cp` = 'Call'
  AND
    /* Replace with a ENUM('None','Stock','Future','Option','MLeg') */ 
    `secType` = 'None';

Doc Columns Query

SELECT * FROM SRAnalytics.doccolumns WHERE TABLE_NAME='ProductDefinitionV2' ORDER BY ordinal_position ASC;