PraxIS May 2005

_______________________________________________________

Welcome to PraxIS

This is a short issue this month because of my holidays in April. Let me know if there's a particular topic you'd like covered.

Patrick O'Beirne

_______________________________________________________ _______________________________________________________

1)  IT Risk and Security

How do they do that?

I've had a spoof attempt using an email address I used on Yahoogroups. I've since changed it. It was an unsubscribe followed by a subscribe followed by a virus/trojan post filename="our_secret.zip" (which could be Sober.O) followed by an unsubscribe all inside a minute. It was to johnkremer-tips which is on sparklist.com. The Sparklist scanner didn't catch the virus, it gave it the status "OK".

What made me curious is that the Sparklist system requires confirmation, so how did the spoofer sign up using my address? To activate a subscription, I would have had to reply to the "Your confirmation is needed" message, which I did not. I did a test on the Sparklist system and discovered the vulnerability, and have now informed the system owner. They need to make two changes to their authentication method.

My general security recommendations are at:

http://www.sysmod.com/free-home-computer-security.htm

____________________________________________________________
____________________________________________________________

2) Spreadsheets

Best practice?

The usually quiet Eusprig yahoogroup sparked into life recently with some firmly held opinions exchanged on whether using range names in Excel is a "best practice". I'll summarise it here and add my own comments.

"Best practice" is a misleading term if it is taken to mean a practice that everyone must do under all circumstances. There are very few of those, and I'd suggest two which can be briefly expressed as "specify and test":

1. Know what you need to do before you do it
2. Check that what you have done is what is needed

But in reality we can accept that a working definition is a practice that in general does more good than harm, that helps protect against errors or detect them.

Powerful features can be likened to power tools. In expert hands they speed up the work; other hands make a mess faster using them. Because all the power of Excel is there, the temptation for people to try things just beyond their skill level must be hard to resist. That is why some vendors (like Wimmer Systems) provide lock-down solutions so that management can restrict what users can do.

So "best" practices depend on the context of the skill of the intended application, maintainer, the user(s), the training and support available (or that can be charged for), and much more.

Among the most commonly cited good practices in creating spreadsheets are Range Names. I mainly use them for referring to parameters like TAXRATE=Control!$F$5. I had a quick check of a 120 spreadsheets using my SCANXLS (http://www.sysmod.com/scanxls.htm) utility and found that my ratio of range names to formulas is about 1:100.

Range Names

Readability and auditability

Substituting a descriptive name for a cell address assists readability. On the other hand, it adds an extra layer of addressing that needs to be checked. After a name is created, the user can still overwrite the contents of the cells to use them for a different meaning that the name would imply. That is one example of a wider problem of implementation diverging from documentation. I don't see checking range names as any great burden. When auditing spreadsheets, I usually print off the table obtained from Insert > Name > Paste > List, using INDIRECT to add a third column showing the current value of the cell or sum or count of the range. Although, INDIRECT does not work for references to closed workbooks.

Clicking on the formula toolbar shows references highlighted in different colours. If the cells are visible, you can drag or resize the marked ranges to make modifications on-the-fly. 

I wish there was a native bookmark facility in Excel that allowed me to mark the current cell, go off and chase down the components of a formula, and then come back to where I started. I can use Tools > Formula Auditing > Evaluate formula, but it allows stepping in to single cell references only. I can not use it to visit ranges to check if the block specified corresponds to the area of data actually there. Excel's auditing arrows are too fiddly for me to click on. Of course, there are plenty of drill-down tools that allow that, such as Aaron Blood's Explode (http://www.xl-logic.com/pages/explode.html), XDrill in XLSpell (http://www.sheetware.com), Exchecker (http://www.spreadsheetauditing.com), and so on. I expect if you're a vendor you'll come back to me to tell me how!

Safety in referring to cells that may be moved.

When a cell is moved, references to it are automatically adjusted where the reference is either:
(a) in the same workbook
(b) in a linked workbook that is open.

Cell references in closed workbooks or in VBA code (e.g. Range("A1") ) cannot be automatically adjusted. Therefore range names are a safer reference in these cases:

Simplification of long names.

A long path name or formula reference can be defined as a named formula, e.g.

CashTaxRate ='F:\DOCS\SSTESTS\Example Ready reckoner.xls'!CashTaxRate

This may be necessary if you reach the 1024 character limit for formulas, but let's hope you don't have to use such monsters.

Dynamic range names

These are created using OFFSET in conjunction with some calculation that returns a count of cells. Or, the range may be redefined by a macro triggered by a sheet update. They are frequently cited as being useful for charts that automatically expand as new data is added. They make money for auditors because not only are they slower to check, the fact that the range depends on the data increases the number of tests needed. Of course, if the extra checking is beyond either the ability or the inclination of the person whose job it is to test the spreadsheet, there's another problem.

Other peculiarities

A drop down list can use a named range to refer to cells on another worksheet.

Copying a range from one workbook to another can bring defined names with them, which can create what are called "ghost links". You can detect these by using Insert  > Name > Define and look for those containing the error value #REF!. Microsoft make available a DelLinks utility to clean up bad links at http://support.microsoft.com/kb/q188449/  XL: Delete Links Wizard.

You can have local names on a sheet that override the global definition of the same name. This can allow the same formula to be used with the same range name to mean different things on different sheets. This can give rise to some really misleading formulas - unless of course you check the list of names first to inform yourself of their existence.

Excel automatically substitutes range names for single cells that you click on. That's fine, as long as you intended it to be an absolute reference. For example, range name A1 as "that". In C1 you type = A1+B1 then you copy down, you get a relative reference. But if in C1 you type =, click on A1, Excel substitutes "that", type +B1, enter. Copy down, .. the reference to A1 is now absolute, using the range name "that".

The discussions are at:

http://groups.yahoo.com/group/eusprig/
http://www.mrexcel.com/board2/viewtopic.php?t=144698
http://www.ozgrid.com/forum/showthread.php?t=33449
http://www.vbaexpress.com/forum/showthread.php?t=3082
who also have a poll with the results at:
http://www.vbaexpress.com/forum/poll.php?do=showresults&pollid=25
Are Range Names Good Practice? No: 7, Yes: 13

____________________________________________________________
____________________________________________________________

3) Business

Systems Publishing

That's the new business I've started, entering the field with my own book on spreadsheet quality and best practice that should be out by the end of the year.

I've learned a lot about publishing from the Self-Publishing yahoogroup. I've applied for the ISBN block (1-905404-##), I've had an editor proofread the book, and engaged a designer for the cover. Curiously, there are only two low-volume forums on this side of the Atlantic. There is some activity on  http://users.boardnation.com/~selfpublish/index.php "Publish yourself in the UK". I could not find any group in Ireland on this. If you know of one, let me know! I'm still selecting the printer and may choose a UK one for short run printing, and one in Ireland for the longer run. Distribution costs weigh heavily here, so there's a lot in the equation. If you've been down this road already, I'd welcome your comments.

See www.SystemsPublishing.com

_______________________________________________________
_______________________________________________________

FEEDBACK

If you like this newsletter, a great way to show your support is to make your next book or CD purchase from our Amazon World Search page at http://www.sysmod.com/amazon.htm

Email your comments to FEEDBACK (at) SYSMOD (dot) COM

Thank you! Patrick O'Beirne, Editor

_______________________________________________________ _______________________________________________________

4) Off Topic

http://micro.magnet.fsu.edu/primer/java/scienceopticsu/powersof10/ A graphical exploration from the universe via a tree on Earth to an atom, in powers of ten.

http://www.vacantcanvas.com/macs.htm  This humour annoyed quite a few Mac users!

_______________________________________________________
_______________________________________________________

Copyright 2005 Systems Modelling Limited, http://www.sysmod.com . Reproduction allowed provided the newsletter is copied in its entirety and with this copyright notice.

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 - it's free!

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 will be moderating 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/
_______________________________________________________