SpdrAuctionState

V8 Message Definiton

METADATA

Attribute	Value
Topic	2510-market-data-auctions
MLink Token	OptExchAuction
Product	SRAuction
accessType	SELECT

Table Definition

Field	Type	Key	Default Value	Comment
secKey_at	enum - AssetType	PRI	'None'
secKey_ts	enum - TickerSrc	PRI	'None'
secKey_tk	VARCHAR(12)	PRI	''
secKey_yr	SMALLINT UNSIGNED	PRI	0
secKey_mn	TINYINT UNSIGNED	PRI	0
secKey_dy	TINYINT UNSIGNED	PRI	0
secKey_xx	DOUBLE	PRI	0
secKey_cp	enum - CallPut	PRI	'Call'
secType	enum - SpdrKeyType	PRI	'None'
auctionExch	enum - OptExch	PRI	'None'	exchange handling the auction
auctionExDest	VARCHAR(16)	PRI	''	external exDest of auction usually means auction is offexchange
srAuctionID	BIGINT		0	unique SR AUCTION ID required when responding to an auction notice
exchAuctionId	VARCHAR(20)		''
exchAuctionType	VARCHAR(4)		''
isTestAuction	enum - YesNo		'None'	test auction should only respond from Taccnts
auctionEvent	enum - AuctionEvent		'None'
auctionShape	enum - NoticeShape		'None'
auctionType	enum - AuctionType		'None'
auctionSide	enum - BuySell		'None'	Market side clientimbalance side of auction if known responder should be opposite side
auctionSize	INT		0	size available to trade
auctionPrice	DOUBLE		0	auction price can be positive or negative
isAuctionPriceValid	enum - YesNo		'None'
auctionDuration	INT		0	expected auction imbalance action duration ms
auctionStartSize	INT		0	initial starting auction size
auctionStartPrice	DOUBLE		0	initial starting auction price
auctionStartTimestamp	BIGINT		0	auction start timestamp
minResponseSize	INT		0	minimum size of the response order
limitType	enum - AuctionLimitType		'None'	client imbalance limit type if available
firmType	enum - FirmType		'None'	firm type of the client side of auction if available
memberMPID	VARCHAR(10)		''	exchange member initiating auction if available
clientAccnt	VARCHAR(10)		''	client account designation if known
otherDetail	VARCHAR(16)		''	additional auction detail exchange specific
matchedSize	INT		0	size already matched may still be available to trade at a better price
numUpdates	TINYINT UNSIGNED		0	number of auction updates received not counting auction termination message
numResponses	TINYINT UNSIGNED		0	as reported by exchange if available
bestResponseSize	INT		0
bestResponsePrice	DOUBLE		0
cumFillQuantity	INT		0	as reported by exchange if available
avgFillPrice	DOUBLE		0
marketStatus	enum - MarketStatus		'None'	market status preopen open closed etc
srcTimestamp	BIGINT		0	source timestamp nanoseconds if available
netTimestamp	BIGINT		0	network timestamp message arrival direct exchange gateway
dgwTimestamp	BIGINT		0	network timestamp mbus message send direct exchange gateway
timestamp	DATETIME(6)		'1900-01-01 00:00:00.000000'
LegsList	JSON		'JSON_OBJECT()'

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
auctionExch	10
auctionExDest	11

JSON Block (LegsList)

Field	Type	Comment
legSecKey	OptionKey
legSecType	enum - legSecType
legSide	enum - legSide
legRatio	ushort	leg ratio 1 2 etc

CREATE TABLE EXAMPLE QUERY

CREATE TABLE `SRAuction`.`MsgSRAuctionState` (
    `secKey_at` ENUM('None','EQT','IDX','BND','CUR','COM','FUT','SYN','WAR','FLX','MUT','SPD','MM','MF','COIN','TOKEN','ANY') NOT NULL DEFAULT 'None',
    `secKey_ts` ENUM('None','SR','NMS','CME','ICE','CFE','CBOT','NYMEX','COMEX','RUT','CIDX','ARCA','NYSE','OTC','NSDQ','MFQS','MIAX','DJI','CUSIP','ISIN','BXE','ESX','ANY','CXE','DXE','NXAM','NXBR','NXDUB','NXLS','NXLDN','NXML','NXMLT','NXOS','NXP','EUREX','CEDX','ICEFE') NOT NULL DEFAULT 'None',
    `secKey_tk` VARCHAR(12) NOT NULL DEFAULT '',
    `secKey_yr` SMALLINT UNSIGNED NOT NULL DEFAULT 0,
    `secKey_mn` TINYINT UNSIGNED NOT NULL DEFAULT 0,
    `secKey_dy` TINYINT UNSIGNED NOT NULL DEFAULT 0,
    `secKey_xx` DOUBLE NOT NULL DEFAULT 0,
    `secKey_cp` ENUM('Call','Put','Pair') NOT NULL DEFAULT 'Call',
    `secType` ENUM('None','Stock','Future','Option','MLeg') NOT NULL DEFAULT 'None',
    `auctionExch` ENUM('None','AMEX','BOX','CBOE','ISE','NYSE','PHLX','NSDQ','BATS','C2','NQBX','MIAX','GMNI','CME','CBOT','NYMEX','COMEX','ICE','EDGO','MCRY','MPRL','SDRK','DQTE','EMLD','CFE','MEMX','EUREX','CEDX','NXAM','NXBR','NXLS','NXML','NXOS','NXP','ICEFE') NOT NULL DEFAULT 'None' COMMENT 'exchange handling the auction',
    `auctionExDest` VARCHAR(16) NOT NULL DEFAULT '' COMMENT 'external exDest of auction (usually means auction is off-exchange)',
    `srAuctionID` BIGINT NOT NULL DEFAULT 0 COMMENT 'unique SR AUCTION ID (required when responding to an auction notice)',
    `exchAuctionId` VARCHAR(20) NOT NULL DEFAULT '',
    `exchAuctionType` VARCHAR(4) NOT NULL DEFAULT '',
    `isTestAuction` ENUM('None','Yes','No') NOT NULL DEFAULT 'None' COMMENT 'test auction (should only respond from T.accnts)',
    `auctionEvent` ENUM('None','Start','Update','End') NOT NULL DEFAULT 'None',
    `auctionShape` ENUM('None','Single','MLeg') NOT NULL DEFAULT 'None',
    `auctionType` ENUM('None','Exposure','Improvement','Facilitation','Solicitation','Opening','Closing','RFQ','Block','Flash') NOT NULL DEFAULT 'None',
    `auctionSide` ENUM('None','Buy','Sell') NOT NULL DEFAULT 'None' COMMENT 'Market side (client/imbalance side of auction; if known) [responder should be opposite side]',
    `auctionSize` INT NOT NULL DEFAULT 0 COMMENT 'size available to trade',
    `auctionPrice` DOUBLE NOT NULL DEFAULT 0 COMMENT 'auction price (can be positive or negative)',
    `isAuctionPriceValid` ENUM('None','Yes','No') NOT NULL DEFAULT 'None',
    `auctionDuration` INT NOT NULL DEFAULT 0 COMMENT 'expected auction / imbalance action duration (ms)',
    `auctionStartSize` INT NOT NULL DEFAULT 0 COMMENT 'initial (starting) auction size',
    `auctionStartPrice` DOUBLE NOT NULL DEFAULT 0 COMMENT 'initial (starting) auction price',
    `auctionStartTimestamp` BIGINT NOT NULL DEFAULT 0 COMMENT 'auction start timestamp',
    `minResponseSize` INT NOT NULL DEFAULT 0 COMMENT 'minimum size of the response order',
    `limitType` ENUM('None','Market','Limit') NOT NULL DEFAULT 'None' COMMENT 'client / imbalance limit type (if available)',
    `firmType` ENUM('None','Customer','Firm','MarketMaker','ProCustomer','BrokerDealer','AwayMM','FirmJBO','BrkrDlrCust') NOT NULL DEFAULT 'None' COMMENT 'firm type of the client side of auction (if available)',
    `memberMPID` VARCHAR(10) NOT NULL DEFAULT '' COMMENT 'exchange member initiating auction (if available)',
    `clientAccnt` VARCHAR(10) NOT NULL DEFAULT '' COMMENT 'client account designation (if known)',
    `otherDetail` VARCHAR(16) NOT NULL DEFAULT '' COMMENT 'additional auction detail (exchange specific)',
    `matchedSize` INT NOT NULL DEFAULT 0 COMMENT 'size already matched (may still be available to trade at a better price)',
    `numUpdates` TINYINT UNSIGNED NOT NULL DEFAULT 0 COMMENT 'number of auction updates received (not counting auction termination message)',
    `numResponses` TINYINT UNSIGNED NOT NULL DEFAULT 0 COMMENT 'as reported by exchange (if available)',
    `bestResponseSize` INT NOT NULL DEFAULT 0,
    `bestResponsePrice` DOUBLE NOT NULL DEFAULT 0,
    `cumFillQuantity` INT NOT NULL DEFAULT 0 COMMENT 'as reported by exchange (if available)',
    `avgFillPrice` DOUBLE NOT NULL DEFAULT 0,
    `marketStatus` ENUM('None','PreOpen','PreCross','Cross','Open','Closed','Halted','AfterHours') NOT NULL DEFAULT 'None' COMMENT 'market status (pre-open, open, closed, etc)',
    `srcTimestamp` BIGINT NOT NULL DEFAULT 0 COMMENT 'source timestamp (nanoseconds) if available',
    `netTimestamp` BIGINT NOT NULL DEFAULT 0 COMMENT 'network timestamp message arrival @ direct exchange gateway',
    `dgwTimestamp` BIGINT NOT NULL DEFAULT 0 COMMENT 'network timestamp mbus message send @ direct exchange gateway',
    `timestamp` DATETIME(6) NOT NULL DEFAULT '1900-01-01 00:00:00.000000',
    `LegsList` JSON NOT NULL DEFAULT JSON_OBJECT() 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`,`auctionExch`,`auctionExDest`)
) ENGINE=SRSE DEFAULT CHARSET=LATIN1 COMMENT='';

SELECT TABLE EXAMPLE QUERY

SELECT
    `secKey_at`,
    `secKey_ts`,
    `secKey_tk`,
    `secKey_yr`,
    `secKey_mn`,
    `secKey_dy`,
    `secKey_xx`,
    `secKey_cp`,
    `secType`,
    `auctionExch`,
    `auctionExDest`,
    `srAuctionID`,
    `exchAuctionId`,
    `exchAuctionType`,
    `isTestAuction`,
    `auctionEvent`,
    `auctionShape`,
    `auctionType`,
    `auctionSide`,
    `auctionSize`,
    `auctionPrice`,
    `isAuctionPriceValid`,
    `auctionDuration`,
    `auctionStartSize`,
    `auctionStartPrice`,
    `auctionStartTimestamp`,
    `minResponseSize`,
    `limitType`,
    `firmType`,
    `memberMPID`,
    `clientAccnt`,
    `otherDetail`,
    `matchedSize`,
    `numUpdates`,
    `numResponses`,
    `bestResponseSize`,
    `bestResponsePrice`,
    `cumFillQuantity`,
    `avgFillPrice`,
    `marketStatus`,
    `srcTimestamp`,
    `netTimestamp`,
    `dgwTimestamp`,
    `timestamp`,
    `LegsList`
FROM `SRAuction`.`MsgSRAuctionState`
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','ESX','ANY','CXE','DXE','NXAM','NXBR','NXDUB','NXLS','NXLDN','NXML','NXMLT','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'
  AND
    /* Replace with a ENUM('None','AMEX','BOX','CBOE','ISE','NYSE','PHLX','NSDQ','BATS','C2','NQBX','MIAX','GMNI','CME','CBOT','NYMEX','COMEX','ICE','EDGO','MCRY','MPRL','SDRK','DQTE','EMLD','CFE','MEMX','EUREX','CEDX','NXAM','NXBR','NXLS','NXML','NXOS','NXP','ICEFE') */ 
    `auctionExch` = 'None'
  AND
    /* Replace with a VARCHAR(16) */ 
    `auctionExDest` = 'Example_auctionExDest';

Doc Columns Query

SELECT * FROM SRAuction.doccolumns WHERE TABLE_NAME='SpdrAuctionState' ORDER BY ordinal_position ASC;