I received a comment on one of my older posts about interviewing for an Excel VBA job, asking about possible interview questions. I’ve been on both sides of the interview table, and I enjoy interviewing, and I’ve thought about this quite a lot. So I thought I’d write a more thorough reply, and make it a full post instead of hiding it in a comment. Maybe others can find this useful, too.

If I was conducting an interviewing for an Excel VBA, I would probe you from the following directions.

One: your general attitude towards and approach to programming. I’d ask you to write code to solve some simple generic problem that shouldn’t take you much more than 15 minutes. It could be sorting an array, or printing a checkerboard pattern of 0s and 1s, etc. I’d check that you have meaningful variable names, that you declare your variables (which in VBA you don’t necessarily need to do), that your code is reasonably well-organised etc.

Two: your knowledge of Excel. I’d want to see that you can use array formulas and pivot tables, lookup functions, named ranges, etc. You can’t be a good Excel VBA developer if you’re not a good Excel developer. I would give you a laptop and a few actual problems to solve, and leave you to it.

Three: your knowledge of the Excel object model. I’d ask you to write code that manipulates a worksheet – for example, sort and filter a range, or clean out duplicate data from a list, or consolidate data from multiple files. I’d look for knowledge of and ability to work with fundamental Excel objects such as Workbook, Worksheet, Range etc; also I’d definitely want to see that you don’t write “macro recorder”-style code with Select and Activate everywhere.

Four: your knowledge of how Excel VBA works. I’d check what you know about events (“Can you write code that runs every time the worksheet changes?”), about user-defined functions (“What is and isn’t allowed in UDFs?”), volatile functions (“How can you ensure that your UDF is volatile?”, “Which built-in Excel functions are volatile?”).

Five: questions specific to whatever the job is about. If it’s at a bank, I’d want to see you do some basic financial calculations. If it’s a database reporting job, see if you can work with ADO and basic SQL, or interop with Word, or creating charts in VBA, or whatever else is relevant.

This is quite a lot and might not all get covered in the first interview, but I would ideally want to go through all of this before deciding whether to hire you.

Personally I’d prioritise the parts just as I’ve listed them here, most important first. But I’ve been to interviews where they do almost the opposite – I once got a 10-question multiple-choice test about some nitty-gritty stuff in Excel but they didn’t care about what my code looked like. So you never know.

The reason I’ve put “knowledge of Excel” above “knowledge of Excel VBA” is that in my opinion, the latter is more straightforward and easier to learn. It’s just code, and it is reasonably well documented. It’s harder to find people who are good at writing efficient Excel formulas. But often formulas can yield a far more efficient solution – faster by several orders of magnitude. If I have a choice between formula and code, I’d almost always choose the formula.

John, I hope you find this helpful, and good luck with your interview!

It’s been a while since I said anything about work. It’s not for lack of activity, or of interest – work is as busy as ever, and I am really enjoying it. I guess there just haven’t been any individual events that stand out or prompt a post.

It really is the best job I’ve ever had. Not that the previous ones were bad… With one exception, I’ve felt the same about all my previous jobs at this company: each one was better than the previous one, either more interesting, or more suited to my skills. (The exception was the job that made me leave the firm after 4 months.)

So what is so good about it? The best thing is that I work with other developers. While I spent at least 50% of my time writing code in my last job, I was the only member of the team whose main focus was code. And I was by far the most experienced “developer” in that team – even though I had no comp sci background, I had been coding for several years, whereas the others had done little or none. Also, I was the only one really interested in software development. This meant that I had no one to discuss my projects with. No one to ask, How should I design this? Does this look like a good solution? How could I improve my code? Now, on the other hand, I sit right next to people who are interested in software development, know a lot about it, and are willing to discuss it. Design decisions, small and large, are discussed before, during and after coding. As a result, I am learning more, doing a better job, and enjoying the process a lot more.

Working in a team imposes its restrictions on me, and most of the time, that is a good thing. Too much freedom can easily become lack of direction – a certain amount of discipline and control actually make life easier. When I was the only developer – either in my previous job, or in my hobby projects – I could more or less do anything. Now I need technical specifications and documentation and class diagrams, and maybe time estimates and prototypes. I need to make design decisions upfront and can no longer change my mind whenever I feel like it, because other people’s work depends on what I do, and what I have said I will do, and what I have said I will need from them. I’ve sometimes found this hard and unnatural: my preference is generally leave my choices open, to start working and let the details emerge as needed. But on the whole I think this has led to better-quality work.

The other great advantage of working in a technical department is the sheer breadth of experience to be gained. I used to work with Excel VBA at work, and VB6 ⁄ VB.NET at home. Then SAS was added to my toolbox at work, and that was a major decision for the group and a major change for me. Now I’m regularly reading and/or writing bits and pieces of Java, C#, VB.Net, and SQL, interleaved with command scripts, VBA, XML schemas, and proprietary languages, plus support tools like source control systems, class diagrams, change management systems etc. While I can’t call myself an expert in any of these, even just having exposure to such a broad array of technologies is both fun and educational.

I’ve been in this job for four months now, but it feels a lot longer than that, because of how much I’ve learned. I am looking back at projects I did a year or two ago and can’t help thinking that I could do them so much better now.

Now that I’ve moved to a new team, the old team needs someone to replace me. I spent most of this morning interviewing candidates for the job.

The main requirements are reasonable knowledge of finance and financial markets, and good knowledge of Excel VBA and SAS. We’re prepared to (and will probably have to) relax this somewhat and just pick the person who’s closest. I had hoped to find someone better than myself, but it looks like the combination of skills we’re after is a rare one.

The applicants (four of them) have been a mixed lot. Interestingly, three out of the four where Australians. Looks like this is the thing for young Aussies to do: get an education, then travel a while, then spend a couple of years in London. (Our newly-hired team assistant is also Australian.)

Most interestingly, several of the CVs were factually correct but in reality misleading. Two out of four listed both SAS and Excel VBA prominently on their CVs, but when questioned about their experience admitted that they haven’t used them for years, and when asked to demonstrate their skills, they could barely manage the basics, if that. A third one hadn’t done any SAS work but Excel VBA was among the first-listed of his technical skills, and he supposedly had a programming background including C++, yet he struggled hard with questions that required him to write pseudocode for a simple problem.

I think it is almost impossible to evaluate someone’s coding skills without letting them actually write code. Almost anyone can make their past projects sound important, and present themselves as being central to the projects’ success. But when I ask them to actually do something, I can see what they’re really able to do.

Sample Excel problem: return the name associated with the highest value in a list. One candidate’s solution: hard-coded link to the cell with relevant name. Hopeless.
Sample coding problem: create a checkerboard pattern, with alternating black and white cells (or alternating 0s and 1s). Those who knew (or claimed to know) VBA got to write VBA code, others could write pseudocode or use any other language they liked. Only two got anywhere near a working solution.

(That last one I actually “stole” from one of my own interviews, when I was looking around for a developer job a month ago. I think it’s an excellent question because all it really requires is logical thinking, and you could write many variations on the basic theme.)

And what can one say about a candidate who, when interviewed about his financial knowledge, says that his real background is in programming so that’s his strong side, and when interviewed about his programming knowledge, points out that his recent jobs have all been finance-oriented, so that’s what he’s better at.

On the positive side, one of the candidates actually had a honest CV that reflected his skills, and was able to demonstrate those skills in practice. He doesn’t know any SAS, but if we could learn it then surely so can he. And he appeared to have a solid foundation – good coding habits, clean code, sensibly commented. (He actually brought printouts of his code to the interview, which definitely worked in his favour.) I’d rather hire someone with good habits and let them learn the language, than someone who knows the language but produces messy code. Unless we suddenly get a last-minute application, looks like he’s got the job.

First day of the new job. First impressions.

The team seems great. Relaxed, knowledgeable and helpful.

The hours are much more sensible than in the previous team. No one was there before 8:30, and by 18:00 most people were leaving. Which is a nice change compared to the 7:30 to 19:00 I’m used to.

The move itself was a shambles… My things, which were supposed to be moved yesterday, weren’t, so I spent the first half of the day with nothing but a desk and a computer. Then the admin team messed up and terminated my account, so I spent the second half of the day with all my stuff but no computer. And no access to any doors – the access pass was also terminated – which was a bit of a bother given that the loos (among other important things) are outside the locked doors. It was like in primary school when you needed to raise your hand and ask the teacher, “May I be excused?”.

Sighs of relief, the intense career thinking is over (and consequently also my silence here).
I’ve signed the contract. They don’t know it yet, but I have.

It was either a long-term wise decision, or just plain foolish… and I won’t really know which it was until several years from now.

I accepted a job with the Technology department of the company I’m currently with. I’m going in as a lateral hire, but will nevertheless join their graduate training programme, together with new graduates. That’s eight weeks of full-time training this summer and frequent continuing training throughout the first year.

And I’ve turned down a perfectly good job from another firm, which also offered lots of training, and exciting products, and the people seemed nice as well. Plus the pay would have been about 50% higher.

The other job was one I would immediately be quite good at, because to a large extent it uses technologies I already know, and advancement from there. The one I chose doesn’t expect me to be particularly productive at all during the first year or so, but spend the time learning instead – and it will certainly be needed, because all the technologies used here will be new to me (Java above all).

In the end, I think both jobs would have turned out well and be broadly comparable. Apart from the fact that one offered 50% more money, but the other is with a firm that I know and trust, with people I know and like.

So I guess this is how much I value my relationship with the firm.

Another interview today, significantly more intense than yesterday’s. The whole thing took over 2 hours, and there were three of them. And of course one tries one’s best to be really focused, so when I got home afterwards I felt exhausted. More questions, and their questions about the business side of things required real thinking. (Is an average-price option cheaper or more expensive than a corresponding European option?) They also asked their questions in person, which takes more concentration and energy than filling in a quiz on paper in a quiet room on your own, which is what I did yesterday. On the technical side they tested actual ability rather than just theoretical knowledge. They had a list of tasks that they wanted to see me do, ranging from trivial Excel tasks (simple formulas) to some proper VBA tasks (including one that required me to write code to sort an array – and I’ve not sorted any arrays manually for over a year at least!) And again two of them were sitting there in the room, occasionally peeking over my shoulder to see what I was doing. Distracting, to say the least.

So their approach to recruiting was more professional and thorough than the others’, which is a good sign. But I don’t know if I would want that job, now that I’ve heard more about it – and more importantly, heard more about how they work, and how the place works. In particular I doubt I would learn very much there apart from nitty-gritty details about Excel. First of all the team is ring-fenced from the rest of the technology department and only works for one group of traders, so there are few contacts with other teams, i.e. no chance to learn from others, and few (if any) opportunities to move forward to new areas. Secondly they are, according to their own descriptions, so busy that they don’t have much time to show the ropes to any new hires, and rarely have time to discuss their projects with each other, much less have any training. Any learning is to be done in your spare time. Which to me indicates that training and learning is not a priority for them. And it is the top priority for me. They also mentioned that they spend 90% of their time improving and extending existing spreadsheets, which again means that it’s all about tinkering with details rather than broadening your horizons or learning to think about bigger projects and processes. On the whole it appears like a narrow role with no real room for growth.

Far more promising was a discussion I had today with my current company. I met the head of our department’s technology team, who also happens to be in charge of career development for the Technology division in Europe. Our team has worked quite a lot with him on various projects, so my manager had mentioned to him that I was considering a career change. After hearing my story, his firm opinion was that instead of trying to slowly shift from the business side towards technology, the best way forward would be to join the Tech division’s graduate programme, effectively starting from the very beginning, together with people who’ve joined straight from university.

After the initial shock of the idea wore off (“Go back 5 years and start over? Throw away almost 5 years’ worth of advancement?”) I have to agree that the idea might be a good one. It would, almost per definition, be the fastest way to learn things, because that’s what graduate programmes are about. It would also be more efficient than trying to share my time between doing my current job and at the same time trying to work my way into Technology. And it has a lot clearer and broader long-term potential than the two other jobs I’ve seen thus far, which both seemed quite focused on one relatively narrow area both in terms of technology and in terms of the business area it’s applied to.

To be continued.

Today’s interview: not bad.

Part 1 was a multiple-choice test about Excel VBA, C#, derivatives in general, and interest rate derivatives. The VBA questions were easy, things like “Which one of the following is the string concatenation operator in VBA?” and “By default, parameters are passed a) ByRef b) ByVal c) ByUse d) ByDefault”. Anybody who cannot answer these should not engage in further discussions about a VBA job.

The C# questions took more work, but I got most of them right as well – some because it’s so similar to VB.Net, and some because I’ve been reading C# code on various .NET blogs. I’ve occasionally wondered whether I might be wasting my time reading them – I guess the answer is no.

The general derivatives questions were not as basic as the VBA questions, but not particularly hard either. They were all about fundamental concepts and analytics, so even though I haven’t done much with derivatives in the last year and a half, I could dig up those memories and manage them well.

The last part was hard, because I’ve never worked with interest rate derivatives. With common sense and some lucky guessing I got just over half of them right. From his comments it sounded like the wrong ones were at least close and not too badly wrong.

Part 2, the interview itself, was relaxed. I would have been a tougher interviewer myself, definitely. The only hard part was when he started asking more technical questions about .NET, things like garbage collection and COM interop. I sort of know how these things work, but not well enough, and that was probably pretty obvious. But even there I obviously did OK, because he said he wanted me to come back for more interviews. He also asked me what bugs I had discovered in Excel, and at that moment I couldn’t think of a single one…

My own first impressions were somewhat mixed. He was obviously knowledgeable and experienced, but I didn’t sense much enthusiasm at all. Maybe he is just that kind of guy. Or maybe it was because this was late in his working day. But I’m used to working with people who are excited about what they do, and I quite like that. I’ll have to see what the other guys are like.

Now, off to read about memory management and garbage collection in .NET!

Project Career Change is progressing. I will be interviewing with one firm tomorrow evening and with another one on Thursday.

I’m looking forward to the interviews – I quite like interviewing. It’s pleasant to talk to someone when both of us are entirely focused on the conversation and there are no disruptions. And the people you meet via interviews are usually intelligent, interesting and likeable.

I’ve been with my current firm for four and a half years now, but I haven’t been entirely without interview practice all this time. I’ve volunteered to interview a number of potential new hires for the firm, and an even larger number of summer intern candidates. Sitting on the other side of the table put things in a different perspective and helped me understand how things work, and what interviewers are really looking for with all their questions.

Besides, I’ve moved around within the firm. Internal movers are examined almost as thoroughly as external hires, so when I moved from one department to another (back in April 2004) I went through about 10 interviews, I believe. Not that that helped, really… After several months I realised that I was not at all the right person for the job, and the job was not at all right for me, so I quit. Sometimes I’m too persuasive for my own good and manage to convince others of my own views even though I’m completely wrong.

I quit in September 2004 and had time to apply (and interview) for one other job before I was offered a temporary and experimental job in another role in my old company, in a neighbouring team. They knew me and I knew them, so there was no interviewing as such, only a brief chat. I took the offer and have been there for the last 16 months now.

I’m also looking forward to hearing more about the jobs. Job listings will never be more than dead pieces of paper (or dead bytes) with formal facts – it’s difficult to get a feel for what the job is really about. How do the listed qualifications and requirements correlate to the real job? What do they actually develop? (These two listings weren’t too bad, but others just mention developing “tactical solutions for the trading desk” – what exactly is that?) How does the team work? What does their development process look like?

It’s becoming difficult to focus my attention on my current job.

Things are starting to move on the job change front. I had my first chat about a job opportunity today, with the prospective employer. The job in question turned out to be horribly boring, so I had to tell him after half an hour that it really wasn’t quite what I had in mind. It involved auditing and testing Excel templates for traders to enter structured trades – it sounded like I would mostly be reading through piles and piles of IF() formulas. Apparently the company has a backlog of these templates that they think would take about 18 months to work through, and they have been looking for someone for this role for the last 2 years now, off and on. I can see why.

My manager has now seen that I am actively looking for a new job, and is not at all happy about the prospect of me leaving, although he understands and accepts that I want to move after 16 months in temp contract limbo. But the team has more to do than we have resources for, and he wouldn’t like 1/5 of the team to disappear, so he is now trying to find out what he could do that would make me stay.

I have been here before… Things went the same way when I last tried to quit, in September 2004: the managers managed to entice me to stay (which is how I ended up on a temp contract). I’m not entirely happy with how this turned out – it feels like I haven’t achieved much at work since then – but not too dissatisfied either.

This time around I have a much clearer idea of what I want and what I don’t want. I’m also in less of a hurry to leave, which will hopefully lead to a more durable solution. Last time I just felt an urgent need to get out of where I was. This time it’s a longer-term desire to start moving in a good direction.

I’m also more relaxed about the idea of quitting my job. The second time is much easier. I’ve been on a temp contract with zero days’ notice for such a long time that I’ve gotten used to not having any job security, apart from being indispensable to the team. I’ve had to think from day one that this is not forever, and thought about what I could or would do when this finishes.

All this means that I’m in an excellent position to negotiate for a job. Over the next few days I’ll write down what I would want from my next job, and think about if and how this might be achieved within my current team (or perhaps by being loosely associated with the team).

I find it very easy to get into a smooth flow of daily activities and lose sight of the bigger picture. It is like I’m slowly falling asleep and living in a doze. Once every couple of months something nudges me and I wake up for a moment, and realise that I had fallen asleep again.

About half a year ago, I wrote the following (this was Before the Blog so it’s an excerpt from a long offline cogitation):

I have reviewed my life before, both long-term and short-term, but recently (last 6 months or so) I’ve done less and less of it, and cruised on auto-pilot. I need to provide myself with discipline and structure, if I want to stay moving and stay on track.
I spend a lot of time on things that are ultimately not important, and very little doing things that are really important.
If I did nothing more with my life than this, I would look back at it with regret.

Re-reading this a week ago, I was disappointed to see that I was exactly in the same place now as then.

I’m not a big believer in goals – goals can only take you to places you already know. But I do believe in moving, in having direction and momentum. There’s always time to tweak the direction later, but if there is no movement then there’s no chance of getting anywhere at all. (Unless a big flying saucer suddenly arrives to pick you up and deposit you somewhere else, but I’m not counting on that.)

This time around, the realisation that I had stood still for 6 months was shocking enough to get me moving for real.

One of the main things I have been dissatisfied with is my job. In a way it seems reckless and, well, presumptuous to complain about it, because it is, in itself, a very good job. (I work for an investment bank.) I’ve got good colleagues, good atmosphere, excellent pay, reasonably interesting tasks; I’m good at what I do and my work is appreciated. The only tangible shortcoming is that the hours are long.

But the main problem is that I just do not care about what I’m doing. I do not think that the job is important, in the grand scheme of things, or that the firm and even the financial industry really deserve much energy to be spent on them. While I can see that efficient financial markets play a role in the world, it’s not something that really makes a difference. Not a job I would proudly tell my grandchildren about, if you see what I mean.

The problem is that I am not sure where I would like to work instead. I know I want to move, but don’t really know in which direction.

So I’m making the first step a small one.

I intend to move from my current “financial / quantitative analyst” role into a software development role. I’ll stay in the investment banking industry, because it’ll be easier to get a new job here; I will start in Excel VBA development, but expect to gradually move on from there. Software development is a more broadly applicable skill than financial analysis, so whenever I decide to move on (which I know I will do), the next step could be a more interesting one.

It’s a step, which is good in itself as it gets me moving. It is also unlikely to lead me in a completely wrong direction so it’s not going to make things worse.

In the last few days I’ve sent my CV to a couple of recruitment firms and already gotten back a dozen job specs. This is looking promising.

I haven’t felt this excited about my life for a long time.