Bank Statement Technology & Data Formats
The Hybrid Treasurer's Toolkit — the practical guide
Which tools you actually need, every bank statement and payment format you'll meet, and how to match your stack to your account count instead of buying enterprise for a 3-bank setup.
- → Pick the right connectivity, processing, and reporting tools for your actual setup — not the vendor pitch
- → Recognize every statement and payment format you'll meet, and know which ones to ignore
- → Trace the end-to-end flow from bank file to cash position, with a free-vs-paid tool for each step
- → Match your account count (1-5 / 5-20 / 20+) to the stack that actually fits it
Alina Turungiu | TreasuryOS · treasuryease.com
Cash Position Technology Stack
What exists, what it does, and whether you actually need it. Mix and match based on your setup.
Manual CSV/Excel download.
✓ Everyone — starting point, never skip
Automated daily delivery.
✓ 5+ banks, high volume
✗ <5 banks — manual is faster
Secure file transfer, EU standard.
✓ DE/FR/CH banks
✗ Not always fit outside EU
Real-time balance & txn pulls.
✓ Real-time cash monitoring
✗ EOD is enough — most APIs limited
Parse, clean, merge bank files.
✓ Everyone — free, start here
✗ Never skip — even TMS needs it
Custom parsing, API integrations.
✓ Non-standard formats, custom APIs
✗ Overkill for MT940/CAMT
Robot downloads from portals.
✓ Banks without SFTP/API
✗ Automate at source if available
Auto-import & report.
✓ 10+ accounts, multi-entity
✗ <10 accounts — €50k/yr for free Excel
PivotTable + conditional formatting.
✓ 1-10 accounts, free
✗ Sharing live with a team annoys you
Auto-refresh, shareable, visual.
✓ Team access, multi-site
✗ Solo treasurer — PivotTable is enough
Daily cash summary to inbox.
✓ Team awareness, CFO update
✗ Only you look at cash
Integrated cash management module.
✓ Enterprise, 20+ entities
✗ Bought a TMS just for this — wrong reason
Start with what you have. Add complexity only when you've outgrown the simple version.
Bank Statement Formats
What your banks send you — and what's inside each format.
End-of-day. Structured text (:60F: opening, :61: txn, :62F: closing).
✓ Universal — any SWIFT bank delivers this
✗ If bank already sends CAMT.053, use that
Replacing MT940. Richer: remittance info, debtor/creditor.
✓ Always prefer this when available
✗ If unsupported, stick with MT940
US format. Fixed-width, group/account/txn hierarchy.
✓ US banking operations
✗ Non-US ops — you'll never see it
Intraday txns only. No balances. Provisional — can change by EOD.
✓ High-value same-day payments
✗ EOD reporting is sufficient
XML intraday. Richer than MT942. Includes provisional balances.
✓ Real-time liquidity monitoring
✗ You check cash once a day
Only if you manage high-value same-day payments or need real-time liquidity monitoring. Most SMBs don't. Start with EOD statements, add intraday only when it creates real value. Not always REAL-TIME is the answer, nor is it for everyone.
ISO 20022 is the future. SWIFT is migrating all traffic. CAMT replaces MT. Richer data, better STP, improved compliance.
Payment & Reporting Formats
Outbound payments and reconciliation — what each format does and whether you need it.
ISO 20022 credit transfers. Batch or single, multi-currency.
✓ Any multi-bank payment via file
✗ Single-bank manual portal payments
ISO 20022 direct debits. Mandate management, recurring charges.
✓ Collecting from customers
✗ Outbound payments only
SWIFT payment request. Multi-bank from one source. Legacy.
✓ SWIFT-based ops, legacy TMS
✗ Bank accepts PAIN.001 — use that
SWIFT bank-to-bank credit transfer. Not initiated by corporates.
✓ Understanding confirmations
✗ You don't generate these
EUR transfers in SEPA zone. PAIN.001 subset.
✓ Any EUR payment in Europe
✗ Non-EUR or non-European ops
Debit/credit notifications. Real-time transaction confirmations.
✓ Real-time payment matching
✗ Daily batch reconciliation is enough
Also used for reconciliation. Match entries against books.
✓ Daily bank reconciliation
✗ Never skip — this is your primary tool
SWIFT debit/credit confirmations. Legacy equivalents of CAMT.054.
✓ SWIFT-connected banks only
✗ Bank sends CAMT.054 already
80% of treasury teams only need two formats: PAIN.001 (payments out) + CAMT.053 (statements in). Everything else is situational.
How It All Connects
The end-to-end flow and which tools at each step.
| Step | Free / Low Cost | Paid / Enterprise |
|---|---|---|
| Collect | Manual download, email rules | SWIFT Alliance Lite2, EBICS client |
| Parse | Power Query (Excel) | TMS auto-import, Python scripts |
| Clean | Power Query transforms, XLOOKUP | Master data management tools |
| Position | Excel PivotTable, Power BI (free) | TMS dashboard, Tableau |
| Automate | Power Automate (M365), VBA macro | TMS scheduler, RPA bots |
What 80% of treasury teams actually need: Bank portal + Power Query + Excel PivotTable + Power Automate. Total cost: €0.
What Do You Actually Need?
Match your situation to the right stack.
Collect: Manual download or email
Formats: CSV / MT940
Process: Power Query in Excel
Output: Excel PivotTable
Automate: Power Automate (optional)
Cost: €0
Collect: SFTP / EBICS / email rules
Formats: MT940 + CAMT.053
Process: Power Query + Power BI
Output: Power BI dashboard
Automate: Power Automate flows (cheaper)
Cost: €0–500/mo
Collect: SWIFT / H2H / APIs
Formats: CAMT.053 + .052 + PAIN.001
Process: TMS + custom integrations
Output: TMS dashboard + BI layer
Automate: Fully automated STP
Cost: More than €10k+/year
Not every treasury needs enterprise tools.
Quick Reference
✓ = most treasuries need this · unmarked = situational, add only when needed.
| Task | Format | Tool (Free) | Tool (Paid) |
|---|---|---|---|
| ✓ Daily cash position | MT940 / CAMT.053 | Power Query → PivotTable | TMS auto-import |
| Intraday monitoring | MT942 / CAMT.052 | API (light) + Excel refresh | TMS real-time feed |
| ✓ Payment initiation | PAIN.001 / SEPA XML | Bank portal upload | TMS payment hub |
| ✓ Bank reconciliation | CAMT.053 + CAMT.054 | Power Query matching | TMS auto-reconcile |
| Cash forecasting | Historical statements | Excel + trends | AI/ML forecasting module |
| ✓ Multi-bank consolidation | Mixed formats | Power Query Append | TMS + SWIFT |
| FX position | Statement + market data | Excel + ECB rates | FX trading platform |
Start with Column 3 (Free). Move to Column 4 (Paid) only when Column 3 can't keep up. That's the only rule. Everything else is vendor marketing.
What you know now
Every vendor demo made TMS/RPA/Power BI feel mandatory
→ You can weigh each tool against your own account count, entity count, and team size before spending anything
MT940, CAMT.053/052/054, PAIN.001/008, BAI2, MT101/103/900/910 all blurred together
→ You know PAIN.001 + CAMT.053 cover 80% of needs, and exactly when each of the rest actually applies
"Real-time" and "intraday" sounded like upgrades everyone should want
→ You know most SMBs don't need them — EOD statements are enough until they create real value
"What stack do I need?" had no clear answer
→ You have three concrete tiers — 1-5, 5-20, 20+ accounts — each with its own tools and realistic cost
From Bank Statement to Cash Position
Put the Power Query + Excel stack from this course to work in a real, step-by-step build.
Next step · CourseBank Connectivity: The Hybrid Treasurer's Toolkit
SWIFT, H2H/SFTP, EBICS, bank APIs, and multi-bank aggregators — the connectivity layer in depth.
Built by a treasurer, for treasurers. · treasuryease.com