Skip to main content

eq_backfill.py — Equity EOD History Rebuild

backend/nseeod/eq_backfill.py rebuilds scr_nseeq_eod date by date from a folder of NSE cash-market bhav copies and delivery (MTO) files. Each date is replaced completely.

Why it exists (Sept 2026): until then the table was complete only for 2026. Earlier years had a row on most days, but for very few stocks:

  • the median was 1 row per day for 1991–2006, and 2–94 rows for 2010–2021;
  • RELIANCE had 141 rows in 2025;
  • 59,869 rows had invalid OHLC.

Source backups (local PC)​

FolderContentsCoverage
D:\kss\stock\EODData\NSE_CM\{year}\One bhav copy per trading day: cmDDMMMYYYYbhav.csv (old format, to 2024-07-05) or BhavCopy_NSE_CM_0_0_0_YYYYMMDD_F_0000.csv (UDiFF)7,933 files, 1994-11-03 → 2026-09-25, no gaps
D:\kss\stock\EODData\NSE_MTO\{year}\MTO_DDMMYYYY.DAT security-wise delivery position6,146 files, 2002-01-01 → 2026-09-25 (NSE publishes nothing earlier; 6 dates in 2002–03 were never published)

Each folder has a manifest.csv (date, file, rows or records, source, SHA-1) and a README.txt.

How they were built:

  • Sources: existing folders and tradeentryscrbhavcopies.rar, the VPS disk (June–Sept 2026), and NSE's archive for the rest (2018–2019, late 1994, scattered days).
  • Checks on every file: the date inside the file matches its name, and it has rows. NSE error pages saved as .DAT were rejected and downloaded again (6 local, 5 on the VPS).

Usage​

Run from backend/ on the machine whose database you want to fill. The loader writes through pgs.conn().

# dry run: what would load, and how many existing rows would be replaced
python -m nseeod.eq_backfill --cm-dir /home/kss/EODData/NSE_CM --mto-dir /home/kss/EODData/NSE_MTO

# load (all dates, or a range)
python -m nseeod.eq_backfill --cm-dir ... --mto-dir ... --apply
python -m nseeod.eq_backfill --cm-dir ... --mto-dir ... --from 2018-01-01 --to 2019-12-31 --apply

How it works​

  • Same ingest as the daily Bhav Downloader. It calls utils.process_and_update_EqBhav (old format) or utils.new_process_and_update_EqBhav (UDiFF). History therefore follows the same series policy (EQ_SERIES_TYP_MAP), global-mname resolution, ISIN sync and delivery merge as daily data (nseeod/CLAUDE.md rule 2). Symbols not yet in scr_master are inserted with their series' typ.
  • One-time snapshot. Before the first write, the whole table is copied to scr_nseeq_eod_pre_backfill. Drop it by hand once you're satisfied.
  • Only stock rows are replaced. The delete (and the failure-restore) covers only typ 1, 10 and 11, the values in EQ_SERIES_TYP_MAP. Index rows (typ 2, and typ 8 G-Sec indices) are never touched. They come from the separate index file through EquityBhav's indexupdater, which this tool doesn't run. The first local run deleted by date only and wiped 216,750 index rows; they were restored from the snapshot.
  • Per date: delete the date's stock rows, load the file, then count the stock rows. If the load fails or writes 0 rows, the date is restored from the snapshot and listed in logs/eq_backfill_failed.txt.
  • Resumable. Finished dates are appended to logs/eq_backfill_done.txt and skipped next time. Delete that file to redo everything.
  • Logging: progress every 250 dates. Ingest messages are counted and printed once at the end, never per day.

Test results on 7 representative dates (1995 old format, 2002 bare delivery layout, 2011 without ISIN, 2012, 2018, 2025 UDiFF, 2026):

  • the row count matched the source file exactly every time (EQ/BE/SM/ST/BZ/RR with volume > 0);
  • 0 invalid OHLC rows;
  • delivery quantity filled from 2002 onward.

Local run (2026-09-27)​

  • 7,933 dates, 0 failures. The full run took 51 minutes; the 1995–2003 reload took 7 minutes.
  • Result: 11,445,725 rows (stocks plus restored index rows).
  • Checks: 0 duplicate stock/date pairs, 0 invalid OHLC rows. Delivery quantity is present on 87–97% of rows from 2002.
  • Parquet: the export matched Postgres on all 8,744 dates (eq_store.py).

Fixes made alongside (2026-09-26/27)​

WhereProblemFix
utils.parse_delivery_file / merge_delivery (new)The inline MTO parsers assumed the titled layout (skip 4 lines; symbol, series and quantity in fields 2, 3 and 5). January-2002 files use a bare layout (20,SYMBOL,SERIES,QTY), so they silently got delqty = 0.One shared parser keyed on record type 20 handles both layouts. Both bhav functions use it.
utils.process_and_update_EqBhavOld bhav copies before mid-2011 have no ISIN column, which would raise KeyError (never hit before, because the daily path only sees new files).Adds an empty isin column when it's missing.
utils.one_row_per_symbol (new)In 1996–2002 a stock often traded in both EQ (physical) and BE (demat) on the same day, at different prices. Both map to typ = 1, so the table got two rows per stock per day (~55k pairs).Keep one row per symbol per day: EQ > SM > RR > BE > ST > BZ, then the higher volume. Used by both ingest functions.
utils.repair_ohlc (new)NSE's early files have the open outside high–low (Oct 1995 – Apr 1997) and open = 0 (1996–2003): 31,141 rows.Repair and log (decided 2026-09-27): high/low = 0 → rebuilt from open/close; high < low → swapped; open = 0 → previous close; open and close clamped into [low, high]. Every change is appended to LOG_PATH/eq_ohlc_repairs.csv (copied to NSE_CM/ohlc_repairs.csv). The original files stay untouched in the backup.
utils.requestnsefileSaved requests.get(url).text whatever the response, so NSE error pages became MTO_*.DAT and those days got delqty = 0.Stops on 404, and only saves a real delivery report (a 10,MTO, or "Security Wise Delivery Position" header).
NSE calendar (holidays / specialdays, exch 1)Didn't match NSE's actual trading days.+54 holidays (weekdays NSE published no bhav copy for, 1994–2007). +7 weekend sessions (2006, 2010, 2012). −2 day/month-swapped holidays (2008-09-12, 2010-05-11). −1 special day with no bhav copy (1999-07-11). Applied to both the VPS and local databases. The calendar now matches the bhav copy backup exactly.
updateholidays() replaces all NSE calendar rows from Holidays.csv / Muhurat.csv

Export first with GetHolidays() (SaveHolidays), so the CSVs include these fixes, before ever running it again.