FactorPad
Faster Learning Tutorials

Learn to manage stock portfolios in Excel

If you are an aspiring institutional investor eager to learn the quantitative approach, this introduction answers common questions.
  1. What? - Learn what Quant 101 is about.
  2. Why? - Outline reasons why you need to learn a scientific approach.
  3. Who? - Cover your current skills and my background.
  4. How? - Understand how the course works.
  5. Where? - Locate other helpful resources.
  6. When? - Understand that the time to start is now.
  7. Next up: System setup - Install Excel Add-ins and download our data set.
by Paul Alan Davis, CFA, March 22, 2018
Updated: July 21, 2018
A spreadsheet is the best place to learn to manage stock portfolios, and this series offers a bridge to institutional-level practices.

Outline Back Next

~/ home  / finance  / quant 101  / manage stock portfolios


Learn the modern approach to managing stock portfolios

Beginner

Watch the Video

Learn to Manage Stock Portfolios in Excel (17:55)

Videos are available at one organized Quant 101 Playlist on YouTube (opens in a new browser window).

Video Script

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.

Overview

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.

Step 1 - What is Quant 101?

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.

a. The publicly traded stock markets by region

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
Source: The World Bank - Market capitalization of listed domestic companies (2016)
b. Three broad types of investments

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.

c. Three levels of investor experience

You will often hear me refer to three levels of experience here.

  1. Individuals
  2. Professionals
  3. Institutions

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.

d. Four investment philosophies

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.

e. No mathematical notation!

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:

  • Portfolio variance is the sum of the squared weights times each cell of the covariance matrix

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.

Step 2 - Why Learn the Scientific Approach?

Next, why should you learn the scientific approach?

a. The trend

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.

b. Markets are highly efficient

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.

c. Follow the jobs

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.

d. Gatekeepers are more sophisticated

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.

Step 3 - Who are you? Who am I?

Next, let's cover prerequisites and my background.

a. Your knowledge level and prerequisites

Having experience in five areas will be helpful for you.

  • Math - For math, an understanding of algebra is important. Some experience with linear algebra, like arrays and matrices is helpful. Everything we do here is linear and involves two dimensions that can be depicted on an x and y axis, so no calculus is required at this stage.
  • Statistics - As for statistics, if you have worked with the bell-shaped curve, correlation and single-factor regression, probabilities and descriptive statistics you should be good to go.
  • Investments - A knowledge of investment vehicles, indexes, benchmarks, ETFs, portfolios, security pricing, corporate actions, dividends, splits, spinoffs, portfolio theory, and stock valuation ratios like the P/E ratio will all come in handy, but I will cover each one.
  • Performance - We will cover arithmetic and geometric returns, portfolio performance, risk-adjusted returns, performance attribution, so any prior experience with these will be helpful, but not required.
  • Excel - Familiarity with some Excel functions, importing files, charting and array functions will be helpful but I will walk through the steps with you.

Besides that, a strong desire to learn and challenge yourself are two characteristics that will take you a long way in this industry.

b. My background and qualifications

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.

Step 4 - How Does the Course Work?

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.

Step 5 - Where Can You Find Help?

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.

Step 6 - When is the Best Time to Start?

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.

Summary

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.

Step 7 - Next: System Setup

So join us for the next episode, where we will get Excel set up and import the data.

Have a nice day.


What's Next?

I encourage you to check out our YouTube Channel. Subscribe straight from here.

  • To access all tutorials, click Outline.
  • For the Outline to the series, click Back.
  • For Excel system set up of Add-Ins and a data set, click Next.

Outline Back Next

~/ home  / finance  / quant 101  / manage stock portfolios



 
 
Keywords:
manage stock portfolios in excel
stock portfolio management
portfolio investments
quantitative finance
quant 101
portfolio investment
asset management
institutional portfolio management
financial modeling
investment models
equity risk model
investment algorithm
quant 101 prerequisites