finance-reconciler

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:

pip3 install pandas ofxparse tabulate python-dateutil

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:

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

For OFX/QFX files:

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:

python3 scripts/categorize.py run

Re-categorize everything (after adding rules):

python3 scripts/categorize.py run --recategorize

Add a custom rule:

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

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

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

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.