Spreadsheets Are Hot–and Cranking Out Complex Code
wired.com
wired.com
The other advice I give is if you are generating analytics, have a PowerBI connector of some kind because the people who make decisions (managers, etc) make them based on PowerBI, and not from an interface their staff is a peer at using, and likely has control over. In enterprise, they want data in metrics their staff can't see, hence a separate tool.
Spreadsheets will always be with us I think. The opportunity may be in creating one that is has sufficient work-alike features with legacy ones, with new power features (python, etc) where there is a connector between the high power open development environment, and the familiar Excel ones managers use. Key thing being not asking managers or sr. employees to change.
I agree that the ideal world is one where you can connect the spreadsheets to other data sources, so you get the best of both.
"The number looks ok" is not a good validation, and there has been some very public data errors as a result of bad spreadsheets.
I often wondered if in the average business is even 10% of the spreadsheets where actually audited what would happen.... I suspect the results would be rather shocking
But, whatever - more work for everyone! Certainly suits me. It's like wanting the average person to keep programming C because you're in infosec.
> Airtable really ought to be killing Excel, but the SaaS model combined with a stupidly low artificial row count limit (over 50000 rows is listed as "contact us for pricing") means that it will never achieve penetration into weird and wonderful use cases like Excel has.
Many Excel processes are 20+ years old. No SaaS could replace the stability and pricing.
When we think of document based workflows as a problem (vs. say just the data/info), we tend to think of them as inefficient and prone to duplication, forking, editing, versioning problems - but I'd argue these are valuable features because they create levers for managing. Maybe I've spent too much time staring into the enterprise abyss and this is the inner deadness of a consultant speaking, but what documents facilitate (e.g. MS Office) is flexibility of ownership, provenance, authenticity, sources of truth, authority, and other qualities.
When you solve a problem, it becomes inert, there is nothing about it to manage anymore, which means someone can't extract value from it, and that's value destruction to them. SaaS problematizes these document features and then "solves," them, which in fact just constrains managers by concretizing data and workflows instead of being a tool that provides some data that ultimately supports a narrative conversation without being a forcing function on a dynamic of ongoing "problems" that is producing value for the business.
I'd suggest this is the quiet part your SaaS prospect customers can't say out loud, because managing isn't solving problems, it's extracting value from them, and using tech to collapse dynamics that are producing value is anti-value from that perspective.
It’s a shame, because I really want to like it - but it’s less flexible than a spreadsheet, and less powerful than a database.
Yes, observed the same. And "The new tool is faster and more reliable!" does not help either. They got their workflow and cope with it - for years.
Only time people adopted new tools - banking - when they could deliver their assignments way faster to their superiors. Personal advantages must be spotted.
Spreadsheets are malleable by design. Being able to modify how it functions on the fly is an enormous power for these users. And the better/faster/smarter SaaS replacement is also extremely rigid and inflexible. So while it works to replace the current version of their god spreadsheet, it can’t adjust on the fly as the user’s needs change like the spreadsheet can.
I’m convinced that there does exist a “better spreadsheet” that treats power users as exactly they and incorporates things from the software engineering works like version control, modularity, reusability, sharability, etc. that hasn’t been built yet.
The more complicated you make it, the more it becomes like real software development. And that is a skill that most people don’t possess.
Excel is ridiculously complicated. I think you're vastly underestimating the ability of these "average business people" because they don't know how "real software development" works.
Elsewhere: Making all cells CAS gives you a strong foundation for version control, modularity, resizability and share-ability.
Happy to share more thoughts on this. I’m two years into the process.
* There’s a language which is like Haskell/Elm/PureScript in terms of being purely functional and statically typed. But with syntax that looks more like Excel.
* Purity gets us fearless recalculation.
* Static types let us build UI elements automatically based on the inferred types of code.
* It’s content-addressable like Unison. That means every expression and “cell” has a unique SHA512 hash of it which refers to only that expression.
* Content addressability makes cache invalidation of results trivial.
* It also makes it easy to say “I want exactly this version of that person’s cell and for all time.” Makes it impossible to break someone else’s code once it’s working.
* It also lets you fearlessly federate, if ever needed.
* Content addressed also means you can write tests against code and have them run on every change. Only the tests whose dependencies changed will be rerun. That’s not normal in Python or Haskell, but in a spreadsheet it is.
There are other design choices related to your comment but I don’t want to ramble on.
In general? Not really. Just as frequently, the fancy tool promoters don't care to understand the subtleties of the job and when it requires flexibility or judgment that the spreadsheet accommodates better. They have their hammer -- software formally engineered by software experts for disempowered "users" -- and everything looks like a nail.
"It's faster and more reliable (when everything goes as planned)" isn't really the slam dunk these folks think it is.
Give these users a more flexible tool like Alteryx, that actually lets them do their job, and I've seen that they'll happily migrate off of Excel.
The flip side of this is that understanding the behaviour of a spreadsheet is generally a specialist job, which is why we have people whose job it is to “run” the spreadsheet. The spreadsheet has rules and boundaries and it will stop working if you just start plugging random values into formula cells.
Excel is like Emacs; most users will write some Elisp at some point, it’s designed to be meddled with from the ground up. AirTable is the VSCode; most users will never write a line of plugin code and when you do you’ll find you can’t extend much.
I think that there will be a generational shift here - you are not going to train an SVP to use Python, but the next generation of SVPs might have more exposure and be willing to use Numpy in a Jupyter notebook.
And in the other direction - there is definitely scope to come up with an “Excel isomorphic” Python framework for data science. It’s fairly easy to generate an Excel sheet from Python computations, but maintaining bi-directionality is Hard, and would require restrictions on the Excel side. I think with the right UI, you could do this though.
He wrote some Python to solve some mildly complex business problem. I told him to translate it into Excel for stakeholders. He did, and the answer came out completely different.
It turned out he had made multiple catastrophic errors in the Python. This is not the first, second, or third time this has happened.
Python, and tech-beyond-Excel in general, just isn't the silver bullet software types often seem to think it is. Even experts sometimes seem to do a worse job in it than in Excel.
Excel has its problems too. Different tools for different jobs. Tech boosters need to understand this and not just cynically assume that spreadsheet lovers are old fogeys who are afraid of their jobs being automated away.
Spreadsheets often mix the data and code/formulas and the formulas are hidden behind the sheet view and sprinkled across many cells. At least Python scripts separate the code from the data so you can write tests using known good or fuzzing data. And you can use version control to track and review code changes.
The two ways I know to get correct work out of him are (1) review it and kick it back to him when problems are found, (2) have him implement what he's trying to do in Excel.
What specific bad habits do you think can be addressed?
I know it's hard to diagnose anything from some forum posts, but I'll take your speculation as to what he should maybe work on.
While I was writing my thesis and job hunting, I attended a few workshops aimed at “grad students breaking into industry”. The main thing I noticed from applied mathematics students in particular was they would write out long functions (really hard to debug) or they worked exclusively in Jupyter notebooks (these have super complicated state, so it takes a lot of discipline to be able to translate these into usable code).
The tools aren’t the issue.
Even for things that are intuitive and have been implemented thousands of times before, like web logins and shopping carts, where the tests one should do are not hard to think of... even so, software engineers rarely develop tests that catch all possible bugs on the first try.
Or they have formulas, but the author calculates a few numbers and throws it away. In this case they’re like a scratch pad, or a calculator with a visible memory.
I think it’s certainly possible to make better business modeling tools, and have played with some designs in that space. But they’ll never be Excel. And that’s ok
Spreadsheets are better because as you say the owner is their job to maintain it. If you replace with an IT process the new "owner" is likely a below-average developer that probably is uninterested in the business. A few years down the road the usefulness of the replacement will suffer.
I disagree. Spreadsheets are incomparably worse because what you charitably described as "the owner is their job to maintain it" in real life it's reflected as having a single employee who abused a first-move advantage to monopolize and excerpt unduly control over, and even hijack, key operation areas.
We all heard horror stories of how employees screwed over their former bosses because only they had control over things like key spreadsheets. Advocating for spreadsheets is advocating for these vulnerabilities.
Spreadsheets will exist regardless of what developers think of them. Ironically, that's a good thing.
Many projects that put food on dev's tables started out as out-of-control Excel monstrosities that were created and operated for long spans of time by well-meaning and productive folks. They start as simple manual spreadsheets with some formulas and then evolve into much more involved beasts. Work gets done and it's all nicely contained in somebody's cube and they look good and can be rightfully proud of their accomplishment.
Things just get done. For a while. Sometimes a LONG while. Until the bitter realities that software developers have learned to deal with over the decades start to seep into these projects and drown the unwitting folks who created them, slowly but surely, like an ever-increasing number of small holes in the bottom of boat. That's when things break or become unmanageable and that's when developers start getting engaged-- assuming these excel masterpieces have actually become mission-critical.
There's a guy at work that operates one of these excel monstrosities. It's been going for ~7 years now. It's a monster excel spreadsheet that, among an ever-growing list of things, does dubious probabilistic forecasting of future PO's based on shit ripped from salesforce (not even using the api). He has a dedicated laptop behind him pulling in data from multiple sources, like clockwork, and has recently started making attractive Power-BI dashboards using his excel worksheets as the data sources. And you know what? He looks GOOD to the people that matter. Does the forecasting actually work? Not really, but being so immersed in all that data has made him knowledgeable about many details of the operation. He's able to keep track of costs and stay on top of things. It doesn't matter (to him) that the whole thing will vaporize when he leaves, or that he could tire of it and just foist it upon some hapless supply-chain person who's just learned to use formulas in excel.
The guy's spreadsheet seems to work. He's delivering what his bosses want to see. You might have an issue with the final output but they apparently don't. What exactly is the problem you think you can fix?
I am on the side of excel being used like this, even if it's hot-garbage. The worst that can happen is that it collapses upon itself and then others need to come in and do it right, or migrate the thing to something else entirely.
The other horror stories of errors in spreadsheets, them yes I have witnessed them regularly.
Most spreadsheets are built with the mindset that it is the end of the dataflow. However, at some point, this data needs to be shared forward. This might not be the original intention, but the more important the report is, the more important downstream use-cases become.
This is when spreadsheets become problematic. One can say that it's the owner's job to keep it compatible, but thinking of keeping it compatible isn't what normal spreadsheet users do.
Some issues I've seen:
* One can add a column easily/ rename it. This breaks any data sharing because now, downstream reports break. (In many cases, the data could have been added as a row instead of a column (new status code, etc.)
* Data-types are not enforced. Nothing prevents entering text into what should be a number or even create a completely new status code. Again, automation downstream breaks.
* Important info is usually not included. The spreadsheet is the latest representation of the data, so in many cases, attributes like the time-period (because it's implied) and unique identifiers (skus most frequently) aren't included.
* Maintaining compatible dimensions across different domains is not a priority for a spreadsheet owner. Finance may group countries differently than Supply-chain, which means they'll always see different numbers and argue that their number is correct.
Source: work at a Fortune 500 company, that has way too many excel reports (with critical performance metrics) and combining them to get an accurate view of the company performance is very labor-intensive and error-prone.
I personally think the most powerful low-code spreadsheet tools we can build are those that allow spreadsheet users to easily transition to full programming languages, if they want to. So rather than locking users into limited and proprietary product number #115 (some of them are mentioned in this article), IMO it's better if users can transition to a full programming language (like Python) very naturally. Som I've spent the past 2 years building Mito [2].
Mito is a spreadsheet extension to your JupyterLab environment. You can display any Pandas dataframe as a spreadsheet, and edit it in a very similar way to Excel. For each edit you make, it generates the corresponding Python code below for those edits. Practically, you can think about Mito as recording a macro, but instead of generating scummy-crummy VBA code, it generates Python.
We're open core [3]. Feedback greatly appreciated!
[1] https://naterush.io/blog/Spreadsheets-are-the-Ultimate-Progr... [2] https://trymito.io [3] https://github.com/mito-ds/monorepo
If your just looking to work with spreadsheets with Python, I’d also reccomend checking out XLWings - I haven’t used it myself but some of our users do and love it!
I think their users are people like me - who work a lot in Jupyter and swear by it - in a Python data analysis/visualisation environment. To bring in the best of spreadsheets into that could be magical. To just work in libreoffice calc or Excel would be a nonstarter, it just doesn't match all the other python tools in the workflow.
Sadly, it isn't. Microsoft set the precedent with Windows 10's telemetry, which they only give you a setting to turn off if you bought Enterprise Edition.
I love python, hate the limits on google sheets apis, and don't really honestly think VBA is "scummy-crummy" it is just unsupported. Microsoft made a huge mistake discontinuing real Visual Basic, as it honestly could have been where Python is now, instead VB.net is basically dead, and the momentum Microsoft had with wysiwyg code editing is way behind where it was.
Very cool that you are generating code for the Jupyter notebook. What are the practical row limits as I see that as one reason someone might use Pandas instead of Excel?
For us, it’s nice because we don’t have to reinvent the wheel(s) that Jupyter comes with :-)
The blending of spreadsheets and notebooks seems inevitable. One trend notebook-as-program. I've heard several variations of this: "The data scientists give us a notebook, and our automation runs the notebook to do <ML thing> on <our internal data sets>". It's clunky to use a format initially intended for interactive visual use for headless automation, but there's a practical wisdom to sticking with whatever format the data scientists prefer. Twenty years ago, I saw the same sort of thing with engineers and spreadsheets.
The one thing notebooks lose, though, is data flow computing. That's a major strength of spreadsheets, and the imperative execution of notebook cells seems like a step backwards. Although I'm sure somebody has bolted some kind of inter-cell dependency execution onto Jupyter by this point.
What does that mean? The citation doesn't mention "open core", but does say Mito is open source.
If you've ever had to work with someone else's Access database, it is unusual to see a reasonably normalized relational database. Most people are much more comfortable with the single flat file of Sharepoint lists.
I get that there are real advantages, I'm just wondering out loud whether they are universally applicable/important to small scale databases.
For writes, there's Airtable, Google Forms, PowerApps etc.
https://github.com/appsmithorg/appsmith
https://news.ycombinator.com/item?id=26657803#26658546 Ask HN: Best low-/no-code solution for simple web-based database frontends
I must be missing something obvious - I don't understand your question.
DB Browser for SQLite is the coolest database product I’ve used recently. Similar to Mito, DB Browser generates SQL into a log as operations are performed in the GUI. Kind of a fun way to learn some basic SQL. I need to find a good GUI authoring layer for SQLite…
MS Access with an ODBC connector.
There are only about 30 million programmers. There are over 1 billion Excel users. Excel is Turing complete. Excel is by far the most used programming language on the planet. It is easily 20 times more popular than the next contender.
The value of Excel is that it is presenting the data, with a set of formulae that let you keep derived data up-to-date. This inferred data provides sums and computations, sometimes simple, but sometimes exquisitely complex. And through this whole range of complexity, with a billion users, virtually nobody treats Excel seriously like a programming language.
We have a programming language which is essentially acting as a declarative database, and yet we don't do unit tests, we don't keep track of changes, we collaborate with Excel by sending it to our colleagues in the mail and god-forbid we should doing any serious linting of what is in the thing.
Anyone who has used Excel in anger realizes why it is so brilliant. Show me another declarative constraint based, data driven inference language that I can teach to my grandmother.
The problem isn't Excel. The problem is that we are treating Excel like its a word processor, and not what it is: a programming language.
- It's easy to load data from database or API without VBA, yet impossible to write updates back without VBA. With VBA it's still messy string concatenation of SQL queries.
- VBA is security nightmare
- Version control is bad. Distributing spreadsheets in emails and collecting changes back is nightmare. Sharing a file on network drive lacks fine-grained permission control.
- It's hard to maintain proper normalized relational data model. It's impossible to abstract the normalized model from the user (i.e. show labels in selectboxes but store ID's)
- It's locale dependent. Date formatting, column separator (comma/semicolon separated), even function names. Unusable in international data exchange.
- It mixes formatting, logic and data. Impossible to make reusable blocks. New lambda functions help somewhat.
And so much more.
The click and drag down feature results in a kind-of code reuse. It bypasses the need to consciously name things. Is this generalisable?
For small scale code, these concerns may be overkill.
In fact it is one of the main reason I have largely abandoned spreadsheats, the behaviour I actually want is found in sql views. Calculate all items in column with one expression vs calculate items in column with unique expressions that were copy, pasted then transformed for each and every row.
The other main reason I abandoned spreadsheets is row level integrity. too many times data that goes together in a row has drifted apart(sort on column subset is main culprit), another inherent problem solved by using a relational database.
the solution to having excel at every desk is to have postgres at every desk. the code will be just as bad but the data integrity will be better.
Imagine taking a data pipeline and renaming all columns and variables alphabetically and then asking someone to check the business logic
Also it pushes people to include excessive parts of formulas into a single calculator rather than breaking them into comprehensible and testable chunks.
All of which are subscription based SAAS. Maybe I am showing my VisiCalc oldster roots, but I want my document related tools to be pay once / run locally.
I long for a computing world where the free tier is run-in-a-browser-on-someone-else's-computer and the paid tier is run local and native.
The problem is portability and lock-in. Which itself might be due to lack of standardization.
AFAIK FramaSoft decided to stop working on FramaCalc (based on / hosting EtherCalc, based on SocialCalc), because it was just too hard for a small association like that (which already has to deal with managing lots of other services for free), but they seem to have decided to postpone the shutdown, I guess because of COVID ?
(For instance it supports exporting ODS but not importing it.)
I'm glad there are more tools to make working with data easier, but I don't use any of them because putting my data into a proprietary service I have to keep paying for is a dealbreaker.
It is a horrible and unusable business model/architecture.
1) Requiring subscription to access when it is not technically required is onerous and very questionable value, especially for tools that I'll use intermittently.
2) Requiring online access instead of local & native apps makes the false assumption that I'm always connected. Often my most productive times are on 6-hour flights (& no, the onboard wifi is not reliable) or away at locations where there is no connectivity.
3) Performance will always suck compared to a fully local app - all the optimizations a developer could do to make it run acceptably could also be applied to a local app to make it really snappy
4) Any SAAS carries significant extra risk that does not exist local native app - that the software /service could disappear any time due to a host of business reasons. Sure, native app companies can also go out of biz or discontinue products, but I still have the software and can upgrade on MY schedule.
5) MOST CRITICALLY, entire classes of very attractive customers are LEGALLY locked out of using your apps. In my industry, there is an entire new class of information called CUI — Confidential Unclassified Information — anything related to DOD projects that isn't quite classified, but there are very strong restrictions on what can be exposed in what way. My wife's industry is legal, and they have almost nothing that could be put on a SAAS, even billing information. And of course same with medical.
Of course there are some apps where the online SAAS is close to essential, like real-time collaboration / conferencing.
However anything else, and it looks strongly like the business model is extractive and rent-seeking, instead of providing value. (and this also applies even to IOT devices - I should be able to access those via the IP, not only through your service...)
Some of the solutions we make have been widely adopted too, with hundreds and in some cases thousands of users.
Both sides can either hack together horrible work-arounds (a matter of "when you have a hammer, everything looks like a nail...") as well as brilliantly thought through solutions.
Each tool should be used for it's best use cases, but not bent into what it wasn't designed for!
IMHO spreadsheets excel at intuitively manipulating the data ON the data itself. While "modern data" tools (especially dbt) try to convert date teams to use developer best practices... At the expense of less intuitive/direct manipulation of the data.
That being said, I think there are also things we could explore in that space: how to make the modern data stack more intuitive?
I'd start with getting people to learn relational data modeling and SQL [1][2] at a deep level. Stop reaching for python and pandas/spark for every basic data manipulation task or query. Stop adding in layers of Airflow/Dagster/Prefect when a simple cron would work. Stop adding in Kubernetes/GKE/Fargate to manage the aforementioned. Stop moving data between systems constantly (meltano, airbyte, fivetran) when you already have it in a perfectly good place. Stop with the toxic positivity that's completely overflowing the modern-data world and all these bullshit VC-infused startups who are convinced they need every single element and more.
99% of business needs can be satisfied by a single Postgres/Mysql installation and a halfway-competent person armed with SQL and an understanding of normalization. Reach for Excel when you need to do more "hands-on" analysis, business modeling, charting, basic forecasting, and presentation for non-technical users.
I definitely agree with the over-hype making simple mundane tasks way harder than they should be.
So yeah, do NOT over-engineer!!
But, on the other hand, doing everything with a single Postgres and spreadsheets seems to go with the hammer-to-nail adage. And all too often, you end up with unmaintainable duck-taped hack-arounds... Which is clearly NOT better (nor necessarily worst) than the over-engineered solution.
In some cases (maybe not 1% but clearly not the majority either), it does make sense to look at other tools that might be available.
That being said, there are waaaay too many options to filter through, because of that darn hype bubble.
I'd like to have a separate worksheet type for "datasheets". Looks and behaves just like an ordinary worksheet, but:
- plugging in a formula applies the formula on every cell of the column. No exceptions. - you can not have different data types in cells within one column. That is, if you have dates in a column, you can't have a string in one cell of thecolumn etc.
Yes, I know about powerthings in excel. No, I do not want to mess with those. Just normal spreadsheet formulas and sheets.
(Okay, a couple of other things: Get rid of vba and bring in the full python ecosystem instead. And if not yet available, version control. It is luckily a while I have worked with excel, so these may be outdated comments)
I think you're on to a good idea, but it has to be a table in the middle of a spreadsheet if you want to get acceptance. What would probably cinch it for you is if you could embed an entire table in a bounded box, with scrolling. It would break the row/column addressing for the contents, but that's always clumsy with tables in sheets anyway.
Example: a data table lives in C10-M10 / C20/M20
Down in C21-M21 you could have sums, counts, etc. that work across all the data.
With this arraignment, you could then sum columns, count, etc. It would be really slick if you snuck in SQL. The tradeoff in immediate addressing of cells within a table could be acceptable.
A while ago, I saw something that allowed spreadsheets in spreadsheet cells, it was mostly intended for interactive use, and dispensed with cell addresses completely in a manner that seemed reasonable at the time.
Domo was claiming it could get companies off of spreadsheets. But it turns out that they're an enterprise level reporting system that's a minimum of $30,000. That's not a viable option for a 5-person non-profit, but they'll get swept up in the conversation only to find out later that no, Domo and Excel are not equivalents.
On a side note: I didn’t find a Python library for time series generation (not analysis). Something where you can build some models (e.g. loan, income, expenses) which depend on a common parameter (time) and then evaluate all your models for different values of the common parameter. Right now, I generate pandas series/dataframes and combine them afterwards, which also took some massaging of pandas (which I also usually don‘t use a lot).
I think there are a lot of mis-uses of spreadsheets. When users record list type data, long-form content, relational data, etc within spreadsheets, they are probably better off using an app - they, or their IT team can build.
I once seen an enterprise org process where a health and safety check was completed on a clipboard. That user would go back to their desk, push that data into excel, another user would write a script to push that excel data into MySQL.
There are many use cases where spreadsheets are perfect - building financial models.
For transparency - I am the cofounder of a no/low-code tool called Budibase. I am also a happy spreadsheet user. https://github.com/Budibase/budibase
Hmm.
It's the leading open source low code platform and perfect for building UIs on top of spreadsheets
Instead, the spreadsheet paradigm has the promise of being far more powerful. Jupyter notebooks are one example of adapting it to a different realm, and it also ended up being used everywhere and looked down upon by the snobs.
Yes please! Can I get this for Google Sheets? :/