T Nation

Mastering Excel


#1

Hey all,

Any finance people in here who are extremely proficient in excel? Did you use any particular training course /program or just develop it over many years? I will be working as a financial analyst soon and am definitely a little soft in the excel department (especially for a finance dude).

I know the basics, but am just trying to learn the intermediate-high end stuff to really up my value in the future.

Any recommendations would be mighty helpful!

Thanks


#2

x2. I'm going into finance as well and am curious about this. The most I can do is create a 5/3/1 template.


#3

Training the Street!

I'm a second year student at Harvard Business and they out source excel training to these guys. I've been to several of their modeling classes and they're really great for everything from basic DCF to LBO deals.


#4

Couple questions:

  1. Are you aware of this function list?:
    http://office.microsoft.com/en-us/excel-help/excel-functions-by-category-HP005204211.aspx

  2. Are you looking to replicate investment banking hedging trading programs, or are you looking to do financial analysis the old school long term investment way?

For the old school stuff, I recommend two books:

Security Analysis by Graham and Dodd
http://www.amazon.com/Security-Analysis-Sidney-Cottle/dp/0070132356/ref=sr_1_24?ie=UTF8&qid=1327861397&sr=8-24

The Intelligent Investor by Graham, Zweig and Buffett (yes Warren)
http://www.amazon.com/Intelligent-Investor-Definitive-Investing-Practical/dp/0060555661/ref=sr_1_1?ie=UTF8&qid=1327861344&sr=8-1

Another for anyone interested in general theory and philosophy:

The Essays of Warren Buffett: Lessons for Corporate America, Second Edition
http://www.amazon.com/Essays-Warren-Buffett-Lessons-Corporate/dp/0966446127/ref=pd_vtp_b_4

If you are leaning more towards quantitative/computational finance, google the names on this list:

I would say Paul Wilmott is the big one. I like him because he is willing to criticize the field and its sometimes overarching models. I have a list of other resources and tools if this is what you want.

Another author who is a good read is Nassim Nicholas Taleb, he has some interesting links on his webpage:
http://www.fooledbyrandomness.com/

In general with excel, look up the function on the list at the top, and if you have problems using it, google or youtube it, there are likely tutorials for them. It will take some work on your part to find what you need, but once you learn to apply it and refine your models, it gets easier.

My advice for anyone regardless of level or investing style is to read the Essays of Warren Buffett. It becomes a lot easier to ignore the up and down hype in the media/markets and focus on the true nature of business, enterprise, investment, and how it relates to long term returns.


#5

Thanks everyone for the input. I just bought some stuff from Training the Street and will be diving into that very soon. Also, thanks for that list Peter; I think that serves as a good starting point in terms of knowing whats out there and what I need to work on.

If anyone else has any advice, please keep it coming!


#6

No worries, thanks for the question, I found some interesting functions when I looked up the excel list, might have to update excel to 2010 or check if open office has them.

Just wondering what kind of analysis you are doing, because I have loads of quantitative/mathematical finance tool and article links and it really depends on viewpoint you take for modeling. I am aware of the standards, but recently this has begun to look kind of weak. Of course the job likely dictates it, but there are lots of interesting ways to extend screens to try to make up for the shortcomings.


#7

This is a very interesting thread, thanks for posting


#8

Peter,

Not sure on the specifics of what I will need to look into modeling-wise yet. I don't start for a few more months. I was more looking for mastering intermediate level, general excel skills that every finance man should know. I think I should start broad and I'll narrow once I know what the specifics are...?


#9

I got you. You seem to be on the right path then. If you were doing the hedge fund-like trying to model the world we could really get to the point were excel would be overloaded, but for straight out security analysis it is more than enough. My previous comment on extending screens is for 'rule-the-world' type models not old school analysis of individual securities.

The three books I recommended cover these basics very well, if you were to get one, The Intelligent Investor has all the formulas in it and is shorter. Security Analysis is a longer treatment of the same subject and the Essays of Warren Buffett is more akin to philosophy.

Anyhow, have fun and don't crash the market on us.


#10

Really, the best way is just working in it and utilizing the Help function. If you can get your hands on some financial models that you can play in, it will help you a lot. Otherwise, you can try creating your own models and working through some solutions on your own.

Play around creating macros. Use the "record macro" functionality to work through some basic functions. Then, go into the macro editor and see what the syntax is. While writing macros is advanced, you can get a lot of insights into Excel just by playing with them.

Click on the "fX" (insert function) icon on the toolbar and scroll through the different built-in functions and look at the syntax. Your bread and butters will likely be "If,then, else", "vlookup", "hlookup", "select case", "average", "maximum"/"minimum" functions, among others.

But, like I said, there is no substitute for just plain getting a model and immersing yourself in it until you know how it works inside and out or getting the assignment to create a model from scratch.


#11

Thanks for that list Peter. Excel use is generally pretty basic for accounting but lately I've been getting some interesting and specific questions from corporate managers. Most that I can assist with just fine, but some that leave me banging my head against the wall for a couple hours in my own time.

Will check those out.


#12

this x 100

Also there are tutorials, both web pages and youtube vids, so searching a given function may give one a different presentation of how said function works, sometimes different resources make learning easier. Learning how to write macros is the next step and in the financial industry this is a very marketable skill.

@Teledin

Thank Houston07 too, he triggered the response. I ain't shitting when I say most of the world's financial markets are run from excel spreadsheets and VBA macros. It is a bit scary. Though for accounting or micro-economic analyses it really is an excellent tool and can help managers make decisions that may otherwise not be obvious or are counter-intuitive based on current philosophies.