SpdrAutoHedgeState

V8 Message Definiton

SpdrAutoHedgeState records are published by autohedge servers and describe the current state of an autohedge controller that is managing RiskGroup autohedge orders.

METADATA

Attribute	Value
Topic	5290-strategy-autohedge
MLink Token	SystemData
Product	SRTrade
accessType	SELECT

Table Definition

Field	Type	Key	Default Value	Comment
accnt	VARCHAR(16)	PRI	''
riskGroupId	CHAR(19)	PRI	'0000-0000-0000-0000'
hedgeSecKey_at	enum - AssetType	PRI	'None'	Execution Hedge SecKey from SpdrAutoHedgeControltargetSecKey
hedgeSecKey_ts	enum - TickerSrc	PRI	'None'	Execution Hedge SecKey from SpdrAutoHedgeControltargetSecKey
hedgeSecKey_tk	VARCHAR(12)	PRI	''	Execution Hedge SecKey from SpdrAutoHedgeControltargetSecKey
hedgeSecKey_yr	SMALLINT UNSIGNED	PRI	0	Execution Hedge SecKey from SpdrAutoHedgeControltargetSecKey
hedgeSecKey_mn	TINYINT UNSIGNED	PRI	0	Execution Hedge SecKey from SpdrAutoHedgeControltargetSecKey
hedgeSecKey_dy	TINYINT UNSIGNED	PRI	0	Execution Hedge SecKey from SpdrAutoHedgeControltargetSecKey
hedgeSecType	enum - SpdrKeyType	PRI	'None'	Execution Hedge SecType from SpdrAutoHedgeControltargetSecType can be None
clientFirm	VARCHAR(16)	PRI	''
groupingCode	CHAR(19)		'0000-0000-0000-0000'
ticker_at	enum - AssetType		'None'	ticker group eg ES ZN
ticker_ts	enum - TickerSrc		'None'	ticker group eg ES ZN
ticker_tk	VARCHAR(12)		''	ticker group eg ES ZN
securityDesc	TINYTEXT		''
ssaleFlag	enum - ShortSaleFlag		'None'
positionType	enum - PositionType		'None'
hedgeState	enum - HedgeGroupState		'None'
hedgeText	TINYTEXT		''
uPrc	DOUBLE		0	hedge target underlier price
isDeltaMixed	enum - YesNo		'None'
accumulatorError	TINYTEXT		''
grpDeltaBot	DOUBLE		0	option delta bot in ctrlDe units
grpDeltaSld	DOUBLE		0
grpDDeltaBot	DOUBLE		0	option delta bot using uMid at time of option fill
grpDDeltaSld	DOUBLE		0
lastGrpFillDttm	DATETIME(6)		'1900-01-01 00:00:00.000000'
ctrlDeltaBot	DOUBLE		0	hedge target delta units bot
ctrlDeltaSld	DOUBLE		0
ctrlDDeltaBot	DOUBLE		0	hedge target delta bot using actual fill price
ctrlDDeltaSld	DOUBLE		0
lastCtrlFillDttm	DATETIME(6)		'1900-01-01 00:00:00.000000'
absOptCn	INT		0	total number of option contracts executed in this hedge group
netDelta	DOUBLE		0	grpDeltaBot grpDeltaSld ctrlDeltaBot ctrlDeltaSld
netDDelta	DOUBLE		0	grpDDeltaBot grpDDeltaSld ctrlDDeltaBot ctrlDDeltaSld
openHedgeTime	FLOAT		0	size weighted average time in seconds hedge risk has been open executions to fills
hedgeTargetVol	FLOAT		0	implied expected today annualized volatility for hedge target
expectedPrcRange	FLOAT		0	SQRTavgHedgeTime x hedgeTargetVol cone
opnDeltaBot	DOUBLE		0	open risk delta bot in ctrlDe units
opnDeltaSld	DOUBLE		0
opnDDeltaBot	DOUBLE		0	open risk delta bot in ctrlDe units
opnDDeltaSld	DOUBLE		0
clsDeltaBot	DOUBLE		0	close risk delta bot in ctrlDe units
clsDeltaSld	DOUBLE		0
clsDDeltaBot	DOUBLE		0	close risk delta bot in ctrlDe units
clsDDeltaSld	DOUBLE		0
slippagePnL	FLOAT		0	opnDeltaBot clsDeltaPrcSld opnDeltaPrcBot opnDeltaSld opnDeltaPrcSld clsDeltaPrcBot
slippageUnitPnL	FLOAT		0	slippagePnL grpDeltaBot grpDeltaSld pnl unit share or fc
slippageNormPnL	FLOAT		0	slippageUnitPnL expectedPrcRange
minDDeltaBand	FLOAT		0
maxDDeltaBand	FLOAT		0
bParentNumber	BIGINT		0
bActiveSize	INT		0
bBrkrStatus	TINYTEXT		''
bBrkrError	TINYTEXT		''
sParentNumber	BIGINT		0
sActiveSize	INT		0
sBrkrStatus	TINYTEXT		''
sBrkrError	TINYTEXT		''
ddMult	DOUBLE		0
underliersPerCn	INT		0	future underliers per contract
underlierType	enum - UnderlierType		'None'	type of underlier affects greek calculations
pointValue	DOUBLE		0	NLV value of a single point change in display premium hedge target
pointCurrency	enum - Currency		'None'
liveHedgeMark	DOUBLE		0	hedgeSecKey live mark midmarket zero in SRSE
updtReason	VARCHAR(20)		''
timestamp	DATETIME(6)		'1900-01-01 00:00:00.000000'	SR system timestamp

PRIMARY KEY DEFINITION (Unique)

Field	Sequence
accnt	1
riskGroupId	2
hedgeSecKey_tk	3
hedgeSecKey_yr	4
hedgeSecKey_mn	5
hedgeSecKey_dy	6
hedgeSecKey_at	7
hedgeSecKey_ts	8
hedgeSecType	9
clientFirm	10

CREATE TABLE EXAMPLE QUERY

CREATE TABLE `SRTrade`.`MsgSRAutoHedgeState` (
    `accnt` VARCHAR(16) NOT NULL DEFAULT '',
    `riskGroupId` CHAR(19) NOT NULL DEFAULT '0000-0000-0000-0000',
    `hedgeSecKey_at` ENUM('None','EQT','IDX','BND','CUR','COM','FUT','SYN','WAR','FLX','MUT','SPD','MM','MF','COIN','TOKEN','ANY') NOT NULL DEFAULT 'None' COMMENT 'Execution Hedge SecKey (from SpdrAutoHedgeControl.targetSecKey)',
    `hedgeSecKey_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' COMMENT 'Execution Hedge SecKey (from SpdrAutoHedgeControl.targetSecKey)',
    `hedgeSecKey_tk` VARCHAR(12) NOT NULL DEFAULT '' COMMENT 'Execution Hedge SecKey (from SpdrAutoHedgeControl.targetSecKey)',
    `hedgeSecKey_yr` SMALLINT UNSIGNED NOT NULL DEFAULT 0 COMMENT 'Execution Hedge SecKey (from SpdrAutoHedgeControl.targetSecKey)',
    `hedgeSecKey_mn` TINYINT UNSIGNED NOT NULL DEFAULT 0 COMMENT 'Execution Hedge SecKey (from SpdrAutoHedgeControl.targetSecKey)',
    `hedgeSecKey_dy` TINYINT UNSIGNED NOT NULL DEFAULT 0 COMMENT 'Execution Hedge SecKey (from SpdrAutoHedgeControl.targetSecKey)',
    `hedgeSecType` ENUM('None','Stock','Future','Option','MLeg') NOT NULL DEFAULT 'None' COMMENT 'Execution Hedge SecType (from SpdrAutoHedgeControl.targetSecType) [can be None]',
    `clientFirm` VARCHAR(16) NOT NULL DEFAULT '',
    `groupingCode` CHAR(19) NOT NULL DEFAULT '0000-0000-0000-0000',
    `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 'ticker group (eg. @ES, @ZN)',
    `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' COMMENT 'ticker group (eg. @ES, @ZN)',
    `ticker_tk` VARCHAR(12) NOT NULL DEFAULT '' COMMENT 'ticker group (eg. @ES, @ZN)',
    `securityDesc` TINYTEXT NOT NULL DEFAULT '',
    `ssaleFlag` ENUM('None','Long','Short','Exempt','Auto','Open','Close','Cover','NA') NOT NULL DEFAULT 'None',
    `positionType` ENUM('None','Opening','Closing','Auto') NOT NULL DEFAULT 'None',
    `hedgeState` ENUM('None','NoHedgeCtrl','MLegHold','InBand','BandSh','BandLn','Exception','DDMultErr','UndTypeErr','UndPerCnErr','Init','UPrcErr','AccumulatorError') NOT NULL DEFAULT 'None',
    `hedgeText` TINYTEXT NOT NULL DEFAULT '',
    `uPrc` DOUBLE NOT NULL DEFAULT 0 COMMENT 'hedge target underlier price',
    `isDeltaMixed` ENUM('None','Yes','No') NOT NULL DEFAULT 'None',
    `accumulatorError` TINYTEXT NOT NULL DEFAULT '',
    `grpDeltaBot` DOUBLE NOT NULL DEFAULT 0 COMMENT 'option delta bot (in ctrlDe units)',
    `grpDeltaSld` DOUBLE NOT NULL DEFAULT 0,
    `grpDDeltaBot` DOUBLE NOT NULL DEFAULT 0 COMMENT 'option $delta bot (using uMid at time of option fill)',
    `grpDDeltaSld` DOUBLE NOT NULL DEFAULT 0,
    `lastGrpFillDttm` DATETIME(6) NOT NULL DEFAULT '1900-01-01 00:00:00.000000',
    `ctrlDeltaBot` DOUBLE NOT NULL DEFAULT 0 COMMENT 'hedge target delta (units) bot',
    `ctrlDeltaSld` DOUBLE NOT NULL DEFAULT 0,
    `ctrlDDeltaBot` DOUBLE NOT NULL DEFAULT 0 COMMENT 'hedge target $delta bot (using actual fill price)',
    `ctrlDDeltaSld` DOUBLE NOT NULL DEFAULT 0,
    `lastCtrlFillDttm` DATETIME(6) NOT NULL DEFAULT '1900-01-01 00:00:00.000000',
    `absOptCn` INT NOT NULL DEFAULT 0 COMMENT 'total number of option contracts executed in this hedge group',
    `netDelta` DOUBLE NOT NULL DEFAULT 0 COMMENT 'grpDeltaBot - grpDeltaSld + ctrlDeltaBot - ctrlDeltaSld',
    `netDDelta` DOUBLE NOT NULL DEFAULT 0 COMMENT 'grpDDeltaBot - grpDDeltaSld + ctrlDDeltaBot - ctrlDDeltaSld',
    `openHedgeTime` FLOAT NOT NULL DEFAULT 0 COMMENT 'size weighted average time (in seconds) hedge risk has been open (executions to fills)',
    `hedgeTargetVol` FLOAT NOT NULL DEFAULT 0 COMMENT 'implied / expected today (annualized) volatility for hedge target',
    `expectedPrcRange` FLOAT NOT NULL DEFAULT 0 COMMENT 'SQRT(avgHedgeTime x hedgeTargetVol) cone',
    `opnDeltaBot` DOUBLE NOT NULL DEFAULT 0 COMMENT 'open risk delta bot (in ctrlDe units)',
    `opnDeltaSld` DOUBLE NOT NULL DEFAULT 0,
    `opnDDeltaBot` DOUBLE NOT NULL DEFAULT 0 COMMENT 'open risk $delta bot (in ctrlDe units)',
    `opnDDeltaSld` DOUBLE NOT NULL DEFAULT 0,
    `clsDeltaBot` DOUBLE NOT NULL DEFAULT 0 COMMENT 'close risk delta bot (in ctrlDe units)',
    `clsDeltaSld` DOUBLE NOT NULL DEFAULT 0,
    `clsDDeltaBot` DOUBLE NOT NULL DEFAULT 0 COMMENT 'close risk $delta bot (in ctrlDe units)',
    `clsDDeltaSld` DOUBLE NOT NULL DEFAULT 0,
    `slippagePnL` FLOAT NOT NULL DEFAULT 0 COMMENT 'opnDeltaBot * (clsDeltaPrcSld - opnDeltaPrcBot) + opnDeltaSld * (opnDeltaPrcSld - clsDeltaPrcBot)',
    `slippageUnitPnL` FLOAT NOT NULL DEFAULT 0 COMMENT 'slippagePnL / (grpDeltaBot + grpDeltaSld) [pnl / unit (share or fc)]',
    `slippageNormPnL` FLOAT NOT NULL DEFAULT 0 COMMENT 'slippageUnitPnL / expectedPrcRange',
    `minDDeltaBand` FLOAT NOT NULL DEFAULT 0,
    `maxDDeltaBand` FLOAT NOT NULL DEFAULT 0,
    `bParentNumber` BIGINT NOT NULL DEFAULT 0,
    `bActiveSize` INT NOT NULL DEFAULT 0,
    `bBrkrStatus` TINYTEXT NOT NULL DEFAULT '',
    `bBrkrError` TINYTEXT NOT NULL DEFAULT '',
    `sParentNumber` BIGINT NOT NULL DEFAULT 0,
    `sActiveSize` INT NOT NULL DEFAULT 0,
    `sBrkrStatus` TINYTEXT NOT NULL DEFAULT '',
    `sBrkrError` TINYTEXT NOT NULL DEFAULT '',
    `ddMult` DOUBLE NOT NULL DEFAULT 0,
    `underliersPerCn` INT NOT NULL DEFAULT 0 COMMENT 'future underliers per contract',
    `underlierType` ENUM('None','Equity','Other','FX') NOT NULL DEFAULT 'None' COMMENT 'type of underlier (affects $greek calculations)',
    `pointValue` DOUBLE NOT NULL DEFAULT 0 COMMENT '$NLV value of a single point change in display premium (hedge target)',
    `pointCurrency` 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',
    `liveHedgeMark` DOUBLE NOT NULL DEFAULT 0 COMMENT 'hedgeSecKey live mark (mid-market) [zero in SRSE]',
    `updtReason` VARCHAR(20) NOT NULL DEFAULT '',
    `timestamp` DATETIME(6) NOT NULL DEFAULT '1900-01-01 00:00:00.000000' COMMENT 'SR system timestamp',
    CONSTRAINT nonnegative_riskGroupId CHECK(ASCII(riskGroupId) < 56),
    CONSTRAINT nonnegative_groupingCode CHECK(ASCII(groupingCode) < 56),
    PRIMARY KEY USING HASH (`accnt`,`riskGroupId`,`hedgeSecKey_tk`,`hedgeSecKey_yr`,`hedgeSecKey_mn`,`hedgeSecKey_dy`,`hedgeSecKey_at`,`hedgeSecKey_ts`,`hedgeSecType`,`clientFirm`)
) ENGINE=SRSE DEFAULT CHARSET=LATIN1 COMMENT='SpdrAutoHedgeState records are published by autohedge servers and describe the current state of an autohedge controller that is managing RiskGroup autohedge orders.';

SELECT TABLE EXAMPLE QUERY

SELECT
    `accnt`,
    `riskGroupId`,
    `hedgeSecKey_at`,
    `hedgeSecKey_ts`,
    `hedgeSecKey_tk`,
    `hedgeSecKey_yr`,
    `hedgeSecKey_mn`,
    `hedgeSecKey_dy`,
    `hedgeSecType`,
    `clientFirm`,
    `groupingCode`,
    `ticker_at`,
    `ticker_ts`,
    `ticker_tk`,
    `securityDesc`,
    `ssaleFlag`,
    `positionType`,
    `hedgeState`,
    `hedgeText`,
    `uPrc`,
    `isDeltaMixed`,
    `accumulatorError`,
    `grpDeltaBot`,
    `grpDeltaSld`,
    `grpDDeltaBot`,
    `grpDDeltaSld`,
    `lastGrpFillDttm`,
    `ctrlDeltaBot`,
    `ctrlDeltaSld`,
    `ctrlDDeltaBot`,
    `ctrlDDeltaSld`,
    `lastCtrlFillDttm`,
    `absOptCn`,
    `netDelta`,
    `netDDelta`,
    `openHedgeTime`,
    `hedgeTargetVol`,
    `expectedPrcRange`,
    `opnDeltaBot`,
    `opnDeltaSld`,
    `opnDDeltaBot`,
    `opnDDeltaSld`,
    `clsDeltaBot`,
    `clsDeltaSld`,
    `clsDDeltaBot`,
    `clsDDeltaSld`,
    `slippagePnL`,
    `slippageUnitPnL`,
    `slippageNormPnL`,
    `minDDeltaBand`,
    `maxDDeltaBand`,
    `bParentNumber`,
    `bActiveSize`,
    `bBrkrStatus`,
    `bBrkrError`,
    `sParentNumber`,
    `sActiveSize`,
    `sBrkrStatus`,
    `sBrkrError`,
    `ddMult`,
    `underliersPerCn`,
    `underlierType`,
    `pointValue`,
    `pointCurrency`,
    `liveHedgeMark`,
    `updtReason`,
    `timestamp`
FROM `SRTrade`.`MsgSRAutoHedgeState`
WHERE 
    /* Replace with a VARCHAR(16) */ 
    `accnt` = 'Example_accnt'
  AND
    /* Replace with a CHAR(19) */
    `riskGroupId` = 'Example_riskGroupId'
  AND
    /* Replace with a ENUM('None','EQT','IDX','BND','CUR','COM','FUT','SYN','WAR','FLX','MUT','SPD','MM','MF','COIN','TOKEN','ANY') */ 
    `hedgeSecKey_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') */ 
    `hedgeSecKey_ts` = 'None'
  AND
    /* Replace with a VARCHAR(12) */ 
    `hedgeSecKey_tk` = 'Example_hedgeSecKey_tk'
  AND
    /* Replace with a SMALLINT UNSIGNED */ 
    `hedgeSecKey_yr` = 123
  AND
    /* Replace with a TINYINT UNSIGNED */ 
    `hedgeSecKey_mn` = 1
  AND
    /* Replace with a TINYINT UNSIGNED */ 
    `hedgeSecKey_dy` = 1
  AND
    /* Replace with a ENUM('None','Stock','Future','Option','MLeg') */ 
    `hedgeSecType` = 'None'
  AND
    /* Replace with a VARCHAR(16) */ 
    `clientFirm` = 'Example_clientFirm';

Doc Columns Query

SELECT * FROM SRTrade.doccolumns WHERE TABLE_NAME='SpdrAutoHedgeState' ORDER BY ordinal_position ASC;