PraxIS June 2007

07-06 Contents: Info Quality, Bogus and real data leaks, SoftTest survey, Web content, Spreadsheet tips and conferences

ISSN 1649-2374 This issue online at http://www.sysmod.com/praxis/prax0706.htm   [Previous] [Index]  [Next]

_______________________________________________________

Welcome to PraxIS

This month I'm giving some spreadsheet tips and code in response to some questions posed. Any comments welcome!

Patrick O'Beirne

_______________________________________________________ _______________________________________________________

1)  IT Risk and Security

Quality Schadenfreude

The visitor statistics for the Eusprig 'Horror Stories' page www.eusprig.org/stories.htm show that people like to read about others' misfortune. I've just discovered a new site on 'train wrecks' in Information Quality:

http://www.iqtrainwrecks.com/   An IQ Trainwreck is a problem that affects real people in the real world that has, at its heart, poor quality information or a failure to manage the quality of information. These can range from the inconvenience of dealing with poor customer service from poor quality data/information (see www.obriend.info  for an example) to the loss of life or limb that might arise if there is a failure to manage Information Quality appropriately.

On the other hand, for examples of best practices see the website of the International Association for Information and Data Quality - www.iaidq.org

Bogus reports of Canadian 'spy coin'

The myth of Canadian "poppy quarters" with embedded radio-frequency transmitters apparently resulted because several different U.S. Army contractors travelling in Canada became alarmed with the new coins and filed confidential espionage memos. The coins showed a red poppy overlaid on the Canadian maple leaf, where the red poppy had a protective coating that looked like a microscopic wire mesh "that looked like nano-technology." About 30 million coins were minted, commemorating Canada's 117,000 war dead.

http://www.cbc.ca/cp/Oddities/070507/K050723AU.html Canada's poppy quarters caused sensational warnings of 'spy coins' in U.S.

http://www.theregister.com/2007/05/09/bogus_nano_spy_coin_alert/ "We haven't seen anything like it before, Jim..."

Ryanair check-in site exposes data

http://www.theregister.co.uk/2007/05/24/ryanair_site_security/  There are a lot of websites that mix secure and unsecure data, and Ryanair have been found doing it too. According to the comments posted to this article, confidential data could thereby be sniffed over Wifi links. There are worse things, like giving away your password for a chocolate bar; but hey, I'd give them a password for that; it doesn't have to be a real one :-)

US Director National Intelligence embeds spreadsheet in Powerpoint

http://www.thespywhobilledme.com/the_spy_who_billed_me/2007/06/exclusive_offic.html

R.J. Hillhouse writes "In a holdover from the Cold War when the number really did matter to national security, the size of the US national intelligence budget remains one of the government's most closely guarded secrets. ... In the presentation originally made to a DIA conference in Colorado on May 14, Terri Everett, an Office of the Director of National Intelligence senior procurement executive, revealed that 70% of the total Intelligence Community budget is spent on contractors....Because these figures are classified, a scale of the total number of award dollars was omitted from the Y-axis of the bar chart. ...By double clicking on the bar chart, a small spreadsheet with the raw classified data appears."

____________________________________________________________
____________________________________________________________   

2) Software Testing

SoftTest Ireland, the special interest group for software testers, has surveyed its members on the topics they would like to hear about at their educational events. The topics of most interest were Test Management and Test Tools/Automation.

http://www.SoftTest.ie SoftTest Ireland, download report as PDF

____________________________________________________________
____________________________________________________________

3) Web content

How does your site look to a spam sniffer?

Try putting your own web site address into SiteAdvisor.com and see what it finds. It's a relief that none of the download files on other sites that I provide links to have as yet been infected with viruses.

Their most recent report is "Mapping the Malweb" at:

http://www.siteadvisor.com/studies/map_malweb_mar2007.html

The .ie domain for Ireland is the second least risky domain in the world, along with Finland's .fi.

What visitors to your web site care about

We've heard about the "Long Tail" in web marketing, the many residual search results that add up, something like the Scottish phrase "many a mickle makes a muckle". Gerry McGovern specialises in a website analysis method called "The Long Neck" described at

http://www.gerrymcgovern.com/mcgovern-carewords.htm

He has a regular newsletter on Content Management Solutions called "New Thinking". As he says 'When I hear many senior managers talk about their websites, I am surprised that they are still quoting the utterly useless measure, HITS. (HITS stands for "How Idiots Track Success.")'

____________________________________________________________
____________________________________________________________

4) Spreadsheets

Tip: Converting text numbers to values

When data is pasted in from another application such as an accounting package, it sometimes arrives as text rather than numbers. Excel 2002 and later should display a little green triangle in the top left corner of the cell as an indicator that it contains a number stored as text.  In case the cells have been formatted as text, always begin by resetting the format: On the Format menu, click Cells, and then click the Number tab to change the number format of the cells. If the cells contain non-numeric characters, you may have to retype the numbers or do a search & replace to remove dollar signs or other symbols.

Convert to Number, Excel error-correction option

This menu is only available in Excel 2002 and later. Select cells starting with one that shows the green triangle warning, then click the error warning symbol, a yellow diamond containing an exclamation mark. From the drop down menu, select "Convert to Number".

Copy Blank / Paste Special Add

Select a blank cell. Press Ctrl+C to copy it. Select the range of cells that you want converted to numbers and choose the menu Edit > Paste Special, Add and click OK. For variety, you could copy a cell containing 1 and use Paste Special, Multiply.

Data > Text to Columns

If the data is in a column, select the data, and choose the menu Data > Text To Columns.

Formula method

The VALUE() function converts numbers stored in text to numbers. To get rid of spaces and nonprinting control characters such as the non-breaking space (character code 160) which is commonly found in web pages, use
=VALUE(SUBSTITUTE(SUBSTITUTE(CLEAN(A1)," ",""),CHAR(160),""))

The N() worksheet function returns a zero for text cells so it is no help for this task. Other links are:

http://support.microsoft.com/kb/291047 How to convert text to numbers in Excel 2002 and in Excel 2003

http://www.dailydoseofexcel.com/archives/2006/02/18/number-stored-as-text/ many more ideas including this macro from Matt H:

Sub ConvertToDouble() ' Converts numbers stored as text to type Double

Dim cell As Range

For Each cell In Selection

    If Not IsEmpty(cell) And IsNumeric(cell.Value) Then

        cell.Value = CDbl(cell.Value)

    End If

Next cell

End Sub

If the cell contains mixed numerics and other characters you can use a function like this:

Function Numerics(sText As String) As String
Dim n As Long, sMid As String, sResult As String
For n = 1 To Len(sText)
    sMid = Mid(sText, n, 1)
    If sMid Like "#" Then
        sResult = sResult & sMid
    End If
Next n
Numerics = sResult
End Function

New companies enter the crowded Spreadsheet Control market

http://www.securexls.com SecureXLS adds audits and change controls to Excel. Their background appears to be in Laboratory Information Management Systems (LIMS) which have been regulated for a long time by the FDA.

http://www.i5logic.com  A new company founded to market the GoalDebug method developed by Martin Erwig and Robin Abraham at Oregon State University.

http://www.eurekalert.org/pub_releases/2007-05/osu-nat052307.php Goaldebug announcement.

http://web.engr.oregonstate.edu/~erwig/papers/abstracts.html#VLHCC05a Martin Erwig's paper.

http://hdl.handle.net/1957/4953  Robin Abraham's PhD thesis

Eusprig 8th Annual Conference

Compassoft have announced that they will sponsor the eighth conference of the European Spreadsheet Risk Interest Group on 11-13 July 2007 in Greenwich. The theme will be "Enterprise Spreadsheet Management - a necessary evil?". The programme will be up soon at:

http://www.eusprig.org 

Excel User Conferences this Autumn (Fall)

http://www.ExcelUserConference.com

• London, England tentatively Sept. 2007
• St. Louis, Missouri - Oct 24-26, 2007

These conferences will feature the Excel masters presenting training sessions. Topics are likely to be:

• Excel 2007 features, customising the Ribbon.
• Visual features and Dashboards for Impact
• VBA tips, efficiency, optimisation, UDFs, User Forms
• Pivot Tables and Charting
• Reducing errors, better design
• Understanding spreadsheets developed by others
• and of course a Q&A Forum

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

http://www.sysmod.com/az.php?a=190540400X&b=Spreadsheet+Check+Control Available worldwide from Amazon.

http://sysmod.buy.ie/catalog/product_info.php?products_id=188  Our offer - free shipping to EU .

List large directories of spreadsheets, find linkages and quality

http://www.sysmod.com/scanxls.htm  ScanXLS 3.0 overview of spreadsheet properties
ScanXLS3 works in Excel 2000 to 2007 and can process the much larger files in Excel 2007 (version 12), 16384 columns by 1048576 rows. It lists all XL* files in directories and reports many types of error and unusual properties. It allows you to specify as many properties and search terms as you wish. It optionally reports a detailed list of cell addresses with errors. It also reveals dependencies by the use of workbook formula links and external data sources.

_______________________________________________________
_______________________________________________________

FEEDBACK

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

Thank you! Patrick O'Beirne, Editor

_______________________________________________________ _______________________________________________________

5) Off Topic

The Annals of Improbable Research has a few related articles on how to try the patience of readers and listeners:

http://improbable.com/airchives/paperair/volume2/v2i5/howto.htm How To Write A Scientific Paper by E. Robert Schulman

http://www.improbable.com/2007/05/17/how-to-write-consistently-boring-scientific-literature/ Kaj Sand-Jensen, Oikos, vol. 116, no. 5, May 2007

http://www.improbable.com/news/2003/mar/unbearable_lecture.html How To Make a Scientific Lecture Unbearable by Alexander Kohn

They refer to the paper "On the pursuit and misuse of useless information" by Bastardi and Shafir, J Pers Soc Psychol. 1998. Decision makers often pursue information that appears relevant but would have no impact on choice. These days, the internet makes large volumes of useless information readily available. This paper is available for purchase from the APA, but you should be able to Google for it and find a PDF version if you're really keen.

None of the above is of course anything like providing intentionally misleading or obfuscating information such as the ChewBacca defence:

http://en.wikipedia.org/wiki/Chewbacca_defense  Wikipedia (Text) 

http://www.youtube.com/?v=yU-tZy3NIS4 (Video of the South Park episode)

_______________________________________________________
_______________________________________________________

Copyright (c) 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

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/
_______________________________________________________