How to show an institutional investor that I'm comfortable on Excel?

Ask your investment, budget, and other money related questions here
Post Reply
TopHatFox
Posts: 2322
Joined: Thu Oct 17, 2013 10:07 pm
Location: FL; 25

How to show an institutional investor that I'm comfortable on Excel?

Post by TopHatFox »

Trying to think of something tangible to show that I'm good at excel, something more than "I've used it before in these classes/certifications and you'll have to take me at my word."

Maybe printing out a brief sample project, but what would even be on it? Hmm....

BRUTE
Posts: 3797
Joined: Sat Dec 26, 2015 5:20 pm

Re: How to show an institutional investor that I'm comfortable on Excel?

Post by BRUTE »

pivot tables

slowtraveler
Posts: 722
Joined: Sun Jan 11, 2015 10:06 pm

Re: How to show an institutional investor that I'm comfortable on Excel?

Post by slowtraveler »

You could use it to compare various sectors/stocks and do some calculations on them all to point out which may be better investments?

jacob
Site Admin
Posts: 15907
Joined: Fri Jun 28, 2013 8:38 pm
Location: USA, Zone 5b, Koppen Dfa, Elev. 620ft, Walkscore 77
Contact:

Re: How to show an institutional investor that I'm comfortable on Excel?

Post by jacob »

+1 to pivot tables ... it's what separates the wheat from the chaff. Bonus points if you can do it blindfolded or w/o a mouse.

TopHatFox
Posts: 2322
Joined: Thu Oct 17, 2013 10:07 pm
Location: FL; 25

Re: How to show an institutional investor that I'm comfortable on Excel?

Post by TopHatFox »

Hm, maybe I could make a brief pivot table to print and show (with some graphs), and send the full file to them attached to the short thank you note post interview?

Fun news is, I've never heard of a pivot table, but I'm sure I can figure it out.

Chad
Posts: 3844
Joined: Fri Jul 23, 2010 3:10 pm

Re: How to show an institutional investor that I'm comfortable on Excel?

Post by Chad »

+1 to pivot tables. If you demonstrate you know pivot tables you demonstrate you know everything else.

TopHatFox
Posts: 2322
Joined: Thu Oct 17, 2013 10:07 pm
Location: FL; 25

Re: How to show an institutional investor that I'm comfortable on Excel?

Post by TopHatFox »

Perfect. I'll make a pivot table. Now to figure out what data set to make it out of...hm

Tyler9000
Posts: 1758
Joined: Fri Jun 01, 2012 11:45 pm

Re: How to show an institutional investor that I'm comfortable on Excel?

Post by Tyler9000 »

Another example that gets people's attention is the ability to use Excel to iteratively solve problems by converging on a solution. Numerical integration is a classic example, but it's useful in lots of applications. I've used it to solve for things like mechanism positions and withdrawal rates.

Pro interview tip: To take it one step further from simply demonstrating competence, try solving a sample problem related to the job you're applying for. That can check off three boxes and simultaneously show off your excel skills, your ability to condense and communicate findings, and your skill for the job.

Lucky C
Posts: 755
Joined: Sat Apr 16, 2016 6:09 am

Re: How to show an institutional investor that I'm comfortable on Excel?

Post by Lucky C »

Data Analysis and Solver Tool-Paks don't seem to be discussed much (so probably won't come up in interview?) but I find them useful for investing problems. Have you installed these Tool-Paks and used them (should be available/free even in student Excel versions)? Very easy to do and start playing with so you are at least familiar with them, if you are asked. Here is how I've used these features applied to investing:

Data Analysis Tool-Pak: This has various statistical functions, which you shouldn't cram for an interview unless you are already comfortable with statistical tests. However one easy and convenient feature is Histogram creation. Excel is not user-friendly for histograms, but if you install Data Analysis all you have to do is create a set of numbers to define your bins and then tell the histogram plotter where your data and bins are. I have used this when creating my own FireCalc using randomized returns, then creating a histogram to show the distribution of ending portfolio values.

Solver Tool-Pak: Set cells as input variables and Excel will use a solving engine to find values of the inputs that meet the results you define (output in other cells). I have used this on a sheet containing historical data of different stocks/ETFs, with inputs being the allocation % of each investment. Then using those % allocations, I calculated the resulting portfolio, its return and volatility, and resulting Sharpe ratio. I set Solver to vary the allocation to maximize the Sharpe ratio.*

For an advanced feature like Pivot Tables or Tool-Paks which you only get a chance to spend a couple of hours looking into before the interview, I wouldn't bring out a printout or steer the conversation in that direction. The interviewer could start digging deeper than your current depth on the topic. It's just good enough to know about these features if the interviewer brings up a problem that could be aided by them. In my interview for my current job, I studied a bit on an advanced measurement technique, but hadn't spent the many days in the lab needed to get comfortable with it. I wouldn't have brought it up, but the interviewer asked about it so I just described it in basic terms to show that I was aware of it, without getting myself in trouble saying that I could already do it. Evidently that was enough to meet their expectations and hire me!

*This was fun to play with and see what would have been possible in the past, but I wouldn't apply this type of optimization to real portfolios. I was optimizing based on daily data and short term correlations, which can get you in a lot bigger misalignment of past performance & future returns than the common way of portfolio construction using annual asset data over many decades.

OTCW
Posts: 437
Joined: Thu Mar 31, 2011 12:55 am

Re: How to show an institutional investor that I'm comfortable on Excel?

Post by OTCW »

I find the vlookup, sumif, countif, and array functions invaluable when dealing with big data sets.

mfi
Posts: 93
Joined: Sun Jul 29, 2012 10:27 pm

Re: How to show an institutional investor that I'm comfortable on Excel?

Post by mfi »

With respect to historical performance, know how to calculate in Excel daily/monthly returns from cash flows, geometrically link monthly returns into quarterly returns, calculate annual/annualized returns following the global investment performance standards https://www.cfainstitute.org/ethics/cod ... index.aspx

With respect to forecasting/modeling, learn how to build Monte Carlo simulations in Excel VBA

halfmoon
Posts: 697
Joined: Mon Nov 07, 2016 10:19 pm

Re: How to show an institutional investor that I'm comfortable on Excel?

Post by halfmoon »

These entries make me want to go and lie down for awhile. I told a friend once: "I've concluded that I only use about 10% of Excel's capabilities." His reply: "Don't flatter yourself."

I've revised that to 1%. :roll:

Post Reply