Let’s talk about one of my favorite things about Xero—its inherent ability to manipulate data quickly and easily. Unlike other software I’ve used, you can quickly import statements, delete unnecessary statement lines, or trash it all and try again—all in a matter of a minute or two without ruining your work.
I’m going to run through an example so you can see what I mean.
Setting the Stage
Just to give you a little frame of reference, here’s what we’re doing and why.
In this example, we’ve got a credit card account with transactions already posted in Xero. We know that the ending balance in the card at 12/31/15 is $807.18, and we’re trying to reconcile to that. But when we look at the transactions in Xero, we think we’re missing some transactions, and the account doesn’t reconcile.
Our plan is to:
- Remove any past credit card statement imports
- Import an accurate list of credit card activity
- Reconcile the account
(NOTE: This process is most commonly used when cleaning up a new file or fixing a file that isn’t well maintained throughout the month. Accounts that are reconciled on a daily basis are much less prone to error and don’t generally need this level of revision. However, it’s good to know how to do it when you need it!)
Let’s Start With a Credit Card Statement
Here we’ve got a run of the mill credit card statement. There’s no bank feed set up… maybe because it’s a one-time thing, or maybe it’s from a bank that you no longer have online access to, or whatever. You do have a PDF copy, so let’s start there.
(NOTE: More often than not, this step and the next one (manipulating the .csv file) aren’t even necessary because you can download data files directly from the bank’s website and import them directly into Xero with no manipulation whatsoever. They take OFX, QFX, QBO, QIF, and CSV. But the following shows how you do it the “hard” way.)
- Open the PDF, highlight the statement lines, and right-click on the shaded area.
- Choose Export Selection As… and choose .csv file.
- Name the .csv file and save it somewhere where you’ll be able to find it.
Now Let’s Manipulate the .csv File
Check out the highlighted areas:
- I added a heading so you can see how I’m going to map the import, but this is not required.
- I added the final column, “Import_Amt.” This is a simple calculation taking the “Amount” column and changing the signs from positive to negative and vice versa. This is the column we’ll be using as the transaction amount to import.
Now that the import is ready to go, we’re going to take a look at Xero and make sure we’re starting with a clean slate.
Looking in Xero
First we’ll go to the credit card account and click on the Bank Statements tab to look for any statements that cover the range that we’re looking at.
We want to remove the following bank statement because we don’t want to accidentally end up with duplicate transactions when we import our nice, new, clean statement.
You’re going to click on the statement, and it will show you all of the imported statement lines. You can delete them one by one (and restore them individually whenever you want), or you can delete the entire statement at once.
We’re going to delete the whole statement by clicking on Delete Entire Statement at the bottom of the screen.
Pay special attention to the pop-up window when you choose to delete the entire statement. If you unselect the box that says “Also delete reconciled transactions for this statement,” all of the entered transactions will stay in the file.
I repeat, all the entered (and coded) transactions will stay in the file.
Sometimes you’ll want to delete those as well—like if there is a bunch of duplicated data. But in this case, we don’t want to get rid of data. We just want to make sure that we’ve captured everything for our credit card reconciliation.
So let’s uncheck the box and delete the statement.
(NOTE: For Xero newbies and/or the faint of heart, you can also select all the transactions on the statement and choose delete—at the top of the screen, not the bottom. It won’t delete the statement and it won’t delete any transactions in Xero, so it gets you to the same place. And if you decide that you really didn’t want to delete the statement after all, you can restore any or all of the lines with a couple of clicks. The only “downside” is that you have extra statements listed in the Bank Statement tab, which can get a bit cluttered and confusing.)
Now we have a clean slate in Xero, and these are the transactions that still exist in the Account Transactions tab for the credit card.
Note that the transactions are all showing as Unreconciled. That’s okay. We’ll deal with that next.
Time to Import
Now it’s time for the credit card data that we just pulled from the statement. Our goal is to make sure that everything that the credit card company has a record of has made its way into the client’s records. So here we go.
- Go to the credit card account in Xero and click Import a Statement.
- Click on Browse to upload the statement.
- Double-check that the mapping is accurate. Note that we are using the “Import_Amt,” and because we switched the sign to be negative, the transaction is showing as a debit. That’s exactly what we want.
- You might also notice that at the bottom left corner of the blue screen is a checkbox for column headings. If you don’t have any column headings, you would just leave that blank.
Now Let’s Reconcile Individual Transactions
Here’s the fun part. Now we get to match the bank statement against the existing transactions in Xero and see what’s left over.
All the green boxes on the right indicate that there’s an existing transaction in Xero that lines up with the bank transaction you just imported. Yeah! You can click “OK” for the matches and then just deal with the remaining leftovers.
From this screen, you can create new transactions in the system directly from the Reconcile tab.
For more information about reconciling, check out my previous blog post, “How Xero Helps Me Get Work Done Fast.”