Avoiding Stripe and Xero Integration Headaches
Stripe and Xero are two of the biggest players in their own industries. Xero is of course owning the small business accounting space in Australia and New Zealand. Stripe is doing great things in the world of accepting web based credit card payments.
For two systems of such popularity in perfectly compatible industries, you would expect they’d play nice with each other. Unfortunately that isn’t the case.
We get regular questions about the best way to reconcile Stripe transactions in Xero, so we’ve brought in expert James Rose to take you through it step by step. James hated manually importing Stripe transactions into Xero so much he built an app “Silver Siphon” to automate it.
Take it away James …
Xero does support a large number of payment gateways, allowing your clients to pay their invoices online. But that is where the integration ends. If you process payments externally to Xero and you want to reconcile that data, you’re in for a world of bookkeeping pain.
Ideally, you would be able to pull in your transaction data like you would for a bank account. With bank accounts you get every single transaction and some details you can use for identification.
That allows you to set up bank rules so instead of matching every transaction, you simply hit that nice green button Xero shows. It also does an amazing job of detecting outstanding invoices which match the dollar amount.
If you go back and try to set up a new account for something like Stripe, you’ll notice that Stripe and every other gateway (bar PayPal) is left off the list.
That means you only really have a couple of options to do your books properly:
Reconcile Stripe’s lump sum payments into your bank account, net fees. This isn’t too bad if you only have a couple of transactions per lump sum. If you have more than a handful, this process gets tedious quickly (There’s a step-by-step for this below).
Periodically export Stripe data, manipulate it into a format Xero understands and import it. Finally, you’ll get to use those Microsoft Excel skills you’ve lied about on your resume for years. If you need your books to be up to date, you’ll need to do this process once a day.
Step By Step Guide
1. Reconciling lump sum Stripe payments against multiple invoices
When Stripe transfers get deposited into your account, they look like this:
Click one of the Find & Match buttons
It will show a list of outstanding invoices. Go through and select all the invoices that make up the lump sum transfer.
Yes, this part is a massive pain. You will probably need to have two browser windows open, one with your Stripe dashboard, to identify the relevant payments.
At this point the total will be out by the amount of fees deducted by Stripe.
To account for that, click the adjustments button in the bottom corner and select “Bank Fees”. Enter the amount that the total is out by and it will allow you to reconcile when everything matches up.
2. Reconciling lump sum Stripe payments against codes
If you do not need to reconcile against invoices, it can be a bit more tedious.
There are actually a few ways you can handle this. I’m unfortunately not the best person to talk to about this. Bean Ninjas are.
It will come in handy to know what the fee items are for each transfer. To see that, you can either export your data from Stripe (read on) or view it directly in your Stripe dashboard.
To do that, over in Stripe click on the ‘transfers’ menu item, then select the transfer in question. Down the bottom you will see a breakdown of what parts make up this transfer.
3. Manipulating Stripe data for import into Xero
First, create a CSV export in Stripe.
- Go to Payments -> View All
- Filter to the dates required
- Click export
Now the trick is to get the data into a format Xero understands.
Generally, that means creating these columns in your CSV:
These fields are pretty self explanatory. Depending on your Excel skills you can either copy and paste, write some functions or write a macro that you can use every time you need to go through this process.
The most important part is that you extract the fees for each payment into their own transaction with a negative amount.
Over in Xero, you will need to have created a new dummy Stripe “bank” account. Then you can simply import statements directly into this.
The Easier Way
As you’ve probably gathered, these solutions are kind of a pain. They’re also pretty good at draining your time. If you’re like me, the amount of time you spend on bookkeeping is inverserly proportional to your enjoyment of life in general. I don’t know how these Bean Ninjas do it, but I’m sure glad they do.
I recognised this whole problem in mid 2014, while helping out a client with setting up Stripe to handle their monthly payments. It turns out lots of businesses suffer the same problem. Our own ninjas (of the code variety) spent the next 6 months grinding out an app that automates the second option.
Now all you have to do is plug in your Stripe account, plug in your Xero account and all your Stripe transactions will appear in Xero. Makes is a bit easier, huh?
We hope this has been a useful article. If you have any questions, please feel free to leave a comment below.
- Avoid Stripe and Xero headaches: Step by Step Guide - 27 June, 2019