# Data dictionary ## Coverage Masscrest covers US-listed single stocks and ETFs. OTC-traded names and index products (SPX, VIX, and similar) are out of scope. For every listed-option contract in scope, Masscrest tracks buy, sell and net flows across three investor cohorts: | Cohort | Description | |---|---| | Institutional | Long-only, long/short, market-neutral, long-biased, multi-strat and other equity hedge funds; mutual funds, sovereign and endowment funds, family offices and other asset managers; proprietary trading desks, banks and dealers trading on behalf of customers. | | Retail | Global self-directed retail traders (not investment advisors or private-banking clients). | | Interdealer / market maker | ~99% of trades take place against a market maker, so this cohort sits opposite to Institutional and Retail flows. Interdealer trades are between dealers, typically to offset greek exposure. | ## What we provide Three product surfaces sit on top of the same underlying inference layer: 1. **Option flow by contract** — buy, sell, and net trading imbalance for every listed contract, expressed in shares, premium, and greek-adjusted notional (delta, gamma, vega). Query with contract-level filters (`dte`, `abs_delta`, `callput`). Tables: [`option_flows_01_day`](/documentation/dictionary/option-flows-01-day) and [`option_flows_10_min`](/documentation/dictionary/option-flows-10-min). 2. **Option flow by underlying ticker** — the same trading imbalance aggregated to the underlying root symbol × callput. Pre-aggregated at ingest; use when you don't need contract-level filters. Available at the underlying grain (daily + 10-min) and rolled up to sector / industry / ETF / ADR / single-stock / all cohorts (daily only). Tables: [`underlying_option_flows_01_day`](/documentation/dictionary/underlying-option-flows-01-day), [`underlying_option_flows_10_min`](/documentation/dictionary/underlying-option-flows-10-min), and [`grouped_flow_signal_01_day`](/documentation/dictionary/grouped-flow-signal-01-day) (the grouped table carries the raw flow alongside the z-scored signal — see below). 3. **Signal** — proprietary indicator of abnormal net options positioning (delta, gamma, vega exposure) driven by institutional investors on the underlying. Historically correlated with subsequent excess returns; used to surface conviction at the ticker, industry, or sector level. Tables: [`underlying_flow_signals_01_day`](/documentation/dictionary/underlying-flow-signals-01-day) (per underlying) and [`grouped_flow_signal_01_day`](/documentation/dictionary/grouped-flow-signal-01-day) (aggregated to sector / industry / ETF / ADR / single-stock / all). | Surface | Grain | Daily | 10-min | |---|---|---|---| | Option flow by contract | `(date, figi, callput, strike, expiration)` | ✓ | ✓ | | Option flow by underlying ticker | `(date, figi, callput)` | ✓ | ✓ | | Option flow — grouped (sector / industry / …) | `(date, group_key, callput)` | ✓ | — | | Signal — per underlying | `(date, figi, callput)` | ✓ | — | | Signal — grouped (sector / industry / …) | `(date, group_key, callput)` | ✓ | — | **Typical uses** - **Follow institutional flow.** See where hedge funds and asset managers are building or unwinding positions. - **Spot retail-vs-institutional divergences.** Identify names where the two cohorts sit on opposite sides. - **Trade the optionality dimension.** Separate directional bets from vega and gamma positioning; know whether a move is premium-driven or forced by dealer hedging. - **Layer market narrative on price action.** Explain why a name moved when the tape alone doesn't tell you: accumulation ahead of a break, capitulation into a bottom. - **Control for flow in factor models.** A signal orthogonal to price and volume. Wire it in as a regression control alongside conventional risk factors. ## Delivery & availability Daily datasets are keyed on US-equity trading dates and delivered one calendar day later (Friday's data lands Saturday). Real-time notification is available via webhook; a polling fallback is documented below. ### Cadence - Frequency: T+1 — data for trading day T is delivered on calendar day T+1 (Friday's data lands Saturday). Data only exists for US equity trading days; delivery cadence itself is calendar-day, not trading-day. - Publish window: 05:00 – 09:00 UTC on the calendar day after the trading date. ### Webhook (recommended) Register an HTTPS endpoint via `POST /v1/webhooks` (returns HTTP 201) with your API key — no admin gesture required, no ticket to open. When each day's data lands, we push a signed JSON payload. Wire the delivery handler to your flat-files sync job and the day's parquets pull automatically the moment they're published, without polling the manifest. ```json { "event_type": "daily_options_flow.available", "trading_date": "2026-07-24", "published_at": "2026-07-25T04:37:12Z", "workflow_run_id": "aa1cb0c2-5b47-46f1-9c9a-8c9d1c2b0e33" } ``` Field semantics: - `trading_date`: the US equity session the data covers, in `YYYY-MM-DD` (UTC-derived, but represents the exchange trading session, not the write time). - `published_at`: ISO-8601 UTC timestamp of when the notification was dispatched. Data is fully written before this fires, so it is safe to start reading immediately. - `workflow_run_id`: unique id for the publishing run. Use as your idempotency key: dedupe on this if you receive the same message twice. - `event_type`: always `daily_options_flow.available` today. See "Supported events" below. ### Verifying the signature Every request carries two headers: - `X-Masscrest-Timestamp: ` - `X-Masscrest-Signature: sha256=` Compute HMAC-SHA256 over `{timestamp}.{raw_body}` using your webhook secret. Reject requests where the timestamp is more than 300 seconds from your clock to block replay attacks. The `secret` returned at registration is a 64-character hex string. Decode it to bytes before using it as the HMAC key; UTF-8-encoding the hex string produces the wrong signature. Python: ```python import hmac, hashlib, time def verify(secret_hex: str, timestamp: str, raw_body: bytes, received_sig: str) -> bool: # Reject stale requests (replay protection). if abs(int(time.time()) - int(timestamp)) > 300: return False key = bytes.fromhex(secret_hex) # NOT secret_hex.encode() expected = hmac.new(key, f"{timestamp}.".encode() + raw_body, hashlib.sha256).hexdigest() # Signature header format: "sha256=[,sha256=]" (comma-separated during rotation grace) for sig in received_sig.split(","): if hmac.compare_digest(f"sha256={expected}", sig.strip()): return True return False ``` Node.js: ```js const crypto = require("crypto"); function verify(secretHex, timestamp, rawBody, receivedSig) { if (Math.abs(Math.floor(Date.now() / 1000) - Number(timestamp)) > 300) return false; const key = Buffer.from(secretHex, "hex"); // NOT Buffer.from(secretHex) const expected = crypto .createHmac("sha256", key) .update(`${timestamp}.`) .update(rawBody) .digest("hex"); // Header may contain two signatures separated by "," during the 24h rotation grace window. return receivedSig.split(",").some((sig) => crypto.timingSafeEqual(Buffer.from(`sha256=${expected}`), Buffer.from(sig.trim())) ); } ``` ### Retry policy Retry on any non-2xx response or timeout after 10 seconds. One initial delivery plus up to six retries (seven POSTs worst case), spaced 30s, 2m, 10m, 1h, 6h, 24h. Use `workflow_run_id` as your idempotency key. After the sixth failed retry we disable the webhook and email you. ### Supported events One event type is live today: | Event type | Fires when | |---|---| | `daily_options_flow.available` | The daily options-flow pipeline completes for a session (T+1), between 05:00 and 09:00 UTC on the calendar day after the trading date (Friday's data fires Saturday). | Additional events will be listed here as we add them. Register only for events you handle; an unrecognized event type is rejected at registration (HTTP 422). ### Managing webhooks Self-service via your API key — every endpoint below authenticates with the same `X-API-Key` you use for `/v0/*` reads, and each key only sees and manages its own webhooks. Trial accounts may register up to **2 webhooks**; paid accounts are unlimited. | Method | Path | Purpose | |---|---|---| | `POST` | `/v1/webhooks` | Register a URL. Returns `webhook_id` + `secret` (secret shown once). | | `GET` | `/v1/webhooks` | List your webhooks (secrets redacted). | | `GET` | `/v1/webhooks/{id}` | Fetch one webhook by id (secret redacted). | | `DELETE` | `/v1/webhooks/{id}` | Remove a webhook. | | `POST` | `/v1/webhooks/{id}/rotate-secret` | Generate a new secret. Old secret stays valid for 24h. | | `POST` | `/v1/webhooks/{id}/send-test` | Fire a synthetic delivery to the URL for integration testing. Add `?with_retries=true` to engage the retry ladder on 5xx for failure-handling validation. | ### Polling (alternative) Each daily publish lands a manifest file in the [flat files](/documentation#flat-files) area of the customer bucket; poll it programmatically between 05:00 and 11:00 UTC on the day after each session to detect delivery: ``` gs://prod-masscrest/_manifests/year=YYYY/date=YYYY-MM-DD/manifest.json ``` The manifest is written as the very last step of the daily publish, after every parquet and every downstream ingest is in place. Its existence is the delivery-complete signal; once visible it is safe to start reading immediately. Sample payload: ```json { "workflow_run_id": "aa1cb0c2-5b47-46f1-9c9a-8c9d1c2b0e33", "trading_date": "2026-07-31", "published_at": "2026-08-03T01:42:22Z", "files": [ { "path": "gs://prod-masscrest/option_flows/v0/01_day/year=2026/date=2026-07-31/000000000000.parquet", "byte_count": 24898886 }, { "path": "gs://prod-masscrest/underlying_option_flows/v0/01_day/year=2026/date=2026-07-31/000000000000.parquet", "byte_count": 1479423 }, { "path": "gs://prod-masscrest/underlying_flow_signals/v0/01_day/year=2026/date=2026-07-31/000000000000.parquet", "byte_count": 3512032 }, "…" ] } ``` ## Data corrections When we detect an error in already-published data, we notify the client's primary technical contact and communicate the fix within 48 hours. Corrections are written in place, keyed on the same row grain (`date`, `figi`, and `contract_id` where applicable). Re-sync the affected partition to pick up corrected values. ## Trial vs. paid tier Trial accounts share the paid-tier schemas and endpoints — nothing about the shape of the data changes — but usage is capped so a trial can validate integration and evaluate signal quality without pulling the full historical universe. Paid accounts have none of these limits. | Surface | Trial | Paid | |---|---|---| | **REST API — per-figi endpoints** (`/v0/option_flows/*`, `/v0/underlying_option_flows/01_day`, `/v0/underlying_flow_signals/01_day`) | **25 unique FIGIs / UTC day**, cumulative across every per-figi call. The 26th distinct FIGI in a UTC day returns HTTP 403 with a structured JSON body: `{"error": "trial_figi_cap_exceeded", "cap": 25, "resets_at": "", "touched": [{"figi": "...", "symbol": "..."}, ...], "requested_new": [{"figi": "...", "symbol": "..."}, ...]}`. Cap resets at 00:00 UTC. Must supply an explicit `figi` or `underlying_ticker` / `traded_underlying` filter — a broad `sector` / `industry`-only sweep returns HTTP 400. | Full universe, no per-figi cap. Broad `stock_mapping` filters (sector / industry / is_etf / is_adr) work as documented. | | **REST API — per-figi snapshots** (`/v0/underlying_option_flows/01_day/snapshot`, `/v0/underlying_flow_signals/01_day/snapshot`) | HTTP 403. A wide snapshot would charge every returned FIGI against the 25-figi cap in a single call, so snapshot endpoints are blocked outright on trial. Use the ranged per-figi endpoints with an explicit `figi` filter, or `/v0/grouped_flow_signal/01_day/snapshot` (not figi-capped) for broad cross-sectional exploration. | Full snapshot access; `stock_mapping` filters (sector / industry / is_etf / is_adr) work as documented. | | **Data grain** | Aggregate rollups only: per underlying × day, per (underlying, callput) × day, and grouped rollups (sector / industry / ETF / ADR / single-stock). Per-contract data is not available. | Same aggregate rollups plus per-contract data through the flat-files bucket. | | **REST API — intraday** (`/v0/*/10_min`, including snapshots) | HTTP 403. Daily grain only. | Full intraday access. | | **REST API — cohort rollups** (`/v0/grouped_flow_signal/01_day(+/snapshot)`) and metadata (`/v0/stock_mapping`) | Not figi-capped. Sector / industry / ETF / ADR / single-stock rollups and metadata browsing are free. | Same. | | **REST API — universe** | Full — any FIGI Masscrest tracks is queryable (subject to the 25-figi cap). | Full. | | **REST API — daily history depth** | 730 days. | Full history from 2019-11-10. | | **REST API — daily call cap** | 100 calls / UTC day (HTTP 429 on the 101st call). | 1,000 calls / UTC day (contact sales for higher). | | **MCP server** | All tools remain callable. API 403s / 400s (25-figi cap, intraday block, broad-filter block) surface as tool errors — the assistant sees a plain-language message and stops retrying. No MCP-side role gate. | All tools, no cap. | | **Flat files** | Requires the `programmatic` entitlement (opt-in per account; contact sales). Enabled trials get HMAC keys against **`gs://prod-masscrest-trial`** — an aggregates-only, daily-only, 180-day-lagged subset with fresh reference metadata. See the Flat files section for the exact bucket layout. Trials without `programmatic` see HTTP 403 on any flat-files call. | Full production bucket `gs://prod-masscrest` — contract-level and intraday parquets, no lag. | | **Webhooks** (`/v1/webhooks`) | Self-service registration with your API key, capped at **2 registered webhooks** per account. | Self-service registration with your API key, unlimited webhooks. | | **Portfolio saves** | Unlimited. | Unlimited. | ### Trial-to-paid upgrade On upgrade, the API-tier changes take effect immediately (up to a 5-minute per-instance principal-cache TTL). The Clerk webhook revokes the trial HMAC key at the moment of upgrade, so any local `~/.boto` or gsutil configuration pointing at `gs://prod-masscrest-trial` becomes invalid. Call `POST /v0/flat-files/credentials/rotate` (or the create endpoint) to receive new keys against the production bucket `gs://prod-masscrest` and re-point your pipeline. --- # option_flows_01_day Per-contract daily option-flow rows, split by participant class (institutional `_i`, retail `_r`, market-maker `_m`). One row per unique option contract that traded on the session, uniquely identified by `(date, traded_symbol, callput, strikeprice, expirationdate)`. - Grain: `(date, traded_symbol, callput, strikeprice, expirationdate)` - History: 2019-11-10 → - Timing: T+1 (published between 05:00 and 09:00 UTC on the calendar day after the trading date — Friday's data lands Saturday) `*_qty` fields are raw traded contract counts (integer). `*_premium` fields are dollar premium (`qty × traded price × adj_shares_deliverable`). `*_delta` / `*_gamma` / `*_vega` fields are the traded contracts as greek-weighted USD notional: dollar notional multiplied by the respective per-contract greek, then scaled by `adj_shares_deliverable`. Delta-weighted notionals are naturally signed by option type (calls +, puts −); `net_* = buy_* − sell_*` and inherits the sign. The `_i + _r + _m ≈ 0` accounting identity holds row-by-row on the notional columns (up to rounding). `floor_*` is exchange-floor / broker-committed volume, held in its own columns because Masscrest does not track these trades and their side (buy vs sell) is not available. Additional per-contract context columns carry the last-observed underlying spot, forward, IV and greeks for the session (useful as an end-of-day mark). > **Base table vs REST vs flat files.** This dictionary entry describes the **normalized base table** as it lands in flat files (`gs://prod-masscrest/option_flows/v0/01_day/…`). It carries `traded_symbol` (OCC OPRA root) as the underlying identifier and does **not** carry `figi`, `contract_id`, `traded_underlying`, `adj_shares_deliverable`, or `cumulative_split_factor` — those are sourced via a two-hop join through [`contract_id_mapping`](/documentation/dictionary/contract_id_mapping) and [`stock_mapping`](/documentation/dictionary/stock_mapping) (recipe below). The REST endpoint [`GET /v0/option_flows/01_day`](/documentation/option-flows-01-day) resolves the `figi` / `traded_underlying` filter server-side and adds those two columns to the response, so REST callers do not need to join. ## Fields | field_name | type | unit | grain | description | provenance | timing_lag | history_start | null_semantics | example | |---|---|---|---|---|---|---|---|---|---| | `date` | Date | trading day (UTC) | `(date, traded_symbol, callput, strikeprice, expirationdate)` | Session date. US equity trading calendar. | exchange | T+1 | 2019-11-10 | never null | `2026-07-24` | | `traded_symbol` | LowCardinality(String) | ticker | `(date, traded_symbol, callput, strikeprice, expirationdate)` | OCC OPRA root of the option as it traded on the session (no-dot form: `BRKB`, not `BRK.B`). Numerated forms (`WOLF1`, `XOM2`) appear when OCC recycles a root after a corporate action; both `WOLF` and `WOLF1` map to the same `traded_underlying=WOLF` via `contract_id_mapping`. Join key to [`contract_id_mapping`](/documentation/dictionary/contract_id_mapping) for `traded_underlying` / `contract_id` / `adj_shares_deliverable` / `figi`. | exchange | T+1 | 2019-11-10 | never null | `AAPL` | | `callput` | LowCardinality(String) | option type | `(date, traded_symbol, callput, strikeprice, expirationdate)` | `'C'` (call) or `'P'` (put). | exchange | T+1 | 2019-11-10 | never null | `C` | | `strikeprice` | Float64 | USD | `(date, traded_symbol, callput, strikeprice, expirationdate)` | Contract strike price, in the deliverable equity's price units. Not split-adjusted at this grain; join to `contract_id_mapping` for `cumulative_split_factor` if you need a current-share-equivalent strike. | exchange | T+1 | 2019-11-10 | never null | `215.00` | | `expirationdate` | Date | boundary | `(date, traded_symbol, callput, strikeprice, expirationdate)` | Contract expiration date (US equity calendar). | exchange | T+1 | 2019-11-10 | never null | `2026-08-15` | | `dte` | Int64 | days | `(date, traded_symbol, callput, strikeprice, expirationdate)` | Days to expiry: `expirationdate − date` (calendar days). `0` on the expiry-day trading session; can be negative on late prints of already-expired contracts (rare). | derived | T+1 | 2019-11-10 | never null | `22` | | `last_underprice` | Nullable(Float64) | USD | `(date, traded_symbol, callput, strikeprice, expirationdate)` | Underlying spot price observed at the last option trade of the session. Not split-adjusted (contemporaneous mark). | exchange | T+1 | 2019-11-10 | null when the contract has no trades in the session | `224.31` | | `last_fwd_underprice` | Nullable(Float64) | USD | `(date, traded_symbol, callput, strikeprice, expirationdate)` | Forward price on the underlying, adjusted for dividends and the risk-free rate. Not adjusted for splits. | derived | T+1 | 2019-11-10 | null when the contract has no trades in the session | `224.68` | | `last_price` | Nullable(Float64) | USD | `(date, traded_symbol, callput, strikeprice, expirationdate)` | Last traded option price on the session. Per-contract price (not scaled by `adj_shares_deliverable`). | exchange | T+1 | 2019-11-10 | null when the contract has no trades in the session | `9.42` | | `last_iv` | Nullable(Float64) | annualised vol (0.30 = 30%) | `(date, traded_symbol, callput, strikeprice, expirationdate)` | Implied volatility at the last trade of the session, fit against `last_fwd_underprice` and `last_price`. | derived | T+1 | 2019-11-10 | null when the contract has no trades / IV inversion fails | `0.2418` | | `last_delta` | Nullable(Float64) | delta per contract | `(date, traded_symbol, callput, strikeprice, expirationdate)` | Contract delta at the last trade of the session. Signed by option type (calls +, puts −). | derived | T+1 | 2019-11-10 | null when the contract has no trades in the session | `0.5620` | | `last_gamma` | Nullable(Float64) | gamma per contract | `(date, traded_symbol, callput, strikeprice, expirationdate)` | Contract gamma at the last trade of the session. | derived | T+1 | 2019-11-10 | null when the contract has no trades in the session | `0.0184` | | `last_vega` | Nullable(Float64) | vega per contract | `(date, traded_symbol, callput, strikeprice, expirationdate)` | Contract vega at the last trade of the session (dollar P&L per 1 vol point change). | derived | T+1 | 2019-11-10 | null when the contract has no trades in the session | `0.2712` | | `qty` | Int64 | contracts | `(date, traded_symbol, callput, strikeprice, expirationdate)` | Total unique traded contract volume for the session (each print contributes its quantity once; `m_qty` is not double-counted). | exchange | T+1 | 2019-11-10 | 0 on no-volume rows (present only when other fields exist) | `1_842` | | `buy_r_qty` / `sell_r_qty` / `net_r_qty` | Int64 | contracts | `(date, traded_symbol, callput, strikeprice, expirationdate)` | Retail buy / sell / net contract count. `net = buy − sell`. | model | T+1 | 2019-11-10 | 0 when no qualifying retail flow | `241` | | `buy_r_premium` / `sell_r_premium` / `net_r_premium` | Float64 | USD | `(date, traded_symbol, callput, strikeprice, expirationdate)` | Retail dollar premium paid on buys, received on sells, and their net. Signed on `net`. Scaled by `adj_shares_deliverable` (deliverable-adjusted). | model | T+1 | 2019-11-10 | 0.0 when no qualifying retail flow | `312_040.0` | | `buy_r_delta` / `sell_r_delta` / `net_r_delta` | Float64 | USD | `(date, traded_symbol, callput, strikeprice, expirationdate)` | Retail buy / sell / net flow as USD notional × delta (deliverable-adjusted). Naturally signed by option type (calls +, puts −); `net = buy − sell`. Positive `net` = net long-delta demand from retail on this contract. | model | T+1 | 2019-11-10 | 0.0 when no qualifying retail flow | `-38_720.55` | | `buy_r_gamma` / `sell_r_gamma` / `net_r_gamma` | Float64 | USD | `(date, traded_symbol, callput, strikeprice, expirationdate)` | Retail buy / sell / net flow as USD-notional-converted gamma (dollar notional multiplied by the per-contract gamma, deliverable-adjusted). | model | T+1 | 2019-11-10 | 0.0 when no qualifying retail flow | `12_450.30` | | `buy_r_vega` / `sell_r_vega` / `net_r_vega` | Float64 | USD | `(date, traded_symbol, callput, strikeprice, expirationdate)` | Retail buy / sell / net flow as USD-notional-converted vega (deliverable-adjusted). | model | T+1 | 2019-11-10 | 0.0 when no qualifying retail flow | `88_012.40` | | `buy_i_qty` / `sell_i_qty` / `net_i_qty` | Int64 | contracts | `(date, traded_symbol, callput, strikeprice, expirationdate)` | Institutional buy / sell / net contract count. Same convention as retail. | model | T+1 | 2019-11-10 | 0 when no qualifying institutional flow | `987` | | `buy_i_premium` / `sell_i_premium` / `net_i_premium` | Float64 | USD | `(date, traded_symbol, callput, strikeprice, expirationdate)` | Institutional dollar premium buy / sell / net (deliverable-adjusted). | model | T+1 | 2019-11-10 | 0.0 when no qualifying institutional flow | `1_120_338.55` | | `buy_i_delta` / `sell_i_delta` / `net_i_delta` | Float64 | USD | `(date, traded_symbol, callput, strikeprice, expirationdate)` | Institutional buy / sell / net flow as USD notional × delta (deliverable-adjusted). Naturally signed; positive `net` = net long-delta demand from the institutional class on this contract. | model | T+1 | 2019-11-10 | 0.0 when no qualifying institutional flow | `892_310.00` | | `buy_i_gamma` / `sell_i_gamma` / `net_i_gamma` | Float64 | USD | `(date, traded_symbol, callput, strikeprice, expirationdate)` | Institutional buy / sell / net flow as USD-notional-converted gamma (deliverable-adjusted). | model | T+1 | 2019-11-10 | 0.0 when no qualifying institutional flow | `41_882.70` | | `buy_i_vega` / `sell_i_vega` / `net_i_vega` | Float64 | USD | `(date, traded_symbol, callput, strikeprice, expirationdate)` | Institutional buy / sell / net flow as USD-notional-converted vega (deliverable-adjusted). | model | T+1 | 2019-11-10 | 0.0 when no qualifying institutional flow | `312_048.80` | | `buy_m_qty` / `sell_m_qty` / `net_m_qty` | Int64 | contracts | `(date, traded_symbol, callput, strikeprice, expirationdate)` | Market-maker buy / sell / net contract count. Includes the residual absorption of customer (retail + institutional) net imbalance on the opposite side, so the row-level accounting identity `_i + _r + _m ≈ 0` holds by construction on the notionals. | model | T+1 | 2019-11-10 | 0 when no qualifying market-maker flow | `-1_098` | | `buy_m_premium` / `sell_m_premium` / `net_m_premium` | Float64 | USD | `(date, traded_symbol, callput, strikeprice, expirationdate)` | Market-maker dollar premium buy / sell / net (deliverable-adjusted). Mirror of `_i + _r` on the same row up to floor / rounding. | model | T+1 | 2019-11-10 | 0.0 when no qualifying market-maker flow | `-1_432_378.65` | | `buy_m_delta` / `sell_m_delta` / `net_m_delta` | Float64 | USD | `(date, traded_symbol, callput, strikeprice, expirationdate)` | Market-maker buy / sell / net flow as USD notional × delta (deliverable-adjusted). Mirror of `_i + _r` on the same row. | model | T+1 | 2019-11-10 | 0.0 when no qualifying market-maker flow | `-853_590.00` | | `buy_m_gamma` / `sell_m_gamma` / `net_m_gamma` | Float64 | USD | `(date, traded_symbol, callput, strikeprice, expirationdate)` | Market-maker buy / sell / net flow as USD-notional-converted gamma (deliverable-adjusted). | model | T+1 | 2019-11-10 | 0.0 when no qualifying market-maker flow | `-54_333.00` | | `buy_m_vega` / `sell_m_vega` / `net_m_vega` | Float64 | USD | `(date, traded_symbol, callput, strikeprice, expirationdate)` | Market-maker buy / sell / net flow as USD-notional-converted vega (deliverable-adjusted). | model | T+1 | 2019-11-10 | 0.0 when no qualifying market-maker flow | `-400_172.00` | | `floor_qty` | Int64 | contracts | `(date, traded_symbol, callput, strikeprice, expirationdate)` | Total floor-executed contract count (direct-negotiation outcry trades on the exchange floor). No directional classification provided; reported for volume completeness. | exchange | T+1 | 2019-11-10 | 0 when no floor prints | `24` | | `floor_premium` | Float64 | USD | `(date, traded_symbol, callput, strikeprice, expirationdate)` | Total floor-executed dollar premium (deliverable-adjusted). No directional classification provided; reported for volume completeness. | exchange | T+1 | 2019-11-10 | 0.0 when no floor prints | `24_120.0` | | `floor_delta` | Float64 | USD | `(date, traded_symbol, callput, strikeprice, expirationdate)` | Total floor-executed USD notional × delta (deliverable-adjusted). No directional classification provided; reported for volume completeness. Naturally signed by option type. | exchange | T+1 | 2019-11-10 | 0.0 when no floor prints | `13_492.20` | | `floor_gamma` | Float64 | USD | `(date, traded_symbol, callput, strikeprice, expirationdate)` | Total floor-executed USD-notional-converted gamma (deliverable-adjusted). No directional classification provided; reported for volume completeness. | exchange | T+1 | 2019-11-10 | 0.0 when no floor prints | `1_842.10` | | `floor_vega` | Float64 | USD | `(date, traded_symbol, callput, strikeprice, expirationdate)` | Total floor-executed USD-notional-converted vega (deliverable-adjusted). No directional classification provided; reported for volume completeness. | exchange | T+1 | 2019-11-10 | 0.0 when no floor prints | `9_820.60` | ## Reconstructing figi / adj_shares_deliverable The base table drops mapping-derived columns to keep the parquet footprint small and the schema stable through corporate-action lineage changes. Reconstruct `traded_underlying`, `contract_id`, `adj_shares_deliverable`, `cumulative_split_factor`, and `figi` at query time with a two-hop join through [`contract_id_mapping`](/documentation/dictionary/contract_id_mapping) and [`stock_mapping`](/documentation/dictionary/stock_mapping). Both mapping tables are point-in-time, so the `phase_start` / `phase_end` predicates against `f.date` return the row that was in effect on the trading day. ```sql SELECT f.*, m.traded_underlying, m.contract_id, m.adj_shares_deliverable, m.cumulative_split_factor, s.figi FROM option_flows_01_day AS f INNER JOIN contract_id_mapping AS m ON m.traded_symbol = f.traded_symbol AND m.expirationdate = f.expirationdate AND m.callput = f.callput AND m.traded_strikeprice = f.strikeprice AND m.phase_start <= f.date AND m.phase_end >= f.date LEFT JOIN stock_mapping AS s ON s.traded_underlying = m.traded_underlying AND f.date >= s.phase_start AND f.date <= s.phase_end ``` `contract_id_mapping` resolves the OCC-root symbol (e.g. `WOLF1` after a corporate-action epoch) to the stable `traded_underlying` (e.g. `WOLF`), the Masscrest-built `contract_id` (tracks economic identity across the whole corporate-action lineage), the per-contract `adj_shares_deliverable`, and the `cumulative_split_factor`. `stock_mapping` then resolves `traded_underlying` to the OpenFIGI composite `figi` for the underlying at the point-in-time phase. --- ### Join hints For per-underlying daily aggregates (rolled up across every contract on the same `(date, figi)`), use [`underlying_option_flows_01_day`](/documentation/dictionary/underlying_option_flows_01_day) — that surface pre-aggregates and carries `figi` as a real column, so no mapping join is needed. For underlying prices and turnover, join to [`px_01_day`](/documentation/dictionary/px_01_day) on `(date, figi)` after the two-hop reconstruction above (or use `px_01_day_adj` for split-adjusted OHLCV). ## Gross-volume accounting Each trade has a customer side (`buy_{r,i}_qty` / `sell_{r,i}_qty`) and a market-maker counterparty (`buy_m_qty` / `sell_m_qty`, rebuilt to absorb the customer imbalance so the row-level `_i + _r + _m ≈ 0` identity holds on notionals). **DO**: aggregate on `qty` — one entry per trade. **DON'T**: sum the cohort legs (`buy_r_qty + sell_r_qty + buy_i_qty + sell_i_qty + buy_m_qty + sell_m_qty + floor_qty`) — inflates volume by ~30-35% because the market-maker leg gets added on top of the customer legs. ## REST endpoint: bucketed axes The REST endpoint `GET /v0/option_flows/01_day` returns an underlying-level rollup (one row per `(date, figi)` after any optional per-contract filters). The router accepts `figi` / `traded_underlying` filters and projects `figi` + `traded_underlying` onto every response row (resolved server-side), so the REST response shape is fewer columns than the base table but includes the identifiers you would otherwise need the two-hop join for. Three optional booleans add fixed-cardinality axes to that GROUP BY without pulling raw contract rows — useful for asking "where did the flow land on the surface?" without paying for the full contract feed. Full parameter reference in the [endpoint docs](/documentation/option-flows-01-day); the anchors here are the axis definitions and how they compose. | Param | Default | Bucket labels | Rows per `(date, figi)` | |---|---|---|---| | `by_callput` | `false` | `"C"`, `"P"` | 2 | | `by_expiry_bucket` | `false` | `"0-7d"`, `"8-30d"`, `"31-90d"`, `"91+d"` | 4 | | `by_moneyness_bucket` | `false` | `"0-0.2"`, `"0.2-0.4"`, `"0.4-0.6"`, `"0.6-0.8"`, `"0.8-1.0"` | 5 | - DTE per source contract is `expirationdate - date` (calendar days). Rows are returned in semantic DTE order (short-dated first), not lexicographic. - Moneyness is the absolute delta on the source contract row (same value the existing `abs_delta_min` / `abs_delta_max` filters key on), so a 20-delta OTM call and a 20-delta OTM put both land in `"0-0.2"`. - Axes compose. Passing all three yields at most `2 × 4 × 5 = 40` rows per `(date, figi)`. The bucket column is only present on the response row when the corresponding axis is on. - Bucket axes are additive to the pre-aggregation filters (`callput` / `dte_min` / `dte_max` / `abs_delta_min` / `abs_delta_max`), so e.g. `callput=C&by_expiry_bucket=true` returns 4 rows per `(date, figi)` (all calls, split across the 4 DTE buckets). ### Response-header signal The response carries `X-Bucket-Axes: ` listing the axes that ended up in the GROUP BY (in add order, subset of `callput,expiry_bucket,moneyness_bucket`). Absent when no bucket axis is on. ### Row-count cap and the parquet escape hatch Projected row count is deterministic from the request: `days × 2^by_callput × 4^by_expiry_bucket × 5^by_moneyness_bucket`. If the projection exceeds **25,000** rows **and** `format=json`, the endpoint returns HTTP 400 with a structured body: ```json { "error": "row_cap_exceeded", "projected_rows": 47000, "threshold": 25000, "hint": "Add format=parquet" } ``` Set `format=parquet` to bypass — Parquet is ~5-10× smaller on the wire and streams straight from the router. On the daily variant the cap only bites on multi-year full-cube pulls (40 rows × 252 trading days ≈ 10k rows). ### Code sample — CASY, full (callput × expiry × moneyness) cube over 2 sessions ```bash curl -H "X-API-Key: $MASSCREST_API_KEY" \ "https://api.masscrest.com/v0/option_flows/01_day?traded_underlying=CASY&by_callput=true&by_expiry_bucket=true&by_moneyness_bucket=true&start_date=2026-09-04&end_date=2026-09-05" ``` ```python import requests response = requests.get( "https://api.masscrest.com/v0/option_flows/01_day", headers={"X-API-Key": MASSCREST_API_KEY}, params={ "traded_underlying": "CASY", "by_callput": True, "by_expiry_bucket": True, "by_moneyness_bucket": True, "start_date": "2026-09-04", "end_date": "2026-09-05", }, ) response.raise_for_status() rows = response.json() # response.headers["X-Bucket-Axes"] == "callput,expiry_bucket,moneyness_bucket" # response.headers["X-Row-Count"] == up to "80" (2 days x 40 buckets) for row in rows: print( row["date"], row["callput"], row["expiry_bucket"], row["moneyness_bucket"], "net_i_delta=", row["net_i_delta"], "net_r_delta=", row["net_r_delta"], ) ``` Sample row: ```json { "date": "2026-09-05", "figi": "BBG000BJK6K5", "traded_underlying": "CASY", "callput": "C", "expiry_bucket": "0-7d", "moneyness_bucket": "0.4-0.6", "net_r_delta": 42800, "net_i_delta": 812600, "net_m_delta": -855400, "adj_close": 412.18 } ``` Every row also carries the full 45-column flow-aggregate set (`buy_r_premium` / `net_i_vega` / ...) and the joined split-adjusted price columns (`adj_open` / `adj_close` / `adj_volume` / `total_turnover`) — omitted from the sample row for brevity. Backwards-compatible: with no `by_*` params, the response shape is byte-identical to the pre-bucket behaviour. --- # option_flows_10_min 10-minute-bucketed sibling of `option_flows_01_day`. Per-contract option-flow rows at a 10-minute cadence: one row per unique option contract that traded within the bucket, keyed on `(date, ten_min_timeframe, traded_symbol, callput, strikeprice, expirationdate)`. - Grain: `(date, ten_min_timeframe, traded_symbol, callput, strikeprice, expirationdate)` - History: 2019-11-10 → - Timing: T+1 (published between 05:00 and 09:00 UTC on the calendar day after the trading date — Friday's data lands Saturday) Request window is capped at 30 days per call on the REST endpoint. Omitted dates default to the most recent 30. For multi-year intraday history use flat files at `gs://prod-masscrest/option_flows/v0/10_min/`; the REST cap keeps response sizes bounded. > **Base table vs REST vs flat files.** This dictionary entry describes the **normalized base table** as it lands in flat files (`gs://prod-masscrest/option_flows/v0/10_min/…`). It carries `traded_symbol` (OCC OPRA root) as the underlying identifier and does **not** carry `figi`, `contract_id`, `traded_underlying`, `adj_shares_deliverable`, or `cumulative_split_factor` — those are sourced via a two-hop join through [`contract_id_mapping`](/documentation/dictionary/contract_id_mapping) and [`stock_mapping`](/documentation/dictionary/stock_mapping) (recipe below). The REST endpoint [`GET /v0/option_flows/10_min`](/documentation/option-flows-10-min) resolves the `figi` / `traded_underlying` filter server-side and adds those two columns to the response, so REST callers do not need to join. ### Bucket boundary convention `ten_min_timeframe` is the end of the 10-minute bucket, in New York wall-clock time stored as a naive `DateTime`. Bucket covers `(ten_min_timeframe − 10 min, ten_min_timeframe]`. First RTH bucket ends at `09:40:00`, last at `16:00:00` (39 buckets per full RTH session). Join to `px_10_min` on `(date, ten_min_timeframe, figi)` after the two-hop reconstruction below; both sides use the same NY-wall-clock convention. Field semantics match `option_flows_01_day` exactly: the same buy / sell / net triples across the participant classes, greek-weighted USD notionals, the accounting identity `_i + _r + _m ≈ 0` (row-by-row), and the same `last_*` end-of-bucket context columns. The only structural difference is the added `ten_min_timeframe` dimension. ## Fields | field_name | type | unit | grain | description | provenance | timing_lag | history_start | null_semantics | example | |---|---|---|---|---|---|---|---|---|---| | `date` | Date | trading day (UTC) | `(date, ten_min_timeframe, traded_symbol, callput, strikeprice, expirationdate)` | Session date. US equity trading calendar. | exchange | T+1 | 2019-11-10 | never null | `2026-07-24` | | `ten_min_timeframe` | DateTime | NY wall-clock, bucket close | `(date, ten_min_timeframe, traded_symbol, callput, strikeprice, expirationdate)` | End timestamp of the 10-min bucket (naive NY-time). Bucket covers `(ten_min_timeframe − 10 min, ten_min_timeframe]`. | exchange | T+1 | 2019-11-10 | never null | `2026-07-24 09:40:00` | | `traded_symbol` | LowCardinality(String) | ticker | `(date, ten_min_timeframe, traded_symbol, callput, strikeprice, expirationdate)` | OCC OPRA root of the option as it traded in the bucket (no-dot form: `BRKB`, not `BRK.B`). Numerated forms (`WOLF1`, `XOM2`) appear when OCC recycles a root after a corporate action; both `WOLF` and `WOLF1` map to the same `traded_underlying=WOLF` via `contract_id_mapping`. Join key to [`contract_id_mapping`](/documentation/dictionary/contract_id_mapping) for `traded_underlying` / `contract_id` / `adj_shares_deliverable` / `figi`. | exchange | T+1 | 2019-11-10 | never null | `AAPL` | | `callput` | LowCardinality(String) | option type | `(date, ten_min_timeframe, traded_symbol, callput, strikeprice, expirationdate)` | `'C'` (call) or `'P'` (put). | exchange | T+1 | 2019-11-10 | never null | `C` | | `strikeprice` | Float64 | USD | `(date, ten_min_timeframe, traded_symbol, callput, strikeprice, expirationdate)` | Contract strike price, in the deliverable equity's price units. Not split-adjusted at this grain; join to `contract_id_mapping` for `cumulative_split_factor` if you need a current-share-equivalent strike. | exchange | T+1 | 2019-11-10 | never null | `215.00` | | `expirationdate` | Date | boundary | `(date, ten_min_timeframe, traded_symbol, callput, strikeprice, expirationdate)` | Contract expiration date (US equity calendar). | exchange | T+1 | 2019-11-10 | never null | `2026-08-15` | | `dte` | Int64 | days | `(date, ten_min_timeframe, traded_symbol, callput, strikeprice, expirationdate)` | Days to expiry: `expirationdate − date` (calendar days). `0` on the expiry-day trading session. | derived | T+1 | 2019-11-10 | never null | `22` | | `last_underprice` | Nullable(Float64) | USD | `(date, ten_min_timeframe, traded_symbol, callput, strikeprice, expirationdate)` | Underlying spot at the last option trade within the bucket. Not split-adjusted. | exchange | T+1 | 2019-11-10 | null when the contract has no trades in the bucket | `224.10` | | `last_fwd_underprice` | Nullable(Float64) | USD | `(date, ten_min_timeframe, traded_symbol, callput, strikeprice, expirationdate)` | Forward price on the underlying, adjusted for dividends and the risk-free rate. Not adjusted for splits. | derived | T+1 | 2019-11-10 | null when the contract has no trades in the bucket | `224.48` | | `last_price` | Nullable(Float64) | USD | `(date, ten_min_timeframe, traded_symbol, callput, strikeprice, expirationdate)` | Last traded option price in the bucket. Per-contract price (not scaled by `adj_shares_deliverable`). | exchange | T+1 | 2019-11-10 | null when the contract has no trades in the bucket | `9.38` | | `last_iv` | Nullable(Float64) | annualised vol (0.30 = 30%) | `(date, ten_min_timeframe, traded_symbol, callput, strikeprice, expirationdate)` | Implied volatility at the last trade of the bucket. | derived | T+1 | 2019-11-10 | null when the contract has no trades / IV inversion fails | `0.2418` | | `last_delta` | Nullable(Float64) | delta per contract | `(date, ten_min_timeframe, traded_symbol, callput, strikeprice, expirationdate)` | Contract delta at the last trade of the bucket. Signed by option type. | derived | T+1 | 2019-11-10 | null when the contract has no trades in the bucket | `0.5620` | | `last_gamma` | Nullable(Float64) | gamma per contract | `(date, ten_min_timeframe, traded_symbol, callput, strikeprice, expirationdate)` | Contract gamma at the last trade of the bucket. | derived | T+1 | 2019-11-10 | null when the contract has no trades in the bucket | `0.0184` | | `last_vega` | Nullable(Float64) | vega per contract | `(date, ten_min_timeframe, traded_symbol, callput, strikeprice, expirationdate)` | Contract vega at the last trade of the bucket. | derived | T+1 | 2019-11-10 | null when the contract has no trades in the bucket | `0.2712` | | `qty` | Int64 | contracts | `(date, ten_min_timeframe, traded_symbol, callput, strikeprice, expirationdate)` | Total unique traded contract volume in the bucket. | exchange | T+1 | 2019-11-10 | 0 on no-volume rows | `184` | | `buy_r_qty` / `sell_r_qty` / `net_r_qty` | Int64 | contracts | `(date, ten_min_timeframe, traded_symbol, callput, strikeprice, expirationdate)` | Retail buy / sell / net contract count within the bucket. | model | T+1 | 2019-11-10 | 0 when no qualifying retail flow | `24` | | `buy_r_premium` / `sell_r_premium` / `net_r_premium` | Float64 | USD | `(date, ten_min_timeframe, traded_symbol, callput, strikeprice, expirationdate)` | Retail dollar premium buy / sell / net for the bucket (deliverable-adjusted). | model | T+1 | 2019-11-10 | 0.0 when no qualifying retail flow | `31_204.0` | | `buy_r_delta` / `sell_r_delta` / `net_r_delta` | Float64 | USD | `(date, ten_min_timeframe, traded_symbol, callput, strikeprice, expirationdate)` | Retail buy / sell / net flow as USD notional × delta (deliverable-adjusted). Naturally signed by option type; positive `net` = net long-delta demand from retail on this contract in the bucket. | model | T+1 | 2019-11-10 | 0.0 when no qualifying retail flow | `-3_872.55` | | `buy_r_gamma` / `sell_r_gamma` / `net_r_gamma` | Float64 | USD | `(date, ten_min_timeframe, traded_symbol, callput, strikeprice, expirationdate)` | Retail buy / sell / net flow as USD-notional-converted gamma (deliverable-adjusted). | model | T+1 | 2019-11-10 | 0.0 when no qualifying retail flow | `1_245.30` | | `buy_r_vega` / `sell_r_vega` / `net_r_vega` | Float64 | USD | `(date, ten_min_timeframe, traded_symbol, callput, strikeprice, expirationdate)` | Retail buy / sell / net flow as USD-notional-converted vega (deliverable-adjusted). | model | T+1 | 2019-11-10 | 0.0 when no qualifying retail flow | `8_801.40` | | `buy_i_qty` / `sell_i_qty` / `net_i_qty` | Int64 | contracts | `(date, ten_min_timeframe, traded_symbol, callput, strikeprice, expirationdate)` | Institutional buy / sell / net contract count for the bucket. | model | T+1 | 2019-11-10 | 0 when no qualifying institutional flow | `98` | | `buy_i_premium` / `sell_i_premium` / `net_i_premium` | Float64 | USD | `(date, ten_min_timeframe, traded_symbol, callput, strikeprice, expirationdate)` | Institutional dollar premium buy / sell / net (deliverable-adjusted). | model | T+1 | 2019-11-10 | 0.0 when no qualifying institutional flow | `112_034.55` | | `buy_i_delta` / `sell_i_delta` / `net_i_delta` | Float64 | USD | `(date, ten_min_timeframe, traded_symbol, callput, strikeprice, expirationdate)` | Institutional buy / sell / net flow as USD notional × delta (deliverable-adjusted). Positive `net` = net long-delta demand from the institutional class on this contract in the bucket. | model | T+1 | 2019-11-10 | 0.0 when no qualifying institutional flow | `89_231.00` | | `buy_i_gamma` / `sell_i_gamma` / `net_i_gamma` | Float64 | USD | `(date, ten_min_timeframe, traded_symbol, callput, strikeprice, expirationdate)` | Institutional buy / sell / net flow as USD-notional-converted gamma (deliverable-adjusted). | model | T+1 | 2019-11-10 | 0.0 when no qualifying institutional flow | `4_188.70` | | `buy_i_vega` / `sell_i_vega` / `net_i_vega` | Float64 | USD | `(date, ten_min_timeframe, traded_symbol, callput, strikeprice, expirationdate)` | Institutional buy / sell / net flow as USD-notional-converted vega (deliverable-adjusted). | model | T+1 | 2019-11-10 | 0.0 when no qualifying institutional flow | `31_204.80` | | `buy_m_qty` / `sell_m_qty` / `net_m_qty` | Int64 | contracts | `(date, ten_min_timeframe, traded_symbol, callput, strikeprice, expirationdate)` | Market-maker buy / sell / net contract count for the bucket. Includes the residual absorption of customer (retail + institutional) net imbalance on the opposite side. | model | T+1 | 2019-11-10 | 0 when no qualifying market-maker flow | `-108` | | `buy_m_premium` / `sell_m_premium` / `net_m_premium` | Float64 | USD | `(date, ten_min_timeframe, traded_symbol, callput, strikeprice, expirationdate)` | Market-maker dollar premium buy / sell / net (deliverable-adjusted). Mirror of `_i + _r` on the same row up to floor / rounding. | model | T+1 | 2019-11-10 | 0.0 when no qualifying market-maker flow | `-143_237.65` | | `buy_m_delta` / `sell_m_delta` / `net_m_delta` | Float64 | USD | `(date, ten_min_timeframe, traded_symbol, callput, strikeprice, expirationdate)` | Market-maker buy / sell / net flow as USD notional × delta (deliverable-adjusted). Mirror of `_i + _r` on the same row. | model | T+1 | 2019-11-10 | 0.0 when no qualifying market-maker flow | `-85_359.00` | | `buy_m_gamma` / `sell_m_gamma` / `net_m_gamma` | Float64 | USD | `(date, ten_min_timeframe, traded_symbol, callput, strikeprice, expirationdate)` | Market-maker buy / sell / net flow as USD-notional-converted gamma (deliverable-adjusted). | model | T+1 | 2019-11-10 | 0.0 when no qualifying market-maker flow | `-5_433.30` | | `buy_m_vega` / `sell_m_vega` / `net_m_vega` | Float64 | USD | `(date, ten_min_timeframe, traded_symbol, callput, strikeprice, expirationdate)` | Market-maker buy / sell / net flow as USD-notional-converted vega (deliverable-adjusted). | model | T+1 | 2019-11-10 | 0.0 when no qualifying market-maker flow | `-40_017.20` | | `floor_qty` | Int64 | contracts | `(date, ten_min_timeframe, traded_symbol, callput, strikeprice, expirationdate)` | Total floor-executed contract count within the bucket (direct-negotiation outcry trades on the exchange floor). No directional classification provided; reported for volume completeness. | exchange | T+1 | 2019-11-10 | 0 when no floor prints | `4` | | `floor_premium` | Float64 | USD | `(date, ten_min_timeframe, traded_symbol, callput, strikeprice, expirationdate)` | Total floor-executed dollar premium within the bucket (deliverable-adjusted). No directional classification provided; reported for volume completeness. | exchange | T+1 | 2019-11-10 | 0.0 when no floor prints | `4_120.0` | | `floor_delta` | Float64 | USD | `(date, ten_min_timeframe, traded_symbol, callput, strikeprice, expirationdate)` | Total floor-executed USD notional × delta within the bucket (deliverable-adjusted). No directional classification provided; reported for volume completeness. Naturally signed. | exchange | T+1 | 2019-11-10 | 0.0 when no floor prints | `2_349.20` | | `floor_gamma` | Float64 | USD | `(date, ten_min_timeframe, traded_symbol, callput, strikeprice, expirationdate)` | Total floor-executed USD-notional-converted gamma within the bucket (deliverable-adjusted). No directional classification provided; reported for volume completeness. | exchange | T+1 | 2019-11-10 | 0.0 when no floor prints | `184.10` | | `floor_vega` | Float64 | USD | `(date, ten_min_timeframe, traded_symbol, callput, strikeprice, expirationdate)` | Total floor-executed USD-notional-converted vega within the bucket (deliverable-adjusted). No directional classification provided; reported for volume completeness. | exchange | T+1 | 2019-11-10 | 0.0 when no floor prints | `982.60` | ## Reconstructing figi / adj_shares_deliverable The base table drops mapping-derived columns to keep the parquet footprint small and the schema stable through corporate-action lineage changes. Reconstruct `traded_underlying`, `contract_id`, `adj_shares_deliverable`, `cumulative_split_factor`, and `figi` at query time with a two-hop join through [`contract_id_mapping`](/documentation/dictionary/contract_id_mapping) and [`stock_mapping`](/documentation/dictionary/stock_mapping). Both mapping tables are point-in-time, so the `phase_start` / `phase_end` predicates against `f.date` return the row that was in effect on the trading day. The mapping join keys off `(traded_symbol, expirationdate, callput, strikeprice)` — the same contract identity across every 10-minute bucket of the session, so the join is one-to-many at the bucket grain. ```sql SELECT f.*, m.traded_underlying, m.contract_id, m.adj_shares_deliverable, m.cumulative_split_factor, s.figi FROM option_flows_10_min AS f INNER JOIN contract_id_mapping AS m ON m.traded_symbol = f.traded_symbol AND m.expirationdate = f.expirationdate AND m.callput = f.callput AND m.traded_strikeprice = f.strikeprice AND m.phase_start <= f.date AND m.phase_end >= f.date LEFT JOIN stock_mapping AS s ON s.traded_underlying = m.traded_underlying AND f.date >= s.phase_start AND f.date <= s.phase_end ``` `contract_id_mapping` resolves the OCC-root symbol (e.g. `WOLF1` after a corporate-action epoch) to the stable `traded_underlying` (e.g. `WOLF`), the Masscrest-built `contract_id` (tracks economic identity across the whole corporate-action lineage), the per-contract `adj_shares_deliverable`, and the `cumulative_split_factor`. `stock_mapping` then resolves `traded_underlying` to the OpenFIGI composite `figi` for the underlying at the point-in-time phase. --- ### Join hints For per-underlying 10-min aggregates (rolled up across every contract on the same `(date, ten_min_timeframe, figi)`), use [`underlying_option_flows_10_min`](/documentation/dictionary/underlying_option_flows_10_min) — that surface pre-aggregates and carries `figi` as a real column, so no mapping join is needed. For underlying prices and turnover, join to [`px_10_min`](/documentation/dictionary/px_10_min) on `(date, ten_min_timeframe, figi)` after the two-hop reconstruction above. For daily rollups of the same contracts, sum across `ten_min_timeframe` or use `option_flows_01_day` directly. ## Gross-volume accounting Each trade has a customer side (`buy_{r,i}_qty` / `sell_{r,i}_qty`) and a market-maker counterparty (`buy_m_qty` / `sell_m_qty`, rebuilt to absorb the customer imbalance so the row-level `_i + _r + _m ≈ 0` identity holds on notionals). **DO**: aggregate on `qty` — one entry per trade per bucket. **DON'T**: sum the cohort legs (`buy_r_qty + sell_r_qty + buy_i_qty + sell_i_qty + buy_m_qty + sell_m_qty + floor_qty`) — inflates per bucket volume by ~30-35% because the market-maker leg gets added on top of the customer legs. --- # underlying_option_flows_01_day Per-underlying × callput daily aggregation. Same participant-split option-flow columns as `option_flows_01_day`, rolled up across every contract on the same `(date, figi, callput)` grain rather than kept at contract resolution. Up to three rows per underlying per session: `callput = 'C'` (call side), `callput = 'P'` (put side), and `callput = 'CP'` (pre-computed call-vs-put row — see "The CP row" section below). Adds context columns (contract count, aggregate share count, session-end underlying mark, 30-day ATM IV). - Grain: `(date, figi, callput)` - History: 2019-11-10 → - Timing: T+1 (published between 05:00 and 09:00 UTC on the calendar day after the trading date; Friday's data lands Saturday) Underlying price, turnover, and metadata are not baked into this table. Join `px_01_day` (adjust locally with `split_factors`) and `stock_mapping` on `traded_underlying`. ### Pre-aggregated units — no client-side conversion Every `_shares` / `_premium` / `_delta` / `_gamma` / `_vega` column on this surface is **already multiplied by `adj_shares_deliverable`** (standard 100 for pre-corporate-action contracts) at the pipeline stage: - `_shares` = `Σ (qty × adj_shares_deliverable)` — underlying-share equivalents (not contracts). - `_premium` = `Σ (qty × traded_price × adj_shares_deliverable)` — USD notional. - `_delta` / `_gamma` / `_vega` = `Σ (qty × traded_price × per-contract greek × adj_shares_deliverable)` — USD notional × greek. Consume the values directly. This differs from the per-contract base table `option_flows_01_day`, where the same fields ship raw (pre-multiplier) and require a `contract_id_mapping` join for the deliverable. ### Difference vs `option_flows_01_day` `option_flows_01_day` is per-contract, one row per unique traded contract (with `_qty` in contracts and `_premium` / greek columns in raw base-contract units before the deliverable multiplier). `underlying_option_flows_01_day` collapses every contract on the same `(date, figi, callput)` into a single row, applies the deliverable multiplier at ingest, and keeps the call vs put dimension. No contract-level filters (`dte` / `abs_delta`) on this table. ## Fields | field_name | type | unit | grain | description | provenance | timing_lag | history_start | null_semantics | example | |---|---|---|---|---|---|---|---|---|---| | `date` | Date | trading day (UTC) | `(date, figi, callput)` | Session date. US equity trading calendar. | exchange | T+1 | 2019-11-10 | never null | `2026-07-24` | | `figi` | String(12) | identifier | `(date, figi, callput)` | Composite FIGI for the underlying. Join key to `stock_mapping`, `px_01_day`, `split_factors`. | exchange | n/a | 2019-11-10 | never null | `BBG000MM2P62` | | `traded_underlying` | String | ticker | `(date, figi, callput)` | Listed equity ticker on `date`. Dotted dual-class form (`BRK.B`). | exchange | T+1 | 2019-11-10 | never null | `AAPL` | | `callput` | String(2) | option type | `(date, figi, callput)` | `'C'` (call side), `'P'` (put side), or `'CP'` (call-vs-put differential row). | exchange | T+1 | 2019-11-10 | never null | `C` | | `n_contracts` | UInt32 | contracts | `(date, figi, callput)` | Distinct option contracts that traded on this underlying × callput on `date`. | exchange | T+1 | 2019-11-10 | 0 on no-volume days | `184` | | `total_shares` | Float64 | underlying-share equivalents | `(date, figi, callput)` | Sum of all traded contract volume converted to underlying-share equivalents (`Σ qty × adj_shares_deliverable`), regardless of side. | exchange | T+1 | 2019-11-10 | 0.0 on no-volume days | `2_384_100.0` | | `last_underprice` | Float64 | USD | `(date, figi, callput)` | Underlying spot price observed against the last option trade of the session. Not split-adjusted (contemporaneous mark). | exchange | T+1 | 2019-11-10 | null on no-volume days | `224.31` | | `atm_iv_30d` | Float64 | annualised vol (0.30 = 30%) | `(date, figi, callput)` | Best-effort estimate of ATM 30-day implied volatility for the underlying on this session. Not derived from a fitted volatility surface. For high-accuracy implied-volatility estimation, particularly on less liquid contracts, we recommend using specialist third-party providers. | derived | T+1 | 2019-11-10 | may be null when the figi had no option activity on the session | `0.2418` | | `buy_r_shares` / `sell_r_shares` / `net_r_shares` | Float64 | underlying-share equivalents | `(date, figi, callput)` | Retail buy / sell / net share equivalents for the (underlying, callput) row. | model | T+1 | 2019-11-10 | 0.0 when no qualifying volume | `24_100.0` | | `buy_r_premium` / `sell_r_premium` / `net_r_premium` | Float64 | USD | `(date, figi, callput)` | Retail dollar premium buy / sell / net. | model | T+1 | 2019-11-10 | 0.0 when no qualifying volume | `312_040.0` | | `buy_r_delta` / `sell_r_delta` / `net_r_delta` | Float64 | USD | `(date, figi, callput)` | Retail buy / sell / net flow as USD notional × delta for the (underlying, callput) row. Naturally signed (calls +, puts −); positive `net` = net long-delta demand from retail on this call/put side. | model | T+1 | 2019-11-10 | 0.0 when no qualifying volume | `-3_872_000.55` | | `buy_r_gamma` / `sell_r_gamma` / `net_r_gamma` | Float64 | USD | `(date, figi, callput)` | Retail buy / sell / net flow as USD notional × gamma. | model | T+1 | 2019-11-10 | 0.0 when no qualifying volume | `1_245_030.30` | | `buy_r_vega` / `sell_r_vega` / `net_r_vega` | Float64 | USD | `(date, figi, callput)` | Retail buy / sell / net flow as USD notional × vega. | model | T+1 | 2019-11-10 | 0.0 when no qualifying volume | `8_801_240.40` | | `buy_i_shares` / `sell_i_shares` / `net_i_shares` | Float64 | underlying-share equivalents | `(date, figi, callput)` | Institutional buy / sell / net share equivalents. | model | T+1 | 2019-11-10 | 0.0 when no qualifying volume | `128_450.0` | | `buy_i_premium` / `sell_i_premium` / `net_i_premium` | Float64 | USD | `(date, figi, callput)` | Institutional dollar premium buy / sell / net. | model | T+1 | 2019-11-10 | 0.0 when no qualifying volume | `412_034.55` | | `buy_i_delta` / `sell_i_delta` / `net_i_delta` | Float64 | USD | `(date, figi, callput)` | Institutional buy / sell / net flow as USD notional × delta for the (underlying, callput) row. Positive `net` = net long-delta demand from the institutional class on this call/put side. | model | T+1 | 2019-11-10 | 0.0 when no qualifying volume | `89_231_000.00` | | `buy_i_gamma` / `sell_i_gamma` / `net_i_gamma` | Float64 | USD | `(date, figi, callput)` | Institutional buy / sell / net flow as USD notional × gamma. | model | T+1 | 2019-11-10 | 0.0 when no qualifying volume | `4_188_270.70` | | `buy_i_vega` / `sell_i_vega` / `net_i_vega` | Float64 | USD | `(date, figi, callput)` | Institutional buy / sell / net flow as USD notional × vega. | model | T+1 | 2019-11-10 | 0.0 when no qualifying volume | `31_200_480.80` | | `buy_m_shares` / `sell_m_shares` / `net_m_shares` | Float64 | underlying-share equivalents | `(date, figi, callput)` | Market-maker buy / sell / net share equivalents. `_i + _r + _m ≈ 0` per row. | model | T+1 | 2019-11-10 | 0.0 when no qualifying volume | `-130_860.0` | | `buy_m_premium` / `sell_m_premium` / `net_m_premium` | Float64 | USD | `(date, figi, callput)` | Market-maker dollar premium buy / sell / net. Mirror of `_i + _r`. | model | T+1 | 2019-11-10 | 0.0 when no qualifying volume | `-824_067.10` | | `buy_m_delta` / `sell_m_delta` / `net_m_delta` | Float64 | USD | `(date, figi, callput)` | Market-maker buy / sell / net flow as USD notional × delta. Mirror of `_i + _r`. | model | T+1 | 2019-11-10 | 0.0 when no qualifying volume | `-85_359_000.00` | | `buy_m_gamma` / `sell_m_gamma` / `net_m_gamma` | Float64 | USD | `(date, figi, callput)` | Market-maker buy / sell / net flow as USD notional × gamma. | model | T+1 | 2019-11-10 | 0.0 when no qualifying volume | `-5_433_300.00` | | `buy_m_vega` / `sell_m_vega` / `net_m_vega` | Float64 | USD | `(date, figi, callput)` | Market-maker buy / sell / net flow as USD notional × vega. | model | T+1 | 2019-11-10 | 0.0 when no qualifying volume | `-40_001_720.00` | ## The `CP` row `callput = 'CP'` is a pre-computed call-vs-put row on the same `(date, figi)` grain, stored alongside the `'C'` and `'P'` rows (not synthesised at query time). Convention per column type: - **Cohort size columns** (`buy_*_shares` / `sell_*_shares` / `net_*_shares` / `buy_*_premium` / `sell_*_premium` / `net_*_premium` for cohorts `r`, `i`, `m`) — value is **C − P** (call side minus put side). Negative values on `buy_*` / `sell_*` are expected on put-heavy names; the sign reflects which side is bigger. - **Cohort greek columns** (`buy_*_delta` / `sell_*_delta` / `net_*_delta` and the `_gamma` / `_vega` equivalents) — value is **C + P**. Put delta is stored negative on the `'P'` row, so `C + P` is naturally signed (positive `net_i_delta` on `CP` = net long-delta demand from institutions once puts are netted). - **Volume context** (`n_contracts`, `total_shares`) — value is **C + P** (underlying-level totals across both sides). - **Underlying context** (`last_underprice`, `atm_iv_30d`) — same value as the `'C'` and `'P'` rows for the same `(date, figi)` (underlying-level, not per-side). Read the `CP` row for a single call-vs-put summary per underlying per session. For gross activity split by side, read the `'C'` and `'P'` rows directly. ## Gross-volume accounting Each trade has a customer side (`buy_{r,i}_shares` / `sell_{r,i}_shares`) and a market-maker counterparty (`buy_m_shares` / `sell_m_shares`, rebuilt to absorb the customer imbalance so the row-level `_i + _r + _m ≈ 0` identity holds on notionals). Same convention applies to `_premium` / `_delta` / `_gamma` / `_vega`. **DO**: aggregate on `total_shares` — one entry per trade. **DON'T**: sum the cohort legs (`buy_r_shares + sell_r_shares + buy_i_shares + sell_i_shares + buy_m_shares + sell_m_shares`) — inflates volume by ~30-35% because the market-maker leg gets added on top of the customer legs. --- # underlying_option_flows_10_min 10-minute-bucketed sibling of `underlying_option_flows_01_day`. Per-underlying × callput option-flow aggregation at a 10-minute cadence, with contract-count context and session-end marks. - Grain: `(date, ten_min_timeframe, figi, callput)`. Up to three rows per underlying per 10-min bucket: `'C'` (call side), `'P'` (put side), and `'CP'` (call-vs-put differential — see "The CP row" section below). - History: 2019-11-10 → - Timing: T+1 (published between 05:00 and 09:00 UTC on the calendar day after the trading date; Friday's data lands Saturday) Underlying price, turnover, and metadata are not baked into this table. Join `px_10_min` (adjust locally with `split_factors`) and `stock_mapping` on `traded_underlying`. Request window is capped at 30 days per call on the REST endpoint. For multi-year intraday history use flat files at `gs://prod-masscrest/underlying_option_flows/v0/10_min/`. ### Pre-aggregated units — no client-side conversion Every `_shares` / `_premium` / `_delta` / `_gamma` / `_vega` column on this surface is **already multiplied by `adj_shares_deliverable`** (standard 100 for pre-corporate-action contracts) at the pipeline stage: - `_shares` = `Σ (qty × adj_shares_deliverable)` — underlying-share equivalents (not contracts). - `_premium` = `Σ (qty × traded_price × adj_shares_deliverable)` — USD notional. - `_delta` / `_gamma` / `_vega` = `Σ (qty × traded_price × per-contract greek × adj_shares_deliverable)` — USD notional × greek. Consume the values directly. This differs from the per-contract base table `option_flows_10_min`, where the same fields ship raw (pre-multiplier) and require a `contract_id_mapping` join for the deliverable. ### Bucket boundary convention `ten_min_timeframe` is the end of the 10-minute bucket, NY wall-clock, naive `DateTime`. Bucket covers `(ten_min_timeframe − 10 min, ten_min_timeframe]`. First RTH bucket: `09:40:00`; last: `16:00:00`. Same convention as `option_flows_10_min` and `px_10_min`. ### Difference vs `option_flows_10_min` `option_flows_10_min` is per-contract, one row per unique traded contract (with `_qty` in contracts and `_premium` / greek columns in raw base-contract units). `underlying_option_flows_10_min` collapses every contract on `(date, ten_min_timeframe, figi, callput)` into a single row, applies the deliverable multiplier at ingest, and keeps the call vs put dimension. Intended surface for cross-sectional or callput-aware research. ## Fields | field_name | type | unit | grain | description | provenance | timing_lag | history_start | null_semantics | example | |---|---|---|---|---|---|---|---|---|---| | `date` | Date | trading day (UTC) | `(date, ten_min_timeframe, figi, callput)` | Session date. | exchange | T+1 | 2019-11-10 | never null | `2026-07-24` | | `ten_min_timeframe` | DateTime | NY wall-clock, bucket close | `(date, ten_min_timeframe, figi, callput)` | End timestamp of the 10-min bucket (naive NY-time). | exchange | T+1 | 2019-11-10 | never null | `2026-07-24T09:40:00` | | `figi` | String(12) | identifier | `(date, ten_min_timeframe, figi, callput)` | Composite FIGI for the underlying. | exchange | n/a | 2019-11-10 | never null | `BBG000MM2P62` | | `traded_underlying` | String | ticker | `(date, ten_min_timeframe, figi, callput)` | Listed equity ticker on `date`. Dotted dual-class form. | exchange | T+1 | 2019-11-10 | never null | `AAPL` | | `callput` | String(2) | option type | `(date, ten_min_timeframe, figi, callput)` | `'C'` (call side), `'P'` (put side), or `'CP'` (call-vs-put differential row). | exchange | T+1 | 2019-11-10 | never null | `C` | | `n_contracts` | UInt32 | contracts | `(date, ten_min_timeframe, figi, callput)` | Distinct option contracts traded on this underlying × callput within the bucket. | exchange | T+1 | 2019-11-10 | 0 on no-volume buckets | `48` | | `total_shares` | Float64 | underlying-share equivalents | `(date, ten_min_timeframe, figi, callput)` | Sum of all traded volume converted to share equivalents within the bucket (`Σ qty × adj_shares_deliverable`). | exchange | T+1 | 2019-11-10 | 0.0 on no-volume buckets | `184_500.0` | | `last_underprice` | Float64 | USD | `(date, ten_min_timeframe, figi, callput)` | Underlying spot at the last option trade within the bucket. Not split-adjusted. | exchange | T+1 | 2019-11-10 | null on no-volume buckets | `224.10` | | `atm_iv_30d` | Float64 | annualised vol | `(date, ten_min_timeframe, figi, callput)` | Best-effort estimate of ATM 30-day implied volatility for the underlying in this bucket. Not derived from a fitted volatility surface. For high-accuracy implied-volatility estimation, particularly on less liquid contracts, we recommend using specialist third-party providers. | derived | T+1 | 2019-11-10 | may be null when the figi had no option activity in the bucket | `0.2418` | | `buy_r_shares` / `sell_r_shares` / `net_r_shares` | Float64 | underlying-share equivalents | `(date, ten_min_timeframe, figi, callput)` | Retail buy / sell / net share equivalents within the bucket. | model | T+1 | 2019-11-10 | 0.0 when no qualifying volume | `2_410.0` | | `buy_r_premium` / `sell_r_premium` / `net_r_premium` | Float64 | USD | `(date, ten_min_timeframe, figi, callput)` | Retail dollar premium buy / sell / net. | model | T+1 | 2019-11-10 | 0.0 when no qualifying volume | `31_204.0` | | `buy_r_delta` / `sell_r_delta` / `net_r_delta` | Float64 | USD | `(date, ten_min_timeframe, figi, callput)` | Retail buy / sell / net flow as USD notional × delta within the bucket. Naturally signed by option type; positive `net` = net long-delta demand from retail on this call/put side in the bucket. | model | T+1 | 2019-11-10 | 0.0 when no qualifying volume | `-387_200.55` | | `buy_r_gamma` / `sell_r_gamma` / `net_r_gamma` | Float64 | USD | `(date, ten_min_timeframe, figi, callput)` | Retail buy / sell / net flow as USD notional × gamma. | model | T+1 | 2019-11-10 | 0.0 when no qualifying volume | `124_530.30` | | `buy_r_vega` / `sell_r_vega` / `net_r_vega` | Float64 | USD | `(date, ten_min_timeframe, figi, callput)` | Retail buy / sell / net flow as USD notional × vega. | model | T+1 | 2019-11-10 | 0.0 when no qualifying volume | `880_140.40` | | `buy_i_shares` / `sell_i_shares` / `net_i_shares` | Float64 | underlying-share equivalents | `(date, ten_min_timeframe, figi, callput)` | Institutional buy / sell / net share equivalents. `_i + _r + _m ≈ 0` per row. | model | T+1 | 2019-11-10 | 0.0 when no qualifying volume | `12_845.0` | | `buy_i_premium` / `sell_i_premium` / `net_i_premium` | Float64 | USD | `(date, ten_min_timeframe, figi, callput)` | Institutional dollar premium buy / sell / net. | model | T+1 | 2019-11-10 | 0.0 when no qualifying volume | `41_203.55` | | `buy_i_delta` / `sell_i_delta` / `net_i_delta` | Float64 | USD | `(date, ten_min_timeframe, figi, callput)` | Institutional buy / sell / net flow as USD notional × delta. Positive `net` = net long-delta demand from the institutional class on this call/put side in the bucket. | model | T+1 | 2019-11-10 | 0.0 when no qualifying volume | `8_923_100.00` | | `buy_i_gamma` / `sell_i_gamma` / `net_i_gamma` | Float64 | USD | `(date, ten_min_timeframe, figi, callput)` | Institutional buy / sell / net flow as USD notional × gamma. | model | T+1 | 2019-11-10 | 0.0 when no qualifying volume | `418_870.70` | | `buy_i_vega` / `sell_i_vega` / `net_i_vega` | Float64 | USD | `(date, ten_min_timeframe, figi, callput)` | Institutional buy / sell / net flow as USD notional × vega. | model | T+1 | 2019-11-10 | 0.0 when no qualifying volume | `3_120_080.80` | | `buy_m_shares` / `sell_m_shares` / `net_m_shares` | Float64 | underlying-share equivalents | `(date, ten_min_timeframe, figi, callput)` | Market-maker buy / sell / net share equivalents. | model | T+1 | 2019-11-10 | 0.0 when no qualifying volume | `-13_086.0` | | `buy_m_premium` / `sell_m_premium` / `net_m_premium` | Float64 | USD | `(date, ten_min_timeframe, figi, callput)` | Market-maker dollar premium buy / sell / net. Mirror of `_i + _r`. | model | T+1 | 2019-11-10 | 0.0 when no qualifying volume | `-82_406.71` | | `buy_m_delta` / `sell_m_delta` / `net_m_delta` | Float64 | USD | `(date, ten_min_timeframe, figi, callput)` | Market-maker buy / sell / net flow as USD notional × delta. Mirror of `_i + _r`. | model | T+1 | 2019-11-10 | 0.0 when no qualifying volume | `-8_535_900.00` | | `buy_m_gamma` / `sell_m_gamma` / `net_m_gamma` | Float64 | USD | `(date, ten_min_timeframe, figi, callput)` | Market-maker buy / sell / net flow as USD notional × gamma. | model | T+1 | 2019-11-10 | 0.0 when no qualifying volume | `-543_330.00` | | `buy_m_vega` / `sell_m_vega` / `net_m_vega` | Float64 | USD | `(date, ten_min_timeframe, figi, callput)` | Market-maker buy / sell / net flow as USD notional × vega. | model | T+1 | 2019-11-10 | 0.0 when no qualifying volume | `-4_001_720.00` | ## The `CP` row `callput = 'CP'` is a pre-computed call-vs-put row on the same `(date, ten_min_timeframe, figi)` grain, stored alongside the `'C'` and `'P'` rows. Convention per column type: - **Cohort size columns** (`buy_*_shares` / `sell_*_shares` / `net_*_shares` / `buy_*_premium` / `sell_*_premium` / `net_*_premium` for cohorts `r`, `i`, `m`) — value is **C − P** (call side minus put side) within the bucket. Negative values on `buy_*` / `sell_*` are expected on put-heavy buckets. - **Cohort greek columns** (`buy_*_delta` / `sell_*_delta` / `net_*_delta` and the `_gamma` / `_vega` equivalents) — value is **C + P**. Put delta is stored negative on the `'P'` row, so `C + P` is naturally signed. - **Volume context** (`n_contracts`, `total_shares`) — value is **C + P** (bucket totals across both sides). - **Underlying context** (`last_underprice`, `atm_iv_30d`) — same value as the `'C'` and `'P'` rows for the same bucket (underlying-level, not per-side). Read the `CP` row for a single call-vs-put summary per underlying per bucket. For gross activity split by side, read the `'C'` and `'P'` rows directly. ## Gross-volume accounting Each trade has a customer side (`buy_{r,i}_shares` / `sell_{r,i}_shares`) and a market-maker counterparty (`buy_m_shares` / `sell_m_shares`, rebuilt to absorb the customer imbalance so the row-level `_i + _r + _m ≈ 0` identity holds on notionals). Same convention applies to `_premium` / `_delta` / `_gamma` / `_vega`. **DO**: aggregate on `total_shares` — one entry per trade per bucket. **DON'T**: sum the cohort legs (`buy_r_shares + sell_r_shares + buy_i_shares + sell_i_shares + buy_m_shares + sell_m_shares`) — inflates per-bucket volume by ~30-35% because the market-maker leg gets added on top of the customer legs. --- # underlying_flow_signals_01_day Daily flow-signal derivatives computed on the underlying-level nets (`net_i_*`, `net_r_*`, greeks included) from `underlying_option_flows_01_day`. Per-`(date, figi, callput)` grain. All fields are trailing-window transforms (no forward-looking information), so a row at `date = D` is safe to use for a decision at close of `D` (or, more conservatively, at open of `D+1`). - Grain: `(date, figi, callput)` where `callput ∈ {'C', 'P', 'CP'}`. - History: 2019-11-10 → - Timing: T+1 (published between 05:00 and 09:00 UTC on the calendar day after the trading date — Friday's data lands Saturday; same publish window as flows). ### Signal design This is an in-house Masscrest signal built to flag options activity that is extreme against a stock's own history. Each raw daily net is summed over a 21-trading-day trailing window and then z-scored against its own 2-year rolling mean and standard deviation, scoped to `(figi, callput)`. - `_21dma` = 21-trading-day rolling SUM of the raw daily net, PARTITION BY `(figi, callput)`. (Historically shipped as a rolling mean; arithmetically equivalent for the z-score, since the sum is just the mean multiplied by 21. The distributed series is now the sum.) - `z_net_*_21dma` = z-score of `_21dma` against its 2-year (504 trading day) trailing mean and sample standard deviation. Minimum 63 non-null observations in the trailing window required, else null. Rounded to 2 decimals on emit. The raw rolling μ and σ series feed the z-score internally; only the resulting z-score ships on the response (the baseline `_21dma_2y_avg` / `_21dma_2y_sd` columns are dropped from the wire — clients get the raw daily net, the 21dma, and the z, which is the complete signal contract). The calculation is deliberately simple. Options activity is not stationary across stocks or over time, and z-scores can reach unrealistic multiples of 3 SD during earnings, M&A, or other catalysts. Masscrest does not cap, winsorise, or otherwise regularise the output: keeping the formula plain avoids overfitting to any specific regime and lets clients replicate the transform end-to-end from `underlying_option_flows_01_day` if they want to. Validation of the single-name spike predicate lives at `/research/systematic-single-stock-signal`; the industry-aggregated version and its long-short performance live at `/research/industry-flow-signal`. ### `callput` semantics Every row is scoped to one callput bucket: - `C`: calls only. Raw nets and greeks are direct sums of the call-side contracts. - `P`: puts only. Raw nets and greeks are direct sums of the put-side contracts. - `CP`: pre-computed combined view. Two rules apply, split by metric family: - Size measures (`shares`, `premium`) are `C − P`. These columns are unsigned magnitudes on the `C` and `P` rows, so the pipeline sign-flips the put side on the `CP` row. The result is a directional net: bullish call flow minus bearish put flow, both of which express bullish conviction. - Greeks (`delta`, `gamma`, `vega`) are `C + P`. These columns are already signed on the `C` and `P` rows (calls carry positive delta, puts carry negative), so a straight sum captures net book exposure. `net_*_delta` on the `CP` row is net directional demand across calls and puts combined; same idea for `net_*_gamma` and `net_*_vega`. Rolling windows PARTITION BY `(figi, callput)`; each callput row has its own independent trailing 21dma and 2y baseline, so `z(C) + z(P) ≠ z(CP)`. Filter to exactly one `callput` per query or triple-count. ### Warm-up All fields are computed from 2019-11-10 forward. The 21-day rolling sum needs 21 non-null daily nets; the z-score needs ≥ 63 non-null observations in the trailing 504-session (2-year) window and emits NULL until that threshold is met. Transform chain: daily net → 21-day trailing sum (`_21dma`) → z-score against 2y rolling μ/σ of the 21dma (`z_*`). ### Fire semantics live at the consumer The signal table does not persist `signal_*` (2σ / 3σ fire) or `hp_*` (22-day holding-period) columns; derive them at query time from the z-scores. Masscrest's canonical spike predicate is: WHERE z_net_i_delta_21dma > 2 AND callput = 'CP' -- 2σ institutional-delta spike WHERE z_net_i_delta_21dma > 3 AND callput = 'CP' -- 3σ institutional-delta spike Both retail (`net_r_*`) and institutional (`net_i_*`) transforms ship. By convention Masscrest treats large institutional-side deviations as the primary tradable spike; the retail-side z-scores are available for symmetric analysis (crowded-consensus vs smart-money-vs-retail divergence checks, see the `crowding_tag` in the `taxonomy` resource). ### Adding liquidity and stock-return context This table only carries the signal itself, not stock liquidity or stock returns. If you want to filter for liquid names (e.g. "ignore anything under $10M average daily dollar turnover") or overlay stock returns for validation, join `px_01_day` on `(date, figi)`. Dollar turnover (`turnover`) can be averaged directly; for split-adjusted returns from `close`, also join `split_factors`. ## Fields | field_name | type | unit | grain | description | provenance | timing_lag | history_start | null_semantics | example | |---|---|---|---|---|---|---|---|---|---| | `date` | Date | trading day (UTC) | `(date, figi, callput)` | Session date. US equity trading calendar. | exchange | T+1 | 2019-11-10 | never null | `2026-07-24` | | `figi` | String(12) | identifier | `(date, figi, callput)` | OpenFIGI composite FIGI for the underlying. Stable across ticker changes; join key to `stock_mapping`, `px_01_day`, `split_factors`. | exchange | n/a | 2019-11-10 | never null | `BBG000MM2P62` | | `underlying_ticker` | String | ticker | `(date, figi, callput)` | Point-in-time listed equity ticker on `date`. Dotted dual-class form (`BRK.B`). Named `underlying_ticker` here rather than `traded_underlying`; the API SELECT aliases it back to `traded_underlying` on the wire. | exchange | T+1 | 2019-11-10 | never null | `AAPL` | | `callput` | LowCardinality(String) | enum | `(date, figi, callput)` | One of `'C'`, `'P'`, `'CP'`. See the `callput` note above for the CP synthesis rule. | derived | T+1 | 2019-11-10 | never null | `CP` | | `net_i_shares` | Float64 | underlying-share equivalents | `(date, figi, callput)` | Institutional net share-equivalent flow for the callput bucket. Calculated as `sum(net_i_qty × adj_shares_deliverable)`: the underlying-share equivalent of the traded contracts (standard OCC contract = 100 shares, adjusted for corporate actions). On `callput='CP'` rows: `C − P` (bullish-conviction sign convention). | model | T+1 | 2019-11-10 | 0.0 when no qualifying institutional flow | `18_450.0` | | `net_r_shares` | Float64 | underlying-share equivalents | `(date, figi, callput)` | Retail net share-equivalent flow. Same formula (`sum(net_r_qty × adj_shares_deliverable)`) and CP-synthesis rule as `net_i_shares`. | model | T+1 | 2019-11-10 | 0.0 when no qualifying retail flow | `4_120.0` | | `net_i_premium` | Float64 | USD | `(date, figi, callput)` | Institutional net dollar premium flow. On `CP` rows: `C − P`. | model | T+1 | 2019-11-10 | 0.0 when no qualifying institutional flow | `1_820_038.55` | | `net_r_premium` | Float64 | USD | `(date, figi, callput)` | Retail net dollar premium flow. | model | T+1 | 2019-11-10 | 0.0 when no qualifying retail flow | `312_040.0` | | `net_i_delta` | Float64 | USD | `(date, figi, callput)` | Institutional net flow as USD notional × delta. Naturally signed; positive = net long-delta demand. On `CP` rows: `C + P` (delta is signed by option type; put deltas are stored negative, so a straight sum gives net directional demand). | model | T+1 | 2019-11-10 | 0.0 when no qualifying institutional flow | `12_530_000.00` | | `net_r_delta` | Float64 | USD | `(date, figi, callput)` | Retail net flow as USD notional × delta. | model | T+1 | 2019-11-10 | 0.0 when no qualifying retail flow | `-870_000.00` | | `net_i_gamma` | Float64 | USD × gamma | `(date, figi, callput)` | Institutional net flow as USD notional × gamma. On `CP` rows: `C + P` (gamma is naturally signed by option type). Positive = net gamma demand. | model | T+1 | 2019-11-10 | 0.0 when no qualifying institutional flow | `84_000.0` | | `net_r_gamma` | Float64 | USD × gamma | `(date, figi, callput)` | Retail net gamma flow. | model | T+1 | 2019-11-10 | 0.0 when no qualifying retail flow | `12_100.0` | | `net_i_vega` | Float64 | USD × vega | `(date, figi, callput)` | Institutional net flow as USD notional × vega. On `CP` rows: `C + P`. Positive = net long-vol demand. | model | T+1 | 2019-11-10 | 0.0 when no qualifying institutional flow | `1_400_000.0` | | `net_r_vega` | Float64 | USD × vega | `(date, figi, callput)` | Retail net vega flow. | model | T+1 | 2019-11-10 | 0.0 when no qualifying retail flow | `-210_000.0` | | `net_i_shares_21dma` | Float64 | underlying-share equivalents | `(date, figi, callput)` | Rolling sum of `net_i_shares` over the prior 21 trading days (inclusive of `date`), scoped to this `callput`. | derived | T+1 | 2019-11-10 | null when the trailing 21-day window has fewer than 21 non-null observations | `319_284.0` | | `net_r_shares_21dma` | Float64 | underlying-share equivalents | `(date, figi, callput)` | Rolling 21-day sum of `net_r_shares`. | derived | T+1 | 2019-11-10 | null when the trailing 21-day window has fewer than 21 non-null observations | `68_040.0` | | `net_i_premium_21dma` | Float64 | USD | `(date, figi, callput)` | Rolling 21-day sum of `net_i_premium`. | derived | T+1 | 2019-11-10 | null when the trailing 21-day window has fewer than 21 non-null observations | `25_294_500.0` | | `net_r_premium_21dma` | Float64 | USD | `(date, figi, callput)` | Rolling 21-day sum of `net_r_premium`. | derived | T+1 | 2019-11-10 | null when the trailing 21-day window has fewer than 21 non-null observations | `5_040_252.0` | | `net_i_delta_21dma` | Float64 | USD | `(date, figi, callput)` | Rolling 21-day sum of `net_i_delta`. | derived | T+1 | 2019-11-10 | null when the trailing 21-day window has fewer than 21 non-null observations | `206_220_000.0` | | `net_r_delta_21dma` | Float64 | USD | `(date, figi, callput)` | Rolling 21-day sum of `net_r_delta`. | derived | T+1 | 2019-11-10 | null when the trailing 21-day window has fewer than 21 non-null observations | `-13_020_000.0` | | `net_i_gamma_21dma` | Float64 | USD × gamma | `(date, figi, callput)` | Rolling 21-day sum of `net_i_gamma`. | derived | T+1 | 2019-11-10 | null when the trailing 21-day window has fewer than 21 non-null observations | `1_512_000.0` | | `net_r_gamma_21dma` | Float64 | USD × gamma | `(date, figi, callput)` | Rolling 21-day sum of `net_r_gamma`. | derived | T+1 | 2019-11-10 | null when the trailing 21-day window has fewer than 21 non-null observations | `218_400.0` | | `net_i_vega_21dma` | Float64 | USD × vega | `(date, figi, callput)` | Rolling 21-day sum of `net_i_vega`. | derived | T+1 | 2019-11-10 | null when the trailing 21-day window has fewer than 21 non-null observations | `24_780_000.0` | | `net_r_vega_21dma` | Float64 | USD × vega | `(date, figi, callput)` | Rolling 21-day sum of `net_r_vega`. | derived | T+1 | 2019-11-10 | null when the trailing 21-day window has fewer than 21 non-null observations | `-3_780_000.0` | | `z_net_i_shares_21dma` | Float64 | z-score | `(date, figi, callput)` | Z-score of `net_i_shares_21dma` against its trailing 2-year rolling mean and sample standard deviation, computed at query time. Rounded to 2 decimals on emit. | derived | T+1 | 2019-11-10 | null when the trailing 2y window has fewer than 63 non-null observations or its sample stddev is 0 | `1.34` | | `z_net_r_shares_21dma` | Float64 | z-score | `(date, figi, callput)` | Same construction as `z_net_i_shares_21dma`, applied to the retail 21dma. | derived | T+1 | 2019-11-10 | null when the trailing 2y window is unwarmed or its sample stddev is 0 | `0.42` | | `z_net_i_premium_21dma` | Float64 | z-score | `(date, figi, callput)` | Standardised institutional-premium 21dma vs its 2y baseline. | derived | T+1 | 2019-11-10 | null when the trailing 2y window is unwarmed or its sample stddev is 0 | `1.18` | | `z_net_r_premium_21dma` | Float64 | z-score | `(date, figi, callput)` | Standardised retail-premium 21dma vs its 2y baseline. | derived | T+1 | 2019-11-10 | null when the trailing 2y window is unwarmed or its sample stddev is 0 | `-0.24` | | `z_net_i_delta_21dma` | Float64 | z-score | `(date, figi, callput)` | Standardised institutional-delta 21dma vs its 2y baseline. Masscrest's canonical "spike" predicate is `z > 2` (2σ) or `z > 3` (3σ) with `callput = 'CP'`. | derived | T+1 | 2019-11-10 | null when the trailing 2y window is unwarmed or its sample stddev is 0 | `2.08` | | `z_net_r_delta_21dma` | Float64 | z-score | `(date, figi, callput)` | Standardised retail-delta 21dma vs its 2y baseline. | derived | T+1 | 2019-11-10 | null when the trailing 2y window is unwarmed or its sample stddev is 0 | `-0.55` | | `z_net_i_gamma_21dma` | Float64 | z-score | `(date, figi, callput)` | Standardised institutional-gamma 21dma vs its 2y baseline. | derived | T+1 | 2019-11-10 | null when the trailing 2y window is unwarmed or its sample stddev is 0 | `0.86` | | `z_net_r_gamma_21dma` | Float64 | z-score | `(date, figi, callput)` | Standardised retail-gamma 21dma vs its 2y baseline. | derived | T+1 | 2019-11-10 | null when the trailing 2y window is unwarmed or its sample stddev is 0 | `0.11` | | `z_net_i_vega_21dma` | Float64 | z-score | `(date, figi, callput)` | Standardised institutional-vega 21dma vs its 2y baseline. Masscrest's ≤ −3σ vega tail is historically followed by ~1.6 vol-point IV declines over 21 days (see `research/vega-iv-signal`). | derived | T+1 | 2019-11-10 | null when the trailing 2y window is unwarmed or its sample stddev is 0 | `-1.42` | | `z_net_r_vega_21dma` | Float64 | z-score | `(date, figi, callput)` | Standardised retail-vega 21dma vs its 2y baseline. | derived | T+1 | 2019-11-10 | null when the trailing 2y window is unwarmed or its sample stddev is 0 | `0.30` | --- ### Z-score precision `z_*` fields are rounded to 2 decimals on emit. When comparing a client-side derived spike predicate (`z > 2`) against the rounded value, expect edge cases on low-vol names where the true unrounded z sits just under 2.00 but the rounded value equals 2.00. ### CP synthesis is pre-computed, not query-time The `'CP'` rows are materialised at publish time, not derived at query time. Each `(figi, callput)` bucket has its own independent 21dma and 2y baseline trajectory; `z_net_i_delta_21dma` on the `CP` row is derived from `CP`'s own trailing series, not from summing per-side z-scores. ### Deriving fire flags and holding periods The table does not persist pre-computed fire flags (e.g. "2σ spike today = TRUE") or holding-period booleans. Compute them at query time from the z-scores: `WHERE z_net_i_delta_21dma > 2 AND callput = 'CP'` for the canonical 2σ institutional-delta spike. For a 22-day holding-period rule (fire within the last N days and the same z has not gone negative since), a self-join or a rolling `max(z)` window on the same series is enough. --- # grouped_flow_signal_01_day Group-level daily flow-signal derivatives: sector / industry / etf / adr / single_stock / all rollups of the underlying-level nets and greeks aggregated in `underlying_flow_signals_01_day`. Same 50-flow-metric shape across 5 layers × 10 metrics. Per-`(date, group_key, callput)` grain. - Grain: `(date, group_key, callput)` - History: 2019-11-10 → - Timing: T+1 (published shortly after `underlying_flow_signals_01_day` on the option-analytics workflow, typically 06:00-11:00 UTC on the calendar day after the trading date — Friday's data lands Saturday). ## Response-shape rewrite (2026-08-14) Every row is keyed by a **single `group_key` STRING column**. `group_type` on the REST endpoint takes the same set of canonical values (case-insensitive on input) — a mixed-case sector name (e.g. `"Communication Services"`), a mixed-case industry name (e.g. `"Semiconductors"`), or one of the rollup literals `"etf"` / `"adr"` / `"single_stock"` / `"all"`. The row echoes that value back in `group_key`. ## Group semantics | Query `group_type` | `group_key` shape (on the row) | Row count per (date, callput) | Notes | |---|---|---|---| | `sector` | Canonical mixed-case sector name (e.g. `Technology`, `Financial Services`) | ~11 distinct values | **Excludes ETFs and ADRs.** Figis missing a sector label drop from these rows. | | `industry` | Canonical mixed-case industry name (e.g. `Semiconductors`, `Software - Application`) | ~150 distinct values (matches the FMP industry taxonomy) | **Excludes ETFs and ADRs.** Same drop-on-missing rule. Every industry-day is emitted (gap-free time series) with `n_figi` populated for downstream filtering — see "Thin-industry handling" below. | | `etf` | Literal string `etf` | 1 | Aggregate over all figis with `isEtf='true'` in the latest-known phase (live or delisted). | | `adr` | Literal string `adr` | 1 | Aggregate over all figis with `isAdr='true'` in the latest-known phase (live or delisted). | | `single_stock` | Literal string `single_stock` | 1 | Everything else (non-ETF, non-ADR). Figis missing from `stock_mapping` are treated as `single_stock`. | | `all` | Literal string `all` | 1 | Every figi in the flow universe, no filter. | ### Why sector / industry exclude ETFs and ADRs FMP tags each ETF's `sector` with the issuer's legal-entity sector (typically `Financial Services` for the sponsor, not the underlying-exposure sector). Pooling ETFs into sector rollups inflated `Financial Services` gross flow by 85%+ — meaningless for cohort analysis. Same-shape distortion for ADRs. The 2026-08-11 pipeline change excludes both from `sector` and `industry` aggregations; they get their own top-level `etf` and `adr` branches. As a consequence, `sum(sector) ≠ all` — the residual is precisely the `etf + adr` contribution. ## Valid sector values The ~11 distinct `group_key` strings emitted when `group_type = 'sector'`. Case-insensitive on input; canonical mixed-case on the wire. As-of 2026-08-14 snapshot; may drift as figis are reclassified or new sectors are added. | Sector | Description | |---|---| | `Basic Materials` | Companies extracting and processing raw commodities used across the economy: metals and mining, chemicals, forestry, and construction materials. Cyclically exposed to global industrial demand. | | `Communication Services` | Firms that carry information or entertainment to end users: telecom carriers, media conglomerates, interactive-media platforms, publishers, and gaming. Combines legacy telecom with digital-native content. | | `Consumer Cyclical` | Businesses selling discretionary goods and services whose demand rises and falls with the business cycle: automakers, apparel, restaurants, travel, leisure, and specialty retail. Highly sensitive to consumer confidence. | | `Consumer Defensive` | Producers of everyday essentials that consumers buy regardless of the economy: packaged food, beverages, tobacco, household staples, and discount retailers. Stable revenue through downturns. | | `Energy` | The oil, gas, coal and related supply chain: exploration and production, refining, midstream infrastructure, and oilfield services. Tied to commodity prices and global demand cycles. | | `Financial Services` | Firms that intermediate capital: banks, insurers, asset managers, brokerages, exchanges, and credit-services companies. Rate-sensitive and cycle-exposed. | | `Healthcare` | Businesses across the health value chain: drug and biotech developers, medical-device makers, diagnostics labs, healthcare providers, health insurers, and pharma distributors. Mix of defensive demand and secular growth. | | `Industrials` | Capital goods and services that keep the economy moving: aerospace, defence, machinery, transportation, construction, and logistics. Cyclically exposed to capex spending. | | `Real Estate` | Property-owning and property-servicing companies, dominated by REITs across residential, retail, office, industrial, healthcare, and specialty sub-types. Highly interest-rate-sensitive. | | `Technology` | Hardware and software firms including semiconductors, application and infrastructure software, IT services, computer hardware, and consumer electronics. Growth-biased, with high cyclicality on the semiconductor side. | | `Utilities` | Regulated and independent providers of electricity, natural gas, water, and renewable power. Defensive cash flows, bond-like return profile. | ## Valid industry values The ~150 distinct `group_key` strings emitted when `group_type = 'industry'`. Case-insensitive on input; canonical mixed-case on the wire. As-of 2026-08-14 snapshot; may drift as existing figis are reclassified or new industries are added. For the live authoritative list, call the grouped-flow-signal endpoint with `group_type=industry` and take the distinct `group_key` values from the response. Every industry with any flow on a given date is emitted (2026-08-28 revision — the time series is gap-free). Every industry-day carries populated raw daily nets AND populated rolling / z fields; `n_figi` is exposed on the row so consumers can filter thin-breadth days at query time. See "Thin-industry handling" below for the z-magnitude comparability caveat on structurally-thin industries such as `Copper`, `Uranium`, `Consulting Services`, `Regulated Water`, `Conglomerates`, `Waste Management`, and `Railroads`. | Industry | Description | |---|---| | `Advertising Agencies` | Agencies that plan, create and place advertising across traditional and digital channels for brand and direct-response clients. | | `Aerospace & Defense` | Manufacturers of civil aircraft, military platforms, missiles, satellites and defence-electronics systems for governments and commercial airlines. | | `Agricultural - Machinery` | Producers of tractors, combines, harvesters and other equipment used in commercial farming. | | `Agricultural Farm Products` | Growers, processors and marketers of grains, oilseeds, produce, meat and other primary agricultural output. | | `Agricultural Inputs` | Suppliers of seeds, fertilisers, pesticides and other inputs consumed by farms. | | `Airlines, Airports & Air Services` | Passenger and cargo airlines, airport operators, ground-handling firms and other air-transport service providers. | | `Aluminum` | Miners, smelters and rolled-product manufacturers of aluminium. | | `Apparel - Footwear & Accessories` | Designers and makers of shoes, handbags, jewellery, watches and other fashion accessories. | | `Apparel - Manufacturers` | Companies that design and produce clothing lines under owned or licensed brands. | | `Apparel - Retail` | Retailers selling clothing and accessories through stores or online, including specialty and off-price chains. | | `Asset Management` | Firms managing pooled investment vehicles (mutual funds, ETFs, separate accounts) across broad multi-asset strategies. | | `Asset Management - Bonds` | Managers specialising in fixed-income mutual funds and bond ETFs. | | `Asset Management - Cryptocurrency` | Managers of cryptocurrency-linked funds, trusts and ETFs. | | `Asset Management - Global` | Managers of internationally-diversified equity and multi-asset funds. | | `Asset Management - Income` | Managers focused on dividend-equity and income-oriented fund products. | | `Asset Management - Leveraged` | Sponsors of leveraged and inverse ETFs. | | `Auto - Dealerships` | Retailers of new and used vehicles, plus related service, parts and financing operations. | | `Auto - Manufacturers` | OEMs producing passenger cars, trucks and, increasingly, electric vehicles. | | `Auto - Parts` | Suppliers of components, systems and modules used in vehicle assembly and aftermarket repair. | | `Auto - Recreational Vehicles` | Makers of RVs, motorcycles, boats and other personal-use motorised recreational vehicles. | | `Banks - Diversified` | Large multi-line banks combining consumer, commercial, investment-banking and wealth divisions. | | `Banks - Regional` | Banks concentrated in a specific geographic footprint, focused on retail deposits and commercial lending. | | `Beverages - Alcoholic` | Brewers, distillers and vintners producing beer, spirits and packaged alcoholic drinks at scale. | | `Beverages - Non-Alcoholic` | Producers of soft drinks, bottled water, juices, energy drinks and other non-alcoholic packaged beverages. | | `Beverages - Wineries & Distilleries` | Craft and specialty wine and spirits producers, generally smaller-scale than the mass-market alcoholic-beverage majors. | | `Biotechnology` | Companies developing novel therapeutics using biological processes, typically pre-commercial or single-product-focused. | | `Broadcasting` | Owners of TV and radio stations, local networks and syndicated content distributors. | | `Business Equipment & Supplies` | Manufacturers of office equipment, printers, copiers and workplace supplies. | | `Chemicals` | Producers of commodity petrochemicals, plastics, industrial gases and basic chemical intermediates. | | `Chemicals - Specialty` | Producers of higher-value differentiated chemicals used in coatings, adhesives, catalysts and formulations. | | `Coal` | Miners and marketers of thermal and metallurgical coal. | | `Communication Equipment` | Makers of network hardware, routers, switches and telecom infrastructure gear. | | `Computer Hardware` | Manufacturers of PCs, servers, storage systems and other computing hardware. | | `Conglomerates` | Multi-industry holding companies spanning several unrelated business lines. | | `Construction` | General contractors and construction firms building commercial, industrial and infrastructure projects. | | `Construction Materials` | Producers of cement, aggregates, gypsum, insulation and other bulk building materials. | | `Consulting Services` | Management, technology, HR and strategy consulting firms selling professional advisory services. | | `Consumer Electronics` | Makers of smartphones, tablets, audio devices, wearables and other consumer-facing electronic products. | | `Copper` | Miners and processors of copper ore and refined copper products. | | `Department Stores` | Multi-category retailers under a single store format spanning apparel, home and accessories. | | `Discount Stores` | Mass-market retailers competing primarily on price across broad general merchandise. | | `Diversified Utilities` | Utilities operating across multiple regulated categories (electric, gas, water) rather than a single service. | | `Drug Manufacturers - General` | Large pharmaceutical companies with broad marketed portfolios and multi-therapeutic pipelines. | | `Drug Manufacturers - Specialty & Generic` | Pharma companies focused on generic drugs, biosimilars or niche specialty therapeutic areas. | | `Education & Training Services` | For-profit universities, career schools, tutoring and corporate training providers. | | `Electrical Equipment & Parts` | Manufacturers of electrical components, motors, transformers, cables and industrial electrical systems. | | `Electronic Gaming & Multimedia` | Video-game publishers, developers, esports operators and interactive-entertainment firms. | | `Engineering & Construction` | Firms providing engineering-design and heavy-construction services for large infrastructure projects. | | `Entertainment` | Film, TV and content-production studios and integrated entertainment companies. | | `Environmental Services` | Waste-water treatment, environmental consulting and remediation service providers. | | `Financial - Capital Markets` | Investment banks, broker-dealers and trading firms operating in equity, fixed-income and derivatives markets. | | `Financial - Conglomerates` | Diversified financial holding companies spanning several finance subsectors. | | `Financial - Credit Services` | Consumer-finance companies, credit-card networks, payment processors and buy-now-pay-later providers. | | `Financial - Data & Stock Exchanges` | Exchange operators, index providers, financial-data vendors and market-infrastructure firms. | | `Financial - Diversified` | Miscellaneous financial-services companies that do not fit the more specific finance subsectors. | | `Financial - Mortgages` | Mortgage originators, servicers and secondary-market intermediaries. | | `Food Confectioners` | Producers of chocolate, candy, chewing gum and other confectionery products. | | `Food Distribution` | Wholesalers distributing food and related goods to restaurants, retailers and institutional customers. | | `Furnishings, Fixtures & Appliances` | Makers of home furniture, bedding, kitchen appliances and household fixtures. | | `Gambling, Resorts & Casinos` | Operators of casinos, integrated resorts and online-gambling platforms. | | `General Transportation` | Diversified transportation companies that do not fit the more specific rail, trucking or air subsectors. | | `Gold` | Miners and refiners of gold ore and physical gold, plus gold-focused streaming and royalty firms. | | `Grocery Stores` | Traditional supermarkets and grocery chains selling food and household goods. | | `Hardware, Equipment & Parts` | General hardware and industrial-equipment manufacturers and distributors. | | `Home Improvement` | Big-box home-improvement retailers and specialty tool, paint and hardware chains. | | `Household & Personal Products` | Manufacturers of cleaning products, personal-care items and consumer packaged household goods. | | `Independent Power Producers` | Non-utility power generators selling electricity into wholesale or contracted markets. | | `Industrial - Distribution` | Wholesalers of industrial equipment, parts, fasteners and MRO supplies. | | `Industrial - Infrastructure Operations` | Operators of ports, pipelines, terminals and other industrial-infrastructure assets. | | `Industrial - Machinery` | Manufacturers of heavy machinery for construction, mining, agriculture and industrial processes. | | `Industrial - Pollution & Treatment Controls` | Providers of air, water and industrial pollution-control equipment and services. | | `Industrial - Specialties` | Specialty industrial firms in niche categories that do not fit broader industrial subsectors. | | `Industrial Materials` | Producers of steel-alternatives, industrial ceramics, composites and other engineered materials. | | `Information Technology Services` | IT-consulting, systems-integration, outsourcing and managed-services firms. | | `Insurance - Brokers` | Insurance and reinsurance brokers acting as intermediaries between clients and underwriters. | | `Insurance - Diversified` | Multi-line insurers writing across life, P&C and other coverage types. | | `Insurance - Life` | Insurers focused on individual and group life insurance, plus annuity products. | | `Insurance - Property & Casualty` | Insurers writing property, auto, liability and other short-tail coverage. | | `Insurance - Reinsurance` | Firms providing insurance to primary insurers to cover concentrated or catastrophic risk. | | `Insurance - Specialty` | Insurers focused on niche or hard-to-place risks (marine, aviation, cyber, professional liability). | | `Integrated Freight & Logistics` | Multi-modal freight and logistics operators spanning trucking, rail, air and ocean. | | `Internet Content & Information` | Digital-content platforms, search engines, social networks and online-information providers. | | `Investment - Banking & Investment Services` | Full-service investment banks and firms providing M&A, underwriting and advisory services. | | `Leisure` | Manufacturers of leisure goods, hobby products, toys and personal-recreation equipment. | | `Luxury Goods` | Producers of high-end fashion, jewellery, watches, leather goods and other luxury consumer categories. | | `Manufacturing - Metal Fabrication` | Firms fabricating metal parts, structures and assemblies for industrial and consumer use. | | `Manufacturing - Miscellaneous` | Diversified manufacturers that do not fit more specific industrial subsectors. | | `Manufacturing - Textiles` | Producers of yarn, fabric and finished textiles for apparel and industrial applications. | | `Manufacturing - Tools & Accessories` | Makers of power tools, hand tools and related industrial and consumer tool accessories. | | `Marine Shipping` | Ocean-freight carriers operating tankers, dry-bulk vessels and container ships. | | `Media & Entertainment` | Diversified media conglomerates spanning television, film, publishing and digital content. | | `Medical - Care Facilities` | Hospital operators, nursing-home chains and specialty care-facility providers. | | `Medical - Devices` | Manufacturers of medical implants, surgical tools, monitoring and therapeutic devices. | | `Medical - Diagnostics & Research` | Clinical-diagnostics labs, life-science research tools and diagnostic-imaging providers. | | `Medical - Distribution` | Wholesale distributors of drugs, medical supplies and healthcare products. | | `Medical - Equipment & Services` | Providers of medical equipment plus related installation, maintenance and services. | | `Medical - Healthcare Information Services` | Health-IT firms providing electronic health records, clinical software and data-analytics platforms. | | `Medical - Healthcare Plans` | Managed-care organisations and health-insurance plans covering employer, individual and government populations. | | `Medical - Instruments & Supplies` | Manufacturers of medical instruments, consumables and disposable healthcare supplies. | | `Medical - Pharmaceuticals` | Broadly-focused pharmaceutical companies not classified under the more specific drug-manufacturer subsectors. | | `Medical - Specialties` | Specialty medical companies operating in niche healthcare categories. | | `Oil & Gas Drilling` | Contract drilling firms operating onshore and offshore rigs for oil-and-gas producers. | | `Oil & Gas Energy` | Diversified oil-and-gas firms that do not fit the more specific upstream, midstream or downstream categories. | | `Oil & Gas Equipment & Services` | Oilfield-services providers offering drilling, completion and reservoir-management equipment and expertise. | | `Oil & Gas Exploration & Production` | Upstream producers exploring for and producing crude oil and natural gas. | | `Oil & Gas Integrated` | Integrated majors operating across upstream, midstream and downstream oil-and-gas businesses. | | `Oil & Gas Midstream` | Pipelines, storage terminals and processing operators moving oil and gas from wellhead to market. | | `Oil & Gas Refining & Marketing` | Downstream refiners and fuel-marketing companies converting crude into gasoline, diesel and petrochemical feedstocks. | | `Other Precious Metals` | Miners of platinum, palladium and other precious metals not classified under gold or silver. | | `Packaged Foods` | Producers of branded packaged and processed foods sold through retail and foodservice channels. | | `Packaging & Containers` | Manufacturers of paper, plastic, glass and metal packaging for consumer and industrial products. | | `Paper, Lumber & Forest Products` | Producers of pulp, paper, lumber and other wood-based industrial and consumer products. | | `Personal Products & Services` | Personal-care product makers, beauty firms and personal-services businesses. | | `Publishing` | Publishers of books, newspapers, magazines and other periodical content in print and digital. | | `REIT - Diversified` | REITs owning property portfolios spanning multiple real-estate categories. | | `REIT - Healthcare Facilities` | REITs owning hospitals, medical-office buildings, senior housing and other healthcare properties. | | `REIT - Hotel & Motel` | REITs owning hotel, motel and hospitality properties. | | `REIT - Industrial` | REITs owning warehouses, logistics facilities and light-industrial properties. | | `REIT - Mortgage` | Mortgage REITs earning spread on residential and commercial mortgage assets rather than owning property directly. | | `REIT - Office` | REITs owning office buildings across urban and suburban markets. | | `REIT - Residential` | REITs owning apartment buildings, single-family rentals and manufactured-home communities. | | `REIT - Retail` | REITs owning shopping malls, strip centres and standalone retail properties. | | `REIT - Specialty` | REITs owning niche property types such as data centres, cell towers, self-storage and infrastructure. | | `Railroads` | Class I and short-line freight-rail operators plus passenger-rail companies. | | `Real Estate - Development` | Property developers building residential, commercial and mixed-use projects for sale or lease. | | `Real Estate - Diversified` | Diversified real-estate operating companies not structured as REITs. | | `Real Estate - Services` | Real-estate brokerages, property-management firms and title-and-appraisal services. | | `Regulated Electric` | Rate-regulated electric utilities serving retail customers in defined service territories. | | `Regulated Gas` | Rate-regulated natural-gas distribution utilities. | | `Regulated Water` | Rate-regulated water and wastewater utilities. | | `Renewable Utilities` | Utilities and power generators focused on solar, wind, hydro and other renewable-energy assets. | | `Rental & Leasing Services` | Firms renting equipment, vehicles and other assets to industrial and consumer customers. | | `Residential Construction` | Homebuilders constructing single-family homes and residential communities for sale. | | `Restaurants` | Restaurant operators and franchisors across quick-service, casual-dining and fine-dining categories. | | `Security & Protection Services` | Providers of guarding, cash-in-transit, alarm-monitoring and security-technology services. | | `Semiconductors` | Designers and manufacturers of integrated circuits, memory, logic chips and related semiconductor equipment. | | `Shell Companies` | Publicly-listed holding entities without significant operations, often SPACs or blank-check vehicles. | | `Silver` | Miners and refiners of silver ore and silver products. | | `Software - Application` | Vendors of packaged and SaaS application software for business, industry-vertical and consumer use. | | `Software - Infrastructure` | Vendors of infrastructure software including databases, operating systems, security and developer tools. | | `Software - Services` | Software-enabled services firms delivering platform-hosted business solutions. | | `Solar` | Manufacturers of solar panels, inverters and installers of solar-power systems. | | `Specialty Business Services` | Business-services firms in specialty categories (data processing, marketing services, testing, inspection). | | `Specialty Retail` | Retailers focused on specific product categories such as electronics, sporting goods, jewellery or auto parts. | | `Staffing & Employment Services` | Temporary-staffing, executive-search and human-capital-management firms. | | `Steel` | Integrated and mini-mill producers of carbon and specialty steel products. | | `Technology Distributors` | Distributors of IT hardware, software and networking products to resellers and enterprise buyers. | | `Telecommunications Services` | Wireless and wireline telecom carriers providing voice and data services to consumers and businesses. | | `Tobacco` | Cigarette manufacturers plus producers of cigars, smokeless tobacco and next-generation nicotine products. | | `Travel Lodging` | Hotel and lodging operators and franchisors across the value, mid-scale and luxury segments. | | `Travel Services` | Online travel agencies, tour operators and travel-booking platforms. | | `Trucking` | Long-haul and less-than-truckload freight-trucking companies. | | `Uranium` | Miners of uranium ore and producers of nuclear-fuel feedstock. | | `Waste Management` | Solid-waste collection, disposal, recycling and hazardous-waste management firms. | ## Signal formula - `_21dma` = 21-trading-day rolling SUM of the raw group-level net, PARTITION BY `(group_key, callput)`. (Historically shipped as a rolling mean; arithmetically equivalent for the z-score, since the sum is just the mean multiplied by 21. The distributed series is now the sum.) - `z_net_*_21dma` = z-score of `_21dma` against its 2-year (504 trading day) trailing mean and sample standard deviation, computed at query time. Minimum 63 non-null observations in the trailing window required, else null. Rounded to 2 decimals on emit. The raw rolling μ and σ series are computed on the fly at query time and are not distributed as separate columns — only the resulting z-score ships. Every industry-day is emitted with populated rolling / z fields. The 21dma and 2y baseline include ALL days regardless of `n_figi` — thin days contribute their (smaller) raw values to the trailing window, so each industry's z is calibrated against its own historical breadth mix and populates on every day. Cross-industry z magnitudes aren't directly comparable when breadth profiles differ meaningfully — filter on `n_figi` at query time if comparability matters. See "Thin-industry handling" below. ## Aggregate identities - `single_stock + etf + adr = all` per (date, callput), 0 residual. - `sum(sector) ≈ single_stock` per (date, callput); small residual (< 1% of gross flow) attributable to single-stock figis without a sector label. - Because sector / industry exclude ETFs and ADRs, `sum(sector) ≠ all` — the difference is precisely the `etf + adr` gross flow. Historical rollups produced before 2026-08-11 rolled ETFs+ADRs into sector; those runs have been superseded. - `sum(industry) ≈ single_stock` per (date, callput). Every industry row (thin or full-breadth) contributes its raw flow to the sum, so this identity holds tighter than it did before 2026-08-28. ## Thin-industry handling Every group with any flow on a given date is emitted — the time series is gap-free. Every row (thin or full-breadth) carries populated raw daily nets AND populated rolling / z fields: - `n_figi` is populated with the honest constituent count (visible in the response) — consumers filter thin-breadth days at query time. - Raw daily nets (`net_i_shares`, `net_r_shares`, …, `net_r_vega`) are populated with the actual group sum. - Rolling / derived columns (`_21dma`, `z_*`) are populated on every row. The 21dma and 2y baseline include ALL days regardless of `n_figi` — thin days contribute their (smaller) raw values to the trailing window and to the 2y μ/σ used to z-score. Each industry's z-score is therefore calibrated against its own historical breadth mix — a chronically-thin industry's baseline is set to its own thin-day scale, so z fires when flow is unusually large *for that industry*. Cross-industry z magnitudes aren't directly comparable when breadth profiles differ meaningfully (a +2σ on a 5-FIGI industry is not directly comparable to a +2σ on a 50-FIGI industry). Filter on `n_figi` at query time when magnitude comparability matters. Sector, etf, adr, single_stock, and all branches always exceed 10 contributing FIGIs so the caveat is a no-op for those branches. Only industries — historically ~46 including `Copper`, `Uranium`, `Consulting Services`, `Regulated Water`, `Conglomerates`, `Waste Management`, `Railroads` — are materially affected. Before 2026-08-28 those industries were dropped entirely and visible as gaps in the served time series; now they emit continuous rows with populated raw flow AND populated (but caveated) z-scores. Live coverage: 11 sectors + ~150 industries + 4 rollups (`all`, `etf`, `adr`, `single_stock`) — every group with flow that day appears in the cross-section. ## Classification is not strictly PIT The underlying flow data is point-in-time, but each figi's **sector**, **industry**, and **ETF / ADR** classification uses the most recent labelling rather than the label in force at the time. META, for example, rolls up under `Communication Services` for its entire history even though it was classified as `Technology` in 2019. This keeps historical rollups comparable under a single consistent scheme as companies drift between sectors over time. ## `callput` semantics Every row is scoped to one callput bucket. `C` = calls only, `P` = puts only, `CP` = pre-computed combined view. Two rules apply on the `CP` row, split by metric family: size measures (`shares`, `premium`) are `C − P` (unsigned magnitudes, sign-flipped on puts to produce a directional bullish-minus-bearish net); greeks (`delta`, `gamma`, `vega`) are `C + P` (already signed by option type on the `C` and `P` rows, so a straight sum captures net book exposure). Rolling windows partition by `(group_key, callput)`; each callput row has its own independent trailing 21dma and 2y baseline, so `z(C) + z(P) ≠ z(CP)`. Filter to exactly one `callput` per query or triple-count. ## Warmup NULL conventions - `..._21dma` NULL when a `(group_key, callput)` has < 21 days of history in the trailing 21-row window. - `z_..._21dma` NULL when the 21dma series has < 63 non-null observations in the trailing 504-session (2-year) window, or the window's sample stddev is 0. - These are legitimate NULLs; do not fill. ## REST endpoints - `GET /v0/grouped_flow_signal/01_day`: ranged. Requires `group_type` = a single canonical `group_key` value (sector / industry name or one of the rollup literals `etf` / `adr` / `single_stock` / `all`; case-insensitive on input, fuzzy-suggestion on unknown values). Returns rows for a date range at that one key. - `GET /v0/grouped_flow_signal/01_day/snapshot`: single-day cross-sectional. All filters optional. `date` defaults to latest. Omit both `category` and `group_type` → every `group_key` with any flow on the day (up to ~168 rows on the default `callput = 'CP'`). `category` (snapshot-only: `industry` / `sector` / `rollup` / `all`) narrows to a whole family in one call. `group_type` (single canonical `group_key` value) narrows to one key. `category` and `group_type` are mutually exclusive. ## Fields | field_name | type | unit | grain | description | provenance | timing_lag | history_start | null_semantics | example | |---|---|---|---|---|---|---|---|---|---| | `date` | Date | trading day (UTC) | `(date, group_key, callput)` | Session date. US equity trading calendar. | exchange | T+1 | 2019-11-10 | never null | `2026-07-24` | | `group_key` | LowCardinality(String) | text | key | Canonical mixed-case sector or industry name (e.g. `Communication Services`, `Semiconductors`); or the literal `etf` / `adr` / `single_stock` / `all` for the rollup branches. Single key column — the same value you pass in the REST `group_type` query parameter. | derived | T+1 | 2019-11-10 | never null | `Technology` | | `callput` | LowCardinality(String) | enum | key | One of `C`, `P`, `CP`. See the `callput` note above. | derived | T+1 | 2019-11-10 | never null | `CP` | | `n_figi` | Nullable(Int64) | count | `(date, group_key, callput)` | Number of distinct FIGIs contributing to this group-day. Every row is emitted with raw nets AND rolling / z fields populated; the 21dma and 2y baseline include all days regardless of `n_figi`, so each industry's z is calibrated against its own historical breadth mix. Consumers filter on `n_figi` at query time when z-magnitude comparability across breadth matters. Sector, etf, adr, single_stock, and all branches always carry n_figi >> 10; only certain industries can dip below. | derived | T+1 | 2019-11-10 | never null in practice | `24` | | `net_i_shares` | Float64 | underlying-share equivalents | `(date, group_key, callput)` | Institutional net share-equivalent flow, summed across every figi in this grouping. On `CP` rows: `C − P` (bullish-conviction sign convention). Populated on every emitted row (even `n_figi < 10` days). | model | T+1 | 2019-11-10 | 0.0 when no qualifying institutional flow | `1_820_000.0` | | `net_r_shares` | Float64 | underlying-share equivalents | `(date, group_key, callput)` | Retail net share-equivalent flow, same CP-synthesis rule. | model | T+1 | 2019-11-10 | 0.0 when no qualifying retail flow | `412_000.0` | | `net_i_premium` | Float64 | USD | `(date, group_key, callput)` | Institutional net dollar premium flow. | model | T+1 | 2019-11-10 | 0.0 when no qualifying institutional flow | `182_000_000.0` | | `net_r_premium` | Float64 | USD | `(date, group_key, callput)` | Retail net dollar premium flow. | model | T+1 | 2019-11-10 | 0.0 when no qualifying retail flow | `31_000_000.0` | | `net_i_delta` | Float64 | USD | `(date, group_key, callput)` | Institutional net USD × delta flow. On `CP` rows: `C + P` (delta is signed by option type; put deltas are stored negative). Positive = net long-delta demand across the grouping. | model | T+1 | 2019-11-10 | 0.0 when no qualifying institutional flow | `1_250_000_000.0` | | `net_r_delta` | Float64 | USD | `(date, group_key, callput)` | Retail net USD × delta flow. | model | T+1 | 2019-11-10 | 0.0 when no qualifying retail flow | `-87_000_000.0` | | `net_i_gamma` | Float64 | USD × gamma | `(date, group_key, callput)` | Institutional net USD × gamma. On `CP` rows: `C + P`. | model | T+1 | 2019-11-10 | 0.0 when no qualifying institutional flow | `8_400_000.0` | | `net_r_gamma` | Float64 | USD × gamma | `(date, group_key, callput)` | Retail net USD × gamma. | model | T+1 | 2019-11-10 | 0.0 when no qualifying retail flow | `1_210_000.0` | | `net_i_vega` | Float64 | USD × vega | `(date, group_key, callput)` | Institutional net USD × vega. On `CP` rows: `C + P`. Positive = net long-vol demand across the grouping. | model | T+1 | 2019-11-10 | 0.0 when no qualifying institutional flow | `140_000_000.0` | | `net_r_vega` | Float64 | USD × vega | `(date, group_key, callput)` | Retail net USD × vega. | model | T+1 | 2019-11-10 | 0.0 when no qualifying retail flow | `-21_000_000.0` | | `net_*_21dma` | Float64 | (matches base metric) | `(date, group_key, callput)` | 21-trading-day rolling SUM of the corresponding raw net, scoped to this `(group_key, callput)`. Ten of these, one per raw metric. | derived | T+1 | 2019-11-10 | null when the trailing 21-day window has fewer than 21 non-null observations | (varies by metric) | | `z_net_*_21dma` | Float64 | z-score | `(date, group_key, callput)` | Z-score of the corresponding 21dma against its trailing 2-year (504-session) rolling mean and sample standard deviation, computed at query time. Rounded to 2 decimals on emit. Masscrest's canonical spike predicate for the group-level table is `z_net_i_delta_21dma > 2 AND callput = 'CP'` (or `> 3` for 3σ), same rule as the underlying-level table. Ten of these. | derived | T+1 | 2019-11-10 | null when the trailing 2y window is unwarmed or its sample stddev is 0 | (varies) | --- ### CP synthesis is pre-computed, not query-time Same rule as `underlying_flow_signals_01_day`: the `'CP'` rows are materialised at publish time, and each `(group_key, callput)` bucket has its own independent 21dma and 2y baseline trajectory. ### Group classification is snapshot-only Load-bearing, worth repeating: the sector / industry / etf / adr / single_stock labels used to route flow into each row come from each figi's latest-known `stock_mapping` phase (current phase for live names, last-live phase for delisted names) applied across every historical date. Do NOT use this table for PIT backtests where a stock's historical group membership matters; use `underlying_flow_signals_01_day` and roll up client-side. --- # px_01_day Daily close (unadjusted) for the covered US single-stock and ETF universe, keyed on FIGI. **Access:** this table is not directly queryable via the MCP or REST API. Split-adjusted price columns (`adj_close`, `adj_open`, `adj_volume`, `total_turnover`) are returned as LEFT-JOIN columns on `get_underlying_option_flows_daily` (MCP) and `GET /v0/underlying_option_flows/01_day` (REST). Use those endpoints for daily price context — passing `traded_underlying=` and `callput='CP'` returns one row per date with prices attached to the flow payload. For bulk historical pulls, the parquet flat-file surface is at `gs://prod-masscrest/px_data/v0/01_day/`. - Grain: `(date, figi, symbol)` [1] - History: 2019-11-10 → - Timing: T+1 (published between 05:00 and 09:00 UTC on the calendar day after the trading date — Friday's data lands Saturday) [1] The underlying storage carries `symbol` to preserve point-in-time ticker context, but every join to Masscrest surfaces should use `figi`. ## Fields | field_name | type | unit | grain | description | provenance | timing_lag | history_start | null_semantics | example | |---|---|---|---|---|---|---|---|---|---| | `date` | Date | trading day (UTC) | `(date, figi)` | Session date. US equity trading calendar. | exchange | T+1 | 2019-11-10 | never null | `2026-07-24` | | `figi` | String(12) | identifier | `(date, figi)` | OpenFIGI composite FIGI for the underlying. Stable across ticker changes; join key to every other Masscrest table. | exchange | n/a | 2019-11-10 | never null | `BBG000MM2P62` | | `symbol` | String | ticker | `(date, figi)` | Point-in-time listed ticker on `date`. Dotted dual-class form (`BRK.B`). Preserved for readability; **do not use as a join key** (the same ticker can be recycled across entities over time). | exchange | T+1 | 2019-11-10 | never null | `AAPL` | | `open` | Float64 | USD | `(date, figi)` | Session opening print (unadjusted, contemporaneous). No split adjustment applied. | exchange | T+1 | 2019-11-10 | null on non-trading days for the underlying (delistings, halts) | `223.85` | | `close` | Float64 | USD | `(date, figi)` | Session closing print (unadjusted, contemporaneous). No split adjustment applied. | exchange | T+1 | 2019-11-10 | null on non-trading days for the underlying | `224.31` | | `volume` | Float64 | shares | `(date, figi)` | Session share volume (unadjusted, contemporaneous). No split adjustment applied (a share count on a pre-split day is a real economic quantity that doesn't rescale post-split). | exchange | T+1 | 2019-11-10 | null on non-trading days | `52_308_400.0` | | `turnover` | Float64 | USD | `(date, figi)` | Dollar turnover of the underlying on `date` (`sum(price × volume)` across all intraday prints). Dollar amounts are unit-invariant across splits. | exchange | T+1 | 2019-11-10 | null on non-trading days | `11_724_390_012.0` | --- ### Unadjusted semantics Prices and volume in this table are unadjusted (contemporaneous). To compute split-adjusted series (safe returns across split events, share-count normalisation, historical strike comparisons), join to `split_factors` on `figi` where `date BETWEEN valid_from AND valid_to` and apply `cum_split_factor` per the reconstruction snippet in the flat-files section. The split is deliberate: it lets you reload `px_01_day` and `split_factors` independently, and gives you an explicit knob when you need to invert an adjustment. --- # px_10_min 10-minute OHLCV bars (unadjusted) for the covered US single-stock and ETF universe, keyed on FIGI. Same underlying universe and FIGI join key as `px_01_day`. **Access:** this table is not directly queryable via the MCP or REST API. Split-adjusted price columns (`adj_close`, `adj_open`, `adj_volume`, `total_turnover`) are returned as LEFT-JOIN columns on `get_underlying_option_flows_10min` (MCP), `intraday_flow_day` (MCP fat tool for a single symbol × single day), and `GET /v0/underlying_option_flows/10_min` (REST). Use those endpoints for intraday price context — the LEFT-join emits one row per `(date, ten_min_timeframe, figi)` with prices attached to the flow payload. For bulk historical pulls, the parquet flat-file surface is at `gs://prod-masscrest/px_data/v0/10_min/`. - Grain: `(date, ten_min_timeframe, figi, symbol)` - History: 2019-11-10 → - Timing: T+1 (published between 05:00 and 09:00 UTC on the calendar day after the trading date — Friday's data lands Saturday) ### Bucket boundary convention `ten_min_timeframe` is the end of the 10-minute bucket, in New York wall-clock time stored as a naive `DateTime`. Bucket covers `(ten_min_timeframe − 10 min, ten_min_timeframe]`. First RTH bucket ends at `09:40:00`, last at `16:00:00` (39 buckets per full session). On-boundary 1-minute bars (e.g. `10:00:00` close) land in the closing bucket, not the opening bucket of the next. ### Coverage Regular trading hours only (09:30–16:00 ET). No pre-market, no after-hours. Halted sessions produce no rows for the halted intervals. ## Fields | field_name | type | unit | grain | description | provenance | timing_lag | history_start | null_semantics | example | |---|---|---|---|---|---|---|---|---|---| | `ten_min_timeframe` | DateTime | NY wall-clock, bucket close | `(date, ten_min_timeframe, figi)` | End timestamp of the 10-min bucket (naive NY-time). Bucket covers `(ten_min_timeframe − 10 min, ten_min_timeframe]`. | exchange | T+1 | 2019-11-10 | never null | `2026-07-24 09:40:00` | | `date` | Date | trading day (UTC) | `(date, ten_min_timeframe, figi)` | Session date. US equity trading calendar. | exchange | T+1 | 2019-11-10 | never null | `2026-07-24` | | `figi` | String(12) | identifier | `(date, ten_min_timeframe, figi)` | OpenFIGI composite FIGI for the underlying. Stable across ticker changes. | exchange | n/a | 2019-11-10 | never null | `BBG000MM2P62` | | `symbol` | String | ticker | `(date, ten_min_timeframe, figi)` | Point-in-time listed ticker on `date`. Dotted dual-class form. Preserved for readability; do not use as a join key. | exchange | T+1 | 2019-11-10 | never null | `AAPL` | | `open` | Float64 | USD | `(date, ten_min_timeframe, figi)` | Unadjusted price of the first 1-minute bar in the bucket. Note: the source 1-minute feed carries `open` and `close` only (no high/low), so bar-internal high/low is not available at this grain. | exchange | T+1 | 2019-11-10 | null when the underlying has no trading activity in the bucket | `223.90` | | `close` | Float64 | USD | `(date, ten_min_timeframe, figi)` | Unadjusted price of the last 1-minute bar in the bucket. | exchange | T+1 | 2019-11-10 | null when the underlying has no trading activity in the bucket | `224.02` | | `volume` | Float64 | shares | `(date, ten_min_timeframe, figi)` | Underlying share volume within the bucket. Unadjusted (contemporaneous). | exchange | T+1 | 2019-11-10 | null when the underlying has no trading activity | `1_204_812.0` | | `turnover` | Float64 | USD | `(date, ten_min_timeframe, figi)` | Dollar turnover of the underlying within the bucket. Dollar amounts are unit-invariant across splits. | exchange | T+1 | 2019-11-10 | null when the underlying has no trading activity | `270_881_400.0` | --- ### Unadjusted semantics Prices and volume in this table are unadjusted (contemporaneous). To compute split-adjusted series, join to `split_factors` on `figi` where `date BETWEEN valid_from AND valid_to` and apply `cum_split_factor` per the reconstruction snippet in the flat-files section. The split is deliberate: it lets you reload `px_10_min` and `split_factors` independently, and gives you an explicit knob when you need to invert an adjustment. ### Ticker-renamed FIGIs A very small number of FIGIs carry rows under two different `symbol` values within a single day (the point-in-time symbol at the moment of the trade). If you aggregate over a universe by `figi` alone, use `sum(turnover)` for turnover and `argMax(close, turnover)` for a representative close per `(date, ten_min_timeframe, figi)`. Don't collapse via `any(...)` or you'll pick a stale legacy row. --- # stock_mapping Point-in-time (PIT) reference table for every underlying Masscrest tracks. Maps FIGI to listed ticker, company name, sector / industry, ETF and ADR flags, and the activity window during which that (ticker, FIGI) pair was live. Historical ticker changes, dual-class shares, and shell-reuse events are all encoded as separate phase rows. - Grain: `(figi, traded_underlying, phase_start)`, one row per contiguous period during which a FIGI traded under a given ticker. - History: all open phases plus historical phases as far back as the OpenFIGI record supports (many phases start `1900-01-01` as a "no constraint" sentinel). - Timing: reference table, rebuilt in full each trading day. Re-sync daily to pick up new phases and any corrections. - Typical use: join to any flow / price table on `figi` with `date BETWEEN phase_start AND phase_end` to attach a point-in-time ticker, sector, name, ETF flag, etc. ## Known quirks - For ETFs (`isEtf='true'`, which includes ETCs and ETPs), `sector` and `industry` describe the sponsor legal entity, not the fund's underlying exposure. All BlackRock / iShares sector ETFs show `sector='Financial Services'` because BlackRock is a financial-services firm. Filter `isEtf='true'` out before aggregating flow by sector, otherwise `Financial Services` gross flow inflates by 85%+. - For ADRs (`isAdr='true'`), `sector` and `industry` describe the underlying foreign company's business. HDB is `Financial Services` (HDFC Bank), BABA is `Consumer Cyclical` (Alibaba). Usually the label you want. Filter on `isAdr` if you need to separate US-domiciled sector totals from foreign ADRs. - The served rollup `grouped_flow_signal_01_day` already excludes ETFs and ADRs from its sector and industry `group_key`s. ADRs have their own `adr` `group_key` and ETFs their own `etf`. This quirk only matters when joining `stock_mapping` directly. ### Ticker conventions - Equity tickers use the dotted dual-class convention (`BRK.B`, `RDS.A`), matching every other Masscrest surface. When OPRA options data references the same underlying via its no-dot OCC root (`BRKB`), the equity join still uses the dotted form. Contract-grain `option_flows_*` tables carry `traded_symbol` (OCC root) for options joins and `traded_underlying` (dotted equity) for FIGI / equity joins. - Open phases use `phase_end = 2099-12-31` as the sentinel. Closed phases (renames, delistings, ticker recycles) carry a real end date. - To fetch "all history for whatever is called META today", resolve today's ticker for a FIGI in one query: `SELECT traded_underlying FROM stock_mapping WHERE figi = 'BBG000NDYB67' AND phase_end = '2099-12-31'`, then filter historical phases by that FIGI. ### Multi-phase tickers ~1% of tickers have multiple `stock_mapping` rows across time — one row per FIGI-era — from corporate reorgs (ISIN flip, e.g. XOM's 2026-07-07 Exxon → ExxonMobil Holdings restructure) or ticker recycles after a delisting (PARA was Paramount, then Banzai after 2026-08-07 under a different FIGI). - **Current era only**: `WHERE traded_underlying='X' AND phase_end='2099-12-31'` - **Full history across eras**: `WHERE traded_underlying='X'`, resolve joins with `date BETWEEN phase_start AND phase_end` - **Isolate one era**: filter by `figi` rather than `traded_underlying` Flow, price, and split tables are already attributed to the FIGI authoritative on each row's date. ## Fields | field_name | type | unit | grain | description | provenance | timing_lag | history_start | null_semantics | example | |---|---|---|---|---|---|---|---|---|---| | `traded_underlying` | String | ticker | `(figi, traded_underlying, phase_start)` | Point-in-time listed ticker during `[phase_start, phase_end]`. Dotted dual-class form (`BRK.B`). | exchange | n/a | 1900-01-01 (sentinel) | never null | `FB` | | `figi` | String(12) | identifier | `(figi, traded_underlying, phase_start)` | OpenFIGI composite FIGI for the underlying entity. Stable across ticker changes. | exchange | n/a | n/a | never null | `BBG000NDYB67` | | `phase_start` | Date | boundary | `(figi, traded_underlying, phase_start)` | First trading date this (ticker, figi) pair was active. `1900-01-01` is a "no constraint" sentinel for entities with unknown pre-history. | exchange | n/a | n/a | never null | `2012-05-18` | | `phase_end` | Date | boundary | `(figi, traded_underlying, phase_start)` | Last trading date this (ticker, figi) pair was active. `2099-12-31` sentinel means the phase is currently open (still trading under this ticker today). | exchange | n/a | n/a | never null | `2022-06-08` | | `companyName` | String | name | `(figi, traded_underlying, phase_start)` | Registered legal / operating name for the entity during this phase. | exchange | daily refresh | n/a | null when no vendor record is available for the phase | `Meta Platforms, Inc.` | | `sector` | String | sector | `(figi, traded_underlying, phase_start)` | Sector classification for the entity during this phase. Investor-oriented 11-sector taxonomy (companies grouped by demand-cycle and macro exposure, not by production process). | exchange | daily refresh | n/a | null when unresolved | `Technology` | | `industry` | String | industry | `(figi, traded_underlying, phase_start)` | Industry classification for the entity during this phase. Investor-oriented 154-industry taxonomy nested under the 11 sectors. | exchange | daily refresh | n/a | null when unresolved | `Software - Application` | | `isin` | String(12) | identifier | `(figi, traded_underlying, phase_start)` | ISIN for the entity. **Not** a unique join key: roughly 200 ISINs map to two active FIGIs simultaneously (the primary listing and the US OTC F-share composite of the same foreign entity). Join on `figi`, not `isin`. | exchange | daily refresh | n/a | null when the vendor lacks an ISIN for the phase | `US30303M1027` | | `isEtf` | String | flag (`'true'` / `'false'`) | `(figi, traded_underlying, phase_start)` | Whether the underlying is an ETF or ETP. String-typed for compatibility with the customer-facing endpoints. | exchange | daily refresh | n/a | `'false'` when unresolved (defaults to non-ETF) | `false` | | `isAdr` | String | flag (`'true'` / `'false'`) | `(figi, traded_underlying, phase_start)` | Whether the underlying is an American Depositary Receipt. | exchange | daily refresh | n/a | `'false'` when unresolved | `false` | | `isActivelyTrading` | String | flag (`'true'` / `'false'`) | `(figi, traded_underlying, phase_start)` | Whether the entity is actively trading today. Falls back to `'true'` when the phase is still open (`phase_end = 2099-12-31`) and the vendor lacks an explicit record, `'false'` otherwise. | exchange | daily refresh | n/a | never null (default fallback based on `phase_end`) | `true` | --- # contract_id_mapping Contract-level identity table. Maps every OPRA contract Masscrest has seen to a stable Masscrest-built `contract_id` that survives corporate actions (splits, reverse splits, M&A, deliverable adjustments), plus the point-in-time `adj_shares_deliverable` for each corporate-action phase. Consume it as a separate table so raw flow reads don't pay for mapping columns on every row, and per-contract analytics can bulk-join on `(root_symbol, expiration, callput, strike)`. - Grain: `(contract_id, phase_start, phase_end)` — one row per contract phase between corporate-action epochs (non-overlapping intervals covering the contract's lifetime). - History: full history for every OPRA contract Masscrest tracks (~57M rows). - Timing: reference table, rewritten in full each trading day (post-flow-completion). Re-sync daily to pick up new expirations, new adjustment memos, or deliverable corrections. - Typical use: (a) resolve OPRA-symbol prints back to a stable `contract_id` for a longitudinal per-contract PnL time series that survives a split; (b) attach `adj_shares_deliverable` for the correct phase when re-computing dollar-notional flows from a raw print; (c) join to a client's own OCC-symbol positions to attribute Masscrest flow to a specific historical contract lineage. ### When you actually need this table Most consumers never join here. The customer-facing REST + MCP `option_flows_*` responses already carry both `contract_id` and `adj_shares_deliverable` on every row, resolved server-side. You need `contract_id_mapping` in two scenarios: 1. **Bulk parquet reads of `option_flows_*` from flat files.** The base flat-file tables don't carry `figi` / `contract_id` directly (those live here, on this reference table). If you consume the raw parquet rather than the REST API, join `contract_id_mapping` on `(traded_symbol, expirationdate, callput, traded_strikeprice)` with a PIT clamp on `date BETWEEN phase_start AND phase_end`. 2. **Aligning your own OPRA-symbol books to Masscrest flow.** Given an OCC symbol at a specific date, look up the `contract_id` for that phase, then filter Masscrest flow on that `contract_id` to get the full history of that economic contract even across a corporate-action rename. ### Corporate-action phases (`phase_start` / `phase_end`) A single economic contract can have multiple rows here — one per corporate-action epoch — because a 2-for-1 split, reverse split, or M&A distribution changes the deliverable (`adj_shares_deliverable` moves from 100 to 200 in a 2-for-1, or to a fractional cash+share basket in an M&A). Each row's `phase_start` / `phase_end` interval bounds the phase during which that `adj_shares_deliverable` applied. The intervals are non-overlapping and cover the contract's full active lifetime. - **No adjustment ever**: single row spanning the contract's full lifetime with `adj_shares_deliverable = 100` (standard OCC equity option). - **Post-split contract**: multiple rows, each with the split-adjusted deliverable in force during that phase. `adj_shares_deliverable = 100 × cum_split_factor` (the correction the OCC memo declared for that phase). - **Post-M&A basket**: rows carrying the OCC-declared per-contract share count for the adjusted deliverable, which can be fractional. ### Relation to `split_history` `split_history` is the **underlying**-level split-adjustment factor (per FIGI, per phase). `contract_id_mapping` carries the **contract**-level deliverable (per contract, per phase). The two agree by design on reverse splits: `adj_shares_deliverable` on a phase after a 1-for-2 reverse split is `100 × cumulative_split_factor = 50`. Forward splits do not reduce the deliverable — `adj_shares_deliverable` stays at `100` and the strike adjusts instead. The two diverge on M&A distributions where the OCC declares a bespoke share + cash basket that isn't a clean multiple of the equity split. ## Fields | field_name | type | unit | grain | description | provenance | timing_lag | history_start | null_semantics | example | |---|---|---|---|---|---|---|---|---|---| | `contract_id` | String | identifier | `(contract_id, phase_start, phase_end)` | Masscrest-built stable contract identifier. Deterministic string built from `(root_symbol, expiration, callput, root-strike × 1000, corporate-action epoch tag)`; the trailing `E` epoch tag disambiguates recycled OPRA symbols that share expiry / strike across pre- and post-action cohorts. Same value follows the economic contract across corporate actions. | derived | daily refresh | n/a | never null | `AAPL 260815C00215000_E00000000` | | `traded_symbol` | String | ticker | `(contract_id, phase_start, phase_end)` | OCC OPRA root of the option (no-dot form: `BRKB`, not `BRK.B`). Post-corporate-action, the OCC root can change (numerated forms like `AAPL1`); this column echoes the OCC-declared root for the phase. | exchange | daily refresh | n/a | never null | `AAPL` | | `traded_underlying` | String | ticker | `(contract_id, phase_start, phase_end)` | Point-in-time listed equity ticker for the deliverable underlying during the phase. Dotted dual-class form (`BRK.B`). Resolved via join to `stock_mapping` on `root_symbol`. | derived | daily refresh | n/a | never null | `AAPL` | | `root_symbol` | String | ticker | `(contract_id, phase_start, phase_end)` | Canonical root ticker of the deliverable underlying, stable across the contract's corporate-action lineage. Used as the join key into `stock_mapping` to recover the point-in-time listed `traded_underlying`. | derived | daily refresh | n/a | never null | `AAPL` | | `root_strikeprice` | Float64 | USD | `(contract_id, phase_start, phase_end)` | Original listed strike at contract inception, stable across the lineage. `traded_strikeprice` is the strike adjusted for the phase; `root_strikeprice` is the strike before any corporate-action adjustment. On standard non-adjusted contracts the two are equal. | derived | daily refresh | n/a | never null | `215.00` | | `expirationdate` | Date | boundary | `(contract_id, phase_start, phase_end)` | Contract expiration date (US equity calendar). | exchange | n/a | n/a | never null | `2026-08-15` | | `callput` | String(1) | option type | `(contract_id, phase_start, phase_end)` | `'C'` (call) or `'P'` (put). | exchange | n/a | n/a | never null | `C` | | `traded_strikeprice` | Float64 | USD | `(contract_id, phase_start, phase_end)` | Contract strike as OCC-declared for the phase. Post-split-adjusted contracts show the adjusted strike; pre-split contracts show the raw strike. | exchange | n/a | n/a | never null | `215.00` | | `adj_shares_deliverable` | Float64 | shares per contract | `(contract_id, phase_start, phase_end)` | Shares conversion adjustment for the phase. Standard equity option delivers 100 shares → `100.0`. Post-corporate-action phases carry the OCC-declared per-contract share count (fractional, cash+share baskets, etc.). Post-split standard contracts equal `100 × cumulative_split_factor`. | exchange | daily refresh | n/a | never null | `100.0` | | `cumulative_split_factor` | Float64 | multiplier | `(contract_id, phase_start, phase_end)` | Cumulative split factor applied to the underlying over the contract's lineage from inception through the end of this phase. `1.0` on contracts that have not seen a split. On reverse-split-only mechanics, `adj_shares_deliverable = 100 × LEAST(cumulative_split_factor, 1.0)` — forward splits leave the deliverable at 100. | derived | daily refresh | n/a | never null | `1.0` | | `phase_start` | Date | boundary (inclusive) | `(contract_id, phase_start, phase_end)` | First trading date this `adj_shares_deliverable` applied to. Inclusive. `1970-01-01` sentinel on the contract's initial phase when the exact listing date pre-dates coverage. | derived | daily refresh | n/a | never null | `1970-01-01` | | `phase_end` | Date | boundary (inclusive) | `(contract_id, phase_start, phase_end)` | Last trading date this `adj_shares_deliverable` applied to. Inclusive. On the final (or only) phase of a contract this equals the contract's `expirationdate`; earlier phases end on the trading day before a corporate-action takes effect. | derived | daily refresh | n/a | never null | `2024-05-17` | | `is_index` | Bool | flag | `(contract_id, phase_start, phase_end)` | Whether the underlying is a cash-settled index. Filtered out at export — always `false` on the customer surface. | exchange | n/a | n/a | never null | `false` | ### Numerated OCC symbols A small number of contracts (~1.5k out of ~57M rows) carry numerated OPRA roots (e.g. `WOLF1`, `XOM2`, `GE1`, `MSTX1`) minted by the OCC after a corporate action to disambiguate the pre-action and post-action deliverable cohorts. These are documented as a known limitation: `contract_id` may be null and IV / greeks may be miscomputed on the small subset of trades that pre-date the required OCC memo mapping. Aggregate signals are clean; per-contract analytics on those specific numerated symbols should be treated cautiously. --- # split_factors Cumulative split-adjustment factors per FIGI, structured as a slowly-changing dimension (SCD2). Delivered so clients can back-adjust unadjusted historical prices (see `px_01_day` / `px_10_min`), or invert a Masscrest split-adjusted price back to the raw contemporaneous print for a specific period. - Grain: one row per continuous split-factor phase per `figi`, `(figi, valid_from, valid_to)`, non-overlapping intervals covering each FIGI's timeline. `date BETWEEN valid_from AND valid_to` matches exactly one row per `(figi, date)`. - History: full history for each covered FIGI, back to the earliest known split. Pre-split-history phases use `valid_from = 1900-01-01` as a "no earlier bound" sentinel. - Timing: reference table, rebuilt in full each trading day whenever a new split lands. Re-sync daily. - Coverage: currently ~30k FIGIs. Historical splits back to 1962 and forward-looking upcoming ex-dates are both covered. ### How to use the factor `cum_split_factor` on a date X means: to convert a raw close on date X into a current-shares-equivalent price, divide by this factor. It equals `1.0` on and after the most recent split; `> 1` before a forward split (e.g. `4.0` before a 4-for-1); `< 1` before a reverse split. On or after the latest split within an entity's active period, the factor is `1.0`. ## Fields | field_name | type | unit | grain | description | provenance | timing_lag | history_start | null_semantics | example | |---|---|---|---|---|---|---|---|---|---| | `figi` | String(12) | identifier | `(figi, valid_from, valid_to)` | OpenFIGI composite FIGI for the underlying entity. Join key to `stock_mapping`, `px_01_day`, `px_10_min`. | exchange | n/a | n/a | never null | `BBG000MM2P62` | | `valid_from` | Date | boundary (inclusive) | `(figi, valid_from, valid_to)` | Start date this cumulative factor applies to. `1900-01-01` for the earliest period. | derived | n/a | 1900-01-01 (sentinel) | never null | `2019-08-30` | | `valid_to` | Date | boundary (inclusive) | `(figi, valid_from, valid_to)` | Last date this cumulative factor applies to. Open-ended periods (no future split yet) use the phase's `phase_end`; entities in a currently-active phase carry `2099-12-31`. | derived | n/a | n/a | never null | `2020-08-30` | | `cum_split_factor` | Float64 | ratio | `(figi, valid_from, valid_to)` | Cumulative product of all splits from `valid_from + 1 day` forward within the entity's active phase. Divide a raw close on any date in `[valid_from, valid_to]` by this factor to get a current-shares-equivalent price. `1.0` for the most recent period (no pending split). | derived | n/a | n/a | **null** for extreme reverse-split cases where the true cumulative factor underflows Float64 precision (~1e-15). Treat null as "no valid split adjustment available for this period" and either skip or fail loudly; never coerce to `0.0`. | `4.0` | --- ### Precision Cumulative factors are stored at 12 decimal places to preserve heavy reverse-split stacks (e.g. UVXY has thirteen reverse splits; its true cum factor at `1900-01-01` is ~6.7e-11). Do NOT round to 6 decimals when consuming; that historically truncated ~200 heavy-reverse-splitter FIGIs to `0.0` and silently zeroed downstream price adjustments. For the handful of penny-stock cases where the true cumulative product underflows Float64 precision entirely (e.g. stacked 1-for-50000 × 1-for-19000 × 1-for-10000 ≈ 3.5e-20), we emit `NULL` rather than `0.0` so consumers can detect and skip rather than silently multiplying prices by zero. ### No-split entities FIGIs that never split carry a single row spanning the entity's full active phase with `cum_split_factor = 1.0`. Same shape as those that split; no special-case handling needed. --- # REST API Every endpoint the Masscrest API exposes, with query URL, Python sample, and JSON response inline. ## Stock Mapping ```http GET /v0/stock_mapping ``` Ticker → FIGI / sector / industry reference table (~42k rows). ### Description Retrieve the `stock_mapping` reference table, which maps every ticker Masscrest tracks to its identifiers and company metadata (FIGI, ISIN, sector, industry, ETF/ADR/fund flags) and includes historical ticker changes via `phase_start` / `phase_end` windows. Filters are optional and combine with `AND` for exact match. The full table is ~42,000 rows. **Default `limit` is 10 rows** to keep interactive docs responsive; **pass `limit=0` for the full ~42k-row table**. When the default limit clipped the rowset, the response carries `X-Truncated: true` (absent when the full result fit). ### Response headers (shared with every `/v0/*` list endpoint) - `X-Row-Count: ` — number of rows in the response, regardless of `format`. - `X-Truncated: true` — `stock_mapping` only: default `limit=10` clipped the rowset. ### Error responses - `400 Bad Request` — unknown query parameter. The response body enumerates the accepted names for this endpoint. ### Use cases - Mapping point-in-time tickers to stable FIGI identifiers - Sector / industry classification - Identifying ETFs, ADRs, and funds - Tracking ticker-change history ### Parameters | Parameter | Type | Required | Description | | --- | --- | --- | --- | | `terminal_symbol` | string | No | Current / most recent ticker for the company. | | `figi` | string | No | Composite FIGI identifier. | | `isin` | string | No | ISIN code. | | `sector` | string | No | Sector classification. | | `industry` | string | No | Industry classification. | | `limit` | integer | No | Maximum number of rows to return. `0` means no cap (full table). Defaults to `10`. | | `format` | enum: json, parquet | No | Response format. `json` returns a JSON array (default). `parquet` streams a binary Parquet file (~5–10× smaller, faster to parse). | ### Sample response ```json [ { "symbol": "FB", "figi": "BBG000MM2P62", "phase_start": "2012-05-18", "phase_end": "2022-06-09", "terminal_symbol": "META", "companyName": "Meta Platforms, Inc.", "sector": "Communication Services", "industry": "Internet Content & Information", "isin": "US30303M1027", "country": "US", "isEtf": "false", "isAdr": "false", "isActivelyTrading": "true" }, { "symbol": "META", "figi": "BBG000MM2P62", "phase_start": "2022-06-10", "phase_end": "2099-12-31", "terminal_symbol": "META", "companyName": "Meta Platforms, Inc.", "sector": "Communication Services", "industry": "Internet Content & Information", "isin": "US30303M1027", "country": "US", "isEtf": "false", "isAdr": "false", "isActivelyTrading": "true" } ] ``` --- ## Option Flows: Daily ```http GET /v0/option_flows/01_day ``` Per-underlying daily aggregated option flow joined with the split-adjusted underlying price. ### Description Retrieve daily option-flow rows for a single underlying, aggregated per `(date, figi)`: one row per trading day. Every row combines all contracts on that underlying (after any optional filters) into a single set of signed retail / institutional / dealer aggregates, joined to the split-adjusted underlying price on the same day. Exactly one of `figi` or `traded_underlying` must be supplied. If `traded_underlying` is used, it is resolved server-side to the corresponding FIGI. Supplying both, or neither, returns HTTP 400. An unknown `traded_underlying` returns HTTP 404, not an empty `[]`. Trial tiers cap daily history at 730 days and count each distinct FIGI touched per UTC day against a 25-figi cap. Paid tiers have no per-figi cap and honour the requested history window. See [Trial vs. paid tier](/documentation#trial-vs-paid-tier) for the full policy. ### Optional filters, applied BEFORE aggregation `callput`, `dte_min` / `dte_max`, and `abs_delta_min` / `abs_delta_max` narrow the contract set inside the underlying-level GROUP BY. The aggregate reflects only contracts matching the filter. `dte_min <= dte_max` and `abs_delta_min <= abs_delta_max` are cross-validated (400 on mismatch). ### Bucketed axes (optional) Three optional booleans add fixed-cardinality axes to the GROUP BY. Each defaults to `false`; the response is byte-identical to the pre-bucket behaviour when none are set. - `by_callput=true` — splits each `(date, figi)` row into a call row and a put row. Adds `callput` (`"C"` / `"P"`) to the row. - `by_expiry_bucket=true` — adds a 4-bucket days-to-expiry axis. Bucket labels: `"0-7d"`, `"8-30d"`, `"31-90d"`, `"91+d"`. DTE is computed on the source contract as `expirationdate − date` (calendar days). Rows are returned in semantic DTE order (short-dated first), not lexicographic. - `by_moneyness_bucket=true` — adds a 5-bucket `|delta|` axis. Bucket labels: `"0-0.2"` (OTM), `"0.2-0.4"`, `"0.4-0.6"` (near-the-money), `"0.6-0.8"`, `"0.8-1.0"` (deep ITM). Axes compose: passing all three yields at most `2 × 4 × 5 = 40` rows per `(date, figi)`. Response headers add `X-Bucket-Axes: ` listing the axes that ended up in the GROUP BY (in add order). Bucket columns appear on the row only when their axis is on; the flow-aggregate columns (`net_r_delta` / `net_i_premium` / ...) are unchanged in shape but reflect only the contracts falling into that bucket. Bucket axes are additive to the pre-aggregation filters above, so `callput=C&by_expiry_bucket=true` returns 4 rows per `(date, figi)` — all calls, split across the four DTE buckets. ### Row-count cap Projected row count is deterministic from the request: `days × (1 for daily) × 2^by_callput × 4^by_expiry_bucket × 5^by_moneyness_bucket`. If the projection exceeds **25,000** rows **and** `format=json`, the request returns HTTP 400 with a structured body: ```json { "error": "row_cap_exceeded", "projected_rows": 47000, "threshold": 25000, "hint": "Add format=parquet" } ``` Set `format=parquet` to bypass the cap; Parquet is ~5–10× smaller on the wire and streams straight from the router. Daily worst case (all three axes, one year) sits at 40 × 252 ≈ 10k rows so the cap only bites on multi-year full-cube pulls. ### Response headers & errors Every `/v0/*` list response carries `X-Row-Count: `. When any bucket axis is active, the response also carries `X-Bucket-Axes: ` (subset of `callput,expiry_bucket,moneyness_bucket` in add order). Unknown query parameters return `400 Bad Request` with the list of accepted names in the body. ### Aggregation All fields are summed raw across the contracts passing the filters. No call/put sign flip is applied. For a call-vs-put directional view, pass `by_callput=true` (or query `callput=C` and `callput=P` separately and subtract client-side). - `*_shares`: `sum(qty × adj_shares_deliverable)`. Units are shares (contract count × current OPRA deliverable). - `*_premium`: `sum(premium)`. Dollar notional. - `*_delta` / `*_gamma` / `*_vega`: summed raw. Delta is naturally signed by option type on the source contract (calls +, puts −), so `net_i_delta` already carries directional lean. Prefixes are `_r_` (retail), `_i_` (institutional), `_m_` (dealer). The floor bucket is not exposed via this endpoint. ### Use cases - Building daily flow-based signals per underlying - Studying institutional vs retail vs dealer flow around events - Reconstructing filtered net delta / gamma / vega exposure over time ### Parameters | Parameter | Type | Required | Description | | --- | --- | --- | --- | | `figi` | string | No | Composite FIGI of the underlying. Mutually exclusive with `traded_underlying`. | | `traded_underlying` | string | No | Stock ticker; resolved to FIGI via the `dict_symbol_to_figi` dictionary. Mutually exclusive with `figi`. | | `callput` | enum: C, P | No | Restrict the contract set to Calls (`C`) or Puts (`P`) BEFORE aggregation. Case-insensitive (`c`, `C`, `p`, `P` all accepted). `CP` is rejected here — this endpoint is contract-grain and every contract is either a call or a put; for a summed C+P view use `/v0/underlying_option_flows/*` with `callput=CP`. | | `dte_min` | integer | No | Minimum days-to-expiration (inclusive). Applied per contract before aggregation. | | `dte_max` | integer | No | Maximum days-to-expiration (inclusive). Applied per contract before aggregation. | | `abs_delta_min` | number | No | Minimum `\|last_delta\|` in `[0.0, 1.0]`. Applied per contract before aggregation. | | `abs_delta_max` | number | No | Maximum `\|last_delta\|` in `[0.0, 1.0]`. Applied per contract before aggregation. | | `by_callput` | boolean | No | When `true`, splits each row into a call row (`callput="C"`) and a put row (`callput="P"`). Adds a `callput` column to the response. Default `false` (calls + puts summed together, no `callput` column). | | `by_expiry_bucket` | boolean | No | When `true`, adds a fixed 4-bucket days-to-expiry axis to the GROUP BY. Bucket labels: `"0-7d"`, `"8-30d"`, `"31-90d"`, `"91+d"`. Rows are returned in semantic DTE order (short-dated first), not lexicographic. Adds an `expiry_bucket` column to the response. Default `false`. | | `by_moneyness_bucket` | boolean | No | When `true`, adds a fixed 5-bucket `\|delta\|` axis to the GROUP BY. Bucket labels: `"0-0.2"` (OTM), `"0.2-0.4"`, `"0.4-0.6"` (near-the-money), `"0.6-0.8"`, `"0.8-1.0"` (deep ITM). Delta is signed by option type on the source contract, so the axis is on the absolute value — a 20-delta OTM call and a 20-delta OTM put both land in `"0-0.2"`. Adds a `moneyness_bucket` column to the response. Default `false`. | | `start_date` | string | No | Earliest trading day to include (inclusive, YYYY-MM-DD). Trial tiers are clamped to a 730-day history window. | | `end_date` | string | No | Latest trading day to include (inclusive, YYYY-MM-DD). | | `format` | enum: json, parquet | No | Response format. `json` returns a JSON array (default). `parquet` streams a binary Parquet file (~5–10× smaller). When any `by_*` axis pushes projected row count over 25,000, `json` returns HTTP 400 (`row_cap_exceeded`) and the caller must pass `parquet` to proceed. | ### Sample response ```json [ { "date": "2026-09-05", "figi": "BBG000BJK6K5", "traded_underlying": "CASY", "callput": "C", "expiry_bucket": "0-7d", "moneyness_bucket": "0.4-0.6", "buy_r_shares": 9120, "sell_r_shares": -8240, "net_r_shares": 880, "buy_r_premium": 128400, "sell_r_premium": -102400, "net_r_premium": 26000, "net_r_delta": 42800, "buy_i_shares": 12400, "sell_i_shares": -3200, "net_i_shares": 9200, "buy_i_premium": 2140000, "sell_i_premium": -420000, "net_i_premium": 1720000, "net_i_delta": 812600, "buy_m_shares": -21520, "sell_m_shares": 11440, "net_m_shares": -10080, "net_m_delta": -855400, "adj_open": 411.02, "adj_close": 412.18, "adj_volume": 428192, "total_turnover": 176500000 } // ... up to 39 more rows for 2026-09-05 across the (callput x expiry x moneyness) cube, // then the same shape repeated for 2026-09-04. ] ``` --- ## Option Flows: 10-minute ```http GET /v0/option_flows/10_min ``` Per-underlying 10-minute aggregated option flow joined with the intraday split-adjusted underlying price. ### Description Retrieve 10-minute-bucket option-flow rows for a single underlying, aggregated per `(date, ten_min_timeframe, figi)`: one row per 10-minute bucket. Every row combines all contracts on that underlying (after any optional filters) into a single set of signed retail / institutional / dealer aggregates, joined to the split-adjusted underlying price for the same bucket. Exactly one of `figi` or `traded_underlying` must be supplied. If `traded_underlying` is used, it is resolved server-side to the corresponding FIGI. Supplying both, or neither, returns HTTP 400. An unknown `traded_underlying` returns HTTP 404, not an empty `[]`. > **Trial tier: intraday is not available.** All 10-minute endpoints return HTTP 403 for trial accounts. Trials should use the daily variant `/v0/option_flows/01_day`. See [Trial vs. paid tier](/documentation#trial-vs-paid-tier). > **30-day window cap (paid tier).** Any single call returns at most 30 days of history. If `start_date` / `end_date` are omitted, the response covers the most recent 30 days. Requests spanning more than 30 days return HTTP 400. For multi-year intraday history, use the flat-files bucket `gs://prod-masscrest/option_flows/v0/10_min/`. For longer-horizon signals, use the daily variant `/v0/option_flows/01_day`. ### Optional filters, applied BEFORE aggregation `callput`, `dte_min` / `dte_max`, and `abs_delta_min` / `abs_delta_max` narrow the contract set inside the bucket-level GROUP BY. The aggregate reflects only contracts matching the filter. `dte_min <= dte_max` and `abs_delta_min <= abs_delta_max` are cross-validated (400 on mismatch). ### Bucketed axes (optional) Same three optional booleans as the daily variant — `by_callput`, `by_expiry_bucket`, `by_moneyness_bucket` — each adding a fixed-cardinality axis to the GROUP BY. Axes compose: passing all three yields at most `2 × 4 × 5 = 40` rows per `(date, ten_min_timeframe, figi)`. Bucket labels, ordering rules, and response-column shape match the daily variant; see `/v0/option_flows/01_day` for the full spec. Response header `X-Bucket-Axes` reports which axes ended up in the GROUP BY. ### Row-count cap Projected row count is `days × 39 × 2^by_callput × 4^by_expiry_bucket × 5^by_moneyness_bucket` (39 = full RTH 10-min buckets per session). If the projection exceeds **25,000** rows **and** `format=json`, the request returns HTTP 400 (`{"error": "row_cap_exceeded", ...}`) — pass `format=parquet` to bypass. The intraday cap bites earliest here: 15 days × all three axes ≈ 23k rows, so week-scale intraday full-cube pulls stay within JSON; multi-week ones need Parquet. ### Sign convention Same as the daily variant. See `/v0/option_flows/01_day` for the full sign / prefix / unit notes. ### Response headers & errors Every `/v0/*` list response carries `X-Row-Count: `. When any bucket axis is active, the response also carries `X-Bucket-Axes: ` (subset of `callput,expiry_bucket,moneyness_bucket` in add order). Unknown query parameters return `400 Bad Request` with the list of accepted names in the body. ### Use cases - Intraday flow surveillance around scheduled releases and open/close auctions - Filtered 10-minute delta / gamma / vega exposure per underlying ### Parameters | Parameter | Type | Required | Description | | --- | --- | --- | --- | | `figi` | string | No | Composite FIGI of the underlying. Mutually exclusive with `traded_underlying`. | | `traded_underlying` | string | No | Stock ticker; resolved to FIGI via the `dict_symbol_to_figi` dictionary. Mutually exclusive with `figi`. | | `callput` | enum: C, P | No | Restrict the contract set to Calls (`C`) or Puts (`P`) BEFORE aggregation. Case-insensitive (`c`, `C`, `p`, `P` all accepted). `CP` is rejected here — this endpoint is contract-grain and every contract is either a call or a put; for a summed C+P view use `/v0/underlying_option_flows/*` with `callput=CP`. | | `dte_min` | integer | No | Minimum days-to-expiration (inclusive). Applied per contract before aggregation. | | `dte_max` | integer | No | Maximum days-to-expiration (inclusive). Applied per contract before aggregation. | | `abs_delta_min` | number | No | Minimum `\|last_delta\|` in `[0.0, 1.0]`. Applied per contract before aggregation. | | `abs_delta_max` | number | No | Maximum `\|last_delta\|` in `[0.0, 1.0]`. Applied per contract before aggregation. | | `by_callput` | boolean | No | When `true`, splits each 10-min-bucket row into a call row (`callput="C"`) and a put row (`callput="P"`). Adds a `callput` column to the response. Default `false`. | | `by_expiry_bucket` | boolean | No | When `true`, adds a fixed 4-bucket days-to-expiry axis to the GROUP BY. Bucket labels: `"0-7d"`, `"8-30d"`, `"31-90d"`, `"91+d"`. Rows are returned in semantic DTE order (short-dated first), not lexicographic. Adds an `expiry_bucket` column to the response. Default `false`. | | `by_moneyness_bucket` | boolean | No | When `true`, adds a fixed 5-bucket `\|delta\|` axis to the GROUP BY. Bucket labels: `"0-0.2"` (OTM), `"0.2-0.4"`, `"0.4-0.6"` (near-the-money), `"0.6-0.8"`, `"0.8-1.0"` (deep ITM). Adds a `moneyness_bucket` column to the response. Default `false`. | | `start_date` | string | No | Earliest trading day (inclusive, YYYY-MM-DD). Combined with `end_date` must span at most 30 days; if both are omitted the response covers the most recent 30 days. | | `end_date` | string | No | Latest trading day (inclusive, YYYY-MM-DD). Combined with `start_date` must span at most 30 days. | | `format` | enum: json, parquet | No | Response format. `json` returns a JSON array (default). `parquet` streams a binary Parquet file (~5–10× smaller). When any `by_*` axis pushes projected row count over 25,000, `json` returns HTTP 400 (`row_cap_exceeded`) and the caller must pass `parquet` to proceed. The 25k threshold bites earliest here: 15 days × all three axes ≈ 23k rows. | ### Sample response ```json [ { "date": "2026-09-05", "ten_min_timeframe": "2026-09-05 09:40:00", "figi": "BBG000BJK6K5", "traded_underlying": "CASY", "callput": "C", "expiry_bucket": "0-7d", "moneyness_bucket": "0.4-0.6", "buy_r_shares": 1210, "sell_r_shares": -880, "net_r_shares": 330, "buy_r_premium": 18400, "sell_r_premium": -12100, "net_r_premium": 6300, "net_r_delta": 4820, "buy_i_shares": 2100, "sell_i_shares": -420, "net_i_shares": 1680, "net_i_delta": 168400, "net_m_delta": -173220, "adj_open": 411.02, "adj_close": 411.44, "adj_volume": 24800, "total_turnover": 10200000 } // ... up to 1,559 more rows across the (10-min bucket x callput x expiry x moneyness) cube ] ``` --- ## Underlying Option Flows: Daily ```http GET /v0/underlying_option_flows/01_day ``` Per-underlying × callput daily aggregated option flow (pre-aggregated), joined with adjusted underlying price. ### Description Retrieve daily underlying-level option-flow rows (pre-aggregated across every contract on the same `(date, figi, callput)`), joined to the PIT `stock_mapping` row for `(figi, date)`. Rows are already grouped by `(date, figi, callput)`, so unlike `/v0/option_flows/01_day` no contract-level filters are available. In exchange, cross-underlying scans (see the sibling `/snapshot` endpoint) are significantly cheaper. Provide at most one of `figi` or `traded_underlying`. If `traded_underlying` is used, it is resolved server-side to the corresponding FIGI. Supplying both returns HTTP 400. An unknown `traded_underlying` returns HTTP 404, not an empty `[]`. The underlying selector may be omitted entirely when at least one `stock_mapping` filter (`is_etf` / `is_adr` / `industry` / `sector`) is provided; the response then covers every figi matching the metadata predicate for the date range. > **Trial tier.** Must supply an explicit `figi` or `traded_underlying` — a broad `sector` / `industry`-only sweep returns HTTP 400 with the message `Trial tier requires explicit figi or underlying_ticker filter (up to 25 per UTC day). For broad cohort exploration use programmatic flat-files access.` Each distinct FIGI touched counts toward the 25-figi-per-UTC-day cap; the 26th distinct FIGI returns HTTP 403 with the touched list echoed back. Daily history clamped to 730 days. See [Trial vs. paid tier](/documentation#trial-vs-paid-tier). ### `callput` - `C`: calls-only row per bucket (direct scan). - `P`: puts-only row per bucket (direct scan). - `CP`: default; C and P summed at query time. One row per bucket with `callput = "CP"` in the output. Case-insensitive on input — `c`, `Cp`, `CP`, `p` all accepted; the emitted row always carries the canonical upper-case form. ### Response columns `date`, `figi`, `traded_underlying`, `callput`, `n_contracts`, `total_shares`, `last_underprice`, `atm_iv_30d`, the 45 flow aggregates (`buy/sell/net × shares/premium/delta/gamma/vega × r/i/m`), `adj_open` / `adj_close` / `adj_volume` / `total_turnover` from the matched underlying, and `industry` / `sector` / `isAdr` / `isEtf` from `stock_mapping` (ranged endpoints intentionally omit `companyName`). ### Response headers & errors Every `/v0/*` list response carries `X-Row-Count: `. Unknown query parameters return `400 Bad Request` with the list of accepted names in the body. ### Use cases - Longitudinal per-underlying flow with automatic C+P combination - Cross-sector or ETF-only flow scans without needing to enumerate figis upfront - Feeding call-vs-put ratio signals per underlying ### Parameters | Parameter | Type | Required | Description | | --- | --- | --- | --- | | `figi` | string | No | Composite FIGI of the underlying. Mutually exclusive with `traded_underlying`. Optional when a `stock_mapping` filter is supplied. | | `traded_underlying` | string | No | Stock ticker; resolved via the `dict_symbol_to_figi` dictionary. Mutually exclusive with `figi`. Optional when a `stock_mapping` filter is supplied. | | `callput` | enum: C, P, CP | No | `C`, `P`, or `CP` (default). `CP` sums the C and P rows at query time. | | `start_date` | string | No | Earliest trading day (inclusive, YYYY-MM-DD). Trial tiers are clamped to a 730-day window. | | `end_date` | string | No | Latest trading day (inclusive, YYYY-MM-DD). | | `is_etf` | boolean | No | Filter to ETFs (`true`) or non-ETFs (`false`). Applied to the joined `stock_mapping` row. | | `is_adr` | boolean | No | Filter to ADRs (`true`) or non-ADRs (`false`). | | `industry` | string | No | Filter on `stock_mapping.industry`. Case-insensitive on input; the emitted row carries the canonical mixed-case form. A value that doesn't match any known industry returns HTTP 400 with a fuzzy-match suggestion in the body (e.g. `"Unknown industry: 'semis'. Did you mean 'Semiconductors'?"`). | | `sector` | string | No | Filter on `stock_mapping.sector`. Case-insensitive on input; the emitted row carries the canonical mixed-case form. A value that doesn't match any known sector returns HTTP 400 with a fuzzy-match suggestion in the body (e.g. `"Unknown sector: 'technolgy'. Did you mean 'Technology'?"`). | | `format` | enum: json, parquet | No | `json` (default) or `parquet` (binary, ~5–10× smaller). | ### Sample response ```json [ { "date": "2026-07-14", "figi": "BBG000MM2P62", "traded_underlying": "META", "callput": "CP", "n_contracts": 2838, "total_shares": 4132100, "last_underprice": 661.08, "atm_iv_30d": 0.283, "net_i_premium": -5814246, "adj_open": 660.10, "adj_close": 661.08, "adj_volume": 15321000, "total_turnover": 10129000000 } ] ``` --- ## Underlying Option Flows: 10-minute ```http GET /v0/underlying_option_flows/10_min ``` Per-underlying × callput 10-minute aggregated option flow (pre-aggregated), joined with intraday adjusted price. ### Description Retrieve 10-minute-bucket underlying-level option-flow rows (pre-aggregated across every contract on the same `(date, ten_min_timeframe, figi, callput)`), joined to the PIT `stock_mapping` row for `(figi, date)`. Rows are already grouped by `(date, ten_min_timeframe, figi, callput)`; no contract-level filters are available on this endpoint. Provide at most one of `figi` or `traded_underlying`. If `traded_underlying` is used, it is resolved server-side to the corresponding FIGI. Supplying both returns HTTP 400. An unknown `traded_underlying` returns HTTP 404, not an empty `[]`. The underlying selector may be omitted entirely when at least one `stock_mapping` filter (`is_etf` / `is_adr` / `industry` / `sector`) is provided; the response then covers every figi matching the metadata predicate for the date range. > **Trial tier: intraday is not available.** All 10-minute endpoints return HTTP 403 for trial accounts. Trials should use the daily variant `/v0/underlying_option_flows/01_day`. See [Trial vs. paid tier](/documentation#trial-vs-paid-tier). > **30-day window cap (paid tier).** At most 30 days of history per call. If `start_date` / `end_date` are omitted, the response covers the most recent 30 days. Requests spanning more than 30 days return HTTP 400. For multi-year intraday history use the flat-files bucket `gs://prod-masscrest/option_flows/v0/10_min/`. ### `callput` Same semantics as the daily variant: `C`, `P`, or `CP` (default). `CP` sums C and P at query time. Case-insensitive on input. ### Response columns Standard flow-aggregate columns plus `industry` / `sector` / `isAdr` / `isEtf` from `stock_mapping` (ranged endpoints intentionally omit `companyName`). ### Response headers & errors Every `/v0/*` list response carries `X-Row-Count: `. Unknown query parameters return `400 Bad Request` with the list of accepted names in the body. ### Use cases - Intraday flow surveillance without per-contract filters - Sector-restricted intraday sweeps (e.g. all Semiconductors, 30 days) - Building high-frequency call-vs-put ratio signals per underlying ### Parameters | Parameter | Type | Required | Description | | --- | --- | --- | --- | | `figi` | string | No | Composite FIGI of the underlying. Mutually exclusive with `traded_underlying`. Optional when a `stock_mapping` filter is supplied. | | `traded_underlying` | string | No | Stock ticker; resolved via the `dict_symbol_to_figi` dictionary. Mutually exclusive with `figi`. Optional when a `stock_mapping` filter is supplied. | | `callput` | enum: C, P, CP | No | `C`, `P`, or `CP` (default). | | `start_date` | string | No | Earliest trading day (inclusive, YYYY-MM-DD). Combined with `end_date` must span at most 30 days. | | `end_date` | string | No | Latest trading day (inclusive, YYYY-MM-DD). | | `is_etf` | boolean | No | Filter to ETFs (`true`) or non-ETFs (`false`). Applied to the joined `stock_mapping` row. | | `is_adr` | boolean | No | Filter to ADRs (`true`) or non-ADRs (`false`). | | `industry` | string | No | Filter on `stock_mapping.industry`. Case-insensitive on input; the emitted row carries the canonical mixed-case form. A value that doesn't match any known industry returns HTTP 400 with a fuzzy-match suggestion in the body (e.g. `"Unknown industry: 'semis'. Did you mean 'Semiconductors'?"`). | | `sector` | string | No | Filter on `stock_mapping.sector`. Case-insensitive on input; the emitted row carries the canonical mixed-case form. A value that doesn't match any known sector returns HTTP 400 with a fuzzy-match suggestion in the body (e.g. `"Unknown sector: 'technolgy'. Did you mean 'Technology'?"`). | | `format` | enum: json, parquet | No | `json` (default) or `parquet` (binary, ~5–10× smaller). | ### Sample response ```json [ { "date": "2026-07-21", "ten_min_timeframe": "2026-07-21 09:40:00", "figi": "BBG000MM2P62", "traded_underlying": "META", "callput": "CP", "n_contracts": 411, "total_shares": 62100, "last_underprice": 665.10, "atm_iv_30d": 0.281, "net_i_premium": -132400, "adj_open": 665.10, "adj_close": 664.87, "adj_volume": 3200000, "total_turnover": 1123840120 } ] ``` --- ## Underlying Option Flows: Daily Snapshot ```http GET /v0/underlying_option_flows/01_day/snapshot ``` Single-day cross-sectional snapshot of pre-aggregated option flow for every underlying, joined with adjusted price. ### Description Cross-sectional snapshot: returns one row per `(figi, callput)` (every underlying with flow on the requested `date`, C and P kept as separate rows), joined to the split-adjusted underlying close AND to the PIT `stock_mapping` row for `(figi, date)`. No underlying selector is required; the response covers the full universe. Optional `stock_mapping`-backed filters (`is_etf` / `is_adr` / `industry` / `sector`) narrow the returned figis to those matching the predicate. > **Trial tier: snapshot endpoints are not available.** This endpoint returns HTTP 403 for trial accounts (same treatment as intraday). A wide snapshot would charge every returned FIGI against the 25-figi-per-UTC-day cap in a single call, so the snapshot surface is blocked outright on trial. Use the ranged `/v0/underlying_option_flows/01_day` endpoint with an explicit `figi` / `traded_underlying` filter for per-underlying pulls, or `/v0/grouped_flow_signal/01_day/snapshot` (not figi-capped) for broad cross-sectional exploration. See [Trial vs. paid tier](/documentation#trial-vs-paid-tier). Typical response is ~10,000 rows per trading day (roughly 5,000 underlyings × C+P). Prefer `format=parquet` for anything larger than a browser preview. ### Response columns Standard flow-aggregate columns plus `companyName` / `industry` / `sector` / `isAdr` / `isEtf` from `stock_mapping`. ### Response headers & errors Every `/v0/*` list response carries `X-Row-Count: `. Unknown query parameters return `400 Bad Request` with the list of accepted names in the body. ### Use cases - Cross-sectional cross-underlying ranks / z-scores for a given trading day - Sector- or ETF-restricted end-of-day dashboards - Building end-of-day universe-wide dashboards ### Parameters | Parameter | Type | Required | Description | | --- | --- | --- | --- | | `date` | string | No | Trading day (YYYY-MM-DD) to snapshot. Optional — defaults to the latest available trading day when omitted (matches the `grouped_flow_signal` snapshot behaviour). | | `figi` | string | No | Optional comma-separated list of composite FIGIs to restrict the snapshot to those symbols (paid tier only — trials are blocked at the router with HTTP 403 before this filter is evaluated). | | `is_etf` | boolean | No | Filter to ETFs (`true`) or non-ETFs (`false`). Applied to the joined `stock_mapping` row. | | `is_adr` | boolean | No | Filter to ADRs (`true`) or non-ADRs (`false`). | | `industry` | string | No | Filter on `stock_mapping.industry`. Case-insensitive on input; the emitted row carries the canonical mixed-case form. A value that doesn't match any known industry returns HTTP 400 with a fuzzy-match suggestion in the body (e.g. `"Unknown industry: 'semis'. Did you mean 'Semiconductors'?"`). | | `sector` | string | No | Filter on `stock_mapping.sector`. Case-insensitive on input; the emitted row carries the canonical mixed-case form. A value that doesn't match any known sector returns HTTP 400 with a fuzzy-match suggestion in the body (e.g. `"Unknown sector: 'technolgy'. Did you mean 'Technology'?"`). | | `format` | enum: json, parquet | No | `json` (default) or `parquet` (recommended for full-universe pulls). | ### Sample response ```json [ { "date": "2026-07-20", "figi": "BBG000B9WM03", "traded_underlying": "AB", "callput": "C", "n_contracts": 118, "total_shares": 11800, "last_underprice": 37.91, "adj_close": 37.91 } ] ``` --- ## Underlying Option Flows: 10-minute Snapshot ```http GET /v0/underlying_option_flows/10_min/snapshot ``` Single 10-minute-bucket cross-sectional snapshot of pre-aggregated option flow for every underlying. ### Description Cross-sectional snapshot at a specific 10-minute bucket: returns one row per `(figi, callput)` (every underlying with flow at the requested `(date, ten_min_timeframe)`, C and P kept as separate rows), joined to the split-adjusted underlying price for the same bucket AND to the PIT `stock_mapping` row for `(figi, date)`. No underlying selector is required. Optional `stock_mapping`-backed filters (`is_etf` / `is_adr` / `industry` / `sector`) narrow the returned figis to those matching the predicate. > **Trial tier: intraday is not available.** All 10-minute endpoints return HTTP 403 for trial accounts. See [Trial vs. paid tier](/documentation#trial-vs-paid-tier). The `ten_min_timeframe` param must fall on the requested `date`. Typical response is ~5,000–10,000 rows per bucket. Prefer `format=parquet` for full-universe pulls. ### Response columns Standard flow-aggregate columns plus `companyName` / `industry` / `sector` / `isAdr` / `isEtf` from `stock_mapping`. ### Response headers & errors Every `/v0/*` list response carries `X-Row-Count: `. Unknown query parameters return `400 Bad Request` with the list of accepted names in the body. ### Use cases - Intraday cross-sectional dashboards at 10-minute cadence - Sector- or ETF-restricted intraday flow ranks - Bucket-anchored cross-underlying flow ranks around scheduled events ### Parameters | Parameter | Type | Required | Description | | --- | --- | --- | --- | | `date` | string | No | Trading day (YYYY-MM-DD) the bucket falls on. Optional — defaults to the latest available trading day when omitted. | | `ten_min_timeframe` | string | Yes | Bucket start timestamp (`YYYY-MM-DD HH:MM:SS`). Must fall on `date`. | | `figi` | string | No | Optional comma-separated list of composite FIGIs to restrict the snapshot to those symbols (paid tier only — trials are blocked at the router with HTTP 403 before this filter is evaluated). | | `is_etf` | boolean | No | Filter to ETFs (`true`) or non-ETFs (`false`). Applied to the joined `stock_mapping` row. | | `is_adr` | boolean | No | Filter to ADRs (`true`) or non-ADRs (`false`). | | `industry` | string | No | Filter on `stock_mapping.industry`. Case-insensitive on input; the emitted row carries the canonical mixed-case form. A value that doesn't match any known industry returns HTTP 400 with a fuzzy-match suggestion in the body (e.g. `"Unknown industry: 'semis'. Did you mean 'Semiconductors'?"`). | | `sector` | string | No | Filter on `stock_mapping.sector`. Case-insensitive on input; the emitted row carries the canonical mixed-case form. A value that doesn't match any known sector returns HTTP 400 with a fuzzy-match suggestion in the body (e.g. `"Unknown sector: 'technolgy'. Did you mean 'Technology'?"`). | | `format` | enum: json, parquet | No | `json` (default) or `parquet` (recommended for full-universe pulls). | ### Sample response ```json [ { "date": "2026-07-21", "ten_min_timeframe": "2026-07-21 09:40:00", "figi": "BBG000B9WM03", "traded_underlying": "AB", "callput": "C", "n_contracts": 12, "total_shares": 1200, "last_underprice": 37.86, "adj_close": 37.8605 } ] ``` --- ## Underlying Flow Signals: Daily ```http GET /v0/underlying_flow_signals/01_day ``` Per-underlying × callput daily flow signals (raw nets, 21-day rolling sums, 2y z-scores across shares / premium / delta / gamma / vega). ### Description Retrieve daily flow-signal rows from the pre-aggregated `underlying_flow_signals_01_day` table, joined to the PIT `stock_mapping` row for `(figi, date)`. One row per `(date, figi, callput)`. The source table stores 34 columns per row: 4 keys (`date`, `figi`, `underlying_ticker`, `callput`) plus a 3-layer × 10-metric flow block. The 10 metrics are institutional + retail × shares / premium / delta / gamma / vega. The 3 layers are raw daily nets, `_21dma` (21-trading-day rolling sums), and `z_net_..._21dma` (z-scored against a trailing 2-year rolling mean and sample stddev of the 21dma). The raw μ / σ of the 2-year window are computed at query time and are NOT distributed as separate columns. Rolling windows PARTITION BY `(figi, callput)`; each callput row has its own independent trailing stats, so `z(C) + z(P) ≠ z(CP)`. ### The `callput` dimension Every source row exists in three variants: `C` (calls only), `P` (puts only), and `CP` (synthetic combined view). `CP` is built at ingest time so consumers do not have to reconstruct call-vs-put semantics themselves: - Directional nets (shares, premium) → `CP = C − P`. Bullish call flow and bearish put flow both express long-delta demand; the sign-flipped difference is the net directional pressure. - Naturally-signed greeks (delta, gamma, vega) → `CP = C + P`. Calls carry positive delta, puts negative; the straight sum through-adds correctly. The default `callput` is `CP`; omitting the filter would triple-count each figi-date across the three variants. ### Deriving fire / holding-period predicates Fire flags and holding-period flags are not persisted on this table; derive them at query time from the z-score column: - 2σ institutional-delta fire: `WHERE z_net_i_delta_21dma > 2 AND callput = 'CP'` - 3σ institutional-delta fire: `WHERE z_net_i_delta_21dma > 3 AND callput = 'CP'` Holding-period semantics (fire within the last 22 trading days AND same-metric z has not gone negative since) can be reconstructed with a self-lag or a rolling `max(z)` window function. The pipeline does not persist this because the same rule can be expressed on any of the 10 z-columns × any threshold, and pre-materialising that cross-product would add 60+ columns of low-density flags. ### Underlying selector Provide at most one of `figi` or `traded_underlying`. If `traded_underlying` is used, it is resolved server-side to the corresponding FIGI. Supplying both returns HTTP 400. An unknown `traded_underlying` returns HTTP 404, not an empty `[]`. The underlying selector may be omitted entirely when at least one `stock_mapping` filter (`is_etf` / `is_adr` / `industry` / `sector`) is provided; the response then covers every figi matching the metadata predicate for the date range. > **Trial tier.** Must supply an explicit `figi` or `traded_underlying` — a broad `sector` / `industry`-only sweep returns HTTP 400. Each distinct FIGI touched counts toward the 25-figi-per-UTC-day cap; the 26th distinct FIGI returns HTTP 403 with the touched list echoed back. Daily history clamped to 730 days. For broad cohort exploration use `/v0/grouped_flow_signal/01_day` (not figi-capped). See [Trial vs. paid tier](/documentation#trial-vs-paid-tier). ### Response columns `date`, `figi`, `traded_underlying`, `callput`, the 10 raw daily nets, 10 `_21dma` (21-day rolling sums), and 10 z-scores (against the trailing 2-year rolling μ/σ of the 21dma, computed at query time). Plus `industry` / `sector` / `isAdr` / `isEtf` from `stock_mapping` (ranged endpoint intentionally omits `companyName`). ### Response headers & errors Every `/v0/*` list response carries `X-Row-Count: `. Unknown query parameters return `400 Bad Request` with the list of accepted names in the body. ### Use cases - Longitudinal per-underlying z-score / 21dma trajectories on a chosen callput - Screener alerts on institutional 2σ/3σ z-score crossings (`z_net_i_delta_21dma > 2 AND callput = 'CP'`) - Sector-scoped signal sweeps (e.g. all Technology names with z ≥ 3 in the last week) - Vega-flow monitoring (`z_net_i_vega_21dma`) for vol-timing signals - Comparing call-side vs put-side conviction on the same name (`callput = 'C'` vs `callput = 'P'`) ### Parameters | Parameter | Type | Required | Description | | --- | --- | --- | --- | | `figi` | string | No | Composite FIGI of the underlying. Mutually exclusive with `traded_underlying`. Optional when a `stock_mapping` filter is supplied. | | `traded_underlying` | string | No | Stock ticker; resolved via the `dict_symbol_to_figi` dictionary. Mutually exclusive with `figi`. Optional when a `stock_mapping` filter is supplied. | | `callput` | enum: C, P, CP | No | `C` (calls only), `P` (puts only), or `CP` (default; synthetic combined view). CP is pre-computed, split by metric family: size measures (`shares`, `premium`) are `C − P` (unsigned magnitudes, sign-flipped on puts to produce a directional bullish-minus-bearish net); greeks (`delta`, `gamma`, `vega`) are `C + P` (naturally signed by option type, with put deltas stored negative, so a straight sum captures net book exposure). **The default is `CP` for a reason: every source row triples up on callput and omitting the filter triple-counts.** Case-insensitive on input (`c`, `Cp`, `CP`, `p` all accepted); the emitted row always carries the canonical upper-case form. | | `start_date` | string | No | Earliest trading day (inclusive, YYYY-MM-DD). Trial tiers are clamped to a 730-day window. | | `end_date` | string | No | Latest trading day (inclusive, YYYY-MM-DD). | | `is_etf` | boolean | No | Filter to ETFs (`true`) or non-ETFs (`false`). Applied to the joined `stock_mapping` row. | | `is_adr` | boolean | No | Filter to ADRs (`true`) or non-ADRs (`false`). | | `industry` | string | No | Filter on `stock_mapping.industry`. Case-insensitive on input; the emitted row carries the canonical mixed-case form. A value that doesn't match any known industry returns HTTP 400 with a fuzzy-match suggestion in the body (e.g. `"Unknown industry: 'semis'. Did you mean 'Semiconductors'?"`). | | `sector` | string | No | Filter on `stock_mapping.sector`. Case-insensitive on input; the emitted row carries the canonical mixed-case form. A value that doesn't match any known sector returns HTTP 400 with a fuzzy-match suggestion in the body (e.g. `"Unknown sector: 'technolgy'. Did you mean 'Technology'?"`). | | `format` | enum: json, parquet | No | `json` (default) or `parquet` (binary, ~5–10× smaller). | ### Sample response ```json [ { "date": "2026-07-22", "figi": "BBG000MM2P62", "traded_underlying": "META", "callput": "CP", "net_i_premium": -5814246.0, "net_i_delta": 12530000.0, "net_i_gamma": 240000.0, "net_i_vega": -820000.0, "net_i_premium_21dma": -25416300.0, "z_net_i_premium_21dma": -1.59, "z_net_i_delta_21dma": 2.08, "z_net_i_vega_21dma": -1.34, "industry": "Internet Content & Information", "sector": "Communication Services", "isAdr": "false", "isEtf": "false" } ] ``` --- ## Underlying Flow Signals: Daily Snapshot ```http GET /v0/underlying_flow_signals/01_day/snapshot ``` Single-day × callput cross-sectional snapshot of daily flow signals for every underlying with a row on `date`. ### Description Cross-sectional snapshot: returns one row per `(figi, callput)` with a signal-table row on the requested `date`, joined to the PIT `stock_mapping` row for `(figi, date)`. No underlying selector is required; the response covers the full universe on the requested `callput` (default `CP`). Optional `stock_mapping`-backed filters (`is_etf` / `is_adr` / `industry` / `sector`) narrow the returned figis. > **Trial tier: snapshot endpoints are not available.** This endpoint returns HTTP 403 for trial accounts (same treatment as intraday). A wide snapshot would charge every returned FIGI against the 25-figi-per-UTC-day cap in a single call, so the snapshot surface is blocked outright on trial. Use the ranged `/v0/underlying_flow_signals/01_day` endpoint with an explicit `figi` / `traded_underlying` filter for per-underlying signal pulls, or `/v0/grouped_flow_signal/01_day/snapshot` (not figi-capped) for broad screening. See [Trial vs. paid tier](/documentation#trial-vs-paid-tier). Typical unfiltered response is ~4,000–5,000 rows per trading day per callput variant. Adding a z-score filter (e.g. `WHERE z_net_i_delta_21dma > 2`) is left to the client; the endpoint returns the full universe row-set and clients filter downstream. Prefer `format=parquet` for anything larger than a browser preview. ### Callput semantics Same as the ranged endpoint: `C`, `P`, or `CP`. Default is `CP` when the `callput` parameter is omitted. Case-insensitive on input. See `/v0/underlying_flow_signals/01_day` for the C−P / C+P synthesis convention. Omitting the callput filter would triple-count; the endpoint enforces the default. ### Deriving fire predicates The endpoint returns the full universe row-set for the requested `date` × `callput`; filter downstream on the emitted z-score columns: ```python rows = requests.get(...).json() fires = [r for r in rows if r["z_net_i_delta_21dma"] and r["z_net_i_delta_21dma"] > 2] ``` ### Response columns The 34 signal-table columns (4 keys + 30 flow — 10 raw nets, 10 21-day rolling sums, 10 z-scores against the trailing 2-year μ/σ of the 21dma) plus `companyName` / `industry` / `sector` / `isAdr` / `isEtf` from `stock_mapping`. ### Response headers & errors Every `/v0/*` list response carries `X-Row-Count: `. Unknown query parameters return `400 Bad Request` with the list of accepted names in the body. ### Use cases - Daily screener: apply a z-threshold client-side to surface the day's institutional 2σ+ delta names - Sector-restricted cross-sectional signal maps on a chosen callput - End-of-day dashboards of active vol-flow (`z_net_i_vega_21dma`) crossings - Comparing full-universe C-side vs P-side conviction distributions ### Parameters | Parameter | Type | Required | Description | | --- | --- | --- | --- | | `date` | string | No | Trading day (YYYY-MM-DD) to snapshot. Optional — defaults to the latest available trading day when omitted (matches the `grouped_flow_signal` snapshot behaviour). | | `callput` | enum: C, P, CP | No | `C` (calls only), `P` (puts only), or `CP` (default; synthetic combined view). CP is pre-computed, split by metric family: size measures (`shares`, `premium`) are `C − P` (unsigned magnitudes, sign-flipped on puts to produce a directional bullish-minus-bearish net); greeks (`delta`, `gamma`, `vega`) are `C + P` (naturally signed by option type, with put deltas stored negative, so a straight sum captures net book exposure). **The default is `CP` for a reason: every source row triples up on callput and omitting the filter triple-counts.** Case-insensitive on input (`c`, `Cp`, `CP`, `p` all accepted); the emitted row always carries the canonical upper-case form. | | `figi` | string | No | Optional comma-separated list of composite FIGIs to restrict the snapshot to those symbols (paid tier only — trials are blocked at the router with HTTP 403 before this filter is evaluated). | | `is_etf` | boolean | No | Filter to ETFs (`true`) or non-ETFs (`false`). Applied to the joined `stock_mapping` row. | | `is_adr` | boolean | No | Filter to ADRs (`true`) or non-ADRs (`false`). | | `industry` | string | No | Filter on `stock_mapping.industry`. Case-insensitive on input; the emitted row carries the canonical mixed-case form. A value that doesn't match any known industry returns HTTP 400 with a fuzzy-match suggestion in the body (e.g. `"Unknown industry: 'semis'. Did you mean 'Semiconductors'?"`). | | `sector` | string | No | Filter on `stock_mapping.sector`. Case-insensitive on input; the emitted row carries the canonical mixed-case form. A value that doesn't match any known sector returns HTTP 400 with a fuzzy-match suggestion in the body (e.g. `"Unknown sector: 'technolgy'. Did you mean 'Technology'?"`). | | `format` | enum: json, parquet | No | `json` (default) or `parquet` (recommended for full-universe pulls). | ### Sample response ```json [ { "date": "2026-07-22", "figi": "BBG000B9WM03", "traded_underlying": "AB", "callput": "CP", "net_i_premium": 1240300.0, "net_i_delta": 8420000.0, "z_net_i_premium_21dma": 2.41, "z_net_i_delta_21dma": 2.68, "z_net_i_vega_21dma": 0.62, "companyName": "AllianceBernstein Holding L.P.", "industry": "Asset Management", "sector": "Financial Services", "isAdr": "false", "isEtf": "false" } ] ``` --- ## Grouped Flow Signal: Daily ```http GET /v0/grouped_flow_signal/01_day ``` Group-level (sector / industry / etf / adr / single_stock / all) × callput daily flow-signal rollups. ### Description Retrieve group-level daily flow-signal rows from `grouped_flow_signal_01_day`. One row per `(date, group_key, callput)`. No stock_mapping join; the `group_key` ARE the classification. The source table sums the `underlying_flow_signals_01_day` raw nets across every figi in the selected bucket, then re-runs the 21-day rolling sum + 2-year z-score cascade on the group-level series. Row shape mirrors `underlying_flow_signals_01_day` (10 raw nets + 10 `_21dma` + 10 z-scores; the raw μ / σ of the trailing 2-year window are computed at query time and are NOT distributed as separate columns). Callput semantics are identical (default `CP`, C−P for nets, C+P for greeks). ### Single-axis contract Query with **one** `group_type` value — either a canonical mixed-case sector or industry name (e.g. `"Technology"`, `"Software—Application"`) or one of the rollup literals `"etf"` / `"adr"` / `"single_stock"` / `"all"`. There is no paired second parameter — the family (sector vs industry vs rollup) is inferred from the value itself. The response row echoes the same value in the `group_key` STRING column; matching is case-insensitive on input and the wire always carries the canonical mixed-case form. ### Signal formula - `_21dma` = 21-trading-day rolling SUM of the group-level raw net, scoped to `(group_key, callput)`. - `z_net_*_21dma` = z-score of `_21dma` against its trailing 2-year (504 trading day) rolling mean and sample standard deviation, computed at query time. Minimum 63 non-null observations in the trailing window required, else null. Rounded to 2dp on emit. The raw μ / σ are not distributed as separate columns. Every industry-day is emitted with populated rolling-window fields (`21dma`, `z_*`) and `n_figi` exposed on the row. The 21dma and 2y baseline include ALL days regardless of `n_figi` — thin days contribute their (smaller) raw values to the trailing window, so each industry's z is calibrated against its own historical breadth mix and populates on every day. Cross-industry z magnitudes aren't directly comparable when breadth profiles differ meaningfully — filter on `n_figi` at query time when comparability matters. Sector, etf, adr, single_stock, and all branches always clear the 10-FIGI mark and are effectively unchanged; only industries (Conglomerates, Regulated Water, Uranium, etc.) are materially affected. ### ⚠ Not point-in-time for group classification The sector, industry, ETF, and ADR labels used to bucket each figi come from the CURRENT `stock_mapping` phase (row with `phase_end = 2099-12-31`), applied uniformly to every historical date. Sector / industry labels churn (10–20 reclassifications per year across the covered universe); clients expect historical rollups to use today's classification for consistency with the current portfolio view. The signal rolling window itself is per bucket; only the group-membership assignment is applied retroactively. If you need a fully point-in-time sector rollup, bucket at query time against `stock_mapping` filtered by `date BETWEEN phase_start AND phase_end`, using the `underlying_flow_signals_01_day` table directly. ### Group-type branches - `sector`: one row per distinct sector name per `(date, callput)`. `group_key` is the mixed-case sector string (~11 distinct values). **ETFs and ADRs are excluded from sector rollups** — they have their own top-level `etf` and `adr` branches, and their FMP-reported "issuer sector" is typically the legal-entity domicile (Financial Services for most ETFs), not the underlying-exposure sector. - `industry`: one row per distinct industry name per `(date, callput)`. `group_key` is the mixed-case industry string (~150 distinct values matching the FMP taxonomy). Same ETF/ADR exclusion. `n_figi` is exposed on every row so consumers can filter thin-breadth days at query time — see "Thin-industry handling" below. - `etf`: every figi with `isEtf = 'true'` on the current phase, aggregated. `group_key` = literal `"etf"`. - `adr`: every figi with `isAdr = 'true'` on the current phase, aggregated. `group_key` = literal `"adr"`. - `single_stock`: every figi that is neither ETF nor ADR (figis missing from `stock_mapping` default here). `group_key` = literal `"single_stock"`. - `all`: every figi in the underlying flow table. `group_key` = literal `"all"`. ### Aggregate identities `single_stock + etf + adr = all` exactly per `(date, callput)`. `sum(sector) ≈ single_stock` per `(date, callput)` within < 1% of gross flow (residual = single-stock figis without a current sector label). Note: because ETFs and ADRs no longer contribute to sector / industry rollups, `sum(sector) ≠ all` — the diff is the `etf + adr` contribution. `sum(industry) ≈ single_stock` too — every industry with any flow now emits a row (raw nets populated), and the honest sum lines up within the same < 1% single-stock-without-industry residual. ### Thin-industry handling Every industry-day is emitted with populated raw daily nets AND populated rolling / z fields. `n_figi` is exposed on every row so consumers can filter thin-breadth days at query time. The 21dma and 2y baseline include ALL days regardless of `n_figi` — thin days contribute their (smaller) raw values to the trailing window and to the 2y μ/σ used to z-score. Each industry's z-score is therefore calibrated against its own historical breadth mix — a chronically-thin industry's baseline is set to its own thin-day scale, so z fires when flow is unusually large *for that industry*. Cross-industry z magnitudes aren't directly comparable when breadth profiles differ meaningfully. Structurally-thin industries (historically ~46, including `Copper`, `Uranium`, `Consulting Services`, `Regulated Water`, `Conglomerates`, `Waste Management`, `Railroads`) now emit continuous rows with populated raw flow AND populated z-scores rather than being dropped entirely. Sector, etf, adr, single_stock, and all branches always exceed the threshold and the caveat is a no-op for them. ### Valid `group_key`: sector (~11 total) Sector matching is case-insensitive on input; the row-emitted `group_key` is always the canonical mixed-case form. For a one-line description of each sector, see the [Data dictionary → grouped_flow_signal_01_day](/documentation#grouped_flow_signal_01_day) section. `Basic Materials`, `Communication Services`, `Consumer Cyclical`, `Consumer Defensive`, `Energy`, `Financial Services`, `Healthcare`, `Industrials`, `Real Estate`, `Technology`, `Utilities`. ### Valid `group_key`: industry (~150 total) Case-insensitive on input; canonical mixed-case on the wire. Full enumerated list in the [Data dictionary → grouped_flow_signal_01_day](/documentation#grouped_flow_signal_01_day) section (with a one-line description of each). Passing a `group_type` value that doesn't match any known sector / industry name (or rollup literal) returns HTTP 400 with a fuzzy-match suggestion in the error body (e.g. requesting `"Semi"` returns `"Did you mean 'Semiconductors'?"`). Roughly 46 named industries are structurally thin (never clear the 10-FIGI mark). They appear in the response every day with raw daily nets AND rolling / z fields populated; the z magnitude on those rows is not directly comparable to full-breadth days (see the thin-industry handling section above — filter on `n_figi` at query time when comparability matters). The sector / industry enumerations may drift as existing figis are reclassified or new labels are added. For the live authoritative list, call the snapshot endpoint with `category=sector` (or `industry`) and take the distinct `group_key` values from the response. ### Response columns `date`, `group_key`, `callput`, `n_figi`, plus the 30 group-level flow columns (10 raw nets, 10 `_21dma` rolling sums, 10 z-scores; same layer shape as `underlying_flow_signals_01_day`; no metadata columns, none needed). ### Response headers & errors Every `/v0/*` list response carries `X-Row-Count: `. Unknown query parameters return `400 Bad Request` with the list of accepted names in the body. ### Use cases - Sector-rotation dashboards on institutional-delta z-score crossings (`group_type=Technology&callput=CP`, one call per sector name) - ETF vs single_stock vs ADR flow comparisons (`group_type=etf` / `adr` / `single_stock`) - Sector vol-flow monitoring (`z_net_i_vega_21dma` per sector, one call per name) - Named-industry timeseries (e.g. `group_type=Semiconductors`) plotted next to price ### Parameters | Parameter | Type | Required | Description | | --- | --- | --- | --- | | `group_type` | string | Yes | A single canonical `group_key` value. Either a mixed-case sector name (e.g. `Technology`, `Financial Services`), a mixed-case industry name (e.g. `Software—Application`, `Semiconductors`), or one of the rollup literals `etf` / `adr` / `single_stock` / `all`. Case-insensitive on input; the row-emitted `group_key` is always the canonical mixed-case form. A value that doesn't match any known name returns HTTP 400 with a fuzzy-match suggestion in the body (e.g. requesting `Semi` returns `Did you mean 'Semiconductors'?`). See the endpoint description for the full enumerated lists (~11 sectors, ~150 industries, plus 4 rollup literals). Time series is gap-free — industries with <10 contributing FIGIs on the day still emit rows with raw nets populated but rolling / z fields NULL. To sweep a whole family in one call, use the snapshot endpoint's `category` parameter instead. | | `callput` | enum: C, P, CP | No | `C` (calls only), `P` (puts only), or `CP` (default; synthetic combined view). CP is pre-computed, split by metric family: size measures (`shares`, `premium`) are `C − P` (unsigned magnitudes, sign-flipped on puts to produce a directional bullish-minus-bearish net); greeks (`delta`, `gamma`, `vega`) are `C + P` (naturally signed by option type, with put deltas stored negative, so a straight sum captures net book exposure). **The default is `CP` for a reason: every source row triples up on callput and omitting the filter triple-counts.** Case-insensitive on input (`c`, `Cp`, `CP`, `p` all accepted); the emitted row always carries the canonical upper-case form. | | `start_date` | string | No | Earliest trading day (inclusive, YYYY-MM-DD). Trial tiers are clamped to a 730-day window; this endpoint is not figi-capped, so it is the recommended surface for broad cohort exploration on the trial tier. | | `end_date` | string | No | Latest trading day (inclusive, YYYY-MM-DD). | | `format` | enum: json, parquet | No | `json` (default) or `parquet` (recommended for full-history sector sweeps). | ### Sample response ```json [ { "date": "2026-07-22", "group_key": "Technology", "callput": "CP", "net_i_premium": 182004000.0, "net_i_delta": 1253000000.0, "net_i_vega": -82000000.0, "net_i_premium_21dma": 2529450000.0, "z_net_i_premium_21dma": 0.70, "z_net_i_delta_21dma": 1.94, "z_net_i_vega_21dma": -1.12 } ] ``` --- ## Grouped Flow Signal: Daily Snapshot ```http GET /v0/grouped_flow_signal/01_day/snapshot ``` Single-day cross-sectional snapshot of group-level daily flow-signal rows. Omit both filters to return every group_key (~168 rows / callput / day across ~11 sectors, ~150 industries, and 4 rollup literals). ### Description Cross-sectional snapshot of `grouped_flow_signal_01_day` on a single trading day. Every input parameter is optional; with no filters the endpoint returns every `group_key` with any flow on the latest available day (~168 rows on the default `callput = 'CP'`). - `date` omitted → latest available trading day. - Neither `category` nor `group_type` set → every `group_key` with flow returned in the same payload (~11 sectors + up to ~150 industries + `etf` + `adr` + `single_stock` + `all`, on the default `callput = 'CP'`). Industry rows with fewer than 10 contributing FIGIs on the requested date carry populated raw nets but NULL rolling / z fields (see "Thin-industry handling" below) — they are NOT dropped. - `category` set → restrict the payload to a whole family: `industry` returns every industry row (up to ~150), `sector` returns just the ~11 sector rows, `rollup` returns the 4 rollup rows, `all` returns every family. Snapshot-only parameter. - `group_type` set → restrict to a single `group_key` value (a mixed-case sector / industry name or one of the rollup literals `etf` / `adr` / `single_stock` / `all`). Case-insensitive on input; a value that doesn't match any known name returns HTTP 400 with a fuzzy-match suggestion in the body. - `category` and `group_type` are mutually exclusive. Pass one or the other, never both. Response shape is identical to the ranged endpoint: single `group_key` STRING column + `date` + `callput` + 30 flow columns per row (10 raw nets, 10 `_21dma` rolling sums, 10 z-scores against the trailing 2-year μ/σ of the 21dma), no stock_mapping join. See the ranged endpoint for the full sector / industry enumeration, the CP synthesis rule, and the z-score formula. ### ⚠ Not point-in-time for group classification Same load-bearing caveat as the ranged endpoint: the sector / industry / ETF / ADR labels used to bucket each figi come from the CURRENT `stock_mapping` phase (row with `phase_end = 2099-12-31`), applied uniformly to every historical date. The signal rolling window (21dma + 2y baseline) is per bucket; only the group-membership assignment is applied retroactively. For strict-PIT sector rollups, use the `underlying_flow_signals/01_day/snapshot` endpoint and bucket client-side against a PIT `stock_mapping` snapshot. ### ETF / ADR exclusion from sector & industry Sector and industry aggregations EXCLUDE ETFs and ADRs (which get their own top-level `etf` / `adr` rollups). FMP tags ETFs with the issuer's legal-entity sector (typically `Financial Services`), which is meaningless for underlying exposure — so pooling ETFs into sector rollups would inflate Financial Services by 85%+ of gross flow. The exclusion is applied at the underlying-figi filter step, not on the emitted rows. ### Thin-industry handling Every industry-day is emitted with populated raw daily nets AND populated rolling / z fields (`21dma`, `z_*`). `n_figi` is exposed on every row so consumers can filter thin-breadth days at query time. The 21dma and 2y baseline include ALL days regardless of `n_figi`, so each industry's z is calibrated against its own historical breadth mix and populates on every day. Cross-industry z magnitudes aren't directly comparable when breadth profiles differ meaningfully — filter on `n_figi` at query time when comparability matters. Structurally-thin industries (historically ~46, including `Copper`, `Uranium`, `Consulting Services`, `Regulated Water`, `Conglomerates`, `Waste Management`, `Railroads`) now emit continuous rows with populated raw flow AND populated z-scores rather than being absent. Sector, etf, adr, single_stock, and all branches always exceed the 10-FIGI mark and the caveat is a no-op for them. ### Response headers & errors Every `/v0/*` list response carries `X-Row-Count: `. Unknown query parameters return `400 Bad Request` with the list of accepted names in the body. ### Use cases - End-of-day cross-sectional heatmap across every sector, industry, and rollup on the same day (no filters) - Sector-rotation dashboard: `category=sector` on the latest date, plot z-score by group_key - ETF vs single_stock vs ADR flow comparison for the latest close (`category=rollup`) - Screen every industry for institutional 2σ+ delta fires with one call (`category=industry`, filter `z_net_i_delta_21dma > 2` client-side) ### Parameters | Parameter | Type | Required | Description | | --- | --- | --- | --- | | `date` | string | No | Trading day (YYYY-MM-DD) to snapshot. Defaults to the latest available day. | | `category` | enum: industry, sector, rollup, all | No | Family-level filter. One of `industry` / `sector` / `rollup` / `all`. Restricts the snapshot to the intended grouping family — `industry` returns every industry row with flow on the day (up to ~150), `sector` returns just the ~11 sector rows, `rollup` returns the `etf` / `adr` / `single_stock` / `all` rollup rows, `all` returns every family. Mutually exclusive with `group_type` — pass one or the other, not both. Snapshot-only; not accepted on the ranged endpoint. | | `group_type` | string | No | Single-key filter — a canonical `group_key` value (mixed-case sector or industry name, or one of the rollup literals `etf` / `adr` / `single_stock` / `all`). Case-insensitive on input; the row-emitted `group_key` is always the canonical mixed-case form. A value that doesn't match any known name returns HTTP 400 with a fuzzy-match suggestion in the body. Mutually exclusive with `category` — use `group_type` when you want a single specific `group_key`, `category` when you want a whole family. | | `callput` | enum: C, P, CP | No | `C` (calls only), `P` (puts only), or `CP` (default; synthetic combined view). CP is pre-computed, split by metric family: size measures (`shares`, `premium`) are `C − P` (unsigned magnitudes, sign-flipped on puts to produce a directional bullish-minus-bearish net); greeks (`delta`, `gamma`, `vega`) are `C + P` (naturally signed by option type, with put deltas stored negative, so a straight sum captures net book exposure). **The default is `CP` for a reason: every source row triples up on callput and omitting the filter triple-counts.** Case-insensitive on input (`c`, `Cp`, `CP`, `p` all accepted); the emitted row always carries the canonical upper-case form. | | `format` | enum: json, parquet | No | `json` (default) or `parquet` (binary, ~5–10× smaller). | ### Sample response ```json [ { "date": "2026-07-22", "group_key": "Technology", "callput": "CP", "net_i_premium": 182004000.0, "net_i_delta": 1253000000.0, "net_i_vega": -82000000.0, "z_net_i_premium_21dma": 0.70, "z_net_i_delta_21dma": 1.94, "z_net_i_vega_21dma": -1.12 }, { "date": "2026-07-22", "group_key": "all", "callput": "CP", "net_i_premium": 812000000.0, "net_i_delta": 4820000000.0, "z_net_i_delta_21dma": 1.42 } ] ``` --- # Flat files Read-only HMAC access to the production bucket for systematic pipelines. **Bucket:** `gs://prod-masscrest/v0` Flat-files delivery is for clients whose corporate environment cannot make outbound calls to `api.masscrest.com`. Your pipeline reads Parquet exports directly from `gs://prod-masscrest/v0/`. The recommended path is a **warehouse integration**: bind a Google service account minted by Snowflake, Databricks, BigQuery, or your own GCP runtime and Masscrest grants read-only IAM against it — no secret material in the loop. Provision, list, and revoke bindings from the **Warehouse integrations** card on the Flat files section of the docs page. HMAC key pairs remain available as a fallback for Python / CLI users (see section 5). ### Trial-tier bucket Flat-files delivery on the trial tier requires the `programmatic` entitlement (opt-in per account; contact sales). Enabled trials get HMAC keys against **`gs://prod-masscrest-trial`** — **not** `gs://prod-masscrest`. The trial bucket is an aggregates-only, daily-only mirror lagged 180 days behind current, with fresh reference metadata. Trials without `programmatic` receive HTTP 403 on any flat-files call. See [Trial vs. paid tier](/documentation#trial-vs-paid-tier) on the Data dictionary for the full policy. Flat-file delivery ships raw parquet partitions to a Masscrest-managed bucket. Two access modes are supported: **warehouse integrations** (Snowflake, Databricks, BigQuery, or any Google-service-account-based reader) that bind a warehouse principal directly to the bucket for federated reads with no key material in the loop, and **HMAC access keys** for Python / CLI users who want to read the same parquet with `gsutil`, `boto3`, `s3fs`, or DuckDB. The warehouse path is the recommended one for production pipelines; HMAC remains the fallback for local scripts and edge runtimes. Two buckets are provisioned depending on the account tier: `gs://prod-masscrest` for paid accounts (full history, contract-level and intraday) and `gs://prod-masscrest-trial` for trial accounts with the `programmatic` entitlement enabled (aggregates-only, daily-only, 180-day-lagged). Trials without `programmatic` see HTTP 403 on any flat-files call — the entitlement is opt-in per account; contact sales to enable it. The layout, split-adjustment reconstruction, and access conventions below apply verbatim to both buckets; swap `prod-masscrest` for `prod-masscrest-trial` where noted. The bucket layout is `/v0//year=YYYY/date=YYYY-MM-DD/*.parquet`. Nine surfaces are published: `option_flows`, `underlying_option_flows`, `underlying_flow_signals`, `grouped_flow_signal`, `contract_id_mapping`, `stock_mapping`, `split_history`, `px_data`, and `nport_holdings`. The `data_path` field returned by `GET /v0/flat-files/credentials` is the bucket root (`gs://prod-masscrest/`); append `/v0//` yourself when composing a read path. ## 1. Warehouse integration (recommended) Bind a warehouse principal (Snowflake storage integration, Databricks storage credential, BigQuery cloud-resource connection, or a raw Google service account) directly to the flat-files bucket. Masscrest grants read-only IAM against your principal server-side; the warehouse issues short-lived credentials to itself and streams parquet without any secret material leaving your infrastructure. Provision, list, and revoke bindings from the **Warehouse integrations** card on the Flat files section of the docs page. > **Trial-tier note.** Replace `gs://prod-masscrest` with `gs://prod-masscrest-trial` throughout this section when your account is on the trial tier. Masscrest resolves the correct bucket server-side when the grant actually fires, so a snippet that still points at `gs://prod-masscrest` would mint the integration against the paid bucket while the SA ends up bound to the trial bucket, and the first read will 403. Section 5 below carries the same substitution for the HMAC fallback. ### Snowflake storage integration Snowflake mints the delegated Google service account when you create the storage integration. Paste that service account into the Warehouse integrations card and click **Grant access**. Once the grant flips to `active`, create an external stage pointing at the Masscrest bucket and read parquet directly. ```sql -- Step 1: create the storage integration. CREATE STORAGE INTEGRATION masscrest_flatfiles TYPE = EXTERNAL_STAGE STORAGE_PROVIDER = 'GCS' ENABLED = TRUE STORAGE_ALLOWED_LOCATIONS = ('gcs://prod-masscrest/'); -- Step 2: get the Snowflake-minted service-account email. DESC STORAGE INTEGRATION masscrest_flatfiles; -- Copy the STORAGE_GCP_SERVICE_ACCOUNT value (looks like -- abcd1234@gcpuscentral1-1dfa.iam.gserviceaccount.com) into the -- Warehouse integrations card and click "Grant access". ``` Once the grant is `active`, create an external stage and run an end-to-end verification against the sentinel file (present in both buckets): ```sql -- Step 4: create the external stage and verify. CREATE STAGE masscrest_flatfiles_stage STORAGE_INTEGRATION = masscrest_flatfiles URL = 'gcs://prod-masscrest/v0/' FILE_FORMAT = (TYPE = PARQUET); SELECT $1 FROM @masscrest_flatfiles_stage/_ping/hello.txt (FILE_FORMAT => (TYPE = CSV)); ``` ### Databricks storage credential Databricks on GCP mints a Google service account per storage credential. Paste that email into the Warehouse integrations card and click **Grant access**. Once active, create an external location and query parquet through Unity Catalog. ```sql -- In your Databricks workspace: CREATE STORAGE CREDENTIAL masscrest_flatfiles WITH DATABRICKS_MANAGED_IDENTITY COMMENT 'Masscrest flat-files read-only'; -- Grab the service account email: DESCRIBE STORAGE CREDENTIAL masscrest_flatfiles; -- Paste the "Service Account" value into the Warehouse -- integrations card and click "Grant access". CREATE EXTERNAL LOCATION masscrest_prod URL 'gs://prod-masscrest/' WITH (STORAGE CREDENTIAL masscrest_flatfiles); ``` Once the grant is `active`: ```sql SELECT * FROM parquet.`gs://prod-masscrest/underlying_option_flows/v0/01_day/year=2026/date=2026-08-14/` LIMIT 10; ``` ### BigQuery cloud-resource connection BigQuery uses a cloud-resource connection whose delegated service account you paste into the Warehouse integrations card. ``` bq mk --connection \ --location=us \ --project_id= \ --connection_type=CLOUD_RESOURCE \ masscrest_flatfiles bq show --format=prettyjson --connection \ .us.masscrest_flatfiles | jq .cloudResource.serviceAccountId # Paste that service account into the Warehouse integrations card. ``` Once the grant is `active`, create an external table: ```sql CREATE OR REPLACE EXTERNAL TABLE `.masscrest.underlying_option_flows_01_day` WITH CONNECTION `us.masscrest_flatfiles` OPTIONS ( format = 'PARQUET', uris = ['gs://prod-masscrest/underlying_option_flows/v0/01_day/year=*/date=*/*.parquet'], hive_partition_uri_prefix = 'gs://prod-masscrest/underlying_option_flows/v0/01_day/' ); ``` ### Raw Google service account For any GCP-native runtime (Cloud Run, GKE, Composer, Dataflow, custom Compute Engine), create a service account, paste its email into the Warehouse integrations card, and read the bucket with the standard `google-cloud-storage` client library once the grant is `active`. ``` gcloud iam service-accounts create masscrest-reader \ --project= \ --display-name="Masscrest flat-files reader" gcloud iam service-accounts list \ --project= \ --filter="displayName:Masscrest flat-files reader" \ --format="value(email)" # Paste the resulting email into the Warehouse integrations card. ``` ### AWS S3 runtimes GCS does not accept AWS IAM roles as readers, so there is no native principal-delegation path from an AWS runtime. Run `rclone sync` on a schedule from EC2 / ECS / Lambda using an HMAC key pair (see section 5) to mirror flat files into a bucket you own. `rclone` streams parquet directly bucket-to-bucket without staging locally. ``` # rclone.conf snippet [masscrest] type = s3 provider = Other access_key_id = secret_access_key = endpoint = https://storage.googleapis.com region = auto rclone sync \ masscrest:prod-masscrest/underlying_option_flows/v0/01_day/year=2026/date=2026-08-14/ \ s3:your-analytics-bucket/masscrest/underlying_option_flows/01_day/2026-08-14/ ``` ### Azure ADLS runtimes GCS does not accept Azure Managed Identities as readers, so there is no native principal-delegation path from an Azure runtime. Use `rclone` from Azure Container Instances or Azure Functions with an HMAC key pair (section 5) to sync into ADLS Gen2 on a daily schedule. ### Grant lifecycle Each principal binding runs through `pending → active → failed | revoked` server-side. The Warehouse integrations card polls until the grant flips terminal, then displays a `gsutil cat` command against a small verification file so you can prove end-to-end reachability from your warehouse. Grants persist across bucket-tier upgrades: when a trial account upgrades to paid, the principal is automatically re-bound to `gs://prod-masscrest`. ## 2. Bucket layout All flat files land under `gs://prod-masscrest/`, laid out as `/v0//`, hive-partitioned by `year=YYYY/date=YYYY-MM-DD/` where `date` is the trading date. New partitions for `date = T-1` land in the 05:00–09:00 UTC window on calendar day `T`. The reference surfaces (`stock_mapping/`, `contract_id_mapping/`, `split_history/`, `grouped_flow_signal/`) are full-snapshot reloads: same filename pattern each day, no partition hierarchy. `_manifests/year=YYYY/date=YYYY-MM-DD/manifest.json` is written as the very last step of the daily publish and is the delivery-complete signal for polling clients (see the Data dictionary overview for the payload schema). ``` gs://prod-masscrest/ ├── _manifests/ │ └── year=YYYY/date=YYYY-MM-DD/manifest.json # delivery-complete signal, written last ├── stock_mapping/v0/ │ └── stock_mapping_*.parquet # full snapshot, rewritten daily ├── contract_id_mapping/v0/ │ └── contract_id_mapping_*.parquet # full snapshot, rewritten daily ├── split_history/v0/ │ └── split_history_*.parquet # full snapshot, rewritten daily ├── px_data/v0/ │ ├── 01_day/year=YYYY/date=YYYY-MM-DD/*.parquet │ └── 10_min/year=YYYY/date=YYYY-MM-DD/*.parquet ├── option_flows/v0/ │ ├── 01_day/year=YYYY/date=YYYY-MM-DD/*.parquet │ └── 10_min/year=YYYY/date=YYYY-MM-DD/*.parquet ├── underlying_option_flows/v0/ # underlying-level rollups │ ├── 01_day/year=YYYY/date=YYYY-MM-DD/*.parquet │ └── 10_min/year=YYYY/date=YYYY-MM-DD/*.parquet ├── underlying_flow_signals/v0/ # underlying-level flow-signal derivatives │ └── 01_day/year=YYYY/date=YYYY-MM-DD/*.parquet └── grouped_flow_signal/v0/ └── data-*.parquet # full snapshot, rewritten daily ``` ### Trial-tier bucket Trial accounts with the `programmatic` entitlement land on `gs://prod-masscrest-trial` instead of the production bucket. Same `/v0//` layout, but a strict subset: underlying-level flow tables only, daily grain only, and every partition is 180 days behind the current session. ``` gs://prod-masscrest-trial/ ├── stock_mapping/v0/ │ └── stock_mapping_*.parquet # fresh; full mirror of production ├── contract_id_mapping/v0/ │ └── contract_id_mapping_*.parquet # fresh; full mirror of production ├── split_history/v0/ │ └── split_history_*.parquet # fresh; full mirror of production ├── underlying_option_flows/v0/ │ └── 01_day/year=YYYY/date=YYYY-MM-DD/*.parquet # T-180d lag ├── underlying_flow_signals/v0/ │ └── 01_day/year=YYYY/date=YYYY-MM-DD/*.parquet # T-180d lag ├── grouped_flow_signal/v0/ │ └── data-*.parquet # fresh; full mirror (not figi-capped) └── px_data/v0/ └── 01_day/year=YYYY/date=YYYY-MM-DD/*.parquet # T-180d lag ``` The trial bucket does **not** carry contract-level `option_flows/v0/*` or 10-minute intraday `*/10_min/*` partitions. A daily append at 04:00 UTC extends the lagged tables by one partition (`T-180d`). Reference data (`stock_mapping/`, `contract_id_mapping/`, `split_history/`, and `grouped_flow_signal/`) refreshes on the same daily cadence as the production bucket. On upgrade to a paid account, your trial HMAC key is revoked at the moment of upgrade — call `POST /v0/flat-files/credentials/rotate` to mint a new key against `gs://prod-masscrest`, then re-point your `~/.boto` / gsutil config to the production bucket. ## 3. Split-adjusted price and volume: local reconstruction `px_data/v0/01_day` and `px_data/v0/10_min` ship raw unadjusted `close` and `volume` per `(date, figi, symbol)`. Split-adjustment is decoupled: `split_history/v0/split_history_*.parquet` (a full snapshot of the `split_history` reference table) is the authoritative source, refreshed daily. This lets you keep raw prints for audit and derive `adj_close` / `adj_volume` at query time. ### `split_history` schema One row per `(figi, valid_from, valid_to)`, non-overlapping intervals covering the FIGI's timeline. The `cum_split_factor` column holds the cumulative product of every split from `valid_from + 1 day` forward within the entity's active phase. To convert a raw close on date `X` into a current-shares-equivalent price, divide by `cum_split_factor`; to convert raw volume, multiply. The most recent interval always carries `1.0`. No-split entities carry a single row spanning their full active phase with `cum_split_factor = 1.0`; same shape, no special case. ### Ticker context For time series where the ticker has changed (FB → META, TWTR → X, etc.), join on `figi` (stable), never on `traded_underlying` (point-in-time and recyclable). `stock_mapping` provides `(figi, traded_underlying, phase_start, phase_end, sector, industry, companyName, isEtf, isAdr, isActivelyTrading, isin)`; join with `date BETWEEN phase_start AND phase_end` to attach the correct ticker / sector / ETF flag for the session. Join by `figi` and use `traded_underlying` as the point-in-time ticker. ### Snippet A: pandas + pyarrow ```python import pandas as pd import s3fs # requires: pip install pandas pyarrow s3fs # s3fs signs with the HMAC pair against the S3-compatible GCS endpoint. # Do not use gcsfs or google-cloud-storage here — those libraries # authenticate via OAuth / ADC and reject HMAC credentials. fs = s3fs.S3FileSystem( key="", secret="", client_kwargs={"endpoint_url": "https://storage.googleapis.com"}, ) d = "2026-07-24" px = pd.read_parquet( f"s3://prod-masscrest/px_data/v0/01_day/year=2026/date={d}/", filesystem=fs, ) sh_files = ["s3://" + p for p in fs.glob("prod-masscrest/split_history/v0/split_history_*.parquet")] md_files = ["s3://" + p for p in fs.glob("prod-masscrest/stock_mapping/v0/stock_mapping_*.parquet")] sh = pd.read_parquet(sh_files, filesystem=fs, columns=["figi", "valid_from", "valid_to", "cum_split_factor"]) md = pd.read_parquet(md_files, filesystem=fs, columns=["figi", "traded_underlying", "phase_start", "phase_end"]) px["date"] = pd.to_datetime(px["date"]).dt.date sh["valid_from"] = pd.to_datetime(sh["valid_from"]).dt.date sh["valid_to"] = pd.to_datetime(sh["valid_to"]).dt.date merged = px.merge(sh, on="figi", how="left") in_range = (merged["date"] >= merged["valid_from"]) & (merged["date"] <= merged["valid_to"]) merged = merged[in_range].copy() merged["adj_close"] = merged["close"] / merged["cum_split_factor"] merged["adj_volume"] = merged["volume"] * merged["cum_split_factor"] # Optional: attach point-in-time ticker from stock_mapping. merged = merged.merge(md, on="figi", how="left", suffixes=("", "_md")) merged = merged[(merged["date"] >= merged["phase_start"]) & (merged["date"] <= merged["phase_end"])] out = merged[["figi", "date", "traded_underlying", "close", "volume", "adj_close", "adj_volume"]] ``` ### Snippet B: DuckDB ```sql INSTALL httpfs; LOAD httpfs; SET s3_endpoint='storage.googleapis.com'; SET s3_url_style='path'; SET s3_access_key_id=''; SET s3_secret_access_key=''; WITH px AS ( SELECT date, figi, symbol, close, volume FROM read_parquet('s3://prod-masscrest/px_data/v0/01_day/year=2026/date=2026-07-24/*.parquet') ), sh AS ( SELECT figi, valid_from, valid_to, cum_split_factor FROM read_parquet('s3://prod-masscrest/split_history/v0/split_history_*.parquet') ), md AS ( SELECT figi, traded_underlying AS pit_ticker, phase_start, phase_end FROM read_parquet('s3://prod-masscrest/stock_mapping/v0/stock_mapping_*.parquet') ) SELECT px.figi, px.date, COALESCE(md.pit_ticker, px.symbol) AS symbol, px.close, px.volume, px.close / sh.cum_split_factor AS adj_close, px.volume * sh.cum_split_factor AS adj_volume FROM px LEFT JOIN sh USING (figi) LEFT JOIN md USING (figi) WHERE px.date BETWEEN sh.valid_from AND sh.valid_to AND px.date BETWEEN md.phase_start AND md.phase_end; ``` ### Precision `cum_split_factor` is stored at 12 decimal places to preserve heavy reverse-split stacks (e.g. UVXY's true factor at `1900-01-01` is ~6.7e-11). Do **not** round to 6 decimals; that historically truncated ~200 heavy-reverse-splitter FIGIs to `0.0` and silently zeroed downstream adjustments. Also treat `cum_split_factor IS NULL` as "no valid adjustment for this period"; never coerce to `0.0`, as the extreme tail of penny-stock reverse-split chains underflows Float64 and is emitted as `NULL` by design. ## 4. Applying the same adjustment to flows `option_flows_01_day` and its 10-minute sibling already carry a split-adjusted underlying price via the joined `adj_close` field; clients using the provided column need no local derivation. To overlay flows on your own price series, or compute ratios like `flow_per_shares_outstanding` where the denominator is your own split-adjusted volume, apply the same `close / cum_split_factor` and `volume * cum_split_factor` logic from Section 3 against the raw px files. Join keys are identical: `figi` for the row, `(valid_from, valid_to)` on `split_history` for the factor. See the dictionary entries for [`option_flows_01_day`](/documentation#option-flows-01-day) and [`px_01_day`](/documentation#px-01-day) for the full column contracts. ## 5. HMAC access keys (Python / CLI users) For local scripts, notebooks, and edge runtimes that can't use a warehouse integration, Masscrest also issues HMAC key pairs that authenticate against the GCS S3-compatible endpoint. Provision or rotate the pair from the **HMAC access key** card on the Flat files section of the docs page. This path is a fallback — for production pipelines, prefer the warehouse integration in section 1 (no secret material, shorter blast radius on rotation, native to the SQL engine you're already running). ### S3-compatible access GCS speaks the S3 protocol at `https://storage.googleapis.com`. HMAC keys authenticate against the bucket the same way native S3 keys do. Bucket name is `prod-masscrest` (drop the `gs://` prefix); every other path segment above is a key prefix. Trial accounts substitute `prod-masscrest-trial` throughout the snippets below — the client library, endpoint URL, and signing flow are identical. ```python import boto3 from datetime import date, timedelta s3 = boto3.client( "s3", endpoint_url="https://storage.googleapis.com", aws_access_key_id="", aws_secret_access_key="", region_name="auto", ) d = date.today() - timedelta(days=1) prefix = f"px_data/v0/01_day/year={d.year}/date={d.isoformat()}/" for obj in s3.list_objects_v2(Bucket="prod-masscrest", Prefix=prefix).get("Contents", []): print(obj["Key"], obj["Size"]) ``` ### From AWS (Lambda, EC2, ECS, Glue) The same boto3 client works unchanged from any AWS runtime. No GCP setup is required; the Masscrest HMAC key is just another set of S3 credentials, and the `--endpoint-url` override reroutes the request to `storage.googleapis.com`. The recommended pattern is to store your Masscrest HMAC pair in AWS Secrets Manager (or SSM Parameter Store) and fetch it at runtime: ```python import boto3, json secrets = boto3.client("secretsmanager", region_name="us-east-1") creds = json.loads(secrets.get_secret_value(SecretId="masscrest/hmac")["SecretString"]) s3 = boto3.client( "s3", endpoint_url="https://storage.googleapis.com", aws_access_key_id=creds["access_key"], aws_secret_access_key=creds["secret"], region_name="auto", ) ``` The AWS CLI works with the same override: ``` aws s3 --endpoint-url https://storage.googleapis.com ls s3://prod-masscrest/ aws s3 --endpoint-url https://storage.googleapis.com cp \ s3://prod-masscrest/px_data/v0/01_day/year=2026/date=2026-07-24/ ./local/ --recursive ``` Common downstream patterns from AWS: - **Sync into your own S3 for Athena or Redshift Spectrum.** Schedule a small EC2, Lambda, or Glue job that copies each day's partition into a bucket you own, then query the parquet directly. No format translation, no re-shaping needed. - **External stages in Snowflake or Databricks on AWS.** Both support S3-compatible sources; point the external stage at `s3://prod-masscrest/...` with the `--endpoint-url` override and load daily. - **Step Functions or Glue Workflows.** Poll `s3://prod-masscrest/_manifests/year=YYYY/date=YYYY-MM-DD/manifest.json` as the "day is ready" signal; the manifest also enumerates every parquet the run wrote (fields: `workflow_run_id`, `trading_date`, `published_at`, `files: [{path, byte_count}]`), so it doubles as the file list for your job. ### Notes - Credentials are read-only and list-only. They cannot upload, delete, or overwrite objects. - Disabling flat-files access on the account revokes the key server-side immediately. - For high-volume downloads, run your workload in the same region as the bucket to avoid egress charges.