aashari/mail-expenses

Mail Expenses — Financial Transaction Summary

Period: $ARGUMENTS (default: last 24 hours)

Step 1: Find financial emails

Use SQLite date functions — never compute Unix epochs manually (wrong year risk).

DB="$HOME/Library/Mail/V10/MailData/Envelope Index"

# Date range from $ARGUMENTS. Use SQLite date expressions directly:
# "yesterday"   → dt >= date('now','-1 day','localtime') AND dt < date('now','localtime')
# "today"       → dt >= date('now','localtime')
# "last 7 days" → dt >= date('now','-7 days','localtime')
# "this month"  → dt >= date('now','start of month','localtime')
# Always print the range before querying so the period is visible in output.

sqlite3 "$DB" "
SELECT datetime(m.date_received,'unixepoch','localtime') as dt,
       s.subject, a.address as sender, mb.url, m.ROWID
FROM messages m
JOIN subjects  s  ON m.subject = s.ROWID
JOIN addresses a  ON m.sender  = a.ROWID
JOIN mailboxes mb ON m.mailbox = mb.ROWID
WHERE datetime(m.date_received,'unixepoch','localtime') >= date('now','-1 day','localtime')
  AND datetime(m.date_received,'unixepoch','localtime') <  date('now','localtime')
  AND m.deleted = 0
  AND mb.url NOT LIKE '%Spam%' AND mb.url NOT LIKE '%Trash%'
  AND mb.url NOT LIKE '%Sent%'
  AND (
    s.subject LIKE '%receipt%'
    OR s.subject LIKE '%invoice%'
    OR s.subject LIKE '%payment%'
    OR s.subject LIKE '%order%'
    OR s.subject LIKE '%purchase%'
    OR s.subject LIKE '%charge%'
    OR s.subject LIKE '%transaction%'
    OR s.subject LIKE '%billing%'
    OR s.subject LIKE '%renewal%'
    OR s.subject LIKE '%subscription%'
    OR s.subject LIKE '%paid%'
    OR s.subject LIKE '%refund%'
    OR s.subject LIKE '%transfer%'
    OR s.subject LIKE '%successful%'
    OR s.subject LIKE '%confirmation%'
    -- Indonesian / multilingual
    OR s.subject LIKE '%bukti%'
    OR s.subject LIKE '%pembayaran%'
    OR s.subject LIKE '%tagihan%'
    OR s.subject LIKE '%transaksi%'
    OR s.subject LIKE '%berhasil%'
    -- Common financial senders
    OR a.address LIKE '%bank%'
    OR a.address LIKE '%paypal%'
    OR a.address LIKE '%stripe%'
    OR a.address LIKE '%xendit%'
    OR a.address LIKE '%livin%'
    OR a.address LIKE '%gopay%'
    OR a.address LIKE '%ovo%'
    OR a.address LIKE '%dana%'
    OR a.address LIKE '%apple%'
    OR a.address LIKE '%google%'
    OR a.address LIKE '%amazon%'
    OR a.address LIKE '%shopee%'
    OR a.address LIKE '%tokopedia%'
    OR a.address LIKE '%grab%'
  )
ORDER BY m.date_received DESC;" 2>/dev/null

Step 2: Parse amounts from email bodies

python3 ~/.claude/skills/_mail-shared/parser.py <ROWID1> <ROWID2> ...

Step 3: Extract amounts with Python regex

From each parsed body, extract monetary amounts:

import re

patterns = [
    r'(?:Rp|IDR)\s*[\d.,]+',          # Indonesian Rupiah
    r'(?:USD|US\$|\$)\s*[\d.,]+',      # USD
    r'(?:SGD|S\$)\s*[\d.,]+',          # Singapore dollar
    r'Total[:\s]+(?:Rp|IDR|USD|\$|€|£)[\d.,]+',
    r'Amount[:\s]+(?:Rp|IDR|USD|\$|€|£)[\d.,]+',
    r'(?:€|£|¥|₹|RM|THB|PHP|VND)\s*[\d.,]+',
]

Step 4: Categorize transactions

• Transfers (bank to bank, BI-Fast, QRIS, virtual account)
• Bills/Subscriptions (recurring charges, renewals)
• Shopping (e-commerce, marketplace)
• Food/Lifestyle (restaurants, delivery, groceries)
• Travel (flights, hotels, transport)
• Refunds (money coming back)
• Credit Card Payments (paying off balances — not new spending)

Output Format

Financial Activity — [PERIOD]

Line-by-line per account (no markdown tables — WhatsApp/Telegram render them as raw text):

💳 [Bank/Wallet Name]

HH:MM · [Merchant] · Rp X.XXX

Subtotals by category. Total: Rp X.XXX

Note refunds separately. Flag duplicates and unusually large transactions proactively.