Adding Window Functions to a Spreadsheet

shevy-java

The syntax is so archaic ... it really is anything but a thing of beauty. prev_balance = PREVIOUS(rec, order_by="Date").Running_Balance or 0 prev_balance + $Amount It's such a strange mix-up of stuff. PREVIOUS() is a macro or looks like one, right? But it is a function call assumingly. But then what's with .Running_Balance? Guess that also ties into a function. But why use different syntax for essentially the same thing? It seems rather weird to write code like that. What would the python variant be like? Or a DSL? The syntax strangeness continues: rates = Rates.lookupRecords(Person=$Person, order_by="Rate_Start") rates.find.le($Date).Hourly_Rate Does that really convey most easily what is wanted? It seems needlessly verbose. Why would it require both $Person and $Date? Should that not be easier to figure out in its own right? Is that efficient? People need to remember that mentally, unless they use AI.

librasteve

I’m all in favour of making spreadsheets better, with code friendly formulas. Here’s one I’m working on - somewhat earlier in dev cycle than grist and while I did consider Python (and lua) for the formula DSL decided to cook up my own. All feedback good and bad v welcome. https://omasheet.org

seemaze

Imagine a world where we starting teaching SQL in primary school..

halo

I sometimes feel the world is slowly reinventing BusinessObjects

mallahan

The way Excel formulas so easily break drove me to create https://www.treesum.app/ .

quaintdev

Checkout ironcalc if you are looking for spreadsheet alternatives https://www.ironcalc.com/index.html

cluckindan

Argh, the Claude-written text is painful to read.

nukanse

Somebody should implement compiled spreadsheets, to get more speed.

heisenzombie

Regarding adding higher level functions to spreadsheets in general. For people using Excel (at least, a modern-ish version that has LAMBDA), LLMs are shockingly good at writing LAMBDA functions. Especially if you have the “Advanced Formula Environment”, which lets you write whole modules of multi-line functions in text files. I’m personally allergic to ever using macros in Excel, and this is basically a better version of that for many (not all) purposes. For a stupid example, I had Opus write a bunch of linear algebra functions, including stuff like computing eigenvectors, culminating in a single function that computes rotation matrices for point-cloud best-fitting (ie the procrustes problem). Yes, I’m fully aware it’s sort of cursed to do that, yes it works, and yes normal people can use the resulting spreadsheet fine, they just magically have an ALIGN3D(frompoints, topoints) function.