Orders6 min read
Match Shopify Payments Payouts to Bank Deposits and Orders
Export one row per Shopify Payments payout to tick off bank deposits, then the transactions inside each payout to tie every deposit back to its orders, fees and refunds.
In this guide
Your bank statement shows a deposit from Shopify. Your order list shows dozens of sales. Somewhere between the two, Shopify Payments took its fees, paid back refunds, held a reserve and grouped several days of charges into one transfer. Matching one side to the other by hand is the most tedious part of closing the month.
This guide walks through doing it with two reports in Columna: one that lists every payout, so you can tick off each bank deposit, and one that lists every transaction inside those payouts, so you can explain each deposit down to the order.
Why the deposit never equals your sales
A Shopify Payments payout is a net amount. For each payout, Shopify starts from the payments customers made and then subtracts or adds:
- refunds you paid back to customers,
- adjustments and disputes,
- reserved funds, retried payouts, advances and rebate credits,
- the fees Shopify took.
What is left is what arrives in your bank. So a reconciliation has two layers: does every payout match a bank line, and does every payout add up to the orders and fees behind it.
Before you start: allow payout access
Both payout reports read your Shopify Payments account. In Columna: Order Export & Sheets, open Templates, then the Accounting tab. The first time, the button on a payout report says Allow payout access. Click it and Shopify asks you to allow read-only access to your Shopify Payments account and payouts. You do this once.
If your store does not use Shopify Payments, these reports are greyed out with the reason, because there are no payouts to read. In that case use your payment provider’s own statements.
Layer 1: one row per payout
On Shopify Payments payouts, click Export report. The Orders page opens with the report selected. Set the date range to the month you are closing, click Export, choose CSV or Excel, and click Export again.
The date range picks payouts by the date they were issued. The order filters on that page (status, tags, search) do not apply to this report, and if you had orders selected you will see “Payout reports cover a date range, not selected orders”: clear the selection and export again.
Each row is one payout:
| Column | What to do with it |
|---|---|
| Payout date | The day the payout was issued, in your store’s time zone |
| Payout ID | The payout number shown in Shopify admin; note it next to the bank line |
| Status | Scheduled, In transit, Paid, Failed or Canceled |
| Type | Deposit (to your bank) or Withdrawal (from your bank) |
| Bank reference | The trace reference from your bank, when Shopify has it |
| Gross charges | Payments received from customers |
| Refunds | Refunds paid back, as a negative amount |
| Adjustments | Adjustments and disputes |
| Reserves and other | Reserved funds, retried payouts, advances, rebate credits |
| Fees | Every fee Shopify took from the payout, added up |
| Net | The amount Shopify sent to your bank |
| Currency | The payout currency |
Ticking off the bank statement
Put the export next to your bank statement for the same month and work down the Net column:
- Match each Paid payout’s Net to a bank credit of the same amount. When there is a Bank reference, it is the quickest way to be sure you have the right line.
- A Withdrawal is money Shopify took from your bank, usually when refunds and fees were larger than the sales in that period. Look for it as a debit, not a credit.
- Payouts that are Scheduled or In transit at month end will land in your bank next month. Note them as timing differences rather than chasing them.
- A Failed or Canceled payout never reached the bank, so it has no bank line. Check it in Shopify admin under the same Payout ID.
If every Paid payout has a bank line and every Shopify bank line has a payout, layer 1 is done.
Layer 2: what is inside each payout
Your accountant will usually want more than “Shopify paid us this amount”. The Payout transactions report gives one row per charge, refund and adjustment, with the order it belongs to. Export it the same way, with the same date range.
The date range here selects transactions by the date of the payout they were paid in, so the two exports describe the same set of money. Transactions not paid out yet, transfers and test transactions are left out.
| Column | What it holds |
|---|---|
| Transaction date | When the charge, refund or adjustment happened |
| Type | Charge, Refund, Adjustment, Dispute and so on |
| Order | The order number, when the transaction belongs to an order |
| Payout date | The issue date of its payout; blank when that payout was issued outside the date range |
| Payout ID / Payout status | The payout it was paid in |
| Amount / Fee / Net | The amount, Shopify’s fee, and what is left |
| Currency | The transaction currency |
Tying a payout back to its orders
Open the transactions file in a spreadsheet and filter it by one Payout ID. Then:
- The sum of Net for that payout should equal the payout’s Net in the first file.
- The sum of Fee is the fee total for that payout, which is what you book as a payment processing expense.
- Every Charge row has an Order number, so you can trace each part of the deposit to a sale. Refund rows point to the order that was refunded.
A pivot table on Payout ID with sums of Amount, Fee and Net turns the whole month into one small table your accountant can check line by line against the payouts file.
Things to know before you rely on it
- The Order column is the Shopify order name, such as #1042. If you issue invoices from an invoicing app with its own numbering, map the two yourself.
- Payout amounts are shown exactly as Shopify reports them. Shopify Payments does not include VAT per payout, so a payout cannot tell you how much VAT you collected. For VAT, use a sales journal or the EU OSS / IOSS report from the same Accounting tab.
- Everything is read and laid out in your browser. Nothing from your payouts is stored by the app.
If your accountant also wants a sales journal
On the same Accounting tab there are two sales journals, one for Belgium and one for France (journal des ventes). They list sales and credit notes by date with the net amount per VAT rate, the VAT and the gross amount, and they work with every payment provider, not only Shopify Payments. A refund of an older order appears on the day it was made. If you sell from Belgium or France, exporting the journal and the payout files for the same month gives your accountant both sides of the books: what you sold, and the money that came in for it. The accounting section of the Columna page shows the other order exports accountants usually ask for.
These files are worksheets, not filed returns or bookkeeping entries. Compare the totals with Shopify’s own finance reports before you close the month, and keep the exports with your records so the numbers can be traced later.