prev_balance = PREVIOUS(rec, order_by="Date").Running_Balance or 0
prev_balance + $Amount
The syntax strangeness continues:
rates = Rates.lookupRecords(Person=$Person, order_by="Rate_Start")
rates.find.le($Date).Hourly_Rate
People need to remember that mentally, unless they use AI.
PREVIOUS returns a record, then .Running_Balance pulls out one field of it.
E.g. UPPER($A) is just a function that calls $A.upper(). And $A is just syntactic sugar over rec.A.
PREVIOUS() is only special in that it automatically creates an index, which is important for efficiency. But generally you can look up a set of records and write multi-line Python code to do whatever you want with it and return a value. And it all runs in a sandbox with no access to anything except this document's data.
I reckon it makes more sense to return a table, and then have an index lookup in that table: e.g., index(PREVIOUS(blah_blah), column). Then you can also use an indirection to choose which column from another cell if desired.
This is how google finance in sheets returns some data (like exchange rates, iirc).
The root issue is that there's almost 50 years of spreadsheet syntax to consider and not colliding with.
All feedback good and bad v welcome.
oh and omasheet is Rust and uses Polars so I am working with that rather than NumPy SciPy
People fool themselves into thinking "it's tabular, therefore it's structured" then go on to do assinine shit like putting bonus calculations in random cells, or adding extra tables on the same sheet.
The big value B2B is never going to be whether it has Python. The big value is being able to pull that data and the calculations out of Excel.
For 41 years Excel has been the dominant way to mess with numbers in a grid. Google Sheets and Apple Numbers are really just me too players, albeit with their niches. LibreOffice struggles imo since its main claim is to be fully Excel compatible but there are so many ancient Excel sheets that fail in practice (VBScript?).
I think that there will continue to be a need for folks to mess with numbers in a grid … more between a super calculator and an interactive dataset. I could have chosen a DB vernacular for Omasheet - DuckDB does this for Polars - but that lacks some of the numeric purity of the sheet. So I have gone for the core Polars query model - closer to Arrow columnar data (parquet), streaming support, lowered calculations, lazy execution … but without the DB abstraction layer.
Maybe a controversial take, but IMO Except is the most successful and most popular GUI framework in history. The Internet could die today, and tomorrow the world would go back to spreadsheet programs for business tasks.
I see that import/export pipelines are there, so transform pipelines should be simple, and I guess that's a quick-n-dirty usecase it's there for (the spreadsheet part of "taco-bell programming" ala Dziuba ?).
Personally though for me often, the spreadsheet's interactive and quick recalcs is the important part, the exploratority quick checks with numbers updating, now if it can take arbitrary sheets and get out all "unstructured" calculations so one can edit them more properly later it's a win, or am I missing other major usecases?
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.