Build Queryable Options IV DB from Live API Polls
Capture SpiderRock LiveImpliedQuote snapshots for TSLA every 10s into SQLite: append full history for audits (12k+ rows in 2min), upsert latest view per option_key. Query to reconstruct vol smiles and track ATM IV/skew changes over time.
Dual-Table Schema Enables Time-Series Audits and Instant Current Views
Store live options analytics in two SQLite tables for balanced access patterns. implied_quote_history is append-only, preserving every snapshot with id autoincrement primary key, asof_ts (UTC ISO timestamp per poll), and option_key (stable identifier: symbol|expiry|strike|cp|at|ts) as join key. Indexes on (symbol, expiry, asof_ts) and (option_key, asof_ts) speed expiry-time or option-timeline queries. Columns capture surface IV (s_vol), ATM vol (atm_vol), Greeks (delta, gamma, theta, vega), underlying price (u_prc), years to expiry (years), rate, bid/ask/IVs, calc_source (filter to "Loop" for consistent snapshots), quote_ok flag (1 if bid/ask non-zero), and src_ts.
implied_quote_latest uses option_key primary key for upserts: each poll overwrites with newest values, setting last_asof_ts to current snapshot time. Same columns and index on (symbol, expiry). PRAGMA journal_mode=WAL and synchronous=NORMAL ensure reliable writes. This split avoids full-history scans for "current surface" while retaining audit trail—history grows unbounded (e.g., 1454 rows/snapshot × 9 polls = 12,806 total), latest stays flat at ~1454 rows.
Normalize and Poll API for Reliable Snapshots
Fetch via REST getmsgs on https://mlink-live.nms.saturn.spiderrockconnect.com/rest/json with apiKey, msgType=LiveImpliedQuote, where=okey.tk:eq:TSLA, limit=2000. Response: list of messages ending in QueryResult; filter to mTyp=LiveImpliedQuote, calcSource=Loop, non-zero sVol.
Flatten nested pkey.okey into option_key via |. Build DataFrame rows with all fields; sort by src_ts, dedupe latest per option_key. quote_ok = int(not (o_bid == 0 and o_ask == 0)) flags quoted options without dropping analytics-only rows.
Loop polls every poll_interval_s=10 for poll_duration_s=120: timestamp asof_ts, fetch/normalize/write. Batch executemany inserts history; upsert latest with on conflict(option_key) do update set all fields. Handles varying row counts (e.g., 1454 → snapshot_rows fluctuates due to limit). Production tip: pin expiries/strikes or interpolate to fixed moneyness for stability.
Reconstruct Smiles, Skew, and Metrics from History Queries
Query history for analysis: count rows per expiry (group by expiry order by n desc limit 10) to pick representative like 2026-11-20 (highest coverage). Pull asof_ts, strike, cp, s_vol, u_prc for expiry/symbol; filter calls; plot s_vol vs strike for timestamps (first/mid/last of ts_list).
Zoom near spot: s0 = u_prc.median(), strikes in [s0*0.6, s0*1.4] reveals ATM shifts invisible in full range. Enables questions like "TSLA surface at 10:32?" or "when skew steepened?"—replay via where symbol=? and expiry=? or option_key, asof_ts.
Track evolution: query timelines per option/expiry to compute ATM IV (min s_vol near spot), skew proxies (wing vs ATM deltas). Stored u_prc, years, rate support smile rebuilds or Greeks audits without re-API calls. Trade-off: API fees for data; limit caps chains; no interpolation here keeps ingestion simple but may vary strikes across polls.