Xero in on Xero
Recently, we discussed what bank feeds in Xero are, and how to set up the automated versions. But what happens if a bank doesn’t support the live feed into Xero, or if there is another reason that the feeds can’t be turned on for a particular client?
Fortunately, Xero offers a wide range of file import options beyond the live feeds to get bank data into the platform, so everyone can share in the awesomeness of real-time bank reconciliation. Some of them, such as .ofx files, don’t require any formatting or additional work. Just log into your bank website, select the date range, and then export to the appropriate format such as .ofx or .qif
Other formats, such as .csv, require some mapping or manipulation, and often contain additional details that can be helpful when reconciling, such as cardholder name or vendor industry. These extra details are often very useful when reconciling an account with multiple card holders, for example. It's usually worth the extra effort to set these files up and then import.
There are a couple points to make when working with .csv files. Xero is particular about the formatting inside the .csv file, especially with the date and the amount columns. Amounts should be formatted as numbers without any currency symbols or commas and must be in one column (rather than separate columns for debits and credits). For more examples and information on importing .csv files, Xero has a great support article here.
After you have the .csv import file set up, you can go to Xero and import it. For this file format, Xero gives you an extra screen where the mapping actually takes place - this is where you can select additional fields such as reference, description, and others.
As mentioned above, some common issues when importing files are not setting up the columns with the appropriate format. Also, errors occasionally pop up if you do a download in the middle of the day. For instance, if you initially import one file at noon on Tuesday and then later select Tuesday again, you may end up with duplicates in the file. This is more likely when using a .csv import - Xero is very good at picking up duplicates in the .ofx and the other file types.
If you do import a file with the signs reversed (and we’ve all done it), no problem - just go to the Bank Statement tab for that account, select “Statements” instead of “Statement Lines”, then find the offending import and delete it. Now you’ve got a clean slate to start over from - use the old Excel trick of multiplying by -1 to flip the sign, and then import again.
Now that you know all the ways to get bank data into Xero, you will never have to look at a traditional bank reconciliation sheet again. Plus, your clients will all be able to participate in real-time accounting. Next step - multiple ways to actually code that bank data.
XPT: Always download and import your files through the previous day. This will help prevent duplicates, especially if you do it consistently. Also, some Yodlee feeds are set up to pull in pending transactions rather than just cleared transactions. If your bank has one of these, you'll notice quickly over a weekend, because you'll see the same transaction pull in multiple times. In these cases skip the automatic import and go ahead and do it manually.
Dan Schmidt
Author Bio: Dan Schmidt is the CEO of The Emerging Business CFO, a virtual accounting and financial advisory firm that works to free founders and entrepreneurs from the stress of managing the daily operational grind. The company offers operational accounting, systems setup and fractional CFO services. Twitter: @ebcfo