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:

ETFCode
S&P 500SPY
Emerging MarketsEEM
Nasdaq 100QQQ
Gold MinersGDX
China Large CapFXI
Non-US Large StocksEFA
Russell 2000IWM
Oil and Gas ExplorationXOP
Barclays High YieldsJNK
US Real EstateIYR

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.

E

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.

ETF Table Sorted - Google Sheets

Video

You can watch me creating the portfolio in this video.

YouTube video

Get More Winning Trades

If you are eager to take your trading strategies and market analysis to the next level, consider exploring Tradinformed Excel Backtest Models. These Excel-based models will help you design, optimize and track your trading strategies.

The 15-in-1 Package is our most powerful package. This will help you learn how to backtest trading strategies and make informed trading decisions while providing you with the tools you need to develop your own trading systems.