Learning Excel / spreadsheet techniques for portfolio analytics and operations

Hi All!

I'd like to learn a bit more about all of the modeling and MS Excel based spreadsheet portfolio calculation, analysis, and optimization tools that investment managers use in their portfolio accounting, risk management, and pricing / valuation of positions processes.

Something like what Wall Street Prep or Tain The Street offers but geared more for what I just mentioned would be ideal but I couldn't come across anything.

I think the best solution currently available will probably be a book. Would love some suggestions!

I'm thinking something along these lines.

Essentially I'm looking for something that would help someone if they were in the role of a Portfolio Risk Analyst, Portfolio Optimization Analyst, Middle Office Valuation Analyst, Performance Analyst, etc....

Hedge Fund Modeling and Analysis using Excel
http://www.amazon.com/Hedge-Modeling-Analysis-Using-Excel/dp/0470747196…

Quantitative Finance: Simulation using Excel
http://www.amazon.com/Quantitative-Finance-Simulation-Based-Introductio…

Would love any other suggestions!!

 

StreetGuy, sorry there are no responses yet. Maybe one of these topics can point you in the right direction:

More suggestions...

Fingers crossed that one of those helps you.

I'm an AI bot trained on the most helpful WSO content across 17+ years.
 
SHB:

Makes sense

Well if you're going to do it in excel anyways you can link cells in other files. Its very easy a quick google of "how to refrence cells in other excel files" should help you out.

Unless I'm totally missing what you are asking.

This is what I was thinking, I was hoping there was some other kind of shortcut. Kmzz mentioned a macro enabled roll up, which seems like the most realistic solution, I guess I have to watch youtube videos to learn how to do this...

 

I would recommend a macro

If that's too much of a black box for your bosses, at the very least I would assign codes for every line item in your cash flow so you can point to each step of the rollup

Fill the unforgiving minute with 60 seconds of run. - Kipling
 
SHB:

I dont see why that has to be its just =[abc.xls]sheet1!a1 you can point them all anywhere and individually

Ya this is what I'm doing right now, 5 of the properties are developments and come online at different times, but I don't see why this should make a difference as long as they are linked at the correct dates

Just out of curiosity how did you guys learn macro's? Take a class, watch you tube, buy a book?

 
SHB:

I dont see why that has to be its just =[abc.xls]sheet1!a1 you can point them all anywhere and individually

And he'll have to do that [15 properties * number of cells he wants to consolidated] which is not very efficient IMO. Individually linking like 200+ cells is not fun lol im assuming each cell he wants isnt right next to each other and may be spread across various tabs in a workbook
 

build a roll-up of all financial statement line items from all 15 props then eliminate duplicates so that you have the same list of items on each sheet.

then you can build your statements from there, that way each property has the same line items (all starting in cell d3 for example) and you can quickly link all of them back through to a summary sheet.

 

No you dont have to do them individually, just click/drag/copy across the lines, it works the same way. On a portfolio level analysis I'm sure hes just taking EGI, gross expenses, NOI or each proprty and putting it into a summary.

Its not like hes copying every line of every workbook. Even if he were, with two click/drags he has the entire thing...

Edit: anyways moral of the story is its not any different than linking to another sheet inthe same workbook

 

if you set up each individual property summary in the same way and always starting in the same cell, its an easy link-through to a summary page.. or are we both saying the same thing?

 

You can probably do what you're after. You'll need to write your VBA to look for the first empty row/column and then append your new data there. Just because you can do this, doesn't mean you should though. Excel is just not meant to house a large database of stock price/return data, and approaching it in this way will inevitably lead to a cumbersome and dysfunctional system. If you really want to do it this way, I think you'll get better results housing your data in SQL or some other relational database and doing your analysis in python or R.

 

Deleniti ipsam fugiat et minima eum voluptatum pariatur. Laudantium magnam quibusdam quia doloremque blanditiis totam. Est quia dignissimos consequatur a qui atque et possimus. Asperiores qui ut eum quae ut. Eum ut unde aperiam velit et laborum. Commodi assumenda eos aut et facilis velit facilis.

Et aliquam dolor odio est est quasi enim fugiat. Autem id illum ea nostrum nihil. Illo odio neque aspernatur nam iure earum incidunt.

Unde molestias qui rerum. Provident ut harum assumenda.

 

Dolorum illo esse similique blanditiis sunt et voluptatem. Omnis consequatur esse praesentium expedita placeat. Inventore aut fuga qui ducimus quas laborum fugit sed. Quia cumque cumque vel cum aut. Incidunt rem et eum esse porro ratione. Eaque ea voluptates atque enim. At dolorum quasi enim ea amet non consequatur.

Reprehenderit sed nemo rerum ut placeat exercitationem. Fugiat itaque voluptates omnis impedit eum sed quam.

Career Advancement Opportunities

March 2024 Investment Banking

  • Jefferies & Company 02 99.4%
  • Goldman Sachs 19 98.8%
  • Harris Williams & Co. (++) 98.3%
  • Lazard Freres 02 97.7%
  • JPMorgan Chase 03 97.1%

Overall Employee Satisfaction

March 2024 Investment Banking

  • Harris Williams & Co. 18 99.4%
  • JPMorgan Chase 10 98.8%
  • Lazard Freres 05 98.3%
  • Morgan Stanley 07 97.7%
  • William Blair 03 97.1%

Professional Growth Opportunities

March 2024 Investment Banking

  • Lazard Freres 01 99.4%
  • Jefferies & Company 02 98.8%
  • Goldman Sachs 17 98.3%
  • Moelis & Company 07 97.7%
  • JPMorgan Chase 05 97.1%

Total Avg Compensation

March 2024 Investment Banking

  • Director/MD (5) $648
  • Vice President (19) $385
  • Associates (86) $261
  • 3rd+ Year Analyst (13) $181
  • Intern/Summer Associate (33) $170
  • 2nd Year Analyst (66) $168
  • 1st Year Analyst (202) $159
  • Intern/Summer Analyst (144) $101
notes
16 IB Interviews Notes

“... there’s no excuse to not take advantage of the resources out there available to you. Best value for your $ are the...”

Leaderboard

1
redever's picture
redever
99.2
2
BankonBanking's picture
BankonBanking
99.0
3
Betsy Massar's picture
Betsy Massar
99.0
4
Secyh62's picture
Secyh62
99.0
5
GameTheory's picture
GameTheory
98.9
6
dosk17's picture
dosk17
98.9
7
DrApeman's picture
DrApeman
98.9
8
CompBanker's picture
CompBanker
98.9
9
kanon's picture
kanon
98.9
10
numi's picture
numi
98.8
success
From 10 rejections to 1 dream investment banking internship

“... I believe it was the single biggest reason why I ended up with an offer...”