Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

I was wrong about spreadsheets (2017)

reifyworks.com

Post reply on HN
Print view View original

301–310 of 378 posts

Re: I was wrong about spreadsheets (2017)

#301

Post by dsjoerg »

PaulHoule wrote: post #282
eej71 wrote: post #218

Earlier quoted context omitted.

I think it will be easier to solve those problems in the Excel development space than it will be to export the Excel solutions to another solution domain. Also the kind of mistakes you've described can (and do) happen in other programming domains that are deemed more respectable.

Unfortunately no. Excel is hamstrung by having to maintain backwards compatibility for an endless number of hacks. For instance, it is notorious that 0.1 + 0.2 != 0.3 in binary exponent floating point math. Excel does funny stuff with number formatting that hides this, but it is like having a bubble under a plastic sheet that moves someplace else when you push on it -- numeric strangeness appears in different places.…

GP was about how Excel lacks "access control", "auditing", "change tracking". Then OP says it'll be easier to add those things to Excel than to make non-Excel suit the purpose.

Then you say no, backwards compatibility problems. But how does Excel's need for backward compatibility make it hard to add "access control", "auditing", or "change tracking"?

Re: I was wrong about spreadsheets (2017)

#302

Post by hjk05 »

Your strengt is your weakness is your strength. Spreadsheets excel at ad-hoc things because they aren’t systems, they don’t have restrictions and limitations on what data you can pull in or where you store it or what you calculate. They are horrible for all the same reasons, people end up spending tons of time semi manually joining up data from different sheets and copy pasting numbers from emails into calculations. The sheets take on a life of their own as they are emailed around between people, and errors like crop up: “oh, I think you have the version of the sheet that doesn’t work in October[real example], that’s got fixed in a version that was sent to Peter, check the mail stream to see who the original was sent to..” And they grow over time without any obvious way of optimizing old dependent calculations so you end up with sheets that do company wide economic calculations but take 10min just to open, so IT ends up setting up batchjobs to open it everyday, copy in new data and generate PDF of results[also real example].

Of cause you can just put systems and rules around your spreadsheet practices and start adding passwords and accompanying documentation of “what you must do when using this document”. But When you get into these situations it’s almost always a better decision to set up an actual system to handle your data.

Re: I was wrong about spreadsheets (2017)

#303

Post by tvanantwerp »

brootstrap wrote: post #262
ubermonkey wrote: post #259

There's nothing WRONG with the spreadsheet model, and never has been. It's immensely powerful and useful, and the growth in capability over the life of a product like Excel is remarkable and enviable. Excel is perhaps Microsoft's greatest product. The problem with it -- and there IS a problem -- is really a problem of applicability. Excel, like Lotus before it, is the first place many people encounter the ability to…

Yeah i agree with you. Excel is very powerful to some degree but like all things it can be abused. somewhere in a cube farm i can hear the shouts... "Hey Karen did you get the latest budget sheet?" "Is it budget_final_v2_2019.xlsx?" "Damnit Karen that is from last week dear god dont tell me you sent that to corporate!! We are on budget_final_FINAL_v4.xlsx"

I've been showing a team of economists working on some economic modeling how to use git and R. They were amazed that they could keep working on a file without creating backup copies, or worrying about team mates overwriting their work. It's a work in progress, but they are eager to learn and understand the value there.

Re: I was wrong about spreadsheets (2017)

#305

Post by halfeatenpie »

ozim wrote: post #207
halfeatenpie wrote: post #26

I work in critical infrastructure planning. My organization builds software in R, Python, and other programming languages customized for these major organizations. So many critical infrastructures, billions of dollars in planning, and just systems are built out of Excel. It's amazing. You'd assume something that services millions of people a day would have some more sophisticated and customized solution, but you're w…

Yeah it works and you don't need support contracts and then: - Fidelity's "Minus Sign Mistake": loss of $1.3 billion - TransAlta "Clerical Error": loss of $24 million - Fannie Mae "Honest mistake": loss of $1.3 billion Then you get employee turn over where new employees don't get "arcane" knowledge passed down by people who left and took their spreadsheet foo with them. Excel does not have "access control", "auditing…

See that's the thing.

You don't need Access Control because it's just an excel file and whoever has the file has the planning model. In most organizations like this, there's only like 5 or 8 people who all work together on a team, so it's not like 50-some people who are separated. Excel is a tool, a tool that you use to get things done. I mean even programming solutions have issues such as Knight Capital Group's $460 million loss due to mistakes in their code and deployment processes. Planning modules does not mean Operational modules. It's a big difference/jump between the two.

Again, these are all tools. What's important is how you use it and how do you properly validate these calculations. the thing you also need to understand is that no critical infrastructure planning is perfectly to-the-dot numbers. Our systems are far too complex, built off of human operational decisions, and have so many unknown losses that planning modules are based on scenarios. Also we're talking about thousands of these agencies around the world and their critical infrastructure planning teams are mostly 5 to 8 people. The current agency I'm working with has hired around 3000 employees to operate the systems but only 5 or so are really in-the-deep running these models and building these plans.

These people are not programmers. They spent their time learning about resource planning, mathematics, operation theory, physics, and engineering. Excel is an incredibly user-friendly tool that lets them automate a tremendous amount of their tasks. They're all very intelligent and can definitely learn how to code, but that takes their time away from more critical skills and tasks they need to accomplish.

The most important solution to the problems you've stated is the workflow. You setup a proper workflow, and risks of these problems should be minimized.

Re: I was wrong about spreadsheets (2017)

#306

Post by Ntrails »

mcphage wrote: post #281
navigatesol wrote: post #260

Earlier quoted context omitted.

> Then you get employee turn over where new employees don't get "arcane" knowledge passed down by people who left and took their spreadsheet foo with them. Yeah, this never happens with a proprietary codebase!

Who needs historical knowledge when you can just rewrite the program every few years in a different environment with all new bugs? (and a smattering of the old ones, too)

Few work things have bemused me more in the last couple of years than watching someone re-implement a slow SQL process in an HDFS big data platform forgetting all of the things that went wrong the first time, fixing them again, and then running into weeks worth of new bugs just to get not time critical data faster.

Re: I was wrong about spreadsheets (2017)

#307

Post by halfeatenpie »

ekianjo wrote: post #241
halfeatenpie wrote: post #26

I work in critical infrastructure planning. My organization builds software in R, Python, and other programming languages customized for these major organizations. So many critical infrastructures, billions of dollars in planning, and just systems are built out of Excel. It's amazing. You'd assume something that services millions of people a day would have some more sophisticated and customized solution, but you're w…

> I have deep respect for Excel. I do something around the same line as you describe (build better tools in R/Python for larger data problems) but I have a deep aversion to Excel. It's proprietary, has horrible standards (it does not support native UTF8 csv files for example), and is stuck in the 80's in terms of paradigm. And this is precisely the tool that prevents people from doing things more efficiently because…

Excel is a tool. It empowers those who don't know how to code to do their jobs more effectively than before without "learning to code". It's something you keep in your garage in case you need it. It depends on what their current workflow is. Sometimes I'll use Excel, sometimes I'll use R/Python.

What's important is that Excel gives you access to the computing power of modern technology with a lower barrier-of-entry/knowledge. That's one of the most valuable things that many people fail to understand. Not everyone is a programmer and for a lack of a better term, "doesn't really care about UTF-8 and just want the calculations to work". Also, in many cases Enterprise IT in critical infrastructures are resistant to supporting R or Python due to their security models (firewall rules prevents install.packages('tidyverse') for example).

This is what makes us specialists and someone they bring on board to help them out and explain to them about what the "best-use-case" is, and then watch as people try to cram a square peg into a round hole.

Re: I was wrong about spreadsheets (2017)

#308

Post by jon-wood »

viraptor wrote: post #21

My issue with spreadsheets is that they could be improved a lot with minimal changes, not nobody seems to do it in popular office packages. Making them more database-like and making table data first-class (at least you can make named tables in excel on windows) could be used to push people a bit more towards organised data, without changing how anything works. Half of the mess that makes excel hell comes from the fac…

It had its problems, but Apple's Numbers product got this right I feel - it was a kind of blend between desktop publishing and spreadsheets, where you could scatter arbitrary tables of data around a page and then have them refer to each other. I suspect a lot of people who are throwing multiple tables of data in the same sheet are doing so because they want both of them to be visible at once.

Re: I was wrong about spreadsheets (2017)

#309

Post by PaulHoule »

dsjoerg wrote: post #301
PaulHoule wrote: post #282

Earlier quoted context omitted.

Unfortunately no. Excel is hamstrung by having to maintain backwards compatibility for an endless number of hacks. For instance, it is notorious that 0.1 + 0.2 != 0.3 in binary exponent floating point math. Excel does funny stuff with number formatting that hides this, but it is like having a bubble under a plastic sheet that moves someplace else when you push on it -- numeric strangeness appears in different places.…

GP was about how Excel lacks "access control", "auditing", "change tracking". Then OP says it'll be easier to add those things to Excel than to make non-Excel suit the purpose. Then you say no, backwards compatibility problems. But how does Excel's need for backward compatibility make it hard to add "access control", "auditing", or "change tracking"?

"access control" by definition means "make things not work some of the time"

Excel has change tracking, but like Jupyter notebooks and similar products it doesn't make the clean distinction between code and data that is necessary for it to be useful. (e.g. if I develop an analysis pipeline and use it for May 2019 it should be as easy as falling off a log to run it for June 2019)

Re: I was wrong about spreadsheets (2017)

#310

Post by acdanger »

ozim wrote: post #207
halfeatenpie wrote: post #26

I work in critical infrastructure planning. My organization builds software in R, Python, and other programming languages customized for these major organizations. So many critical infrastructures, billions of dollars in planning, and just systems are built out of Excel. It's amazing. You'd assume something that services millions of people a day would have some more sophisticated and customized solution, but you're w…

Yeah it works and you don't need support contracts and then: - Fidelity's "Minus Sign Mistake": loss of $1.3 billion - TransAlta "Clerical Error": loss of $24 million - Fannie Mae "Honest mistake": loss of $1.3 billion Then you get employee turn over where new employees don't get "arcane" knowledge passed down by people who left and took their spreadsheet foo with them. Excel does not have "access control", "auditing…

For reference on the the above errors: https://www.cio.com/article/2438188/eight-of-the-worst-sprea...
Post reply on HN