Build a Better Process
Build a Better Process
/ factorpad.com / fin / quant-101 / manage-stock-portfolios.html
An ad-free and cookie-free website.
Beginner
Videos are available at one organized Quant 101 Playlist on YouTube.
Learn to Manage Stock Portfolios in Excel (17:55)
Welcome. Today's topic is learning how to manage stock portfolios in Excel.
I'm Paul, and as someone who has spent a 20+ year career managing hundreds of millions, then billions of US dollars in stock portfolios I want to caution you that learning the institutional way cannot be accomplished in one short tutorial.
So here I will introduce you to how we will learn to manage stock portfolios in Excel first; meaning, the theories, practices, procedures and concepts. After that, you will have the knowledge to advance beyond the spreadsheet to portfolio optimization programs sitting on risk and alpha models, which is where the real money is being managed at institutional firms.
In a previous tutorial we covered each of the 7 chapters at a high level and provided an outline of the 30 tutorials.
If this sounds like too much for you, and you only have time for one tutorial, stick around and I'll give you a few pointers, that took me years to learn.
In this introductory tutorial, we will walk through the important who, what, where, when, why and how questions so you have a full grasp of how this series can help you.
First, we review what the series covers.
Second, we discuss why, besides a six-figure career and a growingly competitive and technology-driven world, you should invest your time learning institutional-level processes.
Third, we cover who you are, meaning prerequisites, interest level and directions, then who I am and my qualifications.
Fourth, we review specifics of how this course is organized.
Fifth, we cover where this all takes place, meaning where to find help and other resources.
Sixth, we discuss when is the best time to start.
And in our next episode we will kick off the Excel system setup process including installing Add-ins and downloading the data set so you can get started right away.
This is my 224th video on YouTube and Subscribers asked me to publish everything I say in text so they can go back and slow things down, examine that tricky Excel formula and take notes. So I took the time to create a web page for each tutorial. For those watching the video, the first link in the description goes straight to it.
As you will see, I put a good amount of emphasis on learning faster, and what helps with that is getting multiple senses involved. So listen to and watch the YouTube videos, read the text, jot down notes and where we have exercises I suggest duplicating them right in your own spreadsheet.
First off, what is the Quant 101 series all about?
It is designed to help you create investment models of public securities in a spreadsheet, focusing on a 4-stock data set to learn the concepts before moving to the institutional level. There, the same concepts are used to analyze thousands of stocks, typically in a programming language.
Here we consider all investment philosophies and rely less on formula notation and memorization and more on learning the concepts. Videos average 15 minutes and about half have homework exercises.
Let's cover each of those points in more detail.
We focus on individual stocks rather than private companies, and to get our mind around the scope, according to World Bank figures, the market capitalization, or value, of stocks around the globe was $65 Trillion US dollars, as of 2016.
| Region | $Value (Trillion) |
|---|---|
| Africa & Middle East | 2 |
| Americas | 31 |
| Asia | 23 |
| Europe | 9 |
| Total | $65 Trillion |
If we consider that there are generally three different types of investments, cash, debt and equity, our focus is on equities, or stocks, but no hybrids like preferred stocks.
Returns on stocks come in the form of fixed returns like dividend payments and a variable component often called capital appreciation, or capital gains. This table illustrates the point.
| Instrument | Fixed | + | Variable | = | Total |
|---|---|---|---|---|---|
| Cash | 3% | + | 0% | = | 3% |
| Debt | 4% | + | 1% | = | 5% |
| Equity | 2% | + | 6% | = | 8% |
With investments that have a variable component, gains are not certain, meaning the investment can go down in value. For this risk of loss and lower fixed rate of return, stocks over the long-term have provided a return exceeding both cash and debt securities.
Cash instruments like money market funds, CDs and Treasury Bills do not have a variable component, which means they have no risk.
Also while we are focused on stocks here, we will also spend a lot of time analyzing portfolios, which may include from 30 to hundreds of stocks.
Portfolios come a variety of structures. A mutual fund is a portfolio, as is an ETF or a pension fund. An index can be a considered a portfolio as well.
We also focus on liquid securities, or those with prices published at least daily.
Other financial modeling courses often cover the fundamental analysis of both public and private companies. There the objective is to analyze the financial statements and business prospects of individual companies in a spreadsheet, whether that be for investment banking, mergers and acquisitions or for fundamental stock picking.
Here on the other hand, we focus on portfolio construction, risk management, and portfolio performance analysis and for that all we need is a stream of monthly returns, which we will import in our next tutorial.
You will often hear me refer to three levels of experience here.
Here is how I think about it.
First, some Individuals manage their own stock portfolios, however most entrust their money to Professionals like financial advisors, wealth managers, financial planners and brokers.
Second, some Professionals manage their clients' portfolios themselves, however most entrust this money to Institutions.
Third, Institutions pool this money in the form of mutual funds, pension funds, hedge funds and manage stocks in one portfolio. This benefits shareholders with economies of scale and allows portfolio managers to specialize and focus on performance.
Using a guitar player analogy, individuals may be comfortable playing in front of friends and family. Professionals play at local gigs, while the institutional-level musician may play at recording studios or concert venues, distributing their recordings to thousands of customers.
Reaching the Institutional level takes years of education and experience, and since I've been there, I designed this series to speed up your progress, because that is where the pay and job security is best for analytically-minded individuals.
Next, for conveniene, I put investment philosophies into four broad groups.
| Passive | Fundamental |
| Quantitative | Technical |
First, Passive. Investors in this camp, frankly believe that active portfolio management does not work, especially after costs. Instead they buy a broadly diversified pool of stocks in a mutual fund or ETF. Here stock weights are determined by market capitalization or other characteristics. This includes the popular smart-beta products, which we will cover later.
The next three are forms of active management, meaning the portfolio manager deviates from the benchmark by actively overweighting and underweighting securities in the benchmark.
Second is fundamental, which you can think of as the in-depth analysis of a company's financial statements, competitive position, industry and the macroeconomic trends impacting that company. The fundamental analyst attempts to find mispriced stocks and invest accordingly.
Technical processes relate to selecting stocks based on past price movements. The goal here is to find stocks that have price momentum based on visual patterns found in charts.
Fourth is quantitative. Quantitative approaches use computers heavily to evaluate hundreds or thousands of stocks. This approach customarily attempts to maximize the forecasted return over risk ratio and tends to focus on statistics and capturing mispriced securities through diversified portfolios.
Keep in mind, these philosophies are not mutually exclusive. While some firms are steadfast about their beliefs and gravitate to the corners, others are comfortable blending philosophies.
I should clarify, you can have a nearly passive smart-beta, a fundamental or a technical process to pick your favorite stocks. But when it comes to portfolio construction a quantitative approach is often followed. So here I use the term Quant 101 merely as a label, but it applies to all investment philosophies.
As you will see in Chapter 2, we will cover stock market history from each of these perspectives, because each has its own strengths and weaknesses.
Next, a point about mathematical notation.
Throughout this series, despite the fact that we will focus on the scientific aspects of managing stock portfolios, you will see no confusing mathematical notation, or greeks as some people call them.
It has been my experience that when most beginners are faced with formula notation, frankly, they check out. Use of heavy math and symbol notation loses most of the audience, so I translated all of this into plain English. I've also translated a number of important and dense academic journal articles for you, pulling out the most relevant points.
As an example, the textbook approach to the calculation for portfolio variance is a nasty looking thing. Instead, the approach used here would be to summarize it by saying:
That, with the visual of a covariance matrix and you've nailed down the concept. We will cover portfolio risk in Chapter 3.
If this seems easier for you, then you're a perfect candidate for this series.
Next, why should you learn the scientific approach?
First, the adoption of technology in the investment space is changing the landscape dramatically. The growth of index funds, robo-advisors, smart-beta products, so the more structured approaches that encompass rules-based, academic and scientific methods have been growing. Other more artistic and subjective approaches have been in decline.
Second, public equity markets are highly efficient.
It is difficult to find enough mis-priced stocks to consistently outperform benchmarks while offsetting the higher fees associated with the chase.
As an example, I recall seeing a presentation by one individual who wrote an algorithm that performed a keyword search over every conference call transcript released each morning. It took only a few minutes for the algorithm to search hundreds of transcripts for keywords like "exceed", "strong" and "outperform" on the positive side, and "disappointed", "underperform" and "lower" on the negative end. This algorithm scored each transcript, much like Google scores web pages for its search algorithm, allowing the manager to act on these before the human fundamental analyst has time to read through the transcript.
This is an example of how quickly information is incorporated into market prices.
So finding values using the traditional fundamental way, while still effective in the Private Equity space maybe, is more difficult with public securities.
Third, firms are less inclined to align their brand with a star manager. In the last century, firms relied on names like Peter Lynch, Bill Gross and Warren Buffett to raise assets, but unlike computer code, humans leave or retire and assets flow out.
Instead, firms want consistent and predictable revenue streams so they build reliable processes incorporating big data analysis, machine learning and artificial intelligence into their models.
That said, you have to go where the jobs are, and those with a scientific and coding background, even in the portfolio management space, are in the highest demand.
And for the most important takeaway, gatekeepers, or the decision-makers at institutional-level firms are getting smarter. They control asset flows and they understand the concept of risk-adjusted return. They also can afford the systems and data sets to provide the answers they require.
Next, let's cover prerequisites and my background.
Having experience in five areas will be helpful for you.
Besides that, a strong desire to learn and challenge yourself are two characteristics that will take you a long way in this industry.
Now for my background. My name is Paul Alan Davis.
I followed the path of an undergraduate degree in Finance, and then an MBA. I started in auditing and then developed investment processes for Registed Investment Advisors in the US. I then became a CFA Charterholder, and managed $5 Billion in quantitative equity mutual funds. I was invited to speak on CNBC and Bloomberg TV and was quoted in the Wall Street Journal, Forbes, Money and gained experience presenting to mutual fund boards, clients and prospects.
Since founding FactorPad, my focus has been on education. I started with Excel and branched out to statistical programming in Python. I use Linux, Mac and Windows boxes, but my favorite is the Linux command line. I do all of my own web development so you will find tutorials on that as well, if you are interested.
What I'm most passionate about is this topic of risk-adjusted returns, analyzing portfolios and processes. I just don't think enough people know how to practically apply these skills, so that's where I focus my energies, on technology, governance and performance.
If your firm needs help with any of these please reach out to me.
Now for how the course works.
We will walk through the whole portfolio management process using only one data set. It is a 5-year history, of monthly returns, on four stocks, Microsoft, Ebay, Abbott Labs and Merck. We also have a return for the Market and one called Rf, for the risk free rate.
That whole process I mentioned includes evaluating past performance, gathering market expectations and forecasting future returns and risk. We will walk through portfolio theory, build a risk model and an optimizer. Not bad for one small data set using Excel, huh?
A quick note. We aren't trying to statistically prove anything here; instead, by limiting the size of the data set it allows us to focus on learning the concepts before moving on to thousands of stocks.
We will create a new spreadsheet file for each of our 7 chapters and copy this Returns tab to each spreadsheet, which is the topic of our next tutorial, as mentioned. Then later, in chapter 2, I will show you how to calculate returns should you want a custom data set.
Also on the page you will often see a formula and Excel functions we use during the exercises.
Now let me point you to helpful resources.
First, we have a Glossary of Terms that not only defines words, but also offers quizzes, a 'Use it in a Sentence' section and a 5-minute video with a script.
Second, post your question in the YouTube comments section that way everyone will benefit from your insight.
Third, you can also look up terms and concepts up on Wikipedia. The downside is that their authors often use formula notation and inconsistent terminology. That said, it is comprehensive.
Finally, when is the best time to start?
Think of it like this. If you could use a system to compute your probabilities of success at any moment, why wouldn't you use it? With a quantitative process you can use that advantage. It's like having a computer with you at a casino.
The scientific approach allow you to get all of the moving parts associated with the economy and individual companies out of your head. This will lead to better and more rational decisions. That's what modeling is all about.
So in the end you have to ask yourself. Do you want to do it the right way? The institutional way, where job security and pay is the highest or do you want to take the easy path and wing it? Stick with Quant 101 and you'll learn how to do it the right way.
The time is now.
So to summarize, here we defined our scope as the public stock markets, discussed why we are taking a scientific approach, prerequisites and how the course works. I then pointed you to where you can find additional help, and finally, now is the best time to take charge of your career.
So join us for the next episode, where we will get Excel set up and import the data.
Have a nice day.
For reminders subscribe our YouTube Channel and email list.
/ factorpad.com / fin / quant-101 / manage-stock-portfolios.html
A newly-updated free resource. Connect and refer a friend today.
by