process-my-statements

# Process My Statements Drop a batch of PDF or CSV bank and credit-card statements and I produce a reviewed Google Sheets workbook with a formula-driven P&L. Full pipeline: extract every transaction in parallel, canonicalize parties, categorize against your locked chart of accounts, tag inter-account transfers, and write a workbook you can hand to your accountant. Suspense bucket and reconciliation warnings sit at the top — I never plug, never invent an account code, never post. ## Output Target: Google Sheets via Composio Use the Composio CLI available in PATH. All Google Sheets writes go through it. **Before any run**, verify `googlesheets` toolkit connected: ```bash composio execute GOOGLESHEETS_SEARCH_SPREADSHEETS -d '{"query": "", "max_results": 1}' ``` If returns `"No active connection found for toolkit \"googlesheets\""`, STOP and ask user to connect: ```bash composio link googlesheets --no-wait ``` Take `redirect_url` from response, present to user as markdown link with `#houston_toolkit=googlesheets` appended (so Houston render connect card). Wait for approval before continuing. ## Connections I need I run external work through Composio. Before this skill runs I check that the categories below are linked. Missing → I name the category, ask you to connect it from the Integrations tab, stop. - **Google Sheets** (spreadsheets) — required. The whole pipeline ends in a Google Sheets workbook with a formula-driven P&L; without it there's no output. See the "Output Target: Google Sheets via Composio" block above for the verification command and connect link. - **Stripe** (billing) — optional. Pulls payouts and processor fees so they categorize cleanly when they show up in your bank feed. If Google Sheets isn't connected I stop and ask you to connect it before doing any work. ## Information I need I read your bookkeeping context first. For every required field that's missing I ask ONE plain-language question (best modality: connected app > file drop > URL > paste) and wait. - **A finished bookkeeping context** — Required. Why: I need your accounting method, suspense code, and registered accounts before I categorize. If missing I ask: "Have we set up the books yet? If not, run the setup once so I know your fiscal year, accounting method, and registered accounts." - **A chart of accounts** — Required. Why: I lock it during the run; every category I assign has to come from your chart of accounts. If missing I ask: "Do we have a chart of accounts yet? If not, let's draft one first." - **Your bank accounts and credit cards** — Required. Why: I group transactions by last 4 and need each account's account code. If missing I ask: "What bank accounts and credit cards does the business use? I'll register any new ones automatically when statements come in, but it's faster if you tell me up front." - **The statements to process** — Required. Why: the pipeline starts from PDFs or CSVs you drop. If missing I ask: "Can you drop the bank and credit card statements as PDFs, or attach them in chat?" - **Vendor rules from a prior period** — Optional. Why: lets me match new charges to known vendors and keep questions to a minimum. If you don't have them I keep going and learn from this run. ## Storage Layout Agent single-company. Chart of accounts + memory live at agent root (flat). Each run get own folder under `runs/{period}/`. ``` context/ └── bookkeeping-context.md # live brief (entity, fiscal year, accounting method) config/ ├── context-ledger.json # metadata: company, accounting-method, banks, etc. ├── chart-of-accounts.json # authoritative chart of accounts (locked during a run) ├── prior-categorizations.json # {canonical_party: gl_code} - vendor history └── party-rules.json # user-confirmed exact rules statements/ # source PDFs + side files (vendor list, etc.) └── _inbox/ # drop zone for PDFs before this pipeline runs runs/ └── {period}/ # e.g., 2024, 2024-Q1, 2024-01 ├── run.json # full run artifact (the recovery source) ├── _extractions/{pdf_stem}.json # transient - Haiku extractor outputs (one per PDF) ├── _work/{account_last4}.json # transient - packets handed to each Categorizer ├── _categorizations/{account_last4}.json # transient - Sonnet categorizer outputs └── _sheet_state/{period}.json # transient - Sonnet Sheets Writer output ``` If directory missing, create with `mkdir -p` on first use. **Bank accounts** live on context ledger, not separate `client.json`: ```jsonc // config/context-ledger.json (excerpt) { "domains": { "banks": { "accounts": [ {"last4": "9041", "type": "credit-card", "bank": "Chase", "glCode": "20000", "glName": "Chase CC #9041"} ] } }, "universal": { "suspenseCode": { "code": "99999", "name": "Suspense" } } } ``` ## Inputs User provides one or more: 1. Explicit PDF paths in message (most common - attachments dropped into chat). 2. PDFs in `statements/_inbox/` - list with `ls statements/_inbox/*.pdf`. 3. Period identifier (year / quarter / month) - used for `runs/{period}/` folder name. 4. (Optional) custom chart of accounts file (xlsx / csv / inline text), vendor list, or prior Transaction Detail. ## Procedure <!-- houston-workflow:v1 --> ### Step 1 - Bootstrap context + lock chart of accounts 1. **Load existing state:** - `context/bookkeeping-context.md` - brief. If missing, stop and ask user to run `set-up-my-books` first (or ask to do inline). - `config/context-ledger.json` - accounts, suspense code. - `config/chart-of-accounts.json` - authoritative chart of accounts. If exists, **LOCK for this run.** - `config/prior-categorizations.json` - vendor → account code memory. - `config/party-rules.json` - exact-match rules. 2. **First-run bootstrap (only if `config/chart-of-accounts.json` absent):** - If user provided chart of accounts file (xlsx/csv), parse (openpyxl for xlsx) into `config/chart-of-accounts.json` as `[{code, name, type, statementSection}]`. - If user described chart of accounts inline, structure that way. - Else fall back to bundled default in `CHART_OF_ACCOUNTS.md` - but copy to `config/chart-of-accounts.json` so next runs share codes. - Copy source PDFs + side files into `statements/` (keep filenames; subfolders per account OK, e.g., `statements/9041/2024-01.pdf`). - If prior Transaction Detail provided, extract `{vendor_name: [gl_codes]}` and seed `config/prior-categorizations.json` with majority code per vendor (only if consistent in ≥ 80% past records). 3. **Lock chart of accounts for rest of run.** Treat `config/chart-of-accounts.json` immutable until Step 7. If transaction can't categorize, send to Suspense - NEVER invent new account code. 4. **Determine period.** Default: min(period_start) to max(period_end) across all statements. Period slug: `YYYY` for full-year, `YYYY-QN` for quarter, `YYYY-MM` for single month. Create `runs/{period}/_extractions/`, `runs/{period}/_work/`, `runs/{period}/_categorizations/`, `runs/{period}/_sheet_state/`. ### Step 2 - Extract transactions (parallel Haiku subagents) **Don't read PDFs in orchestrator - dispatch Haiku subagents in parallel.** Much faster, keeps orchestrator context clean for categorization and Sheets assembly. **Dispatch pattern:** For each PDF (or small batch ≤ 3 single-month PDFs from same account), launch one `Agent` call in parallel with: - `subagent_type: "general-purpose"` - `model: "haiku"` - `description: "Extract {bank} {account_last4} {YYYY-MM}"` (or similar, 3–5 words) **Send all dispatches in one message so they run concurrently.** Twelve monthly statements → twelve parallel agents, finish in roughly time of one. Each subagent writes result to disk at `runs/{period}/_extractions/{source_pdf_stem}.json` and returns short confirmation ("wrote N transactions, reconciles: yes/no"). Orchestrator reads JSON files back after all agents complete. **Subagent prompt template** (paste, fill `{...}` per dispatch): ``` You are extracting transactions from a single bank or credit card statement PDF. PDF path: {absolute_pdf_path} Expected account_last4 (if known): {last4 or "unknown"} Expected account type: {"credit_card" | "checking" | "savings" | "unknown"} TASK Read the PDF with the Read tool (it is multimodal - it sees the pages). If the PDF has more than 10 pages, use the `pages` parameter to read it in slices. Extract EVERY transaction and the statement's opening/closing balances. Write the result as JSON to: {output_path} OUTPUT JSON SCHEMA { "source_pdf": "{pdf filename, not path}", "bank_name": "Chase" | "Wells Fargo" | etc., "account_last4": "9041", "account_type": "credit_card" | "checking" | "savings", "statements": [ // usually one, but multi-period PDFs can have many { "statement_date": "2023-01-12", "period_start": "2022-12-13", "period_end": "2023-01-12", "opening_balance": 1090.96, "closing_balance": 1085.63, "transactions": [ {"date":"2022-12-15","description":"...","amount":-45.00,"source_page":3} ] } ] } SIGN CONVENTION - NON-NEGOTIABLE Normalize to "money out of the business = negative, money in = positive": - Checking / savings: deposits +, withdrawals / debits / fees -. - Credit card: purchases / interest / fees -, payments / credits / returns +. (This is the OPPOSITE of how many CC statements print; flip if needed.) EXTRACTION DISCIPLINE - The transaction AMOUNT is the change, not the running balance column. - Skip "Beginning Balance" and "Ending Balance" marker rows. - Include bank fees and interest as transactions. - Continued-on-next-page rows: include once. - Multi-period PDFs: emit one entry per statement under `statements[]`. - Date format: ISO YYYY-MM-DD. If a txn date is ambiguous (12/15 with no year) use the year consistent with the statement period. RECONCILIATION SELF-CHECK Before writing the file, verify for each statement: computed_close = opening_balance + sum(transaction.amount) (for checking/savings) computed_close = opening_balance - sum(transaction.amount) (for credit_card, using the sign convention above) If |computed_close - closing_balance| > 0.02, include a "reconciliation_note" field on that statement describing the diff - do NOT silently force a match. Write the JSON file. Return a one-line summary: "wrote {N} txns across {M} statement(s), recon: {ok|diff=$X.XX}" ``` **After dispatching, orchestrator:** 1. Wait for all subagents finish (run in parallel automatically). 2. Read each `runs/{period}/_extractions/*.json`. 3. Merge into single in-memory list per account_last4. 4. Dedupe on `(account_last4, date, amount, description)` if two statements overlap. 5. Apply same reconciliation self-check in orchestrator (trust but verify). **When NOT dispatch subagents:** - Only one small PDF, need data immediately - Read inline. - PDF scanned image, very low quality - do yourself so can inspect OCR artifacts visually. - Subagent returned reconciliation diff > $0.02 - re-read specific statement yourself in orchestrator and correct extraction. See `EXTRACTION.md` for named layout patterns (bare tables, running-balance columns, Wells Fargo Spanish layout, etc.) - include relevant pattern hint in subagent prompt when bank known in advance. ### Step 3 - Reconciliation check (warning only, never blocking) For each statement: ``` computed_closing = opening_balance + sum(transaction.amount for transaction in statement) mismatch = abs(computed_closing - closing_balance) > 0.02 # 2 cent tolerance ``` If mismatched, add warning to reconciliation sheet and continue. Don't stop pipeline. ### Step 3b - Merge extractions and write Categorizer work packets After all Haiku Extractors finish, orchestrator reads and merges output before dispatching Categorizers: 1. **Read all `runs/{period}/_extractions/*.json`.** 2. **Group transactions by `account_last4`.** For each unique last4 across all extraction files, collect all transactions from all statements for that account. 3. **Register new accounts.** Any `account_last4` not yet in `context-ledger.json → domains.banks.accounts[]` - add with bank name and account type from extraction file, leave `gl_code` blank for now. 4. **Deduplicate.** Within each account, remove duplicate transactions on `(date, amount, description)` - appear when statements overlap (e.g., two months share border date). 5. **Write one work packet per account** to `runs/{period}/_work/{account_last4}.json`: ```json { "account_last4": "9041", "account_type": "credit_card", "bank": "Chase", "gl_code": "20000", "suspense_code": "99999", "transactions": [ { "date": "2023-01-15", "description": "AMAZON.COM*AB12C NJ", "amount": -45.00, "statement_date": "2023-01-20" } ], "chart_of_accounts": [ { "code": "6090", "name": "Office Expenses", "type": "expense" } ], "prior_categorizations": { "Amazon": "6090" }, "party_rules": { "PG&E": "6150" } } ``` Fields: - `account_last4`, `account_type`, `bank`, `gl_code` - from `context-ledger.json → domains.banks.accounts[]` (gl_code may be blank for new accounts) - `suspense_code` - from `context-ledger.json → universal.suspenseCode.code` - `transactions` - merged, deduped list for this account only; include `statement_date` if present in extraction JSON - `chart_of_accounts` - full contents of `config/chart-of-accounts.json` - `prior_categorizations` - full contents of `config/prior-categorizations.json` (empty `{}` if absent) - `party_rules` - full contents of `config/party-rules.json` (empty `{}` if absent) 6. **Create output subdirectories if absent:** ```bash mkdir -p runs/{period}/_work mkdir -p runs/{period}/_categorizations mkdir -p runs/{period}/_sheet_state ``` ### Step 4+5 - Dispatch Categorizer subagents (Sonnet, parallel) **Don't canonicalize or categorize inline in orchestrator.** Dispatch one Sonnet Categorizer per `account_last4` in single message so run concurrently. For accounts with >500 transactions, split into ≤500-row chunks and dispatch multiple agents for same account (outputs concat in order). **Dispatch pattern:** For each account (one Agent call per account in single message): - `subagent_type: "general-purpose"` - `model: "sonnet"` - `description: "Categorize {bank} {account_last4}"` (3–5 words) **Each Categorizer returns one-line status:** `"account {last4}: {N} txns, {R} ready / {V} review / {U} suspense (${S})"` --- **Categorizer subagent prompt template** (fill `{...}` per account): ``` You are categorizing bank/credit card transactions for bookkeeping. Work packet path: {absolute_work_packet_path} Output path: {absolute_output_path} TASK 1. Read the work packet JSON at the work packet path above.