Personal Finance Reconciler

SKILL.md

---
name: finance-reconciler
description: Privacy-first personal finance tracker with local SQLite storage
requires: python3
install: pip3 install pandas ofxparse tabulate python-dateutil
---

# Finance Reconciler

A privacy-first personal finance skill that imports bank transactions, auto-categorizes them, tracks budgets, answers natural language spending queries, and generates reports. All data stays local in SQLite — nothing is sent to external servers.

## First-Time Setup

On first use, run these two commands before anything else:

```bash
pip3 install pandas ofxparse tabulate python-dateutil
```

```bash
python3 scripts/db.py
```

If either command fails, stop and show the user the error. Do not proceed until setup succeeds.

## First-Time User Onboarding

If the user has no transactions in the database yet (or says something general like "help me track my finances", "get started", or "what can you do"), walk them through this:

1. Explain that this tool tracks spending from bank statement files — all data stays on their machine.
2. Tell them to download a statement from their bank:
   - **Chase**: chase.com → Statements & Documents → Download account activity → CSV
   - **Bank of America**: bankofamerica.com → Statements & Documents → Download Transactions → CSV
   - **Wells Fargo**: wellsfargo.com → Account Activity → Download → Comma Delimited
   - **Any bank**: Look for "Export to Quicken" or "Download OFX/QFX" for OFX format, or any CSV download option
3. Ask them to share the file path or drop the file into the conversation.
4. Suggest they try these after importing:
   - "How much did I spend on groceries last month?"
   - "Set a $400 monthly budget for dining"
   - "Show me my monthly report"

## Handling File Input

When the user wants to import a bank statement:

- **If they provide a file path** (e.g., `~/Downloads/chase_jan.csv`): use that path directly with the import script.
- **If they attach/upload a file**: the file will be available at a local temp path. Use that path with the import script.
- **If they mention a bank but don't provide a file**: tell them exactly how to download it. For example: "To get your Chase statement, log in to chase.com, go to Statements & Documents, select your account, choose a date range, and download as CSV. Then share the file path with me."
- **If the file format is unclear**: the import script auto-detects Chase, Bank of America, and Wells Fargo formats. For other banks, it falls back to generic CSV parsing that matches common column names (Date, Description, Amount). If that also fails, ask the user what columns their CSV has.

Supported file types: `.csv` (Chase, BofA, Wells Fargo, generic) and `.ofx`/`.qfx` (universal).

## Operations

### 1. Import Transactions

For CSV files:

```bash
python3 scripts/import_csv.py <file_path> [--format chase|bofa|wells_fargo|generic] [--account <name>]
```

For OFX/QFX files:

```bash
python3 scripts/import_ofx.py <file_path> [--account <name>]
```

Choose the import script based on file extension (`.ofx`/`.qfx` → `import_ofx.py`, everything else → `import_csv.py`). The bank format is auto-detected if `--format` is omitted. Both scripts output JSON.

After a successful import, **always run categorization automatically** (step 2) without the user asking. Then present a summary like: "Imported 47 transactions from Jan 1–31. Here's the breakdown: Groceries $342, Dining $189, ..."

If the import returns duplicates > 0, mention it: "Skipped 12 duplicate transactions that were already imported."

### 2. Categorize Transactions

Run on uncategorized transactions:

```bash
python3 scripts/categorize.py run
```

Re-categorize everything (after adding rules):

```bash
python3 scripts/categorize.py run --recategorize
```

Add a custom rule:

```bash
python3 scripts/categorize.py add-rule <category> <pattern> [--type keyword|exact|regex|custom]
```

Categories: groceries, dining, transport, utilities, subscriptions, shopping, healthcare, entertainment, income, uncategorized.

If more than 20% of transactions land in "uncategorized", proactively tell the user: "I couldn't categorize X transactions. Here are some common ones: [list top uncategorized merchants]. Want me to add rules for any of these?" Then use `add-rule` for each one they confirm, and re-run with `--recategorize`.

### 3. Query Spending

```bash
python3 scripts/query.py "<natural language query>"
```

The query parser understands:
- **Time**: "this month", "last month", "January", "last 30 days", "this year", "2024-01-01 to 2024-03-31"
- **Categories**: "groceries", "dining", "transport", "utilities", "subscriptions", "shopping", "healthcare", "entertainment", "income"
- **Merchants**: "at Starbucks", "from Amazon"
- **Aggregations**: total/sum, count, average, largest/max, smallest/min, list/show

Translate the user's question into the closest query string. Present results conversationally — for totals state the amount, for lists format as a readable table with dates and amounts.

### 4. Manage Budgets

```bash
python3 scripts/budget.py set <category> <amount> [--period monthly|yearly]
python3 scripts/budget.py status [--category <name>] [--period monthly|yearly]
python3 scripts/budget.py list
python3 scripts/budget.py delete <category> [--period monthly|yearly]
```

Budget statuses: **ok** (under 80%), **warning** (80-100%), **exceeded** (over 100%).

When reporting status, use clear language: "Your dining budget is at 87% ($261 of $300) — you have $39 left this month." Highlight exceeded budgets prominently.

### 5. Generate Reports

```bash
python3 scripts/report.py [--month <1-12>] [--year <year>] [--period monthly|yearly] [--format json|text|html]
```

Use `--format text` for conversational summaries. Use `--format html` when the user wants a file they can open in a browser — save the output to a file and give them the path.

Present report highlights conversationally: biggest spending categories, changes from last month, budget concerns, and top merchants.

## Data Location

All data is stored locally at `~/.openclaw/skills/finance-reconciler/data/transactions.db` (SQLite). Set the `FINANCE_DATA_DIR` environment variable to override.

## Responding to Common User Intents

| User says | What to do |
|-----------|-----------|
| "Import my statement" / shares a file | Run import → categorize → show summary |
| "How much did I spend on X?" | Run query.py with their question |
| "Set a budget for groceries" | Ask for the amount if not given, then run budget.py set |
| "Am I over budget?" | Run budget.py status for all categories |
| "Show me my report" / "monthly summary" | Run report.py for current or specified month |
| "What can you do?" / "help" | Explain the 5 operations with examples |
| "Why is X categorized as Y?" | Explain the rule-based system, offer to add a custom rule |
| "I don't have a file yet" | Give them bank-specific download instructions |

All scripts output JSON to stdout. Parse the JSON and present results in clear, conversational language — never dump raw JSON to the user.