Build a Better Process

Quant 101: Learn Quantitative Investing in Excel

Quant 101 is a series of financial modeling tutorials designed to help you learn concepts of quantitative finance in Excel.
  1. Self-starters - Join the modern and lucrative world of quantitative investing with a free self-study series.
  2. Your schedule - Tackle web-based text and video tutorials on your own time and at your pace.
  3. Keep it simple - Import a free four-stock data set to Excel which is all you need for the whole series.
  4. Institutional - Learn institutional-level processes from a former $5 Billion equity Portfolio Manager.
  5. Consistent - Follow a consistent format to save time.
  6. Next: the Outline - See the full list of content.
face pic by Paul Alan Davis, CFA
Updated: February 19, 2021
Yes, you can learn quantitative equity portfolio management in Excel and work towards a six-figure income, and the job security that goes with it.

Outline Back Tip Next

/ factorpad.com / fin / quant-101 / index.html

An ad-free and cookie-free website.

What is Quant 101?

Quant 101 is a hands-on series of 30 video and web-based tutorials on equity portfolio management, with a focus on the scientific aspects of risk management and portfolio optimization.

Think of each tutorial as a financial modeling exercise that helps you learn the practices used at the largest institutional asset management firms offering the highest pay for technically-minded individuals.

What Will You Learn?

Once you learn the concepts in a spreadsheet, you can extend your newly-acquired scientific knowledge to programming, which is where scalable quantitative processes are built. Concepts learned here transition well to programming with statistical analysis software like R or Python, and we show you where to make these connections.

We put emphasis on real world best practices and point you to where you can keep learning and advancing.

The series is broken into 7 chapters with videos to keep it engaging and text so you can go back and read the details

How Does is Work?

After downloading a text file and importing it to your Excel spreadsheet, we go through the process with you, building one financial model after another until you know how to calculate return, measure risk, generate a covariance matrix and perform a portfolio optimization. Yes, you can learn to do all of this in Excel.

The goal is to learn quickly and the best way to do that is to use all of your senses. Read the text, listen to and watch the videos and get your hands involved by duplicating what we do right in your own spreadsheet.

A Preview

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

Check out the preview here.

Learn Quantitative Investing in Excel (7:41)

Video Scripts

If you missed something in the video, the whole script is found on a web page by clicking the first link in the video Description. This helps you save time by reading through the difficult parts and checking your work, including those complicated cell calculations.

Nearly all of our video content is duplicated on the website for your convenience.

Download Data Here

The free sample data set is a text file called returns.txt. A link is found at the main page for the Quant 101 series. For those watching the video, see the link in the Description.

The data set is a 61-row and 7-column file that includes monthly returns for four US stocks (Microsoft, eBay, Abbott Labs and Merck), an index and the return on US Treasury Bills for 60 monthly periods. This will be all you need for the whole series. Also, you don't need to sign-up to get it.

Alternatively, if you would like to select your own stocks, you can always customize your own data set. We offer step-by-step instructions to show you how.

An Overview of the Chapters

Together we will create one spreadsheet per chapter with a tab for each tutorial. Some have exercises for you to practice while others offer more of a background. In each of the spreadsheets, we copy over the Returns data set for convenience.

Let's review what you will learn in each chapter.

Chapter 1 - Getting Started with Quant 101

In the opening chapter, after this tutorial, is the outline and then an orientation tutorial and one that walks through step-by-step instructions for Excel Add-ins and the download of the sample data set. (4 web pages, 3 videos)

Chapter 2 - Stock Returns, Distributions and History

In Chapter 2, we calculate both arithmetic and geometric returns, learn the intricacies of how corporate actions impact a stock's return, and then create a frequency distribution and histogram.

We also walk through stock market history highlighting a timeline of advancements in the investment space detailing several tracks. First, the external influences brought on by technology, current events and from academia. Then we touch on advancements from investment approaches like fundamental analysis, behavioral finance, quantitative portfolio management and technical analysis. (4 web pages, 4 videos)

Chapter 3 - Stock Risk, Portfolios and Theory

In Chapter 3, we walk through calculating risk for individual stocks before moving on to portfolio return and risk, then the covariance matrix and charting portfolios in Excel.

The chapter concludes with advancements from portfolio theory from the perspective of the work by Harry Markowitz on Modern Portfolio Theory in the early 1960s.

We also cover advancements a decade later by William Sharpe and other authors who advanced Capital Market Theory and the CAPM Model, which helps us set expectations for stocks. (6 web pages, 6 videos)

Chapter 4 - Statistical Concepts in Finance

It is in Chapter 4 that our love affair with regression begins. We start by comparing correlation with regression and take a deeper dive with calculations in Excel and interpret their meaning for equity markets.

Then we create a portfolio and perform a single-variable linear regression and analyze performance with common measures such as the Sharpe Ratio, Treynor and Jensen's alpha. (4 web pages, 4 videos)

Chapter 5 - Portfolio Algebra with Arrays

In Chapter 5, we discuss time-series modeling and issues that often come up during studies while working through exercises in Excel.

Then because array calculations are helpful for the transition to programming, we cover linear algebra operations and portfolio algebra including transposing, inverting and multiplying scalars, vectors and matrices. (4 web pages, 4 videos)

Chapter 6 - CAPM and Expected Return and Risk

In Chapter 6, we introduce the CAPM Model and see how it is used to evaluate what is baked-in to market expectations for stocks.

We work with the =LINEST() and =INDEX() functions to customize regression output and employ the Security Market Line.

As we shift to risk, we walk through risk contribution and then create a portfolio and compare it to a benchmark, performing a decomposition of both return and risk. We conclude with a comprehensive look at the linear regression output using Excel's Data Analysis method to solidify concepts from statistics. (6 web pages, 6 videos)

Chapter 7 - Portfolio Optimization

In the final Chapter 7, we cover the Security Characteristic Line and evaluate the inputs to the Single-Index Model. Here, to speed things up, we name ranges of data in Excel, which is similar to a practice used in programming.

We conclude with a portfolio optimization case using Excel's Solver functionality to maximize a portfolio's Sharpe Ratio. We introduce the concept of constraints, portfolio turnover and discuss takeaways for further advancement and study. (4 web pages, 4 videos)

Related Content

Other helpful Financial content found at factorpad.com includes a glossary of investment terms and information about innovators in the field of Finance. Technology-related content revolves around coding for data analysis and web publishing.

The content for this course comes from many text books, academic journal articles and from decades of experience as a practicing institutional-level portfolio manager.

The trends all seem to point to further adoption of quantiative investing, as evidenced by the growth of smart beta funds. So if learning the scientific approach to investing is important to you, then your time will certainly be well spent here.

Join us any time you like and have a nice day.

Questions and Answers

Q:  What is the best way to access the videos to prevent getting lost on YouTube?
A:  Two options here. First, go straight to the YouTube Playlist (opens a new window), or second, use the navigation on the next page, the Outline.

Q:  Which programs will we use?
A:  The System Setup page walks through all of the details for setup and installation.

Q:  What are the prerequisites?
A:  Have a familiarity with spreadsheets, basic statistics, stocks and financial markets, algebra and a strong desire to learn.

Q:  Is this video series free?
A:  Yes. If you want to help please tell a friend, mention it on social media or create a hyperlink to this website page on your own website.

Q:  Will additional content be released?
A:  There are many directions we can take this in the future. Please leave feedback on YouTube as that is the best way to give us direction and to help other people who may have the same question as you.

Q:  Can I alter the speed of video playback?
A:  Yes. Follow this link to YouTube's HTML5 help page to ensure your browser is HTML5-compatible. If so, click the settings cog to alter playback speed.

What's Next?

First off, make sure you don't miss new videos by subscribing at our YouTube Channel. Also, new content reminders are sent to Twitter @factorpad and the no-spam email list.

Outline Back Tip Next

/ factorpad.com / fin / quant-101 / index.html

quantitative investment
financial modeling in excel
quant investing
quant equity portfolio management
portfolio management
risk management in excel
excel finance tutorials
stock portfolios in excel
modern portfolio theory
smart beta investing
portfolio analysis
quantitative equity portfolio management

A newly-updated free resource. Connect and refer a friend today.