A monthly bookkeeping routine with a bank statement CSV

Published 19 October 2026 · by Tekiba

Why monthly

The statement arrives monthly, so the work fits that rhythm, and a month is short enough to remember what a payment was. The IRS's guide to starting a business is direct: “You should reconcile your checking account each month.” Intuit gives a second reason in its advice on duplicates: “Reconcile your accounts each month to catch any duplicates you may have missed.” Xero puts it more loosely: “We recommend reconciling your bank accounts on a regular basis”.

The routine

1. Get the statement. Download it as soon as the period closes, while online banking still shows it. Where your bank offers the transactions as a CSV, that is the cleaner start; otherwise convert the PDF. Keep the PDF either way: it is the record.
2. Make the CSV start where last month ended. Overlap is the usual source of duplicates. Intuit's advice: “Import files that start after the newest transaction that's already in QuickBooks.” If the account also has a bank feed, decide which one supplies the rows and do not import the same days from both. Before importing, check the file: opening balance plus every row should reach the closing balance.
3. Import it. In QuickBooks, check your bank rules first: a rule set to post automatically does not wait for you, and Intuit says “Auto-post rules run when you sign in, upload a file, or create or modify a new auto-post rule.” Turn off any that could post a line you have already recorded, so it stays available to match. Each program has its own import screen and column rules; how to import a bank statement CSV into QuickBooks, Xero or Excel covers them. Count the lines that arrive against the rows in the file.
4. Match before you categorise. If a bill, an invoice or a payment is already in your books, match the bank line to it rather than categorising the line as something new; categorising it creates a second entry for the same money. Intuit's advice on duplicates says the same: “Match downloaded transactions to existing records instead of adding them.” Rules and hand categorising are for what is left. Matching invoices and receipts to bank statement CSV rows covers the differences that stop a match.
5. Let rules do the regular lines that are left. Rent, subscriptions, the same supplier every month: write a rule once. QuickBooks explains: “When you download transactions from your bank , you need to categorize them so that they enter the right accounts. Bank rules can do this automatically.” A rule that suggests a category leaves the line for you to accept; one that posts automatically does not, which is why the step before turns those off. Xero's rules work on statement lines: “Bank rules save you manually creating a new transaction each time you get a recurring or similar type of bank statement line.” Wave is different for files: “Transactions imported via statement uploads are not automatically categorized.” Review what a rule did before accepting it; a rule that matches too widely is a quiet way to put the wrong things in the wrong place.
6. Categorise the rest by hand. Whatever no rule caught is the month's real work. If you see the same payee for the third month running, that is a rule to write. If personal spending runs through the account, splitting personal and business transactions in a bank statement CSV covers marking and recording it.
7. Attach the receipts. While the month is fresh, attach the receipt or invoice to the transaction it supports. In QuickBooks, open the transaction and select Attachments. In Xero: “Open the transaction or item. Click the file icon , then click Upload files.”
8. Reconcile. Enter the statement's ending date and ending balance and match lines until nothing is left over. QuickBooks puts the target plainly: “Continue matching until the Difference is $0.00.” If it will not get there, reconcile a bank statement CSV with your accounting software covers finding where it goes wrong.
9. File the month. Put the statement PDF, the CSV and the reconciliation report in one folder per month, named by account and period. At year end, or after a tax period, consider locking the closed months so nothing changes underneath your figures. Xero describes the purpose: “Set a lock date to stop changes being made to transactions during a past period.” QuickBooks has the same idea: “Lock your books to stop changes to past transactions.”

A template to copy

Paste this into a note or a recurring task and tick it off each month.

Month: ______   Account: ______ (last 4: ____)

[ ] Statement downloaded (PDF kept)       period ____ to ____
[ ] CSV made, starts the day after last month's last row
[ ] Auto-post rules checked before upload (QuickBooks)
[ ] Balance check: opening ______ + rows = closing ______
[ ] Imported — rows in file ____  lines arrived ____
[ ] Matched to bills, invoices and payments already recorded
[ ] Rules applied to what was left, and reviewed
[ ] Remaining lines categorised
[ ] Receipts attached
[ ] Reconciled — difference 0.00
[ ] Filed: PDF + CSV + reconciliation report in /____/

Where LedgerDrop fits

For a bank that gives you only a PDF, LedgerDrop turns it into a CSV in your browser and checks the rows against the balances the statement prints, so the balance check in step 2 happens as you convert; the statement is not uploaded. The free CSV has year-first dates and one signed Amount column. LedgerDrop Pro writes the column layouts QuickBooks Online, Xero and freee会計 ask for and converts longer statements. For freee it writes whole-number amounts and leaves out rows with a zero amount, as freee's help asks, saying how many it left out; bank statement CSV upload format for freee has the details. Pro can also save how a bank's columns read — the headings, positions and roles, never a transaction — so in step 2 next month's statement from the same bank takes one click. When a statement is long, look first at where multi-page statements break; if someone else keeps your books, see handing bank statement CSVs to your accountant.

What this page cannot tell you

How to categorise your transactions, which depends on your chart of accounts and your tax position; or when you should lock a period, which neither QuickBooks nor Xero says should be monthly. Menus move between versions, and Wave's automatic categorising depends on the plan. Ask whoever prepares your accounts if you are unsure.

Questions