How to import a bank statement in QuickBooks

For a connected bank or credit card account, QuickBooks pulls in transactions only up to a set date. Uploading a file covers the gaps: an account you can't connect, or a stretch of older transactions. Your bank's website explains how to download a copy of them.

Pick the date range with care. Intuit says the statement should begin at least one day before the earliest transaction QuickBooks already holds for that account. Look over your chart of accounts first, too, so the upload doesn't create duplicates, and work out whether the transactions belong in one account or several. Intuit suggests setting up any parent accounts and subaccounts you need beforehand. If you'd like the transactions categorised as they come in, create your bank rules before uploading.

Check the file itself. It must be in English and no larger than 350 KB, with no more than 1,000 lines, one transaction to a line. QuickBooks takes .csv, .txt, .qbo and .ofx files, and Intuit's preference is the QuickBooks Online data (QBO) format. For a long period, download it in smaller date ranges.

A CSV needs either 3 columns, Date, Description and Amount, or 4: Date, Description, Credit and Debit. Before uploading one, take the word "amount" out of the Credit and Debit columns, clear any Amount, Credit or Debit cell that holds only a zero, and strip numbers out of the Description cells. Keep every date in one format, ideally dd/mm/yyyy. If your bank adds the weekday to each date, as in 20/11/2018 TUE, put the weekday in a column of its own.

1. Go to All apps, select Accounting, then select Bank transactions.
2. If the account you're uploading into has a tile, select it.
3. Open the Link account dropdown and select Upload from file. For an account not yet connected to online banking, select Upload transactions instead.
4. Select Drag and drop or select files, choose the file you downloaded from your bank, then select Continue.
5. From the Select account dropdown, choose the account the transactions belong to and select Continue. If it isn't listed, choose + Add new from the dropdown to create a bank account.
6. Match each column in your file to the right QuickBooks field, then select Continue.
7. Tick the transactions you want to bring in and select Continue.
8. Select Yes, then select Done.

The lines you chose now sit on the Bank transactions page for that account. Where QuickBooks recognises a description, or one of your bank rules fits, it may already have added some details to them.

An uploaded transaction still needs matching or categorising, which is the step that puts it in the right account. After that, reconcile the account so you can check the transactions were recorded correctly.

Start with the mapping, because QuickBooks can't read the data if the wrong column is chosen at that stage. Deleting the columns you don't need helps, since only Date, Description and Amount are required, and so does renaming your columns to match QuickBooks' titles.

Banks build their CSV files differently, so formatting is the next thing to check. Keep the amounts plain: highlight that column, right-click, choose Format Cells and change a Currency category to General to drop symbols and commas. Special characters in a bank description can block the upload, and Ctrl-H finds and replaces them. Make sure the date format matches the one picked at mapping: highlight the date column, open the Number dropdown at the top of Excel and change it to Short Date. Pasting the whole file into a blank spreadsheet can also show up a problem you hadn't noticed.

A file with separate debit and credit columns can give an error when the period holds only money going out. Add an extra line with a positive amount, then untick that line on the last page, where the transactions are listed, before confirming the upload.

Uploading into a subaccount isn't possible at the moment. Connect the subaccount to its bank feed directly, or enter its transactions manually. Intuit asks anyone with an unusual subaccount setup to contact its support.