Bad spreadsheet

Dennis made a good point on the last post about being fed up with people always blaming spreadsheets when there is an error. You know who

I totally agree. I can’t remember the last _spreadsheet_ error I saw*. Actually they are usually _user_ errors, either in the mechanics of spreadsheets, process of development or lack of domain knowledge, or whatever.

I blame Sarbox, Eusprig and auditors, in that order, for raising the awareness of spreadsheet risks, without giving due regard to the wider issue. The wider issue being poor controls of any software development will create risky, error prone systems.

At Eusprig 2007 I think the first 3 speakers (one was me, one was DB from the FSA (regulators of all UK financial services)) highlighted the increased risk from user developed Access databases.

The Eusprig stance is entirely reasonable – they are focused on spreadsheet risk (the clue is in the name). If someone wants to create another group to focus on the risks of end user databases, or other types of risk, Eusprig would be happy to help and collaborate. But in 10 years of Eusprig there is no sign of EuDBrig – possibly because the name is even less catchy?

Sarbox and auditors I feel are a little less reasonable in hounding spreadsheets and spreadsheet users. Partly I suspect this is because auditors think they can audit spreadsheets, but I bet very few of them have the first clue about databases (or code actually). ‘Proper’ systems get proper systems auditors with an IT background, not the fresh faced graduate accountant trainees found in financial auditing. (I have no idea which department is auditing the .net based financial reconciliation system I built for a recent finacial services client.)

Should we start a ‘spreadsheets are innocent’ campaign?

What sort of catchy slogans should we be painting on walls and pavements outside large auditing firms?

‘Excel is innocent – it woz Access wot done it’?

*Of course I can really – its the Excel 2007 calculation presentation bug – I coded a test proc here

cheers

Simon

Advertisements

5 Responses to “Bad spreadsheet”

  1. Rob Bruce Says:

    The example you cite is a classic ‘mixing formatting and data’ issue and not really a garbage-in-garbage-out problem. In that sense the problem is a spreadsheet one in that spreadsheets are where data and formatting tend to get combined more thoroughly and intricately than anywhere else. Only yesterday, for example, we had a question on EXCEL-L about counting cells in a range with particular formatting. I cringe whenever I see stuff like that because we all know the kinds of problems that it can lead to down the line.
    On the other hand, it’s not the grid’s fault per se that someone has plonked an unsustainable design on top of it, though you have to question whether there is something fundamentally wrong with a tool that lets the user screw up so badly, so easily.
    Rob

  2. Simon Says:

    Good point on the data/formatting Rob.
    Actually that Barclays mess was caused by failing to filter the ‘included’ column for Yes, hence leaving the Nos in too.

    If they had done the equivalent in a DB (missed a WHERE clause or missed the field from the SELECT) No one would be saying ‘These databases are error riddled danger zones’, they would just say hey ho operator error, time pressure, training, checking etc etc.

    I saw the same post and had the same reaction.

    I covered that is it the tool question in my Eusprig 2005 paper, well tried to. its here:
    http://www.codematic.net/files/Papers/SimonMurphyEusprig2005Paper.pdf.

    My view is they are much more fragile than other tools.

  3. Mike Staunton Says:

    The bad news about nearly all “miscarriages of justice” campaigns is that they take decades to pay off – but you’re right in that it’s always the messenger (spreadsheet) that gets the blame – and, as you touch on, here there are lots of people with vested interests offering their own solutions.

    In lots of what I do I’m relatively fortunate in that I’m building a model to match a given numerical answer from someone else’s paper and often will find errors in the printed mathematical text along the way – even so, I tend to adopt a build twice approach (once in Excel, once in a VBA function)

    For the rest of what I do where there is no answer to follow, I rely on experienced colleagues to either look at my spreadsheets or run some sensitivity analysis and see if my answers follow our intuition

  4. Dennis Wallentin Says:

    From my point of view (in Sweden) it’s obvious that all the big corporates in the BI-market fear Excel.

    Excel is a genuine low budget excellent interface for BI-reports and it’s available throughout most corporates (and MSFT is creating a new BI-reports tool that will have an Excel look on top of SQL Servers).

    It’s in the interest of their own business that BI-corporates blame Excel.

    Kind regards,
    Dennis

  5. Harlan Grove Says:

    Bad experiences have led me to avoid hidden rows and columns. Anything I don’t want users to see goes into hidden worksheets. I also avoid filters, pivot tables and merged cells like the plague.

    The problem with spreadsheets is that everyone has them but less than 5% have a modest understanding how to screw them up. Knowing how to screw them up is the first step in learning how NOT to screw them up. The other 95% of spreadsheet users are the real problem. No fixing that in no small part because it’s way, Way, WAY not in MSFT’s interest to fix the problem (give 75% an Excel VIEWER and 20% more training).

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s


%d bloggers like this: