Microsoft Inquire: not fit for purpose?

With Office 2013 Microsoft has included the tools acquired from PrOdiance all packaged up as options on an ‘Inquire’ tab. Sadly it seems to me the Inquire tools and specifically the spreadsheet comparison tool is not fit for purpose. This post describes why while this link takes you to a video illustrating its problems.

The spreadsheet comparison tool seems to have some obvious omissions. For example, it is able to detect, and users are able to enable/disable, checks for changes to formulas. But in many spreadsheets formulas containing today’s date or ones that generate text are common yet there’s no way to eliminate these checks making it harder than it should be for users to focus on changes to numeric formulas which are usually more important.

However this is nitpicking. A real problem arises from use of one of the alleged features of the comparison tool: the ability to detect column and row insertions. Detecting these ‘structural’ change is really hard. Of course in the simple case of a whole row or column being inserted its easy but that’s not what users do. They may insert a row but then use that space by filling it with new formulas and values and by copying cells from elsewhere in the sheet even, perhaps, from some of the cells originally in the inserted column’s place.

Why is it hard? Because there’s no referential integrity in Excel content. That is, the programmer cannot tell that the cell in D4 used to be at C3 by looking at some information attached to cell which logs its history, history which might be maintained by Excel. Instead the programmer must try to map cell values and formulas in one revision of a spreadsheet and compare them with cells in a second spreadsheet to try and deduce the changes made by a user. A simple column or row insertion is easy because the code can look for a blank column or row that did not used to be blank. But when the user starts mixing up the information in the sheet all bets are off. This is why ComplyXL does not attempt to find such conditions. Could we try? Sure. Would we be able to provide an answer on which a user can rely? Absolutely not.

But the spreadsheet comparison tool which is part of the Inquire add-in does include this functionality and, predictably, gets it wrong except in simple circumstances. As you will see in the video this is not just a little bit wrong. In the examples shown, the tool claims that columns have been inserted where no columns have been inserted and that column ranges have been deleted where no columns have been deleted at all. To me this extremely disingenuous on the part of Microsoft. The company has many, many high quality computer science types on staff and I believe anyone of them would be able to tell the manager responsible that the goal of reliably detecting column/row insertions and deletions is not attainable.

To me it’s concern that Microsoft is providing this functionality without making clear the limitations of the functionality (please, let me know if I have this wrong) because the result of providing a feature which so obviously does not work except in the simplest case is that a user who is unfamiliar with a spreadsheet but who has to take responsibility for it – an auditor for example – is likely to be told of a number of changes which did not occur. This will cause that person and their employer to invest time and resources to confirm no such change occurred.

The spreadsheets used in the video are not the standard demo Income Statement or Balance Sheet. They were thrown together in a few second just to be able to check the Inquire functionality and are filled with simple formulas. They are sheets I had no idea would illustrate any kind of problem. Are they representative of regular spreadsheets? Well they are not standard, simple spreadsheets but they are like the dense spreadsheets used to tabulate volumes of information from external sources used by financial type all over the world.

Information and Links

Join the fray by commenting, tracking what others have to say, or linking to it from your blog.

Other Posts

Reader Comments

Sorry, comments are closed.