Why does Excel read my statement dates wrong?

The bank writes them day first and a computer set to a month-first country reads 03/04/2024 as 4 March. The query here reads them explicitly as day first, with slashes or hyphens, and writes 2024-04-03, which every program reads the same way.

How do I turn the debit and credit columns into one amount?

Credits minus debits, with a blank counted as nothing — which is what the query does, removing thousands separators inside the arithmetic. The original two columns are carried through beside the result.

Why is the newest transaction at the top?

Because that is how the bank’s website shows it. The query orders the lines by date, oldest first, and keeps the bank’s own order within a day by reversing the file order, so a running total reads forwards; the sort tool here does the same for a file that already has ISO dates.

My bank has account details above the heading — what then?

Open the file in the editor here, untick First row is headings, delete those lines, and download. The health check will confirm the result reads as one table.

Can I match the statement against Stripe or my accounts?

Yes. The Stripe guide here ends in one line per payout; merge or compare that with the statement’s credits on amount and date. An accounting export compared with the statement lists the unreconciled lines.

Is my statement uploaded anywhere?

No. Your statement is read, its debits and credits folded into one amount, and its lines put in date order by a database engine inside this page. Nothing about your banking leaves your device.

All guides

The bank statement CSV: day-first dates, debit and credit in two columns, newest first

Every bank writes its CSV a little differently, but the shapes repeat: dates written day first, money in two columns — a debit column and a credit column, one of them blank on each line — a running balance, amounts with thousands separators, descriptions padded with spaces, and the newest transaction at the top. A spreadsheet reads the dates wrong, sums the debits as positives and leaves the order backwards. The query below rewrites the sample statement as one signed amount per line in date order, and every original value is left as the bank wrote it.

What the download usually contains

A heading row, then one line per transaction: the date as 31/01/2024 or 31-01-2024, a description in capitals with the payee and sometimes a reference, the amount as a debit or a credit — in two columns, or in one column with a sign, or in one column with a separate type column saying which — and the balance after the line. Some banks put the account name and number on lines above the heading, and some export the newest transaction first, so the balance column runs backwards down the page.

The amounts carry thousands separators in most exports, which a spreadsheet may or may not read as numbers depending on its locale, and the day-first dates are read as month-first by a computer set to the United States, silently, for every date whose day is twelve or under.

One signed amount per line, in date order

The SQL console here opens with a query that reads the day-first date — 31/01/2024 or 31-01-2024 — as a date and writes it as 2024-01-31, folds the debit and credit columns into one amount — credits positive, debits negative — with the separators removed inside the arithmetic only, and orders the lines oldest first, keeping the bank’s own order within a day. Run it on the sample statement to see the shape, then drop your own download and change the table name in the query to the one the console gives your file; if your bank’s headings differ, or its dates are written another way, change those in the query too.

Every original column comes through beside the two new ones — the Date as written, the Debit and Credit columns, the Description and the Balance — so the conversion can be checked against them and adjusted, and nothing is lost in the download. The query assumes the newest line comes first, as most statements do; for one already oldest-first, change DESC to ASC in its last line.

Against the books

With a clean ledger, matching it to the accounts is a merge or a compare here. The Stripe guide on this site ends in one line per payout; those lines are the credits on this statement, matched by amount and date. An accounting export in the same shape, compared with the statement, lists the lines in one and not the other — the unreconciled items — without a formula.

A statement with account details above the heading needs those lines deleted first: open it in the editor here with "First row is headings" unticked, delete them, and download.

The sample file

Date,Description,Debit,Credit,Balance
05/02/2024,CARD PAYMENT COFFEE HOUSE,4.50,,4685.01
03/02/2024,CARD PAYMENT PARKING,12.00,,4689.51
03/02/2024,STRIPE PAYOUT,,85.27,4701.51
02/02/2024,DIRECT DEBIT INSURANCE,42.00,,4616.24
01/02/2024,TRANSFER FROM SAVINGS,,250.00,4658.24
31/01/2024,STRIPE PAYOUT,,116.22,4408.24
30/01/2024,CARD PAYMENT STATIONERY LTD,18.99,,4292.02

Obviously made-up data, small enough to read. Download it and drop it on the tool to see the fix before trying your own file.

Questions

Why does Excel read my statement dates wrong?: The bank writes them day first and a computer set to a month-first country reads 03/04/2024 as 4 March. The query here reads them explicitly as day first, with slashes or hyphens, and writes 2024-04-03, which every program reads the same way.
How do I turn the debit and credit columns into one amount?: Credits minus debits, with a blank counted as nothing — which is what the query does, removing thousands separators inside the arithmetic. The original two columns are carried through beside the result.
Why is the newest transaction at the top?: Because that is how the bank’s website shows it. The query orders the lines by date, oldest first, and keeps the bank’s own order within a day by reversing the file order, so a running total reads forwards; the sort tool here does the same for a file that already has ISO dates.
My bank has account details above the heading — what then?: Open the file in the editor here, untick First row is headings, delete those lines, and download. The health check will confirm the result reads as one table.
Can I match the statement against Stripe or my accounts?: Yes. The Stripe guide here ends in one line per payout; merge or compare that with the statement’s credits on amount and date. An accounting export compared with the statement lists the unreconciled lines.
Is my statement uploaded anywhere?: No. Your statement is read, its debits and credits folded into one amount, and its lines put in date order by a database engine inside this page. Nothing about your banking leaves your device.