SR Risk

Overview

• Prior day SpiderRock Connect archived positions and marks
• Start-of-period clearing firm positions and marks
• SpiderRock Connect executions and inbound away drops
• Live position greeks and slide risk across entire portfolio

SR Risk Tables

Query Examples

Start-of-Day and Position Records

SpiderRock can receive Start-of-Day (SOD) records from either a clearing source or directly from individual firms. These records are loaded into the SOD Clearing Record V5 table. Once they are in the SpiderRock system, the SOD records populate the Position Record tables. Each Position Record table contains both SOD data and any intra-day trades that have occurred. Clients can view this table as the base record of their risk exposure within the SpiderRock system. There is a base risk record for each asset type—options, stocks, and futures. Each base record rolls up into two types of summary records:

• Risk Cube Detail: provides risk data used in the Risk Manager
• Symbol Risk Summary: provides symbol-level risk data displayed in the Symbol Viewer, Risk Viewer, and the Hedge Tool Below are example queries that can be used to access each of these record types.

Sample Query 1: To insert a SOD record:

DESCRIBE srrisk.msgsodclearingrecordv5;
INSERT INTO srrisk.msgsodclearingrecordv5 (accnt,clientfirm,seckey_at,seckey_ts,seckey_tk,seckey_yr,seckey_mn,seckey_dy,seckey_xx,seckey_cp,sectype,tradedate,clrposition,clrmark,TIMESTAMP)
VALUES ('T.ACCNT','SRCORE','EQT','NMS','AAPL',2025,10,17,225,'Call','Option','2025-09-12', 123, 12.50, NOW());   
SELECT * FROM srrisk.msgsodclearingrecordv5
WHERE accnt = 'T.ACCNT';

Sample Query 2: The base risk records. To see the current position, a user must add the start of day position to the bot minus the sold.

Option:

SELECT okey_tk,okey_yr,okey_mn,okey_dy,okey_xx,okey_cp,optopnpos AS startofdaypostions,cnbot AS contractsbot,cnsld AS contractssold,optopnpos+cnbot-cnsld AS currentposition,a.*
FROM srrisk.msgoptionpositionrecordv5 a
WHERE accnt = 'T.ACCNT' AND tradedate = CURRENT_DATE;

Stock:

SELECT ticker_tk,stkopnpos AS startofdaypositon,shbot AS sharesbot, shsld AS sharessold,stkopnpos+shbot-shsld AS currentposition,a.*
FROM srrisk.msgstockpositionrecordV5 a
WHERE accnt = 'T.ACCNT' AND tradedate = CURRENT_DATE;

Future:

SELECT fkey_tk,fkey_yr,fkey_mn,fkey_dy,futopnpos AS startofdayposition,cnbot AS contractsbot,cnsld AS contractssold,futopnpos+cnbot-cnsld AS currentposition,a.*
FROM srrisk.msgfuturepositionrecordv5 a
WHERE accnt = 'T.ACCNT' AND tradedate = CURRENT_DATE;

Symbol-Level Summary and Risk Cube Detail

These positions roll up into two summary views. The first is the SymbolRiskSummaryV5 table, which provides a symbol-level summary of risk data. All the information displayed in the Symbol Summary in the Symbol Viewer, Risk Viewer, and Hedge Tool is available in this table.

Sample Query 3: To check this table:

SELECT Ticker_tk,delta,ddelta,gamma,theta,a.*
FROM srrisk.msgsymbolrisksummaryv5 a
WHERE accnt = 'T.ACCNT' AND tradedate = CURRENT_DATE;

The next roll-up view is the RiskCubeDetailV5 table, which combines position data across all asset classes. This view contains the same information displayed in the Risk Manager and allows users to aggregate symbol or account risk.

Like the Position Records, users must combine the Start-of-Day (SOD) records with the bought and sold quantities to determine the current position. Because this table evaluates all securities simultaneously, the primary keys used for identification are the SecKeys.

Sample Query 4: To check this table:

SELECT seckey_tk,seckey_yr,seckey_mn,seckey_dy,seckey_xx,seckey_cp,sectype,shopnpos+shbot-shsld AS currentsharespos,fcopnpos+fcbot-fcsld AS currentfutpos,cnopnpos+cnbot-cnsld AS currentoptpos,a.*
FROM srrisk.msgriskcubedetailv5 a
WHERE accnt = 'T.ACCNT' AND tradedate = CURRENT_DATE;

Sample Query 5: To pull position PnL from the RiskCubeDetailV5 record:

SELECT seckey_tk,seckey_yr,seckey_mn,seckey_dy,seckey_xx,seckey_cp,sectype,shopnpos+shbot-shsld AS currentsharespos,fcopnpos+fcbot-fcsld AS currentfutpos,cnopnpos+cnbot-cnsld AS currentoptpos,opnpnlvolmark AS VolPnl,opnpnlde AS PnlDelta,opnpnlga AS PnlGamma,
opnpnlTh AS PnlTheta,opnpnlve AS PnlVega,opnpnlvo AS PnlVolga,opnpnlva AS PnlVanna,opnpnlRate AS PnlRate,opnpnlsdiv AS PnlSdiv, opnpnlDdiv AS PnlDividend,opnpnlErr AS PnlError
FROM srrisk.msgriskcubedetailv5 a
WHERE accnt = 'T.ACCNT' AND seckey_tk = 'AAPL' AND tradedate = CURRENT_DATE;

Sample Query 6: To check for the current risk status of an account, refer to the AccountRiskRecordV5 table:

SELECT * FROM srrisk.msgaccountriskrecordv5 WHERE accnt='T.ACCNT';