The Tyranny of Spreadsheets • Hacker News Board

Re: The Tyranny of Spreadsheets

#241

Post by stjohnswarts » Fri, Jul 23, 2021, 5:30 PM UTC

dgudkov wrote: post #177

narush wrote: post #20

Fantastic blog post. I highly recommend reading it in full, and also checking out the work of the European Spreadsheet Risks Group, and Felienne Hermans specifically (referenced in the original post). I've been working on a spreadsheet startup [1] for the past 8 months or so and those folks have a large amount of absolutely upsetting-but-helpful research on spreadsheet usage/errors. After the past few years working w…

>Spreadsheets give not-super-technical users a way to program data transformations in what I would argue is the most generally intuitive way that exists I don't think spreadsheets are a good way to program data transformations at all. Data transformations are inherently a pipeline/function abstraction, and even non-technical users understand that. Spreadsheets don't offer a good way to decompose a data transformation…

sometimes all you have is a hammer and a screw though. Not everyone is a computer scientist.

Re: The Tyranny of Spreadsheets

#242

Post by hcarvalhoalves » Fri, Jul 23, 2021, 5:34 PM UTC

dgudkov wrote: post #177

narush wrote: post #20

Fantastic blog post. I highly recommend reading it in full, and also checking out the work of the European Spreadsheet Risks Group, and Felienne Hermans specifically (referenced in the original post). I've been working on a spreadsheet startup [1] for the past 8 months or so and those folks have a large amount of absolutely upsetting-but-helpful research on spreadsheet usage/errors. After the past few years working w…

>Spreadsheets give not-super-technical users a way to program data transformations in what I would argue is the most generally intuitive way that exists I don't think spreadsheets are a good way to program data transformations at all. Data transformations are inherently a pipeline/function abstraction, and even non-technical users understand that. Spreadsheets don't offer a good way to decompose a data transformation…

Spreadsheets provide a spatial mapping (cells) to represent data transformations over time. Users are more comfortable working w/ spatial paradigms, as humans have a better sense of location in space than in time. Thinking in terms of "transformations over time" is difficult to reason about, specially in programming.

Re: The Tyranny of Spreadsheets

#243

Post by taneq » Fri, Jul 23, 2021, 5:37 PM UTC

dgudkov wrote: post #177

narush wrote: post #20

Fantastic blog post. I highly recommend reading it in full, and also checking out the work of the European Spreadsheet Risks Group, and Felienne Hermans specifically (referenced in the original post). I've been working on a spreadsheet startup [1] for the past 8 months or so and those folks have a large amount of absolutely upsetting-but-helpful research on spreadsheet usage/errors. After the past few years working w…

>Spreadsheets give not-super-technical users a way to program data transformations in what I would argue is the most generally intuitive way that exists I don't think spreadsheets are a good way to program data transformations at all. Data transformations are inherently a pipeline/function abstraction, and even non-technical users understand that. Spreadsheets don't offer a good way to decompose a data transformation…

The fact that you had to use so many big words to explain why spreadsheets aren't a good way for users to "write some numbers here and then calculate some numbers from those first numbers over here" says to me that your definition of "good" varies a fair bit from the definition these users are using.

Joins are great and relational databases are super useful but these peoples' problems are 99% solved by "store a list, maybe do some arithmetic."

Re: The Tyranny of Spreadsheets

#244

Post by beefield » Fri, Jul 23, 2021, 5:53 PM UTC

benhurmarcel wrote: post #233

beefield wrote: post #184

Earlier quoted context omitted.

Opposing anecdote: I have converted excel spreadsheets built over years to SQL, and typically it did not take weeks to do that. ( Any organization that has a smallest reason to care about their data should remove save button from excel and start educating their personnel. Using excel in any important role should be seen as making the eventual mistakes on purpose and someone should be kept responsible.)

> I have converted excel spreadsheets built over years to SQL, and typically it did not take weeks to do that. The "years" of work are typically not for implementing the spreadsheet, but to define what it does. The requirements evolve over many versions. Of course it's fast to re-implement it after all the work to define the requirement is done, but it's only possible because Excel allowed all those prototypes.

Grandparent claimed that something done in excel by one person in a week takes a team of five and half a year to reimplement in some other language. I disagree with that as a general rule (of course, exceptions may apply, as usual).

I fully agree that Excel is great for quick drafting, visualizing data quickly, and prototyping. But it should be left there. Anything you do that lasts even overnight and has any significant numbers in it should be done with something else than Excel.

Re: The Tyranny of Spreadsheets

#245

Post by rrrrrrrrrrrryan » Fri, Jul 23, 2021, 6:38 PM UTC

zoomastigophore wrote: post #189

Andy_G11 wrote: post #174

'000s of rows of data in a spreadsheet - sigh. But say 'database' to some people and they hear 'The Devil'. Excel is a glorious tool which welcomes all, the savvy and the unskilled but imaginative newbies alike. There is something about all those little cells that presents an itch everyone wants to scratch, and you just know that for some that scratching is going to produce something akin to a spreadsheet version of…

I think one of the main problem with databases is how do you populate them. I wish there were simple-as-Excel frontends for databases where a normal user could input his data like in preformated Excel table without having to deal with the database mechanics.

Such tools exist, but aren't popular because they're frustrating. Relational databases are much more rigid than flat files. Columns have datatypes which must be enforced, there are foreign keys, some (combination of) columns may have uniqueness constraints, and so on.

All this adds up to a bunch of errors when you're trying to input and manipulate data, because what you've entered is violating some rule someplace in the database.

It would be awesome if someone could build an interface that gives users the flexibility of Excel, and communicated the issues with the data they're trying to write in a really intuitive way. (e.g. syntax highlighting for data with issues, mouseovers / tooltips, dropdowns & autocompletes for foreign keys, etc)

Re: The Tyranny of Spreadsheets

#246

Post by rrrrrrrrrrrryan » Fri, Jul 23, 2021, 7:20 PM UTC

rvba wrote: post #59

markus_zhang wrote: post #23

In my last company we keep a spreadsheet that has tabs occupying almost the maximun number of columns allowed and in different headers. Updating that Excel file needs the full team to work for two full days each month. That was after I created a VBA script to scrape data from internal dashboards and put them directly in the spreadsheet. From my experience these monstrosities are usually created by two reasons: Push f…

Maximum number of columns is 16 384 (in modern Excel). PostreSQL has a limit of 16 000. So Excel > PostrGRE? :) I wonder what kind of data did you use and why it had to be stored in so many columns. This approach would probably kill a "real" database too.

The reason why you often encounter spreadsheets with so many columns is because spreadsheets really, really want data to be flat. Analysts often like to have all the possible datasets pre-joined into one monstrous sheet, then they can easily slice and dice it up however they want.

With a relational database this would be kind of insane, as it's much easier to normalize data into separate tables, then just join up them up when you need to.

Re: The Tyranny of Spreadsheets

#247

Post by rrrrrrrrrrrryan » Fri, Jul 23, 2021, 7:30 PM UTC

beefield wrote: post #184

max46 wrote: post #143

I think I big mistake people make here is that they think they are far better at everything on a computer than a non-programmer. They don't realize how much you can do in Excel and how much they suck at it. If you can't use it without a mouse and/or if you don't know pivot tables, you are just as much of a beginner as a C++ programmer who doesn't know what a pointer is. It's only after trying to convert an excel shee…

Opposing anecdote: I have converted excel spreadsheets built over years to SQL, and typically it did not take weeks to do that. ( Any organization that has a smallest reason to care about their data should remove save button from excel and start educating their personnel. Using excel in any important role should be seen as making the eventual mistakes on purpose and someone should be kept responsible.)

Getting rid of lousy Excel-driven processes is a big part of my current job. SQL only solves half the problem, though: Excel is also a tool for manual data-input and manipulation. To solve this piece you often need to create CRUD webapps, which can be much more complex to develop and maintain than some SQL in the database.

Re: The Tyranny of Spreadsheets

#248

Post by benhurmarcel » Fri, Jul 23, 2021, 7:30 PM UTC

beefield wrote: post #244

benhurmarcel wrote: post #233

Earlier quoted context omitted.

> I have converted excel spreadsheets built over years to SQL, and typically it did not take weeks to do that. The "years" of work are typically not for implementing the spreadsheet, but to define what it does. The requirements evolve over many versions. Of course it's fast to re-implement it after all the work to define the requirement is done, but it's only possible because Excel allowed all those prototypes.

Grandparent claimed that something done in excel by one person in a week takes a team of five and half a year to reimplement in some other language. I disagree with that as a general rule (of course, exceptions may apply, as usual). I fully agree that Excel is great for quick drafting, visualizing data quickly, and prototyping. But it should be left there. Anything you do that lasts even overnight and has any signifi…

Fair enough, the issues for me are that: once something works in a spreadsheet there are no money assigned and willpower to redo it clean in something else, because most of the value is already there; and the people who are available and know the requirements aren’t skilled in anything else.

So prototypes are done in Excel because it’s the fastest and cheapest way to do it, and they don’t get redone in something else afterwards for the reasons above.

Re: The Tyranny of Spreadsheets

#249

Post by LordEthano » Fri, Jul 23, 2021, 8:02 PM UTC

Closi wrote: post #155

codesnik wrote: post #90

Earlier quoted context omitted.

> they can see my workings How? Do they check that every cell in a column actually has the same formula? Do they check data format everywhere? Python script is something that is possible to be actually reviewed, and results - reproduced, and "formula's" there are actually readable. But for some stupid reason excel files are still shared over email.

> Do they check that every cell in a column actually has the same formula? They just have to check the top cell as most of my formulas are array formulas. You don’t have to drag a formula down - that’s a common misconception in the latest versions. If you do =A1 + B1 and want to apply it to the 1000 cells below you just write = A1:A1000 + B2:B1000. That’s still not that readable though, so I’ll apply those cells two…

That's terrible practice, ironically, as it's extremely unreadable. How could someone looking at it know what Sales and Tax actually are? You have to go into the formula name box and dig in to find sales = "yada yada" etc. That doesn't seem too bad until you have a decently sized file and you have to dig into 40 formulas to find the one you want, and go check that it's actually referencing what you want.

I work as a banker, and what you do is one of the very first things new employees are taught not to do in excel. It's an amazing solution for the person that built it, but a terrible one for anyone looking to check the work.

Re: The Tyranny of Spreadsheets

#250

Post by tomnipotent » Fri, Jul 23, 2021, 8:06 PM UTC

beefield wrote: post #244

benhurmarcel wrote: post #233

Earlier quoted context omitted.

> I have converted excel spreadsheets built over years to SQL, and typically it did not take weeks to do that. The "years" of work are typically not for implementing the spreadsheet, but to define what it does. The requirements evolve over many versions. Of course it's fast to re-implement it after all the work to define the requirement is done, but it's only possible because Excel allowed all those prototypes.

Grandparent claimed that something done in excel by one person in a week takes a team of five and half a year to reimplement in some other language. I disagree with that as a general rule (of course, exceptions may apply, as usual). I fully agree that Excel is great for quick drafting, visualizing data quickly, and prototyping. But it should be left there. Anything you do that lasts even overnight and has any signifi…

> done in excel by one person in a week

Done by a domain expert in their field. If you had to create the project from scratch without aid of an existing Excel file to clone, you would not be able to do it even remotely as quickly as the domain expert, if at all.