Quantitative Investment Analysis in Excel (Template Incl.)

Welcome to the course! Follow through this course to build your own quantitative investment model! Thank you for joining!

In this video we briefly discuss:

• Quantitative finance 
• Introduce the Inflection Point Model, the trading model we will build through this course.  You are welcome to keep and improve it.
• Discuss what you will be able to do at the end of the course (aside from having an actionable trading model).
  • Be able to extract stock market data from the web
  • Be able to calculate Relative Strength values for any stock
  • Be able to build models around entry and exit points in a stock
  • Determine profitability/ define risks of a trading strategy
  • Use this information to make informed trades

Before we can build the Inflection Point Model, we must first get familiar with how it works and what information we will need in order to build it.  

See attached for Instructor's version of Inflection Point Model for your reference as you build your own version.  

We will be building a trading model called the Inflection Point Model.  This video introduces how the model looks in Excel, it's functionalities, and the optimization function. This is a peak into what your model will look like when it is complete. 

To build The Inflection Point Model we first need to extract historic stock data from the internet.  In this video, we will pull several years of GLD data and then perform some calculations in order to get the technical indicators needed to build the Inflection Point Model.  

We've just completed calculating the RSI. From here, we need to get Excel to take that data and clean it so that it can recognize when an entry or exit parameter is met. In this video, we will introduce how to do this. 

We're about to write the heftiest code in the whole model.  Don't worry! It's not as hard as it looks.  In this video, we will introduce the profit and loss calculators and how they work on a conceptual level.  

Excel can calculate the profit and loss of any position that would be placed.  This video explains how to automatically determine the price changes from when you enter to when you exit a position.  

Sometimes you will have to put multiple positions on at once (the RSI is below your entry trigger for multiple days).  In this video, we build the function that will identify when a second, third, fourth, etc. position must be placed.  Once Excel recognizes when a subsequent purchase must be placed, it will run a new set of profit/loss calculations. 

Excel's formula bar only permits 64 levels of nesting.  In this video, we figure out a way to get around that limitation by adding a new column of profit/loss calculations that pick up where the previous column leaves off. 

This video shows the final steps of the calculations needed to determine the profit/ loss of the strategy you define.  Specifically, this video shows how to calculate the P/L for the subsequent purchases. 

Errant Trades are trades which are triggered by our entry parameter, however, the trade lasts long enough that Excel fails to recognize the exit parameter.  These are infrequent, however, to ensure the quality of our results, we have to build a function that will identify this situation if it happens.  This video reveals the code and steps necessary to build this function. 

In this video we build out the calculations needed to build the summary data table (the table that shows how many trades we make, how many were successful, shows our total profit, errant trades and average profitability per trade). 

In this video we:

• Calculate probability of success 
• Find profitability of each trade, given the entry points we define
• Determine average profitability per trade (Remember! That's profitability per single share traded)

Create a table that outputs your performance given every combination of entry and exit points.  From there, you can select the entry parameters that fit your personal risk tolerance.  On the second table, you will also see how you would have performed if you had stuck to your strategy for all of the years of GLD's data. 

We will explore the functionality of the optimization function and discuss how to use the model to live trade. 

Some important qualities you need to remember when building your own models:

• Ensure your formulas extend to cover all of your data. If you insert a new stock with more data than the model is currently designed to handle, you will need to pull the formulas down further. 
• Stock picking: Ideal candidates for this strategy have volatile RSIs.  ETFs make excellent candidates for this strategy along with Blue Chips.  Not every stock will be a good candidate for this trading style. 
• You will want to do this analysis on 15 different stocks and find the ones that are the most successful.  Add those to your watch list and monitor them until they pass through their buy triggers. 

Thank you!