What it is

One row per unique option contract that traded on the session, keyed on (date, traded_symbol, callput, strikeprice, expirationdate). Each row carries buy / sell / net triples for retail (_r), institutional (_i), and market maker (_m), expressed four ways: contract count (_qty), dollar premium (_premium), and greek-weighted USD notional (_delta, _gamma, _vega).

Why it exists

The contract-grain view is where you see who's actually on which side of a specific position. Underlying-level aggregates are good for sector or cross-stock work, but questions like "is the institutional buyer in TSLA Jan 300 calls leaning in on a specific expiry?" or "is the put-side flow concentrated in the 20-delta wings?" only answer at this grain. This table is the base: the underlying rollups and the signal tables are summations of it.

How to use it

Filter on the dimensions that matter: date + figi (join via contract_id_mapping) + callput + the strike / expiry range. For a time series on one specific contract, resolve to contract_id once via contract_id_mapping and filter on that so your series survives corporate actions.

Three conventions to internalise before summing:

• net_* = buy_* − sell_*. Delta notionals are naturally signed by option type (calls +, puts −) so net_*_delta already carries directional lean.
• _i + _r + _m ≈ 0 on the notional columns, row by row (up to rounding and floor flow). Market-maker legs absorb the customer imbalance by construction.
• qty is the trade count. Summing the six cohort legs (buy_r + sell_r + buy_i + sell_i + buy_m + sell_m) double-counts the MM leg and inflates volume by about 30 to 35 percent.

floor_* holds exchange-floor / broker-committed prints that Masscrest does not side-classify. Reported for volume completeness; do not include in buy / sell / net logic.

Examples

Reading institutional lean on a single strike. TSLA 2025-01-17 300 call on 2024-11-15: pull the one row keyed (2024-11-15, TSLA, C, 300, 2025-01-17). net_i_delta positive and large relative to qty × last_underprice × last_delta means institutional buyers are dominating the demand for that contract that day.

Reconstructing per-cohort premium across a split. NVDA Jan 2025 500 call across the June 2024 10-for-1 split: join contract_id_mapping on (traded_symbol, expirationdate, callput, traded_strikeprice) with a PIT clamp, then sum net_i_premium by contract_id. The resulting series runs clean across the ex-date even though the row keys differ pre and post split.

Context columns

Each row carries last_* columns (underprice, fwd_underprice, price, iv, delta, gamma, vega) observed at the last trade of the session. Useful as an end-of-day mark for the contract. Null when the contract had no trades in the session (rare at this grain, but can happen on open-position rows pulled from inventory).

• Grain: (date, traded_symbol, callput, strikeprice, expirationdate)

Fields

Reconstructing figi / adj_shares_deliverable from flat files

REST and MCP responses already carry figi, contract_id, traded_underlying, adj_shares_deliverable, and cumulative_split_factor on every row. The flat-file parquet drops them to keep file sizes small. Rebuild with a single PIT join to contract_id_mapping:

SELECT
  f.*,
  m.traded_underlying,
  m.contract_id,
  m.adj_shares_deliverable,
  m.cumulative_split_factor,
  m.underlying_figi AS 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

Join stock_mapping on (underlying_figi, date BETWEEN phase_start AND phase_end) for underlying metadata (sector, industry, isEtf, isAdr, companyName).

What it is

The 10-minute sibling of option_flows_01_day. One row per unique option contract that traded within the bucket, keyed on (date, ten_min_timeframe, traded_symbol, callput, strikeprice, expirationdate). Same cohort split and same metric families (_qty, _premium, _delta, _gamma, _vega across retail / institutional / MM), just resolved at 10-minute cadence instead of session-level.

Why it exists

Intraday matters when the question is about timing. Did the institutional accumulation front-run a specific headline? Did retail buy into the top of a move or chase a breakout? Session-level aggregates smear those entries. 10-minute buckets are the shortest grain Masscrest publishes and are the native cadence of the intraday flow story.

How to use it

Same convention as option_flows_01_day (buy / sell / net, net_* = buy_* − sell_*, cohort identity _i + _r + _m ≈ 0, signed delta notionals). Add the ten_min_timeframe dimension to your filter / join / group-by. Join to px_10_min on (date, ten_min_timeframe, figi) for the matching price context; both sides use the same bucket convention.

REST cap: 30-day request window per call. For multi-year intraday history pull parquet from gs://prod-us-option-tenmin-contract/option_flows/v0/10_min/.

Bucket boundary convention

ten_min_timeframe is the end of the 10-minute bucket, stored as a naive New York wall-clock 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).

Examples

Front-running a headline. An M&A rumour hits at 11:12 ET. Filter (date, figi='<target>') across buckets 11:10:00 through 11:30:00. If net_i_delta on OTM calls spikes above the prior 90-minute average before the newswire print at 11:15, that's a timing signal.

Earnings-afternoon put flow. Day before earnings, pull (date, figi, callput='P') across buckets 15:00:00 to 16:00:00. Rising net_r_qty into the close on short-dated puts flags retail hedging (or chasing) activity ahead of the print.

• Grain: (date, ten_min_timeframe, traded_symbol, callput, strikeprice, expirationdate)

Fields

What it is

Daily option flow aggregated per underlying × callput. One row per (date, figi, callput) with callput ∈ {'C', 'P', 'CP'}. Same cohort split as the contract-grain table (retail _r, institutional _i, market-maker _m) with the same four metric families, but all strikes and expirations collapsed into a single number per side.

Why it exists

Most flow questions aren't about one specific contract. "Is retail bullish on NVDA today?" / "Did institutions rotate into Energy puts this week?" need the total call-side and put-side demand on the name, not one strike at a time. This table is that collapse, done server-side with the correct per-contract deliverable multiplier baked in so you can plot a time series without joining anything else.

How to use it

Filter on (date, figi, callput). Three conventions to internalise:

• Units are pre-multiplied. _shares = Σ (qty × adj_shares_deliverable) in underlying-share equivalents. _premium and the greeks are USD notionals, deliverable-adjusted. No contract_id_mapping join needed.
• Cohort identity _i + _r + _m ≈ 0 holds row by row on the notionals. MM legs absorb customer imbalance.
• The CP row is a pre-computed differential, not a sum of C and P. See "The CP row" below for the convention per column family.

Price and metadata aren't baked in: join px_01_day (plus split_factors for adjustment) and stock_mapping on figi.

No contract-level filters here (no dte, no abs_delta). For those, use option_flows_01_day at contract grain.

Examples

Retail bullishness on NVDA. Filter (date='2024-11-15', figi=<NVDA>, callput='CP') and read net_r_delta. Positive means retail net long delta on NVDA across the whole option chain that session (calls bought minus puts bought, net of sells, after delta-weighting).

Weekly institutional rotation into Energy puts. Join stock_mapping on figi to get sector, filter sector='Energy' and callput='P', sum net_i_premium across the FIGIs for each date in the window. Compare the 5-day sum against the prior 5-day sum.

• Grain: (date, figi, callput)

Fields

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.

What it is

The 10-minute sibling of underlying_option_flows_01_day. Daily option flow collapsed per underlying × callput, resolved at 10-minute cadence. One row per (date, ten_min_timeframe, figi, callput) with callput ∈ {'C', 'P', 'CP'}. Same cohort split and metric families, deliverable-multiplied at ingest.

Why it exists

The daily table answers "what happened today on this name"; this table answers "when during the day did it happen". The intraday footprint of cohort flow often matters more than the aggregate: institutional accumulation into the close looks different from the same volume at the open.

How to use it

Filter on (date, ten_min_timeframe, figi, callput). Same conventions as the daily sibling: pre-multiplied units (no contract_id_mapping join needed), _i + _r + _m ≈ 0 row-level identity, and the CP row as a pre-computed differential (see below).

Join px_10_min on (date, ten_min_timeframe, figi) for the matching price context. Both sides use the same NY-wall-clock bucket convention.

REST cap: 30-day request window per call. For multi-year intraday history pull parquet from gs://prod-us-option-tenmin-underlying/underlying_option_flows/v0/10_min/.

Bucket boundary convention

ten_min_timeframe is the end of the 10-minute bucket, stored as a naive New York wall-clock DateTime. Bucket covers (ten_min_timeframe − 10 min, ten_min_timeframe]. First RTH bucket ends at 09:40:00, last at 16:00:00.

Examples

Opening vs closing institutional flow on SPY. Pull (date, figi=<SPY>, callput='CP') for one session. Split net_i_delta into the 09:40:00 to 10:30:00 buckets (open) and 15:00:00 to 16:00:00 buckets (close) and compare. A big positive close with flat open signals mark-up / rebalancing behaviour; the inverse signals de-risking into the bell.

Headline-timing on a biotech. FDA PDUFA print lands around 14:00 ET. Filter (date, figi=<name>, callput='C') across the 13:40:00 to 14:00:00 buckets. Rising net_i_premium in the three buckets before 14:00 against zero-volume baseline is a leaked-print signal.

• Grain: (date, ten_min_timeframe, figi, callput)

Fields

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.

What it is

The underlying-grain nets from underlying_option_flows_01_day, plus two trailing-window transforms of each metric: a 21-trading-day rolling sum (_21dma) and a 2-year rolling z-score of that sum (z_*). One row per (date, figi, callput) with callput ∈ {'C', 'P', 'CP'}.

Why it exists

Raw daily flow is noisy. The signal story is usually "flow is extreme against the stock's own history", not "flow is extreme in absolute terms". A $50M net call premium is unremarkable on SPY and a monster print on a small-cap. Normalising each raw net against its own 2-year baseline (sum first to smooth, then z-score) collapses the two onto a comparable scale and makes a quintile sort across names meaningful.

Published Masscrest research is built off this transform: the single-name spike predicate at /research/systematic-single-stock-signal and the industry-aggregated long-short performance at /research/industry-flow-signal. All fields are trailing-window only (no forward-looking data), so a row at date = D is safe to act on at close of D or open of D+1.

How to use it

Filter on (date, figi, callput). Grab the raw net, the 21dma, or the z-score depending on what question you're asking.

• Raw net (net_i_shares, net_i_premium, etc.): one session's cohort flow. Use for single-day event studies.
• 21dma (net_i_shares_21dma, etc.): trailing 21-session sum. Smooths the one-day noise, still trailing-only.
• Z-score (z_net_i_shares_21dma, etc.): the 21dma expressed in units of its own 2-year rolling mean and standard deviation. |z| > 2 is the usual "extreme vs own history" threshold.

Each row is scoped to one callput. Filter to a single callput per query or you'll triple-count. Rolling windows partition by (figi, callput) independently, so z(C) + z(P) ≠ z(CP).

The CP row

Size measures (shares, premium) on the CP row are C − P: bullish call flow minus bearish put flow, both of which express the same bullish directional conviction. Greeks (delta, gamma, vega) are C + P: these columns are already signed on the C and P rows (put delta stored negative), so a straight sum gives net book exposure. net_*_delta on the CP row is net directional demand across the whole chain.

Examples

Spike screen. Filter callput='CP' and z_net_i_premium_21dma > 2 on a given date. Returns the names where institutional call-minus-put premium flow this month is more than two standard deviations above the name's own 2-year baseline.

Cohort-divergence signal. Compare z_net_i_delta vs z_net_r_delta on the same (date, figi, callput='CP'). Opposite signs flag names where institutions and retail are positioned against each other, a setup the flagship signals validate at single-name grain.

Signal design

The calculation is deliberately simple:

• _21dma = 21-trading-day rolling SUM of the raw daily net, partitioned by (figi, callput). (Named for legacy reasons: it's a sum, not a mean, but the z-score is identical either way.)
• 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.

Masscrest does not cap, winsorise, or otherwise regularise the output. Z-scores can reach 3 or 4 SD during earnings, M&A, and macro catalysts. 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.

Transform chain: daily net, then 21-day trailing sum (_21dma), then z-score against the 2y rolling μ/σ of the 21dma (z_*).

Warm-up

All fields computed from 2019-11-10 forward. The 21dma needs 21 non-null daily nets; the z-score needs at least 63 non-null observations in the trailing 504-session window. Below threshold, the value is null.

• Grain: (date, figi, callput) where callput ∈ {'C', 'P', 'CP'}.

Fields

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.

What it is

Daily flow signals rolled up above the single-name level. Same transform chain as underlying_flow_signals_01_day (raw net, then 21-day trailing sum, then 2-year rolling z-score), but aggregated across groups: sector, industry, ETF, ADR, single-stock-aggregate, and market-wide. One row per (date, group_key, callput).

Why it exists

Single-name signals get noisy and hard to compare across the universe. A sector- or industry-level roll-up smooths the noise, surfaces regime shifts that show up as cross-stock coordination, and gives you a universe-level dial on the retail vs institutional gap. Masscrest's industry-aggregated long-short validation at /research/industry-flow-signal runs off this table.

How to use it

Filter on (date, group_key, callput). group_key is a single string column that holds either the canonical sector name (Technology, Financial Services, ...), the canonical industry name (Semiconductors, Software - Application, ...), or one of the four rollup literals (etf, adr, single_stock, all). The REST endpoint accepts the same values case-insensitively on group_type and echoes them back in group_key.

As with underlying_flow_signals_01_day: filter to one callput per query, and read the raw / 21dma / z-score column depending on what you're asking.

Examples

Sector-rotation scan. Pull group_type='sector', callput='CP', one trading day. Sort z_net_i_premium_21dma descending. Top rows are the sectors where institutional net premium over the trailing month is most extreme against the sector's own 2-year baseline. Compare against the bottom rows for rotation candidates.

Industry-level long/short screen. Daily cross-section on group_type='industry', callput='CP', filtered to n_figi >= 10 (drop thin-breadth days), long the top quintile of z_net_i_delta_21dma, short the bottom quintile. This is the construction the /research/industry-flow-signal validation uses.

• Grain: (date, group_key, callput)

Group semantics

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.

Valid industry values

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.

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, so 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.

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.

Fields

What it is

Daily OHLCV for the US single-stock and ETF universe. One row per (date, traded_underlying). Prices are unadjusted (contemporaneous print values).

Why it exists

You can't build a clean option-flow story without a matching price context. Masscrest ships the same daily close used internally for signal construction, keyed to the same ticker / FIGI layer as the flow tables, so there's no cross-vendor join drift between flow and price.

How to use it

The table isn't exposed directly over REST or MCP. The adjusted price columns (adj_close, adj_open, adj_volume, total_turnover) are LEFT-joined onto the underlying_option_flows endpoints: pass traded_underlying=<TICKER> and callput='CP' to get_underlying_option_flows_daily (MCP) or GET /v0/underlying_option_flows/01_day (REST) and you get one row per date with prices attached to the flow payload.

For bulk historical pulls, the parquet is fanned out across the daily buckets your plan includes: gs://prod-us-option-daily-underlying/px_data/v0/01_day/, gs://prod-us-option-daily-contract/px_data/v0/01_day/, or the trial subset at gs://trial-us-option-daily-underlying/px_data/v0/01_day/.

Unadjusted semantics

Prices and volume are unadjusted. To compute split-adjusted series (safe cross-split returns, share-count normalisation, historical strike comparisons), join to split_factors on figi with date BETWEEN valid_from AND valid_to and apply cum_split_factor. Keeping the two sides separate lets you reload px_01_day and split_factors independently and gives you an explicit knob when you need to invert an adjustment.

• Grain: (date, traded_underlying)

Fields

What it is

10-minute OHLCV bars for the US single-stock and ETF universe. Same universe and join key as px_01_day, one row per (date, ten_min_timeframe, traded_underlying). Prices unadjusted.

Why it exists

Intraday flow is only useful against intraday price. Shipping the bars directly off the same feed keeps the flow and price sides in lockstep at the 10-minute cadence so bucket joins are clean.

How to use it

Not exposed directly. The adjusted price columns (adj_close, adj_open, adj_volume, total_turnover) are LEFT-joined onto 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). The join emits one row per (date, ten_min_timeframe, figi) with prices attached.

For bulk historical pulls, parquet lives on the 10-min bucket your plan includes: gs://prod-us-option-tenmin-underlying/px_data/v0/10_min/ or gs://prod-us-option-tenmin-contract/px_data/v0/10_min/.

Bucket boundary convention

ten_min_timeframe is the end of the 10-minute bucket, stored as a naive New York wall-clock 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 to 16:00 ET). No pre-market, no after-hours. Halted sessions produce no rows for the halted intervals.

Unadjusted semantics

Prices and volume are unadjusted. To compute split-adjusted series join to split_factors on figi with date BETWEEN valid_from AND valid_to and apply cum_split_factor. Keeping the two sides separate lets you reload px_10_min and split_factors independently and gives you an explicit knob when you need to invert an adjustment.

• Grain: (date, ten_min_timeframe, traded_underlying)

Fields

What it is

A lookup table from FIGI to listed ticker, with company name, sector, industry, and ETF / ADR flags attached. One row per contiguous period during which a given FIGI traded under a given ticker.

Why it exists

Tickers move. Companies rename themselves, merge, get taken private, delist. Tickers get recycled. A sector breakdown or historical PnL series keyed on the ticker alone is wrong whenever one of those events happens. Masscrest pins everything to FIGI (the stable company identifier) first and resolves the ticker as a function of the date. This table is where that resolution lives.

How to use it

Join to any flow or price table on figi with date BETWEEN phase_start AND phase_end. That gives you the ticker, company name, sector, industry, and flags that were true on that trading day. If you want a single row per entity today, filter phase_end = '2099-12-31' (the open-phase sentinel).

Examples

Facebook to Meta. One company, one FIGI (BBG000MM2P62), two rows. FB from inception through 2022-06-08, META from 2022-06-09 onward. A flow query joined on this FIGI returns a clean history across the rename with no manual ticker-swap logic.

XOM restructure. On 2026-07-07 Exxon reorganised into ExxonMobil Holdings. Same traded_underlying='XOM', new FIGI. Two rows with different FIGIs under the same ticker. A query filtered by traded_underlying='XOM' returns both; the PIT join on date picks the right one for each trading day.

Quirks

• ETFs. sector and industry describe the fund's issuer entity (typically Financial Services), not the underlying exposure. For ETF-aggregate flow use the etf rollup on grouped_flow_signal_01_day.
• ADRs. sector and industry describe the foreign company's actual business. HDB is Financial Services (HDFC Bank), BABA is Consumer Cyclical (Alibaba). Filter on isAdr if you need to separate US-domiciled sector totals from foreign ADRs.
• Rollup already clean. The served grouped_flow_signal_01_day rollup excludes ETFs and ADRs from its sector / industry group_keys and routes them to their own etf and adr buckets. The two quirks above only matter when joining stock_mapping directly.

Fields

What it is

A stable Masscrest identifier (contract_id) for every option contract, plus the per-phase shares-deliverable, strike, and symbol as they stood during each corporate-action epoch. One row per contract phase.

Why it exists

Reverse splits, spinoffs, and M&A baskets change what an option delivers without necessarily changing the strike or the symbol. After a 1-for-10 reverse split, an existing XYZ 50 call still has strike 50 but now delivers 10 shares instead of 100. The clearing house usually numerates the adjusted root (XYZ1) and a fresh standard XYZ 50 call relists shortly after, delivering the usual 100 shares. Two contracts with identical callput / strike / expiration and overlapping symbols, radically different payoffs. Keying on strike + symbol alone silently mixes them. contract_id disambiguates.

Forward splits are cleaner: the strike divides by the split ratio and the deliverable stays at 100 shares, so pre-split and post-split rows carry different strikes under the same contract_id without any ambiguity on the key.

How to use it

In most cases you don't. The REST and MCP option_flows_* responses already carry contract_id, adj_shares_deliverable, figi, and traded_underlying on every row, resolved server-side.

You need to join this table in two cases:

1. Reading raw flat-file parquet. The base flat-file tables drop mapping columns to keep file sizes small. Join on (traded_symbol, expirationdate, callput, traded_strikeprice) with date BETWEEN phase_start AND phase_end to attach contract_id, underlying_figi, adj_shares_deliverable, and traded_underlying.
2. Reconciling your own option books to Masscrest flow. Look up the contract_id for the position on the trade date, then filter Masscrest flow on that contract_id to get the full history of that economic contract across any rename or split.

Examples

Reverse split (SOXS, 3x-inverse semiconductor ETF). SOXS reverse-splits often. After a 1-for-10 reverse split, the existing SOXS 50 call keeps strike 50 but drops to 10 shares deliverable. The clearing house numerates the adjusted root to SOXS1. A fresh SOXS 50 call relists shortly after at the standard 100 deliverable. In contract_id_mapping you get two separate contract_ids on the same (50, C, expiration) key: the adjusted series with traded_symbol=SOXS1, adj_shares_deliverable=10, cumulative_split_factor=0.1, and the fresh series with traded_symbol=SOXS, adj_shares_deliverable=100, cumulative_split_factor=1.0. Join on contract_id and the two never get confused.

Forward split (NVDA 10-for-1, June 2024). All open NVDA options had their strikes divided by 10 on the ex-date. The Jan 2025 500 call becomes a Jan 2025 50 call. Two phase rows under the same contract_id: pre-split with traded_strikeprice=500, post-split with traded_strikeprice=50. adj_shares_deliverable=100 on both (forward splits don't touch the deliverable). A PnL series joined on contract_id tracks the position clean across the ex-date with no manual strike scaling.

Relation to split_history

split_history carries the underlying-level split factor per stock. contract_id_mapping carries the contract-level deliverable per contract. The two agree on reverse splits (adj_shares_deliverable = 100 × cumulative_split_factor, both < 1.0). They diverge on forward splits (deliverable stays at 100 while cumulative_split_factor climbs above 1.0, because the strike absorbs the adjustment) and on M&A baskets (deliverable becomes a bespoke cash+share mix not a clean multiple of anything).

Fields

What it is

Cumulative split-adjustment factors per FIGI, structured as a slowly-changing dimension. One row per continuous phase with a given factor; non-overlapping intervals covering each FIGI's timeline. date BETWEEN valid_from AND valid_to matches exactly one row per (figi, date).

Why it exists

px_01_day and px_10_min ship contemporaneous prints, not adjusted prices. You need an adjustment factor to compare prices across a split or to normalise share counts to current units. Shipping the factor as a separate SCD2 table (instead of baking an adj_close into the price table) means you can invert Masscrest's adjustment to recover the raw print whenever you need it, and reload prices and splits independently when one changes.

How to use it

Divide a raw close on any date X by the cum_split_factor whose [valid_from, valid_to] covers X. That gives you a current-shares-equivalent price. The factor is 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.

Join pattern: split_factors on figi with date BETWEEN valid_from AND valid_to.

Examples

Forward split (AAPL 4-for-1, 2020-08-31). Pre-split phase through 2020-08-30 carries cum_split_factor = 4.0. A raw close of 500 on 2020-07-01 gives an adjusted close of 125 (500 / 4). Post-split phase from 2020-08-31 carries cum_split_factor = 1.0 (nothing to adjust against the current unit).

Reverse split (SOXS 1-for-10). Pre-split phase carries cum_split_factor = 0.1. A raw close of 2 on a pre-split day gives an adjusted close of 20 (2 / 0.1). The adjusted series stays on a consistent share-count basis across the reverse split.

• Grain: (figi, valid_from, valid_to).
• History: full history for each covered FIGI back to the earliest known split. Pre-split-history phases use valid_from = 2019-11-10.
• Timing: rebuilt in full each trading day whenever a new split lands.

Fields