Skip to main content

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)

Not the same data as MCX spot

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.

masidsymbolnameunitArchive CSV (csvs/GlobalArchive/)Yahoo TickerFirst Date (live)
1XAUUSDXAUUSDUSD/ozGOLD.csvGC=F1973-01-03
2XAGUSDXAGUSDUSD/ozsilver.csvSI=F1968-01-02
3USDINRUSDINRINR/USDusdinr.csvINR=X1973-01-02
4DowJonesDowJonesINDEXDowjones.csv^DJI1896-05-26
5S&P500S&P500INDEXS&P500.csv^GSPC1950-01-03
6GOLDINRGOLDINRINR/10g(Derived from XAUUSD × USDINR)(Derived)1973-01-03
7SILVERINRSILVERINRINR/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 from csvs/GlobalArchive/. Handles mixed date formats present across the archive files:
    • 4-digit years (MM/DD/YYYY, e.g. 05/26/1896 in Dowjones.csv)
    • 2-digit years (MM/DD/YY, mapped to 1950–1999 for 50–99 and 2000–2049 for 00–49)
    • Timestamp-suffixed strings (MM/DD/YY 00:00:00 in GOLD.csv and silver.csv for 2001–2011)
  • YahooFinanceAdapter: Queries https://query1.finance.yahoo.com/v8/finance/chart/{ticker} (interval=1d) starting from last_csv_or_db_date + 1 day through today.

2. Normalize Layer (normalize_ohlc)​

  • Converts sopen, shigh, slow, sclose to floating-point numeric columns.
  • Fills missing or non-positive sopen, shigh, or slow values with sclose.

3. Validate Layer (validate_ohlc)​

  • Bad dates: Filters out malformed date strings (such as '00:00:00' in Dowjones.csv) and any future-dated rows.
  • Bad prices: Drops rows where sclose is NULL, NaN, or <= 0.
  • OHLC consistency: Clamps shigh = max(sopen, shigh, slow, sclose) and slow = min(sopen, shigh, slow, sclose).
  • Deduplication: Resolves duplicate sdate rows (GOLD.csv has 2,696 duplicate rows and silver.csv has 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 (> 7 calendar 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 on UNIQUE (symbol).
    • scr_global_price: Upserted in batches of 2,000 rows on PRIMARY 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​

ColumnTypeNotes
masidSERIAL PRIMARY KEYInstrument ID
symbolTEXT UNIQUE NOT NULL'XAUUSD', 'XAGUSD', 'USDINR', 'DowJones', 'S&P500', 'GOLDINR', 'SILVERINR'
nameTEXT NOT NULLHuman-readable description
unitTEXT'USD/oz', 'INR/USD', 'INDEX', 'INR/10g', 'INR/1kg'
created_atTIMESTAMPTZ NOT NULL DEFAULT now()Row creation timestamp

scr_global_price​

ColumnTypeNotes
masidINTEGER FK -> scr_global_master.masidPart of composite PK (masid, sdate)
sdateDATE NOT NULLObservation date
sopenNUMERICOpen price/level
shighNUMERICHigh price/level
slowNUMERICLow price/level
scloseNUMERIC NOT NULLClose price/level
created_atTIMESTAMPTZ 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):

SymbolIndexYahooFrom
NASDAQ / NASDAQ100NASDAQ Composite / NASDAQ 100^IXIC / ^NDX1971 / 1985
RUSSELL1000 / RUSSELL2000 / RUSSELL3000Russell 1000 / 2000 / 3000^RUI / ^RUT / ^RUA1992 / 1987 / 1987
SP400 / SP600 / SP100S&P MidCap 400 / SmallCap 600 / 100^MID / ^SP600 / ^OEX1981 / 1989 / 1982
SP500EW / SP500TRS&P 500 Equal Weight / Total Return^SPXEW / ^SP500TR2006 / 1988
NYSENYSE Composite^NYA1965
SOXPHLX Semiconductor^SOX1994
DowTransports / DowUtilitiesDow Jones Transportation / Utility Average^DJT / ^DJU1992
VIXCBOE Volatility Index^VIX1990
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) runs us_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 because datetime.fromtimestamp() fails on Windows for the pre-1970 bars of ^NYA and ^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.py reads them from scr_global_price; the VIX is hidden from that list).
  • First run on a machine: run backfill once (about 15 Yahoo calls). Until then the Portfolios benchmark list falls back to a short live-Yahoo list.