Xero bank reconciliation not matching: finding where the balance went wrong

Name which two balances disagree before anything else

Xero shows two numbers against a bank account, and they're supposed to differ. The statement balance is the sum of the statement lines Xero holds, whether a feed delivered them or you imported them. The balance in Xero is what the transactions entered in your books add up to. A gap between those two is just unreconciled items waiting for somebody, and it's the normal state of an account mid-month rather than a fault.

The number to hold against the bank is the statement balance. If it agrees with the bank's closing balance on the same date, your data is complete and what you have is a reconciling job, so go and reconcile it. If it doesn't agree, the statement lines themselves are wrong, and no amount of matching transactions will fix that, which is the situation the rest of this page is about.

Once you know it's the statement lines, the fastest route to the cause is the date the two balances first part company, not the size of the difference at the end:

1. Take the bank's own closing balance for a date you trust, ideally a month end that somebody has already signed off.
2. Open the bank account in Xero, look at the statement lines for that period, and read the running balance down to the same date.
3. If they already disagree at that point, halve the period and check again. A few passes gets you to a single day, and it's much quicker than reading a whole statement line by line.
4. Write down the size of the difference and whether Xero is high or low. That pair narrows the cause immediately: a difference equal to one transaction is a duplicate or a missing line, and a difference equal to exactly twice a transaction is a sign that got flipped.

• View bank statements and bank statement linesWhere to read the statement lines and the running balance Xero holds for an account, as opposed to the reconciliation screen.

Duplicated lines, where an import overlapped a feed

The sequence that causes this is so common it's almost a routine: a feed stops for a fortnight, somebody exports a CSV and imports it by hand to keep the month closable, then the feed is reconnected and delivers part of that same fortnight. Xero drops lines it recognises from a file it has already imported, and it also publishes a report for finding duplicate statement lines after the fact, which is a fair sign that the first check doesn't catch everything.

What gets through is the same money wearing a different description. A bank prints one thing on a PDF statement and the feed delivers something else for the identical transaction, so a payment that appears twice can look like two different payments to anything comparing text. Dates shift too, because a statement often shows the posting date while the feed carries the transaction date, and a payment recorded on the Friday in one place and the Monday in the other is not going to match itself.

The tell is a difference that appears on the day the two sources overlap, equals the amount of one transaction or a tidy block of them, and then stays exactly the same for the rest of the period, because a constant offset means whatever went wrong went wrong once, on a specific day, and stopped.

• Find and remove duplicate bank statement linesXero's report for the duplicates that reach the ledger, and how it compares dates and amounts to find them.
• Import a CSV bank statementThe manual import, its accepted formats, and how it treats lines it has seen in a previous file.

Missing lines, where the feed left a gap nobody closed

A restored feed runs forward from the moment it's restored, and it doesn't go back over the outage. From Xero's side the incident is resolved and everything is working, while your ledger is still short by however many transactions went through the account in the meantime, and nothing anywhere will flag that, because nothing is wrong any more.

Feeds leave gaps for reasons that have nothing in common except the residue. UK open banking consent expires every ninety days and has to be reauthorised, which is regulation rather than a fault, and an aggregator outage can run for weeks. A feed connected today brings a limited window of history with it, so an account connected halfway through the year is missing everything before that cap however long it has existed. And an account that was never eligible for a feed has no gap so much as a permanent absence, because eligibility is decided per account rather than per bank, and one account can feed perfectly while another at the same bank has nothing on offer. Working out which of those you've hit is the subject of our guide to a Xero bank feed that has stopped working.

Missing lines look different from the other three causes in one useful way. A duplicate or a flipped sign puts a constant offset into the running balance from one day onward, while a run of missing transactions makes the difference grow, shrink and wander as each one you never received passes by. If the gap between Xero and the bank keeps changing, stop hunting for a single wrong line, because you're looking for a period rather than a transaction.

Inverted signs, and the debit and credit column trap

Xero's manual import wants either one signed amount column, with money out negative, or separate debit and credit columns, and it won't take a file that mixes the two conventions. Separate columns are what most bank exports actually look like, and they're where a whole statement can post the wrong way round without a single error message.

Exports that split money in from money out usually write both amounts as plain positive numbers, because the column heading is carrying the direction rather than the number. Which column means money out is then decided entirely by how you map it, the file validates either way, and every row imports cleanly. There's nothing to catch it, and the four ways it goes wrong all look identical from the outside:

• The two columns get mapped the wrong way round at import, and the entire statement posts inverted.
• A card export uses one column written from the issuer's point of view, where purchases are positive because the issuer is owed the money. Imported as-is, a month of spending arrives as a month of income.
• A converter collapses two columns into one and picks the wrong one on rows where the unused column holds a dash, a zero or a space rather than a genuine blank.
• Negatives that were written as a trailing minus or in brackets lose their sign in conversion, so most of the file is right and a scattering of rows is backwards.

The reason this survives every casual check is that nothing about it looks broken, since the row count is right, every date is right, every description is right, every amount is right to the penny, and only the direction is wrong. It gets noticed at the balance and nowhere earlier, which is usually weeks after the import.

The arithmetic is what identifies it, because a flipped line moves the balance by twice its own value, so a payment of 250 out that posted as 250 in leaves you exactly 500 adrift. Halve the difference, go and find a transaction of that amount on or near the divergent day, and you've almost certainly found it.

A clipped export, where the period wasn't what you thought

This one is the quietest of the four, because the file is perfectly correct as far as it goes. Online banking date ranges often default to a rolling window, the last thirty days or the last three months, rather than to a calendar month, so a statement downloaded on the third for what you think is last month can start on the fourth and drop three days off the front. Some banks page their transaction list and export the page you're looking at instead of the range you selected, and a multi-page PDF that loses a page in conversion produces a file that opens fine, imports fine and is missing a Tuesday.

The signature is a difference that equals the net of a contiguous run of days, with an obvious boundary at each end: a page break, a month end, the thirtieth day back from whenever the export was taken. Sort the statement lines in Xero by date and look for a stretch where nothing at all happened in an account that transacts most days.

Nothing in the import checks this for you, because Xero imports what the file contains, so a truncated export succeeds, reports its row count cheerfully and leaves the account wrong. The statement itself almost always carries an opening and a closing balance for the period, which is exactly the check that would catch it, and it's the check nobody does by hand on a hundred lines at half past five.

Closing the difference without creating the next one

Once you know which of the four you're dealing with, the fix is data rather than tooling: take the duplicates out, post the lines that never arrived, correct the block that went in backwards, complete the period the export cut short. The catch is that three of those four fixes are another manual import, done under time pressure on an account that's already wrong, which is a fairly reliable way of generating next month's discrepancy.

StatementClear posts statements into Xero through the same Bank Feeds API the native feeds use, so the lines land as feed lines rather than as a file you imported. Each of the four causes above is checked before anything reaches the ledger: an upload is deduplicated against the lines already sitting in the account, so covering a period the feed partly delivered can't double it, the running balance is recomputed from the statement and checked against it, so a clipped export is caught rather than reconciled, and inverted signs are flagged, which is the failure that passes a row count and a date check and everything else you'd think to look at.

Column mapping happens once per account and is remembered, so the debit and credit columns that caused the problem in the first place stop being a decision somebody makes again every month. For an account with no feed at all the same upload is simply the permanent arrangement rather than a repair, which is the shape most foreign and overseas accounts end up in.

• Bank Feeds API overviewThe interface a posted feed line arrives through, and why that isn't the same as an imported one.
• Bank Feeds API statementsHow a statement and its lines are submitted to a feed connection, including the balances that travel with them.