Hacker News Reader

Comment by JCM9 | Hacker News Reader

Spreadsheets have this strange productivity curve where for quick and dirty stuff it’s very fast but go deeper and the spreadsheet turns into this unmanageable mess that greatly reduces productivity long term (vs say using code to do the analysis in a repeatable way).

The business world is full of the later. For example, some bonkers monstrosity of a spreadsheet that Bob from finance built 5 years ago. Bob is no longer with the company and said spreadsheet is the only way the TPS reports get done each month so the whole company is held together by this thing nobody really understands.

View on HN

I’m in a predominately administrative position for a large organization, with an education in development and a lot of freelance experience, but that’s just not the path my professional career took. Our system privileges are strict and I have to dance around things like SharePoint, PowerApps, PowerAutomate, Access, Excel, and sometimes VB Script within… I have access to Node and Python, but user privs are blocked at using npm. I’ve tried to request perms to utilize basic packages such as Yeoman in order to develop in SPFx SharePoint Framework and they consider each package an application that would require a thorough vetting process

All that to say, it’s an absolute cluster-fsck to automate anything with the proper tooling. If there is any way I can just do it in Excel these days, I do it. I’ve created some pretty ridiculous stuff in it. The upside is that once I am able to navigate cutting all of this red tape and use proper tools, I’ll have plenty of projects in mind and likely be asked to return as a contractor for double+ the money to maintain them in retirement.

We used spreadsheets to manage offices -- there is about 15 separate offices -- complete data set (clients, analytics, call logs) and each sheet has many, many different formulas interconnecting all the sheets in the book.

We use Google Sheets, as we changed from Office at the beginning of this year, and 10-15 times a day my tab crashes on Chrome from just existing, let alone when trying to do any operations.

It's a mess, and I'd rather build a simple web app to replace it, but don't have the time, approval, or financial resources to make the switch. So instead of letting me improve 100+ peoples daily workflows, we just suffer.

Go spreadsheets!

I know it sounds like a terrible idea,but next time you wait for the tab to restart how about looking into the sheets api docs? And the next time,try to write a quick script that just makes updating the cells you need to update a bit easier. At the end of it,at least you won't have to suffer.

+1 for Google Apps Script. I’ve built dashboards in sheets using Apps Script to call web services. I think you can even make your Apps Script act as a rudimentary FAAS using the sheet as a database.

Afaik Excel is still way faster than Google Sheets.

That's not an Excel problem. That's a problem with using Google's shitty, half-baked, bloated facsimile of Excel.

I personally would rather build an Excel sheet (in actual Excel) than a simple web app. In fact, I believe a double digit percentage of SaaS startups would be strictly more useful to their users if they came in the form of a downloadable Excel sheet instead of the bullshit web SPA thing. But of course, selling something useful for the customer is not how you make money today.

That’s because corporate policy usually precludes running actual code. I made plenty of huge ass spreadsheets when I worked at one place because there was no way for me to do my job otherwise, unless we wanted to pay hundreds of thousands of dollars to specific developers to build and validate it. The spreadsheets were okay because of reasons beyond my comprehension. The key factor was, I think, that no one else was allowed to write to the spreadsheets, they could just read them and integrate that information into their decision making. I was the only person who could modify and input data.

How much difference is there between

    =IF(A1 > A2, (B2 + B3), (C2 - C3))

and

    (if (> aye-one aye-two) (+ bee-two bee-three) (- cee-two cee-three))

Infix vs prefix, coordinates vs variable names.

It is syntax on top of the essence of a functional programming language.

And it has the same things. One liner of perl, python, or powershell - anyone can write it and it doesn't take too much to manage its complexity.

However, once you get into more complex relationships between data and structures, it takes discipline to manage it. Spreadsheets often are poor at giving you the tools to manage it and so it takes more effort to make sure that you're not making a mess.

A complex spreadsheet is a complex program that needs to have someone who has the discipline and abstractions necessary to manage it.

With the Excel Lambda, you can add a layer of indirection and do something like that by passing the values into your function rather than building your function strictly out of cell references.

Yep - https://www.microsoft.com/en-us/research/blog/lambda-the-ult...

The point was more one of "Excel is an IDE for a functional language." Doing large projects in Excel should take as much care / design / thought as large projects in Lisp (or any other language).

That Excel is everywhere and people often do simple things that grow large without having the discipline of managing the complexity that excel can become - that's where there's problems.

Rather if you say to someone with enough skill to get started "here's python, good luck" you'll get similar results as "here's Excel, good luck." Just that you'll have an easier time persuading cooperate IT to install Excel on your machine compared to Python.

Every criticism, like this one, of spreadsheets getting unwieldy and confusing and difficult to untangle is entirely applicable to most internal code bases as well. Technical debt, old languages, uncommented code blocks, obsolete anti-patterns, tied to some vendor product or github repo that has been discontinued/abandoned, code copy-and-pasted from Stack Overflow (or ChatGPT generated) that nobody ever understood, horribly inefficient database calls, etc etc.

Further, even a big messy Excel model (including VBA code) is usually self-contained in a single file, while a code-based model can be dozens or hundreds of files, many of which are support libraries for stuff that's built-in to Excel (front-end, vector math, charting). And lets not get started on breaking up a model into distributed microservices!

To be fair, regular code also becomes something nobody understands. A spreadsheet might get big, but it doesn't typically grow to like 20,000 loc or greater that somebody wrote in spaghetti code. I mean they certainly can, but that's a lot rarer.

You can structure code; do it the right way. There is no right way to make a big (Excel) spreadsheet; they're not meant for that.

It's not just "spreadsheets", this happens to any software not written by someone trained in software engineering practices – e.g., some script written by a data scientist that only runs on his machine.

Probably true in general. But there are always exceptions. Hard to imagine a tool better than Excel for slot game math calculations. Maybe it can be done?

Calculations for slot machine mechanics and payouts have been in Excel for a long time. There can be a LOT of complexity in these workbooks. Sometimes it’s tricky to debug - but what’s the alternative? Code is often hard to debug too.

Simulating results (Monte Carlo) is nice but having two sets of data for validation/checking against each other is nice.

I am not aware of any alternatives.

The killer app would something with the general ease of use of spreadsheets but with the audit-ability and change management rigor of code development.

Yes.... you wind up with business people who know a ton about business but nothing about software engineering.

No version control, no approval process, no source code repository, no unit or regression testing, no logging, no test vs production environment, no central place where all code/macros are saved, no documentation.

I'm surprised the spreadsheet never got turned into a bit more of a programming environment.

You end up with these giant spaghetti formulas with no easy way to decompose them into smaller functions.

Spreadsheets could have been a gateway drug to learning programming.

Oh it is a gateway drug. New Excel now features lambda functions. There is Python integration with Excel. VBA is tolerable once you understand real software engineering.

Indeed but spreadsheets still are an incredibly versatile tool, but their utility can diminish as complexity increases

What is the alternative?

Any programming language

Honestly this is something you say as a person who doesn't really run into the use cases for spreadsheets.

I've been writing code since I was a kid, but there are jobs a spreadsheet is just the right tool for. Almost anything that involves creating an overview of lots of interdependent numbers really.

Excel in particular is a lot more powerful than you might be aware of if you're a casual user, and I would honestly recommend you learn it properly, as you would learn a programming language, since it's a really useful skill to have (1).

That said, I've often thought about what a programmer's spreadsheet tool would look like. Scientific grade plotting, N-dimensional spreadsheets, a real programming language in the cell formulas...

Someone must have attempted it?

(1) By the way, ChatGPT is great at teaching Excel.

>That said, I've often thought about what a programmer's spreadsheet tool would look like. Scientific grade plotting, N-dimensional spreadsheets, a real programming language in the cell formulas...

Someone must have attempted it?

That sounds suspiciously similar to any SQL database if you ask me...

I like postgres myself, but people talk fondly of mySQL too.. as for viewing the "spreadsheet", there are probably hundreds of solutions. I'm partial to DBeaver myself, if it's the spreadsheet feeling you're looking for at least.

It's not that they're not powerful, it's everything else. Spreadsheets are great for quickly putting down some data and evolving your understanding as you go. Make a pivot table, filter it, make some charts, show it to someone, throw it away. Where they come unstuck is on long running more or less static important calculations that everyone uses. This is because 1) no version control, so you have copies upon copies being passed around. 2) inscrutable formulas that should be documented udfs. 3) data with no constraints like types, nullability, foreign key, so the data has no integrity. 4) insufficient tests. 5) insufficient error messages. 6) insufficient logging. Etc, etc.

I sort of see where you're coming from, and it's probably correct that processes that rely on complex spreadsheets would often be better served by well engineered and designed software. But that is often not the realistic alternative .

The realistic alternative would be a mess of poorly engineered custom scripts, which offer no advantage over the spreadsheet.

With the spreadsheet, anyone can take out their calculator and check the results, without knowing any programming. That's really the killer feature.

For the n-dimensional part there's xcubes:

https://www.xcubes.net/

Usually databases. 8/10 times I see excel used badly is when it should have been access.

Exactly. Because Excel sheets have no tests or code review

Although this may be true, I'm not sure if it always matters. I mean sure, there are some high profile Excel failures that resulted in some big monetary losses, but the same has happened to hundreds of codebases written in a more traditional language that may have also had code review. Also, Excel CAN be reviewed as well. It's all just logic at the end of the day.

This argument usually comes from some IT group that takes like 2 years to do something for the users that get so frustrated that they do it themselves. Since the users are maintaining it, they're constantly reviewing it and are the ones with the most functional knowledge. An alternative IT group has to go to this same group anyway.

Reply on news.ycombinator.com