09-07 Contents: Eusprig 2009 conference report, Excel 2007 hotfix
ISSN 1649-2374 This issue online at http://www.sysmod.com/praxis/prax0907.htm [Previous] [Index]
| Systems Modelling Ltd.: Managing reality in Information Systems - strategies for success | |
IN THIS ISSUE |
|
| 1) Spreadsheet risk Report from Eusprig conference in Paris |
|
| 2) Excel Hotfix for vanishing range names bug in Excel 2007 |
|
| 3) Off topic: LOL |
|
| About this newsletter and Archives Disclaimer Subscribe and Unsubscribe information |
This month I report on the 2009 Eusprig conference which
featured a back-to-basics approach.
Patrick O'Beirne
____________________________________________________________
____________________________________________________________
The key to excellence is doing it right first time; and that was the
recurring theme in the papers at the tenth annual conference of the
European Spreadsheet Risks Interest Group held at ENS Cachan, Paris, on
2-3 July 2009.
Jocelyn Paine
www.j-paine.org, Documenting Spreadsheets with Pseudo-Code: Cash-Flow
and Loans
His theme was "First document, then Excel". He specifies time based
models in plain text pseudocode and his application (Excelsior)
generates Excel files. It reminds me of Angus Dunn's concept, see
below, and commercial applications like ModelSheet.
Day 1 Keynote: Martin
Erwig, OSU, Software Engineering for Spreadsheets: Challenges and
Opportunities.
He spoke of type checking, and described research that indicated that
combining syntactic label checking with semantic dimension analysis
finds even more faults. A real-world tool from RedRover software, a OSU
spinoff, features goal-directed debugging. He mentioned Gencel and
ClassSheets, tools for the generation of correct spreadsheets, which
may be relevant to the work of Jocelyn Paine (above) and Angus Dunn
(below).
Day 2 Keynote: Deniz
Sumengen, FRC
She spoke of the Technical Standards for Modelling, from the Board of
Actuarial Standards of the Financial Reporting Council. They welcome
comments on the exposure draft available from
http://www.frc.org.uk/bas/publications/pub1986.html
Patrick O’Beirne Systems
Modelling, Checks
and Controls in Spreadsheets
This talk went back to basics on structure: have cross-foot totals,
SUMs that are safe for the insertion or deletion of data rows,
self-checking formulas, and ended with the top ten questions on
spreadsheet use.
Susan Allen, HBOS, Excel
Modelling, Transparency, Auditing and Business Use
Susan reported on how HBOS went from a restricted modelling package to
an Excel-based system because the users preferred it. It features a
separation between input, processing, and output; a formula walker to
help understand dependencies; and change logging.
Tom Grossman, Ozgur Ozluk
& Jan Gustavson University of San Francisco, The Lookup
Technique to Replace Nested-If Formulas
This described how to replace successive IF functions, all doing the
same kind of test, with a VLOOKUP formula. It received the highest
rating from the delegates.
Leslie Bradley &
Kevin McDaid Dundalk Institute of Technology, Error Estimation in Large
Spreadsheets using Bayesian Statistics
This also drew on Software Engineering experience for defect estimation
techniques. Briefly, when you have a very large spreadsheet to audit,
you can estimate the number of defects there might be based on how many
you found so far. That way you might be able to stop the check earlier
either because it looks very good or because it’s too bad to be worth
continuing.
Angus Dunn, There will be
a better way …
Angus has a generator called RingtailXL for Excel project models.
Components are specified using syntax similar to range names,
documentation is included, versions and changes are tracked, and can be
assembled into workbooks.
John Hunt Excel for
managers, An approach for the automated risk assessment of structural
differences
He described how DiffXL infers the nature of changes in block structure
in order to estimate the riskiness of a change. For example, a simple
move of a block would be low risk, formula changes could be high risk.
Matthew Dinmore,
University of Maryland Baltimore, Documenting Problem-Solving
Knowledge: Proposed Annotation Design
He described implicit, explicit, and literate notation. Implicit
notation is inferred from the formulas; explicit is present in comments
and textboxes; and literate programming focuses on the best way to
describe the program rather than on its executable path. He presented
an informed-explicit design consisting of a traditional multi-tabbed
spreadsheet pane positioned next to a multi-tabbed document pane.
Ruth McKeever, Kevin
McDaid & Brian Bishop Dundalk Institute of Technology ,
Analysis of the Impact of Named Ranges on the Debugging Performance of
Novice Users
This won the Student Prize this year. The international panel of
judges, including Martin Erwig of OSU, described it as a well-designed
and thoroughly executed piece of research. This directly challenged the
common advice to use range names. The results show that for novice
debuggers "A spreadsheet that contains range names in formulas will be
more difficult to inspect and correct than a spreadsheet that does not
use names in formulas".
Bill Bekenn & Ray
Hooper Fairway Associates, Some Spreadsheet Poka-Yoke
Poka-Yoke is a Japanese term meaning “Mistake avoidance”. They gave
advice with examples on why things fail when inserting, deleting, and
copying structural elements, and how to design to avoid these common
mistakes.
Angela Collins BDO Stoy
Hayward , Embedded spreadsheet modelling
She is the BDO spreadsheet guru who provides on-the-job training; just
in time; task oriented. It reminds me of Kath McGuire's talk from
2007,that training based on the tasks that people need to accomplish in
their context is more effective than generic classroom-type training.
She supported that by reporting on a survey of user satisfaction.
Derek Flood, Kevin Mc
Daid, Fergal McCaffery. Dundalk Institute of Technology , NLP-SIR: A
Natural Language Approach for SS Information Retrieval
This allows the user to type enquiries in ordinary English in order to
query tables contained in a spreadsheet. Such techniques are available
in database queries. It reminded Grenville Croll of the Lotus 123 addin
called "HAL".
Sriram Iyengar &
John Shvirbely Medal.org and University of Texas , The Medical
Algorithms Project
He described this huge corpus of 11,000 spreadsheets that facilitate
medical decision making. However there were no details of the quality
check and control techniques that they exercised.
Françoise Tort,
François-Marie Blondel, Éric Bruillard ENS Cachan , From errors
detection to behaviour observation: first results
The paper described using Camtasia to capture actions by users
examining a spreadsheet for defects, and later analysis. Two groups
were used, Sciences and Social Sciences. Martin Erwig asked about the
gender differences in the groups which might be an explanation of the
different behaviours. They also presented their results of an
experiment carried out at Eusprig with a digital pen, to learn how to
capture timing of form filling.
Étienne Vandeput
Université de Liège, Belgium , Milestones for Teaching the Spreadsheet
Program
He described the common limitations in most spreadsheet training books
and school courses – that they focus on the commands in the
spreadsheet, not problem solving. This accords with the view of Angela
Collins above. He is developing a corpus of problem-based learning
examples.
Grenville Croll
Spreadsheet Engineering Ltd , Spreadsheets and the Financial Collapse
He comprehensively outlined the risks: human error, fraud,
overconfidence, interpretation, archiving, assumptions, opacity,
reification, and enterprise inoperability. And the role of spreadsheets
in the financial system: Ubiqity, Importance, Criticality, Legality,
Centrality, Contagion. He pointed to statements from the UK Financial
regulator indicating their knowledge of the risks in spreadsheets found
in use. A possible lesson might be drawn from the collapse of the
Jamaican banking system from over-reliance on uncontrolled personal
spreadsheets. He was careful to avoid asserting causation - certainly
all these CDOs could not have been developed and piggybacked into a
dizzyingly unstable pyramid without spreadsheets. But the reason surely
is greed; "don't look too closely as these things as long as they are
making us money".
Mel Glass EASA, Reducing
the Risk of Spreadsheet Usage - A Case Study
He gave a straightforward description a method to web-enable a
spreadsheet that gives full access to its features (eg VBA) which is
more than what Excel Services does.
____________________________________________________________
____________________________________________________________
Last month I mentioned the problem of Excel 2007 unpredictably losing defined names.
Jan Karel Pieterse posted to the Daily Dose of Excel blog that MS have a hotfix:
http://www.dailydoseofexcel.com/archives/2009/07/05/the-mystery-of-the-disappearing-range-name/
Make sure you have Office 2007 updated to at least SP1; visit
Microsoft Update to check if you need it.
After that, visit the hotfix page and request your hotfix download:
http://support.microsoft.com/kb/968863/en-us
ScanXLS scans any given directory and below and obtains a list of all the .XL* files. You then select some or all of these, and it opens each one in turn read-only and reports on some file properties, attributes, the presence of unusual features or settings that may represent a risk or are prone to human error, the error checking statistics provided by Microsoft Excel, a list of other workbooks that it depends on through links, and a scoring on how 'problematic' it might be. SCANXLS can also compare two workbooks to check whether their formulas and/or values are identical.
http://www.sysmod.com/scanxls.htm
____________________________________________________________
____________________________________________________________
Simply send your comments to FEEDBACK (at) SYSMOD (dot) COM
Thank you! Patrick O'Beirne, Editor
_______________________________________________________
_______________________________________________________
http://edition.cnn.com/2009/TECH/07/03/funny.websites/index.html
including Safety Graphic Fun poking fun at the signs that are supposed to help us stay safe:
http://juliasmexicocity.typepad.com/safetygraphics/
_______________________________________________________
_______________________________________________________
Copyright (c) Systems Modelling Limited,
http://www.sysmod.com
. 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
sysmod.com web site. I moderate posts to the EuroIS list, to screen out
inappropriate material.
Patrick O'Beirne, Editor
_______________________________________________________
ABOUT THIS NEWSLETTER
"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 www.sysmod.com from your web site!
______________________________________________________
ARCHIVES
To read previous issues of this newsletter please visit our web site at
http://www.sysmod.com/praxis.htm
DISCLAIMER
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.
_______________________________________________________
PRIVACY POLICY:
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
http://finance.groups.yahoo.com/group/EuroIS/
_______________________________________________________