Spreadsheet auditing (2 of many)

When I am looking at other peoples spreadsheets (well and my own actually) I like to use the audit arrows to see the dependents and precedents. I have used a few tools that create a separate chart of a dependency tree, but personally I like to see it all in context.

Trace dependencies arrow screen shot

I have a couple of rough tools in this area that I use myself but are way too rough to release (this goes back to a discussion on DDOE about proportion of effort to get the key things working, compared to the effort to polish a product to a standard that be won’t be an embarrassment on release).  For XLAnalyst I think the core code that was useful to me took about 1/20th of the effort involved in the final released version.

Going forward I’m thinking I’ll just release the most useful bits here as source code, with a bit of discussion, and if people express enough interest in a proper packaged supported version then that may follow. If other people have code or ideas or other skills or services (anyone *like* documentation?) they might like to contribute to an open source VBA spreadsheet quality/audit type tool then please leave a comment.

When I am clicking around a worksheet I often like the arrows to follow me. If you stick the following 2 lines in the Workbook_SheetSelectionChange event of ThisWorkbook, that will happen for that workbook.

sh.ClearArrows
Target.ShowDependents ' or ShowPrecedents (or both)

sh and target are parameters passed into that event for you.

If you want that to work universally, then you need to wrap it up in a class to catch the events at application level. I’ll post that later with a proper workbook you can download and use. If anyone wants it?

A couple of issues around this

  1. It breaks the clipboard, I think I ended up turning it off during build and only using it during review
  2. ShowPrecedents/Dependents will show all (in the current workbook – doesn’t really work for external links (but youre not using those anyway right?)
  3. The VBA range.Dependents or directDependents does not include off sheet references, making it somewhere between useless and dangerous.
  4. None of this stuff covers conditional formatting or validation type dependencies, just cell formulas.

What are you favourite tools/techniques for sense checking formulas?

cheers

Simon

Advertisements

11 Responses to “Spreadsheet auditing (2 of many)”

  1. Bob Phillips Says:

    Simon,

    If we can come up with a useful definition of a tool that could be useful to us all as a Spreadsheet Auditor, how about initiating an Open Source project to develop it. With a project manager, identified designers, developers testers and technical writers, it should be possible to overcome the 2 key obstacles of development, namely getting bored (like you, I detest the 80% when the tool is complete for me ), and the lack of objectivity,narrowness of vision we have as individuals.

    I have been thinking of starting an code librarion open source project, and this seems another useful topic.

    Bob

  2. Ross Says:

    I have some code that puts arrows on a whole selection, its a bit rough, sometimes very slow, but good for checking where that one cell value is missing from.

    Yeah I’ll do some work on a set of auditing tools, I think we need to think of any new ideas that might be usefull? Maybe speak to folks like Ray and Dermot?

    I think Bobs Idea is good too, does anyone know if soucre forgue has any management type features that would support this? Otherwise maybe bugzilla, can say it’s an area i know much about?

  3. Simon Murphy Says:

    Yes
    I’de be very interested in this, in fact Bob has stolen my thunder a little as I was going to propose this in a few days once I have looked at some of the options.
    My big concern would be multi devs in Excel/VBA is a dreadful experience, so I would suggest going straight for a COM tool and missing out XLA althogether. Versioning the binary bits of workbooks is a nightmare. With VB I think there are source control systems that will work, probably need to look at CVS.
    Source forge is a good idea, I think you get a lot of project type functionality out of the box.
    Maybe we could tie this up with the spreadsheet support idea?

  4. Marcus Says:

    Okay – confession time. Spreadsheet audit is probably one of my most lax areas. Sure I’ve used Excel’s auditing tools and tried a couple of others like MZ-Tools (VBA only) or rolled some small utilities of my own which only focused on a set issue. At the end of the day this area tends to get skimmed as it’s too much trouble. Frown.

    I’ve yet to find a more holistic diagnostic tool for Excel development on par with (say) what FMS has for Access. One impressive tool I’ve tried is Project Analyzer (by aivosto) but this is VB/VBA orientated and doesn’t shed much light on the workbook side of the equation. What have others found useful.

    And hear, hear to all of the above. Agree on multi-dev Excel development being a mine field. Happy to stick my hand up.

    Cheers – Marcus

  5. Simon Says:

    Marcus
    You’re right testing spreadsheets is hard, Ive not found any tools that are that good. Which is why I’ve built a few for my own use, sounds like thats what everyone else is doing too.
    One worry I have with that, is that VBA is fast to develop with but really VBA tools just aren’t fast enough these days, and can’t really be made multi threaded.
    I used one well known (VBA) tool on one spreadsheet, and I had to crash out after 2 hours and it was still nowhere near done.
    I think this is why Operis ( http://www.operis.co.uk/oak.htm ) re-wrote their toolkit in C#.
    Cheers
    Simon

  6. Bob Phillips Says:

    I agree that VBA is not the tool for it, and we should look at some type f COM tool.

    As always with these things, the success will be hugely dependent upon the design. Whilst I agree with SourceForge, and that would have been my suggested route, I would have thought it necessary to at least have a decent spec and a team structure (and some names) before approaching them. I would expect them to check credentials, and this would be the best way to demonstrate same.

  7. Ross Says:

    For my money it would be VB6, maybe C/C++ if speed became an issue. Are you thinking .Net Simon? I think i might also go down the route of a 97-2003 version too?

    But i think Bob’s got it right, more than the quality of the code (i mean something running in .5 seconds rather than .01!) it’s normally the usefulness of the tool thats the key.

    I’ll also have a look at VCS,

    cheers
    Ross

  8. Marcus Says:

    I’ve downloaded and installed TurboDelphi and have even quickly dipped my toe in the water to see how it feels. Meanwhile, I’ve been so busy with everything else I’ve barely touched it since. While I know it’s beneficial to my professional development, planning to do it and doing it are two separate things. As John Lennon put it, life is what happens to you when you’re busy making other plans.

    Cheers – Marcus

  9. Don Price Says:

    Simon,

    In our job we are limited as tto he tools that we can use on our systems, hence our dependence on SpACE for auditing (this is not an advert!).

    From the audits that I have done and feedback from our other auditors, the errors that we find are often to be found when looking for copied formulae, ie, looking for areas where a formula should have been copied and seeing if it has. The fact that people amend spreadsheets that are commonly used often means that the copied formulae are not always copied correctly, so rows, columns, brackets, cells can easily be missed off a perfectly credible-looking spreadsheet.

    There are many tools available for finding these non-copied formulae and more that can be written.

    The other area that we find errors is the use of incorrect formulae – where the developer thinks they know the answer and creates a formula to produce it. This is fraught with danger, particularly as staff change, spreadsheets get handed down through the staff and the intended use is lost and the error gets compounded with time. Sadly, this needs time and effort when auditing, so the best and fastest audit tools can still come down to the mark one eyeball.

    See you at euSpRIG,

    Don

  10. Simon Says:

    I’m thinking VB6 mainly so more people can get involved.
    CVS – Concurrent Version System is an open source source control system, that is very popular. No idea if it works with VB6.
    I would plan to steer clear of .net and Excel 2007 at least for the time being.
    cheer
    Simon

  11. Rickard - Project Finance Professional Says:

    Simon,

    I totally agree with you that a discussion about model review and spreadsheet audit software is highly relevant, however I believe that the strongest risk mitigant in this field to follow best-practise guidelines when developing the models. In my role at Navigator Project Finance I have come across hundreds of financial models of a range of different qualities. Sometimes it is hard to articulate the problems with a model, because of the tension it can sometime create with the author. To overcome this problem we have put this check list together that summarises the top ten mistakes in financial models – all of which can be fixed very easily if one follows some simple rules about best practise modelling.

    http://www.navigatorpf.com/Current_Topics/Financial_Modelling_for_Project_Finance/Project_Finance_Modelling%3A_Top_ten_mistakes/

    Hope it helps.

    Rickard

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: