Morgan Stanley Data Analyst Case (Institutional Securities): Trade Reconciliation, P&L Attribution, and Risk Signals

Context: You are a Data Analyst embedded with Morgan Stanley’s Institutional Securities (Sales & Trading) team. A VP asks for a concise morning readout explaining a sharp daily P&L swing and whether surveillance flags tied to abnormal trading activity are signal or data-quality noise. The exercise reflects Morgan Stanley’s risk-first, controls-driven culture and emphasizes practical SQL, clear assumptions, and stakeholder-ready storytelling. What you get (mock data schema): - trades(trade_id, order_id, symbol, side, qty, price, exec_ts, venue, counterparty_id, trader_id, ccy) - orders(order_id, symbol, side, limit_price, order_qty, submit_ts, client_id, route) - prices(symbol, ts, close, vwap) - positions(account_id, symbol, prev_eod_qty, eod_qty) - fx_rates(date, ccy, usd_rate) - counterparties(counterparty_id, name, rating) - calendar(date, is_trading_day) - corporate_actions(symbol, ex_date, action_type, factor) Case tasks (work through in priority order): 1) Data Quality & Reconciliation (controls mindset): - Identify duplicate/late prints, orphan trades (no matching order_id), and timezone boundary issues. - Propose primary/foreign keys and referential integrity checks; outline dedup logic for partial fills and cancel/replace. - Explain how you would adjust quantities/prices for splits in corporate_actions. 2) Core SQL/Python (outline is acceptable if coding tools aren’t provided): - Compute yesterday’s realized P&L in USD by symbol and counterparty, joining fx_rates for currency conversion and handling missing prices. - Attribute top 3 loss drivers (symbol or counterparty) and quantify slippage versus VWAP using window functions. - Produce a concentration view: share of desk volume by top 5 counterparties and symbols. 3) Risk Signals (pragmatic approximation): - Describe how you would estimate a simple 95% 1‑day historical VaR using last 100 trading days of P&L; state assumptions and caveats. - Flag abnormal activity using z-scores on notional and trade count by symbol; propose alert thresholds and how to minimize false positives. 4) Communication & Stakeholders (MS style): - In 3 bullets each, draft the trader’s readout (what/why/so‑what), the risk manager’s summary (exposures, VaR movement, control breaks), and the compliance view (flags, rationale, next steps). - Be explicit about data lineage, auditability, and when you would escalate before publishing. 5) Extension prompt (Wealth Management tie‑in): - If leadership asks to replicate a similar dashboard for Workplace/stock-plan clients, list the key metrics (exercise events, cash vs. sell‑to‑cover behavior, margin risk) and a privacy-first approach for PII. Clarifying questions interviewers expect: trading-day cutoffs and timezones, corporate action adjustments, handling T+1 corrections, decimal precision/rounding, and backfilling missing market data. Evaluation rubric (mirrors real MS expectations): - SQL/Data Manipulation: correct joins, window functions, scalable approach, and thoughtful handling of nulls/outliers. - Risk & Controls: documented assumptions, reconciliation discipline, audit trail, sensitivity to regulatory context (e.g., record‑keeping, surveillance). - Business Acumen: connects metrics to trader behavior, client impact, and exposure management; prioritizes what matters for the morning meeting. - Communication: concise, structured, stakeholder‑appropriate; defends tradeoffs and flags residual risk. - Culture Alignment: integrity, ownership, collaboration; raises issues early rather than over‑promising. Format & flow (typical MS case pacing): - 10 min brief and schema review - 45 min individual work (whiteboard/SQL outline acceptable) - 15 min walkthrough with probing follow‑ups (optimize query, handle T+1 breaks, propose dashboard) - 5 min Q&A Deliverables: a short verbal walkthrough plus a one‑page summary (findings, assumptions, risks, next steps) and 2–3 representative queries or pseudocode snippets.