Archive for the ‘Excel’ Category

VBA test exercise

Tuesday, 6th February, 2018

I have just been offered the opportunity to do a VBA test (“should take no more than 3 hours”!) for the chance to get an interview… for a 3 month contract!

I didn’t find out how many had been selected for the ‘opportunity’. I could have been 1 of 2 or 3, or 1 of 2-3,000. I would guess 10-20? that’s a lot of people wasting 3+ hours

I told them to do one obviously, but I couldn’t help taking a peek at the exercise and scoping it out (wasting a ton of time which I am annoyed at myself for doing, but there you go).

I could do a pretty half arsed job of it in 3 hours, but to do it properly and considering some of the ‘peripheral’ elements (date integrity, error handling etc) there is no way I could do it to what I would consider an acceptable standard in 3 hours. Add the fact that some of the specs were wrong (do I do as per spec or what I think is correct?) and it was at least a full days work.

Bearing in mind this is a test, that will be reviewed so needs to look the part not just functional.

Perhaps if I had ready to go VBA libraries and components I could cobble them together within the timescales. But who has loads of libraries they actually have the right to use? I have a lots of stuff from all the codematic and xlanalyst stuff, but the bulk of the code I have written belongs to clients. (and some of the more useful stuff appears to only be in C, C++ or C#)

The other red flag was advice to do it in a object oriented way. IN A NON OO LANGUAGE FFS!

This may be contentious, but Object Oriented in Excel VBA is utterly retarded. Fact!

(There may be some exceptions but if you are combining data and operations in VBA ‘objects’, what are you using the Excel grid for? Or are you duplicating all that data?)

So I have a few problems with this company’s recruitment process:

  1. Its unduly burdensome on the potential candidates
  2. The timescales are for an amateur standard hack
  3. pre-built components may not be owned by candidates
  4. OO approach smacks of boys playing at being men.
  5. Spec was vague and incorrect
  6. Required effort will put off many candidates
  7. Some of whom might be ideal but too busy to risk 3+ hours
  8. 3+ hours for a chance of an interview for a 3 month contract is a very poor risk return compared to the rest of the job market.
  9. 3+ hours feels more like they want hobbyists rather than professionals.
  10. lack of respect for candidates time doesn’t bode well for the future role

I mentioned it to one of my contractor mates: “for free? fuck that”.




Cutting Excel out of your process

Thursday, 23rd November, 2017

Some CFOs keen to see the end of Excel.

Why don’t these people understand? Excessive Excel is a symptom of crap systems. Normally caused by decades of underinvestment in IT and piss poor IT McMissManagement.

They made a cynical strategic decision to cut investment in IT and let end user ad-hoc analysis take up the slack. Now they want to eradicate (well hide) the symptom (well evidence).

And should the systems eventually catch up with requirements (even a little bit), those users have so little confidence in IT after decades of under delivery, and unreliability that they will continue to prefer their existing proven approach. surprisingly! (Thats assuming they are ever allowed sufficient access to do their job of course).

Anyone who thinks outsourcing and offshoring your IT will help this Excel eradication process, is a dumbarse. Not quite as dumb as the people who think the solution is a crack team of overpriced under-experienced fresh graduates posing as consultants, but nearly.

The obvious solution is a big integrated system, like say SAP. A couple of spotty yoofs should be able to get that up and running in a few weeks and it will solve everything. Even cheaper – get someone in a different timezone to do it. I would do it for them but I am tidying my sock drawer that day.

Its not a technology thing, putting your shitty inadequate systems in the cloud just make them less secure, and more available. And who needs more availability of crap data and irrelevant analysis?

The actual solution is a minimum of 10 years of high quality IT management to implement fit for purpose systems. So start today and in 2027 you will be sorted, call it 2028 to give you a few months to recruit that high quality management.

Is VBA a dead end ?

Thursday, 28th September, 2017

This topic came up on Excel-l, I got shouted down on there, so I thought I would elaborate on my views here. (its mainly an Excel view but I don’t think the other MS Office apps are much different.)

I see two sides to the VBA story – the technology and the career.

Technology wise a VBA solution is a dead end – it can’t be run on a server (officially), it can’t be run in a browser, it can’t be run in a high performance cluster, it can’t be run on mobile devices. This means it can’t be easily scaled if more users or geographies must be served. You can’t really even have multiple simultaneous users of an Excel VBA app. With the relentless march to cloud and mobile, VBA is left behind.

Of course MS won’t remove support for VBA, the world would fall apart, well the finance, pharma, and energy industries at a minimum. But the editor is pitiful by the standards of this century. It is testament to the work of those MS guys in the ’90’s that the debug Edit/continue is still better than nearly all mainstream IDEs. But its still out of date and hard to extend.

MS can’t invest in VBA because their strategy is .net/cloud/browser blah blah. They had the chance to fight on their strengths – rich client, but instead they tried to be google. And lost. Now we are all struggling with crap web sites in bloated browsers with slow Win3.11 style interactions from 1995.

VBA hasn’t suddenly become bad, its still very good at what it does, its just that we are often now looking for more from our solutions. More users, more device types, more threads, more whatever.

So its not dead but it is basically limited to single user apps on their individual workstations. But I am sure it will continue to work for a long time.

From a career point of view, what else is like VBA? nothing really, so expertise in VBA doesn’t easily lead onto anything else, hence its a bit of a dead end.

VBA is not really object oriented so it doesn’t lead to the sort of object based designs that C# and Java are suitable for. Its not functional so it doesn’t lead to F#/Scala.

Of course you can write VBA in classes, but lack of implementation inheritance hobbles your solution. By the same token you can write VB style ad-hoc procedural code in C#/, but you would be missing out on many of the elegant OO features of .net. You would struggle to implement most modern design patterns in VBA, and the latest CV must-have MVC is also all but impossible.

From a functional point of view (probably more useful than OO these days) VBA can’t treat functions as data smoothly (although CallByName does better than many languages). And a little toot of Application.Run can ease the friction too.

In Excel VBA especially we can get an awful long way without worrying about user interface/user experience or data structure. This is great for your current users (fast development/deployment) not so good for your career development – no other system is so well integrated as Excel/VBA. No worrying about Restful APIs, onClickListeners, Asynchronous callbacks, all very powerful, all generally avoided in VBA land.

If you love VBA, and why not, then continue on, you are probably adding untold business value with the work you are doing (and perhaps a little future maintenance pressure for someone). But I’m not sure it will take your development career too far, business career possibly but IMO it won’t help you much as a developer.

If you are doing VBA now and thinking of progressing to other development technologies then consider carefully how you will make your next steps. Adding Access and SQL to VBA is completely doable, adding modern scripting, web technologies or .net/Java is much tougher.

Getting into .net via is a possibility, but, I think it would be easier to go straight to C#, although the .net/Excel story is still, in 2017 very ugly.

ExcelDNA might be a good bet if you want to inject a bit of .net into your Excel life. I found this approach much more viable than VSTO infrastructure hell. Somebody somewhere is maintaining a lot of both that I developed. I bet the ExcelDNA maintainer is having more fun.

I also managed to blag my way into writing a load of custom Java on Essbase, that route probably isn’t open for many though.

Which led to my new focus on Android which is both challenging and rewarding. Although stay posted for news on that…

btw this is my 1,000th post on SOS, I assume WordPress will give me some superficial badge of honour, reward or ‘achievement unlocked’ bollocks, in the modern way.




10 year birthday

Sunday, 12th February, 2017

I missed it obviously but one of the kids pointed out I have been blogging here for 10 years! ken ell!

(as of January just gone)

I can’t imagine dragging it out for another 10, unless some spreadsheet miracle happens and I am sucked back in. I still think spreadsheets are brilliant at a great many things, I have just given up waiting for numb nuts management to realise.

I built a random mental maths test generator for the kids the other day. I looked at doing it as an app, as a web thing,  but in the end I did it in a spreadsheet in about 10 minutes. The kids hate it!

I love it!

(it even has a bit of VBA in it – woo hoo)



Excel Conference in Amsterdam

Tuesday, 5th January, 2016

I just saw this (on linkedin of all places – I only go there twice a year).

Dunno much about it, except its being run by a gang of well known Excel experts so should be very excellent.

I won’t be going as Excel is basically dead to me these days.

If you are going, have fun.





Excel Modeling World Championships 2014

Wednesday, 27th August, 2014

I saw this last year and thought it was a great idea, and have just been prompted to mention it.

Here is all the info

Big wedge for the winner…

And the fame of course…

ciao, peeps

Excel Dev book

Monday, 6th January, 2014

Thanks for all the input folks.

I had a good think, and although it feels somewhat like a waste not to share some of my most useful experiences, I don’t think now is the time for the book I have in mind.

There are several factors depressing the Excel development market at the moment, some are permanent trends, some I think will reverse in a year or two. In particular there is a heavy regulatory downer on Excel just now, once people get caught messing up in the replacement technologies that focus will move off, allowing people to get back to the benefits of Excel.

Also IT departments currently have a big veto power, I think once people notice that that doesn’t actually move the business forward their influence will decline, allowing people to get back to the benefits of Excel.


On the other hand the time may really be up for professional Excel development.

Either way I’ll still chip in here with points of interest.



Excel Dev Book

Thursday, 19th December, 2013

I keep wondering about writing a book about Excel development.

It would be less technical than PED, perhaps a bit more like Code Complete for Excel. A bit more design based than code based. The target audience would be business folks wanting to improve their Excel clicking and IT folks needing to target Excel. It would be set in the context of the reality of working with Excel in big companies.

I did discuss it with a publisher a while ago, but at this stage I would probably do it as a self publish e-book, with maybe a print option.

It would be based on my couple of weeks Excel experience (cataloguing my CD collection), and would touch many complementary technologies like ADO, ExcelDNA, XLL+.

I’m thinking more of a 300 page wordy tome rather than a 1000 page screenshot fest.

The sort of chapters might be something like

  • strengths and weaknesses of Excel
  • The RAD process with Excel as the client
  • Excel dev models (workbook with VBA, Add-in etc)
  • Excel grid best practices
  • Excel facts and fallacies

The only thing stopping me is the apparent death of Excel as a serious business tool. I’d hate to invest all that effort and then find my mum is the only person willing to buy it. (Well, her and as an excellent stocking filler for my kids at Christmas (not this one of course!)).

There is no doubt in my mind that sensible use of Excel is good in every way for most organisations. Sadly its the bad use that is most common and gets all the bad press. One aim of the book would be to propose some of the smart ways of using Excel (including using some of the newer features).

So my question is:

If there were such a book do you think there would be a market for it? Do you know people who would buy it?

(I know, that’s two questions)



Quant jobs

Wednesday, 18th December, 2013

Where have they all gone?

A couple of years ago Excel + VBA + C++ and a bit of maths and the world was your oyster.

Last year it was all HFT which is just nerd tastic engineering stuff.

This year it has been mainly risk and regulatory control.

Are there Quants any more? what tech are they using? how many mid and back office people are they carrying?

I keep wondering about doing a masters in financial chicanery, but I think that boat has sailed. Probably safer going back to furniture making.

Is the all the quant stuff really only done in Matlab, R or SAS now?



Some other Excel Jobs

Friday, 13th December, 2013

Lets say a decent permie rate for Excel work is 60k

Contractors are more efficient as they don’t have all the 1-1 reviews and career planning bullshit, and are paid a risk premium for being easily sackable so lets say 100k pa or 400 per day. (In this case, via a decent agency the client would probably pay less than 500 per day.)

A good contractor can work most of the year so can approach the 100k total, less holidays (no sick time – everyone know contractors are never sick).

Employment law in most countries is completely bolloxed up meaning the party that wants a flexible skilled resource and the person willing to provide that flexibility and shoulder some operational risk have to jump through all kinds on hoops to achieve their joint aim. Hence agencies and a whole other bunch of buggeration.

Worse than agencies are the body shops, these ‘consultancies’ exploit dysfunctional employment rules to weasel into these flexible requirement gaps.

In the above example, they will take on the ‘permie’ (on a fixed term contract normally) on the 60k, or most likely take a more junior person on say 40k. They then pimp them out to the client for 1,200 per day. They can do this because the only thing more fooked up than employment law is modern procurement practice and the cursed ‘preferred supplier’ scam.

If they can’t sucker someone into a fake permie role (of course they promise to pay you even when you are not charged out to a client, but that won’t last long before you are dumped), then they will try and sucker a contractor in by offering slightly better than the 40k pa, like 200-250 a day instead.

A normal agency adds no more than 20% to the workers rate, these clowns are adding 300% or more. I won’t mention any names, the evidence is clear on the jobsites.

It seems that a few of those missing 400 per day Excel contractor roles are now 200 per day roles so some body shop can make their kilo of flesh, and blood. (Bear in mind the cost to the client has increased in this scenario – and the quality of service has generally declined). But procurement are happy as they have less 2 pound supplier credit checks to do. The banks aren’t generally dumb enough to fall for this but government departments lap it up.

(Sadly this pointless bodyshop approach is the standard in Switzerland, hence why I am looking Europe wide (had a sniff of a Dublin job last week – Guinness at Keoghs every night? – tempting!!))

Are you consulting in the public sector carrying an astronomical, unjustifiable bodyshop overhead?

Have you seen these sort of job ads?