An end-to-end analytics pipeline built on real NHS England Trust Accounts Consolidation data — 206 trusts, three financial years, one queryable warehouse
View on GitHub ↗The NHS sector moved from a £1.6bn surplus in 2021/22 to a £1.6bn deficit in 2023/24 — a £3.2bn swing in two years. NHS England publishes the underlying data, but only as six separate Excel workbooks a year in a long/narrow format designed for archival completeness, not analysis. There is no consolidated, multi-year, trust-level view anywhere in the public data as published.
This project consolidates three years of Trust Accounts Consolidation (TAC) data — six workbooks, 206 organisations — into a single MySQL star schema, computes core NHS financial KPIs against NHS England's own published RAG thresholds, and delivers the result as a 5-page Power BI dashboard usable by a non-technical finance director or board, with every figure traceable back to its exact source file and schedule.
Python pipeline ingesting 6 Excel files (~170MB), medallion architecture (bronze → silver → gold in MySQL), 10-query data-quality validation suite
Star schema (dim_trust, dim_financial_year, dim_subcode → fct_tac), curated analytical views (I&E, P&L, balance sheet, KPIs)
NHS Financial Reporting Manual (FReM) conventions, TAC subcode taxonomy, ODS organisation codes, EBITDA margin RAG thresholds
11-CSV export pipeline (no live DB connection required), 51 DAX measures across a 5-page interactive dashboard
The business question: has the NHS sector's financial position genuinely worsened — and which trusts are most exposed? Below is the full analysis behind that answer, pulled live from the source repository.
Full technical documentation, schema, SQL, and Power BI setup guide are on GitHub.