Building A Bulletproof 2020 Portfolio: Part II
Part I Recap
In Part I of Building a Bulletproof 2020 Portfolio, I took you through my investment philosophy of focusing on return on equity (ROE) and free cash flow (FCF) as the top two determinants of a company’s ability to generate return for its shareholders. I briefly discussed earnings per share, sales, dividends and debt and while I agree they are important measures, they are metrics I look at after my portfolio is developed rather than using them to include/exclude stocks from my stock universe.
I also wrote about the importance of choosing the right time period when building your portfolio. Because the risk ratings of today do not include a major recessionary period, they are mispresented. By constructing my own model portfolio, I can choose whatever time periods I feel are appropriate. And since I believe in reducing my portfolio’s volatility in order to generate superior returns, I chose to include returns as far back as 2008. This way, the end product will have taken into consideration the last major recession.
Gathering Monthly Returns
Now that I have described my philosophy, it is time to begin gathering the data we will need to build an equity model. The first piece of data you will need are historical monthly returns dating back to the time periods you are considering - in my case, January of 2008.
There are numerous sources out there which offer users the ability to download historical price, dividend, and stock split data by day, week, month, or even year. Unfortunately, being free, none of them are perfect. However, there are strengths in numbers and if you end up with the same answer using all four data sources listed below, I would say you’re in pretty good shape. Of course, you can always pay for this information through a reputable company such as Thomson Reuters, but I don’t think this is necessary. I’ve contacted them directly and their prices were much too high for me.
Portfolio Visualizer
Here’s a great starter site where you can probably knock off most of the stocks on your list. Their backtest portfolio tool is extremely easy to use and you can upload a CSV file with up to 150 tickers and weights. Just be sure to select monthly data in settings, and export your file when complete.
There are two downsides with this site that I should note:
their database is incomplete - I found numerous instances where stock data exists but the site indicated it was not trading
when uploading multiple tickers, if data for just one ticker does not exist for the entire time period selected, the results for all tickers only include the time periods for that one ticker. You have to remove the “problem” ticker and keep re-running it until you get all the monthly returns you need. Quite time consuming. The good news is that once you do it, you can just keep adding to it one or two months at a time, which will be much faster.
Yahoo! Finance
I give Samir Khan of InvestExcel total credit for this one. Samir builds all sorts of useful spreadsheets for investors and provides most of the best ones free of charge. His stock quote downloader was what I started using when I first began developing equity models. It’s great for people who aren’t familiar with coding or running bulk queries.
Like Portfolio Visualizer, the Yahoo! Finance database isn’t perfect, but it’s quite good. In particular, the site calculates an “Adjusted Close” price depending on the time periods you enter, which takes into account dividends and splits. The calculation is a bit funky, but it sure makes it easy to calculate monthly returns for each stock.
Canada Stock Channel
I like this site for the simple reason that it’s easy to use. Just enter the ticker and time periods and it will generate the compound and annualized return. However, doing this one-by-one would be crazy and take forever, so you really need to have some coding or basic Excel Power Query knowledge to make this one work for you. Luckily, this site is one of the few out there that uses tables Excel will recognize, so with the right setup you can certainly gather all monthly returns in a single Excel power query. Interested? Send me an email, I’ll be happy to show you how!
Investing.com
My least favourite of the four, but it does help fill in some gaps when the above three methods fail to produce the same results. I couldn’t get these tables to load directly into Excel, but the site does allow you to export to CSV so long as you have an account.
My Advice
Start with Portfolio Visualizer and Yahoo! Finance if you aren’t comfortable with coding or using Excel’s Power Query. If you’d like to build your own query, Canada Stock Channel can help you with the data you missed with the above two, and vice versa. Finally, look for any monthly returns that are +/- 50% or more - sometimes these are simply stock splits or reverse stock splits that indicates an error in the database. You can then verify these on Investing.com.
Gathering Key Metrics
In terms of easiness to access, Reuters is number one for me. They have key metric data available for mostly all publicly traded companies, at least in the U.S. and Canada, and are all in a nice table form, so using Power Query is possible. Here is the link for Barrick Gold (ABX.TO) to give you an idea of what information you can download. Simply replace the ticker (ABX.TO) to get data on another company.
This website has all the key metrics I like to look at, but it also has so much more including valuation ratios, return on assets, sales and dividend growth rates (1, 3-year, and 5-year), beta, margins, payout ratios, etc. It’s a wealth of information, but be cautious if running your query just after a company reported earnings. It does take a few days to update.
Up Next
In Part III, I will be providing you with a basic equity model template you can build on and show you how you can use Excel’s built-in functions to calculate standard deviation, correlation and covariances for the stocks in your stock universe. I will teach you how you can use Excel’s Solver Add-In to maximize the value of a calculated cell such as a Sharpe ratio (excess return divided by total portfolio risk) and set constraints such as limiting the weightings of a particular sector or the total number of stocks in your portfolio.
Stay tuned!