Skip to main content

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_eod becomes 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 REAL columns, so verification compares exactly.
  • Compression: ZSTD.
  • Writes are atomic: each file is written to a .tmp and 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​

FunctionReturns
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 SQL DATE_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() calls eq_store.append_days([date]). This is best-effort, logged, and never fails the sync. It does nothing until the store has been built with export, 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​

  1. The backfill finishes and is verified (eq_backfill.py).
  2. export, then verify (must report 0 mismatched), then check charts and tools.
  3. The same on the VPS after its backfill.
  4. Only then run trim --keep 90 --apply on each database.