The short version
Your statement was written in a country that uses a full stop for thousands and a comma for
decimals. It writes one thousand two hundred and thirty four as 1.234.
Read the American way, that is one point two three four.
Every amount in the column is then out by a factor of a thousand. Because the result is still a valid number, nothing reports an error. You find it at reconciliation.
The same characters mean two different numbers
| In the file | A US bank means | A German bank means |
|---|---|---|
1.234 | 1.23 | 1234.00 |
1,234 | 1234.00 | 1.23 |
12.345 | 12.35 | 12345.00 |
448.989,60 | 448989.60 either way: both separators are present | |
Looking at a single value like 1.234 will never tell you which one it is. The
answer is somewhere else in the column.
How to tell, before you import
Open the file and scan the amount column for any value that gives the answer away. In order of how conclusive they are:
-
Both separators in one value.
1,234.56or1.234,56. Whichever comes last is the decimal point. This settles it on its own. -
A separator with something other than three digits after it.
12,50cannot be a thousands separator, so the comma is the decimal point and the file is European.12.50says the opposite. -
The same separator twice in one value.
1.234.567means the full stop is doing thousands. - None of the above. If every value has exactly three digits after the separator, the file genuinely cannot tell you. Check one amount against your paper or online statement before you go any further.
One qualifying row rescues the whole file. If you can see -12,50 anywhere in the
column, every 1.234 in that same column is one thousand two hundred and thirty
four.
A related clue: semicolons
If the fields are separated by semicolons rather than commas, the file is almost certainly
European. The comma is already being used for decimals, so it cannot also separate fields.
A row written as 06/04/2024,KAFFEE,-12,50 would be four fields instead of
three, and the amount would be split in half.
What our converter does with it
It reads the whole amount column before converting anything, looks for the evidence above, and uses what it finds for every row in that column. When there is no evidence either way, it does not guess quietly: it reads the full stop as a decimal point, which is the US convention, and shows a warning saying that if the statement is European every amount is a thousand times too small.
The same principle applies to dates, where 03/04/2024 is either the 3rd of April
or the 4th of March. In both cases a single value cannot answer the question and the column
usually can.
If it already went into your books
- Restore your backup if you took one before importing. This is the clean fix.
- Otherwise the transactions have to be deleted and re-imported. There is no way to multiply an existing batch by a thousand inside QuickBooks.
- Check the largest amount first next time. An error of this kind is uniform across the whole file, so one number is enough to catch it.
Frequently asked questions
Why did my bank statement import with amounts 1000 times too small?
Because the file uses a full stop as a thousands separator and the importer read it as a decimal point. Every amount in the column comes out at a thousandth of its real value, and because each result is still a valid number, nothing reports an error.
How can I tell whether my CSV uses European or US number formatting?
Look for a value with something other than three digits after the separator. 12,50
means the comma is the decimal point. 12.50 means the full stop is. If every
value looks like 1.234, the file cannot tell you and you need to check a total
against your statement.
Why do European CSV files use semicolons instead of commas?
Because the comma is already the decimal point. A row written as
06/04/2024,KAFFEE,-12,50 has four fields rather than three, so the amount would
be split in half.
I already imported the wrong amounts. What now?
Restore the backup you took before the import. If you do not have one, the transactions have to be deleted and re-imported. This is why it is worth checking the first and largest amount against your statement before you accept an import.