From Bank Statement to Cash Position
The Hybrid Treasurer's Toolkit — the practical guide, step by step
Power Query + Excel. Zero budget. Turn raw MT940, CAMT.053, CSV, and PDF bank files into a single, automated daily cash position.
- → Parse MT940, CAMT.053, and CSV bank files into one clean 6-column table in Power Query
- → Standardize IBANs, naming, dates, and remove duplicates so the data actually merges
- → Append every bank into a single daily cash position, grouped by account and currency
- → Automate the whole pipeline with Power Automate so it's ready every morning without you
The Hybrid Treasurer's Toolkit · treasuryease.com
What Banks Send You
Before you build anything, understand what you're working with.
SWIFT standard
Tagged text (:60F:, :61:, :62F:)
One long text block, not tabular
ISO 20022 XML
<BkToCstmrStmt> nested XML
Deeply nested, verbose structure
Bank portal export
Columns vary per bank
No standard — every bank different
The worst case
Scanned or formatted tables
Not machine-readable without OCR
Parse It — Extract What Matters
Goal: turn raw bank files into a clean table with 6 columns.
| Date | Account (IBAN) | Currency | Debit/Credit | Amount | Balance |
|---|---|---|---|---|---|
| 2025-02-14 | DE89 3704 0044 0532 | EUR | Credit | +45,230.00 | 182,450.00 |
Easy
Get Data → From File → select your export. Power Query auto-detects columns. Rename to match target table. Set data types (Date, Currency, Number).
Medium
Get Data → From File → as Text. Use column delimiters on SWIFT tags (:60F:, :61:, :62F:). Split into rows by transaction marker. Extract fields with Text.BetweenDelimiters().
Easy
Get Data → From XML. Excel displays the XML as a normal table.
Clean It — Standardize Everything
Your parsed data is usable. Normalize it for better usage.
One unique identifier per account. No ambiguity.
"Main Account", "Acct-001", "Deutsche Bank EUR"
DE89370400440532013000
Entity | Location | Currency | Purpose. Always.
"DB Frankfurt", "deutsche_bank", "DB-EUR-Main"
Deutsche Bank | Frankfurt | EUR | Operating
Power Query sorts correctly. No regional traps.
"14/02/2025", "Feb 14, 2025", "2025.02.14"
2025-02-14 (ISO 8601)
Group By or Distinct on composite key.
Same transaction from statement + portal export
Remove Duplicates in PQ on Date + IBAN + Amount
Build It — One View, All Cash
Combine all parsed & cleaned bank data into a single cash position.
Home → Append Queries → select all parsed bank tables
Group By IBAN + Currency → Max(Date) → corresponding Balance
Add Column → Custom → multiply by FX rate for base currency conversion
Close & Load → to Table or to PivotTable
| Account | Bank | Currency | Balance | Base (EUR) |
|---|---|---|---|---|
| DE89 3704 0044 0532 | Deutsche Bank | EUR | 182,450 | 182,450 |
| GB29 NWBK 6016 1331 | NatWest | GBP | 94,200 | 110,214 |
| US64 CITI 0000 1234 | Citi | USD | 256,800 | 237,540 |
| TOTAL | EUR 530,204 | |||
Automate It
You've built the process. Now make it run without you.
Trigger: "When a file is created in folder" → Action: Run Excel script (Refresh All) → Action: Send email with cash position summary to treasury team.
Total setup time: ~1 hour. Cost: included in Microsoft 365.
The Full Pipeline
Enjoy!
What you know now
MT940 and CAMT.053 files felt unusable outside a TMS
→ You know exactly which Power Query import method parses each format into one clean 6-column table
Merging bank data meant messy, inconsistent account names and dates
→ You have 4 concrete before/after rules: IBAN as key, one naming convention, ISO dates, dedupe on Date+IBAN+Amount
A daily cash position across banks meant manual copy-paste every morning
→ You can Append, Group By, and load to Excel — then let Power Automate refresh and email it before you're at your desk
Bank Connectivity
Where these files actually come from — SWIFT, H2H/SFTP, EBICS, APIs, and aggregators.
Next step · Course5 Treasury Tasks Wasting 40+ Hours a Week
More zero-budget fixes for the tasks that quietly eat your week.
Built by a treasurer, for treasurers. · treasuryease.com