Is Excel the most dangerous software in the world?

[kunkel321]

I'm a major Excel nut, (and someone who lost a load of equity in his home since 2008) so I found this interesting. 
An excerpt from the book: Dashboards for Excel (Jordan Goldmeier & Purnachandra Duggirala)

No, really, it’s possible that Microsoft’s Excel is the most dangerous software on the planet.
      —Tim Worstall in Forbes Magazine
Worstall is referring to JPMorgan’s use of Microsoft Excel and its potential contribution to the global recession that began in 2008. The story is a familiar one. JPMorgan, one of the big banks, required a new way for their chief investment officer to model the risk associated with their portfolio of collateralized debt obligations (CDOs) . CDOs, as you might recall, are those complex financial instruments that are comprised of a bundle of mortgages used to speculate and manage the risk for default. In the end, many homeowners eventually defaulted on their mortgages. Yet, JPMorgan’s model had not captured this at all. According to an internal report by JPMorgan, their model contained an error that “likely had the effect of muting volatility by a factor of two and of lowering the VaR [value at risk].” What had happened? How could one of the biggest banks employ a model that was so absent from reality? According to the report, their model was “operated through a series of Excel spreadsheets, which had to be completed manually, by a process of copying and pasting data from one spreadsheet to another.” The error was generated when “After subtracting the old rate from the new rate, the spreadsheet divided by their sum instead of their average, as the modeler had intended.” Worstall, like many others, blames Excel. It’s too insecure, and there’s no audit trail, he contends. Its flexibility is its greatest strength and Achilles heel. His argument isn’t a new one, of course. Without proper internal governance and procedures regarding archiving, getting lost in a nightmare of error-ridden spreadsheets and mixed-up versions is a real possibility. But is that really what happened in the case of JPMorgan? Are more audit controls needed? I’m not so sure.

[Target]

I'm reminded of that old truism about a poor tradesman always blaming his tools

[superboyac]

It's an interesting statement.  What I understand from this and from what I've experienced in my career, excel is used in a very versatile way in the computer world.  It's a very powerful application, very common (possibly the most common application in the world).  If you spend any time using a computer for a living, you probably use excel.  I've seen it used in crazy ways, from prototyping applications (using the grid to draw stuff), to hardcore database-like applications that should be done on access or something built for that.  Excel hits that sweet spot as the go-to tool for anything.

Now, I've also seen these financial guys use excel in their stuff too, and it's crazy.  Like i said, it should be done in a proper database.  And a lot of people just do it in excel.  There's a joke that it's called "excel-gineering".  And auditing an excel document is a nightmare if something goes wrong.  Finding that wrong formula, wrong cell, wrong link, etc. is a terrible excercise.  And if important things are built into it, yea, i can see it being considered a dangerous tool in a very round-about, abstract, indirect kind of way.



But in that sense, the internet-connected-computer is the most powerful weapon ever created.  or is it the h-bomb?

[Renegade]

I'm pretty sure "Excel" = 666 in some kind of numerology.

[eleman]

I'm reminded of that old truism about a poor tradesman always blaming his tools

-Target (November 11, 2015, 06:44 PM)

Yeah, right. Excel should be the real culprit, rather than the greed and leeching zillions out of the economy without actually producing anything.

[anandcoral]

And add 'Copy-Paste' to it and you have a nuclear bomb under you table ticking till Boss finds out.

After that BOOM !

[IainB]

Is Excel the most dangerous software in the world?
Well, whilst calling it the most dangerous software might be stretching it a bit, it could certainly be argued from a historical perspective that they had the potential to be "dangerous" and sometimes had actually proven to be so in fact.
My experience has been that Excel and Access have generally been the de facto financial black holes for a great deal of labour costs, where often non-IT personnel had spent many hours inventively developing one-off or prototype spreadsheet models and access database applications as business solutions to do often quite clever and useful things that simply could not have been done (at the time) in a timely or cost-effective manner using IT otherwise. Often these one-off solutions were tweaked to the stage where they could become so useful that they were commercially indispensable, and might even form a large part of the core legacy solutions for an enterprise.

A great strength of Excel and Access lies in their "tweakability" by the user - the extent of user control. That is also their Achilles Heel - there is a profound scope for human error in developing the logic and formulae in the models/applications, and that is why, from the perspective of stochastic and accounting accuracy, the models need to be independently audited and verified, but certainty in that regard (that all errors have been identified and fixed) is something that was and remains notoriously extremely difficult to achieve - despite the growth of things like spreadsheet auditing tools.

However, having worked on complex models such as ITEM (the UK Independent Treasury Econometric Model) and climate models of the North Sea, I would recommend skepticism towards any plea of incompetence - the "Oh dear, there was an error in the [insert name of scam] financial model" argument. These people aren't necessarily the fools they might have us believe.
For example, in "JPMorgan’s model had not captured this at all...".
Yeah, right.

[Stoic Joker]

I'm reminded of that old truism about a poor tradesman always blaming his tools

-Target (November 11, 2015, 06:44 PM)

+5 - Me too! 

[kunkel321]

Some good replies here.  It is indeed possible that JPMorgan's top people were blaming Excel for their own poor choices.  Even if the goof did happen in the way they say, it is still the fault of the tradesman, and not the tool (to use Target's words).  Also, I had always assumed that there were many corporations that had a hand in this.  This is the first time I've read about a particular company being the "straw that broke the camel's back." 

Anyway, I will also attest to how difficult auditing an old spreadsheet can be.  I don't use Excel for finances, but for timelines and due dates on projects mostly.  I'll make these incredibly complex setups.  Usually they will continue working indefinitely--Excel is quite robust.  Later though, when I want to add a feature to my setup, I have to figure out how I designed darn the thing.  Usually I, essentially, end up going over the entire process of building it again.. .   

Here's the above-quoted book, btw: https://it-ebooks.info/book/6446/

[superboyac]

Is Excel the most dangerous software in the world?
Well, whilst calling it the most dangerous software might be stretching it a bit, it could certainly be argued from a historical perspective that they had the potential to be "dangerous" and sometimes had actually proven to be so in fact.
My experience has been that Excel and Access have generally been the de facto financial black holes for a great deal of labour costs, where often non-IT personnel had spent many hours inventively developing one-off or prototype spreadsheet models and access database applications as business solutions to do often quite clever and useful things that simply could not have been done (at the time) in a timely or cost-effective manner using IT otherwise. Often these one-off solutions were tweaked to the stage where they could become so useful that they were commercially indispensable, and might even form a large part of the core legacy solutions for an enterprise.

A great strength of Excel and Access lies in their "tweakability" by the user - the extent of user control. That is also their Achilles Heel - there is a profound scope for human error in developing the logic and formulae in the models/applications, and that is why, from the perspective of stochastic and accounting accuracy, the models need to be independently audited and verified, but certainty in that regard (that all errors have been identified and fixed) is something that was and remains notoriously extremely difficult to achieve - despite the growth of things like spreadsheet auditing tools.

However, having worked on complex models such as ITEM (the UK Independent Treasury Econometric Model) and climate models of the North Sea, I would recommend skepticism towards any plea of incompetence - the "Oh dear, there was an error in the [insert name of scam] financial model" argument. These people aren't necessarily the fools they might have us believe.
For example, in "JPMorgan’s model had not captured this at all...".
Yeah, right.

-IainB (November 12, 2015, 05:03 AM)

yes!  the last bit is so true!

[Shades]

Is Excel the most dangerous software in the world?
Well, whilst calling it the most dangerous software might be stretching it a bit, it could certainly be argued from a historical perspective that they had the potential to be "dangerous" and sometimes had actually proven to be so in fact.
My experience has been that Excel and Access have generally been the de facto financial black holes for a great deal of labour costs, where often non-IT personnel had spent many hours inventively developing one-off or prototype spreadsheet models and access database applications as business solutions to do often quite clever and useful things that simply could not have been done (at the time) in a timely or cost-effective manner using IT otherwise. Often these one-off solutions were tweaked to the stage where they could become so useful that they were commercially indispensable, and might even form a large part of the core legacy solutions for an enterprise.

A great strength of Excel and Access lies in their "tweakability" by the user - the extent of user control. That is also their Achilles Heel - there is a profound scope for human error in developing the logic and formulae in the models/applications, and that is why, from the perspective of stochastic and accounting accuracy, the models need to be independently audited and verified, but certainty in that regard (that all errors have been identified and fixed) is something that was and remains notoriously extremely difficult to achieve - despite the growth of things like spreadsheet auditing tools.

However, having worked on complex models such as ITEM (the UK Independent Treasury Econometric Model) and climate models of the North Sea, I would recommend skepticism towards any plea of incompetence - the "Oh dear, there was an error in the [insert name of scam] financial model" argument. These people aren't necessarily the fools they might have us believe.
For example, in "JPMorgan’s model had not captured this at all...".
Yeah, right.

-IainB (November 12, 2015, 05:03 AM)

I completely agree with your last paragraph.

**** rant alert ****
Sounds like you never had the "pleasure" of turning a solution created by such an "inventive" person into a system that actually scales. While those tools might provide a solution for a small company, never think that these work on a bigger scale like MS wants you to believe these tools do. Then again, I am of the opinion that whoever (or whomever) made Access should be taken behind the shed to be dealt with. That alone would be a huge improvement in any gene pool.

Those "inventive" people are allowed to make very(!!!) bad methods of handling whatever data/calculations they are involved with. Methods that would have been ridiculed by any IT pro. Every choice you make when using or creating software has significant consequences later on. Think of the "inventive" person as a butcher and the IT pro as a surgeon. You state that they can do the same job, just because both know how to carve meat?

Excel is also such a "success" story. Most people use it by only having one instance open. In this way I really can't blame you for mistaking it to be a useful tool. However, I work with software that really has to shift lots of data and using this data to calculate/generate much more data. There are many ways to get data into and out of this system. Excel is one of those. Customers use consultants that have the same fondness for Excel as most people seem to have.

All is still nice and dandy at this point. Unfortunately, consultants hardly work together, especially when they are not working in the same area of expertise. Because of the size of the customers and the myriad of disciplines they are active in, they use lots of consultants. The software I work with runs on any version of a Windows PC. From a workstation/single server to big beasts. One customer actually has a Windows system with 64 (multi-core) processors.

This customer was happy using our software system as it performed better as expected, while shifting their data and calculating/generating data. And then came the consultants with their Excel based input. Suddenly that beast of a system ran slower than a 486 PC by taking the input of all that Excel based input at the same time. It doesn't matter how much "horsepower" or RAM you put into a Windows based server...just run 20 instances of Excel at the same time. Doesn't matter which version of Excel or how complicated the Excel file being opened by that Excel instance is. Just open 20 instances.

Excel consumes a shameful amount of resources that messes up the way how Windows handles the available resources. Before I could prove to that customer that Excel was the only software to blame, levels of stress were at unhealthy levels to say the least.

My point is that for serious work Excel and Access are not alternatives and should not even be considered as such by anyone with a mind for IT. Now I fully understand that my comments won't change the opinion of most people and that they keep seeing Excel as the benevolent Dr. Jekyll. While IT pro's, which do need to work with lots of data, see Excel for its true nature, a brutally butchering Mr. Hyde.

Let's just say that it requires a lot of surgical skill to fix the butchering, no matter how "inventive" the butcher thinks he/she is.

[Renegade]

^ For Excel connected to a database, maybe opening that many instances might be a problem. I don't do that, so I don't know that case.

But I regularly have 5, 10, 20, 50 instances of Excel running, and I don't have that many problems.

Yes, I do sometimes run into the memory error, but it's not very common. I can usually solve that by closing other applications, with the main culprits being Internet browsers, e.g. Chrome, Firefox, Opera, etc.

Just my own experience.

[IainB]

I completely agree with your last paragraph.
**** rant alert ****
Sounds like you never had the "pleasure" of turning a solution created by such an "inventive" person into a system that actually scales. ...
____________________________________

-Shades (November 14, 2015, 09:17 AM)

Sorry for misleading you, but I did actually have that "pleasure" many times, and the use of "inventive" was a deliberate euphemism for "[expletive deleted]" - so I can resonate with your rant.
The most polite thing I could say about them in polite company was that they were "inventive", but I would also use that term as it was often quite a politically sensitive matter too, in that management had invariably at some point been responsible for mistakenly allowing these people to wreak their peculiar form of havoc on the company's information systems, so there was a lot of egg-on-face type of concern (no-one wants to get fired for making a genuine mistake in ignorance of the potential risks of that sort of mistake).

In one case, I recall that I was called in as a consultant in the role of project director to help at a company where they did indeed have around their necks the proverbial millstone - a legacy of these kinds of "inventive" solutions - mostly Access databases, and some Excel spreadsheets, and none of them documented, of course. A project had been belatedly set up to address the tremendous mess that had resulted and that now needed to be cleared up. It took quite a while, for which I was paid handsomely, so I couldn't really complain.        $$$

I'd happily sweep the streets if I was paid enough.

[TaoPhoenix]

I think what I'm missing here is that "on my better days" I was trained for my couple diff variants of accounting that every now and then you do a "sanity check" - a very fast and ugly-dirty approximate second calculation just to look for these kinds of random weirdness, plus intuition.

So if something begins to feel off, you smash out a "piece of paper and calculator guess" just to see if the results sorta make sense.

Maybe that's beyond the human mind at the big bank level, but "just believing" forever feels risky. So Excel itself isn't exactly the most dangerous software, it's just so generically useful in so many ways and the problem could be the operators maybe stretching the limits of what a mind can intuitively hold.

[IainB]

I think what I'm missing here is that "on my better days" I was trained for my couple diff variants of accounting that every now and then you do a "sanity check" - a very fast and ugly-dirty approximate second calculation just to look for these kinds of random weirdness, plus intuition.
So if something begins to feel off, you smash out a "piece of paper and calculator guess" just to see if the results sorta make sense.
Maybe that's beyond the human mind at the big bank level, but "just believing" forever feels risky. So Excel itself isn't exactly the most dangerous software, it's just so generically useful in so many ways and the problem could be the operators maybe stretching the limits of what a mind can intuitively hold.
________________________________

-TaoPhoenix (November 15, 2015, 10:50 AM)

Yes, I reckon that you are absolutely right about the sanity checking bit - some spreadsheet "developers" don't even understand the need for or benefit of doing simple cross-casts of column totals in financial spreadsheets.
The worst offenders I have met in that regard ("Why would you need to prove your totals?") were mathematicians who apparently had never heard of that other mathematician - Fra Luca Bartolomeo de Pacioli ("the father of accounting and bookkeeping"). For years, all my spreadsheets have been built to self-check their totals, and to compare other totals where a difference would signify that there has been an error somewhere, and report on same in a dashboard.

However, humans are essentially error-prone (trial-and-error being our fundamental way of learning, after all), and our errors can be notoriously very difficult to identify and locate within spreadsheets - needle-in-a-haystack kind of thing - so they can often remain undiscovered, like a ticking time-bomb. I recall the NZ Treasury reportedly made a huge error in its announced budget forecast several years back, a main total apparently being out by a factor of 10, or something, due to a simple spreadsheet error. This was despite them having had a team of some of their best minds peer-reviewing the forecasting model and its results.

By the way, I would suggest that there's probably no need to have a mind that can "intuitively hold" all of a spreadsheet model, if the thing has been systematically built using good theory and "best" practice and with lots of internal checks and reports on same. If a spreadsheet could not be broken down into logical components, with each able to be checked independently, I would be very skeptical of its output, and if only (say) one man ("George" the developer) understood what the model was doing, I'd be even more skeptical.
As in science, mathematical models need to be accessible to see if they pass an independent test of falsifiability.

[Renegade]

Sigh... and go figure... I had 23 instances of Excel open today, and it decided to crawl... Some were blocked files, but either way - it sucks.