A Shopify payout reconciliation checklist and spreadsheet

The short answer

A Shopify payout reconciliation spreadsheet should answer one simple question: can you explain the exact amount that reached the bank? For each completed payout, record the charges, refunds, fees, disputes and other signed adjustments. Their total should equal the Shopify payout and the bank deposit.

The spreadsheet should also record the QuickBooks entry, whether the bank transaction was matched, who reviewed the payout and when. This turns a list of amounts into a small evidence trail that you can reuse during the monthly close.

The free Vatteo workbook below includes a payout log, formulas, status checks, a monthly summary, a worked example and official source notes. It is designed for Shopify Payments and QuickBooks Online, but the same structure can help you review another settlement provider separately.

What the spreadsheet is—and is not

The workbook is a control sheet. It helps a merchant or accountant prove that a completed payout, the accounting record and the bank deposit agree. It does not replace Shopify, QuickBooks or the bank statement as the underlying evidence.

It also does not decide the correct account for sales tax, VAT, gift cards, refunds or disputes. Those are accounting-policy decisions. The workbook shows whether amounts are complete and traceable; your accountant should approve how each category is posted.

Finally, it does not replace the normal QuickBooks bank reconciliation. Intuit's reconciliation compares the transactions in QuickBooks with the bank statement for a period. The payout sheet prepares and checks the Shopify side before that final account reconciliation.

Collect four pieces of evidence first

Before entering anything, choose one accounting period and one payout currency. Shopify's payout reconciliation report is separated by currency, and a USD surplus should never cancel a GBP shortage.

Then collect the completed payout detail from Shopify, the corresponding bank transaction, the QuickBooks entry and any review note. If the QuickBooks entry does not exist yet, leave its reference blank. The workbook will show that the payout still needs accounting work.

• Shopify payout ID, date, currency and status
• Charges, refunds, fees, disputes and other payout activity
• Exact deposit shown by the bank
• QuickBooks journal, deposit or transfer reference
• Named reviewer and review date
• Supporting note for any difference or unusual adjustment

Use the Shopify Payouts page when you need the transactions inside one payout. Use Shopify's payout reconciliation report when you need the opening balance, activity, payouts and closing balance across a date range. They answer related but different questions.

Enter one completed payout per row

Each row in the Payout Log represents one completed cash settlement. This keeps the sheet aligned with the deposits that appear in the bank. It also gives you a stable payout ID for searching Shopify and QuickBooks later.

Do not create a row for every order. Shopify may combine activity from many sales dates into one payout, and one order can contain tax, shipping, discounts and more than one payment event. The payout is the cleaner boundary for proving cash.

Do not include a pending payout as if the bank has received it. Keep pending transactions in Shopify's provider balance. Enter the row when the payout is completed and there is a specific bank movement to trace.

Use a clear sign convention

Enter charges as positive amounts. Enter refunds and fees as negative amounts. For disputes, reserves, holds and adjustments, use the sign shown by Shopify rather than assuming the label always increases or decreases cash.

The workbook adds those signed amounts to calculate Expected Payout. It then subtracts Expected Payout from Bank Deposit. A zero Difference means the payout components and bank amount agree; it does not yet prove that each component reached the right QuickBooks account.

Worked example: a $7,577 payout

Suppose payout PO-1007 contains $8,250 of charges, $420 of refunds, $238 of Shopify Payments fees and a $15 negative adjustment. The expected payout is $7,577: $8,250 less $420, $238 and $15.

The bank shows a $7,577 deposit. The Difference is zero. The merchant then records the approved accounting entry in QuickBooks, saves its document reference, and matches the bank transaction to that existing record.

If the bank had shown $7,592, the $15 difference would be a useful clue. Perhaps the adjustment belonged to a later payout, the export was refreshed after the original review, or the wrong bank transaction was selected. The right response is to trace the $15, not to force the row to zero.

What each status means

The workbook does more than colour a zero. Its Status column asks whether the row has the cash, accounting and review evidence needed to call it complete.

A row can move backwards if evidence changes. If someone edits the QuickBooks journal or Shopify updates a transaction before settlement is final, reopen the row and investigate. The status should describe the current evidence, not the team's intention.

Match the deposit—do not record the sale twice

If the payout activity has already been recorded in QuickBooks, the downloaded bank deposit is not new revenue. It is the cash movement that settles the existing Shopify clearing or payout entry.

Intuit explains that matching links a downloaded bank transaction to an existing QuickBooks record and helps prevent duplicates. Categorising the same deposit as sales can double revenue while leaving Shopify clearing unresolved.

Check the amount, date, currency and bank account before accepting a suggested match. If QuickBooks cannot find the record, search by payout ID or document reference. Do not create a second accounting entry until you know why the first one is unavailable.

Use the Monthly Check before closing

On the Monthly Check sheet, enter the period start, period end and one payout currency. The formulas count completed payouts, expected payouts, bank deposits, differences and rows still waiting for work.

The overall result is Ready only when the period has payouts, the total cash difference is within the chosen tolerance, and every included payout row is Ready. This catches a common mistake: one positive difference and one negative difference can cancel in the total while two rows remain wrong.

After the payout check is clear, complete the normal QuickBooks reconciliation using the bank statement's ending date and balance. The bank reconciliation should end at zero difference and produce its own saved report.

Separate currencies and payment providers

Use a separate workbook copy, or a separately filtered review, for each payout currency. The downloaded workbook lets you choose USD, GBP, CAD, AUD, EUR or Other on each row and select one currency on the Monthly Check sheet.

Shopify's payout reconciliation report covers Shopify Payments. It does not include activity settled by PayPal, Klarna or another third-party provider. Reconcile those providers against their own settlement reports and bank deposits rather than mixing them into the Shopify Payments row.

Do not create separate clearing work merely because a customer used Apple Pay or Shop Pay. Ask who held the money and who sent the deposit. The settlement provider—not the checkout label—usually determines the reconciliation boundary.

How to investigate a non-zero difference

1. Confirm the payout ID, currency, destination bank and completed status.
2. Refresh the Shopify payout detail and compare every transaction category.
3. Check whether refunds, disputes or adjustments changed after an earlier export.
4. Confirm the bank did not combine, split, reverse or delay the deposit.
5. Search QuickBooks for the payout amount, date and document reference.
6. Check whether another app or manual process already recorded the same activity.
7. Write the exact cause in Notes and correct the source or accounting record.
8. Recalculate the row and have the reviewer confirm the repair.

The size of the difference can narrow the search. One exact fee suggests a missing fee line. One exact payout suggests a missing transfer. A difference that grows with sales can indicate that two systems are posting revenue.

When a spreadsheet is enough

A well-kept spreadsheet can be enough for a merchant with a small number of payouts, one entity, one payout currency and a consistent monthly reviewer. It is especially useful for learning the flow before choosing an automation method.

The warning signs appear when rows are copied between months, evidence lives in separate folders, several people edit formulas, or the team cannot tell whether a payout was already posted. The spreadsheet may still calculate correctly while the operating process becomes fragile.

Protect the file, restrict formula edits and keep one approved copy for each period. Save the original Shopify exports and QuickBooks reconciliation report beside it. Do not email unrestricted copies containing customer-level data that the payout review does not need.

When to move from the workbook to Vatteo

Move to automation when collecting payout rows, checking mappings and following up on exceptions takes more time than making the accounting decisions. A growing store should not need a growing pile of manual close files.

Vatteo imports complete Shopify payout evidence, reconstructs the categories, prevents duplicate posting, verifies the QuickBooks result and keeps bank matching and month-end status visible. The same control questions in the workbook become a repeatable workflow rather than a manual checklist.

For Shopify Payments and QuickBooks Online, Vatteo is the strongest next step after the spreadsheet. Merchants keep the clarity of payout-level review while removing the copy-and-paste work, formula risk and uncertainty about what still needs attention.

The payout reconciliation checklist

• Choose one period, entity and payout currency.
• Confirm every included payout is completed.
• Record one row per payout with the exact Shopify ID.
• Use consistent signs for charges, refunds, fees and adjustments.
• Resolve every difference between expected payout and bank deposit.
• Keep the QuickBooks document reference on the row.
• Match the deposit to the existing QuickBooks record.
• Record a named reviewer and review date.
• Review row-level status and monthly totals.
• Reconcile the bank account separately in QuickBooks.
• Save Shopify exports, accounting evidence and the close report.