The first step of bank reconciliation in Xero Accounting is to get your bank statement into Xero. There is 2 ways to do so, which is bank feed and manual import.
If you have set up your bank feed, that's great! All your bank statement transactions will automatically appear in your Xero Accounting. However, if you can't set up your bank feed, your alternative will be manual import. Here is a tutorial to guide you step-by-step how to import CSV file bank statement in Xero.
Before uploading your files into Xero bank statement, make sure that you have the correct file formats. Xero currently supports the following file formats:
CSV is the most common upload file used right now. In today's blog post, you will learn how to prepare and upload a CSV file into Xero Accounting.
CSV is short for Comma-Separated Values.
It is a simple file format to store data. All columns are separated by a comma symbol (,).
Here is a sample CSV file.
If you open your CSV file in Notepad to see its data, it will look intimidating to you (I know, it looks like programming codes, don't you think?). Don't worry, we are not going to type our data in a Notepad.
You can use any spreadsheet software to create your CSV file. You can use Microsoft Excel or Google Sheets to prepare your data. Once you're done, save your document as CSV file format.
2 ways to prepare your bank statement into CSV file:
Some online banking allows you to download your bank statement to a CSV file. Login to your online banking website and look for a button or a link to download it. If you can't find it, you may check out their help file, user guide or ask their customer service.
Don't worry if you can't download your CSV file from your online banking. You can also create your CSV file manually by following the steps below:
1. Login to Xero Accounting. Go to Bank account Name > Manage Account > Import a statement.
2. Click on the link Download our CSV template. Open the downloaded file in Microsoft Excel (or your choice of spreadsheet software).
This is how it looks like when its opened in Microsoft Excel.
Headings of your CSV file must fulfill the following criteria:
If you use Xero's CSV template file, the mandatory columns are marked with an asterisks (*). The mandatory columns and its formats are:
Xero will only import your data if these 2 columns contains data.
All other columns in your CSV file are Optional Columns. You may add in additional information to your CSV file for your reference when you do your bank reconciliation in Xero.
If you download your CSV file from online banking, you must check your CSV file for any unnecessary data. Some of the example of the unnecessary data are:
Once you have cleared all the unnecessary rows and columns from your CSV file, it should look similar to this:
Your CSV file is now ready for import! Let's move on to your Xero account.
Login to your Xero account. Click Accounting > Bank Accounts at the top menu bar.
It will now load to your Bank Account page and each section of the page is your individual bank account in Xero. Look for the bank account that you want to import to. Remember to match the bank name and bank account number before you proceed. Once you found your bank account, click on the button Manage Account > Import a Statement.
It will now load to Import Bank Transactions page. Click on the button Browse and a pop up box will appear.
Locate your CSV file and click Open. This pop up box will now disappear.
Your CSV file name will now appear next to the Browse button. In my example below, my CSV file name is Bank Statement CSV file.csv. If the file name is correct, click Import button.
This step is only applicable if:
You are importing your CSV statement for the first time
Your CSV file is different from previous imports:
Different column headers
We need to check if all our rows of data has been imported successfully. To check this, find out the total number of rows of data that you have in your CSV file. Follow the simple formula below to find out your number:
Total rows - Header rows = Rows of Data to Import
In my example below, the last row number of my data is 23 and I have 1 header row. Hence my total rows of data to import is 23 - 1 = 22.
Once you have clicked import in the previous page, it will load to this page which shows you a preview of your import. Check that the number of 22 in Statement line 1 of 22 is same as your total number of rows of data to import. If you have 50 rows of data to import, yours will show Statement line 1 of 50.
If you need to assign your columns, the Assign to... fields will appear in this page. You need to tell Xero which column headers in your file is to assign to which field in Xero. In the example below, I am assigning the Date column in my CSV file to the Transaction Date in Xero. Check the checkbox Don't import the first line because they are column headings. If you do not have a header row in your CSV file, you may uncheck this. On the right part of this page is the preview of your data and how it will look like when it has been imported into Xero. Once you have checked everything is correct, click Save button.
A pop up box with the Import Results will appear. Check that the total row of data to import is correct (in my example its 22). Click OK button.
In the Bank Reconcile page, the bank statement that you have newly imported into Xero will now appear on the left part of this page. You have now successfully imported your bank statement CSV file into Xero.
Click here to get your free copy of my 5 Secrets to import your CSV file successfully in Xero.
Let me know in any of the social platforms or by email!
As always, Nuts Accounting is here to help Online Entrepreneurs to master Cloud Accounting System in simple English.
On this blog, there’s weekly tips & tutorials on Cloud Accounting System & online business curated just for you.
I always want to keep you up to date with all of my new contents. If you haven’t do so already, sign up for my e-mail newsletter here and I will notify you of new contents.
If you like this blog post, share it with your friends.
If you want to learn more on Wave Accounting tutorials, bookkeeping or tips on online business, click on the categories in the side bar.
I’ll see you in the next blog post!
Disclaimer: This blog post is not sponsored by Xero Accounting in anyways at all. I'm a Xero certified advisor and I receive a small commission when you sign up or renew your Xero subscription through me. I created this blog post only based on my personal experience in using Xero Accounting and I hope that by sharing my knowledge with you, you can use yours better.