Replace Your Budgeting App With This Power Query Pipeline: Auto-Categorise Transactions Like a Pro

Replace your budgeting app with one refresh: a Power Query pipeline that ingests bank CSVs and auto-categorises like a pro

Fed up with manual CSV imports, inconsistent categories and the subscription bill for every budgeting app? This guide shows a reproducible Power Query recipe that reads multiple bank CSVs from a folder, normalises different column layouts, applies rule-based auto-categorisation and produces refreshable monthly and YTD reports — plus a template bundle you can drop into your workbook today.

Why build this in Excel (2026 perspective)

By 2026, small businesses and operations teams want control, auditability and local data ownership. Cloud budgeting apps are useful, but they can be expensive, opaque and rigid. Excel with Power Query gives you an auditable, customisable and offline-capable pipeline — and recent Excel/Power Query updates (late 2024–2025) mean connectors, fuzzy matching and automation are faster and easier than ever.

What you’ll get from this recipe

• A single refreshable Transactions table that combines CSVs from multiple banks (Barclays, HSBC, Lloyds, Monzo, etc.)
• Normalised columns: Date, Description, Amount, Currency, Bank, Reference, RunningBalance
• Rule-based auto-categorisation using a maintainable Categories table (pattern, category, priority)
• Monthly and YTD reports ready as PivotTables or summary tables
• A lightweight VBA snippet to RefreshAll and export reports automatically
• A downloadable template bundle (Transactions query, Categories table, report sheets)

Quick architecture (inverted pyramid: most important first)

1. Place all bank CSVs into a single folder — one file per account export.
2. Create a Power Query using the Folder connector to combine files.
3. Detect and normalise different column names to a canonical schema.
4. Clean values (dates, amounts, negative/positive conventions).
5. Load a Categories table and apply rule-based matching.
6. Load final Transactions table to the worksheet or Data Model and build monthly/YTD reports.

Step-by-step Power Query recipe

Step 1 — Collect bank CSVs into a folder

Create a folder named 'Bank_Imports' and drop CSV exports from each bank there. Prefer CSV column-only exports (no bank headers) but the query will cope with extra header rows.

Step 2 — Connect with Power Query 'Folder' connector

In Excel: Data > Get Data > From File > From Folder. Point to the 'Bank_Imports' folder. This gives you a table containing file names and binary content. Click 'Combine Files' to get started.

Step 3 — Normalise columns (canonical schema)

Different banks use different headers: 'Date', 'Transaction Date', 'Value Date', or 'Posted Date'. Similarly, amount columns may be 'Amount', 'Debit', 'Credit', 'Paid Out', 'Paid In'. You’ll create a transformation that maps all common variants to a canonical set: Date, Description, Amount, Currency, Bank, Reference.

Use a column map at the top of the query to make maintenance easier. Example mapping (as a record in Advanced Editor):

// columnMap example
let
  columnMap = [
    'date' = { 'Date', 'Transaction Date', 'Value Date', 'Posted Date' },
    'description' = { 'Description', 'Details', 'Transaction description', 'Narrative' },
    'amount' = { 'Amount', 'Value', 'Debit', 'Credit', 'Paid Out', 'Paid In' },
    'balance' = { 'Running Balance', 'Balance' }
  ]
in
  columnMap

Step 4 — Parse and normalise amounts and signs

Bank exports often split debit/credit into two columns or use negative amounts. Convert to a single Amount column where expenses are negative and income positive.

// sample M transformation snippet to create Amount column
let
  Source = Folder.Files('C:\path\Bank_Imports'),
  Content = Table.AddColumn(Source, 'ContentBinary', each Csv.Document([Content], [Delimiter=',', Encoding=1252, QuoteStyle=QuoteStyle.Csv])),
  Expanded = Table.ExpandTableColumn(Content, 'ContentBinary', {'Column1','Column2','Column3','Column4'}, {'Col1','Col2','Col3','Col4'}),
  // custom header detection and renaming code here
  Renamed = Table.RenameColumns(Expanded, { {'Col1','Date'}, {'Col2','Description'}, {'Col3','Amount'}, {'Col4','Balance'} }, MissingField.Ignore),
  ChangedTypes = Table.TransformColumnTypes(Renamed, {{'Date', type date}, {'Amount', type number}, {'Balance', type number}})
in
  ChangedTypes

Note: the real query uses dynamic column detection; the template bundle includes a robust Advanced Editor version that handles common UK bank exports.

Step 5 — Build a maintainable Categories table

Create a simple sheet called 'Categories' with these columns: Pattern, Category, Priority, MatchType.

• Pattern: text you expect in Description (e.g., 'TESCO', 'SHELL', 'SALARY')
• Category: e.g., Groceries, Fuel, Salary
• Priority: numeric priority so more specific rules win
• MatchType: 'Contains', 'StartsWith', 'EndsWith', 'Exact', or 'Fuzzy'

Load that sheet into Power Query as a separate query named 'Rules'. Keep it as a table so business users can edit without touching queries.

Step 6 — Create a categorisation function in M

We recommend a small custom function that checks the Rules table in priority order and returns the first matched category. This is easier to maintain than a giant conditional column.

// fnGetCategory(desc as text, amt as number, rules as table) as text
(desc as text, amt as number, rules as table) =>
let
  normDesc = Text.Upper(Text.Trim(if desc = null then "" else desc)),
  sorted = Table.Sort(rules, {{'Priority', Order.Ascending}}),
  toList = Table.ToRecords(sorted),
  findMatch = List.First(List.Transform(toList, each
    let
      p = Text.Upper(_[Pattern]),
      t = _[MatchType],
      m = if t = 'Contains' then Text.Contains(normDesc, p)
          else if t = 'StartsWith' then Text.Start(normDesc, Text.Length(p)) = p
          else if t = 'EndsWith' then Text.End(normDesc, Text.Length(p)) = p
          else if t = 'Exact' then normDesc = p
          else false
    in
      if m then _[Category] else null
  ), each _ <> null),
  result = if findMatch = null then 'Uncategorised' else findMatch
in
  result

Load this function into your workbook (Add as a new query). Then add a column to Transactions: Category = fnGetCategory([Description], [Amount], Rules).

Step 7 — Optional: fuzzy matching for tricky descriptions

If vendors are messy (e.g., PAYPAL *STORENAME), use Power Query's fuzzy merge with rules or implement a simplistic equivalence list. Recent Power Query performance improvements make fuzzy merge in Excel viable for moderately sized datasets (a few tens of thousands of rows).

Step 8 — Build Monthly and YTD summaries

Two approaches work well:

1. Load the Transactions table to the Data Model and use PivotTables (recommended for flexibility and performance).
2. Create pre-aggregated summary tables in Power Query using Group By (faster for static dashboards).

Sample Group By M to get monthly totals:

// MonthlySummary query
let
  Source = Transactions, // reference to cleaned transactions query
  AddMonth = Table.AddColumn(Source, 'YearMonth', each Date.ToText(Date.StartOfMonth([Date]), 'yyyy-MM')), 
  Grouped = Table.Group(AddMonth, {'YearMonth','Category'}, { {'Total', each List.Sum([Amount]), type number}, {'Count', each Table.RowCount(_), Int64.Type} }),
  Sorted = Table.Sort(Grouped, { {'YearMonth', Order.Descending}, {'Category', Order.Ascending} })
in
  Sorted

Automation: VBA snippets to refresh and export (lightweight)

Use a simple macro to RefreshAll and export your monthly report sheet to PDF for archival or emailing. Place this in a module.

// VBA: Refresh and export reports
Sub RefreshAndExportReports()
  Application.ScreenUpdating = False
  ThisWorkbook.RefreshAll
  ' Wait for queries to finish
  DoEvents
  Dim qt As QueryTable, cq As WorkbookQuery
  ' simple pause - for robust solutions hook QueryTable events
  Application.Wait Now + TimeValue('00:00:03')
  ' Export the sheet named 'Monthly Report'
  Dim ws As Worksheet
  Set ws = ThisWorkbook.Worksheets('Monthly Report')
  Dim outPath As String
  outPath = ThisWorkbook.Path & '\Monthly_Report_' & Format(Date, 'yyyyMM') & '.pdf'
  ws.ExportAsFixedFormat Type:=xlTypePDF, Filename:=outPath, Quality:=xlQualityStandard
  Application.ScreenUpdating = True
  MsgBox 'Refresh complete. PDF exported to: ' & outPath
End Sub

Performance and governance tips

• Use tables: Turn every range (Transactions, Categories) into Excel tables so Power Query picks up changes automatically.
• Disable background refresh for critical queries if you need consistent timing during automated refreshes.
• Keep rules simple: Start with Contains/Exact rules, escalate to fuzzy only when truly necessary.
• Document source mapping: Keep a hidden sheet with column variant lists for each bank so new exports are easy to add.
• Audit trail: Add an ImportSource column and a FileDate column so every transaction records its source file and import timestamp. For collaborative workflows and file metadata best practices see Beyond Filing: the collaborative file-tagging playbook.

Edge cases and how to handle them

Multiple currencies

Include a Currency column during import; normalise currency formatting and, if needed, add a simple FX table to convert to a base currency at import time.

Split transactions

For card transactions that you want to split across categories, keep the raw transaction row and add a secondary 'Splits' table where each split references the transaction ID. Power Query can expand splits for reporting.

Credit card payments

Use Rule logic: if Description contains 'CARD PAYMENT' and Amount is negative, map to 'Credit Card Repayment' rather than 'Expenses'. You can add rules that consider both description and sign.

2026 trends and why this approach is future-proof

In 2026, organisations are prioritising data portability, automation and explainability. The trend away from black-box SaaS tools toward composable workflows means spreadsheets with robust ETL (Power Query Dataflows) and lightweight automation (VBA or Office Scripts) are gaining ground. Integration with AI-assistants in Excel makes building rule suggestions faster; you can generate initial categorisation rules with AI and then human-review in the Categories table.

Power Query Dataflows and Power BI integration are also maturing — if you later want to scale, the same Queries can be moved to Power BI or Power Query Online with minor edits. That means your investment today keeps working as your needs grow.

Real-world example — a UK small retailer

"We had three staff exporting monthly bank statements from Barclays and Monzo and spending hours matching payments to categories. After switching to a single Power Query pipeline with a shared Categories table, monthly reporting time dropped from 6 hours to 40 minutes. We now refresh and export reports with one click." — Operations Manager, retail SME, London, 2025

What’s in the template bundle

• Workbook with pre-built Power Queries: CombineFiles, Transactions, Rules, MonthlySummary, YTDSummary
• Pre-built Categories table with common UK vendor patterns to jumpstart categorisation
• VBA module: RefreshAndExportReports + helper macros
• Pivot-ready Report sheets: Monthly Report (pivot), YTD Summary (pivot), Uncategorised items (for manual review)
• README sheet with installation and onboarding steps for your team (onboarding best practices)

Getting started fast — checklist

1. Download the template bundle and save it to a trusted location.
2. Export CSVs from your banks and drop them into the 'Bank_Imports' folder.
3. Open the workbook, go to Data > Queries & Connections, and run RefreshAll.
4. Review the 'Uncategorised' sheet and add rules to the 'Categories' table as needed.
5. Set up a monthly scheduled task (Windows Task Scheduler + script or use Power Automate Desktop) to open the workbook and run the RefreshAndExportReports macro.

Advanced next steps

• Move heavy transformations to Power Query Online/Dataflows if your datasets grow beyond Excel limits.
• Use Office Scripts (or Power Automate) for cloud-based scheduled refresh and email delivery.
• Use the Data Model + DAX measures for dynamic rolling averages, forecasted budgets and variance analysis.
• Version-control your Rules table in a shared location (OneDrive/SharePoint) so the whole team benefits from new rules immediately.

Common pitfalls — and how to avoid them

• Not using tables: manual ranges break Query refreshes. Always use structured tables.
• Overcomplicating rules: long regex rules are hard to maintain — favour small, testable rules with priority order.
• Ignoring audit fields: without source filename and file date you’ll struggle to reconcile later.
• Relying on fuzzy matching as the only strategy — it’s helpful, but deterministic rules are more reliable.

Actionable takeaways

• Replace repetitive CSV copy/paste with a single Folder-based Power Query to combine files.
• Keep categorisation rules in a simple editable table; apply them via a reusable M function.
• Build monthly and YTD reports from the cleaned Transactions table — prefer the Data Model and PivotTables for performance.
• Automate refresh + PDF export with a small VBA macro or Office Script for scheduled reporting.
• Start small, iterate rules weekly, and rely on the 'Uncategorised' review sheet to capture edge cases.

Download the template bundle

Ready to stop paying for that budgeting app and take back control? Download our Power Query Budget Pipeline template (includes all queries, the Categories table, and macros) from the resource link provided with this article. The bundle is designed for UK banks and includes vendor patterns common to 2025–2026 exports.

Final thoughts

This Power Query pipeline gives you the best of both worlds: the control and traceability of Excel, plus the automation and refreshability you’d expect from a modern budgeting app. It’s auditable, flexible and built for growth — from a one‑person sole trader to a small multi-location business. Update your rules, add new bank formats as needed, and your pipeline will keep delivering reliable monthly and YTD reports.

Ready to switch? Download the template, drop in your CSVs and run RefreshAll. Within one refresh you’ll have a combined transaction ledger, categories applied and monthly reports ready to export — all without another subscription.

Call to action

Download the Power Query Budget Pipeline template bundle now from our resources and subscribe to excels.uk for weekly recipes, VBA snippets and starter rule-sets curated for UK businesses. Need hands-on help? Book a 30-minute setup call and we’ll install the pipeline in your workbook, migrate your historical CSVs and tune the categorisation rules for your business.

Related Reading

• Consolidating martech and enterprise tools: an IT playbook for retiring redundant platforms
• Beyond Filing: The 2026 playbook for collaborative file tagging and edge indexing
• Review: PRTech Platform X — Workflow automation for small agencies
• The Evolution of Developer Onboarding in 2026 (useful for README and onboarding steps)
• Moving Your Community from Reddit to New Platforms: A Creator’s Playbook Using Digg’s Relaunch
• How to Use Apple Trade-Ins to Fund a Gift Upgrade (Phones, Watches, Macs)
• How a Strong Economy Can Mask Climate Risks: More Travel, More Emissions, More Extreme Weather
• Repair vs Replace: Fixing a Mac mini M4 You Bought Used — What You Can and Can't DIY
• How Bluesky and Emerging Social Platforms Affect Link Building and Referral Traffic