Tactical pit stop

Had a mini crisis on the other day.

I ended up needing to build most of an Oracle OLAP star schema in Access from text files. No massive problem there, but it was getting late, and under rather a lot of pressure. (and the build files didn’t have field names or an import spec…)

Anyway one issue was multiple versions of a transaction, and we only wanted the most recent. I had a complete block – I knew I had to do some sort of max, but couldn’t work out what I needed. I imported it to Excel because I thought that would be easier. Still had a total blank.

So I got up and went for a walk/bio/comfort break. By the time I got back to my desk I had worked out what I needed to do. I needed to do a group by and get the max of the version field, and join that back to the main query on version. The technicals are important though, its the point of taking 5 mins out and suddenly everything becomes clear. Have you had that?

Whats your recommended/favourite quick ‘pick-me-up’?

cheers

Simon

End user computing

End use computing is a general term that roughly means non IS professionals solving business issues with computing without involving the IS department, or IS specialists much.

Most spreadsheet development probably falls within this, but so too does a bunch of other stuff like Access databases, many intranet sites, maybe even some .net/java development in some orgs.

The IS department is typically responsible for desktop install and configuration, network stuff, and most server based stuff, as well as lots of other things.

Consider the whole data processing that an average, or one particular org, does from initial transaction through to all the final reporting. On average what do you think are the relevant proportions in these categories:

1. Spreadsheets
2. Other End user computing
3. IS managed systems

I would base my estimate on an estimate of headcount in IS v headcount in finance and marketing etc. This is pretty much a guess then:

1. s/s 50%
2. Other EUC 20%
3. IS 30%

So I reckon that EUC makes up the majority of information manipulation in many orgs, and that overall there is more logic tied up in s/s than in IS manged systems. What do you think?

One thing I wonder is are younger companies, with no mainframes different? Or what about IT companies? I think they maybe have more IS controlled/managed stuff?

I’m not sure we can be too precise on the definitions, so feel free to describe your own along with your estimate.

cheers

Simon

SLA

SLA – Service Level Agreement

A conflict resolution tool to resolve conflicts caused by the sort of people who need service level agreements.

SLA’s between different organisations make some sense but within the same company its rather misguided in my opinion.

Do Becks and Roo have an SLA – provide x number of passes within y meters of my position, or else what? (apologies for anyone who doesn’t follow English football – I dont blame you).

Does Ricky Hatton http://news.bbc.co.uk/sport1/hi/boxing/6234340.stm  have an SLA with his second?

Does Dougie Lampkin ( http://www.dougielampkin.co.uk/ ) have an SLA with his Dad? (his dad is (/was?) his spotter – the one who tries to catch the bike when it all goes pear shaped)

The point is people on the same team shouldn’t need SLA’s. And anyway any agreement is only as valuable as the penalty for breach. So if your IS department don’t fix your pc within the SLA, what going to happen? A ‘fine’? A dismissal? or more likely nothing.

I struggle to see the point of SLA’s between depatments in the same company.  All it demonstrates to me is that the departments don’t trust each other. Which suggests they are not all pulling together in the same direction. Goal mis-alignment? or is it goal incongruence?

So I think the existence of SLAs is an indication of a disjointed, uncoordinated, untrusting organisation, not the other way around. What do you think?

Where do you think SLAs are useful? (If at all)

cheers

Simon

Code by Petzold

Yet another book review (I keep busy – even when my post rate drops!)

Code is a book by Charles Petzold, who is well known as an author of C/C++ books for Windows developers. Code is a difficult book to describe. Its very good, and a worthwhile read for anyone serious about being a good developer.

http://www.charlespetzold.com/code/

He basically describes how to build a computer from introductory electronics, through binary and boolean logic to simple programming. Also covering morse code and braille on the way through. Sounds pretty heavy going, but actually it flows well (or maybe I’m way too geeky!)

The bits I found most useful were the wiring up of the various logic gates, a concise explanation of binary, octal and Hex (I forget hex much faster than I can learn it). A very good explanation of ASCII (or the ascii’s as he points out). I think I may actually be able to remember now that a byte is 8 bits so 32 bit is 4 bytes. Not life critical for sure, but one day I would like to understand why my broadband connection doesn’t actually give me the 2, 4 or 8 things per thingy its meant to.

The only minor downside of Code is its a few years old now (published around 2000), so some of the comparatives are a bit dated. I wouldn’t call it a big issue. I would recommend the book as good background reading, not essential for most people, but probably useful for a few.

Here is a link to it at my new favourite (UK) computer bookshop:

http://www.compman.co.uk/scripts/browse.asp?ref=443692

(My old favourite bookshop has lost a few parcels, has higher prices, and longer delivery for the odd package that finally does arrive) 

 Anyone else read it? any views?

cheers

Simon

Eusprig press release – see you there?

In case you didn’t know – this is the best place to find out about ‘serious’ spreadsheeting.

——————————

EuSpRIG 2007 – Worldwide Spreadsheet Experts Meet in London

Experts From Europe, United States, Canada and Australia Meet to Discuss Enterprise Spreadsheet Management issues at the 8th Annual Spreadsheet Risks Conference.

Bury St Edmunds, UK – 3rd June 2007 – The European Spreadsheet Risks Interest Group (EuSpRIG), in association with Compassoft, is holding its 2007 conference on the theme of “Enterprise Spreadsheet Management: A Necessary Evil?” on the 11th – 13th July 2007 at the University of Greenwich, London, United Kingdom. Keynote speakers include Professor Ray Panko of the University of Hawaii, who will be speaking on “Spreadsheet Errors – What the Research Says”, Dean Buckner of the Financial Services
Authority who will be giving a regulatory update on the use of spreadsheets in the financial markets, and Paul Bach, CEO of Compassoft, who will be outlining the state of technology identifying and managing spreadsheets in enterprise environments.

“Many studies have shown that significant sums of money are lost by organisations of all sizes because they fail to check thoroughly that spreadsheets, critical to their business, are free of material error”, said Patrick O’Beirne, EuSpRIG chairperson. “EuSpRIG was formed in 1999 to address this and related issues. The annual conference is an excellent place for leaders in this field to come together to discuss the issues and agree on best practices” he continued.

“Finance teams and auditors everywhere know that there is a very high likelihood of multiple material errors in key spreadsheets that are used every day in their company. Compassoft is proud to work with EuSpRIG to raise this issue to a top priority among CFOs, Controllers, and Audit Firms”, said Paul Bach, CEO of Compassoft, the main conference sponsor.

EuSpRIG 2007 is a forum for business people, regulators, auditors, academics and other interested parties to share information and ideas aboutthe management of spreadsheet risks, related problems and opportunities. There are 18 papers and presentations in this year’s conference. Topics include “Enterprise Spreadsheet Management – A Necessary Good”, “Impact of Errors in Operational Spreadsheets” and “Risk Management for Complex Calculations”, Speakers and delegates are expected from all over Europe,
USA, Canada and Australia including representatives from Merrill Lynch, Lloyds TSB, HMRC, UK, Shell and universities worldwide. Further information about EuSpRIG, including the conference programme and booking information is available at http://www.eusprig.org

About EuSpRIG

EuSpRIG is an organisation dedicated to informing organisations about the material commercial and financial risks involved in the uncontrolled use of untested spreadsheets created by end-users who are not experienced in developing information systems. EuSpRIG is a not-for-profit organisation governed by an elected committee operating under the terms of a written constitution. Committee members include senior managers and directors of leading accounting firms and senior academics from European Universities.

EuSpRIG 2007 Main Conference Sponsor – Compassoft, Inc.
Compassoft reduces financial risk by discovering, validating, monitoring and controlling critical financial data files residing on departmental servers and desktop computers worldwide. Compassoft Enterprise is the industry’s only solution that automatically creates a comprehensive analysis of all spreadsheets, databases and reports distributed across your entire organization, including those within and outside of document management systems. This easy to use and deploy software creates and documents a comprehensive controls structure for key distributed spreadsheets, data bases and reports found across your company’s divisions
and reporting entities. Further information about Compassoft Inc is available from http://www.compassoft.com.

EuSpRIG 2007 Conference Co-Sponsors
The following organizations are pleased to support the EuSpRIG 2007 Conference: SecureXLS, Baker Tilly, Spreadsheet Engineering Ltd, AuditNet, Systems Modelling Ltd, Information Systems Audit and Control Association – Northern England Chapter, University of Wales Institute Cardiff and the University of Greenwich.

EuSpRIG Contact
Grenville Croll, EuSpRIG Membership Secretary, c/o Spreadsheet Engineering Ltd, 63a Churchgate Street, Bury St Edmunds, United Kingdom, IP30 0RL, +44 (0) 1284 748020, grenville [AT] spreadsheetrisks.com.

##ENDS##

http://www.eusprig.org

——————————-

I’m presenting on the Thursday – spreadsheet hell, although more recently I feel I am in Access hell (well Jet hell I spose).

cheers

Simon

Office Secure ™

I was thinking about the current general security undercurrent/fashion. I’m seeing a lot of auditor driven pointless security, and tick box driven busy work, but it did get me thinking.

All you auditors – if the bad guys are on your nework they own you completely. Password protecting financial spreadsheets is not going to save you. In fact suggesting it as security when MS explicitly state it is not, probably amounts to professional negligence. </end rant>

back on topic:

What if MS produced a new ‘secure’ version of Office (in addition to the real one).

1. No macros (xlm style)
2. no VBA at all
3. no automation from external clients (VB/.net etc)
4. no call/register
5. no scripting
6. maybe just code signed .net extensions?
7. some kind of file access limitations
8. no linked tables/ external data stuff
9. no email automation etc.
10. what others have I missed?

I am resisting suggesting that this is similar to some open source alternatives, because many of them have a rich feature set. But open source could well be a viable alternative to office secure ™.

Could you see your organisation moving over to gain the ‘security’ benefits? Or would you continue with the ‘insecure’ one because it offers functionaliy that you rely on?

I think MS are probably under pressure to improve the ‘security’ (as measured by the tick box brigade), I worry that may be at the expense of functionality we rely on. If they brought out an additional cut down version, then the box tickers could migrate to that and suffer the limitations but feel secure. And the rest of us could continue adding real business value using the full power of the full fat version.

I’m sure the cut down version could be generated from the same source code – just remove all the useful stuff, compile and release. So there shouldn’t be too much of a maintenance headache.

What do you reckon?

If you would be interested in office secure ™, maybe you would be interested in my PC secure ™ application? (It just turns your PC off and stops it booting up – now thats real security!) (send your cash in small denominations notes to …)

cheers

Simon

couple of questions and half answers

I’ve had a few email questions recently, so here they are with my ‘answers’. Please comment if you have anything to add or correct. I generally point people to Excel-l, but I’m struggling to find time to participate at the mo. 

denibart asks:

Is it posible to copy from worksheet to codemodul data…

case….

Let say this is on xls sheet

Sub Export()
    
Dim myRecord As Range
Dim sOut As String
Open "c:\source.txt" For Output As #1
For Each myRecord In Range("A2:A10")
With myRecord
sOut = sOut & myRecord.Text
Print #1, Mid(sOut, 1)
sOut = Empty
End With
Next myRecord
Close #1
MsgBox "File exported to: c:\Test.txt", vbOKOnly
    
End Sub

And I want to copy this with vbamacros on modExport..can this be done

So when I start excel workbook_open ….copy range(“a1:a18”) to modExport, is
this possible.

____________________________________________

If I understand right you want to copy the sheet with the code?

If so just right click the worksheets tab and copy, that will take any code in the worksheet class module.

If you want to pass the address you want to export to the txt file then change your export routine to take a string or range parameter as in

sub Export(theRangeToExport as range)

…

 then change this line:

For Each myRecord In Range(“A2:A10”)

to:

For Each myRecord In theRangeToExport

Then call it:

export ActiveSheet.range(“A2:A10”)

hth_____________________________________________

John asks:

Hi,

    I recently posted a query on the Ozgrid.com forum about “How to access XLL add-in functions from VBA code module”. I was wondering if you could clear up some resulting questions about accessing worksheet and add-in functions.

Why when accessing functions such as AVERAGE or MAX, I use the prefix “Application.WorksheetFunction” to access it?

Why when accessing Add-in functions(not sure if this is true for all add-ins or just my particular one), I use the prefix “Application.Run” to access it?

Why is it possible to access the function NOW() directly?

I have gone through the steps to ensure the Add-in is registered using “Application.RegisterXLL”. and displaying the registered functions using “Application.RegisteredFunctions”.

I am curious to find out if it is possible to access all function directly without the prefix(ie Answer = Function(Arg1,Arg2,…)).

Kind regards

John

1. AVERAGE and MAX were redefined into the WorksheetFunction object in xl97 or 2k prior to that (and probably still for compatibility) you just went application.max … I suspect they did it because the app object was getting too big and unwieldy.

2. xll addin functions are just registered with native Excel same as xlm macros. VBA has no knowledge of these. App.run is how you drop out of VBA into native Excel. Personally I always use declares to access xll stuff, but I couldn’t say which approach is better. I would never call a VBA function using app.run though as it breaks the VB call stack and errors don’t bubble up right.

Using declares will get you your ‘answer = func(….). ‘ style. Func will be whatever you declare your xll function will be known as in VBA as in:

Declare Function theVBAName Lib “thexll.XLL” Alias “theXLLName” (ByVal int as long) As Long

for your excel ones you could use
with app.worksheetfunction
  answer = .max()...
end with
which is only one dot off what you wanted.
3. There are 2 now()’s the Excel one and the VB one. In VBA you always call the VB one, you don’t get access to the intrinsic Excel one as there is no point now() is part of the VB language. I’m sure you could get at it if you wanted to, but I have no idea why you would.

hth

Please chip in with your own thoughts, especially if I’ve steered them wrong

cheers

Simon

Excel Worksheet tabs

Do you think Excel sheet tabs overlap the wrong way round?

Here is a (rather poor) picture of an alternative:

People often seem to have difficulty deciding which end the first and last sheet should be. In this new version 6 would actually be the ‘top’ sheet. To me this seems to make more sense as it flows from left to right (maybe?). What do you think?

In fact I think the whole tabbed approach is inadequate for many of the spreadsheets I see. I think we need a much richer, more flexible way of navigating. Dick had a good review of a tree view based navigator over on DDOE a while ago. Thats more like it although I would like the ability to group sheets into various logical groups (like inputs, calcs, reports, or UK, Europe, US etc). Especially as people like to use plenty of characters for their sheets names (favourite recent one – ‘5% discounted projection’ (made formulas hard to follow)).

I have an indexing sheet on the codematic site that I often add to big models, but it would be nice if our spreadsheet applications did that for us in a more feature rich way.

Does anyone else feel we need a better way of navigating big models?

cheers

Simon

ETL tools

I’ve looked at a few ETL (Extract Transform and Load) tools, like what was Data transformation services in SQL server (DTS) and Hyperion Application Layer (HAL).
I’m sure they are very good, but I’m not totally clear what benefits they offer over normal programming.
As far as I can tell:

Positives:
Easier to pick up than programming(???)
Highly focused on manipulating data in various sources.
Easier to create user modifiable (parameterised) packages?
good integration with the data destination product.

Negatives:
Still need significant training to use them
Can only really be used for manipulating data
Vendor lock in

In the past I have used Excel/VBA as a ‘poor mans’ ETL tool, and have used Access for plenty of data cleaning exercises.
I recently replaced/extended a HAL process with a custom C# command line app, which seemed easy enough, so I wonder if I’m missing something about why these tools are better than custom .net apps.

Here is the C# to process a text file, replacing all occurences of ‘a’ with 0, it seems pretty quick, taking just a few seconds for a 17 Mb text file on my old laptop.
I probably should have passed in the strings – version 2 maybe.

using (StreamReader sr = new StreamReader(sFileName))
    {
     string s = sr.ReadToEnd();
     using (StreamWriter sw = new StreamWriter( fixedFileName(sFileName)))
     {
      sw.Write( s.Replace(“a”, “0” ));
     }
    }

I’m sure there is a grep like equivalent that will do this is a single line, but 3 or 4 lines in a general tool like C# is good enough for me, and easier than finding that command line app. Likewise via the gift of ADO doing all the DB stuff was only a few lines of code for each operation.

Do you use ETL tools? If so why? what benefits do they offer over ‘standard’ programming?

Maybe its a question of using general development skills such as programming, ADO, SQL etc versus using one (ETL) vendors ‘simplification’ of those things?

cheers

Simon

Push pull doors

Heres a picture of my favourite door handle design.
its got a 30 x 100 sign saying push and a 20 x 200 handle screaming PULL.
Most ‘normal’ people try to pull the handle because its such a well understood concept. But for people who cant read like young Mark in the picture – they have no chance. At 5 he can’t yet read the 4 letter sign, but he totally understands you pull handles that look like they should be pulled.

This push/pull handle is a commonly used example (and a very good one) of usability, or lack of it.
In the crap web app I was on about recently, to delete a number you have to over type it with ‘0’. There is a remove button, but it is never enabled. To me this is as bad as the door handle.

I often find when I get someone to sit down and use something I have written, they go through this pull/push hassle. I am always glad I am there to witness it, and get suggestions for improvement. In general, these days I get the users to specify the text that goes into dialogs and message boxes.

What examples of misleading interfaces have you noticed recently?

cheers

Simon