Duplicate transactions after importing a QBO file

How QuickBooks decides something is a duplicate

Every transaction in an OFX or QBO file carries a FITID — a Financial Institution Transaction ID. It is the transaction’s identity as far as QuickBooks is concerned. On import, QuickBooks checks whether it has already seen that FITID on that account, and skips it if it has.

That is the entire mechanism. It does not compare dates, amounts or descriptions. Two transactions with the same FITID are the same transaction; two with different FITIDs are different transactions, however identical they look.

Why converted files duplicate

A bank generating its own QBO file has a natural FITID — its internal reference for the transaction. A converter reading a PDF has no such thing, and must derive one. Whether re-importing is safe comes down entirely to how.

• A FITID derived from a timestamp or a random number changes on every conversion. Convert the same statement twice and QuickBooks sees two entirely different sets of transactions.
• A FITID derived from a row counter changes whenever the row order or count changes — so a re-converted statement that picked up one extra row shifts every ID after it.
• A FITID derived from the transaction’s own content — date, amount, description, account — is stable. Convert the same statement a hundred times and the IDs are identical every time, which is what makes duplicate detection work.

The other cause: importing into the feed and the register

If the account is also connected to your bank directly, the connection and the imported file are two separate sources for the same transactions. FITID matching only helps when both sources use the same IDs, and they will not — your bank’s IDs are its own.

Import files only for periods the live connection does not cover. Most connections reach back ninety days; anything older is exactly where a converted statement earns its place.

Cleaning up duplicates you already have

1. 1Work in the banking feed before accepting anything into the register. Excluding a transaction there is trivial; deleting one after it has been categorised and reconciled is not.
2. 2Sort the feed by date and work period by period, rather than searching for individual amounts.
3. 3Where both copies have already been accepted, use the register and delete the one that is not reconciled. Deleting a reconciled transaction will break the reconciliation for that period, and QuickBooks will not warn you clearly.
4. 4Re-run the reconciliation for any period you touched, and confirm the closing balance still matches the statement.

Preventing it

• Use a converter whose FITIDs are derived from transaction content, and verify it with the two-conversion test above.
• Import each period once, and check the date range in the file before importing rather than after.
• Keep imported periods and connected periods apart.
• Review in the banking feed. Everything is reversible there and almost nothing is afterwards.