3 Ways to Fix Xero Import Bank Statement duplicates

Introduction

A common issue that I found when dealing with Xero bank statements is that there can be duplicate transaction issues.

Bank transactions can be imported as bank feeds or manually with CSV files that we export from our bank.

Really hard to pin point who is at fault in this case - could be Xero or with the bank feed. However, seems like this issue has been going on for years and you would think Xero would of fixed it by now!

In this post, I will go over 3 ways that I have managed to workaround the duplicate bank transaction issues that comes up and some tips to avoid it.

Generally, until Xero themselves fix this issue (which seems unlikely), you can do any of the follow options:

1. Import it manually yourself using CSV
2. Manually identify and delete the bank statement lines
3. Remove the bank statements with duplicate entries and wait for the next feed (likely the next day)

Duplicate transactions in bank feeds

In my experience, the duplicates bank transactions can appear on the same bank statement or even worse - appearing on multiple statements. For example in the past I had one statement on the 3rd Aug and found the exact same statement line on the 8th Aug statement!

How to identify duplicate statement lines

Theres 2 ways we can identify duplicates - by running the Bank Reconciliation Summary or the Duplicate statement line report.

Duplicate statement line report

This report will check the “date” and “amount” columns in your Xero bank account. If it finds any duplicates - it will then go further and have a look at the “reference”, “particulars” or “payee” fields.

So lets say we have the following bank statement line like so:

The system first identifies these statement lines as potential duplicates based on the date and amount fields. Then, it finds an exact match across the payee field in Statement Line 1 and the reference field in Statement Line 2 (both having the value INV123). As such, the report would flag these statement lines as duplicates.

If the report finds any potential duplicate statement lines, compare them to your actual bank statement and delete them from your Xero bank account if necessary.

The report doesn’t display:

• Partial matches, you’ll need to manually find and remove these.
• Duplicate statement lines that Xero has automatically picked up. You can view these on the Exceptions tab on the Bank Reconciliation Summary.

To run the Duplicate Statement Lines report, follow the below steps:

1. Log into Xero and go to the Accounting menu. We then select Reports.
2. Click on the Duplicate Statement Lines report.

(Note: If you cannot locate it - an alternative is to use the search bar in the top right)

3. After this select your bank account that you want to check for duplicates this is in the Accounts.
4. Set a date range for the report.
5. Look through the Columns and select the ones you want to appear on the Duplicate Statement Lines report!
6. (Optional) To show values to two decimal places, click the More button, then select the Show decimals checkbox.
7. Finally hit Update.

Bank Reconciliation Summary report

An alternative to the Duplicate Statement line report is the Bank Reconciliation Summary report. Now this report gives you a more in-depth search of your statements.

Additionally this seems to be the recommended way by Xero to identify and delete duplicate statement lines.

We can do this by doing:

1. Open up Xero and log in. Go to the Accounting menu -> Bank accounts.
2. You will see a list of your bank accounts. Find the one that you want to check for duplicates. Hit Manage Account -> Reconciliation Report.
3. After that you will be presented with a new screen -> click on the Bank Statement tab.
4. Determine the date range then click Update.

View the transactions in the statement. If you spot a duplicate statement line, click the date in the Date imported into Xero column.

If you see a duplicate then check the transaction checkbox and then hit Delete.

Option 1: Use manual CSV import

The import from bank feed can be flaky with some banks. With this option, we do away with all of that and import our bank statements manually with CSV.

Extracting PDF bank statement to CSV

If your bank does not provide CSV format, We can use PdfDodo to extract the bank transactions from your bank statement PDF to Excel.

After that we can massage the data to be a format that Xero likes and save it as CSV.

To do start the CSV conversion, we go through the following steps:

• Step 1: Get an account with PdfDodo
• Step 2: Go to the home page and click “Upload Document”
• Step 3: After the document has uploaded - click “Start Analysis”. This will usually take a few seconds.
• Step 4: Click on the uploaded document link to see the results. After PdfDodo has completed analysis, it will list out all the data that it thinks is in a table format.
• Step 5: Find the table that you are interested in and hit “CSV”. This will give you the CSV format.

Obviously this option will require manually effort every day/month, but it can save you the headache of the bank feed stuffing up.

We have total control of our CSV data.

Option 2: Manually delete each statement line

The most manual option is to sift through your bank statement lines and compare with the bank statements in Xero.

Tip

Deleting a bank statement changes the statement balance in Xero. You should only delete a statement line to remove duplicate statement lines.

To delete a bank statement line do the below:

1. Navigate to the Accounting menu, and choose Bank Accounts.
2. Locate the bank account containing the statement line you wish to remove. Click on Manage Account, followed by Bank Statements.
3. Under the Showing section, select Statement Lines.
4. Identify the statement line to be deleted, and check the box next to it.
5. Hit Delete, and click Delete once more to confirm your action.

Option 3: Delete the entire bank statement and wait for the next feed

One nuke option is to find the offending bank statement and delete it. This applies to when you are using bank feeds.

For example, in a previous case that I was working with, the BNZ (Bank of New Zealand) bank feeds was having problems.

You can identify it straight away, because the new bank feeds had $0 balances. So I ended up deleting these and hoping the next import will fix it.

To delete your full bank statement thats been imported from a feed:

1. Navigate to the Accounting menu and choose Bank Accounts.
2. Locate the bank account from which you wish to remove a statement.
3. Click on Manage Account, then select Bank Statements.
4. Under the Showing section, choose Statements.
5. Browse through the list of statements and click on the date of the statement you intend to delete.
6. At the bottom of the statement lines list, click Delete Entire Statement.
7. (Optional) If you wish to remove reconciled transactions, check the box next to Also delete reconciled transactions for this statement.
8. Click Delete to finalize your action.

Summary

Overall dealing with duplicates in Xero with bank statements is not fun. The main cause of this is usually with bank feeds. This could come down to a bug in Xero or with the bank feed itself.

One way to get around this is to do it manually with the CSV import. This can take a bit of manual time for you, but saves headaches with duplicates start popping up.