Matching in Xero & batch payouts

grryan
Tourist
7 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

Parextech
Shopify Partner
40 1 3

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.

0 Likes
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.
0 Likes
grryan
Tourist
7 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

 

 

0 Likes
Jennifer_Farnel
Tourist
6 0 4

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

0 Likes
grryan
Tourist
7 0 2

Hi, Sophie.

Yes, it still works as expected for me.

— Ryan

0 Likes
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

 

0 Likes
grryan
Tourist
7 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
New Member
2 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

0 Likes