The Power of Excel Compels You!

March 24, 2011 3:45 PM

Help me pass a test using Excel (special snowflake banking industry questions inside).

I’m in the middle of a rather Byzantine interview process for a position as a strategic analyst in the market risk department for a boutique transportation bank. The next step is a two-hour, on site test “using” Excel this coming Monday. I’m to bring my own laptop (OS X, Excel Office version 2008) but will have no Internet access; it wouldn’t surprise me one bit if they have their own Faraday cage. I think they will simply hand me some data and ask me to make projections or analyze causes based on the data.

I am a very basic (no pun intended) Excel user who is familiar with what it can do but hasn't had to construct many formulae, marcos, etc. before. Here is where you can help.

Given that this test will be about market risk: What should I study for my self-taught crash course in Excel over the weekend? What’s most generally useful to know in this environment, e.g., pivot tables?

I’ve been through all 600-something posts tagged with, “Excel” here on the green and developed a great list of Excel training resources, but of course I welcome links to any resources I might have missed.

posted by digitalprimate to computers & internet (8 answers total)

vlookup
posted by en forme de poire at 5:08 PM on March 24, 2011 [1 favorite]

(though you've probably seen that, sorry! still, I was going to post it before seeing that it had been previously discussed here)
posted by en forme de poire at 5:09 PM on March 24, 2011

Thinking about areas you'll want to know about

1-Data import and cleanup
A- import data from odbc, csv, tab delimited and other formats
B- locate duplicate records, and duplicate partial records
C-locate bad rows, invalid dates, unexpected data (eg text where number is expected)
D-merging different record sets
2-Logical operators if, and or, XOR functions also sumif, countif and other stuff
3-handling the NAN errors and debugging broken things
4-linking spreadsheets and files and cells
A-vlookup, lookup, match, offset
B-indirect
5-pivot tables
A-setup a pivot table, enter additional rows into the original source sheet, modify the pivot table to include the new rows
B-grouping and sorting functions, counting records, etc.
6-forecasting and trending functions
7-all the stats functions
8-chart wizardry
A-basic chart operations like selecting and updating data selection sets
B-combining chart types eg put a line on a bar chart
9-visual basic for applications and macros

Personally I think you are screwed and if you get he job, got help us all. You are being hired into a risk management position and you are engaging in high stakes cramming.
posted by humanfont at 5:43 PM on March 24, 2011 [2 favorites]

9-visual basic for applications and macros

If you're waling into a Windows shop with a Mac and they hand you a file with VBScript in it you're screwed as Office 2008 for Mac doesn't do VBScript. I think Office 2011 has restored VBScript but I've not played with it.

You should look up the differences between Excel 2008 for Mac and Except 2007/2010 for Windows to know how to work around what you can, or in the case of VBScript you'd need to skip.
posted by birdherder at 5:49 PM on March 24, 2011

Just to clarify: the position isn't actually risk management; it's sector risk assessment. Other people higher up the food chain will use or ignore the department's findings.
posted by digitalprimate at 5:57 PM on March 24, 2011

Second the check out Excel for Windows (2007 and earlier versions if possible - they're preferred in the finance industry as they've been the standard for many years and have awesome shortcuts).

I have found this website incredibly helpful myself when it comes to banking industry jobs, from how to interview for them to how to make models on the job. Read everything you can there, especially in the forums. I'm not sure how quant and Excel-focused your job will be, but this site tailors mostly to corporate finance (investment banking) so it will be quite advanced. You can even ask in the forums what interview questions you're likely to get and I'm sure you'll get replies that will tell you 1) what scenarios there will be and 2) how to work through them. Possibly even sample Excel files.

Good luck to you - it's going to be tough cramming this over a weekend, especially being a Mac user. I also wouldn't expect pivot tables - they're rather basic and I think used more commonly in sales and marketing.

If you're short on time, definitely at least memorize:
- time-saving keyboard shortcuts like add/delete row/column, selections, formatting, paste value, etc.
- nesting basic mathematical functions within cells
- linking cells on different pages/files
- vlookup
- special finance functions like FV(), PPT(), PPMT(), IRR(), PV(), etc.
posted by droolshark at 8:05 PM on March 24, 2011 [1 favorite]

vlookup. Pivot tables. Vlookup and pivot tables again. Named ranges. Then look up how to create formulas you would often use in your work. Then if/sumif/countif.

Don't worry about nesting things, just do it step by step over a bunch of cells and hide the ones with intermediate data.

Pivot tables are used to display data very often.
posted by jeather at 8:32 PM on March 24, 2011

Pivot tables/charts (including how to create calculated fields), vlookups, if statements, sum, average, and count are the primary functions I use (and I use ALOT of Excel). The bigger problem with Excel that I see with less experienced users is knowing when to use these functions. If you can find some exercises where you take at least a few hundred rows of data and analyze it you'll be in much better shape.
posted by Octoparrot at 9:01 PM on March 24, 2011