Learn how to build investing/trading model in Microsoft Excel. This course will take you through the process of building a quantitative investment model. We will build our model on the Relative Strength Index, experimenting with various buy and sell parameters to see how our strategy performs. The model we will build in this course will output your performance, probability of success and average profitability per trade so that you can make the most informed trading decisions possible.
The model will be built around an analysis of technical indicators which you can use to identify trades.
Quantitative finance is all about making investing more of a science and less of an art. If you are interesting in learning how to make trades with 80%-90% success rates, this course is for you.
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:
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 on it 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:
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.
Some important qualities you need to remember when building your own models:
Hello! I am a professional investment analyst who loves data analytics, financial modeling, and quantitative finance. I have spent the last 10 years studying and investing in the stock market (and earning a B.S. in Finance along the way). I have spent way too much time modeling investments in Microsoft Excel and use the knowledge that I glean from these models to make investment decisions. I look forward to sharing what I have learned with you!