Spreadsheet audit software

[Johnny C]

Our Internal Risk framework covers Excel models. At the moment, users classify the level of risk associated with each model themselves, as high, medium, low depending on the complexity of the models (e.g. if the owner got knocked down by a bus would someone else be able to maintain it) combined with the potential financial/reputational impact that a model going wonky might cause.

This is very subjective, and at a meeting recently it turned out the one of our prominent investment teams that deals with structured debt derivatives classed ALL their models as Low risk. To understand structured debt derivatives you probably need a Ph.D in Maths from Cambridge, and their models are going to be absolute beasts, I know they use all sorts of VBA to pull in market data from Bloomberg, Reuters etc.

The Risk person investigating has a limited knowledge of spreadsheets, so I suggested they get some software to audit models, to provide some hard information about complexity. For example, number of links to other sheets, information about calculations (e.g. identifying calculation bottlenecks). This would enable the Risk team to independently assess each model.

I've written some VBA that goes through a workbook and audits links between sheets and maps it with shapes, but it's quite basic and written with my Excel techniques in mind, it does formulae, tables, pivots charts and current values for indirects, but I wouldn't feel comfortable about it being used on some of the statistical behemoths are out there and I don't have the time or remit to develop it to cope with them. Plus it's slow, I've mapped my biggest model (approx 100 sheets and 750k links) took about 2 days to run.

I've seen some software in the past but forget what it was.

Can anyone provide suggestions about such software? Given the potential beneficial impact, cost is unlikely to be an issue if it's a few £K.

Cheers

 

[mole999]

have a look at > http://www.decisionmodels.com/fastexcel.htm you can try a demo of it, outside of my price range for infrequent use

 

[Johnny C]

I saw a reference to Inquire, a COM adding with XL2013. I tried it, it's what we need. A sample run generated an audit file that was too big to send on internal email, I've recommended Inquire and suggested to the Risk team that any model that generates an audit file too large to email is an automatic High risk. This was at 5pm on Friday, I'll investigate the audit more fully next week.

 

[SpillerBD]

Hey Johnny, its been a while. Did you find the INQUIRE Audit covered everything or did you find any thing missing from the results.
I am intrigued by the INQUIRE ribbon as it is a special item not included with every version of Excel. The thing I noticed was that the Audit report makes no indication of WorkBook VBA or specific linked files through the Get & Transform. There is and indication that "$EmbeddedMashup...) is a linked item, just no specifics.

Hope to see what comments you might share.

 

[Johnny C]

Hi
Inquire half does what we want. It covers most things, it doesn't cover VBA. I don't know about Get & Transform, we're still on XL2013. It does flag up some useful stuff like external references in data validation, which most people don't know how to identify.

1) It can be run by the user. Our use for it will be to identify the level of complexity - how many formulae, external references. The purpose for us is, combined with whether a model drives financial decisions, is to provide a quick simple means of assigning a RAG status to a model. Green = not many formulae and does not rive a decision, no further work, model doesn't need to go on a risk register. Amber = not complex but used to drive decisions, model goes on Risk register and some audit required. Red = complex & drives decisions, independent audit and review required.

2) Whilst Inquire does list out every formula (upto the row limit of Excel) that's not much use if, like one of my models, it's got 600K formulae.

What it doesn't do well is the Diagram. True, it will map cell, worksheet and workbook relationships - but on a one at a time basis.

What I need to be able to audit workbooks is something that maps all sheets in a workbook and links between them. I've had to write that myself, the output isn't pretty but it's got different shapes for tables, pivot tables, external references, charts and worksheets and you need to rearrange them into something readable. That way I can map start-end dependencies. It does take several days (yes) but to do it by hand and to guarantee I haven't missed something would take far longer. Mind you Inquire can take a long time to do the worksheet relationship diagram too.

We looked at something called Cluster7, which is a great tool to micromanage every single workbook, but you'd need someone to run it in each department as every single change in a workbook is logged and needs approval. Fine in an operations setting, but in a finance department where everything changes every month that would be a sledgehammer to crack a walnut.