How Is Microsoft Office Used in Investment Banks


New graduates, seeking exciting high flying jobs in the city, often don’t know what to expect once they arrive in the slick new glass towers in the sky. University IT courses are often too bland to be of any use, or too specialist for the entry level roles that they begin with. For example, back in the day, my Microsoft Office training from academia included writing mock letters in Microsoft Word and generating double-order bookkeeping spreadsheets in Microsoft Excel. I did also study ER databases, but was taught Oracle rather than Microsoft Access.

Duties for entry level graduate positions will also be extremely varied, often involving Perl for parsing log files, Bash Scripting for automated tasks and a healthy dose of what is known a Spreadsheet Jockeying.

This article shall provide readers with some concrete examples of what graduates have had to perform in the early days of their careers. The year may be 2010, but new hires will still have to do the donkey work, as it’s always the monkeys at the bottom who have to do the boring stuff. Learning from their experiences should help future graduates prepare for their new jobs, making their first days a little easier, and gaining an edge over the competition. If you can produce a fancy PowerPoint presentation while your peers are struggling with the documentation, you’ll shine like a star already be one step ahead.


Report Generating

Mr N was the new boy at a bulge bracket bank a few years ago. He was placed in an IT Infrastructure Reporting team, and his job basically involved collecting data, tidying it up, and then aggregating it into a Powerpoint report.

Going into more details:

  • He copied a lot of data from website based reports into Microsoft Excel

    If you only want to paste the values, and none of the formatting information, you need to use the Paste Special option, and paste as text. To get there the fast way, enter the keyboard sequence Alt, E, S.

    If you can’t do sums on numbers that you’ve pasted, they are probably being stored as text. Try using the TRIM() function on them.
    He copied a lot of charts from Excel into Powerpoint

    To copy a chart as an images, you may need to use Paste Special again, in order to paste the charts as images rather than chart objects. This is because chart objects sometimes look a bit strange when printed.

  • He parsed a lot of text data in Excel VBA

    Data came from a variety of sources. Some is in the form of formatted text. To parse this, he had to use the regular expression functions in VBA.

    He made good use of Pivot Tables, VLOOKUP and HLOOKUP

  • He had to condense a lot of data into pretty tables. This was done with liberal use of Pivot Tables, and the vlookup and hlookup functions. So make sure you know how to use these well!

Model Analysis

Mr M was an entry level analyst at a hedge fund. Unfortunately, he was not supervised properly, and was told to do some model analysis in Microsoft Excel. While this is possible, and leads to easier visualisation, it does not tend to scale well and large spreadsheets are inherently unstable. For example, if you are trying to analyse inefficiencies in a 30 stock portfolio, modifying your spreadsheet to analyse a 50 stock portfolio might not be too easy.

Anyway, in order to do this work, Mr M needed to make good use pivot tables, vlookup functions (to line up the dates from multiple time series) and some Excel VBA. The VBA was for looping through parameters in the model. Computer scientists will know that this solution is sub optimal, but new boys are there to do what they’re told, not rock the IT boat.

Skills Required

To summarise, a fairly broad range Microsoft Office skills will be required by the new boys and girls in the city. If you are one of them, make sure you can:

– Copy and paste

Make sure you can do unformatted text, numbers, charts and images

– Manipulate data into tables with Pivot Tables, Vlookup and Hlookup.

– Use Visual Basic for Applications (VBA)


If you are one of the lucky few to get a job during this nasty recession, remember to be well prepared, but to not take things too seriously. Hopefully this article will help you to prepare for the IT side of things, but it’s only drawn from a small sample size, and the tasks that you’ll be set will be both varied and challenging.

If you don’t have the latest version of Microsoft Office 2010, please note that large organisations often run a few versions behind. This is because upgrading many computers is a slow process. And the IT support staff like to wait for a few service patches to ensure stability.