Excel blamed for multibillion dollar financial losses.

[StuartBruff]

Interesting article in the New Statesman about the dangers of Excel, highlighting some of the problems with Excel workbooks, particularly it would seem the difficulty of validating them and the poor review processes that the products ubiquity and apparent simplicity can engender ...

http://www.newstatesman.com/technology/2013/02/excel-most-dangerous-piece-software-world

Base article ...

http://baselinescenario.com/2013/02/09/the-importance-of-excel/

The issue is described in the appendix to JPMorgan’s internal investigative task force’s report. To summarize: JPMorgan’s Chief Investment Office needed a new value-at-risk (VaR) model for the synthetic credit portfolio (the one that blew up) and assigned a quantitative whiz (“a London-based quantitative expert, mathematician and model developer” who previously worked at a company that built analytical models) to create it. The new model “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 internal Model Review Group identified this problem as well as a few others, but approved the model, while saying that it should be automated and another significant flaw should be fixed.** After the London Whale trade blew up, the Model Review Group discovered that the model had not been automated and found several other errors. Most spectacularly,

“After subtracting the old rate from the new rate, the spreadsheet divided by their sum instead of their average, as the modeler had intended. This error likely had the effect of muting volatility by a factor of two and of lowering the VaR . . .”

Discussion ...

http://news.ycombinator.com/item?id=5198187

Some of these factors have raised their heads in Mathcad discussions, particularly the cutting and pasting of data and equations (often in the context of symbolic evaluations). It also supports the argument that Mathcad's greater transparency encourages better quality reviews whilst still providing the ease of use that makes Excel so popular (along with the fact that "everybody" has it!)

[RichardJ]

Interesting article(s).

It's a good thing no engineer would be dumb enough to use Excel to design something really critical. Such as bridges, or even lithium ion battery systems for aircraft. At least, I don't think they would....

[MichaelH]

Some of the most egregious assaults on my fragile sanity have come from "bean counters" emailing me and scores of others some poorly organized multi-columned, multi-tabbed Excel file with hundreds or sometimes thousans of rows, with lengthy, confusing instructions on how enter the appropriate data in the correct cells and send it back to them.

Some even like to show-off their virtuosity in Excel by including cleverly crafted macros that crash when you attempt to run them on your PC.

[TomD.inPDX]

Those are the same bean counters that pass along MS Project files and tell you to cut the schedule in half.

Are you sure we don't work for the same company?

Great article, Stuart!

[StuartBruff]

Antonius Dirriwachter wrote:

Those are the same bean counters that pass along MS Project files and tell you to cut the schedule in half.

Are you sure we don't work for the same company?

I think they have a collective hive mind, plus they know engineers love a challenge!

Great article, Stuart!

Glad to share it!

Stuart

[StuartBruff]

Indeed, many a fine instance of that, I've come across.

But 'tis not just the bean counters. Another of my war stories involves the "Intimidation by Numbers" effect that Excel can have and shows up clearly the delights of cutting and pasting formulas. A certain large international company had selected a numeric integrator to use in a complex dynamic simulator. To support their choice they presented a large Excel workbook with a group of ODEs of increasing complexity being solved by several different integrators. Looking at it when I joined the program, I was slightly puzzled by the choice as one of the rejected choices was slightly less complex, slightly more accurate and quicker. I then looked at the worksheet in detail and, after a few moments of bafflement, wondered why nobody seemed to have noticed that the RK4 integrator was performing worse than the simple Euler. Yep, you guessed it - errors in editing the copied formulas, cut and paste using absolute references to the original sheet and insertion of new rows without amending the relative references in the formulas. I wrote a critique of the Excel workbook and attached a Mathcad worksheet doing the same thing but much more clearly. I did have some successes in persuading people not to use Excel for tasks inappropriate to its nature, but I'm not awfully of beating my head against brick walls.

(BTW, I actually quite like Excel and in it's place, with good review processes to support it, it is a very capable tool. I understand the attractions of its simplicity and its good formatting capabilities. But it is a classic example of everything looking like a nail when all you've got is a hammer.

... actually, it would be fairer to say "all you know how to use is a hammer and you're a bit suspicious of them there new fangled screwdrivers".

Do not get me started on MS Word, though. I am firmly convinced that it has a built-in IQ tester and is specifically designed to torment anyone with a PhD. I have seen normally unflappable engineers almost throw their PCs out the window when Word trashes their opus magnum or they spend hours trying to work out why the formatting changes at some point in their document. For some reason, it doesn't like me either. )

[MichaelH]

But it is a classic example of everything looking like a nail when all you've got is a hammer.

Oh. So you've seen the poster above my desk then.

"If your only tool is a hammer, every problem looks like a nail."

[StuartBruff]

Richard Jackson wrote:

Interesting article(s).

It's a good thing no engineer would be dumb enough to use Excel to design something really critical. Such as bridges, or even lithium ion battery systems for aircraft. At least, I don't think they would....

Every now and again, Emperor Murphy likes to whisper in people's ears and convince them that something that is blindingly stupid is a really good idea. He likes to operate through project management as they are so, what's the word I'm looking for ..? Ah, yes, gullible.

He says things like "Yes, I know it looks easy and it clearly wouldn't take an amoeba more than 2 seconds to work out what's going on and how to change a value. I also know that it is wonderfully self-documenting and you just saw a week-long Excel project compressed into 20 minutes but, trust me, it's too complex for your simple-minded engineers to get to grips with. Stick to Excel, it's what you they know and the £1000 you save on the capital budget, and pittance on training, will more than compensate for the extra months of schedule and cost that you would save by using Mathcad".

I think I've narrated before the case of an engineer who approached me for assistance with a nice problem he had involving several interesting integrals and data table generation. I solved his problem in 5 minutes and then spent 10 minutes making it look pretty. He almost fell off his chair in shock at the simplicity and power of Mathcad. Later he almost popped a blood vessel in frustration that, despite the obvious savings in schedule and cost and the substantially reduced technical risk at the design and review stages, his program insisted that he continue to use Excel on budgetary grounds!!!!!!!!

Sadly, this is not a unique situation. In another instance, somebody dashed off a one-time use only Mathcad sheet to verify one of their requirements. They were told to rewrite it in Excel because:

a) Mathcad wasn't a company standard tool (it was, which is why they had it on their work PC!) and

b) it would be too difficult for subsequent engineers to comprehend and modify (despite the fact that it was an ad hoc, one-off worksheet that would not need modifying, that it was easy enough for the PM to follow and that it's calculations were visible and simple to verify).

Come the Revolution, that wall's going to have to be re-built several times ...

Stuart

[RichardJ]

Come the Revolution, that wall's going to have to be re-built several times ...

Right on brother! After the revolution we will force all the accountants to do their books in Mathcad! Ha Ha Ha!

[RichardJ]

What is going on here? Whe I try to insert an image from the web it shows up fine in the editor, but it converts to a URL when I actually post!

[RichardJ]

That one worked as an image, but it's not animated.

Great. It's something to do with the source, the Animated gifs blog: http://communities.ptc.com/blogs/SomeGifs

If I insert the image Stuart used it works fine. I guess that makes the blog now totally worthless

[Werner_E]

I never had succes in inserting graphics (screenshots) from the clipboard directly but had to save them and insert them using the appropriate icon/function in the editor. Wehn I insert from the clipboard it seems to work but only until I save.

Next gif is directly inserted from the clipboard

While I am editing I see the pic and its animated.

The next one was saved and inserted via "insert pic"

Of course it works in the editor incl. animation

Now lets post an we'll see.

Edited: Interesting - I supposed the first one to disappear, but after sending I see both (and both are not animated).

[RichardJ]

Pasting a link to the gifs in the blog used to work fine. It's the reason I created the blog. Strangely, the old posts seem to work OK. If you look at the comments in the blog, the gifs are animated. They are inserted as links to the images in the blog.

[RichardJ]

You can't just paste them in from the clipboard. As you note, that does not work. Overall, the image handling of these forums is poor, and it appears it just got worse in some way.

[Werner_E]

Richard Jackson schrieb:

You can't just paste them in from the clipboard. As you note, that does not work. Overall, the image handling of these forums is poor, and it appears it just got worse in some way.

That could possibly be, but it seems to me that the source does not matter.

When I look at the forst comments to your blog I notice, that the inserted pictures link to the "original". In the recent posts it seems that the picture is copied to a new location which is then linked to.

The next one was inserted via insert pic/from the web and does not work at all

For the picture in your blog I get

Image address: http://communities.ptc.com/servlet/JiveServlet/downloadImage/38-1286-6327/51-28/drinks.gif

Link address: http://communities.ptc.com/servlet/JiveServlet/showImage/38-1286-6327/drinks.gif

For the picture (the first) in this post:

Image address: http://communities.ptc.com/servlet/JiveServlet/downloadImage/2-198067-45966/51-28/drinks%5B2%5D.gif

Link address: http://communities.ptc.com/servlet/JiveServlet/showImage/2-198067-45966/drinks%5B2%5D.gif

[RichardJ]

If I hover over the original "drinks" image in the blog I see this URL:

http://communities.ptc.com/servlet/JiveServlet/showImage/38-1286-6327/drinks.gif

If I hover over the drink image in the comments, I see the exact same URL. If I inset an image as a link using that URL I see an animated image in the editor:

And then hover over the image in the post the URL is exactly as I inserted it, and the image is animated.

[StuartBruff]

Richard Jackson wrote:

You can't just paste them in from the clipboard. As you note, that does not work. Overall, the image handling of these forums is poor, and it appears it just got worse in some way.

Does this mean we're going to have to build another wall?

I'd better get logistics onto making sure we've got enough ammo.