Expert guide

E-commerce Order Reconciliation in Excel

Orders to payouts to bank deposits, with every fee, refund and missing dollar explained

Free Cost Audit

Call us at +1 (385) 386-3860

Short answer: import three files each week, the store order export, the payment processor or marketplace transaction-level payout report, and the bank statement, into Excel Tables with Power Query. Match orders to transactions by order number or transaction ID, sum matched transactions by payout ID, and match payouts to bank deposits by amount and date. Exception lists then show unpaid orders, unmatched transactions, payouts not in the bank, and unusual fees. For direct API feeds instead of exports, see Excel API integration.

For an online store, "sales" means three different numbers: what the store platform says you sold, what the payment processor says it paid you, and what actually landed in the bank. They never match exactly, because fees, refunds, chargebacks, reserves and timing sit between them. Most small sellers accept the difference without explaining it, which is how missing payouts, double refunds and lost chargeback disputes go unnoticed.

The Three Sources

SourceWhat it containsKey for matching
Store order exportOrder number, date, customer, gross, discounts, shipping, tax, payment gateway, refundsOrder number
Processor / marketplace payout reportTransaction ID, order reference, type (charge, refund, fee, chargeback, adjustment), gross, fee, net, payout ID, payout dateOrder reference and payout ID
Bank statementDate, description, amountPayout amount and date (and payout reference in the description, if present)

Always use the transaction-level payout report, not the payout summary. The summary tells you a deposit happened; the detail tells you which orders, fees and refunds make it up.

Step 1: Import With Power Query

  • Save each week's exports into three folders: Orders, Payouts, Bank.

  • Build one query per folder that appends all files and standardises column names and types.

  • Remove duplicates on the natural key (order number, transaction ID, bank line), since exports often overlap.

  • Load as tblOrders, tblTxns and tblBank.

Step 2: Orders to Transactions

On tblOrders:
Charged (processor gross):
=SUMIFS(tblTxns[Gross], tblTxns[OrderRef], [@OrderNo], tblTxns[Type], "charge")

Refunded:
=-SUMIFS(tblTxns[Gross], tblTxns[OrderRef], [@OrderNo], tblTxns[Type], "refund")

Fees:
=-SUMIFS(tblTxns[Fee], tblTxns[OrderRef], [@OrderNo])

Difference (store total vs charged):
=[@Total] - [@Charged]

Status:
=IFS([@Charged] = 0, "Not paid",
     ABS([@Difference]) > 0.01, "Amount differs",
     TRUE, "Matched")

Orders paid by gift card, manual payment or a different gateway will show Not paid against this processor; add the gateway column to the condition so only orders expected through this processor are checked.

Step 3: Transactions to Payouts

Payout summary table (one row per PayoutID, built with UNIQUE):
Net from transactions:
=SUMIFS(tblTxns[Net], tblTxns[PayoutID], [@PayoutID])

Reported payout amount:
=XLOOKUP([@PayoutID], tblPayouts[PayoutID], tblPayouts[Amount])

Check:
=ABS([@[Net from transactions]] - [@[Reported payout]]) < 0.01

Step 4: Payouts to Bank

Bank match (same amount within 5 days after payout date):
=IFERROR(XLOOKUP(1,
   (tblBank[Amount] = [@[Reported payout]]) *
   (tblBank[Date] >= [@PayoutDate]) * (tblBank[Date] <= [@PayoutDate] + 5),
   tblBank[Date]), "NOT IN BANK")

If the bank description includes the payout reference, match on that instead; it is unambiguous even when two payouts have the same amount.

Exception Lists to Work Weekly

ExceptionLikely cause
Order not paidPayment failed, manual payment, different gateway, or fraud cancellation not recorded
Amount differsPartial refund, edited order, currency conversion
Transaction with no orderTest charge, manual invoice, order deleted in the store
Payout not in bankDelayed, held in reserve, sent to an old bank account
Chargeback without responseDispute deadline approaching
Fee % above normalInternational cards, currency conversion, a pricing change

Fee and Channel Analysis

Effective fee rate by channel and month:
=SUMIFS(tblTxns[Fee], tblTxns[Channel], A2, tblTxns[Month], B$1) /
 SUMIFS(tblTxns[Gross], tblTxns[Channel], A2, tblTxns[Month], B$1, tblTxns[Type], "charge")

Seeing the true cost per channel, including marketplace commissions and payment fees, often changes pricing and promotion decisions.

Posting to Accounting

Once reconciled, each payout can be posted as gross sales, refunds, fees and net deposit, so revenue and costs are correct. With QuickBooks, that journal can be created automatically; see QuickBooks integration. The month-end process around it is in the month-end close checklist.

Where AI Helps

  • Categorising unusual transaction types and adjustments from processor descriptions.

  • Summarising the week's exceptions in plain language for the owner.

  • Drafting chargeback responses from order, shipping and communication records for review.

For selling more, not just reconciling better, see bulk product descriptions with AI. Sellers with several stores or marketplaces benefit most from automated consolidation of the exports.

FAQ

E-commerce reconciliation FAQ

Orders, payouts, fees and bank

It is matching three things: the orders in your store platform, the payments and fees in your payment processor or marketplace payout reports, and the deposits in your bank. Every order should be paid, every payout should equal its orders minus fees and refunds, and every payout should arrive in the bank.

Payouts are net of processing fees, marketplace commissions, refunds, chargebacks and reserves, and they group several days of orders into one deposit. Currency conversion, partial refunds and orders paid by gift card also cause differences. Reconciling at order level, then summing to payout, explains the gap.

Import the store's order export and the processor's transaction-level payout report into Excel Tables. Use the order number or transaction ID that both contain to join them with XLOOKUP or Power Query merge. Then sum matched transactions by payout ID and compare with the bank deposit.

Record gross sales and fees separately rather than booking the net payout as sales. That keeps revenue and costs accurate and lets you see the real cost of each payment channel. Your accountant can advise on the right accounts.

Weekly is practical for most small stores, daily for high volume. Monthly reconciliation lets problems such as missing payouts, duplicate refunds or unclaimed chargebacks age past the point where they can be recovered.