eq_store.py — Equity EOD Parquet History Store
Decided 2026-09-27 (scope "A", equity first):
- The full NSE equity EOD history, from 1994 onward, lives in per-stock Parquet files.
- Postgres
scr_nseeq_eodbecomes a staging window of about 90 days. The daily bhav pipeline keeps writing there, so ingest, rollback (deleteadateabove), AMI export and the "latest date" checks don't change.
F&O (scr_nsefo_eod) and MCX are not moved yet. When they are, it will be one table at a
time, each with its own layout (F&O needs by-date/month files for the option chain).
Layout
EQEOD_DATA_PATH/ LOCAL_EQEOD_DATA_PATH / VPS_EQEOD_DATA_PATH in backend/.env
├── raw/{first letter}/{SYMBOL} one extensionless Parquet file per stock (like the IEOD store)
└── _catalog/
├── days.parquet sdate, rows, sum_close, sum_volume (per date)
└── symbols.parquet masid, file, rows, first, last (per stock)
- Columns: the same as
scr_nseeq_eod(masid, sdate, OHLC,slast, prevclose, svolume, svalue, delqty, avgprice, f5min, f15min, l5min, l15min). - Types: stored as float32 like the table's
REALcolumns, so verification compares exactly. - Compression: ZSTD.
- Writes are atomic: each file is written to a
.tmpand then renamed. - File names: a symbol that's a reserved Windows name (
CON,PRN, …) gets a trailing_. Symbols not starting with a letter go in the#folder.
Reading — always through the API
| Function | Returns |
|---|---|
get_eod(symbol_or_masid, frm, to, adjust='none', follow_renames=False) | One stock's rows from Parquet plus staging. Staging wins on a date that's in both. adjust='price' / 'total' applies corporate actions, and follow_renames joins earlier symbols. See corp_actions.py. |
price_windows(pairs, days) / price_moves(down, up, persist) | DuckDB scans used by corp_actions.reconcile: rows around ex-dates, and large held day-over-day moves |
get_returns_panel(frm, to, adjust='total', typ=(1,), max_gap_days=None) | Daily returns of every stock in one DuckDB scan, as a date × stock table. Same adjustment as get_eod, with renamed symbols joined into one column (key = the family's lowest masid). ETFs (ISIN INF…) excluded. Also returns raw traded value. Used by fo_effect. Checked against get_eod on 8 stocks, 2008–2026: identical to 1e-7. |
rename_families() | {masid: family key} over applied symbol changes |
get_day(date) | Every stock on one date. Staging when the date is still there, otherwise one DuckDB scan of the store. |
available_days(frm, to) | sdate, rows for every date (catalog + staging) |
masids_with_data(frm, to) | Stocks with at least one row in the range |
all_masids() | Every stock with any EOD row |
Callers switched on 2026-09-27:
app/services/chart_service.py: the equity/index symbol lists (cached 10 minutes), and the equity 1D/1W/1M/1Y chart, now aggregated in pandas the same way as SQLDATE_TRUNC(week starts Monday).nseieod/nseIEOD.py: ATM spot close.nseieod/audit.py: spot closes and spot OHLC.nseieod/ut_Classes.py: equity EOD anchors.nseieod/eodieod/precheck.py: equity dates.nseieod/eodieod/convert.py: symbols in range.nseieod/orphan.py: masids with EOD.
Queries that only need the latest rows (market summary, watchlist, latest date,
bhav.py AMI export) stay on scr_nseeq_eod.
Writing
cd backend
python -m nseeod.eq_store export # full rebuild from scr_nseeq_eod (run once, after the backfill)
python -m nseeod.eq_store append --days 5 # merge the last N staging dates into the files
python -m nseeod.eq_store verify # Parquet vs Postgres for every shared date
python -m nseeod.eq_store stats
python -m nseeod.eq_store trim --keep 90 # dry run; --apply deletes only VERIFIED older dates
- Daily: after each successful equity day,
EquityBhav.update()callseq_store.append_days([date]). This is best-effort, logged, and never fails the sync. It does nothing until the store has been built withexport, so an unbuilt store never gets one-day files. - Trim: refuses to delete if any date to be removed doesn't match its Parquet copy exactly (row count, sum of closes, sum of volumes).
Rollout order
- The backfill finishes and is verified (eq_backfill.py).
export, thenverify(must report 0 mismatched), then check charts and tools.- The same on the VPS after its backfill.
- Only then run
trim --keep 90 --applyon each database.