Index Constituent Tracker — Survivorship Bias Free

Database: PostgreSQL (same DB as scr_master)

---

Overview

This system tracks which stocks belonged to which NSE index on any given date, enabling survivorship-bias-free backtesting. It handles:

• Index name changes (e.g. CNX 100 → NIFTY 100 in Jan 2016)
• Stock symbol renames (e.g. SESAGOA → VEDL, TATAGLOBAL → TATACONSUM)
• Historical membership snapshots loaded from NSE PDF files
• JOIN integration with the existing scr_master table

---

Architecture

NSE PDFs  →  PDF Parser (pdfplumber)  →  PostgreSQL
                                          ├── idx_index          (canonical index names)
                                          ├── idx_alias          (index name changes)
                                          ├── idx_constituent    (historical membership — CORE)
                                          └── idx_symbol_alias   (stock symbol renames)
                                                    ↕
                                               scr_master         (JOIN on symbol / mname)

---

Q1 — How to scrape data from NSE index PDFs?

Use pdfplumber — it handles NSE constituent PDFs cleanly.

pip install pdfplumber pandas

What the parser extracts

Field	Source in PDF	Example
Index name	Header line "Constituents of ..."	CNX 100
Snapshot date	Header line below	July 31, 2013
Symbol	Table column 1	RELIANCE
Security name	Table column 2	Reliance Industries Ltd.
Industry	Table column 3	REFINERIES
Close price	Table column 4	872.05
Index Mcap (₹ Cr)	Table column 5	143163
Weightage %	Table column 6	6.65

Core parser function

import pdfplumber
import re
from datetime import datetime

def parse_index_pdf(pdf_path: str) -> dict:
    result = {'index_name': None, 'as_of_date': None, 'constituents': []}

    with pdfplumber.open(pdf_path) as pdf:
        # Extract index name and date from first page header
        first_text = pdf.pages[0].extract_text()

        name_match = re.search(r'Constituents of (.+)', first_text)
        if name_match:
            result['index_name'] = name_match.group(1).strip()

        date_match = re.search(
            r'(January|February|March|April|May|June|July|August|'
            r'September|October|November|December)\s+(\d{1,2}),?\s+(\d{4})',
            first_text
        )
        if date_match:
            result['as_of_date'] = datetime.strptime(
                f"{date_match.group(1)} {date_match.group(2)} {date_match.group(3)}",
                "%B %d %Y"
            ).date()

        # Extract table rows from all pages
        for page in pdf.pages:
            for table in page.extract_tables():
                for row in table:
                    if not row or row[0] in ('Symbol', None, ''):
                        continue
                    result['constituents'].append({
                        'symbol':        row[0].strip(),
                        'security_name': (row[1] or '').strip(),
                        'industry':      (row[2] or '').strip(),
                        'close_price':   _safe_float(row[3]),
                        'index_mcap_cr': _safe_float(row[4]),
                        'weightage_pct': _safe_float(row[5]),
                    })

    return result


def _safe_float(val):
    if val is None:
        return None
    try:
        return float(str(val).strip().replace(',', '').replace('\n', ''))
    except ValueError:
        return None

Batch loader for a folder of PDFs

from pathlib import Path
import pandas as pd

def load_pdf_folder(folder: str) -> pd.DataFrame:
    all_rows = []
    for pdf_file in sorted(Path(folder).glob('*.pdf')):
        parsed = parse_index_pdf(str(pdf_file))
        canonical = resolve_index_name(parsed['index_name'])  # see Q2
        for c in parsed['constituents']:
            c['index_name'] = canonical
            c['as_of_date'] = parsed['as_of_date']
        all_rows.extend(parsed['constituents'])
    return pd.DataFrame(all_rows)

Note: File names don't matter. The index name and date are always extracted from the PDF content itself.

---

Q2 — Where to maintain name-change information?

Two separate tables handle two types of name changes.

Index name changes → idx_alias

Maps old index names to canonical names with date ranges.

CNX 100        →  NIFTY 100        (valid 2000-01-01 to 2016-01-31)
S&P CNX NIFTY  →  NIFTY 50         (valid 2000-01-01 to 2016-01-31)
CNX 500        →  NIFTY 500        (valid 2000-01-01 to 2016-01-31)

Stock symbol renames → idx_symbol_alias

SESAGOA    →  VEDL         (2015-06-01, MERGER)
CAIRN      →  VEDL         (2017-04-01, MERGER)
RANBAXY    →  SUNPHARMA    (2015-03-01, MERGER)
TATAGLOBAL →  TATACONSUM   (2020-02-01, RENAME)
CROMPGREAV →  CROMPTON     (2016-10-01, DEMERGER)
ABIRLANUVO →  ABCAPITAL    (2017-07-01, DEMERGER)
IDEA       →  VI           (2018-09-01, MERGER)

Python resolver (used at parse time)

INDEX_NAME_MAP = {
    'CNX NIFTY':        'NIFTY 50',
    'S&P CNX NIFTY':    'NIFTY 50',
    'CNX NIFTY JUNIOR': 'NIFTY NEXT 50',
    'CNX 100':          'NIFTY 100',
    'S&P CNX 100':      'NIFTY 100',
    'CNX 200':          'NIFTY 200',
    'CNX 500':          'NIFTY 500',
    'S&P CNX 500':      'NIFTY 500',
    'CNX MIDCAP':       'NIFTY MIDCAP 50',
    'CNX MIDCAP 100':   'NIFTY MIDCAP 100',
    'CNX SMALLCAP':     'NIFTY SMALLCAP 100',
    # ... add more as you encounter them
}

def resolve_index_name(pdf_name: str) -> str:
    canonical = INDEX_NAME_MAP.get(pdf_name)
    if canonical:
        return canonical
    cleaned = pdf_name.replace('S&P ', '').replace(' INDEX', '').strip()
    return INDEX_NAME_MAP.get(cleaned, pdf_name)

Workflow: Resolve the name in Python at parse time, then always insert using the canonical name. Your queries never need to know the old name.

---

Q3 — Database Schema (PostgreSQL DDL)

Table 1: idx_index — Master list of indices

CREATE TABLE IF NOT EXISTS idx_index (
    idx_id          SERIAL PRIMARY KEY,
    canonical_name  TEXT NOT NULL UNIQUE,   -- Current official name (e.g. 'NIFTY 100')
    description     TEXT,
    stock_count     INT,
    created_at      TIMESTAMP DEFAULT NOW()
);

INSERT INTO idx_index (canonical_name, description, stock_count) VALUES
    ('NIFTY 50',             'Top 50 by free-float mcap',      50),
    ('NIFTY NEXT 50',        'Stocks 51-100',                  50),
    ('NIFTY 100',            'Top 100 by free-float mcap',    100),
    ('NIFTY 200',            'Top 200 by free-float mcap',    200),
    ('NIFTY 500',            'Top 500 by free-float mcap',    500),
    ('NIFTY MIDCAP 50',      'Midcap 50',                      50),
    ('NIFTY MIDCAP 100',     'Midcap 100',                    100),
    ('NIFTY MIDCAP 150',     'Midcap 150',                    150),
    ('NIFTY SMALLCAP 50',    'Smallcap 50',                    50),
    ('NIFTY SMALLCAP 100',   'Smallcap 100',                  100),
    ('NIFTY SMALLCAP 250',   'Smallcap 250',                  250),
    ('NIFTY MICROCAP 250',   'Microcap 250',                  250),
    ('NIFTY LARGEMIDCAP 250','Largemidcap 250',               250),
    ('NIFTY MIDSMALLCAP 400','Midsmallcap 400',               400),
    ('NIFTY TOTAL MARKET',   'Total market index',           NULL)
ON CONFLICT (canonical_name) DO NOTHING;

---

Table 2: idx_alias — Index name changes

CREATE TABLE IF NOT EXISTS idx_alias (
    alias_id    SERIAL PRIMARY KEY,
    idx_id      INT NOT NULL REFERENCES idx_index(idx_id),
    old_name    TEXT NOT NULL,
    valid_from  DATE,
    valid_to    DATE,              -- NULL = still active
    UNIQUE (old_name, valid_from)
);

-- Seed: CNX → NIFTY rebrand (Jan 2016)
INSERT INTO idx_alias (idx_id, old_name, valid_from, valid_to) VALUES
    ((SELECT idx_id FROM idx_index WHERE canonical_name='NIFTY 50'),
        'CNX NIFTY',        '2000-01-01', '2016-01-31'),
    ((SELECT idx_id FROM idx_index WHERE canonical_name='NIFTY 50'),
        'S&P CNX NIFTY',    '2000-01-01', '2016-01-31'),
    ((SELECT idx_id FROM idx_index WHERE canonical_name='NIFTY NEXT 50'),
        'CNX NIFTY JUNIOR', '2000-01-01', '2016-01-31'),
    ((SELECT idx_id FROM idx_index WHERE canonical_name='NIFTY 100'),
        'CNX 100',           '2000-01-01', '2016-01-31'),
    ((SELECT idx_id FROM idx_index WHERE canonical_name='NIFTY 100'),
        'S&P CNX 100',       '2000-01-01', '2016-01-31'),
    ((SELECT idx_id FROM idx_index WHERE canonical_name='NIFTY 200'),
        'CNX 200',           '2000-01-01', '2016-01-31'),
    ((SELECT idx_id FROM idx_index WHERE canonical_name='NIFTY 500'),
        'CNX 500',           '2000-01-01', '2016-01-31'),
    ((SELECT idx_id FROM idx_index WHERE canonical_name='NIFTY 500'),
        'S&P CNX 500',       '2000-01-01', '2016-01-31'),
    ((SELECT idx_id FROM idx_index WHERE canonical_name='NIFTY MIDCAP 50'),
        'CNX MIDCAP',        '2000-01-01', '2016-01-31'),
    ((SELECT idx_id FROM idx_index WHERE canonical_name='NIFTY MIDCAP 100'),
        'CNX MIDCAP 100',    '2000-01-01', '2016-01-31'),
    ((SELECT idx_id FROM idx_index WHERE canonical_name='NIFTY SMALLCAP 100'),
        'CNX SMALLCAP',      '2000-01-01', '2016-01-31')
ON CONFLICT DO NOTHING;

---

Table 3: idx_constituent — Historical membership (CORE TABLE)

CREATE TABLE IF NOT EXISTS idx_constituent (
    constituent_id  SERIAL PRIMARY KEY,
    idx_id          INT NOT NULL REFERENCES idx_index(idx_id),
    as_of_date      DATE NOT NULL,      -- Snapshot date from PDF header
    symbol          TEXT NOT NULL,      -- NSE symbol as it appeared on that date
    security_name   TEXT,
    industry        TEXT,
    close_price     NUMERIC(12,2),
    index_mcap_cr   NUMERIC(14,2),      -- Market cap in Rs. Crores
    weightage_pct   NUMERIC(6,4),       -- Weightage %
    UNIQUE (idx_id, as_of_date, symbol)
);

-- Indexes for fast lookups
CREATE INDEX IF NOT EXISTS ix_constituent_symbol
    ON idx_constituent (symbol, as_of_date);
CREATE INDEX IF NOT EXISTS ix_constituent_date
    ON idx_constituent (idx_id, as_of_date);

---

Table 4: idx_symbol_alias — Stock symbol renames

CREATE TABLE IF NOT EXISTS idx_symbol_alias (
    sym_alias_id    SERIAL PRIMARY KEY,
    old_symbol      TEXT NOT NULL,
    new_symbol      TEXT NOT NULL,
    effective_date  DATE,
    reason          TEXT,   -- RENAME, MERGER, DEMERGER, DELISTED
    UNIQUE (old_symbol, effective_date)
);

INSERT INTO idx_symbol_alias (old_symbol, new_symbol, effective_date, reason) VALUES
    ('SESAGOA',    'VEDL',       '2015-06-01', 'MERGER'),
    ('CAIRN',      'VEDL',       '2017-04-01', 'MERGER'),
    ('RANBAXY',    'SUNPHARMA',  '2015-03-01', 'MERGER'),
    ('MCDOWELL-N', 'UBBL',       '2013-07-01', 'RENAME'),
    ('TATAGLOBAL', 'TATACONSUM', '2020-02-01', 'RENAME'),
    ('CROMPGREAV', 'CROMPTON',   '2016-10-01', 'DEMERGER'),
    ('ABIRLANUVO', 'ABCAPITAL',  '2017-07-01', 'DEMERGER'),
    ('IDEA',       'VI',         '2018-09-01', 'MERGER'),
    ('JPASSOCIAT', 'JAYPEE',     '2014-01-01', 'RENAME'),
    ('RCOM',       'RCOM',       '2020-12-01', 'DELISTED')
ON CONFLICT DO NOTHING;

---

Optional: Add nse_symbol to scr_master

ALTER TABLE scr_master ADD COLUMN IF NOT EXISTS nse_symbol TEXT;
UPDATE scr_master SET nse_symbol = mname WHERE typ = 0;

---

View: v_index_constituent (resolves current symbol automatically)

CREATE OR REPLACE VIEW v_index_constituent AS
SELECT
    ii.canonical_name   AS index_name,
    ic.as_of_date,
    ic.symbol,
    COALESCE(sa.new_symbol, ic.symbol) AS current_symbol,
    ic.security_name,
    ic.industry,
    ic.close_price,
    ic.index_mcap_cr,
    ic.weightage_pct
FROM idx_constituent ic
JOIN idx_index ii ON ii.idx_id = ic.idx_id
LEFT JOIN LATERAL (
    SELECT new_symbol
    FROM idx_symbol_alias
    WHERE old_symbol = ic.symbol
      AND effective_date <= CURRENT_DATE
    ORDER BY effective_date DESC
    LIMIT 1
) sa ON TRUE;

---

Sample Queries

Query 1 — How many indices was RELIANCE part of on 1 Jan 2015?

SELECT ii.canonical_name, ic.as_of_date, ic.weightage_pct
FROM idx_constituent ic
JOIN idx_index ii ON ii.idx_id = ic.idx_id
WHERE ic.symbol = 'RELIANCE'
  AND ic.as_of_date = (
      SELECT MAX(as_of_date)
      FROM idx_constituent
      WHERE symbol = 'RELIANCE'
        AND as_of_date <= '2015-01-01'
  )
ORDER BY ii.canonical_name;

---

Query 2 — NIFTY 500 constitution in Jan 2014?

SELECT ic.symbol, ic.security_name, ic.industry,
       ic.close_price, ic.index_mcap_cr, ic.weightage_pct
FROM idx_constituent ic
JOIN idx_index ii ON ii.idx_id = ic.idx_id
WHERE ii.canonical_name = 'NIFTY 500'
  AND ic.as_of_date BETWEEN '2014-01-01' AND '2014-01-31'
ORDER BY ic.weightage_pct DESC;

---

Query 3 — Track a stock's full index membership history

SELECT ii.canonical_name, ic.as_of_date,
       ic.weightage_pct, ic.close_price
FROM idx_constituent ic
JOIN idx_index ii ON ii.idx_id = ic.idx_id
WHERE ic.symbol = 'RELIANCE'
ORDER BY ic.as_of_date, ii.canonical_name;

---

Query 4 — Which stocks entered/exited an index between two dates?

WITH prev AS (
    SELECT symbol FROM idx_constituent ic
    JOIN idx_index ii ON ii.idx_id = ic.idx_id
    WHERE ii.canonical_name = 'NIFTY 100'
      AND ic.as_of_date = '2014-01-31'
),
curr AS (
    SELECT symbol FROM idx_constituent ic
    JOIN idx_index ii ON ii.idx_id = ic.idx_id
    WHERE ii.canonical_name = 'NIFTY 100'
      AND ic.as_of_date = '2014-07-31'
)
SELECT 'ADDED'   AS action, c.symbol
FROM curr c LEFT JOIN prev p ON p.symbol = c.symbol WHERE p.symbol IS NULL
UNION ALL
SELECT 'REMOVED' AS action, p.symbol
FROM prev p LEFT JOIN curr c ON c.symbol = p.symbol WHERE c.symbol IS NULL
ORDER BY action, symbol;

---

Query 5 — Backtest universe: join with scr_master

SELECT sm.masid, sm.mname, ic.symbol, ic.close_price, ic.weightage_pct
FROM idx_constituent ic
JOIN idx_index ii ON ii.idx_id = ic.idx_id
JOIN scr_master sm ON sm.mname = ic.symbol AND sm.typ = 0
WHERE ii.canonical_name = 'NIFTY 100'
  AND ic.as_of_date = (
      SELECT MAX(as_of_date)
      FROM idx_constituent ic2
      JOIN idx_index ii2 ON ii2.idx_id = ic2.idx_id
      WHERE ii2.canonical_name = 'NIFTY 100'
        AND ic2.as_of_date <= '2015-01-15'
  )
ORDER BY ic.weightage_pct DESC;

---

Query 6 — Available snapshot dates for an index

SELECT DISTINCT ic.as_of_date, COUNT(*) AS stock_count
FROM idx_constituent ic
JOIN idx_index ii ON ii.idx_id = ic.idx_id
WHERE ii.canonical_name = 'NIFTY 100'
GROUP BY ic.as_of_date
ORDER BY ic.as_of_date;

---

Q4 — PostgreSQL or DuckDB?

Use PostgreSQL.

Criterion	PostgreSQL	DuckDB
Already has scr_master	✅ Yes	❌ No
Relational JOINs	✅ Native	✅ But no FK enforcement
Foreign key constraints	✅ Yes	❌ No
LATERAL joins (for alias resolution)	✅ Yes	⚠️ Partial support
Write-once-read-many	✅ Ideal	✅ Good
Backtest JOIN with scr_master	✅ Same DB, fast	❌ Cross-DB join needed

DuckDB is ideal for read-heavy analytics on Parquet files — which is why liquid.py uses it for the catalog. The index constituent data is a write-once relational workload that needs tight scr_master integration, so PostgreSQL is the right home.

---

File Deliverables

File	Purpose
index_tracker_ddl.sql	Run in PostgreSQL to create all tables, seed data, and view
index_constituents.py	Full Python module: parser, resolver, batch loader, insert function, sample queries

---

Workflow Summary

1. Download index PDFs from nseindia.com
         ↓
2. run parse_index_pdf("path.pdf")
         ↓
3. resolve_index_name() maps CNX → NIFTY names
         ↓
4. insert_constituents_pg(df, conn) loads into PostgreSQL
         ↓
5. Query v_index_constituent for any date range
         ↓
6. JOIN scr_master.mname = idx_constituent.symbol
   for survivorship-bias-free backtest universes

---