Financial Terminal & Swedish Index Funds ETL

Summary

I built a production ETL pipeline and an interactive financial terminal for Swedish index fund holdings. The system automatically ingests quarterly XML reports from Finansinspektionen (FI), normalizes ~1.79 million holdings across 941 funds, and exposes them via a FastAPI + HTMX dashboard with fast server‑side filtering.

This removes manual gathering and ad‑hoc spreadsheets, giving investors and analysts a consistent, queryable view of sector exposure, market cap distribution, QoQ deltas, and fund performance each quarter.

Impact / Why It Matters

Eliminates manual data collection – No more downloading and merging FI XMLs by hand; the pipeline handles new quarters automatically.
Standardized analytics – 12‑table schema with materialized views for sector, market cap, country exposure, performance, and QoQ changes.
Fast, lightweight UI – Sub‑10ms server‑side filtering over millions of rows using HTMX + FastAPI, no heavy client framework.
Auditable & reproducible – Every ETL run is logged with file hashes, record counts, and status for traceability.

What I Built

Automated Quarterly ETL – Detects new FI zipped XMLs, parses with lxml, validates ISINs/currencies, and loads into PostgreSQL.
Analytical Schema – 12 structured tables + materialized views (e.g. analytics.mv_holdings_by_quarter) for sector allocation, market cap breakdown, fund performance, AUM growth, and QoQ deltas.
FastAPI + HTMX Terminal – Interactive dashboard for searching funds, inspecting holdings, and comparing sector/market cap profiles with instant filtering.
Dockerized Deployment – Full stack in Docker Compose (PostgreSQL + backend + dashboard) for easy local and server deployment.
Observability – control.etl_log tracks quarter, file hash, record counts, and run status for each pipeline execution.

Results & Evidence

Scale & coverage:

~1.79 million historical and active holdings records.
941 active and historical Swedish index funds.
12 relational tables + analytical views (sector, market cap, country, performance, QoQ, diagnostics).

Performance & usability:

Dashboard supports sub‑10ms server‑side filtering of holdings across millions of records (no heavy client‑side JS).
Materialized views and B‑tree indexes on ISINs and fund names keep common queries fast as data grows.

Operational behavior:

New FI quarters are picked up automatically by a scheduled task (cron/systemd/Docker scheduler) and reflected in the dashboard after the materialized view refresh.
Manual re‑runs are possible for testing/backfill:
1
python3 etl/run_pipeline.py --quarter latest

Tech & Methods

Backend: FastAPI, Uvicorn, Python 3.11+
Frontend: HTMX, HTML5, Jinja2 templates, CSS
Database: PostgreSQL 15+, SQLAlchemy ORM, materialized views, B‑tree indexes
Data Processing: Pandas, lxml (XML), PyYAML (config)
DevOps: Docker, Docker Compose, scheduled ETL jobs
Data Source: Finansinspektionen quarterly fund holdings (zipped XML, UCITS/special funds)

Limits & Next Steps

Validated:

End‑to‑end ETL from FI XML → PostgreSQL → dashboard.
Correct parsing, normalization, and logging of quarterly runs.
Fast, responsive UI for fund/holdings exploration.

Not (yet) validated:

Multi‑user load testing under heavy concurrent usage.
Advanced alerting/monitoring (e.g., Prometheus/Grafana) for ETL health.
Extended analytics (e.g., factor exposures, peer group benchmarking).

Next steps:

Add monitoring dashboards for ETL latency, failures, and data freshness.
Expand analytics (factor tilts, tracking difference vs. benchmarks).
Harden scheduling and backfill tooling for multi‑year reprocessing.

Usage

1
2
3
4
5
6
7
8
# Start PostgreSQL + services
docker-compose up -d

# Start Financial Terminal Dashboard
python3 dashboard_main.py

# Trigger quarterly ETL (manual test / reprocess)
python3 etl/run_pipeline.py --quarter latest

Access the dashboard at http://localhost:8000.