[HN Gopher] Spreadsheet Horror Stories
___________________________________________________________________
Spreadsheet Horror Stories
Author : tosh
Score : 119 points
Date : 2020-02-27 07:56 UTC (1 days ago)
(HTM) web link (www.eusprig.org)
(TXT) w3m dump (www.eusprig.org)
| qubex wrote:
| Released in 2018, so needs the relevant tag (I think).
| dang wrote:
| They've been doing this for many years, and I think it's an
| ongoing effort.
| amadeuspzs wrote:
| Not that the authors would claim any differently, but I am going
| to go out on a limb and state that RDBMS Horror Stories are more
| frequent, and with higher overall impact.
|
| Purely from a SQLi point of view we have:
| https://codecurmudgeon.com/wp/sql-injection-hall-of-shame/
| commandlinefan wrote:
| Or programming in general horror stories:
| http://thedailywtf.com/
| blackbrokkoli wrote:
| Great examples of the fallacy Don Norman points out in "The
| design of everyday things":
|
| Assigning "human error" and declaring the analysis done, not
| considering design may be at fault.
|
| Just Ctrl+F the keywords, the exact phrase is used three times
| and paraphrased a dozen time more. My favorite is "It was
| basically human error... there's nothing wrong with our
| accounting systems". If your spreadsheet has billion-dollar
| impact, why is there no 4-eyes-principle? Why do humans even have
| a hand in data transfer? No sanity checks? No automation? Stop
| blaming the user!
| Ididntdothis wrote:
| It seems a lot of these come down to the fact that people don't
| do a final sanity check. I have seen it quite a few times where
| people threw out numbers in meetings that simply didn't make
| sense but weren't challenged.
| denster wrote:
| We've seen this in customer deployments as well [1], that's why
| we always recommend UIs linked to a spreadsheet that help with
| verification & correctness of the underlying spreadsheet logic.
|
| [1] Implementations of MintData spreadsheets for internal
| tooling & line of business applications.
|
| https://mintdata.com
| brundolf wrote:
| I wonder if "spreadsheet unit tests" are/should be a thing.
| [deleted]
| dfsegoat wrote:
| SQL based unit testing exists. So why not i guess?
|
| http://dbunit.sourceforge.net/intro.html
| denster wrote:
| They are a thing [1]. We use MintData spreadsheets for a large
| part of our regression test suite that tests MintData itself.
|
| A bit meta, but yes, spreadsheets can definitely be used for
| the equivalent of what "unit tests" are in code.
|
| Interestingly, we also have "integration tests" in our
| spreadsheets, but this has more to do with the fact that
| MintData spreadsheets have native API calling ability, so we
| can test with external services end-to-end.
|
| [1] MintData, https://mintdata.com
| iFire wrote:
| Is there an intro plan that isn't $95 / month? I want to pay
| money for this, but there are alternatives at that pricing
| level.
|
| Actually even paying $1140 ($95 * 12 months) for the on-
| premise version is better.
| iFire wrote:
| I had a lot of insights generated from this opensource
| program.
|
| https://medium.com/guesstimate-blog/introducing-
| guesstimate-...
|
| https://github.com/getguesstimate/guesstimate-app
| thedudeabides5 wrote:
| Say what you will about errors in spreadsheets but an
| underappreciated benefit is that they _enable_ this kind of post-
| hoc analysis /error checking.
|
| The transparency of 'it's all in this workbook and you can trace
| it yourself' means finding mistakes (and there are always going
| to be mistakes) and finding the logic behind the conclusions
| (because there is always going to be debate about methodology and
| cleaning practices) is a million times easier than if the
| analysis was done in code.
|
| It may be the case that you get more errors per hour in a
| spreadsheet than in code, but I'd bet 5:1 that errors in code
| based systems persist much longer, due to the lack of
| transparency in what the machine is thinking.
| bobbylarrybobby wrote:
| How is a spreadsheet easier to audit than a codebase?
| mxschumacher wrote:
| All data states and all logic are in one file
| joe_the_user wrote:
| Like a lot of things, I think you have two part here. A
| small spread can be more understandable than a small
| program because you have all the logic apparently visible.
| A large spreadsheet can become harder to understand and
| verify than the medium-sized program that could duplicate
| it's logic.
| cpeterso wrote:
| But spreadsheet logic is buried unseen in cells and
| spreadsheets don't have good version control for reviewing
| new changes and auditing change history.
| thedudeabides5 wrote:
| Yes, but to do good version control on code based systems
| you need to version both the code, and the data being run
| through the pipes.
|
| While there are lots of good tools to version code (git
| etc), and lots of tools to version control data
| (bitemporality being the usual requirement), does anyone
| know a tool which allows you to do both, at the same
| time?
| jkaptur wrote:
| I've thought a lot about this. It's certainly easy to dunk on
| spreadsheets being error-prone (and even easier to dunk on VBA!).
| It's a decades-long running joke:
| https://dilbert.com/strip/2007-08-08
|
| But at a higher level, what strikes me is that spreadsheets don't
| get checked if they tell you what you want to hear. These stories
| are generally about afflicting the afflicted (Reinhart and
| Rogoff) and banks and traders taking on too much global risk in
| pursuit of local reward (the London Whale).
|
| This is a problem with the intersection of software and society
| in general, and I don't have an answer.
| alexhutcheson wrote:
| "There are only two kinds of languages: the ones people complain
| about and the ones nobody uses."[1] That applies to spreadsheets
| as well as to C++.
|
| [1] http://www.stroustrup.com/bs_faq.html#really-say-that
| nerpderp82 wrote:
| > In a paper, 'Does High Public Debt Consistently Stifle Economic
| Growth? A Critique of Reinhart and Rogoff,' Thomas Herndon,
| Michael Ash, and Robert Pollin of the University of
| Massachusetts, Amherst criticise a 2010 paper by Harvard
| economists Carmen Reinhart and Kenneth Rogoff, 'Growth in a Time
| of Debt.' They find three main issues: First, Reinhart and Rogoff
| selectively exclude years of high debt and average growth.
| Second, they use a debatable method to weight the countries.
| Third, there also appears to be a coding error that excludes
| high-debt and average-growth countries. All three bias in favor
| of their result, and without them you don't get their
| controversial result."
|
| This one is kinda buried in the list. This is what motivated the
| harsh austerity measures in Greece. So an entire country was
| punished because of a bug in a spreadsheet.
|
| http://theconversation.com/the-reinhart-rogoff-error-or-how-...
|
| https://prospect.org/culture/books/the-crash-of-austerity-ec...
|
| https://www.theguardian.com/business/ng-interactive/2015/apr...
|
| https://www.thestranger.com/slog/archives/2013/04/17/how-mic...
|
| http://www.cc.com/video-clips/dcyvro/the-colbert-report-aust...
|
| interview with herndon http://www.cc.com/video-clips/kbgnf0/the-
| colbert-report-aust...
| mech1234 wrote:
| Proper response to this:
|
| https://www.ft.com/content/01fc06b8-fb6e-3e36-acb0-a1f8b47a7...
|
| It remains true that high debt ratios hamper growth.
| Ma8ee wrote:
| No. Your link is behind a paywall, but I followed the debate
| during they years, and their results are quite thoroughly
| debunked.
| zoonosis wrote:
| The article addresses the issues raised by OP. You don't
| need to read the paywalled article since it just summarizes
| the statement by Reinhart & Rogoff which is available here
| [1]. Here is an archive of the actual article if you would
| prefer to read that [2].
|
| [1] https://scholar.harvard.edu/files/rogoff/files/response
| _to_h...
|
| [2] https://archive.md/2daXH
| jessaustin wrote:
| It doesn't look good for Reinhart and Rogoff, but if it hadn't
| been their paper it would have been something else. The
| political will existed in Europe to subject Greece to extreme
| measures; it was going to happen. If Germany hadn't found this
| scapegoat they would have found some other one.
|
| And people claim to wonder why Greece is friendly with China...
| Mvandenbergh wrote:
| >This one is kinda buried in the list. This is what motivated
| the harsh austerity measures in Greece. So an entire country
| was punished because of a bug in a spreadsheet.
|
| That isn't true. This paper indicated that moderately high
| levels of public debt constrained economic growth. As you have
| pointed out, the paper was riddled with errors and the evidence
| that moderately high levels of public debt slow economic growth
| isn't really there.
|
| What happened in Greece is quite different. Lenders lost
| confidence in the Greek government's ability to repay their
| debt. Partially because there was re-statement of public debt
| that increased the debt level by 11% overnight.
|
| What the Greek government did after that to get out from under
| this was to cut spending (austerity). Economists have always
| been sceptical that this would work since during a recession is
| the very worst time to cut spending since it reduces cumulative
| demand at a time when it is already down.
|
| Indeed it did not work as we all know, but Eurozone lenders
| were not willing to allow a partial default which is what
| should have happened and being in the Euro means that there is
| no possibility for a devaluation.
| bobcostas55 wrote:
| Greece consistently ran huge deficits during the crisis. They
| had a huge debt write-off in 2012 and still ended up with
| 180% debt/gdp, that doesn't happen if your spending policy is
| austere.
|
| https://3gp11q1ujq964apmpt3s9cda-wpengine.netdna-
| ssl.com/wp-...
| pjc50 wrote:
| Austerity is not measured in Euros but in the human
| casualties of the policy.
| https://en.wikipedia.org/wiki/Greek_government-
| debt_crisis#S...
| [deleted]
| hef19898 wrote:
| Well, basically the EU forced harsh measures on Greece so
| Greece could get money from the EU to repay debts to banks
| and other institutional investors. Investors didn't loose
| anything (over simplifying here), the EU got it's money back
| and Greece, along with Greeks, paid the bill. Would have been
| easier to just wire the money over to the investors directly
| and leave the Greeks alone.
|
| Investors understood the rescue of Greece exactly like that
| anyway if you ask me. But maybe the EU had to maintain the
| public impression of austerity. Or rather the Germans.
| jaclaz wrote:
| > So an entire country was punished because of a bug in a
| spreadsheet.
|
| Not really, the Authors of the spreadsheet made one or more
| errors, AND they did not double check results, NOR any of the
| top-level economists criticized the results.
|
| In other words an entire country was (severely) punished
| because of a wrong theory (based on erroneous data) that all
| the establishment accepted because it came from Harvard.
| brazzledazzle wrote:
| Sounds a lot like the theory was accepted without being
| verified because it stated what they (the ones pushing for
| austerity) wanted it to say. Not disagreeing with your list
| but maybe it's worth adding the politicians/decision makers
| involved to it.
| jaclaz wrote:
| Sure, by "establishment" I intended the politicians too.
|
| Still the main responsability sits on the economists and
| their peers.
|
| I mean, not that I want in any way to absolve the
| politicians, mind you, but what can they do - not being
| economists or more generally technically experts in this or
| that field - if not choosing the theories from people
| qualified by notorious universities?
|
| In my experience what matters in this is reputation (and
| network), which in itself is not "bad" in an absolute
| sense, but I see a lot of sloppy or downright "wrong" works
| (even if rarely with such severe consequences) coming from
| these people lately.
|
| It seems to me like there are not (or not enough) critical
| reviews/checkings/etc. in some (scientific or pseudo-
| scientific) circles.
| joe_the_user wrote:
| The broad theory is that austerity is necessary for growth
| - it's a serious theory even if I think it turned out to be
| wrong with terrible human consequences.
|
| The Reinhart and Rogoff paper was more like factoid of a
| theory - no complicated model, just a dubious claim that
| trips off the tongue - "Studies have shown that when debt
| reach X level, growth tanks"
|
| The thing about this is when the shoddy quality of the
| study became obvious, people could act _that_ was what
| drove all the horrible things coming under austerity, as if
| these weren 't the consensus of a large group, study-or-
| not-study.
| rscho wrote:
| Well, realistically many people are _dying_ from spreadsheet
| incompetence (also named "clinical research") every day, so...
| zoonosis wrote:
| Austerity is effective when it is achieved by cutting spending.
| The cases where it hasn't worked were because the spending cuts
| were accompanied with tax increases [1]. This shouldn't be a
| surprise because tax increases tend to decrease GDP [2].
|
| [1] A. Alesina, C. A. Favero, and F. Giavazzi, Austerity: when
| it works and when it doesnt. Princeton (New Jersey): Princeton
| University Press, 2019.
|
| [2] C. D. Romer and D. H. Romer, "The Macroeconomic Effects of
| Tax Changes: Estimates Based on a New Measure of Fiscal
| Shocks," American Economic Review, vol. 100, no. 3, pp.
| 763-801, 2010.
| andrewstuart wrote:
| Back in 1987 I worked at a company that used Microsoft Multiplan,
| which was an early spreadsheet program.
|
| It did not have a "sure you want to exit?" nor any form of
| intelligent saving.
|
| One of the guys worked on a spreadsheet all night without saving
| it along the way and hit exit, and it did.
|
| https://en.wikipedia.org/wiki/Multiplan
| IAmEveryone wrote:
| While I enjoy snarking at amateurs' mistakes as much as anybody,
| I believe it's an overlooked fact that nothing has been as
| successful in getting non-programmers to program, and in getting
| the benefits of programming out there, as spreadsheets have.
|
| Excel gets almost as much grieve as PowerPoint does. But I've met
| 80-year old booksellers running their own demand-forecasting
| spreadsheets, which were more sophisticated and accurate than
| quite a few ,,business intelligence" portals I've seen over the
| years. And isn't that awesome?
| hef19898 wrote:
| It's incredible what you can achieve with spreadsheets. Done
| well these spreadsheets are also a perfect basis for further
| digitalisation, simply because the underlying process is
| running smoothly.
| dredmorbius wrote:
| Ray Panko of the University of Hawaii has studied the problem
| (and prevalence) of spreadsheet errors for decades:
|
| https://www.researchgate.net/profile/Ray_Panko
|
| https://web.archive.org/web/20070617035246/http://panko.shid...
___________________________________________________________________
(page generated 2020-02-28 23:00 UTC)