Global Archive & Yahoo Finance Ingest Pipeline
Module: backend/AA/global_ingest.py
PostgreSQL Tables: scr_global_master, scr_global_price
Parquet Store: data/global/{SYMBOL}.parquet
Status: Active (reactivated 2026-09-30, wired into the MCX Bhav Downloader 2026-10-01 — see "Daily update" below)
scr_global_master/scr_global_price hold international/global benchmark
prices (XAUUSD, XAGUSD, USDINR, DowJones, S&P500 + derived GOLDINR/SILVERINR).
This is not MCX's own domestic spot data (scr_mcxsp_eod,
backend/mcxeod/) — the two tables serve different purposes and must never be
conflated. See AA/CLAUDE.md rule 7 and mcxeod/CLAUDE.md rule 20.
Architecture Overview
The pipeline ingests historical global market and bullion archives from csvs/GlobalArchive/, automatically tops them up to the current date via the Yahoo Finance v8 Chart API, normalizes and validates every bar, writes immutable Parquet snapshots, populates PostgreSQL (scr_global_master and scr_global_price), and derives GOLDINR (per 10 Grams, matching MCX Gold quote) and SILVERINR (per 1 KG, matching MCX Silver quote).
┌───────────────────┐ ┌───────────────────┐
│ GlobalArchive CSV │──────►│ │
└───────────────────┘ │ Provider Adapter │
┌───────────────────┐ │ (CSV + Yahoo API) │
│ Yahoo Finance │──────►│ │
└───────────────────┘ └─────────┬─────────┘
│
▼
┌───────────────────┐
│ Normalize │
│ (sdate, sopen, │
│ shigh, slow, │
│ sclose) │
└─────────┬─────────┘
│
▼
┌───────────────────┐
│ Validate │
│ │
│ - duplicates │
│ - bad prices │
│ - OHLC clamping │
│ - gaps │
└─────────┬─────────┘
│
┌──────────────┴──────────────┐
▼ ▼
Raw Parquet PostgreSQL
data/global/{SYMBOL}.parquet scr_global_master
immutable scr_global_price
│
▼
┌───────────────────┐
│ Derived Series │
│ - GOLDINR (10 g) │
│ - SILVERINR (1kg) │
└───────────────────┘
Tracked Series (scr_global_master)
Confirmed against the live DB (2026-10-01): 7 rows, 124,753 price rows total.
masid | symbol | name | unit | Archive CSV (csvs/GlobalArchive/) | Yahoo Ticker | First Date (live) |
|---|---|---|---|---|---|---|
1 | XAUUSD | XAUUSD | USD/oz | GOLD.csv | GC=F | 1973-01-03 |
2 | XAGUSD | XAGUSD | USD/oz | silver.csv | SI=F | 1968-01-02 |
3 | USDINR | USDINR | INR/USD | usdinr.csv | INR=X | 1973-01-02 |
4 | DowJones | DowJones | INDEX | Dowjones.csv | ^DJI | 1896-05-26 |
5 | S&P500 | S&P500 | INDEX | S&P500.csv | ^GSPC | 1950-01-03 |
6 | GOLDINR | GOLDINR | INR/10g | (Derived from XAUUSD × USDINR) | (Derived) | 1973-01-03 |
7 | SILVERINR | SILVERINR | INR/1kg | (Derived from XAGUSD × USDINR) | (Derived) | 1973-01-02 |
symbol/name for the raw USD metal series were renamed GOLD→XAUUSD,
Silver→XAGUSD on 2026-10-01 — standard international spot-ticker
convention, chosen so these read unambiguously as the international
benchmark and are never confused with an MCX-traded contract. The Archive CSV
filenames and Yahoo tickers are unchanged (those are external source
identifiers, not the canonical symbol). DowJones and S&P500 keep their
display-name spelling exactly as written here (note the actual Parquet
filenames are DowJones.parquet / S&P500.parquet, not
DOWJONES.parquet/SP500.parquet).
Pipeline Stages
1. Provider Adapters
CsvArchiveAdapter: Reads historical CSV files fromcsvs/GlobalArchive/. Handles mixed date formats present across the archive files:- 4-digit years (
MM/DD/YYYY, e.g.05/26/1896inDowjones.csv) - 2-digit years (
MM/DD/YY, mapped to1950–1999for50–99and2000–2049for00–49) - Timestamp-suffixed strings (
MM/DD/YY 00:00:00inGOLD.csvandsilver.csvfor2001–2011)
- 4-digit years (
YahooFinanceAdapter: Querieshttps://query1.finance.yahoo.com/v8/finance/chart/{ticker}(interval=1d) starting fromlast_csv_or_db_date + 1 daythrough today.
2. Normalize Layer (normalize_ohlc)
- Converts
sopen,shigh,slow,scloseto floating-point numeric columns. - Fills missing or non-positive
sopen,shigh, orslowvalues withsclose.
3. Validate Layer (validate_ohlc)
- Bad dates: Filters out malformed date strings (such as
'00:00:00'inDowjones.csv) and any future-dated rows. - Bad prices: Drops rows where
scloseisNULL,NaN, or<= 0. - OHLC consistency: Clamps
shigh = max(sopen, shigh, slow, sclose)andslow = min(sopen, shigh, slow, sclose). - Deduplication: Resolves duplicate
sdaterows (GOLD.csvhas 2,696 duplicate rows andsilver.csvhas 731 duplicate rows between 2001 and 2011 where both a full OHLC bar and a flat close-only bar exist), prioritizing the full OHLC bar (shigh > slow). - Gap detection: Counts and reports any multi-day gaps (
> 7calendar days) across the series.
4. Derived INR Bullion Calculation (compute_derived_inr_series)
Using the international troy ounce conversion constant:
1 Troy Ounce = 31.1034768 Grams
GOLDINR(INR per 10 Grams — unit convention matches how MCX quotes gold, NOT sourced from MCX):GOLDINR = XAUUSD × USDINR × (10 / 31.1034768)SILVERINR(INR per 1 KG = 1,000 Grams — unit convention matches how MCX quotes silver, NOT sourced from MCX):SILVERINR = XAGUSD × USDINR × (1000 / 31.1034768)
When a metal trading day falls on an FX holiday, pd.merge_asof(..., direction="backward") uses the most recent prior USDINR close (point-in-time forward-fill). Pure spot conversion is used without customs duty or tax markups.
5. Dual Storage (Parquet + PostgreSQL)
- Immutable Raw Parquet: Written to
data/global/{SYMBOL}.parquet— actual filenames on disk:XAUUSD.parquet,XAGUSD.parquet,USDINR.parquet,DowJones.parquet,S&P500.parquet,GOLDINR.parquet,SILVERINR.parquet. - PostgreSQL:
scr_global_master: Upserted onUNIQUE (symbol).scr_global_price: Upserted in batches of 2,000 rows onPRIMARY KEY (masid, sdate).
Daily update — wired into the MCX Bhav Downloader
As of 2026-10-01, this pipeline's daily refresh is not run standalone — every
time a user runs the MCX Bhav Downloader, mcxeod/mcx_downloader.py's
McxProcessor._sync_global() calls global_ingest.update_yesterday()
automatically (same three call sites _sync_spot() uses: run_update()'s
up-to-date branch, run_range_download(), and run_update_for_date()).
update_yesterday() always caps the Yahoo Finance fetch at yesterday's
closed price — never today's still-in-progress interval=1d candle, which
is a live, moving bar during market hours, not a settled EOD value. Use
update_yesterday() for any future automated/scheduled caller; reserve
run_pipeline() with no end_date (fetches through today) for the manual
python -m AA.global_ingest full/incremental run.
Logged into the same mcx_stats.json the MCX UI already polls, under tag
"GLOBAL" — a failure here (e.g. Yahoo Finance unreachable) is its own
FAILED history entry and never fails the MCX F&O sync itself. See
mcxeod/CLAUDE.md rule 20 and AA/CLAUDE.md rule 7.
Database Schema
scr_global_master
| Column | Type | Notes |
|---|---|---|
masid | SERIAL PRIMARY KEY | Instrument ID |
symbol | TEXT UNIQUE NOT NULL | 'XAUUSD', 'XAGUSD', 'USDINR', 'DowJones', 'S&P500', 'GOLDINR', 'SILVERINR' |
name | TEXT NOT NULL | Human-readable description |
unit | TEXT | 'USD/oz', 'INR/USD', 'INDEX', 'INR/10g', 'INR/1kg' |
created_at | TIMESTAMPTZ NOT NULL DEFAULT now() | Row creation timestamp |
scr_global_price
| Column | Type | Notes |
|---|---|---|
masid | INTEGER FK -> scr_global_master.masid | Part of composite PK (masid, sdate) |
sdate | DATE NOT NULL | Observation date |
sopen | NUMERIC | Open price/level |
shigh | NUMERIC | High price/level |
slow | NUMERIC | Low price/level |
sclose | NUMERIC NOT NULL | Close price/level |
created_at | TIMESTAMPTZ NOT NULL DEFAULT now() | Row creation timestamp |
CLI Usage
Always run from backend/ with the virtual environment activated:
cd backend
venv\Scripts\activate
# Full ingest: GlobalArchive CSVs + Yahoo Finance backfill to today + GOLDINR/SILVERINR
python -m AA.global_ingest
# Daily incremental update: fetch only new dates from Yahoo Finance and recompute GOLDINR/SILVERINR
python -m AA.global_ingest --yahoo-only
US market indices (AA/us_indices.py, 2026-10-23)
The S&P 500 and the Dow come from the pipeline above. The other main US indices are Yahoo-only series in the same two tables (unit = 'INDEX') and the same Parquet folder (data/global/{SYMBOL}.parquet):
| Symbol | Index | Yahoo | From |
|---|---|---|---|
NASDAQ / NASDAQ100 | NASDAQ Composite / NASDAQ 100 | ^IXIC / ^NDX | 1971 / 1985 |
RUSSELL1000 / RUSSELL2000 / RUSSELL3000 | Russell 1000 / 2000 / 3000 | ^RUI / ^RUT / ^RUA | 1992 / 1987 / 1987 |
SP400 / SP600 / SP100 | S&P MidCap 400 / SmallCap 600 / 100 | ^MID / ^SP600 / ^OEX | 1981 / 1989 / 1982 |
SP500EW / SP500TR | S&P 500 Equal Weight / Total Return | ^SPXEW / ^SP500TR | 2006 / 1988 |
NYSE | NYSE Composite | ^NYA | 1965 |
SOX | PHLX Semiconductor | ^SOX | 1994 |
DowTransports / DowUtilities | Dow Jones Transportation / Utility Average | ^DJT / ^DJU | 1992 |
VIX | CBOE Volatility Index | ^VIX | 1990 |
cd backend
python -m AA.us_indices backfill [--only NASDAQ,SOX] # whole history from each index's first day; safe to repeat (upsert)
python -m AA.us_indices sync # only rows after the last stored date
python -m AA.us_indices list # symbol, rows, first -> last date
- Daily:
global_ingest.update_yesterday()(called by the MCX Bhav Downloader) runsus_indices.sync()after the main series, best-effort (a failure is a log line, never a failed MCX sync). Always capped at yesterday's closed bar. - Own fetch:
fetch_yahoo()computes dates from the epoch becausedatetime.fromtimestamp()fails on Windows for the pre-1970 bars of^NYAand^GSPC. A 429 or network error is retried twice, then that symbol is logged as FAILED and the others continue. - Price indices: no dividends, except
SP500TR. They are used as benchmarks by Research > Portfolios (backend/portfolio/prices.pyreads them fromscr_global_price; the VIX is hidden from that list). - First run on a machine: run
backfillonce (about 15 Yahoo calls). Until then the Portfolios benchmark list falls back to a short live-Yahoo list.