用 50 行 Python 核對多個司法管轄區的銷售税應付款

A sales tax tie-out needs three checks per jurisdiction per month: each opening payable equals the prior closing, opening plus collected minus remitted equals the ledger's closing payable to the cent, and tax collected is within a small tolerance of taxable sales times that jurisdiction's rate. The first two are exact.

The third is a reasonableness test, and its gap divided by the rate points to the sales to review.

I'm on the ZeraBooks team. We published a single-rate version of this as an open example this week. The obvious next question was what happens with two states, so this is the multi-jurisdiction version, with Decimal throughout and a hint for the most common failure.

The input

One row per jurisdiction per month. Remittances come from the bank statement (what was actually paid). Closing payable comes from the ledger. Keeping those two sources separate is what lets the roll-forward catch anything.

Jurisdiction,Month,Taxable sales,Rate,Opening payable,Collected,Remitted per bank,Closing payable per ledger
State A,2026-04,41200.00,0.0725,2610.40,2987.00,2610.40,2987.00
State A,2026-05,38650.00,0.0725,2987.00,2802.13,2987.00,2802.13
State A,2026-06,44120.00,0.0725,2802.13,3194.20,2802.13,3194.20
State B,2026-05,11500.00,0.0600,655.20,690.00,655.20,690.00
State B,2026-06,12400.00,0.0600,690.00,744.00,690.00,744.00
State B,2026-07,13510.00,0.0600,744.00,810.60,744.00,1554.60

All figures are fictional. State A is the original single-rate example. State B is new, and its July has a deliberate error.

The checker

import csv, sys
from collections import defaultdict
from decimal import Decimal, ROUND_HALF_UP

CENT = Decimal("0.01")

def d(s: str) -> Decimal:
    return Decimal(s.replace(",", ""))

def check(path: str, tolerance: Decimal = Decimal("1.00")) -> list[str]:
    rows = defaultdict(list)
    with open(path, newline="") as f:
        for r in csv.DictReader(f):
            rows[r["Jurisdiction"]].append(r)

    problems = []
    for juris, months in rows.items():
        months.sort(key=lambda r: r["Month"])
        prior_close = None
        for r in months:
            m = f'{juris} {r["Month"]}'
            opening, collected = d(r["Opening payable"]), d(r["Collected"])
            remitted, closing = d(r["Remitted per bank"]), d(r["Closing payable per ledger"])

            # 1. continuity: this opening is last month's closing
            if prior_close is not None and opening != prior_close:
                problems.append(f"{m}: opens at {opening}, prior month closed at {prior_close}")

            # 2. roll-forward, to the cent
            expected = opening + collected - remitted
            if expected != closing:
                diff = closing - expected
                hint = " (equals the remittance: booked to an expense?)" if diff == remitted else ""
                problems.append(f"{m}: closing {closing}, roll-forward gives {expected}, off by {diff}{hint}")

            # 3. reasonableness: collected vs sales x rate
            rate = d(r["Rate"])
            should = (d(r["Taxable sales"]) * rate).quantize(CENT, ROUND_HALF_UP)
            gap = collected - should
            if abs(gap) > tolerance:
                implied = (gap / rate).quantize(CENT, ROUND_HALF_UP)
                problems.append(f"{m}: collected {collected}, expected {should}; gap {gap} is about {implied} of sales")
            prior_close = closing
    return problems

if __name__ == "__main__":
    tol = Decimal(sys.argv[2]) if len(sys.argv) > 2 else Decimal("1.00")
    for p in check(sys.argv[1], tol) or ["all months tie"]:
        print(p)

Standard library only. Run it as python3 check.py sales-tax.csv, with an optional tolerance as the second argument.

The output

State A 2026-06: collected 3194.20, expected 3198.70; gap -4.50 is about -62.07 of sales
State B 2026-07: closing 1554.60, roll-forward gives 810.60, off by 744.00 (equals the remittance: booked to an expense?)

Two different kinds of problem, and the checker distinguishes them.

State A, June: a reasonableness gap

The roll-forward ties: 2,802.13 plus 3,194.20 minus 2,802.13 is 3,194.20, matching the ledger. But taxable sales of 44,120.00 at 7.25% should have produced 3,198.70. The ledger collected 4.50 less. Dividing 4.50 by 0.0725 says roughly 62.07 of sales were taxed wrong or marked non-taxable.

The fix belongs in the invoices, not in the payable. Typical causes: an item flagged non-taxable by mistake, tax rounded per line instead of per invoice, a discount applied after tax.

State B, July: a roll-forward break

June closed at 744.00. July collected 810.60, which is exactly 13,510.00 times 6%, and the bank shows 744.00 paid to State B. So the payable should close at 744.00 plus 810.60 minus 744.00, which is 810.60. The ledger says 1,554.60.

The difference is 744.00, the same as the remittance. That's the signature of a payment posted to an expense account ("Taxes and licenses" is the usual one) instead of against the payable. The liability grows by every remittance, and profit is understated by the same amount. The hint in the output is just that equality check, but it identifies this case most of the time.

Choosing the tolerance

I use 1.00 by default. Per-line rounding on a busy month can legitimately produce a few cents of difference, and a tolerance of zero would flag every month. With a 5.00 tolerance the State A gap disappears, which may be fine for your purposes or may hide 62 dollars of mis-taxed sales. Pick it on purpose and write it down.

A better long-term approach is a tolerance proportional to invoice count, say half a cent per invoice, since that's the maximum per-invoice rounding error. That needs invoice counts in the input, which most sales tax reports include.

What the script deliberately doesn't do

Mixed rates within one jurisdiction. A state with county and city add-ons needs one row per rate, or the rate column needs to be an effective rate you computed elsewhere. The script assumes the rate in the file is correct.
Filing periods that aren't monthly. Quarterly filers can still check monthly, but the remittance lands once a quarter, so most months will show zero remitted and a growing payable. That's correct.
Marketplace sales. Where a marketplace collects and remits the tax, those sales shouldn't be in your taxable sales column at all.
Canada. GST/HST works as collected minus input tax credits rather than collected minus remitted alone. The same structure works with an extra column for credits.

Where this sits in a close

Sales tax payable is one account in a balance sheet reconciliation, where every asset and liability is matched to an outside document: bank and card accounts to statements, payroll liabilities to the payroll register, loans to lender statements, sales tax to the return. Cash goes first, because a remittance that cleared on the wrong date shows up there before it confuses this check.

ZeraBooks is an AI-native general ledger that does the bank and card part: it reads statements, categorizes, and proves every month against each statement's printed opening and closing balance. The AI never does the arithmetic. Entries are approved by a person and reversible. There's no live bank feed; statements are uploaded. It's $79 a month billed annually or $149 monthly, with a 7-day trial.

The full method for every account type, with a worked month, is our guide to balance sheet reconciliation.

Disclosure: I work at ZeraBooks.