When a statement import fails, or quietly lands on the wrong dates

First, work out which failure you have

Three questions, in order.

1. Did you get an error message? The file was refused and nothing changed. Go to the rejections section below.
2. Did it say it worked, but the register is unchanged? Nothing imported, which is almost always duplicate transaction IDs.
3. Did transactions appear? Check the dates before anything else, because this is the failure that does not announce itself.

For the third: sort by date and look at the first and last transaction. If your statement covers March and the imported range runs January to December, the day and month were swapped on every row where the day was 12 or under. The count tells the same story. Send 80 rows, get 62, and the missing 18 were the days past the 12th.

The day-first versus month-first trap, and how to settle it

03/04/2024 is either the third of April or the fourth of March, and the file usually does not say which. Neither does your bank, on the export page, ever.

The only thing that settles it is evidence. Scan the date column for a row where one reading is impossible: any day past 12. Find 25/03/2024 and the file is day-first, for every row in it. Find 03/25/2024 and it is month-first. One such row decides the whole file.

If there is no such row, which happens with short exports and accounts that only move early in the month, nothing in the file can settle it. At that point you are choosing rather than deducing, and the only safe way to choose is against the statement you can read with your own eyes.

Worth the two minutes, because a wrong date order moves transactions into the wrong month and therefore the wrong reporting period, and you find out when something fails to reconcile later.

Where to set the date order in the software you are importing into

Most importers let you declare it. They just do not default to anything sensible.

• GnuCash CSV import has a date format selector, and its manual warns about exactly this: "This does not default to the Locale setting so check it matches the data you are importing."
• QuickBooks Online CSV upload asks for all dates in one consistent format, and the order it recommends differs between the regional versions of its own help page, so read the one for your region. Its other limits: 350 KB or less, up to 1,000 lines per upload, and either 3 columns (Date, Description, Amount) or 4 (Date, Description, Credit, Debit).
• Quicken for Windows CSV import offers no choice. Its help page requires "the Quicken mm/dd/yyyy format", so the column has to be rewritten before import rather than interpreted during it.
• Excel, if you are cleaning the file first, takes the order from your regional settings unless told otherwise. Data, From Text/CSV, Transform Data, then right-click the date column, Change Type, Using Locale.

The format that dodges the problem is OFX, whose dates are written year first, as 20240131.

It said it imported and nothing appeared

This one looks like a bug and is a feature. Intuit's own page explains the mechanism: "Every account and transaction inside of a web connect file has a unique identifier. When transactions are added or matched to the register, QuickBooks stores the ID numbers for these transactions. It will not allow you to import transactions with the same ID more than once to prevent duplicate entries."

So if you imported the file before, or deleted the transactions afterwards and tried again, the IDs are already on record and the file brings in nothing. Intuit's suggested routes are to restore a backup from before the first import and reimport, to enter the few transactions by hand, or to edit the account identifier in the file. It also gives the advice that prevents a repeat: download a new file from the bank covering only the dates you need.

The same applies anywhere OFX style files are imported. Deleting a transaction does not delete the memory of its ID.

Straight rejections, and what each usually means

The error text varies by product and version, so go by the shape of the problem.

• Wrong format for the product. QuickBooks Desktop reads Web Connect .qbo files for bank transactions, and Intuit notes that importing one requires being signed in to your Intuit Account inside QuickBooks Desktop. Quicken's Windows support page lists OFX among the formats it cannot import. Renaming an extension changes nothing inside the file.
• Right format, missing identifiers. A structurally valid OFX with no Intuit institution ID is refused by Quicken and QuickBooks Desktop. That is the QFX and QBO distinction, and not something a converter can honestly invent.
• Too big. QuickBooks Online caps an upload at 350 KB and 1,000 lines. Split by month.
• Wrong number of columns. Importers that specify three or four columns mean exactly that, in that order, with the headers they name.
• Unreadable dates. A day past the 12th in a month-first importer gives an invalid date error, which is the good version of the problem, since it stops instead of guessing.
• An account never connected to a bank. Some products refuse a bank feed file there.

Amounts out by a factor of a thousand

Same class of problem, different character. In much of the world 1.234,56 means one thousand two hundred and thirty four and fifty six cents. In the rest, 1,234.56 means the same thing. Read the first with the second one's rules and you get 1.23.

The tell is in the totals. Sum the amount column and compare against the statement. Out by roughly a thousand times, or amounts that should have cents showing as whole thousands, means the separators were read backwards. The same evidence trick applies.

How to import without needing to undo

Four habits, all cheap.

1. Back up first. Quicken, QuickBooks Desktop and GnuCash all have a file you can copy. Most import routes have no single undo, and the CSV ones have none at all.
2. Import one transaction first. Cut the file to a single row, ideally dated past the 12th. It proves the dates, the amount sign and the destination account in thirty seconds.
3. Import one month, not two years. Fixing 40 rows beats fixing 900.
4. Check the closing balance. Not the first twenty rows, which always look fine. The balance catches a missing transaction, a duplicate and a sign error at once.

On duplicates: formats with transaction IDs let software recognise a repeat. CSV and QIF have none, so reimporting usually gives you everything twice, which Quicken's help states outright for CSV. GnuCash handles it better, matching incoming transactions against existing ones within a window of roughly 42 days and scoring each for you to confirm.

Questions, answered.