Stock Sector Investing Via Quantitative Modeling In Excel

Introducing the Stock Sectors Preview 09:44

This gives you a brief overview of the different stock sectors and the respective ETFs used in this strategy.

What Is Sector Rotation Preview 04:47

Explains the rationale behind Sector Rotation

Sector Scoring System Preview 12:57

This lecture introduces the measure we use to score and rank the different sectors.

Adding a Dynamic Natural Hedge Preview 15:54

We will see in this lecture what is a suitable candidate for a hedge and how to incorporate it into the system such that it adjusts dynamically according to market conditions.

Boosting Returns With Leverage Preview 07:59

We will see how we can further boost the returns of our sector rotation strategy using leverage.

Strengths and Weaknesses of Sector Rotation Preview 05:46

We will look at both the strengths and weaknesses of the sector rotation strategy.

Important Notes Preview 00:25

Information on how to approach this section.

Basic Math Operators And How To Reference Cells Preview 06:34

You will learn the basic math operators and how to reference other cells in Excel.

Manipulating Dates and Catching Errors Preview 08:04

You will learn how to use the following functions:

1. MONTH()

2. YEAR()

3. TODAY()

4. DATEVALUE()

5. ISERROR()

How To Look Up Data Preview 15:42

You will learn how to use the following functions:

1. VLOOKUP()

2. HLOOKUP()

3. LARGE()

Logic Functions Preview 08:49

You will learn how to use the following functions:

1. IF()

2. AND()

3. OR()

Mathematical Functions Preview 17:09

You will learn how to use the following functions:

1. AVERAGE()

2. SUM()

3. PRODUCT()

4. SUMPRODUCT()

5. SUMIF()

6. MAX()

7. MIN()

8. COUNT()

9. COUNTIF()

10. ROUNDDOWN()

Rank Function Preview 04:54

You will learn how to use the RANK() function

Important Notes Preview 00:15

Information on how to approach this section. Please read before proceeding.

Working With Returns Preview 28:17

You will learn the intuition, math and excel implementation of the following:

1. Simple Returns

2. Compounding Returns

3. Net Asset Value (NAV)

4. Compound Annual Growth Rate (CAGR)

5. Log Returns

Correlation Preview 13:59

You will learn the intuition, math and excel implementation of an important statistical measure called correlation which measures how one asset move relative to another. This is a useful measure that can help us identify suitable candidates that can complement each other to bring out maximum diversification benefits.

Single Asset Risk Preview 16:24

You will learn one of the most widely used risk measures in the financial markets - Volatility for a single asset.  You will know what it means, how to calculate it, and how to implement it in Excel.

Investment Performance Metric Preview 08:56

You will learn 2 useful investment performance metrics Sharpe Ratio and Maximum Drawdown, how to calculate and implement them on Excel.

Sector Rotation Walk Through (Strategy) Preview 06:49

This lecture gives you a quick recap of the Sector Rotation strategy we will be building in this Section.

Sector Rotation Walk Through (Excel) Preview 09:47

This lecture gives you a quick walkthrough of how the completed Sector Rotation Model looks like in Excel. You will see how the data is structured and what are the information inside.

Important Notes Preview 02:33

Important notes on how to proceed with the lectures in this Section. Please watch the video before continuing.

Getting The Price Data (Windows Users) Preview 11:26

This lecture shows you how to use the Excel tool Multiple Stock Quote Downloader (Sector) to download the price data from Yahoo Finance in bulk for Windows users.

Getting The Price Data (Mac Users) Preview 15:58

This lecture shows you how to use the Jason Strimpel website to download the price data from Yahoo Finance in bulk for Mac users.

Setting Up The Data Preview 06:05

This lecture sets up the price data for use in the excel spreadsheet.

Setting Up A Basic Portfolio Structure Preview 15:04

You will learn how to model a basic portfolio structure on the spreadsheet to compute daily portfolio returns.

Implementing a Buy and Hold Portfolio Preview 08:25

You will learn how to model a buy and hold portfolio

Building Monthly Rebalancing Into The Portfolio Preview 07:39

You will learn how to model a portfolio that is rebalanced at the end of each month back to equal weights.

Calculating The Volatility Preview 05:39

You will learn how to calculate the volatility of the Sector ETFs

Calculating The Sector Score Preview 02:33

You will learn how to implement a scoring system for the sectors.

Ranking The Sectors Preview 03:56

You will learn how to rank the sectors according to their scores.

Allocating To The Selected Sectors Preview 08:38

You will learn how to implement an allocation to the top sectors.

Preparing the Hedge Criteria Preview 08:05

You will prepare and compute the criteria needed to determine the hedge size.

Determining The Hedge Size Preview 09:36

You will learn how to determine the size of the hedge to apply at rebalancing.

Adjusting The Sector Allocations Preview 05:00

You will see how to adjust the sector allocations with the hedge in place.

Incorporating The Transaction Costs Preview 19:01

You will learn how to incorporate transaction costs into the model.

Executing On Market Open Preview 13:43

You will learn how to adjust the model to take into account execution on the market open of the following trading day instead of at the market close of the current trading day.

Performance Analytics - Calculating The Monthly Returns Preview 11:16

You will learn how to aggregate the daily returns into monthly returns.

Performance Analytics - Calculating The Monthly NAV Preview 01:46

You will learn how to calculate monthly Net Asset Value (NAV)

Performance Analytics - Calculating The Monthly DrawDown Preview 07:38

You will learn how to track the month by month drawdown.

Performance Analytics - Populating The Month By Month Performance Table Preview 06:47

You will learn how to populate the month by month performance table.

Performance Analytics - Populating The Performance Metrics Preview 11:30

You will learn how to calculate and populate the performance metric summary table.

Incorporating Leverage Preview 13:27

You will learn how to incorporate leverage and its associated borrowing costs into the model.

The Operating Dashboard Preview 14:09

You will learn how to build up the operating dashboard which shows you the critical information such as the percentage allocation to each asset, the amount of capital to be allocated to each, and how many shares you should be holding as of rebalancing.

The Completed Sector Rotation Model Preview 00:08

This is the completed Sector Rotation Model.

Operating and Updating The Sector Rotation Model (Windows) Preview 09:25

You will learn how to update the sector rotation file going forward and how to make use of the results for investing (for Windows users).

Operating and Updating The Sector Rotation Model (Mac) Preview 11:21

You will learn how to update the sector rotation file going forward and how to make use of the results for investing (for Mac users).

Additional Note For Updating When You Cross Into A New Year Preview 00:20

Updating the Loan Rate and Avg ON Libor table

Exercise in Daily Operation of Model

This exercise is to help you ensure that you have built the model correctly and that you are able to run it daily and get the correct output. To help you check against your model output, you can subscribe to a one month free trial of AllQuant Portfolio Signal service.

Data Copying and Formatting (Windows) Preview 09:23

You will gain an appreciation of how VBA scripts are used to automate the populating of data onto the Sector Rotation file after downloading it using the free Excel tool (for Windows users)

Data Copying and Formatting (Mac) Preview 21:57

You will gain an appreciation of how VBA scripts are used to automate the populating of data onto the Sector Rotation file after downloading it using a web-based tool (for Mac users).

Bonus Lecture - Our Other Courses Preview 00:25

Check out our full list of courses!