Import a Bank Statement Twice Without Doubling Your Ledger
The quickest way to double a month in your books is to import a bank statement you have already imported. It happens more often than it sounds: a client sends January to March, then February to April, and February arrives twice. Or an import fails halfway, you run it again, and the first half lands a second time.
Whether your accounting software notices comes down to one thing: whether each transaction in the file carries an ID it can remember. Some formats do and some do not, and the ones that do only help if the ID stays the same every time the statement is converted.
The transaction ID is the whole story
OFX, QBO and QFX files give every transaction a field called FITID, short for financial institution transaction ID. Software that reads these formats can keep the IDs it has seen for each account and skip a transaction whose ID it already has. Import the same file twice and the second import adds nothing.
CSV, Excel and QIF have no such field. A row is a date, a description and an amount, and nothing else. Software importing them can only guess at a repeat from those three values, and most of it leaves the decision to you.
What each program does with it
How the common destinations treat a file you have already imported:
- QuickBooks Desktop imports .qbo files through Web Connect and remembers each FITID, so a transaction it has already downloaded is not added again.
- Quicken remembers the FITID of every transaction per account for the files that carry one, and skips repeats the same way. A QIF file has no ID, so it gets no such protection.
- QuickBooks Online uploads CSV, QBO, OFX and QFX files. A CSV row has nothing to recognise it by, so an overlapping CSV can create duplicates, and QuickBooks will at best suggest a match in For review that you have to accept. Upload a .qbo or .ofx file instead where you can.
- Xero imports OFX, QIF and CSV, and has a Duplicate Statement Lines report for finding repeats after they have landed. It is far easier never to import them: start each import the day after the last transaction you already have.
An ID only helps if it is stable
Here is the trap. A file can carry a FITID on every row and still double your ledger, because the ID changes between conversions. Many converters build it from the row’s position: the date plus “row 40”. The same 15 February payment is row 40 of a January to March statement and row 3 of a February to April one, so it gets two different IDs, and QuickBooks treats it as two payments.
Position-based IDs can also fail the other way. Two different transactions that happen to sit at the same position in two files can share an ID, and then the second one is skipped as a duplicate when it is not one. A missing transaction is harder to spot than a doubled one, because nothing in the ledger looks wrong.
A stable ID is built from the transaction itself: its date, its amount and direction, and its description. That is how PivotBank builds the FITID in every QBO and OFX file it writes. The same payment gets the same ID however the statement was sliced, and nothing earlier in the file can move it. Two genuinely identical charges on the same day, two bus fares for the same amount, get a numbered suffix, so the second one is not swallowed as a repeat of the first.
Where even a stable ID cannot help
An ID built from the transaction is only stable while the transaction reads the same. Two cases break that, and both are worth knowing:
- A live bank feed and a file for the same dates. The bank’s feed carries the bank’s own IDs, which no converter can know, so a converted statement covering the same days will not match them. Pick one source for each period.
- The bank’s own download and a converted PDF for the same dates. Same problem: two different ID schemes. Again, one source per period.
When CSV is the only option
Sometimes CSV is all a program will take, or you need the rows in a spreadsheet first. Then the protection has to come from how you import:
- Import only the gap. Set the first date of the import to the day after the last transaction already in the ledger, and trim the file to match.
- Keep one account per file, so a statement for one account cannot land in another.
- Check the balance after every import. If the account in your software does not end on the closing balance the statement printed, something came in twice or not at all, and you find out now rather than at year end.
Which format to choose
If you have the choice, choose the file that carries the ID:
- QuickBooks Online or Desktop: QBO. Our QuickBooks import guide walks through the upload.
- Xero, Sage and FreeAgent: OFX. See importing a statement into Xero.
- Quicken: QIF, which carries no ID, so import only the dates you do not have yet.
- A spreadsheet, or a tool that only reads CSV: CSV or Excel, with the balance check after import.
Try it on a statement
Convert a statement to QBO or OFX twice, or convert two statements that overlap, and the shared transactions carry the same IDs in both files. Before any export, PivotBank checks every row against the statement’s own running balance, so a misread row shows up in the preview instead of in your ledger. You can also verify a statement on its own.