Archive for July, 2010

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?



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

Oh the Irony

Tuesday, 20th July, 2010

Anyone who has followed Dick Moffat and I bemoaning the decline of the Office developer will appreciate our frustration at the lack of development of this important market niche, and our gentle finger wagging at Redmond as the only organisation with the credibility and resources to drive worthwhile change.

(I have for a long time harboured the desire to help create a organisation of professional spreadsheet devs, but whilst its a nice idea I can’t see it gathering the required momentum without some serious backing. )

But this last week has been a real eye opener. Firstly, conference-wise it is clear our skill set is very very much still in demand (general systems, Excel, VBA, .net). The latest from the City is a great all round Excel/VBA/SSDLC/Csharp (you find that hash char on a macbook running Linux on VMWare) can command 1,000 gbp per day, and good ones are being poached left right and centre. It also seems many organisations are setting up Excel centres of excellence to drive up spreadsheet quality standards.

Secondarily, my inbox is sagging under the demands for Excel consultancy, especially xll/C API stuff. Sadly I am already flat out trying to resurrect my blogging career ;-)

Thirdly, Jobserve the barely useable IT jobs website is brimming with Excel related jobs at all levels of experience.

I find it very amusing that our skill set is not that popular, few people are moving in to it, few people regard it highly, but those that do, really do.

We are becoming the master of the skills no one wants but everyone needs. perhaps like septic tank experts??(not cockney RS).

So the message seems to be if you like Excel based work there is a good living to be made, even though not many people will give you any credibility. How long term that might be is an interesting issue. MS managed to kill VB off in a few short years, but VB was more a dev tool than a business tool. I don’t think MS have the requisite influence with the business users that are driving Excel/VBA demand. Business users will happily hold back on working versions rather than feel the upgrade pressure that Visual Studio devs feel.

How do you see things currently? and in the next 3-5 years?


ps I found the # – don’t know yet what I broke getting it working

Hi, Remember me?

Sunday, 18th July, 2010

I used to post here quite regularly.

Last week was mad busy.

I took 4 easyjet flights in the week. the combined travel time was about 6 hrs of flight. The combined delay time was closer to 10 – each flight was at least an hour late, the worst (the one that did an emergency stop on the runway) was 4 hrs late by the time they had flown us a replacement plane over from Scandinavia instead of our smoking one. Its not easy being an international jet setter you know…

Anyway Monday was the International UK Excel Developer Conference in London.

It was an excellent event, just over 20 of us altogether, about half a dozen in the pub before and dozen after.

The day went fairly close to schedule and the feedback has been very positive.

I did a session on general extending Excel technologies, Ross did a more indepth session on Excel DNA. Then I opened the kimono on the realities of being an Excel Add-in entrepreneur, as did Charles. I combined mine with a little look at OLAP data sources, because Roger agreed to show us some of the new features in 2010, in particular the pivoting improvements and slicers.

Then we went to the pub for dinner which many delegates washed down with their own body weight in beer so the afternoon was a bit drowsy for some.

In the afternoon I looked into xlls in some more details and covered some of the other tools such as Keith Lewis’ XLL library, XLW and XLL plus, and some of the criteria for choosing between them. Charles then took us through some of the other considerations for FastExcel and I showed VSTO and Addin Express.

We ended the day with a full and frank discussion about where we see the future of Office development going. With great participation from everyone there. some really interesting views were expressed and I picked up a few things I hadn’t considered before. More on this later though as I picked up even more insight towards the end of the week – more in a future post.

We kind of combined the panel sessions with a big free for all, and the prize giving was cancelled due to a logistics blunder which meant the prizes our friends at Microsoft had sent from the US (some time ago in fairness) did not arrive in Carlisle until the day after the conf. they probably passed us in lndn on the Mon. Luckily all my sessions had overrun so much we had run out of time anyway. The pub after the event was great – in many ways this informal socialising with others operating in the same areas is the highlight for me personally.

The venue I thought was superb and I would definitely use them again. The aircon was most welcome, as anyone who was at the very first Lndn excel user conf will remember.

Thanks to everyone who attended, I hope you found it useful. Any feedback or suggestions for improvements or future sessions or speakers are very welcome.

The current plan is to do another similar event perhaps next May time, or possibly hold it the day before Eusprig on a Wednesday in July. Your thoughts are welcome.



which add-ins?

Tuesday, 13th July, 2010

I got a little fed up poking around trying to work out which version of which addins are loaded where.

I found some great starter code here (thanks guys). And added some stuff.

Its here so anyone else can use it (and I can find it wherever I happen to be), and if you have any suggestions then please leave a comment.

Sub ListAddins()
Dim oAddin As AddIn
Dim oCOMAddin As COMAddIn
Dim wb As Workbook
Dim sAddins As String
Dim RegisteredFunctions As Variant
Dim i As Integer
Dim j As Integer
Dim sd As String: sd = “|”
Dim p As Object
sAddins = “Standard Add-ins” & vbCrLf & “================” & vbCrLf
For Each oAddin In Application.AddIns
sAddins = sAddins & oAddin.Name & sd & oAddin.FullName & sd & oAddin.Installed & vbCrLf
Next oAddin

sAddins = sAddins & vbCrLf & “Standard wb Add-ins” & vbCrLf & “================” & vbCrLf
For Each wb In Application.Workbooks
‘If wb.IsAddin Then
sAddins = sAddins & wb.Name & sd & wb.FullName & sd & vbCrLf
‘End If
Next wb

sAddins = sAddins & vbCrLf & “COM Add-ins” & vbCrLf & “============” & vbCrLf
For Each oCOMAddin In Application.COMAddIns
sAddins = sAddins & oCOMAddin.progID & sd & oCOMAddin.Description & sd & oCOMAddin.Connect & vbCrLf
Next oCOMAddin

sAddins = sAddins & vbCrLf & “Xll Add-ins” & vbCrLf & “============” & vbCrLf
RegisteredFunctions = Application.RegisteredFunctions

If Not IsNull(RegisteredFunctions) Then
For i = LBound(RegisteredFunctions) To UBound(RegisteredFunctions)
For j = 1 To 3
sAddins = sAddins & RegisteredFunctions(i, j) & sd
Next j
sAddins = sAddins & vbCrLf
Next i
‘do nowt
End If

sAddins = sAddins & vbCrLf & “VBA Projects Add-ins/wbs” & vbCrLf & “============” & vbCrLf

For Each p In Application.VBE.VBProjects
sAddins = sAddins & p.FileName & vbCrLf

‘MsgBox sAddins
Debug.Print sAddins

End Sub

I can’t imagine for a minute that will copy out right, but I’ll load a proper version on Codematic when I have incorporated all your suggestions.

I know for example if you have a few market data apps loaded the xll reg functions list will exceed the immediate window capacity – so I just comment that out.

Improvement suggestions welcome, especially with code…


One week to go

Saturday, 3rd July, 2010

One more week to the Dev conf on Jul 12.

The effective use of capital award goes to Alan for getting his order in on the last day of early bird discount yesterday.

The price is now 200 quid which is still cheap for a full day of hardcore Excel developer content.

Sorry the posts are a bit quiet on here at the moment – I’m busy preparing my sessions for the dev con.