About a year ago, Paul wrote about the trials and tribulations of using PayPal. There is a lot to not like about PayPal, though most of my criticisms result from doing the bookkeeping for a company that posts a lot of PayPal transactions. Around the time he was writing, Stripe came to Canada, and we cheered (“Hurrrrah!”), because it meant we had another option. A newer option. An option whose UI might be almost navigable! Paul signed up, delighted in Stripe’s really snazzy documentation, and started writing a front end for us to send invoices that clients could pay by credit card, using Stripe.

At our accountant’s suggestion, we use Xero for our bookkeeping at WonderProxy, and, for the most part, we’re happy with it. Xero can automatically import statements from your bank accounts (including your PayPal account, which is important for us), track accounts payable and receivable, and track transfers between accounts seamlessly. Like I said, we’re mostly happy with it: it has trouble with one of our credit cards, so I have to manually import its statements, which is a bummer, but what can you do? Customer support has always been prompt and, while they’ve sometimes been unable to fix the problem, have at least explained what’s happening and suggested workarounds. It’s satisfactory… and our accountant likes them, so y’know, that seems like a good thing too.

When we started using Stripe, Paul and Will wrote their own front end to manage payments. It’s really easy to use, and has made invoicing customers a genuine pleasure, especially when I think about creating PayPal invoices. It’s another bit of functionality that we’re really happy with. At the time, we didn’t really think about how to get all the Stripe activity correctly represented in Xero. I didn’t actually bother to tackle the problem until our financial year ended and it was time to deal with taxes. Xero supports Stripe as one of its payment processors for its built-in invoicing service, but we don’t use that, and I couldn’t figure out how to make Xero automagically figure out our Stripe transactions otherwise.

Armed with the knowledge that only tracking Stripe’s transfers to our bank account was Bad Accounting, I set out to import our Stripe transactions and properly reconcile them against the transfers to our bank account. If anyone is trying to do the same in Xero, this will hopefully help.

1. Create a new bank account for your Stripe transactions. From the Accounts tab, choose Add Bank Account. I chose the Bank Account account type. It’s probably the most accurate representation of what Stripe is, given the options.
2. Log into Stripe, and download all your transactions as a CSV. Save them as something meaningful, like “Stripe Transactions Date-Range”
3. Still logged into Stripe, navigate to the bank transfers, and download all the bank transfers. You’re going to match these against the statement lines in your bank account, so that everything matches up nicely. Save them as something meaningful, like “Stripe Transfers Date-Range”.
4. If you reconciled the transfers from Stripe to your bank account (like I stupidly did), you need to un-reconcile all of them. This is a pain, but necessary. Do that now.
5. Create CSV subfiles.First, Xero doesn’t understand Stripe’s time format. Awesome, eh? So, you need to remove the time portion of the date-time field. I did this in TextMate with a really long cursor so I could delete it all at once. You could also create another column and do some magic in Excel, selecting only the yyyy-mm-dd portion of the original column. Make sure you do this for both the transactions and the transfers.Second, Stripe’s CSV output doesn’t deal with fees and refunds in a way that Xero understands. You need to pick the columns that Xero will actually need and create new CSV files:
   • a “Payments” file that includes the ‘Created’, ‘Description’ and ‘Amount’ columns,
   • a “Refunds” file that includes ‘Created’, ‘Description’, and ‘Amount Refunded’ for any refunds, and
   • a “Fees” file that includes ‘Created’, ‘Description’, and ‘Fee’.

   For some reason, Xero likes the first column to be the date column, so putting ‘Created’ first is important.

6. Import your appropriately-formatted CSV files into Xero. Reconcile. Rejoice!

It’s a weirdly complicated process, since Stripe’s CSV dump isn’t formatted in an immediately usable way. I’m curious about how Xero deals with Stripe transactions that result from its invoicing service. Perhaps there’s something brilliant that I’m missing. Nevertheless, I’m happy that this way, we account for the full amount our customers pay us, the fees we pay Stripe, and the amount netted, which is transferred to our bank account. I’m planning to do the Stripe import (manually) once a month, so it’s not too onerous. If I’m particularly fancy, perhaps I’ll write something to automate it for me… probably not though.