weekly-report

Instructions

Generate a weekly finance report from the unified SQLite ledger.

Data source

Primary: data/finance.db (SQLite)

Before generating, check if data is fresh:

sqlite3 data/finance.db "SELECT MAX(date) FROM transactions"

If older than 2 days, suggest running bash scripts/sync.sh first.

Report sections

1. Account Summary

SELECT name, mask, type, balance_current, balance_limit,
       CASE WHEN balance_limit > 0 
            THEN ROUND(balance_current * 100.0 / balance_limit, 0) 
            ELSE NULL END as utilization_pct
FROM accounts ORDER BY type, balance_current DESC;

2. Weekly Cash Flow (last 7 days)

SELECT 
  SUM(CASE WHEN amount < 0 THEN ABS(amount) ELSE 0 END) as income,
  SUM(CASE WHEN amount > 0 THEN amount ELSE 0 END) as spending,
  SUM(CASE WHEN amount < 0 THEN ABS(amount) ELSE 0 END) - 
  SUM(CASE WHEN amount > 0 THEN amount ELSE 0 END) as net
FROM transactions 
WHERE date >= date('now', '-7 days');

3. Spending by Category (top 10, last 7 days)

SELECT description, SUM(amount) as total, COUNT(*) as txns
FROM transactions 
WHERE amount > 0 AND date >= date('now', '-7 days')
GROUP BY description
ORDER BY total DESC LIMIT 15;

4. Notable Transactions

• Largest single purchases (top 5)
• Any new recurring charges detected
• Fees or interest charges

-- Fees/interest this week
SELECT date, description, amount FROM transactions
WHERE date >= date('now', '-7 days') AND amount > 0
  AND (LOWER(description) LIKE '%fee%' 
    OR LOWER(description) LIKE '%interest%'
    OR LOWER(description) LIKE '%overdraft%')
ORDER BY amount DESC;

5. Week-over-Week Comparison

-- This week vs last week
SELECT 
  'This week' as period,
  SUM(CASE WHEN amount > 0 THEN amount ELSE 0 END) as spent
FROM transactions WHERE date >= date('now', '-7 days')
UNION ALL
SELECT 
  'Last week',
  SUM(CASE WHEN amount > 0 THEN amount ELSE 0 END)
FROM transactions WHERE date >= date('now', '-14 days') AND date < date('now', '-7 days');