银行流水自动对账

Bank Reconciliation Skill

Pipeline: scripts/pipelines/bank-reconciliation.py
Day: 2 of 14-Day Deterministic Pipeline Build

---

When to Use This Skill

Use when a client needs their bank account reconciled against QBO:

• Monthly close reconciliation
• Investigating GL/bank balance discrepancies
• Catching unrecorded bank fees, interest, or deposits
• Producing auditor-ready reconciliation workpapers
• Tracking outstanding checks or deposits in transit

NOT for:

• Payroll reconciliation (use payroll-recon pipeline)
• AR/AP aging (use ar-collections pipeline)
• Investment/brokerage accounts
• Intercompany eliminations

---

Requirements

pip install openpyxl
# Node.js QBO client must be connected:
node integrations/qbo-client/bin/qbo connect 

---

Usage Patterns

1. Standard Run (Chase, auto-detected)

python3 scripts/pipelines/bank-reconciliation.py \
    --slug sb-paulson \
    --end-date 2026-02-28 \
    --bank-csv ~/Downloads/chase_feb2026.csv \
    --bank-ending-balance 42531.87

2. Specific Account Name

python3 scripts/pipelines/bank-reconciliation.py \
    --slug glowlabs \
    --end-date 2026-02-28 \
    --bank-csv ~/Downloads/boa_feb2026.csv \
    --account "Business Checking" \
    --bank-ending-balance 18250.00

3. Wells Fargo (separate debit/credit columns)

python3 scripts/pipelines/bank-reconciliation.py \
    --slug sb-paulson \
    --end-date 2026-02-28 \
    --bank-csv ~/Downloads/wf_feb.csv \
    --bank-format wellsfargo \
    --bank-ending-balance 55000.00

4. Custom Column Mapping (unknown CSV format)

python3 scripts/pipelines/bank-reconciliation.py \
    --slug sb-paulson \
    --end-date 2026-02-28 \
    --bank-csv ~/Downloads/stmt.csv \
    --col-date "Trans Date" \
    --col-desc "Narrative" \
    --col-amount "Net Amount" \
    --bank-ending-balance 28000.00

5. Wider Date Window + Custom Output

python3 scripts/pipelines/bank-reconciliation.py \
    --slug sb-paulson \
    --end-date 2026-02-28 \
    --bank-csv ~/Downloads/chase_feb.csv \
    --date-window 5 \
    --bank-ending-balance 42531.87 \
    --out ~/Desktop/recon

---

Arguments Reference

---

Supported Bank CSV Formats

Auto-detection reads column headers and picks the best format. Use --bank-format to override.

---

Matching Logic

Pass 1 — Exact: Amount match within ±$0.01, date within ±date-window days (default 3).

Pass 2 — Fuzzy Date: Amount match within ±$0.01, date within ±2×date-window days (default 6).

Pass 3 — Fuzzy Vendor: Amount within ±$1.00, vendor key substring match (strips check numbers, dates, ref numbers), any date.

Unmatched after all 3 passes → flagged in Unmatched tabs.

---

Reconciliation Equation

Book Balance (QBO BS)
+ Deposits in Transit     (book has it, bank hasn't cleared it)
- Outstanding Checks      (book has it, bank hasn't cleared it)
= Adjusted Book Balance

Bank Statement Ending Balance
+ Bank Credits Not in Book  (bank has it, not yet in QBO)
+ Bank Charges Not in Book  (negative; bank has it, not yet in QBO)
= Adjusted Bank Balance

Adjusted Book Balance = Adjusted Bank Balance → RECONCILED ✅

---

Adjusting Entry Auto-Suggestions

The pipeline auto-classifies unmatched bank transactions into:

---

Output: Excel Workbook

File: BankRecon_{slug}_{YYYYMMDD}.xlsx (saved to --out or ~/Desktop)

---

CDC (Change Data Capture)

Cache stored at: .cache/bank-reconciliation/{slug}.json

Tracks changes in:

• Book Balance
• Adjusted Book/Bank Balance
• Deposits in Transit
• Outstanding Checks
• Reconciling Difference

On first run: "First run — snapshot saved." On subsequent runs: only changed fields shown. Saves 90%+ API calls on recurring monthly reconciliations.

---

Design Notes

• All financial math uses Python Decimal — no float drift
• Amount sign convention: positive = deposit/inflow, negative = payment/outflow (both book and bank)
• GL pull uses QBO GeneralLedger report filtered to the reconciliation period
• If GL pull fails (e.g. permissions), pipeline falls back to Balance Sheet only (matching disabled)
• Bank CSV preamble rows (Chase header lines before data) are auto-skipped

---

Typical Workflow

1. Client sends bank statement PDF → export as CSV from bank website
2. Run pipeline with --slug, --end-date, --bank-csv, --bank-ending-balance
3. Review Reconciliation Summary tab — check RECONCILED badge
4. If difference exists: check Unmatched (Book) for outstanding items, Unmatched (Bank) for unrecorded items
5. Post adjusting entries from Adjusting Entries tab
6. Re-run pipeline — difference should clear to $0.00
7. Save Excel to client Google Drive folder