Matching in Xero & batch payouts

grryan
Tourist
8 0 2

Hi, everyone:

 

Can anyone offer suggestions on the issue we have? We use Shopify Payments, which sends batch payouts: we have it set to daily payouts. We use Xero for accounting (and TradeGecko for inventory). This makes the process of matching (reconciling) TradeGecko invoices to Shopify payouts very tedious because we have to fish through the batch because there is not enough information and resolution in the batch for Xero to make a good guess at the match. If the Shopify order number came through with the batch, it would be much better, but since the batch has many orders, the Shopify order number is not used in the Reference field of the batch.

 

To describe it another way, those orders paid with PayPal are easy to match because the payout from PayPal is sent one per order. How can we get payouts to on each order instead of in a batch? Is there a different payment provider that might work better for us?

 

Thank you very much!

Ryan

Replies 14 (14)
Parextech
Shopify Partner
42 1 4

Hi Ryan,

We work on intergrating Shopify to Xero and QuickBooks through an app.

While the QuickBooks app is live and is used by many clients already, Xero app is under review and should be out later this month.

 

I have suggestions to answer to your questions and simplify your work. We can get you specific information related to a payout as well.

Each transaction of a payout and the fees for each.

 

Please email me at kumar@parextech.com and we can discuss more in email/phone.

Thank you.

aVers
New Member
2 0 0

Hi Ryan,

 

I wrote an article about reconciliations in Xero: 

 
It is around a particular integration but it explains all required accounting ins and outs.
grryan
Tourist
8 0 2

Thanks! Since my set-up involves an inventory management software in between Shopify and Xero, I developed this Google Sheets spreadsheet that splits a Shopify batch into single bank statement transactions. Instructions:

 

  1. Export from Shopify then import the CSV of the Shopify batch into Sheet 1.1.
    Screen Shot 2019-11-18 at 1.02.43 PM.png
  2. Export from Shopify then import a CSV of the Shopify orders that are in the batch. (Choose dates that encompass the orders in in the batch.)
    Screen Shot 2019-11-18 at 1.02.48 PM.png
  3. (There are some hidden worksheets that transform the data into the format Xero needs and output it into Sheet 5.)
  4. Verify that the sum in Sheet 5 matches the payout; export that worksheet.
  5. Import the CSV of Sheet 5 into Xero.
    Screen Shot 2019-11-18 at 1.13.35 PM.png
  6. Reconcile the transactions individually, and delete the single-line payout-sum.

 

If you would like to work with this file, please make a copy of it for yourself (File > Make a copy) in your own Google Drive because the link is read-only. I hope that helps someone!

 

— Ryan

 

 

Jennifer_Farnel
Tourist
9 0 11

Hi Ryan - your spreadsheet has saved us literally hours a week!! Thanks for your awesome work - you've made a genuine difference in our lives 🙂

sophie2020
New Member
6 0 0

Hi trying to use this template but Sheet 5 doesn't seem to be giving any more information than in the Payout section - is it still working this template please? Thanks S

grryan
Tourist
8 0 2

Hi, Sophie.

Yes, it still works as expected for me.

— Ryan

sophie2020
New Member
6 0 0

Thanks Ryan 

We're trying to upload the detail of the Payouts to Xero (so that there is a line for each Shopify Sale and a line for each corresponding Shopify Fee) and then reconcile against the lump sum Payout already in Xero (or delete it as suggested). When I get to Sheet 5 it is only giving me the same information that is already in Xero - so the lump Payout amount? Apologies if I'm missing something but I want to upload more of the detail that is in Sheet 1.2 but when I get to Sheet 5 the only fields I can see are: Date (which is set at 1899!) / Amount / Reference / Description? 

S

 

grryan
Tourist
8 0 2

Hi, Sophie.

It is difficult to suggest what the issue might be without looking at the same file and data you are looking at. To troubleshoot and make modifications, I can only suggest that you examine the formulas in each worksheet/tab worksheet-by-worksheet in sequence, including the hidden sheets. The date of 1899 indicates an underlying value of ≤ 1 is taking the date "format". Good luck!

— Ryan

Mark157
Shopify Partner
3 0 0

Hi Ryan,

Came across your post and solution after the add-on's built into Shopfify are not suitable as they want to send all orders into Xero.

We only want to send payments.

Have tried your sheet but having a problem with the date field.  Please let me know if you could walk me through the process with a one on one session.  Also please let me know the cost for your time.

My contact details are mark@cfoconsultant.com.au

Good on you for sharing the spreadsheet with the world!

Cheers,

Mark

Mark157
Shopify Partner
3 0 0

Update

Thank you Ryan for being in touch.  Really appreciated.

Your sheet is working well and the suggestion to change the sheet to the correct "Locale" under settings fixed the date issue.

You have really created a wonderful tool for those lucky enough to have stumbled across it!

 

grryan
Tourist
8 0 2

Thanks, Mark. It was nice to chat.

To expand on Mark's update, it was from

Sheets > File > Spreadsheet settings

that we were able to change the file's “locale“, which affected the date format (dd/mm/yyyy versus mm/dd/yyyy).

Another solution may be to be sure you import the CSV directly into Sheets instead of first opening the CSV in Excel and copying cells there to paste into Sheets. I suspect this would work because the Shopify exports use ISO format for the date, which should signify to Sheets that those cells are of date datatype and not simply string data.

Waterus
New Member
2 0 1

Thanks for this sheet - it looks great! i am having an issue too - when I try to import the CSV into Xero it says "

  • The file you uploaded does not contain valid statement data. Please check the file.

This might also be a date issue as I noted on Sheet 5 the array formula date was inverse of the line items - see below. I have the locale set correctly 

 

10-Jun-2021
6/10/2021
6/10/2021
6/10/2021
6/10/2021
6/10/2021

 

grryan
Tourist
8 0 2

Hi, Waterus:

Yes, I think you are right about the date. It looks like I had that first date cell (A2) formatted incorrectly, but I believe I've fixed it no; I don't have the ability to test it in its entirety. (You can also open the resultant .csv by itself in a spreadsheet (like re-opening it in Sheets) or plain-text editor (e.g., Notepad on Windows, TextEdit on MacOS, or Visual Basic Code on either) to manually edit a small glitch like this, too.)

I hope that helps!

— Ryan

Waterus
New Member
2 0 1

Ryan - thanks SO much - I have just managed to get this to work and import 350 lines into Xero in one hit and it is AMAZING! Thanks for your responses