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
CODEBLOCK0
Usage Patterns
1. Standard Run (Chase, auto-detected)
CODEBLOCK1
2. Specific Account Name
CODEBLOCK2
3. Wells Fargo (separate debit/credit columns)
CODEBLOCK3
4. Custom Column Mapping (unknown CSV format)
CODEBLOCK4
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
| Argument | Required | Description |
|---|
| INLINECODE1 | ✅ | Company slug (must be connected in qbo-client) |
| INLINECODE2 |
✅ | Reconciliation as-of date (YYYY-MM-DD) |
|
--bank-csv | ✅ | Path to bank statement CSV file |
|
--bank-ending-balance | Recommended | Bank statement ending balance (float) |
|
--account | Optional | Account name to match on Balance Sheet |
|
--bank-format | Optional |
auto (default),
chase,
bofa,
wellsfargo,
generic |
|
--col-date | Optional | Override date column header |
|
--col-desc | Override | Override description column header |
|
--col-amount | Optional | Override single amount column header |
|
--col-debit | Optional | Override debit/withdrawal column header |
|
--col-credit | Optional | Override credit/deposit column header |
|
--date-window | Optional | Days for exact match window (default: 3, fuzzy: 2×) |
|
--out | Optional | Output directory (default: ~/Desktop) |
|
--sandbox | Optional | Use QBO sandbox environment |
Supported Bank CSV Formats
| Format | Date Column | Amount Column | Notes |
|---|
| INLINECODE20 | Transaction Date | Amount | Negative = withdrawal |
| INLINECODE21 |
Date | Amount | Negative = withdrawal |
|
wellsfargo | Date | Withdrawals + Deposits | Two separate columns |
|
generic | Tries common names | Tries common names | Use
--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
CODEBLOCK6
Adjusting Entry Auto-Suggestions
The pipeline auto-classifies unmatched bank transactions into:
| Category | Keywords | Suggested Entry |
|---|
| Bank Fee | "service charge", "monthly fee", "wire fee", "nsf fee" | DR Bank Service Charges / CR Checking |
| Interest Earned |
"interest", "dividend" | DR Checking / CR Interest Income |
| Direct Deposit | "payroll", "ach credit", "zelle" | DR Checking / CR AR/Revenue |
| Payment Processor | "stripe", "square", "paypal" | DR Checking / CR Undeposited Funds |
| NSF Returned | "returned item", "returned check" | DR Returned Check Expense / CR Checking |
| Unclassified | (no keyword match) | TBD — review manually |
Output: Excel Workbook
File: BankRecon_{slug}_{YYYYMMDD}.xlsx (saved to --out or ~/Desktop)
| Tab | Contents |
|---|
| Reconciliation Summary | Book/bank balance sections, stats, reconciled status badge |
| Matched |
All 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 Entries | Suggested DR/CR journal entries for each unmatched bank item |
|
CDC Log | Changes since last reconciliation run (book balance, diff, etc.) |
CDC (Change Data Capture)
Cache stored at: INLINECODE31
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
CODEBLOCK7
