Reconciling a Regions statement's summary categories against its rows, then finding the bad day

#python #fintech #parsing #accounting

A Regions checking statement typically sorts the month into five kinds of activity, each with a total in the summary and its own detail section. To reconcile it, total each section's rows and compare them to the matching summary line, then rebuild the balance day by day against the Daily Balance Summary to find the exact day a row went missing. ZeraBooks proves Regions statements against the printed balances the same way. (I founded ZeraBooks. The script below is plain Python.)

What does a Regions statement give you to check against?

In the copies I've seen, the summary lists Beginning Balance, Deposits & Credits, Withdrawals, Fees, Automatic Transfers, Checks and Ending Balance. The detail follows in sections with the same names. Amounts print without a sign. The section heading tells you which way the money moved.

There is also a Daily Balance Summary: the balance at the end of each day that had activity. Most people skip it. It is the most useful table on the statement for debugging, because it lets you check every day instead of only the last one.

Regions layouts vary by account and by year, so treat this as the typical shape, not a spec.

How do you check each section against the summary?

The example is Bayou Tile & Stone, a fictional contractor with Regions business checking ending 6813, October 1 through October 31, 2026.

from decimal import Decimal as D
from collections import defaultdict

BEGIN = D("12640.18")
SUMMARY = {"Deposits & Credits": D("8075.40"), "Withdrawals": D("2542.59"),
           "Fees": D("15.00"), "Automatic Transfers": D("2000.00"),
           "Checks": D("1637.60")}
ENDING = D("14520.39")
SIGN = {"Deposits & Credits": 1}  # every other section is money out

SECTIONS = {
    "Deposits & Credits": [("10/02", "Deposit", "4,200.00"),
                           ("10/16", "Mobile Deposit", "3,875.40")],
    "Withdrawals": [("10/06", "ACH Debit, Daltile", "1,932.55"),
                    ("10/20", "Card Purchase, Home Depot", "610.04")],
    "Fees": [("10/31", "Monthly Service Charge", "15.00")],
    "Automatic Transfers": [("10/15", "Automatic Transfer to Savings", "2,000.00")],
    "Checks": [("10/08", "Check 2051", "1,350.00"),
               ("10/23", "Check 2052", "287.60")],
}
DAILY_BALANCE = {"10/02": "16,840.18", "10/06": "14,907.63", "10/08": "13,557.63",
                 "10/15": "11,557.63", "10/16": "15,433.03", "10/20": "14,822.99",
                 "10/23": "14,535.39", "10/31": "14,520.39"}

def money(s):
    return D(s.replace(",", ""))

def check(sections):
    for name, rows in sections.items():
        total = sum(money(r[2]) for r in rows)
        if total != SUMMARY[name]:
            print(f"{name}: rows {total}, summary {SUMMARY[name]}, gap {SUMMARY[name] - total}")
    by_day = defaultdict(D)
    for name, rs in sections.items():
        for day, _, amt in rs:
            by_day[day] += SIGN.get(name, -1) * money(amt)
    bal = BEGIN
    for day in sorted(set(by_day) | set(DAILY_BALANCE)):
        bal += by_day[day]
        if bal != money(DAILY_BALANCE.get(day, "nan")):
            print(f"first bad day {day}: rebuilt {bal}, printed {DAILY_BALANCE.get(day)}")
            return
    print("ending", bal, "matches" if bal == ENDING else "does NOT match", ENDING)

check(SECTIONS)
broken = {**SECTIONS, "Checks": SECTIONS["Checks"][:1]}
check(broken)

Output:

ending 14520.39 matches 14520.39
Checks: rows 1350.00, summary 1637.60, gap 287.60
first bad day 10/23: rebuilt 14822.99, printed 14,535.39

Does the month add up by hand?

Deposits & Credits: 4,200.00 + 3,875.40 = 8,075.40. Withdrawals: 1,932.55 + 610.04 = 2,542.59. Fees: 15.00. Automatic Transfers: 2,000.00. Checks: 1,350.00 + 287.60 = 1,637.60.

Ending: 12,640.18 + 8,075.40 - 2,542.59 - 15.00 - 2,000.00 - 1,637.60 = 14,520.39. That is the printed ending balance.

Why loop over the Daily Balance Summary days, not your own rows?

This is the bug I hit writing it. My first version only walked the days that had rows. When check 2052 went missing, October 23 had no rows left, so the loop skipped it and the mismatch surfaced on October 31 next to the 15.00 fee. That points you at the wrong line.

Walking the union of both day lists fixes it. A day the bank printed a balance for, where you have no rows, is itself a finding. Now the broken run stops on 10/23, and 14,822.99 - 14,535.39 = 287.60, the exact missing check.

The two checks answer different questions. The section check says which kind of activity is short. The daily check says which day. Together they name the row.

What else trips up a Regions parser?

The year. Detail dates are short, and the year is in the written-out period at the top ("October 1, 2026 through October 31, 2026"). Sorting "MM/DD" strings, like the script does, only works inside one period that doesn't cross New Year. For a December to January statement, attach the year from the period first.

The enclosures. Regions keeps check images on a separate Enclosures tab in Online Statements & Documents. They aren't transactions. The Checks section already lists every check paid, so don't feed the images to the parser.

Regions says up to seven years of statements are online, so a long catch-up is mostly a download job.

ZeraBooks Convert puts all five Regions sections back into one dated, signed list, with check numbers in their own column, and marks the month Proven before you download. Download steps and a worked month are on Regions bank statement to Excel. There are no bank feeds; you upload the PDF. Plans are 1,000 pages a month for $79 a month billed annually or $149 monthly, after a 7-day trial.

Check the total, then check every day. The second one is the one that saves you time.

DEV Community