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

Event budget tracker

@eventcrew/event-budget-tracker

Track an event budget against what you have committed and spent, from a simple CSV of line items. Shows totals by category, lines over budget, bills coming due, contingency left, and cost per attendee or lead.

SkillFinancev1.0.1 (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// Event budget vs. actuals from a CSV of line items.
  3. 3// Usage: node scripts/budget.mjs [data/event-budget.csv] [out-dir]
  4. 4// [--total 50000] [--contingency 10] [--as-of YYYY-MM-DD] [--due-days 7]
  5. 5// [--attendees N] [--leads N] [--meetings N]
  6. 6// Writes: <out-dir>/event-budget.md and <out-dir>/event-budget.json
  7. 7//
  8. 8// Columns (flexible names): category, item, budget, committed, actual, vendor, paid, due_date
  9. 9// budget what you planned to spend on the line
  10. 10// committed what you have agreed to pay (signed quote or PO), invoiced or not
  11. 11// actual what has been invoiced or spent so far
  12. 12// paid yes/no; unpaid lines with a due date are listed as bills to pay
  13. 13// Forecast per line = the largest of committed and actual, or the budget if neither is set yet.
  14. 14import { existsSync, mkdirSync, readFileSync, writeFileSync } from "node:fs";
  15. 15import { join } from "node:path";
  16. 16
  17. 17const args = process.argv.slice(2);
  18. 18function flag(name, fallback) {
  19. 19 const i = args.indexOf(name);
  20. 20 if (i === -1) return fallback;
  21. 21 const value = args[i + 1] ?? "";
  22. 22 args.splice(i, 2);
  23. 23 return value;
  24. 24}
  25. 25function num(name, fallback) {
  26. 26 const raw = flag(name, fallback);
  27. 27 if (raw === null) return null;
  28. 28 const n = Number(String(raw).replace(/[$,\s]/g, ""));
  29. 29 if (!Number.isFinite(n) || n < 0) fail(`${name} must be a positive number (got "${raw}")`);
  30. 30 return n;
  31. 31}
  32. 32function fail(msg) {
  33. 33 console.error(`budget: ${msg}`);
  34. 34 process.exit(1);
  35. 35}
  36. 36const asOf = flag("--as-of", new Date().toISOString().slice(0, 10));
  37. 37const total = num("--total", null);
  38. 38const contingencyPct = num("--contingency", "0");
  39. 39const dueDays = num("--due-days", "7");
  40. 40const attendees = num("--attendees", null);
  41. 41const leads = num("--leads", null);
  42. 42const meetings = num("--meetings", null);
  43. 43const [input = "data/event-budget.csv", outDir = "out"] = args;
  44. 44if (!/^\d{4}-\d{2}-\d{2}$/.test(asOf)) fail(`--as-of must be YYYY-MM-DD (got "${asOf}")`);
  45. 45if (!existsSync(input)) fail(`${input} not found. Start from examples/event-budget.csv.`);
  46. 46
  47. 47function parseCsv(src) {
  48. 48 const rows = [];
  49. 49 let row = [];
  50. 50 let field = "";
  51. 51 let quoted = false;
  52. 52 const push = () => {
  53. 53 row.push(field);
  54. 54 field = "";
  55. 55 if (row.some((v) => v.trim() !== "")) rows.push(row);
  56. 56 row = [];
  57. 57 };
  58. 58 for (let i = 0; i < src.length; i++) {
  59. 59 const ch = src[i];
  60. 60 if (quoted) {
  61. 61 if (ch === '"' && src[i + 1] === '"') {
  62. 62 field += '"';
  63. 63 i++;
  64. 64 } else if (ch === '"') quoted = false;
  65. 65 else field += ch;
  66. 66 } else if (ch === '"') quoted = true;
  67. 67 else if (ch === ",") {
  68. 68 row.push(field);
  69. 69 field = "";
  70. 70 } else if (ch === "\n" || ch === "\r") {
  71. 71 if (ch === "\r" && src[i + 1] === "\n") i++;
  72. 72 push();
  73. 73 } else field += ch;
  74. 74 }
  75. 75 push();
  76. 76 return rows;
  77. 77}
  78. 78
  79. 79const ALIASES = {
  80. 80 category: ["category", "bucket", "type", "group"],
  81. 81 item: ["item", "line item", "description", "name"],
  82. 82 budget: ["budget", "planned", "plan", "estimate", "budgeted"],
  83. 83 committed: ["committed", "contracted", "po", "quote", "agreed"],
  84. 84 actual: ["actual", "actuals", "spent", "invoiced", "spend"],
  85. 85 vendor: ["vendor", "supplier", "payee"],
  86. 86 paid: ["paid", "paid?", "is paid"],
  87. 87 due_date: ["due_date", "due date", "due", "payment due", "invoice due"],
  88. 88};
  89. 89function key(h) {
  90. 90 const v = h.trim().toLowerCase();
  91. 91 for (const [k, names] of Object.entries(ALIASES)) if (names.includes(v)) return k;
  92. 92 return null;
  93. 93}
  94. 94function money(s, where) {
  95. 95 const v = String(s ?? "").trim();
  96. 96 if (v === "" || v === "-") return null;
  97. 97 const neg = /^\(.*\)$/.test(v) || v.startsWith("-");
  98. 98 const n = Number(v.replace(/[()$€£,\s-]/g, ""));
  99. 99 if (!Number.isFinite(n)) {
  100. 100 warnings.push(`${where}: could not read amount "${v}", treated as blank`);
  101. 101 return null;
  102. 102 }
  103. 103 return Math.round((neg ? -n : n) * 100);
  104. 104}
  105. 105
  106. 106const warnings = [];
  107. 107const [header, ...body] = parseCsv(readFileSync(input, "utf8"));
  108. 108if (!header) fail(`${input} is empty`);
  109. 109const keys = header.map(key);
  110. 110if (!keys.includes("budget")) fail(`${input} needs a "budget" column. Found: ${header.join(", ")}`);
  111. 111
  112. 112const lines = body.map((cells, i) => {
  113. 113 const r = {};
  114. 114 keys.forEach((k, j) => {
  115. 115 if (k) r[k] = (cells[j] ?? "").trim();
  116. 116 });
  117. 117 const where = `row ${i + 2}`;
  118. 118 const budget = money(r.budget, where) ?? 0;
  119. 119 const committed = money(r.committed, where);
  120. 120 const actual = money(r.actual, where);
  121. 121 const forecast = committed === null && actual === null ? budget : Math.max(committed ?? 0, actual ?? 0);
  122. 122 const paid = /^(y|yes|true|1|paid|x)$/i.test(r.paid ?? "");
  123. 123 const due = /^\d{4}-\d{2}-\d{2}$/.test(r.due_date ?? "") ? r.due_date : null;
  124. 124 if (r.due_date && !due) warnings.push(`${where}: due date "${r.due_date}" is not YYYY-MM-DD, ignored`);
  125. 125 return {
  126. 126 row: i + 2,
  127. 127 category: r.category || "Uncategorized",
  128. 128 item: r.item || `(row ${i + 2})`,
  129. 129 vendor: r.vendor ?? "",
  130. 130 budget,
  131. 131 committed,
  132. 132 actual,
  133. 133 forecast,
  134. 134 variance: budget - forecast,
  135. 135 status: committed === null && actual === null ? "planned" : actual !== null && actual >= (committed ?? 0) ? "invoiced" : "committed",
  136. 136 paid,
  137. 137 due_date: due,
  138. 138 };
  139. 139});
  140. 140if (lines.length === 0) fail(`${input} has a header but no line items`);
  141. 141
  142. 142const sum = (arr, f) => arr.reduce((a, x) => a + (f(x) ?? 0), 0);
  143. 143const categories = [...new Set(lines.map((l) => l.category))].map((name) => {
  144. 144 const ls = lines.filter((l) => l.category === name);
  145. 145 const budget = sum(ls, (l) => l.budget);
  146. 146 const forecast = sum(ls, (l) => l.forecast);
  147. 147 return {
  148. 148 name,
  149. 149 budget,
  150. 150 committed: sum(ls, (l) => l.committed),
  151. 151 actual: sum(ls, (l) => l.actual),
  152. 152 forecast,
  153. 153 variance: budget - forecast,
  154. 154 pct_of_budget: budget ? Math.round((forecast / budget) * 1000) / 10 : null,
  155. 155 over: forecast > budget,
  156. 156 };
  157. 157});
  158. 158categories.sort((a, b) => a.variance - b.variance);
  159. 159
  160. 160const plannedTotal = sum(lines, (l) => l.budget);
  161. 161const totalBudget = total !== null ? Math.round(total * 100) : plannedTotal;
  162. 162const contingency = Math.round(totalBudget * (contingencyPct / 100));
  163. 163const forecastTotal = sum(lines, (l) => l.forecast);
  164. 164const totals = {
  165. 165 budget: totalBudget,
  166. 166 line_budgets: plannedTotal,
  167. 167 unallocated: totalBudget - plannedTotal,
  168. 168 contingency,
  169. 169 committed: sum(lines, (l) => l.committed),
  170. 170 actual: sum(lines, (l) => l.actual),
  171. 171 forecast: forecastTotal,
  172. 172 remaining: totalBudget - forecastTotal,
  173. 173 remaining_after_contingency: totalBudget - contingency - forecastTotal,
  174. 174 unpaid: sum(lines.filter((l) => !l.paid), (l) => l.actual),
  175. 175};
  176. 176if (totals.unallocated < 0) warnings.push("line budgets add up to more than the total budget");
  177. 177
  178. 178const daysUntil = (d) => Math.round((Date.parse(d) - Date.parse(asOf)) / 86400000);
  179. 179const bills = lines
  180. 180 .filter((l) => !l.paid && l.due_date && (l.actual ?? l.committed ?? 0) > 0)
  181. 181 .map((l) => ({
  182. 182 item: l.item,
  183. 183 vendor: l.vendor,
  184. 184 amount: l.actual ?? l.committed,
  185. 185 invoiced: l.actual !== null,
  186. 186 due_date: l.due_date,
  187. 187 days: daysUntil(l.due_date),
  188. 188 }))
  189. 189 .filter((b) => b.days <= dueDays)
  190. 190 .sort((a, b) => a.days - b.days);
  191. 191const overLines = lines.filter((l) => l.variance < 0).sort((a, b) => a.variance - b.variance);
  192. 192
  193. 193const per = (n) => (n ? Math.round(forecastTotal / n) : null);
  194. 194const unitCosts = {
  195. 195 per_attendee: per(attendees),
  196. 196 per_lead: per(leads),
  197. 197 per_meeting: per(meetings),
  198. 198};
  199. 199
  200. 200let verdict;
  201. 201if (totals.remaining < 0) verdict = "over budget";
  202. 202else if (totals.remaining_after_contingency < 0) verdict = "into contingency";
  203. 203else if (categories.some((c) => c.over)) verdict = "on budget overall, some categories over";
  204. 204else verdict = "on budget";
  205. 205
  206. 206// ---- output ----------------------------------------------------------------
  207. 207const $ = (cents) => {
  208. 208 if (cents === null || cents === undefined) return "";
  209. 209 const neg = cents < 0;
  210. 210 const s = (Math.abs(cents) / 100).toLocaleString("en-US", { minimumFractionDigits: 0, maximumFractionDigits: 2 });
  211. 211 return neg ? `-$${s}` : `$${s}`;
  212. 212};
  213. 213const md = [
  214. 214 `# Event budget (as of ${asOf})`,
  215. 215 "",
  216. 216 `**${verdict[0].toUpperCase()}${verdict.slice(1)}.** Forecast ${$(totals.forecast)} of ${$(totals.budget)} budget. Remaining ${$(totals.remaining)}${contingency ? ` (${$(totals.remaining_after_contingency)} after ${contingencyPct}% contingency)` : ""}.`,
  217. 217 "",
  218. 218 `Committed ${$(totals.committed)}. Invoiced ${$(totals.actual)}. Invoiced but unpaid ${$(totals.unpaid)}.`,
  219. 219 totals.unallocated > 0 ? `Not yet allocated to a line: ${$(totals.unallocated)}.` : "",
  220. 220 "",
  221. 221 "## By category",
  222. 222 "",
  223. 223 "| Category | Budget | Committed | Invoiced | Forecast | Left | Used |",
  224. 224 "| --- | ---: | ---: | ---: | ---: | ---: | ---: |",
  225. 225 ...categories.map(
  226. 226 (c) =>
  227. 227 `| ${c.over ? "**OVER** " : ""}${c.name} | ${$(c.budget)} | ${$(c.committed)} | ${$(c.actual)} | ${$(c.forecast)} | ${$(c.variance)} | ${c.pct_of_budget ?? "n/a"}% |`,
  228. 228 ),
  229. 229 `| **Total** | ${$(plannedTotal)} | ${$(totals.committed)} | ${$(totals.actual)} | ${$(forecastTotal)} | ${$(plannedTotal - forecastTotal)} | ${plannedTotal ? Math.round((forecastTotal / plannedTotal) * 1000) / 10 : "n/a"}% |`,
  230. 230 "",
  231. 231 "## Lines over budget",
  232. 232 "",
  233. 233 overLines.length
  234. 234 ? overLines.map((l) => `- ${l.item} (${l.category}): ${$(-l.variance)} over (${$(l.forecast)} vs ${$(l.budget)})`).join("\n")
  235. 235 : "None.",
  236. 236 "",
  237. 237 `## Bills due within ${dueDays} days`,
  238. 238 "",
  239. 239 bills.length
  240. 240 ? bills
  241. 241 .map((b) => `- ${b.days < 0 ? `**OVERDUE ${-b.days}d** ` : ""}${b.due_date}: ${b.vendor || "vendor?"}, ${b.item}, ${$(b.amount)}${b.invoiced ? "" : " (committed, invoice not logged)"}`)
  242. 242 .join("\n")
  243. 243 : "None.",
  244. 244 "",
  245. 245];
  246. 246if (attendees || leads || meetings) {
  247. 247 md.push("## Unit costs (forecast)", "");
  248. 248 if (attendees) md.push(`- Per attendee (${attendees}): ${$(unitCosts.per_attendee)}`);
  249. 249 if (leads) md.push(`- Per lead (${leads}): ${$(unitCosts.per_lead)}`);
  250. 250 if (meetings) md.push(`- Per meeting (${meetings}): ${$(unitCosts.per_meeting)}`);
  251. 251 md.push("");
  252. 252}
  253. 253if (warnings.length) md.push("## Warnings", "", ...warnings.map((w) => `- ${w}`), "");
  254. 254const mdText = md.filter((l, i, a) => !(l === "" && a[i - 1] === "")).join("\n");
  255. 255
  256. 256const toDollars = (o) =>
  257. 257 Object.fromEntries(Object.entries(o).map(([k, v]) => [k, typeof v === "number" && !["pct_of_budget", "row", "days"].includes(k) ? v / 100 : v]));
  258. 258mkdirSync(outDir, { recursive: true });
  259. 259writeFileSync(join(outDir, "event-budget.md"), mdText);
  260. 260writeFileSync(
  261. 261 join(outDir, "event-budget.json"),
  262. 262 `${JSON.stringify(
  263. 263 {
  264. 264 as_of: asOf,
  265. 265 verdict,
  266. 266 totals: toDollars(totals),
  267. 267 categories: categories.map(toDollars),
  268. 268 over_budget_lines: overLines.map((l) => ({ item: l.item, category: l.category, over_by: -l.variance / 100 })),
  269. 269 bills_due: bills.map(toDollars),
  270. 270 unit_costs: toDollars(unitCosts),
  271. 271 warnings,
  272. 272 },
  273. 273 null,
  274. 274 2,
  275. 275 )}\n`,
  276. 276);
  277. 277console.log(mdText);
  278. 278console.log(`Wrote ${join(outDir, "event-budget.md")} and ${join(outDir, "event-budget.json")}`);
Sign in to comment on a line