PraxIS Jul-Aug 2010

10-07 Contents: UK XL Dev Con, Eusprig 2010 summary

ISSN 1649-2374 This issue online at   [Previous] [Index]

Systems Modelling Ltd.: Managing reality in Information Systems - strategies for success


     Excel developers' workshop London July 12     

     Summary of 11th EuSpRIG conference, Greenwich, 15-16 July 2010

About this newsletter and Archives
Subscribe and Unsubscribe information

Welcome to PraxIS

This issue covers July and August which is a vacation month. I wonder would it be time to move from a 1990s style newsletter to a Wordpress blog?

Patrick O'Beirne



Excel developers' workshop London July 12

I had the chance to hear Simon Murphy, Ross McLean, Charles Williams, and Roger Govier share their knowledge of the tools and skills that an Excel developer needs. Ross and Simon both had interesting demos of XLL technology and Excel DNA.

UK XL Devcon cheerfulness  

Charles Williams' slide on the add-in marketplace stated : "Don't give up the day job"
Other comments are posted at:

Summary of 11th EuSpRIG conference, Greenwich, 15-16 July 2010

Morten Siersted’s keynote address described the danger of “trust me” models, that people do not review because they cannot. F1F9 spreadsheets are built by an industrialised process by a team of 25 developers in India, to the FAST modelling standard that creates highly navigable and therefore understandable models.

Dean Buckner of the FSA described the effects of spreadsheet jockeys creating islands of automation in a sea of data. If he sees any spreadsheet operations that match up related data (similar to a database Join), he knows that is where to look for problems.

Tom Grossmann presented the first side-by-side comparison of FAST, Operis and BPM’s SSRB modelling standard. He had 45 minutes but could have done with three times as much! He stresses the basic quality principle “Find the best way to do something and do it always”. Rather than a naïve expectation that there is one best way, he reminded us of the US Army saying that “there’s not a right way or a wrong way, there’s the Army way.”

Thomas Lemon showed how Protiviti use the ClusterSeven technology for compliance monitoring. Users register their spreadsheets into the compliance system. He stressed the importance of a clear executive mandate. He recommended that auditors should not only add controls but add value; and provide instructions on how to check.

Seb Dewhurst described EASA’s authoring tool for creating a web based interface for multi-user access to a spreadsheet running on a server. To prepare the spreadsheet, interactive elements such as VBA pop-ups need to be removed.

Patrick O’Beirne described ways to “refactor” spreadsheets – ie to improve their understandability and maintainability without changing their functionality. He covered formulas, reference structure, data normalisation, and VBA refactoring.

Simon Murphy finds that the more rigid the IT approval process, the more people will work around it with spreadsheets. He sees a growing market for migration of VBA applications to C#. A rule of thumb for filtering out the trivial spreadsheets is to look for those which are still in use after a year. He recommends deskside expert support rather than classroom training.

David Colver: “Names are natural”. Symbolic names were adopted since the earliest days of computing to make code easier to write and read. He argues that the same principle applies to the use of range names in Excel. Operis models can have thousands of names. To check names you have to verify that the range referred to is the correct one for the meaning of the model. Then, as for any other formula, you check that the formula itself is correct. The Operis OAK tool can de-apply range names, a facility missing in Excel. They do that to remove the names from their models for clients who specifically request it.

Darren Miller of Sumwise showed how a web client can be used to specify a model using symbolic names for row and column items which can then be used for calculations without the need to copy master formulas into ranges. The syntax used is similar to the Table syntax in Excel 2007/2010. He is looking for beta testers to give them initial feedback.

Ralph Baxter spoke of the role of continuous monitoring to provide a quick health check on every spreadsheet that is saved to a server.

Angus Dunn asserted that a spreadsheet generator such as RingTailXL would be a safer way to specify and build spreadsheets than at present by hand. Along with Angela Collins, he proposed a set of standards categorised as Require, Desire, Avoid, and Ban.

Ray Panko posed a test for recommendations: “is the prescription safe and effective?” Suggest we should have same ratio of 40% of ALC is in Test, like MS. SW testing covers 2 weeks training in MS. Humans find 56% of defects, software tools 0.5%. We’re teaching new dogs old tricks.

Grenville Croll presented a retrospective analysis of a 2001 spreadsheet that modelled Real Options. He concluded that the models underestimated the stochastic nature of the market and the buyers overpaid for the options.

Mary Pat Campbell requires users to leave a spreadsheet in a fit state for others; or for yourself one year later. Consider the plight of new management taking over, having to justify the previous teams’ models to the auditors. She recommended training in Excel, systems thinking, problem recognition and solving.

Dermot Balson’s paper also required that spreadsheets should be created with others in mind, nit just the original creator. “Make it easy to check and maintain, and safe to use.”

Ruth McKeever won the ISACA student prize with a paper that provided evidence that the use of range names hindered the debugging performance of novices. This is because any feature that reduces error visibility hinders the perception of those who are not aware of the design tradeoffs in such techniques.

Ben Rittweger presented a survey of spreadsheet risk management practices in organisations in Ireland. 59% claimed to have controls on access and validation.

Françoise Tort provided a set of principles to design a curriculum for teaching spreadsheets. A study had shown that students did not master basic spreadsheet concepts but rather acquired habits and routines. She recommended exploring unusual situations, implementation options, and quality.

Andrea Kohlhase discussed the use of semantic technology for assessing spreadsheets. Their institute DFKI created SACHS, a help system for their financial control system in Excel. It provides explanatory text, colour coding, and dependency graphs.

Simon Thorne presented a proposed MSc module in EUC risk management at UWIC with ten topics including spreadsheet errors and risk management.


Ruth McKeever's paper shows that people not trained in testing do not test well. If range names hinder novices, the problem is that they are novices. They have not yet passed the Pons Asinorum of range name use. They may know what they are, but they have not yet acquired awareness of the risks and  tradeoffs in using them. People should not be asked to test or maintain a model above their skill level. One software requirement is maintainability, and the spec can say that it should be maintainable by people who don’t know range names. An analogy with DIY car maintenance is that it is not possible with modern cars; your choice is to stick to old cars or have the job done by a trained mechanic.

My paper on spreadsheet refactoring is available at,%20O'Beirne.pdf

and the workbook (which I presented instead of Powerpoint slides) to illustrate some of the principles is at'Beirne%20(enable%20macros).xlsb
If your browser downloads that as a .zip file, just change the extension to .xlsb
And as the name hints, enable macros! 

Standards / Best practices / recommendations / guidelines / good and bad ideas

There was quite an amount of discussion and it was finally decided that Eusprig should set up a web page giving the pros and cons of the many proposals made to build better spreadsheets. Ray Panko would like any proposal to be backed by, or to stimulate, research to demonstrate that the prescription is safe and effective.

Group at conference dinner

Ralph Baxter presenting

XLTest 1.21 upgrade

XLTest is an Excel add-in that dramatically increases a tester's ability to discover unusual conditions in spreadsheets,
This upgrade includes:
1.    Detailed listings can now list found areas separately
2.    Lists macro shortcut keys and button actions
3.    Shows DV & CF formulas in A1 and R1C1 style
4.    Lists formulas in charts, OLE objects
5.    Adds timing estimates for bulk operations
6.    Worksheet and Workbook comparisons show both R1C1 and A1 styles.
7.    Workbook comparison includes option to compare VBA source using WinMerge.
8.    Comparison functions made available as a public macro to your own VBA code
9.    Added VB Component export and import.
Still only 99 euro!
Bonus for readers of Praxis : Download the complete XLTest manual here  (3.5MB PDF)

Book: Spreadsheet Check and Control: 47 best practices to detect and prevent errors



Simply send your comments to FEEDBACK (at) SYSMOD (dot) COM

Thank you! Patrick O'Beirne, Editor


Copyright (c) Systems Modelling Limited, . Reproduction allowed provided this copyright notice is included.

We appreciate any feedback or suggestions for improvement. If you have received this newsletter from anybody else, we urge you to sign up for your personal copy by sending a blank email to   EuroIS-subscribe (at) yahoogroups (dot) com

For those who would like to do more than receive the monthly newsletter, the EuroIS list makes it easy for you to discuss issues raised, to share experiences with the rest of the group, and to contribute files to a common user community pool independent of the web site. I moderate posts to the EuroIS list, to screen out inappropriate material.

Patrick O'Beirne, Editor
"Praxis" means model or example, from the Greek verb "to do". The name is chosen to reflect our focus on practical solutions to IS problems, avoiding hype. If you like acronyms, think of it as "Patrick's reports and analysis across Information Systems".
Please tell a friend about this newsletter.
We especially appreciate a link to from your web site!
To read previous issues of this newsletter please visit our web site at

This newsletter is prepared in good faith and the information has been taken from observation and other sources believed to be reliable. Systems Modelling Ltd. (SML) does not represent expressly or by implication the accuracy, truthfulness or reliability of any information provided. It is a condition of use that users accept that SML has no liability for any errors, inaccuracies or omissions. The information is not intended to constitute legal or professional advice. You should consult a professional at Systems Modelling Ltd. directly for advice that is specifically tailored to your particular circumstances.
We guarantee not to sell, trade or give your e-mail address to anyone.
To subscribe to this Newsletter send an email to
EuroIS-subscribe (at) yahoogroups (dot) com
To unsubscribe from this Newsletter send an email to
EuroIS-unsubscribe (at) yahoogroups (dot) com
EuroIS is the distribution list server of the PraxIS newsletter. It also offers a moderated discussion list for readers and a free shared storage area for user-contributed files. The archives of this group are on YahooGroups website