With Shopify merchants have the ability to accept payments via Shopify Payments and also through a whole host of other payment gateways too.
The good news is that Link My Books works for all payment gateways through one single connection to your Shopify account.
For sales paid for via Shopify Payments, Link My Books generates one settlement summary report for each payout you received from Shopify. This means that the invoices you send to Xero or QuickBooks from Link My Books will match exactly the amount Shopify deposited into your bank account. So these are nice and easy to reconcile directly on your bank reconciliation page in Xero or QuickBooks.
If you are accepting other payment gateways such as PayPal, Klarna and Clearpay via your Shopify store, Link My Books will generate a settlement summary report once per month for each gateway summarising the sales and refunds.
These monthly summaries will be cleared through a clearing account (one clearing account per payment gateway).
What is a clearing account?
A clearing account (sometimes referred to as a suspense or holding account) is an account where funds from payment providers that are in transit or as yet undeposited into your bank account are accounted for.
We use these clearing accounts to record the funds that are expected from each third-party payment gateway, which have yet to be deposited into your bank account.
How many clearing accounts do I need?
One for each payment gateway you accept payments through. Don't worry we create these in your chart of accounts for you automatically as they are needed so no action is required from your side.
How do I reconcile these payment gateway clearing accounts?
STEP 1: Send the Link My Books settlement summary report to Xero or QuickBooks. This will debit the clearing account with the net value of your sales minus any refunds via that payment gateway.
STEP 2: Allocate any deposits from the payment gateway to the relevant payment gateway clearing account. This will credit the clearing account.
(We name all payment gateway accounts clearly so it should be easy to identify which is which)
STEP 3: Download your monthly fees invoice from each payment gateway and account for those directly in Xero or QuickBooks separately.
👉 There can sometimes be a balance left in the clearing account at the end of each month, nothing to worry about - this is usually down to funds yet to be deposited into your bank account for sales that occurred during that month.
Full walkthrough of the process
Below is an example of a PayPal settlement summary report produced by Link My Books. We will use this example to walk through the entire process from start to finish.
Some payment processors have bank feeds into Xero or QuickBooks which can be utilised using Option 1 below, for those that don't (or if you'd prefer to skip the bank feeds) follow Option 2 below.
👉 Note that even though this example is for PayPal, the same general process applies to any other payment gateway too.
Option 1 – Using the PayPal bank feed
If you’re using the PayPal bank feed with Xero already then this is the option for you.
Let’s look at an example for one month:
Unlike Shopify Payments, payouts from Shopify-PayPal will not have a matching deposit into your bank account.
This is because the funds from these sales go into your PayPal balance.
To counter this Link My Books allocates these payout balances to the Shopify Gateway Clearing account. So in our example, the £239.38 amount would be allocated to that account as below:
On the PayPal bank feed in Xero the corresponding payments would look like this:
And the PayPal fees like this:
We will allocate the PayPal deposit amounts to the Shopify Gateway Clearing account and PayPal fee line items should be allocated directly to an account you use for PayPal payment processing fees:
This will clear off the 239.38 amount Link My Books allocated to the same account via the settlement invoice, which you can validate by running an Account Transactions report for the Shopify Gateway Clearing account:
Next, you should read our help article on Using bank rules in Xero to speed up Shopify-PayPal reconciling
👉 If you have any follow-up questions after reading this article then please get in touch with support via the blue message widget at the bottom right of this page.
Option 2 – Using a manual journal entry
The second option is if you are not using the PayPal bank feed. In this section, we will show you how to reconcile your Shopify-PayPal settlement invoices generated by Link My Books with the PayPal Activity Download report, as well as how to account for your PayPal fees.
Link My Books generates Shopify-PayPal settlements based on the information available from Shopify. Since PayPal do not pass the information about their fees to Shopify, we are unable to include PayPal fees directly in the settlements either.
Contents of this section
Step 1 – On PayPal, download the report of your activity for the month
Log in to your PayPal account and navigate to:
Activity > Reports > All Reports > Activity download
👉 Important - Once you download the CSV file from PayPal you will need to filter the column “Custom Number” to show only “Shopify” transactions.
The columns we are otherwise interested in are the Gross, Fee and Net columns, so sum those up to show the totals at the bottom like this:
Step 2 – Confirm the Link My Books report matches PayPal
Since Link My Books generates one Shopify-PayPal settlement report per month, all we need to do next is confirm that the figure shown in the PayPal report matches the value of the settlement in Link My Books for that month.
In our example the £49.95 amount shown on Link My Books matches the 49.95 value in the PayPal data:
Step 3 – Allocate PayPal deposits to the clearing account
When you post a Shopify-PayPal settlement to your accounting platform, Link My Books adds a line to allocate the total amount to the Shopify Gateway Clearing account.
When you withdraw funds from PayPal, those deposits should be allocated to the Shopify Gateway Clearing account (assuming that you are only using the PayPal account for Shopify and nothing else).
At the end of the month, the balance of the Shopify Gateway Clearing account should reflect your total PayPal fees.
In our example, £49.95 was allocated to the Shopify Gateway Clearing account for the invoice generated by Link My Books and £47.00 from deposits into the bank from PayPal, leaving £2.95 – which is the amount of the PayPal fees.
Step 4 – Account for your PayPal fees
Now you need to account for the PayPal Fees and balance off the Shopify Gateway Clearing account, luckily you do that in one entry in your accounting software.
In Xero, this can be achieved via a Bill entry:
In QuickBooks, this can be achieved via a Journal entry:
Once you have done this your Shopify Gateway Clearing account balance should be showing as £0.00.
👉 If you have any follow up questions after reading this article then please get in touch with support via the blue message widget at the bottom right of this page.