8 loans found in a bank statement: the engineering behind our treasury platform
At one of the companies in the project, we found 8 loans that nobody had entered into the system, using only the debits on its bank statement. The references recurring each month pointed to financing arrangements. Once linked, each debit is compared with the instalment expected under the repayment schedule. Getting there means first solving the problems behind the scenes: receiving files, understanding different formats, preventing duplicates and checking that balances reconcile.
That work underpins Pool Bancario, the treasury platform for multiple companies that we developed with CAIC, deployed on AWS, with strict isolation between companies in every query.
Statements arrive through several channels
The main one is Norma 43, the AEB/CSB 43 standard used by Spanish banks to exchange statements. Files arrive over SFTP with public key authentication. With some banks, we connect to their server each day. Others place the file on ours.
Our custom parser reads the records needed to reconstruct accounts and transactions:
- 11: account header.
- 22: transaction.
- 23: additional descriptions.
- 33: account trailer, including the balance.
- 88: end of file.
We also read the Excel and CSV files each bank exports from its website. Each bank has its own format. Some do not even include the account number, so we have to identify the account another way. Statements emailed by the accounts team are imported automatically every 20 minutes.
The same transaction arrives again
A statement downloaded halfway through the morning is completed the following day. Some banks also reorder transactions between deliveries. Each transaction therefore has a fingerprint:
company | bank | date | amount | balance | description | occurrence
The occurrence distinguishes two legitimate transactions that are identical in every other respect, such as two identical direct debits on the same day. It is also combined with a physical key based on date, amount and balance. A row’s position in the file is no basis for a decision.
The company is part of the fingerprint. In a platform serving multiple companies, isolation must hold from end to end.
Balances are the proof
After importing, we check the continuity of each account:
closing_balance(D) = closing_balance(D-1) + sum of transactions on D
There is a catch: some banks deliver statements in descending order. If you infer the closing balance from the last row, you get a cascade of false discrepancies. The correct closing balance is the balance that is not the starting point of any other transaction that day.
“Processing a file without errors does not prove it has been understood correctly. Reconciling the balances does.”
From debit to financing arrangement
Once the transactions are clean, recurring references identify loans and mortgages linked to their financing arrangements. Any deviation is measured against the repayment schedule.
Credit facilities combine two sources: the drawn amount comes from the statement, and the limit comes from a stepped schedule of agreed reductions. The position for each bank brings together the limit, drawn amount, available credit, current account and currency. Trade finance (COMEX) and confirming (reverse factoring) are excluded from liquidity.
At one company, the calculation matched the accounts team’s weekly Excel file to the cent: €1.9 million drawn. Reconciliation by bank against the Banco de España’s CIRBE data also compares the credit exposure reported by banks with our records.
Rules need priorities
Classifying transactions turns a statement into business information. It can also introduce major errors. A broad rule such as “anything mentioning the group is an internal transfer” was swallowing payroll transactions because the bank includes the company name in the description. Staff costs appeared as €139,250 when the actual figure was €371,015.
The solution: specific rules come first and catch-all rules come last, whoever writes them.
Transfers between a company’s own accounts are detected in two independent ways: a description rule and matching opposite amounts within a 3-day window. Neither catches them all on its own. At one company, they accounted for 34% of gross cash flow.
Reconciliation means understanding the reference
For receipts, we match each incoming payment against outstanding scheduled receivables in the ERP at three confidence levels:
- A: reference and amount. Fully validated against the ERP.
- B: several references in a single transfer.
- C: identified customer and FIFO allocation to overdue receivables.
The transfer reference points to the scheduled receivable, not the invoice. Confusing the two breaks the match. Receipts through confirming and factoring are customer payments too. Excluding them left out 218 transactions worth €928,248.
In payments the problem was one of meaning: the ERP still held thousands of old supplier instalments that had been paid but never closed. Until they were filtered out, any supplier ageing report was inflated. The filter decides what goes into the analysis.
AI proposes, a person validates
On that basis, AI suggests which receipt matches which invoice, which cases need review, which customers to chase and which payments to prioritise. It takes account of due dates, liquidity at each bank and the remaining headroom on each credit facility.
Nothing is written to the ERP without human validation.
CAIC explains the financial side on its blog: how to use this information to manage receipts, payments and financing. Tesorería con IA: del extracto del banco a decidir qué cobrar y qué pagar.
Frequently asked questions
What is Norma 43?
+
It is the AEB/CSB 43 standard used by Spanish banks to deliver account statements. It is a text file with fixed-length records: account header (11), transactions (22), additional descriptions (23), account trailer (33) and end of file (88).
How are statements received without downloading them manually?
+
Over SFTP with public key authentication. Depending on the bank, we connect to its server each day or the bank places the file on ours. Statements received by email are also imported automatically.
How are duplicate transactions prevented?
+
Each transaction has a fingerprint containing the company, bank, date, amount, balance, description and occurrence, combined with a physical key. Downloading a statement twice or having the bank reorder it therefore creates no duplicates.
Does AI record receipts or payments in the ERP?
+
No. AI suggests which receipt matches each invoice, what to review, whom to chase and which payments to prioritise. Nothing is recorded in the ERP without validation by a person.
Is your bank reconciliation breaking down along the way?
If you are connecting banks and an ERP, and incomplete statements, ambiguous references or repeated transactions are causing problems, let’s talk.
Contact usLet's talk
Ready to put AI to work in your business?
Tell us about your project and we will propose the most suitable solution, with no obligation.