Replying to a Message

REvolution Computing

Forum Navigation:

login

FORUMS > General Forum

Replying to Topic: Spreadsheet Errors
Created On Mon Aug 16, 04 03:55 PM by GrenvilleCroll

GrenvilleCroll
Senior Member

Posts: 312
Joined: Jul 2004

Mon Aug 16, 04 03:55 PM

User is offline

For those of you using MS Excel in a serious way, you might like to take a look at the website of the European Spreadsheet Risks Interest Group (ww.eusprig.org). We have had a successful five year series of annual international conferences looking at ways of quantifying, identifying, detecting, correcting and preventing etc the various ways of messing up big time with Excel. Having established the basic arguments with, I believe, some credibility, we are looking to the future and would appreciate some feedback from the Quant community.

Also, though I have been involved with spreadsheets since 1984 - I worked for Lotus then went on to design and publish the Bond Yield Calculations for Lotus 1-2-3 which was used in the City for about a decade - I have relatively less experience of how Excel is used in the City/Quant environment of today. If anyone could paint a picture of how Excel is used nowadays, it would be appreciated.

I have been invited to attend the Foundations of Spreadsheets conference in Rome at the end of September - any feedback that I could contribute there would be appeciated.

-------------------------
There is no underlying physical reality, only number

DavidJN
Senior Member

Posts: 1115
Joined: Jul 2002

Mon Aug 16, 04 05:10 PM

User is offline

Put simply and succinctly, despite the higher operational risk, Excel is everywhere - it is the primary front-line tool of analysis in the financial business. Most traders price deals in spreadsheets and enter them in large-scale deal capture systems afterwards. If you use the search facility on this website, you will find a considerable amount of information shared on how to link proprietary analytics (often written in C++, but also other languages) to Excel. Most trading system vendors offer linkages to Excel via APIs or custom toolkits.

DominicConnor
Senior Member

Posts: 10751
Joined: Jul 2002

Tue Aug 17, 04 01:24 PM

User is offline

The risk site identifies a screwup using a spreadsheet, however as DavidJN righly says, Excel is utterly pervasive. Nothing large (good or bad) happens without it passing at some time though Excel.

Grenville outbids me on time fighting spreadsheets, I only got involved in spreadsheets when my gang of fellow IBM FVers got to fix Excel 4 for MS in the late 80s. At various points in my life, the mortgage has been paid by doing hard things to bad spreadsheets, so I'm not sure if I welcome your initiative

I note the conference is short on speakers from the user community, of course vendors and academics are always happier to speak.

My $0.02 is that the known unreliability of spreadsheets is itself a key reason why so few major screwups happen. Most people don't take Excel output as completely trusted.
Unlike closed loop systems, Excel allows users to spot and fix errors themselves, thus any number of spreadsheets are "wrong" at any point in time.
Another advantage is that you can structure a spreadsheet to show it's working. Thus a user can trace back a suspect result to its source. A closed system gives you a result which you have to trust.

Being easily adaptable, a spreadsheet can cope with the random crap that occurs in the real world that you have to deal with now, and wasn't thought of when the system was designed. As people increase their outsourcing I see Excel use increasing because users can't wait three months to negotiate development with the outsourcer and for a 2nd rater to polish an ancient Oracle DB.

Being a semi-open system a major spreadsheet also educates its users, many now have been longer in their position than the people using them.
Thus a newbie to the business area can poke around and learn how things happen.

I can see how this mught upset anyone with an audit background since there is simply no way to formalise this stuff without making it less reliable.
It is a balancing act between flexible enough to cope with the crap in the real world.

What is missing from al lthis is education. Most people "just pick up" Excel. This is both good and bad. The bad is that they are never taught the important issues of structure, data integrity, versioning, etc. They learn expensive lessons, costing their employers non-trivial money. Worse, the lessons are often learned by the poor soul who inherits an undocumented unstructured workbook as a critical part of his job.

-------------------------
Discussion on the new regulations on bonuses here.

spursfan
Senior Member

Posts: 725
Joined: Oct 2001

Tue Aug 17, 04 02:11 PM

User is offline

here's my couple of thoughts

like you, i've been using spreadsheets for nigh on two decades - in more recent times though i've been converted to predominantly using spreadsheets for prototyping and then coding up VBA user-defined functions - so, to my mind, the complex spreadsheet auditing beloved of accountants is completely the wrong route to follow

our book, advanced modelling in finance using excel and vba, reflects my view of how excel should be used and that i knew nothing of vba functions before writing the book (we use macros only to automate solver and the chart wizard); so i'd be advocating using excel as a front-end with real programs and program libraries behind (and i'm continue to be surprised at how much can be done in VBA by writing efficient code)

terrorbyte
Senior Member

Posts: 247
Joined: Jul 2002

Tue Aug 17, 04 03:23 PM

User is offline

Excel being used as a bidding system => 24 million dollar mistake

Trans Alta

DominicConnor
Senior Member

Posts: 10751
Joined: Jul 2002

Tue Aug 17, 04 04:30 PM

User is offline

Yes we all have a laugh at that one.

In my opinion, a vastly larger sum is lost by mildly broken sheets. A good example is where a price hasn't updated. This is harder to spot because of course the older one is still quite plausible, or where you've slightly mis aligned an column of price calcs with their static data.

-------------------------
Discussion on the new regulations on bonuses here.

patch22
Senior Member

Posts: 286
Joined: May 2002

Wed Aug 18, 04 08:21 AM

User is offline

the part of that story that amuses me is the statement that the management are "calling in an outside party to make its own assessment". It never ceases to amaze the lengths that management will go to for the apperance of covering their arses when the reality is usually that they should just listen to their own people directly.

In my experience it is the accountants (middle/back office) who give Excel a bad name. The sort of spreadsheets these guys knock up are basicly huge databases with numerous inter-sheet and inter-workbook linkages, pivot tables off of these, minal use of vba (except maybe something that?s been "recorded" to filter or print, and database formulas across massive ranges - that sooner or later fail.

Very quickly this sort of thing becomes a part of their daily process but begins to demand more and more repetitive efforts to keep such things as ranges and data updated. Hence some simple but enormous errors that often go days or weeks unfound. It gets to a point that when someone moves on the new guy has to "learn" the spreadsheet. And obviously there is no good way to comment worksheet formulas in the way that code can be.

The way to use Excel is as a highly flexible GUI with lots of space for the power user to play and enrich until from time to time the developer consolidates all worthy enrichments into the standard build. Data should rightfully be in a database such that the sheet is only loading from or saving to this, and analytics should rightfully be provided as VBA or C++ function calls.

More and more spreadsheet replacement projects are hitting the problem that the smarter users preferred the Excel system such that Excel is almost forced to remain the GUI, no matter how much techie stuff is built behind it.

DominicConnor
Senior Member

Posts: 10751
Joined: Jul 2002

Wed Aug 18, 04 10:51 AM

User is offline

And obviously there is no good way to comment worksheet formulas in the way that code can be.

It certainly requires you to work a little harder.
However, you can insert comments to cells to describe their formulae.
Most people don't bother using meaningful names, in fact I would bet good money that >80% of people who write formulae don't even know that you can write profit as

=Units*(Price-Cost)

Rather than
=Sheet4!EG10*(Sheet102!BF109-Sheet16!IG9)

There is nothing to stop you putting text in your spreadsheet to describe how it works. Honest, really I've checked

Looking at many sheets out there, you'd assume that there was someone in compliance at each bank whose job it was to delete any useful comments from worksheets.

-------------------------
Discussion on the new regulations on bonuses here.

Doney
Member

Posts: 43
Joined: Jun 2004

Wed Aug 18, 04 02:16 PM

User is offline

Ugh I remember the day I first learnt the Alt-Enter trick for formulae, and the F9 for partial evaluation..

Clopinette
Senior Member

Posts: 256
Joined: Feb 2002

Wed Aug 18, 04 02:45 PM

User is offline

I think Excel is often used to do more than it can handle:

You don't have a clear control on the order of calculations. And when the spreadsheet gets big sometimes they don't even happen !

-------------------------
Keep your mind open, but not so open that your brain falls out

Maelo
Senior Member

Posts: 1120
Joined: Jul 2002

Thu Aug 19, 04 07:00 AM

User is offline

My little experience with Excel:

it solver is an excellent tool for teaching optimization;

however, I found out some sort of glitch whenever integer programming is required (its solution is not always "optimum" IF IT OFFERs A SOLUTION AT ALL. ). I have observed that in several occacsions what could be done with a simple manual check (for integer programming) cannot obtain a solution.

I think once I tumble against a website warnigng about this,but can't remeber where it was, anyone knows?

GrenvilleCroll
Senior Member

Posts: 312
Joined: Jul 2004

Fri Aug 20, 04 12:44 PM

User is offline

Thanks for all the input. I hadnt realised just how pervasive Excel is in this commmunity. Also interesting and somewhat reassuring is that Excel is used here by professional software engineers, who, like I, subscribe to the norms of that honourable profession. There remains an impression however, that much is still hacked up on the fly, and EuSpRIG would welcome further input from this community at our next conference (probably to be held in Greenwich, London in July 2005). Management summaries are welcomed, and there have been a number from a variety of user communities. Note that one of the speakers at the last conference was Dean Bruckner of the Financial Services Authority (!)

The previous post is a little hard to understand but I think our friend is saying that when solving an integer problem with the Built-in Solver, he doesn't receive an optimal solution. When solving an integer model, you must set the Integer tolerance setting to 0 in Tools/Solver/Options. The default is currently 5%. This will ensure that, if a solution exists to the MIP, Solver will find the optimal solution.
Also, there was bug in the Excel 2000 Solver which would give "Solver can not find a feasible solution" when it did, in fact, find an answer. I believe this bug only occurred on integer problems. The bug was fixed in Office XP.

-------------------------
There is no underlying physical reality, only number

tristanreid
Senior Member

Posts: 441
Joined: May 2004

Fri Aug 20, 04 07:19 PM

User is offline

One thing that would be helpful is a way to 'dump' a workbook into a fully descriptive format. I mean something like a reference to all the formulae, the code, and named ranges. Does such a thing exist?

-t.

-------------------------
I haven't a particle of confidence in a man who has no redeeming petty vices.
- Mark Twain, a Biography

Posts: 64
Joined: Jul 2002

Mon Aug 23, 04 11:56 AM

User is offline

Has anyone looked into pricing in operational risk specific to spreadsheets?

i.e. if a customer wants to do a bespoke trade that cannot be handled in our designated booking system then we have to book it in a spreadsheet and add a few bips to cover operational risk capital charge (and additional maintenance). how many bips to add is subjective.

this should have desired effect since increasing op risk spread will make us less competitive and so decisions about investment on more sophisticated booking systems can be made more rationally.

DominicConnor
Senior Member

Posts: 10751
Joined: Jul 2002

Tue Aug 24, 04 10:25 AM

User is offline

One thing that would be helpful is a way to 'dump' a workbook into a fully descriptive format. I mean something like a reference to all the formulae, the code, and named ranges. Does such a thing exist?
Not that I know of...

I have however used some database stuff for sorting out large, really broken workbooks.

Databases have solved many of these issues before. The first and most obvious difference is that the structure of the data is a separate thing from the data itself.
A database will also allow you specify higher level controls like "all the itms in this list must be different", or "all the stocks in this list must be members of the 'allowed' list", or simply that the ISIN codes have the right format.

Excel also doesn't do views all that well. A database will elt you look at a data table ordered by one of the columns, or give rather better filters than those in Excel.

-------------------------
Discussion on the new regulations on bonuses here.

secondMan
Senior Member

Posts: 362
Joined: Aug 2003

Tue Aug 24, 04 11:52 AM

User is offline

it does not fit in here, but i must get rid of it. i found excel 95 the best. since then the program did basically not improve.
i do not understand why spreadsheets can basically not be bigger today, though machine capacity multiplied. and i do
not understand why there is no more database features in there (besides the business aspect of not jeopardising access).
and who the hell did all this "improvements" in the diagrams?

ok. yes. i am fine. i behave myself.

peace

-------------------------
do it now.

tristanreid
Senior Member

Posts: 441
Joined: May 2004

Tue Aug 24, 04 04:37 PM

User is offline

Yeah, giving Excel some of the features that you see in any database would really be helpful. Being able to assign constraints on a range, or to have one range be a view of another, would help a lot. And being able to query a system table to find out the characteristics of a sheet, workbook, etc!
Actually, that's probably not too hard. Something that walks the major objects and dumps info about them. But what i'd love most is something that preserves information about references, so that you could find the named ranges and also find how many/which formulae reference them.

-t.

-------------------------
I haven't a particle of confidence in a man who has no redeeming petty vices.
- Mark Twain, a Biography

MichaelA
Member

Posts: 52
Joined: Oct 2001

Tue Aug 24, 04 07:33 PM

User is offline

This probably sounds obvious, but to eliminate the problems of bugs creeping into Excel spreadsheets, does anyone else have a system of peer review of spreadsheets prior to their being released ?
When I moved jobs 3 months ago, I thought it was a bit of a waste of time (how many people admit to making mistakes in their code ?), but a peer review has this remarkable knack of picking out screw-ups.

tristanreid
Senior Member

Posts: 441
Joined: May 2004

Tue Aug 24, 04 08:41 PM

User is offline

Yeah, peer review is great. So is a design process before the work begins. Having any type of process in place is a great and wonderful thing. The problem is that most of these things start with someone who has great analytic skill but is only moderately competent at excel, then the project grows, and several ideas are all controlled by the same workbook, someone introduces a hack to keep a history of the results, the decision-making logic gets all entangled with the reporting logic, and eventually someone new to the group gets handed the thing and told 'make this work'.

it makes me sad

-t.

-------------------------
I haven't a particle of confidence in a man who has no redeeming petty vices.
- Mark Twain, a Biography

DominicConnor
Senior Member

Posts: 10751
Joined: Jul 2002

Wed Aug 25, 04 12:23 PM

User is offline

Yeah, giving Excel some of the features that you see in any database would really be helpful....
Yeah, but would people really pay for such a tool ?
The prevalent view is that Excel is an "easy" tool, except of course when it goes wrong...
Since it only costs a few hundred, and corporate users get it for "free", I don't expect they'd pay 500 for a tool that did views, constraints, integrity checks, and a rather deeper form of auditing than you currently get.

The other thing I'd add is type information. Declare something of type FX Rate or "Probability" and it will complain if you use onewhere you'd specified another.
Types would have information such as "sane" ranges (catch numbers too large) and "legal" ranges (probabilities that are outside 0..1)
Insane ranges complain, and illegal ranges halt with an error.

A good suite of this kind would have these

-------------------------
Discussion on the new regulations on bonuses here.

FORUMS > General Forum

Forum Navigation:

© All material, including contents and design, copyright Wilmott Electronic Media Limited - FuseTalk 4.01 © 1999-2010 FuseTalk Inc.