SR Risk

Overview

The SR Risk database provides comprehensive risk management data, including position records, marks, and portfolio analytics. Key features include:

• 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

The following image shows the available tables in SR Risk:

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 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

Sample Query 1: 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: Retrieve base risk records. To see the current position, add the start-of-day position to the bought quantity minus the sold quantity.

Option Position Record:

SELECT okey_tk, okey_yr, okey_mn, okey_dy, okey_xx, okey_cp, 
       optopnpos AS startofdaypositions, 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 Position Record:

SELECT ticker_tk, stkopnpos AS startofdayposition, 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 Position Record:

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 SymbolRiskSummaryV5 table 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: Retrieve symbol risk summary:

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

The RiskCubeDetailV5 table 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: Retrieve risk cube detail:

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: Retrieve 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: Retrieve the current risk status of an account from the AccountRiskRecordV5 table:

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