Index-funds-etl: FI & Swedish Index Funds

Production ETL pipeline + real-time financial terminal for Swedish index fund holdings. Automates quarterly FI XML ingestion, transforms 1.79M holdings across 941 funds into 12 analytical tables, and serves instant sector/market-cap analysis via FastAPI + HTMX.
Tech Stack: Python FastAPI HTMX Jinja2 SQLAlchemy PostgreSQL Pandas LXML Docker

Technical Overview

The system automates the processing of Finansinspektionen’s (FI) quarterly zipped XML reports, converting raw asset filings into normalized, structured datasets. It consolidates portfolio holdings by sector, market capitalization, and performance metrics across historical reporting periods.

Operating as a dual-mode solution, it functions both as an automated background ETL pipeline for recurring quarterly ingestion and as a self-hosted FastAPI + HTMX financial terminal for sub-10ms portfolio queries and filtering.

Core Features & Architecture

  • Automated Quarterly ETL (Extract–Transform–Load):

    • Extract: Detects and downloads FI’s newly published quarterly zipped XML files (e.g. Fondinnehav 2024Q4 ... .zip).
    • Transform: Cleans and validates data (ISINs, currencies, duplicates), categorizes securities by sector and market cap, and computes derived metrics (fund performance, AUM growth, QoQ deltas).
    • Load: Persists results into PostgreSQL with materialized views (analytics.mv_holdings_by_quarter) and B-tree indexes on ISINs and fund names.

  • Real-time Financial Terminal (FastAPI + HTMX):

    • Sub-10ms server-side filtering and search across millions of holdings.
    • Instant sector exposure, market-cap breakdowns, and fund-level analytics without heavy client-side JavaScript.

  • Schema & Scale:

    • ~1.79 million holdings records
    • 941 Swedish index funds (active + historical)
    • 12-table analytical schema (holdings, sector allocation, market cap breakdown, performance, QoQ deltas, country exposure, asset pricing, ETL logs, etc.)

  • Infrastructure: Fully containerized via Docker and Docker Compose for reproducible deployment and database setup.

Automated Quarterly Execution (FI Reports)

The pipeline is designed around FI’s quarterly reporting cycle and handles new releases automatically.

  1. Detection & Extraction: A background/scheduled task checks FI’s public register for newly published quarterly zipped XML reports.
  2. Ingestion & Validation: Parses XML payloads, standardizes currencies, verifies asset identifiers (ISINs), and updates control.etl_log with run metadata and checksums.
  3. Materialized View Refresh: Automatically refreshes analytics.mv_holdings_by_quarter to reflect the latest quarter end (quarter_end = MAX(quarter_end)).
  4. Dashboard Auto-Update: The FastAPI/HTMX terminal immediately serves the newly published quarter data on the next page refresh or search query.

Real-World Scale & Validation

  • Data Volume: ~1.79M holdings records processed and stored.
  • Fund Coverage: 941 Swedish index funds (active + historical).
  • Analytical Output: 12 structured tables/views enabling sector/market-cap breakdowns, country exposure, asset pricing, fund performance metrics, and QoQ deltas.

Tech Stack & Specs

  • Backend Framework: Python 3.11+, FastAPI, Uvicorn
  • Frontend / UI: HTMX, HTML5, Jinja2 Templates, CSS
  • Database & ORM: PostgreSQL 15+, SQLAlchemy (with materialized views and indexing)
  • Data Processing: Pandas, LXML (XML parsing), PyYAML (config)
  • Deployment: Docker, Docker Compose; optional scheduler for automated quarterly runs

Live Product & Links

finance index-funds automation data-engineering etl-pipeline fastapi htmx containerization