Monitor a Relative Strength Strategy using Google Sheets

Last Updated on September 21, 2023 by Mark Ursell

Imagine you are watching a horse race. The runners and riders are at the starting gate. The starter’s flag goes down, and the horses are away.

Your horse starts well and is in the lead. But, after a while, it gets tired and runs slower. Other faster horses overtake your horse, and you watch as your exhausted horse drifts to the back. Usually, you would rip up your betting ticket and start looking at the next race. But in this race, you have the option of betting in-play. So, as soon as your horse starts to tire, you can switch to the next strongest horse. Not only that, but you can change as many times as you want.

Relative strength is an incredibly powerful tool to analyse and trade the financial markets. Everyone from long-term investors to high-speed day traders uses relative strength to help them trade. In this article, I will show you how you can set up Google Sheets to analyse and monitor relative strength.

This article has an accompanying video, and you can watch the video below:

Relative strength

Relative strength measures the returns of one sector or market compared to another. A popular ratio is measuring the performance of the S&P 500 Index and the Nasdaq 100. I have written about this a few times before. You can read the articles here: How to Test a Stockmarket Trading Strategy and Invest Profitably Using Relative Strength.

In this example I am comparing 10 different markets but you can include as many different markets as you like.

Google Sheets

I use Google Sheets to monitor my trading strategies. Google Sheets is a useful tool for traders because it has a direct link to Google Finance. Google Finance has a massive amount of financial data, and you can tap directly into this and use it in your sheets.

The formula =googlefinance()  gives you access to a vast amount of data from Google Finance. You can access live price data, historical price data, profitability ratios, and market capitalization.

Store Live Price Data in Google Sheets

I have written a guide about how to store live price data in Google Sheets. You can get this guide for free simply by subscribing to the Tradinformed Newsletter.

Relative Strength Portfolio

It has never been easier to compare different markets. In this example, I am using ETFs (Exchange Traded Funds). Using ETFs you can monitor and trade a vast array of different markets and sectors.

I am using a selection of some of the most popular ETFs. The markets that I am using are:

Set up the markets

Firstly I will set up a table using the information in the table above. Leave a gap of 1 row between each market.

To watch me demonstrate how I set up the table, check out the video below.

Set up the Dates

I measure relative strength over two time periods. The 30-day time period, which I call Recent Strength. A 90 day time period, which I call Medium Term Strength.

To get these dates I set up the portfolio using the formula:

today’s price: =today() price 30 days ago: =today() – 30
price 90 days ago: =today() – 90

Enter these formulas above the table you have just set up. Again, leave a space of 1 column between each date.

Get the Price Data

Now you have set up the table, use the formula =googlefinance(code, date) to return the price on a specific date. You will notice that this formula returns an array of 4 pieces of data and this is why we left the gaps in the table.

To make the information more useful, I make a copy of the table and then crop out the data that I don’t need. The table should look something like this. Again you can see me demonstrating how to do this quickly in the video below.

Calculate Relative Strength

In this example, I calculate relative strength as a percentage. I am using two different time periods:

Short-term strength:  (current price – previous price)/previous price

Long-term strength: (current – previous price)/previous price

Next, I take the simple average of these percentages.

Sort the ETFs

The next thing that I want to do is make the data easier to read and understand. Google Sheets is very flexible. The way I sort the data is to use the =rank() formula to find the order of strength.

Then I use the formula =index(match()) to create a new table that sorts the data from the strongest to the weakest. Using this approach you can make a table like this, that lists your ETFs from the strongest at the top to the weakest.

Video

You can watch me creating the portfolio in this video.