lake.sec_daily_ohlc dataset schema

Scroll sideways to compare all columns. Select a heading to sort.

No matching rows. Clear the filter to see all records.

Current logical schema for lake.sec_daily_ohlc

Query lake.sec_daily_ohlc using the column names and types below. The query validator and engine use this logical catalog; a field absent from an older shard returns typed NULL.

Save this page's SQL as query.sql and the runner below as query.py. Use a registered key with lake scope from Developers. The runner submits the displayed sec_daily_ohlc query once, saves its operation, waits on that same ID, saves the returned manifest and downloads its Parquet parts.

This submission has not been executed for the reference page. Inspect the SQL, dates and resource limits before running it. Reuse the request key only for this identical SQL and limits; use a new key when changing them.

python3 -m pip install nexustrade==1.42.0
export NEXUSTRADE_API_KEY="YOUR_LAKE_SCOPED_KEY"
python3 query.py

import json
from pathlib import Path
from nexustrade import NexusTradeClient

# Save the SQL displayed on this page as query.sql.
sql = Path("query.sql").read_text()
client = NexusTradeClient.from_environment()
operation = client.create_lake_query(
    {"query": sql, "limits": {"timeoutSeconds": 30, "maxRows": 50,
                              "maxResultBytes": 1048576}},
    idempotency_key="reference-sec_daily_ohlc-20261012-v1",
)
Path("query-operation.json").write_text(json.dumps(operation, indent=2))
finished = client.wait_for_lake_query(operation["id"])
if finished["status"] != "completed":
    raise RuntimeError(finished)
manifest = client.get_lake_query_manifest(operation["id"])["result"]
Path("query-manifest.json").write_text(json.dumps(manifest, indent=2))
print(json.dumps(manifest, indent=2))
for part in manifest["parts"]:
    Path(f"query-{part['part']}.parquet").write_bytes(
        client.download_lake_query_part(operation["id"], part["part"], max_bytes=1048576)
    )

Equity daily OHLC + light fundamentals (SEC SoT).

Covers the tradable STOCK + ETF universe. ETFs and other non-filers (SPY, QQQ, USO) are present as price-only rows: OHLC and volume are populated, `peRatioTTM` / `psRatioTTM` / `pbRatioTTM` and other statement-linked fields are usually NULL. A null ratio means the issuer files no statements, not that the ticker is missing — query ETF price history exactly like equities.

`dividendYield` is ALREADY a percentage. Two percent is `dividendYield > 2`, not `> 0.02`.

`marketCap` is `unadjustedClose * sharesOutstanding` as of that date, and is NULL for ETFs and for filers whose only share figure is a period average. Filter with `marketCap IS NOT NULL` rather than treating NULL as zero. Never re-derive market cap from `closingPrice`, which is split- and dividend-adjusted — that basis mismatch reported a $31B company as $31,363.

`sharesAsOf` is when the share count was measured, not when it became public; between filings it is the precision bound on `marketCap`.

`sharesAvailableAt`, `sharesAccession`, and `sharesSourceFilingUrl` identify the selected share source; `sharesOutstandingScope` distinguishes issuer-wide, class-specific, and unqualified vendor counts. Older shards may have NULL source lineage. For a multi-class issuer, an issuer-wide count multiplied by one listed class's price is only a one-price company-value approximation, not a class-weighted valuation or independent confirmation of the share count.

Latest screens SHOULD pin to the newest session with full coverage, not raw `MAX(date)`: a partially published session can sit at the tip with a fraction of the universe. Use `WHERE date::DATE = (SELECT session FROM latest_full)` with `WITH latest_full AS (WITH sessions AS ( SELECT date::DATE AS session, COUNT(DISTINCT ticker) AS tickers FROM lake.sec_daily_ohlc WHERE date::DATE >= (SELECT MAX(date::DATE) FROM lake.sec_daily_ohlc) - INTERVAL 45 DAY GROUP BY 1 ), scored AS ( SELECT session, tickers, MEDIAN(tickers) OVER (ORDER BY session ROWS BETWEEN 20 PRECEDING AND 1 PRECEDING) AS trailing_median FROM sessions ) SELECT MAX(session) AS session FROM scored WHERE trailing_median IS NULL OR tickers >= 0.9 * trailing_median)`.

The daily tip is only admitted after the SEC price refresh finalizes a session (21:00 America/New_York); in-progress Polygon bars are never written. Do NOT write `date < CURRENT_DATE` — that lags a finished tip by a calendar day after the refresh lands. This table is end-of-day closes, not live quotes.

`ticker` is the symbol the security traded under ON THAT DATE. A company that changed ticker keeps its earlier rows under its earlier ticker (Facebook is `FB` before 2022-06-09 and `META` after; Cabot Oil & Gas is `COG` until 2021-10-01, then Coterra is `CTRA`), and a reused ticker holds a different company before its current holder listed (`CTRA` in 2019 is Contura Energy).

For one company's history across a rename, join `lake.security_segments` on `ticker` and the date span and filter its `canonical_symbol`, e.g. `JOIN lake.security_segments s ON o.ticker = s.ticker AND o.date::DATE BETWEEN s.from_date AND COALESCE(s.to_date, DATE '9999-12-31') WHERE s.canonical_symbol = 'META'`.