Universal Ingestion Engine — Automatic Bank CSV Detection

Automatic CSV Format Detection

SpeyBooks no longer asks you to select your bank format when importing a CSV. The new Universal Ingestion Engine (UIE) analyses your file automatically — detecting the header row, column roles, date format, and amount model without any manual configuration.

What’s New

• Auto-detection for any CSV — drop a file from NatWest, Starling, Monzo, HSBC, Lloyds, Tide, or any other bank. The engine identifies the layout from the data itself.
• Verified bank presets — known UK bank formats are recognised instantly with a green “Recognised format” banner. Unknown formats are analysed heuristically and shown with a blue “Format auto-detected” banner.
• Balance proof — when a balance column is present, the engine mathematically verifies that the sum of all transactions matches the difference between opening and closing balances. A green “Balance mathematically verified” badge confirms the import is correct.
• Smart Map step — the import wizard now shows exactly what the engine detected: column mapping, date format, amount model, and balance proof status. You can review before proceeding to the preview.
• Buried header handling — CSVs with metadata rows above the header (common in NatWest downloads) are handled automatically. The engine finds the real header row regardless of how many lines of account information sit above it.
• Three amount models — signed single column, separate debit/credit columns, and amount-with-direction (DR/CR) are all detected and parsed correctly.
• Date disambiguation — when dates could be DD/MM or MM/DD, the engine scans the full column to find a disambiguating value. UK format (DD/MM) is used as the default when truly ambiguous.
• Pending transaction filtering — rows marked as pending or with empty dates are automatically excluded from the import and counted separately as “Ignored”.

Import Wizard Changes

• Simplified upload — the bank format dropdown has been removed. You now only need to select your bank account and drop a CSV file.
• New Smart Map step — replaces the old column mapping step. Shows the auto-detected schema with column roles, date format, and amount model at a glance.
• Balance badge in preview — the preview step now shows whether the balance was mathematically verified, giving you confidence before committing.
• Partition stats — the response now includes counts for valid, ignored, and error rows, so you can see exactly what the engine did with every row in your file.

Technical Details

• Row conservation — every row in the CSV is classified into exactly one of four partitions: Header, Valid, Ignored, or Error. The sum is enforced to equal the total row count — no rows are silently dropped.
• Deterministic parsing — the same file always produces the same result. No randomness, no machine learning, no external API calls.
• Zero new dependencies — the engine uses only csv-parse, which was already in the project.

Known Issues

• Column mapping overrides are not yet available in the Smart Map step. If auto-detection gets the mapping wrong, there is currently no way to correct it before import. This will be added in a future release.