SEC Fails to Deliver
Fails-to-deliver by security from the SEC's CNS data: shares that did not settle on time.
sec_fails_to_deliver — the dataset name to pass to the Obscura API.
What one row means
One CUSIP's aggregate fails-to-deliver position for a single settlement date, from SEC's Consolidated Fails-to-Deliver (CNS FTD) half-month bulk files: the total share quantity that failed to settle at NSCC's Continuous Net Settlement system for that security on that date, plus the closing price SEC used to compute dollar exposure.
One row per (occurred_on, cusip).
Point-in-time availability
Every Obscura dataset carries available_date: the calendar day the publisher made the row
available, day-of, with no session rounding. It is the one column a backtest filters on, and it means the
same thing on every dataset in the catalog.
For sec_fails_to_deliver: published: available_date is the day SEC posted the HALF-MONTH ZIP that contains the row, identical on every row of the file, materialized by the collector from the object's HTTP Last-Modified (m20260702_000198). Availability is a property of the FILE, not of the settlement date: the zip lands whole, so a day-1 row and a day-15 row become knowable at the same instant. The date is SEC's OWN DAY on SEC's own clock (Eastern), day-of and with no advance for the hour it posted at: a zip written at 17:04 ET was published that day, not the next. An mtime is only admissible here because the artifacts are excluded BY NAME (see below) — an mtime that moved for any reason other than the publication is not evidence of one. The previous occurred_on + n generated column was a per-row lag on a per-file event and was wrong at BOTH ends — it stamped a file's earliest rows before the file existed whenever SEC ran late (cnsfails202511b posted 2026-02-05 while +30 claimed 2025-12-16) and its latest rows up to two weeks after it. Where SEC's server mtime is not a credible publication stamp — the 257 archive files it bulk-touched on 2020-12-19, and 17 with no mtime at all — the source's measured steady-state schedule (half start + 33 days) stands in and errs late.
Refresh cadence
Obscura refreshes sec_fails_to_deliver weekly — the most frequent scheduled job that re-collects or re-exports it. This is Obscura's own pipeline cadence, not the upstream publisher's release schedule; when a row became public is recorded per row in available_date.
Schema — 8 columns
The full public column list for sec_fails_to_deliver, with the meaning of every field. The same
schema is served unauthenticated at https://api.obscura.trade/v1/catalog/sec_fails_to_deliver.
| Column | Type | Description |
|---|---|---|
| occurred_on | date · not null | The FTD settlement date at NSCC's Continuous Net Settlement system, from the source file's per-row 'SETTLEMENT DATE' field. Half of the (occurred_on, cusip) upsert key; also used by the collector to detect already-ingested year/month pairs. |
| cusip | text · not null | 9-character CUSIP identifying the security with a fail-to-deliver position that day. Second half of the (occurred_on, cusip) upsert key. Not a ticker — join to symbol-keyed datasets via a CUSIP/ticker map, not directly. |
| symbol | text | Ticker symbol as SEC lists it for the CUSIP that half-month; NULL when the source field is blank. SEC's own symbol mapping can lag corporate actions, so treat as informational, not authoritative — cusip is the stable key. |
| quantity | bigint · not null | Total number of shares that failed to deliver for this CUSIP on this settlement date, aggregated across all participants at NSCC's CNS system. Whole shares, no fractional component in the source. |
| description | text | Free-text issuer/security description string as SEC prints it in the file; often truncated/abbreviated by SEC's fixed-width-derived export. Descriptive only, not used for joins. |
| price | double precision | Reference closing price SEC applies to the CUSIP for that settlement date, used to translate the fails quantity into an approximate dollar value of undelivered shares. NULL means SEC had no reference price that day, not a zero-dollar fail. |
| scraped_at | timestamp with time zone · not null | Internal ingestion timestamp; this table's synced_at analogue under a legacy name predating the synced_at naming convention. Refreshed on re-upsert of an already-seen (occurred_on, cusip) pair. Row bookkeeping only, EXCLUDED from parquet exports. |
| available_date | date · not null | PUBLIC-availability date: the day SEC posted the half-month CNS FTD zip that contains this row. Identical for every settlement date inside one file, because the file lands whole — a day-1 row and a day-15 row became knowable at the same instant. Read from the object's HTTP Last-Modified where that is a credible publication stamp, and from SEC's measured steady-state schedule (half start + 33 days) where it is not; recorded per file in sec_ftd_processed.published_on so a bronze replay reproduces it. The point-in-time column to filter/join on — NEVER the settlement date itself. |
Access sec_fails_to_deliver
Two delivery paths, one identifier. Both require an Obscura account and an active subscription; the catalog entry and the schema above are public.
import obscura
client = obscura.Client("obs_live_…")
df = client.query(
dataset="sec_fails_to_deliver",
symbols=["NVDA", "AAPL"],
start="2024-01-01",
)Create a free account Browse all 95 datasets
Frequently asked questions
What is in the sec_fails_to_deliver dataset?
Fails-to-deliver by security from the SEC's CNS data: shares that did not settle on time. One CUSIP's aggregate fails-to-deliver position for a single settlement date, from SEC's Consolidated Fails-to-Deliver (CNS FTD) half-month bulk files: the total share quantity that failed to settle at NSCC's Continuous Net Settlement system for that security on that date, plus the closing price SEC used to compute dollar exposure.
How do I avoid look-ahead bias with sec_fails_to_deliver?
Filter on sec_fails_to_deliver.available_date, the day the publisher made the row public. For this dataset that date is derived as follows — published: available_date is the day SEC posted the HALF-MONTH ZIP that contains the row, identical on every row of the file, materialized by the collector from the object's HTTP Last-Modified (m20260702_000198). Availability is a property of the FILE, not of the settlement date: the zip lands whole, so a day-1 row and a day-15 row become knowable at the same instant. The date is SEC's OWN DAY on SEC's own clock (Eastern), day-of and with no advance for the hour it posted at: a zip written at 17:04 ET was published that day, not the next. An mtime is only admissible here because the artifacts are excluded BY NAME (see below) — an mtime that moved for any reason other than the publication is not evidence of one. The previous occurred_on + n generated column was a per-row lag on a per-file event and was wrong at BOTH ends — it stamped a file's earliest rows before the file existed whenever SEC ran late (cnsfails202511b posted 2026-02-05 while +30 claimed 2025-12-16) and its latest rows up to two weeks after it. Where SEC's server mtime is not a credible publication stamp — the 257 archive files it bulk-touched on 2020-12-19, and 17 with no mtime at all — the source's measured steady-state schedule (half start + 33 days) stands in and errs late. A query of the form WHERE available_date <= '<as-of date>' never sees a row before it existed.
In what formats can I get sec_fails_to_deliver?
As a Parquet bulk export (POST https://api.obscura.trade/v1/download) or as JSON from the typed query API (POST https://api.obscura.trade/v1/query), both with dataset="sec_fails_to_deliver". The column schema is public at https://api.obscura.trade/v1/catalog/sec_fails_to_deliver.
Can I filter sec_fails_to_deliver by company or symbol?
Yes. sec_fails_to_deliver carries symbol, the column the API's symbols filter resolves against.
How often is sec_fails_to_deliver updated?
Obscura refreshes sec_fails_to_deliver on a weekly schedule — that is the most frequent scheduled job that re-collects or re-exports the table. It is Obscura's own pipeline cadence, not the upstream publisher's release schedule; when the publisher makes a row available is described by the availability rule above, and is recorded per row in available_date.