Meet the Other Phone. Protection built in.

Meet the Other Phone.
Protection built in.

Buy now

Please or to access all these features

Work

Chat with other users about all things related to working life on our Work forum.

DominiConnor - advice/help needed re. Excel

9 replies

Kevlarhead · 12/06/2007 21:08

I'm still looking to get out my IT firm, and I'm now applying for a trainee analyst job with an investment management company in Edinburgh.

Just wondered if you could recommend any particular techniques or features that would impress the recruiters and (hopefully) interviewers. Currently kicking excel into life so I can try out DDE.

OP posts:
clerkKent · 13/06/2007 12:34

I work for a financial services company - you will need techniques like pivot tables and vlookup, but VB macros are the main thing (imo).

Kevlarhead · 14/06/2007 01:12

Got all of them. Wondered if there was anything above and beyond that which people look for in the financial sector.

OP posts:
DominiConnor · 14/06/2007 12:54

A few things which I'd look for.
Good VBA, as in
Being able to use the object model, call sheet style functions, and I might ask if you know how to make the Solver work from VBA.

You ought to be aware that
Dim x,y as double
does not do what you think it does.

Also that VB integers are signed 16 bit and therefore do not always handle VB sheets which use an unsigned integer, so rows above 32767 can go bad.

You should be wise in the ways and limitations of the audit tool. The Solver is important to AMs, it shouldn't be, but is.

Last year I was forced to select Excel developers at a bank, and one set which did for most of them based upon AND, IF, etc.

Given
=AND(q(),q())

Function Q() As Boolean
Q = True
End Function

Two questions:
1: How many times does Q get called ?
2: Why ?
3: Why might I reasonably think Q gets called a different number of times ?

Another question is what do you do about an Add-In that keeps crashing Excel ?
First there is the process that you should know of proving that it is a particular addin.
Next, you should know about having two instances of Excel running, which means only one blows up at a time. DDE can be your friend here, as can the VBA "Shell" command.

Also a good skill is importing data.
A lot of data given to AMs is in CSV, tabbed, etc. Learn this, it's not hard though if you can write VBA to deal with "broken" data that's good.

Data import is god in general. Sucking simple info out of Access (not just import/export), is a good thing. A smattering of SQL always helps, even if they haven't mentioned it.

One tip for DDE, there is on "OnTime" event in VBA. most people who work in financial markets don't know about it. This can be used to "wake up" DDE sheets, or other feeds that stop for no good reason. If you have a lump of data that "appears" in a directory every do often, you can peek at it every so often.

A knowledge of different classes of financial instruments is good. Bonds vs equities, what "risk" actually means, coupons, dividends, and "corporate actions". This is mostly things like stock splits which have the strange effect that although the price of the stock should halve (or third), the sum of the two "parts", is higher.
Knowing the difference between an option and a future is useful.

DominiConnor · 14/06/2007 12:55

BTW, I'm better known as DCFC on Wilmott.com

Rhubarb · 14/06/2007 12:57

Whoa! You look half normal!

DominiConnor · 14/06/2007 13:09

Don't let the publicity photos fool you. I'm a fat middle aged geek.

Rhubarb · 14/06/2007 13:38

But I was expecting someone in red with horns and a goatee beard!

DominiConnor · 14/06/2007 14:38

Absolutely a fat middle aged geek who can use photo changing software.

zanz · 02/03/2010 20:56

An Old post but I was not expecting to see high level discussions about Excel Addins and VBA etc on this site.

The logical order of learning Excel would be

BAsic calculations involving plus minus multiplication and division. Then BODMAS brackets eg 2+53 = 17 or (2+5)3 =21

Then basic functions SUM AVERAGE MAX MIN COUNT to find the total, average, biggest smallest and how many of something in a range/block of cells

Then Logical Functions IF AND OR NOT SUM IF COUNTIF. These give alternative outcomes dependent upon if a condition is TRUE/FALSE

Thee are then other specialilist groups of functions for lists/databases, finance, text, dates etc

All of the above may then need to be calculated across multiple sheets or files

All of this may need to be graphed.

Alternatively Excel may just be the container for data calculated elsewhere eg a dealing system.

All of the above may need to be automated and this is where VBA or programming comes in

The question always is for a job what should you already know looking for a job and what is so specialist your employer should train you first.

If yo are after a manual downloadable on certainly the basics try www.www.mousetraining.co.uk/ms-office-training-manuals.html

I am going to have a hunt now for a working at home section for all those tech problems of the home alone worker

New posts on this thread. Refresh page