{cl.tip}

# The spreadsheet with one formula @icon(table) @category(everyday) > Forty-two cells, and only one of them is written down. Click any cell to read its formula in the bar, nudge the category it belongs to, and watch the grid, the totals and six months of savings move together. #budget #spreadsheet #svg #interactive #money # Assumptions @icon(sliders-horizontal) @density(compact) @skin(form) $monthly income = $4200 // 2000..9000 by 100 @label(Take-home a month) @icon(wallet) annual inflation = 3.2 // 0..12 by 0.1 @suffix(%) @label(Inflation, a year) @icon(trending-up) cell = 12 // @label(Selected cell) @icon(mouse-pointer-click) @advanced @note(row × 10 + column, so 12 is row 2, column C — this row is what a click writes) tweaks = 4444444 // @label(Category dials) @icon(sliders-vertical) @advanced @note(digit N is category N's dial, 0..8, where 4 is untouched and each step is 5%) # Engine @hidden // Object KEYS are parsed too. The `total:` column of the Data tab was `six:`, // and that one word stopped the row from being a row at all — the engine reads // number-WORDS, so `six` became 6 and the literal fell apart. Present mode then // printed the source text where the table should have been, and both linters // stayed green. // `cell` and `tweaks` above are both written by clicking the drawing, so both // must be ONE WORD: a `data-ic-action` substitutes live values by the row's raw // label, and a two-word name never resolves. They also have to be defined // BEFORE the rows that read them — a reference from inside a lambda written // earlier in the file silently evaluates to 0. _cats = ["Rent", "Groceries", "Transport", "Utilities", "Fun", "Health", "Subscriptions"] _base = [1850, 620, 240, 180, 310, 140, 95] _months = ["Jan", "Feb", "Mar", "Apr", "May", "Jun"] _letters = ["A", "B", "C", "D", "E", "F"] // THE ONE FORMULA. Every cell in the grid is this line with two indices // substituted — that is the whole point of the calc, and the reason the sheet // can be re-planned by moving a slider instead of dragging a fill handle. // The monthly rate is the twelfth ROOT of the annual one, not a twelfth of it: // 3.2% a year is 0.263% a month, and dividing instead would overstate the // six-month total. _mrate = (1 + annual inflation / 100)^(1 / 12) - 1 _dial(r) = floor(tweaks / 10^r) mod 10 _adj(r) = 1 + (_dial(r) - 4) * 0.05 _val(r, m) = _base[r] * _adj(r) * (1 + _mrate)^m _rowtot(r) = sum(map(seq(6, 0), m => _val(r, m))) _coltot(m) = sum(map(seq(7, 0), r => _val(r, m))) _grand = sum(map(seq(7, 0), r => _rowtot(r))) _peak = max(map(seq(42, 0), n => _val(floor(n / 6), n mod 6))) _spend = _grand _saved = monthly income * 6 - _grand _rate = if(monthly income > 0, _saved / (monthly income * 6) * 100, 0) _bigr = 0 + indexof(map(seq(7, 0), r => _rowtot(r)), max(map(seq(7, 0), r => _rowtot(r)))) // Thousands separators by hand. `format(n, "finance")` renders 1850 as "1.85K", // which is the wrong register for a spreadsheet cell, and concat() deliberately // hands over a raw number so chart axes stay unformatted. _pad(n) = if(n < 10, concat("00", n), if(n < 100, concat("0", n), concat(n))) _th(v) = if(round(v) < 1000, concat(round(v)), concat(floor(round(v) / 1000), ",", _pad(round(v) mod 1000))) _money(v) = concat("$", _th(v)) # Selection @hidden // The selected cell is one number: row × 10 + column. Clicking a cell writes // it, and everything the formula bar says is read back out of it. _sr = floor(cell / 10) _sc = cell mod 10 _addr = concat(_letters[_sc], _sr + 1) _dialterm = if(_dial(_sr) == 4, "", concat(" × ", round(_adj(_sr), 2))) _fx = concat(_cats[_sr], " · ", _months[_sc], " = ", _base[_sr], _dialterm, " × ", round(1 + _mrate, 5), "^", _sc) _fval = _money(_val(_sr, _sc)) _selx = 162 + _sc * 80 + 2 _sely = 114 + _sr * 30 + 2 _adjlab = concat("DIAL: ", _cats[_sr]) _multlab = if(_dial(_sr) == 4, "at baseline", concat("now ×", round(_adj(_sr), 2))) _kept = concat(_money(_saved), " kept · ", round(_rate, 1), "%") _grandlab = _money(_grand) # Geometry @hidden // EVERY NUMBER AND STRING THE DRAWING NEEDS IS COMPUTED HERE. A row is capped // at 4000 characters, and an svg() template runs on a fuel budget, so the // template has to be pure layout: each of its slots is a property read, and // each cell carries a ready-made `transform` string so one slot places it // instead of four x/y slots. Both budgets fail the same way when exceeded — // an EMPTY CELL, with no error on the row, in the console, or in `--lint`. _cells = map(seq(42, 0), n => {t: concat("translate(", 162 + (n mod 6) * 80, " ", 114 + floor(n / 6) * 30, ")"), o: 0.05 + 0.32 * sqrt(_val(floor(n / 6), n mod 6) / _peak), v: _th(_val(floor(n / 6), n mod 6)), k: concat("cell = ", floor(n / 6) * 10 + (n mod 6)), tip: concat(_cats[floor(n / 6)], " · ", _months[n mod 6], " · ", _money(_val(floor(n / 6), n mod 6)))}) _lines = map(seq(7, 0), r => {t: concat("translate(0 ", 114 + r * 30, ")"), n: r + 1, name: _cats[r], tot: _th(_rowtot(r)), c: if(r == _sr, "var(--accent)", "var(--text)"), w: if(r == _sr, 700, 400)}) _cols = map(seq(6, 0), m => {mx: 202 + m * 80, tx: 234 + m * 80, l: _letters[m], m: _months[m], tot: _th(_coltot(m)), c: if(m == _sc, "var(--accent)", "var(--text)")}) // Five dials for the selected category, clamped to 0..8 so a run of clicks // cannot carry a digit into its neighbour and silently move a different row. _step(d) = min(8, max(0, _dial(_sr) + d)) - _dial(_sr) _go(d) = concat("tweaks = ", tweaks + _step(d) * 10^_sr) // Parallel arrays again, for the same reason: an inline array of two-key // objects would not evaluate, and the only symptom was five pills that never // drew. Nothing else on the row, in the console or in `--lint` says so. _pilld = [-2, -1, 0, 1, 2] _pillt = ["−10%", "−5%", "reset", "+5%", "+10%"] _pillk(i) = if(_pilld[i] == 0, concat("tweaks = ", tweaks + (4 - _dial(_sr)) * 10^_sr), _go(_pilld[i])) _pills = map(seq(5, 0), i => {x: 176 + i * 80, mx: 212 + i * 80, t: _pillt[i], k: _pillk(i), c: if(_pilld[i] == 0, "var(--text-dim)", "var(--accent)"), tip: if(_pilld[i] == 0, concat("Put ", _cats[_sr], " back to its baseline"), concat("Move ", _cats[_sr], " and re-total the sheet"))}) _tilelab = ["SIX-MONTH SPEND", "KEPT", "BIGGEST LINE"] _tileval = [_money(_grand), _money(_saved), _cats[_bigr]] _tilesub = [concat("42 cells, one formula · ", _money(_grand / 6), " a month"), concat(round(_rate, 1), "% of ", _money(monthly income * 6), " take-home"), concat(_money(_rowtot(_bigr)), " · ", round(_rowtot(_bigr) / _grand * 100), "% of everything")] _tilecol = ["var(--text)", if(_saved < 0, "var(--ic-svg-error)", "var(--ic-svg-success)"), "var(--text)"] _tiles = map(seq(3, 0), i => {x: 16 + i * 232, tx: 30 + i * 232, l: _tilelab[i], v: _tileval[i], s: _tilesub[i], c: _tilecol[i]}) # Art @hidden // THE SHEET IS DRAWN, NOT SCREENSHOTTED. Every structural colour is a theme // variable — `fill:var(--text-dim)` sits on the root