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!!
Well, using MarketXLS works for me. It's great. I hope it will also help you too.
Portfolio Analysis - Excel Model (Originally Posted: 10/24/2017)
I am looking to build a model in excel for CRE Portfolio Analysis. Not looking for an "investment/acquisition" model but more of a "historical operational" model. The intent of the model is for my Managing Director to get a snapshot of a portfolio at the Asset Management level. Looking for performance metrics such as Revenue, Actual vs. Market Rents, Operating Expenses, NOI, Occupancy, Capital (TI's, LC's).
Again, I am modeling for historical performance i.e. a current snap shot of a portfolios performance, over time (annual) with maybe 6-10 years of data at the property level.
Any advice and/or recommendations will be greatly appreciated. If one of you monkeys, currently has a model that is anything like the one I have briefly described above, please pm me if you don't mind sending the model my way.
Thanks!
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.
Excel Question- Portfolio level acquisition (Originally Posted: 10/02/2013)
I currently work in the development group at a REIT that just sold a large portfolio of properties. The transaction will close in the next few weeks and we will be faced with the task of redeploying a large amount of capital as quickly as possible, which essentially means I'll be working on acquisition underwriting until we get this money out.
I've been underwriting a portfolio on a one off basis in separate worksheets and I need to determine the most appropriate technique to consolidate the property level cash flows in order to perform a more portfolio level analysis.
The majority of other acquisitions I've worked on have been much smaller whereas this portfolio will likely be 10-15 properties, 500m+. Could some of you guys who work on larger deals chime in and provide some general guidance or insight? Whats the best way to do this?
10-15 isnt that big a name cell + roll-up macro will work wonders
Why aren't you using Argus?
They are multi-family properties, we only use Argus for office/retail
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
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.
^ 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
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?
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
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
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?
Spreadsheet for own portfolio (Originally Posted: 10/06/2017)
Hey guys,
So I am starting to experimenting with my own portfolio of different asset classes where I will apply fundamentals combined with some technical analysis and risk management to see where this will take me. I am really excited about try this out!
Now I will want to see the price development of my assets over time and also be able to rebalance in order to manage risk exposure. My wondering is, will I have to import new closing adjusted/ average daily price on a new column each day (while deleting the oldest) or is there some kind of VBA script/ excel addon imports data in new cells with, say, daily intervals.
I know that it is possible to excel to yahoo finance for instance for live update but what if I want this update to be registered into new cells because I want to save historical data?
By all means, if you have other softwares for managing a portfolio that you recommend. Any advice would be appreciated! I am just starting out :)
Regards
I realised that this thread should perhaps be in the ER forum?
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.
Excel help - portfolio rebalancing by beta (Originally Posted: 03/29/2012)
Hi WSO,
I'm having a bit of difficulty with a problem in Excel and was hoping that some of you might be able to help. I have daily return data for 6 securities where I have calculated rolling betas and alphas for each previous 5 trading days.
I want to create a simple portfolio that is long the 3 lowest beta securities while being short the highest 3 beta securities. The portfolio will be rebalanced every calendar month to ensure that the 3 lowest beta securities are in the "low-beta" portfolio and the 3 highest beta securities are placed in the "high-beta" portfolio.
As the betas of the underlying securities change over time, so does the rankings of the betas and therefore the formations of the "low-beta" and "high-beta" portfolios. I'm having trouble figuring out how to do the monthly rebalancing of the portfolio the easiest way and I was wondering if any of you had any suggestions on how to do this?
Thanks in advance and please let me know if you need any additional info.
Shameless bump
Do you have access to a bloomberg terminal?
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.
See All Comments - 100% Free
WSO depends on everyone being able to pitch in when they know something. Unlock with your email and get bonus: 6 financial modeling lessons free ($199 value)
or Unlock with your social account...
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.