How To Rebase A Chart - - Alexander Jarvis

Have you ever wondered which stock has performed better or worse over a year? What does the landscape look like after that period? Did one company significantly outperform another, and by how much?

In investment banking, this is a basic skill. As a trader analyzing stock performance, this is a regular task. But for most people, no one teaches this essential method.

If you’re interested in comparing the relative performance of stocks, you’ll face a challenge: each stock has a different price, and they fluctuate independently. Naturally, these differences make it difficult to compare performance on a single chart. It’s like running a race where everyone starts at different positions—hard to tell who’s winning, right?

That’s where rebasing comes in.

Why Compare Stock Performance?

Imagine you’re looking at two companies: one tech giant and a newer e-commerce company. Over the past year, both stocks have had their ups and downs, but you’re curious—which one actually performed better? Did one soar ahead, or did they match pace over time?

For those in finance, knowing how to compare stock performance is essential. As a trader or investment banker, you’re comparing stocks daily. But for those new to investing or financial analysis, it’s a skill no one usually teaches.

So, let’s break it down: if you want to see how stocks perform relative to each other, you’ll face a challenge. Each stock starts at a different price, and without adjusting for that, comparing them on a chart becomes tricky.

This is where rebasing comes in. Think of rebasing as a way to bring everything to the same starting line—just like setting runners side-by-side before a race starts.

What is Rebasing, and Why Do We Need It?

In Excel and stock analysis, rebasing is the process of adjusting stock prices to a common starting point, making it easy to compare their performance over time.

Rebasing is like bringing all the runners back to the same starting line in a race. By doing this, we normalize the stocks to the same initial value, allowing us to compare their performance more effectively. The concept might sound complex, but it’s easier than you think, especially with Excel.

Imagine you’re looking at two companies:

• Company A (Google): Share price of $100
• Company B (Taobao): Share price of $200

At first glance, Taobao’s stock price seems twice that of Google’s. If you plot these two on a chart as-is, Taobao will appear much higher, making Google’s performance seem insignificant—even if Google outperforms Taobao in percentage gains over time.

At first glance, Taobao’s price is twice that of Google’s. Without adjusting for this difference, comparing these companies on a chart would be like comparing apples to oranges. But if we rebase them to the same starting price (say $100), the percentage changes over a year become comparable. Now, it’s apples to apples.

Why Rebasing Matters in Financial Analysis

Rebasing is essential for anyone interested in understanding relative stock performance. Without it, comparing stocks with different starting prices can be misleading, as price changes won’t reflect percentage changes clearly. Financial professionals rely on rebasing to present an accurate picture of how stocks perform relative to each other over time.

In practical terms, rebasing allows you to answer questions like:

• Which stock performed better relative to its starting price?
• How do different companies compare when we ignore their initial price levels?

For traders and analysts, rebasing is a vital tool for daily analysis, especially when reviewing multiple stocks side-by-side.

How the math works

The math is actually really simple. You just need to know how it works.

Formula for Rebasing

The core idea behind rebasing is to adjust the starting prices so that each stock begins from the same point. The formula used to achieve this in Excel is:

=(Rebase # / Starting Price) * Ending Price

Where:

• Rebase #: This is the number you choose as your starting point (often set to 100).
• Starting Price: The initial stock price at the beginning of your chosen time frame.
• Ending Price: The stock price at each point in time (like each day or month) as you track performance.

Example Formula in Excel

=($L$4/$D$9)*D27

=($L$4 [Rebase #] / $D$9 [ Starting price])*D27 [Ending price]

Where:

• $L$4: The Rebase # (in this case, 100).
• $D$9: The starting price for the stock.
• D27: The stock price at a specific point in time.

Step 1: Setting Up in Excel

1. In Excel, create columns for Date, Stock A Price, and Stock B Price.
2. Decide on your Rebase # (we’ll use 100 here).
3. Identify each stock’s starting price (the price on the earliest date in your analysis).

Step 2: Applying the Rebase Formula

Let’s say you have Google (starting price $100) and Taobao (starting price $200), and your goal is to rebase them both to 100. Here’s how you would set up the formula in Excel.

• In a new column (we’ll call it Rebased Stock A for Google), input the formula:

=($L$4 / $D$9) * D27

• Where:
  • $L$4 holds the Rebase # (100).
  • $D$9 is the starting price for Stock A (Google), so you’d enter 100 here.
  • D27 is the ending price you’re rebasing (use Excel’s fill function to apply this formula down the column for each date).

Repeat the same for Stock B (Taobao), adjusting for its specific starting price. This formula scales each stock price relative to its starting point, allowing for easy visual comparison.

What a chart looks like without rebasing

If you plot Google and Taobao’s stock prices without rebasing, the graph will show Taobao towering over Google since its initial price was higher. This makes Google’s performance less noticeable, even if it had a larger percentage increase over the year.

Before rebasing, the stock prices of Google and Taobao would appear vastly different due to the large gap between their starting prices. This makes it nearly impossible to compare performance meaningfully.

Example:

Without rebasing, the graph would show Taobao’s price of $200 towering over Google’s $100, and any small movement in Google’s price would seem less significant.

What a chart looks like with rebasing

Once rebased, both stocks start at the same point (100), making it much easier to visually compare their percentage movements over time.

Example:

After rebasing, the chart clearly shows how the prices move relative to each other. Even though Google’s initial price was lower, its performance is now easily comparable to Taobao’s.

What the data set looks like

The dataset on the left is the unedited share price data.

The dataset on the right is the rebased table. You can see everything starts at 100 and then starts moving around.

Sample Data and Visualization

Original Stock Prices

Date	Google Price	Taobao Price
2023-01-01	100	200
2023-02-01	105	220
2023-03-01	95	215

Rebased Stock Prices

Date	Rebased Google	Rebased Taobao
2023-01-01	100	100
2023-02-01	105	110
2023-03-01	95	107.5

The rebased values reflect how each stock performs relative to the initial starting point, making it much easier to see which stock had a stronger relative performance over time.

Common Questions and Practical Tips

Q: Why do we choose 100 as the Rebase #? Using 100 is standard practice since it makes percentage changes intuitive. For instance, a stock that rises to 120 is up by 20%, making it easy to interpret gains and losses.

Q: How often should you rebase stocks? Typically, rebasing is done once, using the first data point in your time series. However, you might rebase periodically (annually, for example) if you’re comparing across very long time frames or want a fresh baseline.

Excel Tip: Lock cells with $ in Excel to ensure formulas reference fixed values when you drag the formula across cells. For example, $L$4 ensures the Rebase # doesn’t shift as you apply the formula down the column.

Get the free template

I’m sure that was all super-fun reading, but you want to really have a play so you can learn.

To help you get started, download our free Excel template pre-filled with rebasing formulas and a sample dataset. This template will walk you through setting up your own comparative stock analysis, so you can practice rebasing and apply it to real-world data.