NHS Trust Financial Analytics

An end-to-end analytics pipeline built on real NHS England Trust Accounts Consolidation data — 206 trusts, three financial years, one queryable warehouse

Overview

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.

Key Findings

206
NHS Trusts and Foundation Trusts covered
2.18M
Rows in the consolidated fact table
124 of 206
Trusts in deficit by 2023/24, up from 37 in 2021/22
51
DAX measures across the 5-page dashboard

Tech Stack & Skills Demonstrated

Data Engineering

  • Python pipeline ingesting 6 Excel files (~170MB)
  • Medallion architecture: bronze → silver → gold in MySQL
  • 10-query data-quality validation suite

Dimensional Modelling & SQL

  • Star schema: dim_trust, dim_financial_year, dim_subcode → fct_tac
  • Curated analytical views (I&E, P&L, balance sheet, KPIs)

NHS Domain Knowledge

  • NHS Financial Reporting Manual (FReM) conventions
  • TAC subcode taxonomy, ODS organisation codes
  • EBITDA margin RAG thresholds (NHS England standard)

Power BI

  • 11-CSV export pipeline, no live DB connection required
  • 51 DAX measures, 5-page interactive dashboard

Explore the Code

Full technical documentation, schema, SQL, and Power BI setup guide are on GitHub.

GitHub ↗ Visual Write-up on Notion ↗ ← Back to Portfolio