5 ms·

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

by ozim 7y ago

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", "change tracking". Just getting work done is not enough.

tajd 7y ago
Or tests suites for checking formulae or how the said formulae are applied across the spreadsheet?
eej71 7y ago
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.
- PaulHoule 7y ago
  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. The right answer is to go to decimal exponent floating point math, but that is only HW accelerated on IBM Mainframes, maybe on RISC-V at some point. You'll probably crash Excel if you have enough numbers in it for performance to matter, but Microsoft would be afraid of any performance regression and it would break people's sheets so it won't happen. On a technical basis we could use an Excel replacement that has some characteristics of Excel, and other characteristics of programming languages; one old software package to look to for inspiration is https://en.wikipedia.org/wiki/TK_Solver https://en.wikipedia.org/wiki/TK_Solver What makes it almost impossible to do on a marketing basis is that Excel is bundled into Microsoft Office so if you have an Office subscription you have Word, Powerpoint, Excel, Access, etc.
  - telegato 7y ago
    To be fair in Javascript 0.1 + 0.2 === 0.3 also is false :). Not saying that Javascript is a great programming language.
    - PaulHoule 7y ago
      It's a problem in all mainstream programming languages that Excel has a strange answer to. The trouble with it is that it is a big distraction to the "non-professional programmer" who uses tools like Excel.
    - thfuran 7y ago
      Yeah, that's the case for anything using IEEE 754 floating points.
  - dsjoerg 7y ago
    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"?
    - PaulHoule 7y ago
      "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)
  - Vaslo 7y ago
    Why would anyone working with amounts in the millions or billions care about 0.1 + 0.2 != 0.3? Splitting hairs here I think.
    - 7y ago
mbesto 7y ago
> - 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 Weigh that against <insert any company> makes $X Billion due to correct usage of Excel multiplied thousands and thousands of times over. Survivorship bias at it's finest. Excel isn't perfect and has notable downsides, but work gets done all of the time on them for the aforementioned reasons.
- swiley 7y ago
  I’m not convinced there’s a such thing as “correct usage of excel.” What a lot of companies do is just scare entry level employees into being very careful. My friend’s girlfriend works at a place like this and people just accept all the problems with manually editing large spreadsheets because “that’s just life.” Anything in excel is a hack and most of its users don’t know any better.
  - behringer 7y ago
    What's your solution? That the manager who wanted some report comes to you and begs you to do it and in 6 months it's finally delivered and all wrong?
    - jvanderbot 7y ago
      The sweet spot where I work is online forms that pre-populate excel playgrounds and allow download. Then, you can tweak till your heart's content.
    - kowdermeister 7y ago
      I can't not remember now the Salesforce for developers article :)
    - michaelmrose 7y ago
      If you regularly produce reports one would suppose that you would have an existing project template and know how that would allow a reasonable individual to deliver a result that isn't all wrong in a reasonable length of time not THAT dissimilar from the time to throw up a spreadsheet. This also wouldn't silo the data in the users laptop, subject it to being destroyed because the user dropped it, and limit frequency of updates and access to how fast the user can respond to emails.
navigatesol 7y ago
>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!
- mcphage 7y ago
  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)
  - Ntrails 7y ago
    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.
inputcoffee 7y ago
The Fidelity "minus sign mistake" didn't create a loss. The mistake was in relaying the information to the end user. It didn't actually cause a loss of that magnitude. That is like saying if I mistyped in a word doc, that word created the loss.
halfeatenpie 7y ago
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.
acdanger 7y ago
For reference on the the above errors: https://www.cio.com/article/2438188/eight-of-the-worst-spreadsheet-blunders.html https://www.cio.com/article/2438188/eight-of-the-worst-sprea...
yellowstuff 7y ago
Fidelity didn't lose $1.3B, they miscalculated by $1.3B. The same with the other numbers you quote. Spreadsheets are error prone, but they still make sense for a lot of use cases. https://www.cio.com/article/2438188/eight-of-the-worst-spreadsheet-blunders.html https://www.cio.com/article/2438188/eight-of-the-worst-sprea...
HeavyStorm 7y ago
Ahn, so sorry, but that's a very flawed argument. Arcane knowledge will be an issue with magical spreadsheets, as it is with any software (and I could argue that with custom software you have this legacy knowledge over both code AND ui, which is worse than just a spreadsheet). The last paragraph is key, though - auditing and access control are tough ones for excel, and there are many tasks that require this functionality. The trade-off is customizability - any excel can be changed to your specific need whereas that nice and shiny enterprise software you build may lack one or two things which you will never be able to change.
schergr 7y ago
Uh... You have the same problem with Cobol written 20 years ago without comments.
thirdsun 7y ago
> Excel does not have "access control", "auditing", "change tracking". Just getting work done is not enough. Doesn’t the web version of Excel already solve that? I’m not familiar with it but Google Sheets does these things, at least to some extent - I’d expect Office 365 to do so as well.

by ozim 7y ago

tajd 7y ago

Or tests suites for checking formulae or how the said formulae are applied across the spreadsheet?

eej71 7y ago

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.

PaulHoule 7y ago
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. The right answer is to go to decimal exponent floating point math, but that is only HW accelerated on IBM Mainframes, maybe on RISC-V at some point. You'll probably crash Excel if you have enough numbers in it for performance to matter, but Microsoft would be afraid of any performance regression and it would break people's sheets so it won't happen. On a technical basis we could use an Excel replacement that has some characteristics of Excel, and other characteristics of programming languages; one old software package to look to for inspiration is https://en.wikipedia.org/wiki/TK_Solver https://en.wikipedia.org/wiki/TK_Solver What makes it almost impossible to do on a marketing basis is that Excel is bundled into Microsoft Office so if you have an Office subscription you have Word, Powerpoint, Excel, Access, etc.
- telegato 7y ago
  To be fair in Javascript 0.1 + 0.2 === 0.3 also is false :). Not saying that Javascript is a great programming language.
  - PaulHoule 7y ago
    It's a problem in all mainstream programming languages that Excel has a strange answer to. The trouble with it is that it is a big distraction to the "non-professional programmer" who uses tools like Excel.
  - thfuran 7y ago
    Yeah, that's the case for anything using IEEE 754 floating points.
- dsjoerg 7y ago
  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"?
  - PaulHoule 7y ago
    "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)
- Vaslo 7y ago
  Why would anyone working with amounts in the millions or billions care about 0.1 + 0.2 != 0.3? Splitting hairs here I think.
  - 7y ago

mbesto 7y ago

> - 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 Weigh that against <insert any company> makes $X Billion due to correct usage of Excel multiplied thousands and thousands of times over. Survivorship bias at it's finest. Excel isn't perfect and has notable downsides, but work gets done all of the time on them for the aforementioned reasons.

swiley 7y ago
I’m not convinced there’s a such thing as “correct usage of excel.” What a lot of companies do is just scare entry level employees into being very careful. My friend’s girlfriend works at a place like this and people just accept all the problems with manually editing large spreadsheets because “that’s just life.” Anything in excel is a hack and most of its users don’t know any better.
- behringer 7y ago
  What's your solution? That the manager who wanted some report comes to you and begs you to do it and in 6 months it's finally delivered and all wrong?
  - jvanderbot 7y ago
    The sweet spot where I work is online forms that pre-populate excel playgrounds and allow download. Then, you can tweak till your heart's content.
  - kowdermeister 7y ago
    I can't not remember now the Salesforce for developers article :)
  - michaelmrose 7y ago
    If you regularly produce reports one would suppose that you would have an existing project template and know how that would allow a reasonable individual to deliver a result that isn't all wrong in a reasonable length of time not THAT dissimilar from the time to throw up a spreadsheet. This also wouldn't silo the data in the users laptop, subject it to being destroyed because the user dropped it, and limit frequency of updates and access to how fast the user can respond to emails.

navigatesol 7y ago

>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!

mcphage 7y ago
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)
- Ntrails 7y ago
  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.

inputcoffee 7y ago

The Fidelity "minus sign mistake" didn't create a loss. The mistake was in relaying the information to the end user. It didn't actually cause a loss of that magnitude. That is like saying if I mistyped in a word doc, that word created the loss.

halfeatenpie 7y ago

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.

acdanger 7y ago

For reference on the the above errors: https://www.cio.com/article/2438188/eight-of-the-worst-spreadsheet-blunders.html https://www.cio.com/article/2438188/eight-of-the-worst-sprea...

yellowstuff 7y ago

Fidelity didn't lose $1.3B, they miscalculated by $1.3B. The same with the other numbers you quote. Spreadsheets are error prone, but they still make sense for a lot of use cases. https://www.cio.com/article/2438188/eight-of-the-worst-spreadsheet-blunders.html https://www.cio.com/article/2438188/eight-of-the-worst-sprea...

HeavyStorm 7y ago

Ahn, so sorry, but that's a very flawed argument. Arcane knowledge will be an issue with magical spreadsheets, as it is with any software (and I could argue that with custom software you have this legacy knowledge over both code AND ui, which is worse than just a spreadsheet). The last paragraph is key, though - auditing and access control are tough ones for excel, and there are many tasks that require this functionality. The trade-off is customizability - any excel can be changed to your specific need whereas that nice and shiny enterprise software you build may lack one or two things which you will never be able to change.

schergr 7y ago

Uh... You have the same problem with Cobol written 20 years ago without comments.

thirdsun 7y ago

> Excel does not have "access control", "auditing", "change tracking". Just getting work done is not enough. Doesn’t the web version of Excel already solve that? I’m not familiar with it but Google Sheets does these things, at least to some extent - I’d expect Office 365 to do so as well.