Here is a list of things you need to do before you can run your personal portfolio analysis in Excel model.
This guide was created for readers who have requested a high level review of the Excel portfolio analysis process first before they are ready for a deep dive. For this reason, we present no numbers, calculations, formulas or spreadsheets directly. Within the review links to posts that walk through actual calculations are embedded that provide the shortest path to building an Excel portfolio analysis model when you are ready for it.
Qualifications to the Portfolio Analysis in EXCEL model
The first step is a call on your investment philosophy and style.
What is the extent of your exposure to financial markets? Have you traded before? Are you aware of your investment style? Technical or Fundamental? Value, Growth or Momentum? How much time have you spent paper trading or with play money using tools, forums and communities online. Who are your favorite investors? Have they written anything about their investment styles? How much of what they have written, have you read? What is your investment thesis and your investment strategy? Have you backtested it? What were your results? Do you maintain a trading log? Can you share your most profitable trade as well as the ones that (almost) wiped you out?
If your answers to any of the above questions is negative or you don’t know how to answer them at this stage, please stop here and go back to the beginning. Wrap up your plans to trade for a living or trade to the top of the world.
Prep for aspiring traders
You can begin by going through reading lists for aspiring traders. Topics to be covered include investment styles and philosophy (Value Investing by Bruce Greenwald), traders biographies (Snowball on Warren Buffet, Wall Street Meat by Andrew Kessler), market guides (Investments by Kane Bodie and Marcus), Financial Analysis (Analysis for Financial Management by R.C. Higgins), Corporate Finance (Corporate Finance by Brealey & Myers) and accounting (Financial Accounting by Meigs & Meigs). You can’t run a marathon without training six months for it. Can’t trade without some basic knowledge about how markets work and what drives value. You can get lucky with a few trades but consistent profits require awareness of trading process and discipline and that takes time.
You shouldn’t be trading because you feel it is a good idea, because your neighbors, your train buddy, your gym partners or your colleagues at work have been bragging about their trading gains or because you have nothing else to do and have some capital to burn. Maybe you have even read about the lucky ones who got into Disney, Amazon and Netflix at the right time.
Don’t.
Trading requires skills, process and discipline. Not everyone is well suited for it. Even the ones who are well suited and qualified for it, don’t do well. Your chances as a complete newbie are quite dim.
Start all over again. Do not press the button for the buy order. Quit while you are still ahead. Remember very few individuals have gotten rich trading, despite the perception and the hype. Most have lost their proverbial shirts and wardrobes.
Securities Universe and data set for Portfolio Analysis in EXCEL
Security universe
If you successfully passed the above hurdles the next big step is creating your securities universe. A securities universe is your playground where you trade. This is your first filter. Which side of the pond do you want to fish in? Successful investors, invest in sectors and segments that they understand well and businesses that they follow. What do you understand best? Where is your edge? What do you follow?
While my origins are in technology, I don’t understand all of it. I understand some small parts that deal with enterprise software and consumer technologies. I don’t understand semi-conductors and security software. The few times I have strayed from the path I have lost my shirt. I have also spent two decades working with financial services. Once again I understand bits of it, especially the ones that I suspect are likely to fall off in coming years or go the way of the Dodo. I know enough to have a small edge in these two segments and have used that to make as well as burn money. My security universe primarily includes tech and financial services stocks with a few play on demographics. That is my starting point. Find out yours.
Price data
Once you have identified the universe you are going to play, the next step is price data. There is a fair bit of free data floating around. If you have a trading system or screen, you should have the option to download price history for securities in your portfolio and your watch list. If you don’t, finance.yahoo.com or Quandl or a number of other free or free-mium sources can help you out. Download about ten years. You may not use all of it or need it but in my space ten years covers about 4 product cycles in technology and three economic cycles in finance. Get enough to cover between three to four cycles for your sector and segments.
If you don’t have a data set your can borrow mine. For the time being.
Step one. Calculate Risk and Return.
Calculate risk and return series for all the securities in your universe. You would need to structure and create the Excel portfolio analysis spreadsheet first. Once we carry put these two steps, calculate return per unit of risk for each security and then configure your model to also calculate it for your portfolio.
Return per unit of risk is your first filter. It looks deceptively simple but it is one of the most powerful security selection filter at work. While other more sophisticated and complex models are available, you can get fairly decent returns from this simple model. You can step up your game by calculating holding period return on a year by year basis and use that as proxy for return rather than expected return. For risk we tend to use good old standard deviation or volatility in trading speak.
Step Two. Allocate.
Your Excel portfolio analysis spreadsheet should be able to show you two metrics. Return per unit of risk for each security and return per unit of risk for your portfolio. While we plug in initial place holder allocation values to test the models, the actual portfolio allocation will be done by Excel Solver based on a model we specify.
We work with three models.
The first is our simple base case model which uses return per unit of risk as the portfolio allocation model. We use Excel Solver to find an allocation scheme that maximizes portfolio return per unit of risk. Alternatively, you can also specify a baseline performance index. You would like a portfolio that optimizes risk and return trade off but also beats a given primary market index. Details of such a model can be found at Excel Solver Model for Portfolio construction.
Our second model maximizes Alpha. Alpha is a proxy for excess return. Our third takes it one step further by betting on Alpha cyclicality – which plays of the tendency of Alphas to mean revert. Both focus on finding the optimal alpha for your investment strategy. If you are not sure what Alpha is or how to calculate it, see the difference between Alpha and Beta.
Step three. Back Test.
If you were lucky enough to go back in time and invest based on your model, what would returns look like? Answer the question by calculating Holding period returns for all three strategies. A backtest uses historical price data to see if the trade would have made money under market conditions that have already been recorded. It is the first step used to test a model before we are ready to deploy it. If the backtest is successful, we may also paper trade (trade with play money) for a few trading cycles to see if the trade holds under real time.
Step four. Deploy capital. Rinse and repeat.
If your backtest and paper trading tests are positive, you can go forth and deploy capital. Each trade must have a clear entry and exit point. Back it up with risk limits that act as breakers for your capital.
And you are done. As we promised. Shortest path once you are ready. No numbers, formula, Excel spreadsheets or models. Just over one thousand words.
Which reminds me, last words of advice.
Start small. Feed your winners. Cull everything else. Invest in what you understand, do your homework and know your style. And remember to give trading the time, commitment and respect it deserves.
Lecture notes from the portfolio optimization course, previously hosted on this page have been moved to a centralized Portfolio Optimization Course location. You can still access them for free and without a charge.
