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-Usin...

Quantitative Finance: Simulation using Excel
http://www.amazon.com/Quantitative-Finance-Simulat...

Would love any other suggestions!!

Comments (27)

Jan 29, 2017

Well, using MarketXLS works for me. It's great. I hope it will also help you too.

Jan 29, 2017
Jan 29, 2017

10-15 isnt that big
a name cell + roll-up macro will work wonders

Jan 29, 2017

Why aren't you using Argus?

Jan 29, 2017

They are multi-family properties, we only use Argus for office/retail

Jan 29, 2017

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.

Jan 29, 2017
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...

Jan 29, 2017

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

Jan 29, 2017

I don't think a macro is really necessary. You can just reference the other workbooks in a "summary" workbook. They auto update and will save you a ton of time compare to a macro with negligible benefit.

Jan 29, 2017

^ He can do that only if the same values (like Yr 1 NOI) are in the same cell in each workbook (if hes doing an indirect or something). Otherwise it will get ugly

Jan 29, 2017

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

Jan 29, 2017
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?

Jan 29, 2017
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

Jan 29, 2017

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.

Jan 29, 2017

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

Jan 29, 2017

True. When I roll things up, they are not always next to each other which is what i stated above could be the situation. For ex, pulling cap rates from a valuation table, pulling specific year IRRs, CoC, Lev IRR numbers, etc. which will not be an easy formula pull and have to be individually linked

Jan 29, 2017

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?

Jan 29, 2017

I realised that this thread should perhaps be in the ER forum?

Jan 29, 2017

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.

    • 1
Jan 29, 2017

Shameless bump

Jan 29, 2017

Do you have access to a bloomberg terminal?

I eat success for breakfast...with skim milk

Jan 29, 2017
Comment