Skip to main content
Version: Staging

StockExchImbalance

V8 Message Definiton

StockExchImbalance records contain live exchange closing auction imbalance details. Imbalance information can be available from more than one exchange for each ticker.\nFinal StockExchImbalance records are published to the SpiderRock elastic cluster nightly after the auction close.

METADATA

AttributeValue
Topic2990-market-data-stock
MLink TokenSystemData
ProductSRLive
accessTypeSELECT

Table Definition

FieldTypeKeyDefault ValueComment
ticker_atenum - AssetTypePRI'None'
ticker_tsenum - TickerSrcPRI'None'
ticker_tkVARCHAR(12)PRI''
auctionTimeDATETIME(6)PRI'1900-01-01 00:00:00.000000'Projected Auction Time hhmm
auctionTypeenum - AuctionReasonPRI'None'Auction type None Open Market Halt Closing RegulatoryImbalance
exchangeenum - PrimaryExchangePRI'None'
referencePxFLOAT0For Pillarpowered markets the Reference Price is used to calculate the Indicative Match Price
pairedQtyINT0For Pillarpowered markets the number of shares paired off at the Indicative Match Price
totalImbalanceQtyINT0For Pillarpowered markets the total imbalance quantity at the Indicative Match Price If the value is negative the imbalance is on the sell side if the value is positive the imbalance is on the buy side
marketImbalanceQtyINT0For Pillarpowered markets the total market order imbalance quantity at the Indicative Match Price If the value is negative the imbalance is on the sell side if the value is positive the imbalance is on the buy side
imbalanceSideenum - ImbalanceSide'None'The side of the TotalImbalanceQty
continuousBookClrPxFLOAT0For Pillarpowered markets the price at which all interest on the book can trade including auction and imbalance offset interest and disregarding auction collars
closingOnlyClrPxFLOAT0For Pillarpowered markets the price at which all eligible auctiononly interest would trade subject to auction collars
ssrFillingPxFLOAT0For Pillarpowered markets not supported and defaulted to 0
indicativeMatchPxFLOAT0For Pillarpowered markets the price that has the highest executable volume of auctioneligible shares subject to auction collars It includes the nondisplayed quantity of Reserve Orders
upperCollarFLOAT0If the IndicativeMatchPrice is not strictly between the UpperCollar and the LowerCollar special auction rules apply See Rule 735P for details
lowerCollarFLOAT0If the IndicativeMatchPrice is not strictly between the UpperCollar and the LowerCollar special auction rules apply See Rule 735P for details
auctionStatusenum - AuctionStatus'None'Indicates whether the auction will run
freezeStatusenum - YesNo'None'Indicates freeze
numExtensionsTINYINT UNSIGNED0Number of times the halt period has been extended
sourceTimeDATETIME(6)'1900-01-01 00:00:00.000000'Time record was generated in the order book in seconds
netTimestampBIGINT0PTP timestamp

PRIMARY KEY DEFINITION (Unique)

FieldSequence
ticker_tk1
ticker_at2
ticker_ts3
auctionTime4
auctionType5
exchange6

CREATE TABLE EXAMPLE QUERY

CREATE TABLE `SRLive`.`MsgStockExchImbalance` (
`ticker_at` ENUM('None','EQT','IDX','BND','CUR','COM','FUT','SYN','WAR','FLX','MUT','SPD','MM','MF','COIN','TOKEN','ANY') NOT NULL DEFAULT 'None',
`ticker_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',
`ticker_tk` VARCHAR(12) NOT NULL DEFAULT '',
`auctionTime` DATETIME(6) NOT NULL DEFAULT '1900-01-01 00:00:00.000000' COMMENT 'Projected Auction Time (hhmm).',
`auctionType` ENUM('None','Open','Market','Halt','Closing','RegulatoryImbalance') NOT NULL DEFAULT 'None' COMMENT 'Auction type: None; Open; Market; Halt; Closing; RegulatoryImbalance',
`exchange` ENUM('None','NYSE','NYSEArca','NYSEMkt','NASDAQ','NASDAQBOS','BATS','PHLX','IEXG','CSE','NSE','FINRA','PORTAL','OTC','CME','CBOT','NYMEX','COMEX','ICE','EUREX','XETRA','CDEX','BXE','CXE','DXE','LSE','NXAM','NXBR','NXDUB','NXLS','NXLDN','NXML','NXMLT','NXOS','NXP') NOT NULL DEFAULT 'None',
`referencePx` FLOAT NOT NULL DEFAULT 0 COMMENT 'For Pillar-powered markets, the Reference Price is used to calculate the Indicative Match Price.',
`pairedQty` INT NOT NULL DEFAULT 0 COMMENT 'For Pillar-powered markets, the number of shares paired off at the Indicative Match Price.',
`totalImbalanceQty` INT NOT NULL DEFAULT 0 COMMENT 'For Pillar-powered markets, the total imbalance quantity at the Indicative Match Price. If the value is negative, the imbalance is on the sell side; if the value is positive, the imbalance is on the buy side.',
`marketImbalanceQty` INT NOT NULL DEFAULT 0 COMMENT 'For Pillar-powered markets, the total market order imbalance quantity at the Indicative Match Price. If the value is negative, the imbalance is on the sell side; if the value is positive the imbalance is on the buy side.',
`imbalanceSide` ENUM('None','Buy','Sell','NoImbalance','InsufOrdsToCalc') NOT NULL DEFAULT 'None' COMMENT 'The side of the TotalImbalanceQty.',
`continuousBookClrPx` FLOAT NOT NULL DEFAULT 0 COMMENT 'For Pillar-powered markets, the price at which all interest on the book can trade, including auction and imbalance offset interest, and disregarding auction collars.',
`closingOnlyClrPx` FLOAT NOT NULL DEFAULT 0 COMMENT 'For Pillar-powered markets, the price at which all eligible auction-only interest would trade, subject to auction collars.',
`ssrFillingPx` FLOAT NOT NULL DEFAULT 0 COMMENT 'For Pillar-powered markets, not supported and defaulted to 0.',
`indicativeMatchPx` FLOAT NOT NULL DEFAULT 0 COMMENT 'For Pillar-powered markets, the price that has the highest executable volume of auction-eligible shares, subject to auction collars. It includes the non-displayed quantity of Reserve Orders.',
`upperCollar` FLOAT NOT NULL DEFAULT 0 COMMENT 'If the IndicativeMatchPrice is not strictly between the UpperCollar and the LowerCollar, special auction rules apply. See Rule 7.35P for details.',
`lowerCollar` FLOAT NOT NULL DEFAULT 0 COMMENT 'If the IndicativeMatchPrice is not strictly between the UpperCollar and the LowerCollar, special auction rules apply. See Rule 7.35P for details.',
`auctionStatus` ENUM('None','WillRunOpenAndClose','WillRunInterest','WillNotRunImbalance','WillNotRunClsAuction') NOT NULL DEFAULT 'None' COMMENT 'Indicates whether the auction will run.',
`freezeStatus` ENUM('None','Yes','No') NOT NULL DEFAULT 'None' COMMENT 'Indicates freeze',
`numExtensions` TINYINT UNSIGNED NOT NULL DEFAULT 0 COMMENT 'Number of times the halt period has been extended.',
`sourceTime` DATETIME(6) NOT NULL DEFAULT '1900-01-01 00:00:00.000000' COMMENT 'Time record was generated in the order book (in seconds)',
`netTimestamp` BIGINT NOT NULL DEFAULT 0 COMMENT 'PTP timestamp',
PRIMARY KEY USING HASH (`ticker_tk`,`ticker_at`,`ticker_ts`,`auctionTime`,`auctionType`,`exchange`)
) ENGINE=SRSE DEFAULT CHARSET=LATIN1 COMMENT='StockExchImbalance records contain live exchange closing auction imbalance details. Imbalance information can be available from more than one exchange for each ticker.\nFinal StockExchImbalance records are published to the SpiderRock elastic cluster nightly after the auction close.';

SELECT TABLE EXAMPLE QUERY

SELECT
`ticker_at`,
`ticker_ts`,
`ticker_tk`,
`auctionTime`,
`auctionType`,
`exchange`,
`referencePx`,
`pairedQty`,
`totalImbalanceQty`,
`marketImbalanceQty`,
`imbalanceSide`,
`continuousBookClrPx`,
`closingOnlyClrPx`,
`ssrFillingPx`,
`indicativeMatchPx`,
`upperCollar`,
`lowerCollar`,
`auctionStatus`,
`freezeStatus`,
`numExtensions`,
`sourceTime`,
`netTimestamp`
FROM `SRLive`.`MsgStockExchImbalance`
WHERE
/* Replace with a ENUM('None','EQT','IDX','BND','CUR','COM','FUT','SYN','WAR','FLX','MUT','SPD','MM','MF','COIN','TOKEN','ANY') */
`ticker_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') */
`ticker_ts` = 'None'
AND
/* Replace with a VARCHAR(12) */
`ticker_tk` = 'Example_ticker_tk'
AND
/* Replace with a DATETIME(6) */
`auctionTime` = '2022-01-01 12:34:56.000000'
AND
/* Replace with a ENUM('None','Open','Market','Halt','Closing','RegulatoryImbalance') */
`auctionType` = 'None'
AND
/* Replace with a ENUM('None','NYSE','NYSEArca','NYSEMkt','NASDAQ','NASDAQBOS','BATS','PHLX','IEXG','CSE','NSE','FINRA','PORTAL','OTC','CME','CBOT','NYMEX','COMEX','ICE','EUREX','XETRA','CDEX','BXE','CXE','DXE','LSE','NXAM','NXBR','NXDUB','NXLS','NXLDN','NXML','NXMLT','NXOS','NXP') */
`exchange` = 'None';

Doc Columns Query

SELECT * FROM SRLive.doccolumns WHERE TABLE_NAME='StockExchImbalance' ORDER BY ordinal_position ASC;