Finance teams at merchants who accept crypto usually ask the same question at month end: does the money that reached the bank match the orders we shipped? A crypto gateway that converts to USD and pays out to a bank account produces a settlement report, your store produces an order export, and the bank produces a statement. This post shows a small pandas script that joins the three and flags the differences, so a human only reviews the exceptions.

The three inputs

  1. Order export from your store or billing system: order ID, customer currency amount, currency, status, created time.
  2. Gateway settlement report: invoice ID, your order ID (passed as metadata when creating the invoice), crypto asset, network, crypto amount received, USD value at confirmation, fee, net amount, payout batch ID.
  3. Bank statement: date, amount, reference. Each gateway payout usually appears as one line per batch.

The key design decision happens before any code: always pass your own order ID to the gateway when creating an invoice. Without it, reconciliation becomes fuzzy matching on amounts and timestamps, which breaks as soon as two customers buy the same product within a minute.

Loading and normalising 

import pandas as pd orders = pd.read_csv("orders.csv", parse_dates=["created_at"]) settle = pd.read_csv("gateway_settlement.csv", parse_dates=["confirmed_at"]) bank = pd.read_csv("bank.csv", parse_dates=["date"]) orders["order_id"] = orders["order_id"].astype(str).str.strip() settle["order_id"] = settle["order_id"].astype(str).str.strip() for col in ["usd_value", "fee", "net"]: settle[col] = pd.to_numeric(settle[col], errors="coerce").round(2)

Normalise identifiers as strings and strip whitespace. Spreadsheet exports love turning order numbers into floats.

Matching orders to invoices 

m = orders.merge(settle, on="order_id", how="outer", indicator=True) paid_not_found = m[m["_merge"] == "right_only"] # gateway paid, no order order_unpaid = m[(m["_merge"] == "left_only") & (m["status"] == "paid")] both = m[m["_merge"] == "both"].copy() both["diff"] = (both["usd_value"] - both["order_total_usd"]).round(2) mismatch = both[both["diff"].abs() > 0.50]

Three exception lists come out of this:

  • Paid but no order usually means an order was deleted, or a test invoice was paid on the live account.
  • Order marked paid but no settlement is the dangerous one. It often points to a webhook handler that trusted a "pending" event, or to manual status changes by support.
  • Amount mismatch covers underpayments, overpayments and orders priced in a currency other than USD. Choose a tolerance that reflects your rounding rules; fifty cents works for most digital goods.

Matching payouts to the bank 

batches = settle.groupby("payout_batch")["net"].sum().round(2).reset_index() bank["batch"] = bank["reference"].str.extract(r"(PB-\w+)") recon = batches.merge(bank, left_on="payout_batch", right_on="batch", how="left") recon["bank_diff"] = (recon["amount"] - recon["net"]).round(2)

If your bank statement does not carry the batch reference, match on amount within a date window instead, and expect to review a few lines by hand. Small differences can come from bank charges on incoming transfers in some of the 38 countries where gateways offer local deposits; record them as bank fees rather than adjusting the order.

Fees and reporting

With a flat processing fee, the check is simple: fee should equal one percent of the USD value, within rounding. Flag anything else.

settle["fee_check"] = (settle["usd_value"] * 0.01).round(2) - settle["fee"] odd_fees = settle[settle["fee_check"].abs() > 0.02]

Export the exception lists to one workbook with a sheet per list and send it to finance. In practice, after the first month the lists are short, and the monthly close takes minutes instead of a day.

Practical tips

  • Run the script weekly, not monthly. Exceptions are easier to explain while they are fresh.
  • Keep the raw exports unchanged in a dated folder; write results to a separate folder.
  • Store crypto amounts as strings or decimals, never floats, if you also reconcile on-chain amounts.
  • Log the gateway's transaction hash next to each order so support can answer "did my payment arrive?" without asking finance.

We describe the payout flow, supported assets and integration options (API, plugins and SDK) in our crypto payment gateway guide. The service is offered by a Hong Kong company to overseas merchants only.

Published by PandaWM, an overseas marketing team based in Hong Kong.