Cloud Accounting Xero

Xero Tips & Tricks: Fixing Monthly Statements

Written by Patti Scharf

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.)

  1. Open the PDF, highlight the statement lines, and right-click on the shaded area.
  2. Xero Account ActivityChoose Export Selection As… and choose .csv file.
  3. 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:

CSV File

  1. I added a heading so you can see how I’m going to map the import, but this is not required.
  2. 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.

Past_Stmts

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.

Delete Stmt

(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.

Missing_Trx

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.

  1. Go to the credit card account in Xero and click Import a Statement.
  2. Click on Browse to upload the statement.
  3. 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.
  4. Import_MapYou 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.”

Filling in Holes

About the author

Patti Scharf

Patti is a blogger, speaker, and thought leader in the accounting technology industry and is the co-founder and Chief Controller for Catching Clouds LLC. She is a CPA and holds several current software certifications: Xero, Bill.com (Guru), QuickBooks Online, and FreshBooks and was previously an Advanced Certified QuickBooks ProAdvisor. She absolutely loves designing effective accounting systems for ecommerce businesses and is committed to advancing the artistry of accounting through the use of technology.

10 Comments

  • Hi Patti,

    Thank you for writing this Xero Fixing of Monthly Statements article with Tips and Tricks. Fun.

    We would be interested in knowing your ideas, tips or tricks for using the Transfer and Discuss Tabs which are available when matching Bank Monthly Statements to Xero Transactions.

    Also do you know of any “best practices” for Transferring transactions from within Credit Lines or Card Accounts to Sub Credit Card and Account, especially for those companies who have multiple Credit Cards by employees/executives, all on one Credit Card Bank Account Statement?

    Hope others will share their experiences, tips, tricks, and opinions, as well.

    Sincerely,

    Tamra

    Tamra Groff, Senior Consultant, GASC/GHFG

    • Hi Tamra,

      Thanks for the idea. I’ll write an article about how to handle credit card accounts with multiple cards (i.e., subaccounts). I’ve got a pretty slick process for that which I suspect will help out many.

      As for the Transfer and Discuss tabs… I’m not sure I know exactly what it is you’re getting at. I’m not sure there’s really a “trick.” People just figure out how it will best help them in the workflow for their own firms. Can you elaborate?

      Thanks,

      Patti

  • Hi Patti,

    We will look forward to your article on handling credit cards with multiple cards or sub-accounts.

    Regarding Transfers and Discuss Tabs, as you did not cover them in this article when defining the process of Reconciling Transactions, we thought maybe you could illustrate your ideas, observed workflow process or when a client of yours was using them. If there was a creative use case, we thought by asking, you might share it. Tips or Tricks was probably a poor use of words.
    Agreed, “People just figure out how it will best help them in the workflow for their own firms”.

    Hope others will share their experiences, procedures and/or workflows, as well.

    Thanks,

    Tamra

    Tamra Groff, Senior Consultant, GASC/GHFG

    • Hi Tamra,

      You’re right – the Transfer and Discuss tabs probably do deserve a nod here.

      The Transfers tab is simply used when funds don’t actually belong to an income, expense, asset, etc. account… this is for funds being shifted from one financial institution to the next. So if you are making a transfer from your checking account to your savings account, or if you’re making a payment on a credit card (remember credit cards are treated as “bank” accounts in Xero), you’ll want to use the Transfers tab.

      And the Discuss tab is basically just a place to enter reminders to yourself or to coworkers or clients (if their permission settings are high enough) about particular issues with a transaction. In our office, I most commonly see our bookkeepers entering things like “Sent email to Joe asking for details” so we know why the transaction is still pending reconciliation.

      I hope that helps!

  • Hi Patti,

    Thank you for responding and giving a nod to both Transfer and Discuss Tabs. They deserve it.

    Hope this is helpful to others. This is pretty much how we have seen others use them too.

    Have you ever tried adding a URL link for a Paperless Storage app. or an Attachment to a .pdf, Word or Excel document, or external link for a web page to a Discuss Tab? We were wondering if it would accept it, reject it upon saving, or not hyper link when clicked on, upon retrieval.

    This would be nice for workflow documents attached to Global Payment Processor transactions. Or maybe Xero would consider adding this function or a Tab called Workflow or Attachments in Bank Reconciliation to Bank Statements, at least on the Global app. Xero, Global app. has some of this, but not in Bank Reconciliations where Global Payments Accounts are reconciled. What is your or your company’s thoughts on this, being involved in eCommerce and Global Payments?

    Thank you in advance for your response. Hope others will join in with their experiences too.

    Sincerely,

    Tamra

    Tamra Groff, Senior Consultant, GASC/GHFG

    • Hi Tamra,

      No, I hadn’t tried it, but it sounded like an interesting idea, so I gave it a shot. Nope – no hyperlink. It saves the address I typed it, as you would imagine, so you’d just need to copy and paste, but no, you can’t click and have it take you elsewhere. We use that feature a lot in our project management tool, Asana, and it would be great here as well.

      On the flip side… we usually use the Discuss Tab if we’re missing info, so there’s generally not much to attach. If we had support, we’d obviously be creating the transaction and attaching the support there. But I can see how other people might find this to be useful.

      I’m actually not a fan of using web links as support because you have no control over whether or not the target website will change or otherwise be unavailable in the future. I’d much rather have an actual digital copy of the support and attach it that way. Please see more about my opinions on workflow and document management in last month’s article, The Great Paperwork Shuffle (https://www.sleeter.com/blog/2015/08/paperless-accounting/).

      I think it would be a nice Xero feature to be able to attach bank statements directly to a reconciliation report so you can keep the two bits of info together. For now, we save them in the client’s electronic filing cabinet as a historical record in case we ever need them.

  • Hi Patti,

    Agreed. All good points and thank you for testing it. Some Workflow is best in another app., too.

    We were thinking of attaching Bank Statements, as you stated; and/or, a hyper link to the Electronic File Cabinet folders or Paperless Storage app. folders to refer to other notes and other related documents, etc.

    We have a need for tracking Bank Transaction Authorizations and corresponding Documents for a Global Payment from a Global Payments Processor which is tracked by Accounts in Bank Feeds within Xero. The Global Payments Processor put’s out a Monthly Statement similar to a Bank Statement, only that there are Import/Export Trade Documents and Currency Exchange Documents for each Payment. Each Document is scanned and stored separately in an Electronic Filing Cabinet folder or Paperless Storage folder, as a historical record, just like you described for Bank Statements and Reconciliations.

    It would be nice to hyper link a location of these Compliance Documents to each Payment, accordingly. As Accounting needs to verify signage, compliance, and in some cases, things like discrepancies, such as, currency rates, gains, losses, and trade compliance issues, etc.

    For data storage purposes and data volumes for Taxation, Audit, and Country compliance, it probably would be best to have “hype links” to these documents, rather than attaching each document within Xero. That way, Xero accounting and the data base structures would not be affected by these documents in data volumes, storage issues, or potentially getting lost in Accounting, closures, or consolidation processes. Tied back to each Payment is important.

    We already have Spotlight Reporting doing some of the Accounting, Financial models and Business Analysis work for Multi Currency Exchanges, Consolidations, Compliance, etc. We have Workflow app. objects and routers for the Documents from the Global Payment Processor, just no Tie to the actual Payment in Bank Accounts after the Bank Feed or at Bank Reconciliation.

    Glad to hear we are both thinking about these “nice” additions or improvements to Xero.

    Thank you for sharing. Really appreciate your comments and opinions.

    Tamra

    Tamra Groff, Senior Consultant, GASC/GHFG

    • Yep, we’re of the same mind.

      A hyperlink to where the document permanently resides would be helpful. But again… it’s not really permanent. More times than I can count have I filed something away only to move it later (although following a doc link is often different than pointing to specific web page). I think attaching in both places (Xero and the document storage) is most reliable… but also most cumbersome.

      No perfect solutions at the moment.

      Thanks for adding to the conversation.

  • […] 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. […]