Metadata-Version: 2.4
Name: nse-warehouse
Version: 0.1.0
Summary: NSE bhavcopy as a partitioned Parquet warehouse with a DuckDB query layer, corporate-action adjustment, and point-in-time-correct features.
Author-email: Siddharth Gaur <siddharthgaur200304@gmail.com>
License-Expression: MIT
Project-URL: Homepage, https://github.com/siddharthgaur1/nse-warehouse
Project-URL: Issues, https://github.com/siddharthgaur1/nse-warehouse/issues
Keywords: nse,bhavcopy,duckdb,parquet,indian-markets,equities
Classifier: Development Status :: 3 - Alpha
Classifier: Intended Audience :: Financial and Insurance Industry
Classifier: Programming Language :: Python :: 3
Classifier: Topic :: Office/Business :: Financial :: Investment
Requires-Python: >=3.10
Description-Content-Type: text/markdown
License-File: LICENSE
Requires-Dist: duckdb>=1.0
Requires-Dist: pyarrow>=15
Requires-Dist: httpx>=0.27
Provides-Extra: dev
Requires-Dist: pytest>=8; extra == "dev"
Provides-Extra: bench
Requires-Dist: pandas>=2.0; extra == "bench"
Requires-Dist: psutil>=5.9; extra == "bench"
Dynamic: license-file

# nse-warehouse

[![Portfolio](https://img.shields.io/badge/↩-siddharthgaur1-111827?style=flat-square)](https://github.com/siddharthgaur1)
[![ci](https://github.com/siddharthgaur1/nse-warehouse/actions/workflows/ci.yml/badge.svg)](https://github.com/siddharthgaur1/nse-warehouse/actions/workflows/ci.yml)
[![License: MIT](https://img.shields.io/badge/License-MIT-yellow.svg)](LICENSE)

NSE bhavcopy as a partitioned Parquet warehouse with a DuckDB query layer,
corporate-action adjustment, data-quality checks, and a point-in-time-correct
feature pipeline.

**No data is committed.** NSE publishes the bhavcopy; redistributing a
reconstructed copy of it is not something this repo does. What ships is the
pipeline and a documented build process — run it and you get your own warehouse.

![DuckDB query timings over 1,137,908 rows](docs/query-timings.png)

*Query timings from the committed `bench/results.json` (timings only, no prices). Regenerate the chart: `python bench/make_readme_chart.py`; the numbers themselves come from `bench/run_bench.py`.*

## Two things the measurements contradicted

Both were premises going in. Both turned out to be wrong, and the numbers are
below rather than quietly dropped.

### 1. Partitioning does not help at this scale

| Layout | Date-filtered | Full scan | Symbol-filtered | Size |
|---|---|---|---|---|
| Partitioned by year | 15.3 ms | 29.5 ms | 13.1 ms | 48.7 MB |
| **Unpartitioned single file** | **12.5 ms** | **24.3 ms** | **12.0 ms** | **42.3 MB** |

The single file wins on every query shape *and* is 13% smaller. More files means
less cross-file compression and more metadata to open, and with six
year-partitions over a mostly-contiguous span there is nearly nothing to prune.

Partitioning is still the default, but for a different and honest reason:
incremental rebuilds. Adding a year is a new directory rather than a rewrite.
It is not a query-speed optimisation here.

### 2. NSE equity data is not big enough to OOM pandas

The spec asked for "a query that would OOM in pandas running fine here". It does
not exist for this dataset, so no such demo is shipped.

Measured peak RSS for the same group-by:

| Engine | Peak RSS | Rows |
|---|---|---|
| DuckDB (streaming from Parquet) | **47.3 MB** | 1,137,908 |
| pandas (concat then group) | 506.4 MB | 1,137,908 |

That is a **10.7× memory ratio**, which is the real and defensible claim. But
extrapolated linearly, pandas would only exhaust 16 GB at ~36 M rows — roughly
**60 years** of NSE cash equities at current breadth. A full 20-year backfill is
~12 M rows and fits in pandas comfortably.

So the honest version of the scale claim is *"10× less memory and it streams"*,
not *"pandas cannot do this"*. Manufacturing an OOM by loading the data
badly would have been easy and dishonest.

## What was built and measured

Dataset actually built (see *Coverage* below — this is a sample, not a full
backfill):

| | |
|---|---|
| Rows (EQ/BE series) | 1,137,908 |
| Distinct symbols | 3,969 |
| Trading days | 497 |
| Date range | 2005-01-03 → 2026-08-05 |
| Raw ZIPs | 80.5 MB (497 files, 0 failures) |
| Parquet | 48.7 MB |

### Query timings

Median of 5 runs, warm cache, DuckDB 1.5.5, local NVMe.

| Query | Median | Rows |
|---|---|---|
| Point lookup (1 symbol, 1 day) | 12.0 ms | 1 |
| One symbol, full history | 15.1 ms | 497 |
| One day, full cross-section | 7.1 ms | 2,703 |
| One-year slice aggregate | 15.3 ms | 1 |
| Full-scan aggregate | 31.3 ms | 1 |
| Top movers, one day | 10.3 ms | 20 |
| Monthly turnover time series | 31.7 ms | 25 |
| Cross-sectional volatility ranking | 141.8 ms | 20 |

### Compression, measured rather than defaulted

| Codec | Size | Write | Scan |
|---|---|---|---|
| **zstd** (chosen) | 42.3 MB | 0.64 s | 9.5 ms |
| gzip | 41.4 MB | 1.82 s | 11.6 ms |
| snappy | 61.6 MB | 0.48 s | 6.9 ms |
| uncompressed | 101.0 MB | 0.47 s | 6.2 ms |

zstd gets within 2% of gzip's size at a third of the write cost and a faster
scan. snappy is 46% larger for 2.6 ms. zstd is the default because of this
table, not because it is fashionable.

## Corporate actions — the part most people get silently wrong

NSE's corporate-actions endpoint returns 403 to automated clients, so actions
are **inferred from the price series**.

The obvious approach fails, and it is worth knowing why. One might hope
`PrvsClsgPric` is already adjusted, making `prev_close[t] != close[t-1]` an
exact flag. It is not — checked against Reliance's 1:1 bonus, ex-date
2024-10-28:

```
2024-10-25   close = 2655.70   prev_close = 2679.60
2024-10-28   close = 1334.35   prev_close = 2655.70   <- raw prior close
```

`prev_close` carries no signal. The only usable one is the discontinuity:
`1334.35 / 2655.70 = 0.5024`, which snaps to 1/2.

**It works.** On the real warehouse, RELIANCE is detected correctly, and the
effect on returns is the whole point:

| | Return on 2024-10-28 |
|---|---|
| Raw close | **−49.76%** |
| Adjusted close | **+0.49%** |

Every momentum and volatility feature spanning that date is wrong without this.

### A bug the real data found

The first run detected **509** actions. Inspecting them showed the warehouse's
sampled date ranges (2005, 2012, 2020, 2024, 2025-26) created *seams*: the
"previous" close for 2012-03-01 was from 2005-01-31, producing ratios like 80×
that snapped to plausible-looking splits.

Detection is now gap-aware — pairs more than 7 calendar days apart are skipped —
which drops it to **230**. This is not just a sampling artefact fix: a genuinely
suspended stock resuming months later at a different price would have been
misread as a split in a full backfill too.

### Known limits, stated not implied

- **Dividends are not handled.** Ordinary dividend drops are indistinguishable
  from noise this way. These are **capital-adjusted**, not total-return prices.
- **Small bonuses are missed.** A 5:4 bonus (ratio 0.8) sits on the edge of the
  plausible-trading band and is not reliably caught.
- **False positives remain.** Rights entitlements (`-RE` suffix) are volatile
  instruments that trip the detector; `SUMEET-RE` produced hits on two
  consecutive days, which is a near-certain tell that neither is real.
- Detected actions land in a `corporate_actions` table so they can be reviewed
  and overridden by an external feed later without rebuilding the raw layer.

## Data quality findings on the real data

`python -m nse_warehouse.cli check`. These are findings, not failures.

| Check | Result |
|---|---|
| OHLC consistency | **0 violations** across 1.14 M bars |
| Zero volume with turnover | **0** |
| Symbols whose ISIN changed | 354 — e.g. `YESBANK` has 3 |
| ISINs traded under several symbols | 269 — e.g. `INE200A01026` → ALSTOMT&D → AREVAT&D → GET&D → GVT&D |
| Symbols quiet >60 days | 1,238 (delistings, rights expiries, suspensions) |
| Breadth outliers | 19 days — all 2005, ~808 symbols vs a 2,372 median |

The breadth "outliers" are not errors: NSE listed ~800 names in 2005 and ~2,700
now. The check is measuring twenty years of market growth, which is the right
answer to a question phrased as an anomaly check.

`missing_trading_days` reports 5,136 — almost entirely the gaps *between* the
sampled ranges, not missing data. With a contiguous backfill this number becomes
meaningful; here it mostly measures the sampling plan.

## Coverage — what was actually downloaded

Not a full 20-year backfill. 497 trading days were fetched, chosen to exercise
every format era plus a contiguous recent span:

| Range | Purpose | Days |
|---|---|---|
| 2005-01 | Era 1: no ISIN, unpadded dates | 19 |
| 2012-03 | Era 2: ISIN column appears | 21 |
| 2020-01 | Era 2 mature | 23 |
| 2024-10 → 2024-11 | Era 3 (UDiFF) + Reliance bonus | 41 |
| 2025-01 → 2026-08 | Contiguous recent span | 393 |

Zero download failures. The full backfill is one command
(`cli fetch --start 1996-01-01 --end 2026-08-05`) and roughly 5,000 files; at
the built-in 1.5 s politeness interval that is ~2 hours of wall clock. It was
not run here, and no numbers in this README pretend it was.

## The three format eras

Mapped by fetching real files, not by reading documentation.

| Era | Range | Columns | Notes |
|---|---|---|---|
| 1 | ≤ ~2011 (verified from 1996-01-02) | 11 | **No ISIN, no trade count.** Unpadded day: `3-JAN-2005`. Trailing comma. |
| 2 | ~2012 → 2024-11 | 13 | `TOTALTRADES` and `ISIN` added; day now zero-padded |
| 3 | 2024-01 → present | 34 | UDiFF, ISO dates, `FinInstrmTp` mixes derivatives into the same file |

**The eras overlap**: both formats serve 2024-01 through 2024-11. The fetcher
prefers UDiFF there because it carries strictly more information. The legacy
path 404s from 2024-12.

Era 1's missing ISIN is not cosmetic — ISIN is the only stable identity across a
rename, so symbol-change tracking is impossible on pre-2012 data. The quality
check reports that limitation rather than working around it.

## Feature pipeline

Point-in-time correct, enforced rather than trusted. Every window ends at `t-1`
(`ROWS BETWEEN n PRECEDING AND 1 PRECEDING`), and `assert_no_lookahead()` fails
the build if any feature value reflects its own bar.

Built on **adjusted** prices: an unadjusted 1:1 bonus looks like a −50% return
and poisons every momentum and volatility feature spanning it.

```
training_set: 1,137,908 rows, 16 columns
no-lookahead assertion passed
```

Features: 5/21/63/252-day momentum, 21/63-day realised volatility, 21-day median
turnover, Amihud illiquidity, and cross-sectional percentile ranks computed
strictly within a single date. The 5-day forward return lives in a separate
`targets` view so it can never be mistaken for a feature.

## Install

```bash
pip install nse-warehouse
```

Python 3.10+. Runtime dependencies are `duckdb`, `pyarrow` and `httpx` — nothing
else. `pandas` is *not* required: the warehouse is queried through DuckDB, and
pandas appears only in `bench/` as the thing being measured against.

## Build it yourself

Installing gives you the `nse-warehouse` command. Every subcommand is also
reachable as `python -m nse_warehouse.cli <cmd>` if you prefer.

```bash
nse-warehouse fetch --start 2025-01-01 --end 2026-08-05
nse-warehouse build          # -> partitioned Parquet
nse-warehouse adjust         # -> corporate_actions + bars_adjusted
nse-warehouse check          # -> quality report
nse-warehouse features       # -> features / targets / training_set
nse-warehouse stats
```

Working from a clone instead, with the benchmark and test extras:

```bash
python -m venv .venv && .venv/bin/pip install -e ".[dev,bench]"
python -m bench.run_bench                  # -> bench/results.json
```

The fetcher is resumable and idempotent: files already on disk are never
re-requested, holidays are remembered in a manifest so a resume does not
re-probe them, and downloads are write-then-rename so a kill leaves no
half-file. Interrupt it freely.

Politeness is not configurable below 0.5 s per request. This is a public archive.

## Licence and data position

Code: MIT.

**NSE bhavcopy data is NSE's.** This repo ships the fetcher and the build
process, never the data. If you redistribute a built warehouse, that is between
you and NSE's terms.

## Not done

- Full 1996→2026 backfill (pipeline ready, ~2 hours, not run).
- Wiring into `nifty-forecaster` as a consumer — the feature views exist and are
  tested, but the integration is not written.
- An authoritative corporate-actions feed to replace the heuristic.
