SR Risk

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:

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:

• Symbol Risk Summary: Provides symbol-level risk data displayed in the Symbol Viewer, Risk Viewer, and the Hedge Tool
• Risk Cube Detail: Provides risk data used in the Risk Manager

Query Examples

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

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

   Retrieve 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

   Retrieve 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

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

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.

Query Example

1. Retrieve symbol risk summary:

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

Risk Cube Detail Table

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.

Query Examples

1. 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;
   

2. 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;
   

3. Retrieve the current risk status of an account from the AccountRiskRecordV5 table:

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