Comically with their resources they could have put together a type-safe back end on sqllite with multiple available frontend APIs to gradually transfer users to Excel But Not Shitty, yet seem to have made no effort towards anything like that.
I still see people getting date and numeric fields mangled by excel on analysis processes that take literal days to solve because it ends up recursing over all the cells multiple times, and yet even showing them a simple Python or whatever program that does the same thing in minutes, and the result is still put in excel, the next time they need something similar ... Here's another excel abomination.
These aren't dumb people. They've been trained by Excel that the world is awful, slow, and error prone.
with their resources they could have put together a type-safe back end on sqllite with multiple available frontend APIs to gradually transfer users to Excel But Not Shitty
I think that's pretty much what they were going for with MS Access, it seems like Access hasn't matured nearly as much as its long life should have allowed. Possibly because it would eat too much into SQL Server. They have to keep its feature footprint too small to appeal to users who only barely need something like SQL Server.
As for Excel == slow, yeah-- I actually do like Excel, it's in my toolbox, but waiting 5 minutes for a vlookup against a few hundred thousand records to complete because I need one single column in another sheet is maddening when it would take nearly no time at all if I loaded the data into Python -> Pandas.
> but waiting 5 minutes for a vlookup against a few hundred thousand records to complete because I need one single column in another sheet is maddening when it would take nearly no time at all if I loaded the data into Python -> Pandas.
I replied elsewhere in the thread about me working on a new spreadsheet alternative that’s like a reactive Haskell. (Attempting to preserve community norms by not self-promoting more than once per submission.)
One thing that I’m planning is that for any table/array/tree etc. that exceeds a certain size, the data itself and any outputs of functions that work on that data will be offloaded into a real database as rows which can be indexed. Either via SQLite, using PostgreSQL, or even BigQuery (but I don’t trust Google much), this would let users transparently grow their data sets from 1,000 rows to 100,000 to 1,000,000 rows without having to suddenly switch languages or representations. (As an online product this is easier to do, but I think a desktop app equivalent could do it too.) Array functions would in the end be streaming functions rather than literally loading all data in memory.
In Inflex tables are literally arrays of records, but as the language is purely functional and statically typed, the compiler has a lot of freedom to rewrite code into more efficient representations (e.g. streaming), as done in Haskell or SQL.
Access hasn’t matured because the “easy to make database and app” concept has moved to a focus on PowerApps / Sharepoint. It wasn’t/isn’t a strategic platform and isn’t designed for data analysis.
For the sort of stuff Access does you should do it within Excel, which now has the ability to build a fully relational dataset using PowerQuery and PowerPivot.
But yeah, excel can be slow! It’s not designed for big datasets without PQ but inevitably gets used for it because it’s easy and available. I can still do a lookup in excel quicker than I can write one in pandas even if it takes 5 mins. As a hint, the new XLookup strictly dominates Vlookup and depending on how the data is structured/sorted can be many times faster according to the search parameters.
I suspect your example is only for context, but just in case it saves you time in future, if you can sort the data first then you can use something like: if(vlookup(value,range,1,TRUE)=value, vlookup(value,range,column,TRUE), error_marker). The TRUE returns next nearest match, the IF ensures you have an exact match, and it takes a few seconds to lookup over hundreds of thousands of rows.
> they could have put together a type-safe back end on sqllite with multiple available frontend APIs to gradually transfer users to Excel But Not Shitty
They have already done this (although not with SQLite), see “PowerQuery / Get and Transform” which is a full type safe data cleaning/transformation interface, MCode language, and then you can link this typesafe data into excel and then into PowerPivot + DAX / Measures.
All of the above is already built into excel as standard, just nobody seems to know about it. Open excel and click data, get and transform, open PowerQuery editor.
I still see people getting date and numeric fields mangled by excel on analysis processes that take literal days to solve because it ends up recursing over all the cells multiple times, and yet even showing them a simple Python or whatever program that does the same thing in minutes, and the result is still put in excel, the next time they need something similar ... Here's another excel abomination.
These aren't dumb people. They've been trained by Excel that the world is awful, slow, and error prone.