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#

bash

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

---

Usage Patterns#

1. Standard Run (Chase, auto-detected)#

bash

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#

bash

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)#

bash

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)#

bash

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#

bash

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#

ArgumentRequiredDescription--slug✅Company slug (must be connected in qbo-client)--end-date✅Reconciliation as-of date (YYYY-MM-DD)--bank-csv✅Path to bank statement CSV file--bank-ending-balanceRecommendedBank statement ending balance (float)--accountOptionalAccount name to match on Balance Sheet--bank-formatOptionalauto (default), chase, bofa, wellsfargo, generic``--col-dateOptionalOverride date column header--col-descOverrideOverride description column header--col-amountOptionalOverride single amount column header--col-debitOptionalOverride debit/withdrawal column header--col-creditOptionalOverride credit/deposit column header--date-windowOptionalDays for exact match window (default: 3, fuzzy: 2×)--outOptionalOutput directory (default: ~/Desktop)--sandboxOptionalUse QBO sandbox environment

---

Supported Bank CSV Formats#

FormatDate ColumnAmount ColumnNoteschaseTransaction DateAmountNegative = withdrawalbofaDateAmountNegative = withdrawalwellsfargoDateWithdrawals + DepositsTwo separate columnsgenericTries common namesTries common namesUse --col-* if fails

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#

text

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:

CategoryKeywordsSuggested EntryBank Fee"service charge", "monthly fee", "wire fee", "nsf fee"DR Bank Service Charges / CR CheckingInterest Earned"interest", "dividend"DR Checking / CR Interest IncomeDirect Deposit"payroll", "ach credit", "zelle"DR Checking / CR AR/RevenuePayment Processor"stripe", "square", "paypal"DR Checking / CR Undeposited FundsNSF Returned"returned item", "returned check"DR Returned Check Expense / CR CheckingUnclassified(no keyword match)TBD — review manually

---

Output: Excel Workbook#

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

TabContentsReconciliation SummaryBook/bank balance sections, stats, reconciled status badgeMatchedAll matched pairs with match type (exact/fuzzy), amounts, date diff**Unmatched (Book)**Outstanding checks + deposits in transit (in QBO, not cleared)Unmatched (Bank)Bank items not in QBO (fees, interest, unrecorded deposits)Adjusting EntriesSuggested DR/CR journal entries for each unmatched bank itemCDC LogChanges since last reconciliation run (book balance, diff, etc.)

---

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#

text

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