CSV Bank Statement Expense Analyzer

# CSV Bank Statement Expense Analyzer

Import your bank or credit card CSV exports, auto-categorize every transaction, and get a full spending breakdown with savings recommendations.

## When to Use

- "Analyze my bank statement from last month"
- "Where is my money going?"
- "Categorize my credit card transactions"

## Requirements

- **python3** with pandas (`pip install pandas`)
- **csvkit** (`pip install csvkit`) for CSV inspection
- Bank/credit card statement exported as CSV

## Workflow

### Step 1 — Inspect the CSV

First, understand the file structure:

```bash
# Preview the CSV
csvlook --max-columns 8 "{BANK_CSV_FILE}" | head -20

# Get column names
csvcut -n "{BANK_CSV_FILE}"

# Check row count
csvstat --count "{BANK_CSV_FILE}"
```

Common column formats:
- Chase: `Transaction Date, Post Date, Description, Category, Type, Amount, Memo`
- Bank of America: `Date, Description, Amount, Running Bal.`
- Discover: `Trans. Date, Post Date, Description, Amount, Category`

### Step 2 — Normalize & Categorize

```bash
python3 << 'PYEOF'
import pandas as pd
import json, sys

df = pd.read_csv("{BANK_CSV_FILE}")

# Detect amount column (try common names)
amount_col = next((c for c in df.columns if c.lower().strip() in ['amount', 'debit', 'transaction amount']), None)
date_col = next((c for c in df.columns if 'date' in c.lower()), None)
desc_col = next((c for c in df.columns if c.lower().strip() in ['description', 'memo', 'name', 'payee']), None)

if not amount_col:
    print("ERROR: Could not detect amount column. Columns found:", list(df.columns))
    sys.exit(1)

df['amount'] = pd.to_numeric(df[amount_col], errors='coerce').abs()
df['date'] = pd.to_datetime(df[date_col], errors='coerce')
df['description'] = df[desc_col].astype(str).str.lower()

# Auto-categorize by keyword matching
categories = {
    'Housing': ['rent', 'mortgage', 'hoa', 'property'],
    'Groceries': ['walmart', 'costco', 'trader joe', 'whole foods', 'kroger', 'safeway', 'grocery', 'aldi'],
    'Dining': ['restaurant', 'doordash', 'uber eats', 'grubhub', 'mcdonald', 'starbucks', 'chipotle', 'cafe'],
    'Transport': ['gas', 'shell', 'chevron', 'uber', 'lyft', 'parking', 'transit', 'metro'],
    'Subscriptions': ['netflix', 'spotify', 'hulu', 'disney', 'apple.com', 'amazon prime', 'youtube'],
    'Shopping': ['amazon', 'target', 'best buy', 'etsy', 'ebay'],
    'Health': ['pharmacy', 'cvs', 'walgreens', 'doctor', 'dental', 'insurance'],
    'Utilities': ['electric', 'water', 'internet', 'phone', 'comcast', 'verizon', 'att'],
    'Income': ['payroll', 'direct dep', 'salary', 'transfer from'],
}

def categorize(desc):
    for cat, keywords in categories.items():
        if any(kw in desc for kw in keywords):
            return cat
    return 'Other'

df['category'] = df['description'].apply(categorize)

# Spending summary
spending = df[df['category'] != 'Income'].groupby('category')['amount'].agg(['sum', 'count']).sort_values('sum', ascending=False)
spending.columns = ['total', 'transactions']

print("\n=== SPENDING BY CATEGORY ===")
print(spending.to_string())
print(f"\nTotal spending: ${spending['total'].sum():,.2f}")
print(f"Total income: ${df[df['category'] == 'Income']['amount'].sum():,.2f}")

# Save categorized CSV
df.to_csv('outputs/finance/categorized-transactions.csv', index=False)
PYEOF
```

### Step 3 — Generate Spending Report

```markdown
# Monthly Spending Report — {MONTH} {YEAR}

## Summary
| | Amount |
|---|---|
| 💰 Total Income | ${INCOME} |
| 💸 Total Spending | ${SPENDING} |
| 📊 Savings Rate | {RATE}% |
| 🏦 Net | ${NET} |

## Spending Breakdown
| Category | Amount | % | Transactions |
|----------|--------|---|-------------|
| 🏠 Housing | ${X} | X% | N |
| 🛒 Groceries | ${X} | X% | N |
| 🍽️ Dining | ${X} | X% | N |
| 🚗 Transport | ${X} | X% | N |
| 📺 Subscriptions | ${X} | X% | N |
| 🛍️ Shopping | ${X} | X% | N |
| ❓ Other | ${X} | X% | N |

## Top 10 Merchants
| Merchant | Total | Count |
|----------|-------|-------|
| {Merchant} | ${X} | N |

## Savings Opportunities
- 🔴 Dining out is ${X}/mo — cooking 2 more meals/week saves ~${Y}/mo
- 🟡 {N} subscriptions totaling ${X}/mo — review if all are needed
- 🟡 {Merchant} appears {N} times — consider bulk buying
```

### Step 4 — Save Report

```bash
mkdir -p outputs/finance
# Categorized CSV saved in Step 2
# Save report markdown
cat > "outputs/finance/{MONTH}-{YEAR}-spending-report.md" << 'EOF'
{REPORT}
EOF
```

## Important Rules

- Never store or transmit raw bank data outside the local filesystem
- Always show the user the auto-categorization results for review
- Ask user to correct any miscategorized transactions before finalizing
- Never provide investment or tax advice

## Example Prompts

- "Analyze my Chase statement from February" (provide CSV)
- "Where am I spending the most money?"
- "Show me all my subscription charges"