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 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 the Shopify Gateway Clearing account, which we create in your chart of accounts automatically.
What is a clearing account?
The Shopify Gateway Clearing account is an account on your balance sheet where funds from PayPal that are in transit or as yet undeposited into your bank account are accounted for.
We use the Shopify Gateway Clearing account to record the funds that are expected from PayPal, which have yet to be deposited into your bank account.
How many clearing accounts do I need?
If you're accepting payments on Shopify through other payment gateways such as Klarna, Clearpay or Afterpay etc. then we'll create one clearing account for each of them. Don't worry we create these in your chart of accounts for you automatically if they are needed so no action is required from your side.
In this article, we'll focus on PayPal, but we have other help articles focusing on Klarna and other payment gateways in our Other Payment Gateways help section which you may find useful.
How do I reconcile the Shopify Gateway Clearing account?
STEP 1: Send the Link My Books settlement summary report for PayPal to Xero or QuickBooks. This will debit the clearing account with the net value of your sales minus any refunds that were paid for via PayPal.
STEP 2: Allocate any deposits into your bank from PayPal to the PayPal clearing account. This will credit the clearing account.
STEP 3: Account for any PayPal fees 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.
PayPal has a bank feed into Xero or QuickBooks which can be utilised using Option 1 below, but if you'd prefer not to use the PayPal bank feed option due to your store having a high volume of orders then follow Option 2 below.
Option 1 – Using the PayPal bank feed
If you're using Xero follow our guide here to set up Xero bank rules to allocate your PayPal orders to the Shopify Gateway Clearing account and your PayPal Fees to a bank fees or PayPal fees account accordingly.
If you're using QuickBooks follow our guide here to set up QuickBooks bank rules to allocate your PayPal orders to the Shopify Gateway Clearing account and your PayPal Fees to a bank fees or PayPal fees account accordingly.
Once you have set up your bank rules that will clear off the amount Link My Books allocated to the Shopify Gateway Clearing account via the Shopify-PayPal entry we posted to your bookkeeping software, which you can validate by running an Account Transactions report for the Shopify Gateway Clearing account:
In Xero that would look something like this:
In QuickBooks that would look something like this:
You may want to ask your accountant to help you with this or reach out to our support team for further assistance.
👉 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.