Shopping

Match your online orders list to bank debits, line by line

A line-by-line way to reconcile online orders with your bank statement, with a made-up ten-order month showing splits, cancellations and refunds.

Editorial illustration for Match your online orders list to bank debits, line by line

To reconcile online orders with your bank statement, copy your order list into a sheet, copy the matching debits from the statement beside it, and pair them by date and amount. What is left over is the interesting part: split shipments, cancelled orders, refunds that have not arrived, and the odd cash-on-delivery order that never touched the bank at all.

What do you need before you start?

  • Your order history for the month, with date, item and order total for each order.
  • The bank or card statement for the same month, plus the first week of the next one, because refunds and late debits spill over.
  • A sheet with two blocks: orders on the left, debits and credits on the right.

Do this once a month, not once a year. The order page is still easy to read, and a missing refund is still within reach of the seller's support.

A made-up ten-order month

Say your March order list has ten orders totalling ₹11,444. Here is how the statement lines up against them.

No.Order dateItemOrder totalBank debitStatus
12 MarPhone case₹399₹399Matched
23 MarKitchen mixer₹2,450₹2,450Matched
35 MarTwo books₹680₹340 + ₹340Split shipment, two debits
47 MarT-shirt₹799₹799Cancelled, refund ₹799 on 12 Mar
59 MarHeadphones₹1,999₹1,999Matched
611 MarCooking oil₹899₹899Matched
714 MarShoes₹2,299₹2,299Returned, refund ₹2,299 on 25 Mar
818 MarBedsheet₹1,150₹1,150Matched
921 MarUSB cable₹249NoneCash on delivery
1026 MarWater bottle₹520₹520Matched, debit one day later

Five rows are plain matches and need only a tick. Row 3 needs a note because one order produced two debits. Rows 4 and 7 need a note because money went out and came back, so those orders are a wash once the refund has landed. Row 9 is cash, and row 10 simply debited a day late.

How do you pair the lines?

  1. Sort both blocks by date and work down the order list, not the statement.
  2. For each order, find a debit within a day or two for the same amount. Mark it 'matched'.
  3. If an order has two smaller debits that add up to its total, mark it 'split' and tick both lines.
  4. If there is no debit at all, check the payment mode on the order page. Cash on delivery explains it.
  5. Match every credit to the order it reverses and note the refund date beside it.

Here the debits add up to ₹11,195 (₹11,444 less the ₹249 cash order), and the two refunds total ₹3,098 (₹799 plus ₹2,299). So the bank outflow for the month's shopping is ₹8,097. A check formula for the bottom of the sheet is =SUM(D2:D11)-SUM(E2:E11), provided each debit is typed as a plain number (680 for the split row, 0 for the cash order). It gives ₹249 here, which should equal the cash-on-delivery orders and nothing else.

What if a debit has no order, or an order has no debit?

A debit with no order may be another household member's purchase, a subscription renewal, or a charge you did not make. Look at the merchant name, which often differs from the shop's brand name, and ask the family before you worry. If you still cannot place it, contact your bank through its official channels. An order with no debit usually means cash on delivery, a wallet payment, or a failed payment that retried; the order page should say which.

When does a spreadsheet stop being enough?

The routine works for a dozen orders a month. When you are copying fifty lines by hand, importing the statement into SpendixAI and reviewing the rows before saving means the debits arrive already dated and categorised, leaving you to do only the pairing. Add the cash-on-delivery orders as manual expenses so the month is complete.

SPENDIXAI JOURNAL

Useful ideas start with a clearer picture of your own spending.

Explore your reports