A bank statement says 1,234. Is that one thousand two hundred and thirty-four, or 1.234 with a decimal comma? In a US statement it is the first. In a German statement, 1.234 is the thousand and 1,234 is the small number. Printed money has no locale tag, and the PDF text layer hands you plain strings. If you convert statements to CSV or QBO, this is where the quiet errors start.
This is how I handle it in the amount parser of a Mac statement converter written in Swift. Nothing here is specific to Swift.
Use Decimal, never Double
Money goes into Decimal from a string, with a fixed en_US_POSIX locale for the conversion. A Double turns 0.10 into something that is almost 0.10, and a totals check that adds a hundred of them will drift. The parser's job is to produce the exact digits the bank printed.
Signs come in many costumes
The same debit of 12.34 can be printed as:
-12.34(12.34)-
12.34-(trailing minus, common in older mainframe style statements) -
12.34 DR, orDR 12.34 -
$ -12.34, where the minus only appears once the currency symbol is removed
Credits can be CR, a plus sign, or nothing. So the order of operations matters: normalise the text, strip the currency, pull off a CR or DR marker, then handle parentheses, then a leading or trailing sign. A leading and a trailing sign on the same number (-12.34-) is rejected rather than guessed.
Normalising means more than it sounds. Real text layers contain a Unicode minus (U+2212), en and em dashes used as minus, non-breaking and narrow no-break spaces as thousands separators, and curly apostrophes as the Swiss grouping mark. I map all of these to plain ASCII first, and only when the string actually contains a non-ASCII byte, because most cells are plain ASCII and the table of replacements would be wasted work on them.
Which separator is the decimal one?
Rules that settle most cases without asking anyone:
-
Both
.and,present: the last one is the decimal separator.1.234,56is 1234.56,1,234.56is 1234.56. -
The same separator twice or more: it must be grouping.
1.234.567is the integer 1234567, and it also tells you that this statement uses the comma as its decimal mark. -
One separator with 1 or 2 digits after it: decimal.
12,5and12.50. -
One separator with 4 or more digits after it: decimal (
1.2345, as in exchange rates). -
One separator with exactly 3 digits after it: ambiguous.
1,234and1.234could be either.
Rule 5 is the dangerous one. I only treat it as a possible thousands group when there are 1 to 3 digits before the separator, no spaces or apostrophes before it and no leading zero. A leading zero (0,125) cannot be a grouped thousand, so it is a decimal. Then the decision comes from the statement as a whole instead of from the cell.
Let the whole statement vote
Every unambiguous cell in the amount columns is evidence. 1.234,56 votes for "comma is decimal". 1,234.56 votes for "point is decimal". Rule 2 votes too. The parser counts the votes, and the winner becomes the style used for the ambiguous cells like 1,234.
If there are no votes at all, or a tie, the style defaults to point, and the result is flagged as not certain. That flag matters. An uncertain style should not be silent, because the downstream check can then be strict about it.
Odd grouping needs room too. Lakh-style digits (12,34,567) are valid in Indian statements: the parser accepts groups of two before a last group of three when the separator is a comma. The Swiss 1'234.56 and French 1 234,56 forms work through the same grouping step: exactly one kind of separator in the integer part, a first group of at most three digits, and the rest three digits long.
Things that look like amounts and are not
A statement is full of numbers that are not money: dates (03.04.2025), account numbers, phone numbers, percentages, reference codes. Wrongly accepting them is as bad as misreading an amount, so the parser rejects:
- whole numbers with a leading zero (
007) - whole numbers of more than 9 digits with no decimals
- strings with letters left after the currency and CR/DR are removed
- anything over 40 characters
For deciding whether a cell looks like an amount, a fraction is strong evidence. Whole numbers of 8 or more digits are treated as account or phone numbers.
There is also a text-layer artefact worth knowing. Some PDFs produce 49.,44 for 49,44, a stray point glued to the comma. If the string is otherwise plain numeric, I drop that point before parsing.
Where the arithmetic comes in
None of this is trusted blindly. After parsing, the statement is checked: opening balance plus the transactions has to equal the closing balance, and where there is a running balance on each row, every row is checked. If a 1,234 was read the wrong way, the sum is off by a lot and the check says so, with the rows involved. A parser that cannot be sure is much better paired with a proof than with a promise.
Test it under other locales
The parser must not depend on the Mac's region settings. A German Mac and a US Mac must read the same PDF the same way and write the same CSV. I run the conversion under several regions and compare the output byte for byte, and feed it public sample statements from different countries.
What still goes wrong
Truly ambiguous cells in a statement with no other evidence (a short statement where every amount is a round thousand, such as 1,000 and 2,500) fall back to the default and are flagged. Scans can turn a , into a .. And a bank that mixes styles in the same column will beat any voting scheme. For those the user has to pick the number style in the preview.
If you want to try it on your own statements: Balanced is free to convert and check, with the first 10 transactions per file exported. Pro is USD 59 a year. I'd like to hear which statement layouts it reads wrong: https://kayabyte.dev/mac/balanced/?ref=devto-bal-amounts
Full disclosure: I'm the developer of Balanced, a Mac app that turns bank statements, seller reports and invoices into checked CSV, Excel and accounting imports, all on your Mac with no upload and no AI.
Written with AI assistance. The findings come from my own review and test notes.
Top comments (0)