One of the most annoying things about using Xero and Shopify: tracking payments with detail. If you’re one of the thousands of businesses using Xero and Shopify, you might go to the standard Shopify integration by Xero, but “The shopify integration by Xero doesn’t account for sales tax and can’t be used by tax registered businesses.” Ouch 😩.
How to properly integrate Shopify & Xero?
There are loads of solutions I’ve tried in the past and they’ve been more than lacking.
They tend to be:
- Expensive to run on the long term – especially with multiple Shopify stores,
- Hard to set up,
- Or not actually what it says on the box – and only do half the job.
So I came up with a process myself, and I think it works well for reconciliation the way I need to do it.
Using this process I am matching on daily payments/payouts from Shopify as I don’t want to store every single customer and every single purchase – but this CAN work on individual customer purchases. I just don’t have time to reconcile tens of thousands of items each year.
The solution makes it super easy to:
- Reconcile in Xero with the click of a button;
- Find previous Shopify payouts and review;
- Update if you’ve missed out on payouts in the past, or review anything that needs to happen.
Here’s how the automation between Xero and Shopify works:
The automation is on a schedule, once daily, near when Shopify payouts are made to our bank account.
Each time the automation starts, it retrieves the latest PaymentID (link to Shopify documentation) that we have stored in a data store, and uses it to search Shopify’s payouts data in the next step.
Using the PaymentID, the automation connects to Shopify and searches for payouts after the last PaymentID stored.
This means, we don’t go through every payout again, saving data and time in the process – this happens every day, you don’t want to have to search for years of payouts just to get to today.
Once any PaymentIDs have been returned from Shopify, we search Xero for them.
This is a quick check, to see if there are already invoices made out with that PaymentID in it. If that’s the case, we don’t need to do anything further – and the process ends for each PaymentID found in Xero.
If that’s not the case, the process continues – and later will store the PaymentID in a new invoice.
Now it’s time to create an invoice for each payout made.
The automation takes the payout from Shopify and does all the hard work required.
- Creates a sales invoice
- Creates a new line in the invoice labelled Payout, with a quantity of 1.
- Inserts into the Unit Amount the sum of fees, charges, refunds, reserved fees, retried payout fees, and the amount from Shopify for the total. (This means Xero records the final amount having removed any fees/charges that were present in the total)
- Notes any tax on income, flagging GST or other taxes for that line item.
- Allocates it to the correct account code.
- Creates a new line in the invoice labelled Charges Fee with a quantity of 1.
- Inserts into the Unit Amount the sum of fees and charges, with a negative value.
- Notes tax on expenses, flagging GST or other taxes for that line item.
- Allocates it to the correct account code for the expenses.
Steps 6 to 9 are the power of this automation. By taking the fees and recording them against an invoice, we are still recording in Xero the correct data for reconciliation. It’s also allocated within a single invoice for checking later. - Creates a reference in the invoice which is the same number provided by Shopify as PaymentID.
- Sets expected and planned payment dates, as the same date in the Shopify Payout.
- Sets status as Authorized.
- Selects the correct currency.
- Does not send to contact.
This action loops as many times as we need, to use up all the Shopify PaymentIDs without corresponding Xero invoices.
PHEW! That’s a lot of work done.
After all that tracking is done, the process is complete. The automation then goes back to our data store, and updates it with the most recent PaymentID from Shopify. This will then be used for retrieval the next time the automation runs.
I have been successfully running this process for the last 4 years, without too much trouble. On the odd day a payout might be missed, or an invoice doesn’t quite work – but all it takes is for a quick update of the data store to run the automation again, and I can find missed payouts to reconcile.
The best bit, is that my accountant is happy, manual labour is probably reduced by about 97%, and the business can claim on what it is owed.
Is this something you’d be interested in having? Contact me to discuss how I can help save your business hours upon hours of effort.