Files
2026-11-17 21:48:36 +01:00

162 lines
6.4 KiB
Python

"""Detect recurring payments (subscriptions, rent, insurance...) and flag missed ones."""
from collections import Counter, defaultdict
from datetime import date, timedelta
from decimal import Decimal
from statistics import median
from dateutil.relativedelta import relativedelta
from sqlalchemy import select
from sqlalchemy.orm import Session
from categorize import normalize
from models import Category, Recurring, Transaction
# name, (min gap, max gap) in days, min occurrences, grace days before "missed"
FREQUENCIES = {
"weekly": ((5, 9), 4, 3),
"monthly": ((25, 36), 3, 6),
"quarterly": ((80, 100), 3, 14),
"yearly": ((350, 380), 2, 21),
}
def step(freq: str) -> relativedelta:
return {"weekly": relativedelta(weeks=1), "monthly": relativedelta(months=1),
"quarterly": relativedelta(months=3), "yearly": relativedelta(years=1)}[freq]
def grace(freq: str) -> int:
return FREQUENCIES[freq][2]
def tx_key(tx: Transaction) -> str:
return normalize(tx.counterparty) or normalize(tx.description)
def _clusters(txs: list[Transaction], tol: float = 0.25) -> list[list[Transaction]]:
"""Split one counterparty's payments into groups of similar amounts."""
out: list[list[Transaction]] = []
for tx in sorted(txs, key=lambda t: abs(t.amount)):
if out:
ref = median([abs(t.amount) for t in out[-1]])
if ref and abs(abs(tx.amount) - ref) / ref <= Decimal(str(tol)):
out[-1].append(tx)
continue
out.append([tx])
return out
def _classify(dates: list[date]) -> str | None:
gaps = [(b - a).days for a, b in zip(dates, dates[1:])]
if not gaps:
return None
med = median(gaps)
for name, ((lo, hi), min_n, _) in FREQUENCIES.items():
if lo <= med <= hi and len(dates) >= min_n:
regular = sum(lo <= g <= hi for g in gaps) / len(gaps)
if regular >= 0.7:
return name
return None
def _excluded_category_ids(db: Session) -> set[int]:
return set(db.scalars(select(Category.id).where(Category.kind == "transfer")))
def detect(db: Session, today: date | None = None) -> int:
"""Scan the last ~13 months of outgoing payments and create/update Recurring rows."""
today = today or date.today()
excluded = _excluded_category_ids(db)
txs = db.scalars(select(Transaction).where(Transaction.amount < 0,
Transaction.date >= today - timedelta(days=400)))
groups: dict[str, list[Transaction]] = defaultdict(list)
for tx in txs:
if tx.category_id in excluded:
continue
k = tx_key(tx)
if k:
groups[k].append(tx)
existing = list(db.scalars(select(Recurring)))
created = 0
for key, items in groups.items():
for cl in _clusters(items):
# one payment per day is enough to establish the rhythm
by_day = {t.date: t for t in cl}
dates = sorted(by_day)
freq = _classify(dates)
if not freq:
continue
last = dates[-1]
if last + step(freq) * 3 + timedelta(days=grace(freq)) < today:
continue # stopped more than ~3 cycles ago: probably cancelled
amount = Decimal(median([t.amount for t in sorted(cl, key=lambda t: t.date)[-3:]])).quantize(Decimal("0.01"))
rec = next((r for r in existing if r.match == key and r.amount
and abs(abs(r.amount) - abs(amount)) / abs(r.amount) <= Decimal("0.3")), None)
if rec is None:
rec = Recurring(name=cl[-1].counterparty[:120] or key, match=key, source="detected",
status="detected", amount=amount)
db.add(rec)
existing.append(rec)
created += 1
if rec.source == "detected":
rec.frequency = freq
rec.amount = amount
rec.occurrences = len(dates)
rec.last_date = max(last, rec.last_date or last)
rec.next_date = rec.last_date + step(rec.frequency)
rec.account_id = Counter(t.account_id for t in cl).most_common(1)[0][0]
cats = [t.category_id for t in cl if t.category_id]
rec.category_id = Counter(cats).most_common(1)[0][0] if cats else rec.category_id
db.flush()
for t in cl:
t.recurring_id = rec.id
db.flush()
refresh(db, today)
db.commit()
return created
def refresh(db: Session, today: date | None = None) -> None:
"""Advance last/next dates for every recurring item using the newest matching payment."""
today = today or date.today()
for rec in db.scalars(select(Recurring)):
since = rec.last_date or (rec.next_date - timedelta(days=grace(rec.frequency) * 2) if rec.next_date else None)
q = select(Transaction).where(Transaction.amount < 0 if rec.amount < 0 else Transaction.amount > 0)
if since:
q = q.where(Transaction.date > since - timedelta(days=0 if rec.last_date else 1))
tol = Decimal(rec.tolerance_pct) / 100
hits = [t for t in db.scalars(q.order_by(Transaction.date))
if rec.match in tx_key(t) or rec.match in normalize(t.description)]
hits = [t for t in hits if abs(abs(t.amount) - abs(rec.amount)) <= abs(rec.amount) * max(tol, Decimal("0.5"))]
for t in hits:
t.recurring_id = rec.id
if rec.last_date is None or t.date > rec.last_date:
rec.last_date = t.date
rec.occurrences = (rec.occurrences or 0) + 1
if rec.last_date:
nxt = rec.last_date + step(rec.frequency)
if rec.next_date is None or nxt > rec.next_date or rec.source == "detected":
rec.next_date = nxt
db.flush()
def state(rec: Recurring, today: date | None = None) -> str:
"""ok | due-soon | missed | ignored"""
today = today or date.today()
if rec.status == "ignored":
return "ignored"
if not rec.next_date:
return "ok"
if today > rec.next_date + timedelta(days=grace(rec.frequency)):
return "missed"
if rec.next_date - today <= timedelta(days=7):
return "due-soon"
return "ok"
def monthly_equivalent(rec: Recurring) -> Decimal:
f = {"weekly": Decimal("52") / 12, "monthly": Decimal(1), "quarterly": Decimal(1) / 3,
"yearly": Decimal(1) / 12}[rec.frequency]
return (abs(rec.amount) * f).quantize(Decimal("0.01"))