Archive for the ‘error’ Category

Any of you buy into FacePlant ™?

Thursday, 2nd August, 2012

I just happened across a comment that it is now trading down below twenty having IPO’ed at 40 odds. No wonder so many execs are getting trampled in the stampede out the door.

I can think of more fun ways to lose half my money.

GB comes to mind…

I spent a lot of my money on booze, birds and fast cars – the rest I just squandered.
George Best

I also read about the Knight destructacon algo disaster, potentially driving them titsup. I am sure this was a ‘proper’  HFT algo tech, not some Excel/VBA monster. But the effect seems pretty similar (Google Dr Evil spreadsheet). Please, please, please let the Knight rider ‘system’ be called KITT.

In a similar vein I also spotted that UBS are suing NASDAQ for their part in the FacePalm debacle. (Or FaceBerg as it is now known). Presumably they ended up owning too many of these citrus shares.

So its been a good few weeks for crap systems, no national spreadsheet horror stories though (Unless you include the Olympic ticketing/empty seat fiasco, which we were told last year was spreadsheet based).

What little spreadsheeting I have been doing recently has been Gnumeric, I can’t remember the last time I fired up Excel (or Windows in fairness).

I am still resisting the pressure to participate in Facebilk, I think they will probably rename it to facespace as people leave in droves, and evidence emerges of millions  of fake accounts. Too old for faceache, too young for linked in, what should I do?

On the investment front I guess you could always try to recoup your FB losses with MS shares ready for the Win8 effect? (buying puts of course ;-)).

cheers

simon

Advertisements

Academic and commercial spreadsheet errors

Thursday, 22nd December, 2011

[I just posted this on Eusprig – but I suspect it is too long to hold the interest in a list post]

I think there is a total chasm between
a. academic researchers whose main spreadsheet experience is the classic ‘student grades’ thing and
b. business spreadsheet jockeys who are in spreadsheets all day everyday.

group a think several hundred formulas is big, group b think several thousand is small.
group a think most commercial spreadsheets have material errors, group b rarely see any error effect.
a think b are over confident, b think a are inexperienced.

Within Eusprig I think we need to find a way to reconcile and explain these two completely opposed views of apparently the same thing. Otherwise neither side will ever gain any credibility from the other.

Personally I don’t believe many commercial spreadsheets have material errors, because most commercial spreadsheets are immaterial. They are a small piece of a bigger effort.

Yes I have seen spreadsheets wrong by millions, and 10+ % or whatever you want to call materiality. But did it change anything? no, not ever.

In a billion dollar, multi year, deal evaluation model, a multi million formula error can be dwarfed by inflation or interest rate assumptions. But whatever, if the price comes in at 1 billion and the client only wants to pay 900 million, then the whole analysis, errors and all, is largely irrelevant. Now the question is ‘are we prepared to take the risk that we can deliver this and survive for 900m?’ or slightly more cynically ‘will they ever tie cost overruns back to me and take back my bonus?’

In my experience spreadsheets are normally one of many inputs to important decisions, any inputs out of tune with the majority are either reviewed for credibility or rejected.

So I agree that most spreadsheets have defects, and I agree that very few lead to an erroneous outcome. And I agree that this is the Human element of spreadsheet interaction, ignored in much academic research. I also believe that the big issue is wasted time and effort, around ineffective spreadsheet use, not error impact.

Maybe we need some more holistic research that covers the whole person/spreadsheet system (in a commercial setting) rather than the spreadsheet in isolation.

I would highlight that in my experience when a spreadsheet changes hands (for holiday cover, job role change or whatever) there is a huge spike in wasted time and risk of nonsense outputs, and external support requests.

What’s is your experience? have you also found that the complete information system that includes these potentially erroneous spreadsheets is usually somewhat self healing? (and self learning – ‘x in reporting is useless, I now ignore everything they send me’)

cheers
simon

Code execution has been interrupted

Thursday, 23rd June, 2011

by whom?

not me!

I had this issue a couple of weeks ago, but for some reason (possibly an update) it seems to have stopped.

As my code was running, for no apparent reason this dialog kept popping up. It seemed especially prelavent when writing data to cells. In the end I had to wrap all my writes with

   ‘Application.EnableCancelKey = xlDisabled
   wsLogging.Cells(m_outrow, col).Value = wsLogging.Cells(m_outrow, col).Value & ” | ” & message
   ‘Application.EnableCancelKey = xlInterrupt

Anyone else bothered by this? I was going to try cleaning the code, but now it seems to have gone away. Or possibly it was someone elses code, and/or some interference from another addin? Whatever, I can’t seem to repro the behavior anymore.

If it makes any difference I am using a multilingual Office (2007 –  12.0.6550.5004).

so I am curious, did anyone else see this problem? did you work out what caused it? did you find a better workaround? has it fixed itself?

cheers

simon

Eusprig 2011

Friday, 17th June, 2011

This years spreadsheet risk and quality extravaganza is almost upon us. 

It is exactly just less than a month away in mid July.

You can book here.

I am not presenting this year, as I thought I would let someone else have a turn speaking (and of course I missed the submission deadline).

In fact I probably wont be attending as I’m not sure where I will be working/holidaying then.

I would be expecting a good talk from Patrick as we worked together this year on a few spreadsheet related projects. Indeed he came face to face with the source of several of my formula horrors from previous years!

oh looks like he is not presenting this year, but on the bright side there is some more original research on the power (or not) of range names, amongst other interesting papers.

Here is the (current) draft outline schedule.

Are you going?

Cheers

simon

Linguistic gymnastics

Saturday, 11th June, 2011

As I’m sure many of you know I am a fully fledged European, living it up in a multi-country, multi-culture, multi-language, (limited understanding ;-)) kind of a way.

At my current role I am running the multi language version of Office in English – which is pretty good (apart from Outlook obviously, that does not respect language settings properly). I have Windows set to English interface too, and that works well too.

Google and its spooky spammy ad network seem completely unable to respect my language choice. I would never notice this as I usually run an ad-blocker but here at work we get the full pointless hit. I had no idea the internet was so infested. Even on my blog here I have seen ads, I didn’t think it was popular enough to warrant it.

But anyway I just wasted a couple of hours of my life trying to understand why some VBA wouldn’t work.

Its something I have done 13 1/3 times before with no problem, but here, today, no go. writing a formula from VBA to a cell – no bigee.

ActiveCell.Formula = “=CONCATENATE(….”

I built up my string carefully using an in cell version as a model. I copy pasted it into a cell no bother. And yet when I tried to run it, it errored. With the ‘oh so helpful’ ‘Application or object defined error’. It would have been easier and more honest for that to read ‘Error, tough shit’

So anyway, I wondered if it was an R1C1 thing, I have been burnt by that a few times, so I changed all my code to be R1C1 stylee, and tried to set ac.formulaR1C1, still no go.

I noticed it was a long formula, so I cut out a big chunk of stuff to get it in under 256 chars, still no joy.

Then I thought maybe I had too many arguments, so I changed my code to just do 3, still no joy. In the immediate pane (pain) I tried activecell.formula = “=A1”, worked fine no problem.

So then I asked another dev to help, then, as is often the case as soon as you ask someone else, it finally it hit me: with nl settings the argument separator is a semi colon ; whereas in en it is a comma ,. Edit replace ; with , and job done, my formula worked. VBA always talks to Excel with en culture. So my simple immediate window test formula worked fine, but anything with multi-arguments failed. there is a point there about making tests realistic I guess.

So there it is that’s what I did the other morning morning, I relearnt that I need to build formulas in VBA using commas not semi colons. Of course I could ‘just’ change the windows regional settings, but anyone who has done that will know what a can of worms that opens. for example VSTO add-ins wont install, CSVs wont import, and a million other things I have not been burnt by recently, no, regional settings is best left alone.

As a matter of interest I find it very difficult to get used to entering formulas via the UI with ;’s instead of ,’s too. nearly every time I get a slap for using a comma.

Have you had any multi lingual challenges recently?

cheers

Simon

Too much cheese

Wednesday, 30th March, 2011

Its deja vu-tastic round here at the moment, best cut back on the late night gorgonzola!

When C# was released it aimed right between VB and C++. VB was seen as lacking in a few areas, and C++was seen as too hard/dangerous/inefficient. C# slotted nicely in the middle.

Many VB devs made the move to C# and enjoyed the extra power and easy access the the .net framework.

Most C++ devs stayed away though, claiming it wasnt powerful enough. And lo over time all those things that made C++ hard have been added into C# so it now as complex as C++ ever was.

Imagine where we would be if, instead of creating a brand new language they had invested all the time money and effort into STL and Boost, C++ would be IT. whatever the question.

But at least they addressed the power issue in C#, and in 4.0 they have improved the Office interoperability.

And so to user interface.

‘No one modifies their Office interface so we have taken away the facility to do it’, oh hang on – that was Office 2007, in 2010 its back in – but very clumsy (could change).

Now, to me this adding back in is a big thing, because it shows a couple of vital things:

  1. Their user experience ‘data’ was misleading, and this is an acknowledgement of that (they wouldn’t add (back) a feature that ‘only 2% use’ would they?
  2. Microsoft can and do listen to their the community, and will make things right, even if ‘evidence’ does not back it up.

I think point 2 there is brilliant news. I just hope they can fully discredit the CIP ‘data’ and undo all the mistakes over reliance on it has led them to make.

But mainly I am pleased and re-assured they are listening – 2010 is shaping up to be an Excellers Excel.

cheers

Simon

Who nicked my data?

Friday, 25th March, 2011

1467 records went in

1453 came out

repeatedly

consistently.

I had great fun recently tracking this down.

The system creates an Access database on the fly, adds a bunch of tables and queries, populates it all then pulls the final answer of the stack of queries back into Excel. I could have done it in one SQL statement, but it would have been the size of a book.

Anyway when running it from Access everything was fine, when triggered from Excel someone nicked (a few of) my records!

Back to Access, everything fine, Excel wrong, Access still ok…

Eventually, after much digging, I remembered a previous painful ADO encounter. One of my queries was using the ‘*’ wild card which is fine in Access, but of course in ADO it is ‘%’!

One of my colleague then suggested a neater solution than the brute force approach I was about to commit:

SELECT blah blah from blah where (x Like “*fut*”) OR (x like “%fut%”)

This will work with either driver without the need for separate queries (the route I was considering).

Have you been burnt by this different wild card issue?

cheers

Simon

European Spreadsheet Risk Group 2010

Friday, 23rd July, 2010

We had another excellent Eusprig Conference last week – congrats to the organisers.

Lots of interesting discussions both within the sessions and outside, and of course in the pub.

Some highlights:

Sumwise is a new spreadsheet-alike product that allows more structured models, runs in the browser locally or remotely. It looked really good, I can see a whole class of problems that it fixes very elegantly.

EASA presented on their tech for publishing spreadsheets to web servers for browser based usage scenarios. Having built a few of these Excel-runners myself (I still have the scars) I appreciate what’s involved. I liked the way it would work with any spreadsheet and is not as picky as Excel Services (2007 anyway, 2010 is more accommodating).

ClusterSeven were talking about new value they are discovering for clients by tracking cell changes over time. They are able to build up not just validation trends, but also business, pricing, economic etc trends. I suspect converting all that unstructured tat scattered across the average spreadsheet forest into mineable information is more valuable, and a better sales story than the hunt for ‘potential’ errors, or mischief.

Dean Buckner from the FSA described their current views on data risk, and it close relation spreadsheet risk/end user apps. I always enjoy the clarity with which Dean explains what the FSA care about and how those things should be addressed. For example sometimes just a written policy is fine, for other areas the FSA want clear practical evidence.

There was some interest in trying to create a generally agreed set of best practices, with caveats as required. I’m not sure if this is something Eusprig will officially endorse/sanction, but I think its something they must if they want to maintain credibility. You can’t spend 10 years saying ‘what about the spreadsheets?’, and then offer nothing to help.

I was disappointed to miss some of the academic papers which ran on a different track. I am not a fan of the Eusprig 2 track approach. I don’t think there are enough people interested in this area to divide further, and I think the current conf length (1.5 days)  could be extended by 3 hours to allow the academic stuff, perhaps on the Friday afternoon.

So instead of hearing the evidence of how names can impair less experienced users we had a half hour slot about why a certain modelling company use names extensively. This was a little long on hyperbole and a little short on fact/evidence for me. And it unfortunately failed to address all the real world scenarios that make many experienced commercial devs wary of names in the real world.

My favourite (repeatable) quote of the event was actually just after
Ralph Baxter the CEO of clusterseven was explaining some of their new features/use cases to me as we ascended some lift in the tube system. As we got the street level some bloke turned round and said….
Drum roll please…
“Ralph thats the best elevator pitch I have ever heard”
That bloke turned out to be Mel Glass from EASA, we all then spent the next hour discussing the harsh reality of corporate spreadsheet use. (And some of the opportunities around at the moment)

One of the people pushing for some generally approved spreadsheet techniques was Morten Siersted from F1F9. Of course we will never all agree about the minutiae (note the interminable named ranges debate). But it has to be better to have reviewed a well thought out approach and decide where you will adopt and where you won’t and the supporting reasons.
FAST is one of these well thought out approaches, and its free/open source, non commercial etc etc. And unlike some of the others, Fast stands on its own. there are no chargaeble tools required to implement or test it.

Its here.

I’m not sure where the best place is to discuss it, but I do think we should discuss it. I’ll maybe do a more in depth post in the next week and we can discuss it there, or if FAST put up a discussion blog post that would be even better.

I’m not sure which is the most contentious, climate change or spreadsheet modelling/developer standards?

We’ll see I guess.

Did you go to Eusprig? what did you think?

cheers

Simon

ps I managed to use the é and the è on my Swiss keyboard today.

Eusprig 2010

Tuesday, 22nd June, 2010

Pack yer party frocks its nearly time for Eusprigs annual conference.

In my opinion this is a must attend event for anyone serious about professional spreadsheeting. It should also be required for anyone contemplating spreadsheet management/migration/quality/control projects. Why make all your own mistakes when you can invest 2 days, a couple of hundred quid and learn from others, bypassing a heap of pain (and cost) for your own organisation/project?

There are two key aspects

  1. The technical conference content, including several schools of thought on best practice (not sure whether to bring my cornering kit, or my gum shield and boxing gloves) spreadsheet quality project post mortems, and lots of original research. And not just wishy washy stuff – proper, well designed experiments that really focus in on key issues. I am particularly looking forward to the slot demonstrating that names make spreadsheets harder to understand. I’ve had plenty of rows on that very topic.
  2. Social/networking – where else do you get the opportunity to buy drinks for big cheeses at the FSA, and big cheeses from the spreadsheet audit/management software vendors, and student bludgers? At least that’s how I remember it.

Full conference details (including dates: Thur/Fri 15th – 16th July, and venue: Greenwich Uni London, England, and agenda etc) are here.

I’m giving a keynote first thing Friday (which seems a bit harsh!).

Why not combine it with the Excel Dev Conf for the full spreadsheet-oholic effect?

Are you going to Eusprig??

If not why not? (serious question) – what would need to change for you to attend?

cheers

Simon

Comedy support

Wednesday, 16th June, 2010

I had a call from a user the other day about a report that had stopped working.

I had a look at the VBA, not bad, but damn hard to follow (suck a loads of stuff into a random set of arrays of various dimensions, shuffle it and miraculously drop the right values into a range elsewhere.) well normally the right values – now it wasn’t working right.

I adjusted the caller parameters to fit a minor change in the data, tested it and had no clue if it was working. (It’s that type code that gives you no clue what its doing just presents the answer (Paul Daniels stylee)). I sat with the user to understand what it was meant to do. Lots of buggering around and a few back and forths, until it finally dawned on me:

The last few rows were being cut off by the print area!

Change the print area, user happy, ticket closed. Only about a bazilion hours longer than it should have taken – d’oh!

I truly can’t remember the last time I printed out a spreadsheet, and thats my excuse and I’m sticking to it.

Do you print out much stuff?

cheers

Simon