Skip to content
SlashClone
DiscoverUse in ClaudeSubmit a link
Sign in
Made by@ledgerly

Expense categorizer

@ledgerly/expense-categorizer

Categorize a bank or card CSV export with a rules file you can edit. Writes a categorized CSV, spend by category and by month, and a review list of unknown merchants with suggested rules. Offline and repeatable.

SkillFinancev1.0.0 (latest)Tested
Use in Claude Save Like Clone Comment
Activity
New: be the first to try it
  1. 1#!/usr/bin/env node
  2. 2// Categorize bank or card transactions with an editable rules file.
  3. 3// Usage: node scripts/categorize.mjs <transactions.csv> --rules <rules.csv> [--out out/expenses] [--spend-sign auto|negative|positive]
  4. 4// Rules CSV columns: pattern, category, match (contains | starts_with | exact | regex; default contains),
  5. 5// optional min_amount, max_amount. First matching rule wins, so put specific rules above general ones.
  6. 6import { fail, mdTable, money, monthKey, num, parseArgs, parseDate, readCsv, round, toCsv, writeOut } from "./lib.mjs";
  7. 7
  8. 8const args = parseArgs(process.argv.slice(2));
  9. 9const input = args._[0];
  10. 10const rulesPath = args.rules;
  11. 11const outPrefix = args.out || "out/expenses";
  12. 12if (!input || !rulesPath) fail("usage: node scripts/categorize.mjs <transactions.csv> --rules <rules.csv> [--out out/expenses]");
  13. 13
  14. 14const tx = readCsv(input, "transactions CSV");
  15. 15if (!tx.length) fail(`${input} has no rows.`);
  16. 16const cols = Object.keys(tx[0]);
  17. 17const pick = (names) => names.find((n) => cols.includes(n));
  18. 18const dateCol = pick(["date", "transaction date", "posted date", "posting date", "trans. date", "booking date"]);
  19. 19const descCol = pick(["description", "merchant", "payee", "name", "details", "memo", "narrative"]);
  20. 20const amountCol = pick(["amount", "amount (usd)", "transaction amount"]);
  21. 21const debitCol = pick(["debit", "withdrawal", "withdrawals", "money out"]);
  22. 22const creditCol = pick(["credit", "deposit", "deposits", "money in"]);
  23. 23if (!dateCol || !descCol || (!amountCol && !debitCol))
  24. 24 fail(`could not find date, description, and amount columns in ${input}. Found: ${cols.join(", ")}. Rename them to date, description, amount (or debit and credit).`);
  25. 25
  26. 26const ruleRows = readCsv(rulesPath, "rules CSV", ["pattern", "category"]);
  27. 27const rules = ruleRows
  28. 28 .filter((r) => r.pattern && r.category && !r.pattern.startsWith("#"))
  29. 29 .map((r, i) => {
  30. 30 const match = (r.match || "contains").toLowerCase();
  31. 31 let re = null;
  32. 32 if (match === "regex") {
  33. 33 try {
  34. 34 re = new RegExp(r.pattern, "i");
  35. 35 } catch (e) {
  36. 36 fail(`rule ${i + 1} ("${r.pattern}") is not a valid regex: ${e.message}`);
  37. 37 }
  38. 38 }
  39. 39 return { line: i + 2, pattern: r.pattern, p: r.pattern.toLowerCase(), category: r.category, match, re, min: num(r.min_amount), max: num(r.max_amount) };
  40. 40 });
  41. 41
  42. 42// Decide which sign means money out.
  43. 43let spendSign = args["spend-sign"] || "auto";
  44. 44if (spendSign === "auto" && amountCol) {
  45. 45 const vals = tx.map((r) => num(r[amountCol])).filter(Number.isFinite);
  46. 46 const neg = vals.filter((v) => v < 0).length;
  47. 47 spendSign = neg >= vals.length / 2 ? "negative" : "positive";
  48. 48}
  49. 49
  50. 50function normalize(desc) {
  51. 51 return desc
  52. 52 .toUpperCase()
  53. 53 .replace(/^(POS|DEBIT CARD PURCHASE|CHECKCARD|PURCHASE|ACH DEBIT|ACH)\s+/, "")
  54. 54 .replace(/^(SQ|TST|SP|PP|PAYPAL)\s?\*\s?/, "")
  55. 55 .replace(/[#*]\S*/g, " ")
  56. 56 .replace(/\b\d[\d/.-]*\b/g, " ")
  57. 57 .replace(/\b(INC|LLC|LTD|CO|COM|WWW|HTTPS?)\b/g, " ")
  58. 58 .replace(/[^A-Z&' ]/g, " ")
  59. 59 .replace(/\s+/g, " ")
  60. 60 .trim()
  61. 61 .split(" ")
  62. 62 .slice(0, 3)
  63. 63 .join(" ");
  64. 64}
  65. 65
  66. 66function matchRule(desc, amount) {
  67. 67 const d = desc.toLowerCase();
  68. 68 for (const r of rules) {
  69. 69 if (Number.isFinite(r.min) && amount < r.min) continue;
  70. 70 if (Number.isFinite(r.max) && amount > r.max) continue;
  71. 71 if (r.match === "regex" ? r.re.test(desc) : r.match === "exact" ? d === r.p : r.match === "starts_with" ? d.startsWith(r.p) : d.includes(r.p)) return r;
  72. 72 }
  73. 73 return null;
  74. 74}
  75. 75
  76. 76const out = [];
  77. 77const skipped = [];
  78. 78tx.forEach((r, i) => {
  79. 79 const d = parseDate(r[dateCol]);
  80. 80 let signed;
  81. 81 if (amountCol) signed = num(r[amountCol]);
  82. 82 else signed = (Number.isFinite(num(r[creditCol])) ? num(r[creditCol]) : 0) - (Number.isFinite(num(r[debitCol])) ? num(r[debitCol]) : 0);
  83. 83 if (!d || !Number.isFinite(signed)) {
  84. 84 skipped.push(i + 2);
  85. 85 return;
  86. 86 }
  87. 87 const outflow = amountCol ? (spendSign === "negative" ? signed < 0 : signed > 0) : signed < 0;
  88. 88 const amount = Math.abs(signed);
  89. 89 const desc = r[descCol] || "";
  90. 90 const rule = matchRule(desc, amount);
  91. 91 const category = rule ? rule.category : outflow ? "Uncategorized" : "Uncategorized income";
  92. 92 out.push({ date: d.toISOString().slice(0, 10), month: monthKey(d), description: desc, amount: round(amount), direction: outflow ? "out" : "in", category, rule: rule ? `${rule.match}:${rule.pattern}` : "", merchant_key: normalize(desc) });
  93. 93});
  94. 94
  95. 95const outflows = out.filter((r) => r.direction === "out");
  96. 96const inflows = out.filter((r) => r.direction === "in");
  97. 97const byCat = new Map();
  98. 98for (const r of outflows) byCat.set(r.category, (byCat.get(r.category) || 0) + r.amount);
  99. 99const totalOut = outflows.reduce((a, r) => a + r.amount, 0);
  100. 100const months = [...new Set(out.map((r) => r.month))].sort();
  101. 101
  102. 102const unknown = new Map();
  103. 103for (const r of out.filter((x) => x.category.startsWith("Uncategorized"))) {
  104. 104 const k = r.merchant_key || r.description;
  105. 105 const u = unknown.get(k) || { merchant_key: k, count: 0, total: 0, direction: r.direction, example: r.description };
  106. 106 u.count++;
  107. 107 u.total += r.amount;
  108. 108 unknown.set(k, u);
  109. 109}
  110. 110const unknownList = [...unknown.values()].sort((a, b) => b.total - a.total).map((u) => ({ ...u, total: round(u.total), suggested_rule: `${u.merchant_key.toLowerCase()},<category>,contains` }));
  111. 111
  112. 112writeOut(`${outPrefix}-categorized.csv`, toCsv(out, ["date", "description", "amount", "direction", "category", "rule", "merchant_key"]));
  113. 113writeOut(`${outPrefix}-unknowns.csv`, toCsv(unknownList, ["merchant_key", "direction", "count", "total", "example", "suggested_rule"]));
  114. 114
  115. 115const categorizedShare = outflows.length ? outflows.filter((r) => r.category !== "Uncategorized").reduce((a, r) => a + r.amount, 0) / totalOut : 1;
  116. 116const summary = {
  117. 117 source: input,
  118. 118 rules: rulesPath,
  119. 119 spend_sign: amountCol ? spendSign : "debit/credit columns",
  120. 120 transactions: out.length,
  121. 121 skipped_rows: skipped.length,
  122. 122 outflow_total: round(totalOut),
  123. 123 inflow_total: round(inflows.reduce((a, r) => a + r.amount, 0)),
  124. 124 categorized_share_of_spend: round(categorizedShare, 4),
  125. 125 by_category: Object.fromEntries([...byCat.entries()].sort((a, b) => b[1] - a[1]).map(([k, v]) => [k, round(v)])),
  126. 126 uncategorized_count: out.filter((r) => r.category.startsWith("Uncategorized")).length,
  127. 127 unknown_merchants: unknownList.length,
  128. 128};
  129. 129writeOut(`${outPrefix}-summary.json`, JSON.stringify(summary, null, 2) + "\n");
  130. 130
  131. 131const cats = [...byCat.keys()].sort((a, b) => byCat.get(b) - byCat.get(a));
  132. 132const monthTable = cats.map((c) => [c, ...months.map((m) => money(outflows.filter((r) => r.category === c && r.month === m).reduce((a, r) => a + r.amount, 0))), money(byCat.get(c))]);
  133. 133const md = [
  134. 134 `# Spend by category: ${months[0]} to ${months[months.length - 1]}`,
  135. 135 "",
  136. 136 `${out.length} transactions. Money out ${money(totalOut)}, money in ${money(summary.inflow_total)}. ${(categorizedShare * 100).toFixed(1)}% of spend matched a rule.${skipped.length ? ` Skipped ${skipped.length} row(s) with no date or amount.` : ""}`,
  137. 137 "",
  138. 138 "## Money out by category",
  139. 139 "",
  140. 140 mdTable(["Category", "Total", "Share"], cats.map((c) => [c, money(byCat.get(c)), `${((byCat.get(c) / totalOut) * 100).toFixed(1)}%`])),
  141. 141 "",
  142. 142 "## By month",
  143. 143 "",
  144. 144 mdTable(["Category", ...months, "Total"], monthTable),
  145. 145 "",
  146. 146 "## Needs review",
  147. 147 "",
  148. 148 unknownList.length
  149. 149 ? mdTable(["Merchant", "In/out", "Count", "Total", "Example", "Suggested rule"], unknownList.slice(0, 25).map((u) => [u.merchant_key, u.direction, u.count, money(u.total, 2), u.example, `\`${u.suggested_rule}\``]))
  150. 150 : "Nothing left uncategorized.",
  151. 151 "",
  152. 152].join("\n");
  153. 153writeOut(`${outPrefix}-summary.md`, md);
  154. 154
  155. 155process.stdout.write(`Wrote ${outPrefix}-categorized.csv, ${outPrefix}-summary.md, ${outPrefix}-summary.json, ${outPrefix}-unknowns.csv\n`);
  156. 156process.stdout.write(`${out.length} transactions, ${(categorizedShare * 100).toFixed(1)}% of spend categorized, ${unknownList.length} unknown merchant(s) to review.\n`);
Sign in to comment on a line