Comparing data across time isn’t always simple, but it’s usually necessary. A few classic tricks can make it easier to parse trend from noise.
When examining data trends, the primary tool in the spreadsheet wizard’s toolbox is indexing. When data are indexed, it means all of the data points are compared to their level at some particular point in time. That gives every trend the same starting point, and helps us properly compare data points across time in order to see them in context.
Take stock market investing.
Bored during the Covid-19 lockdown, a friend recently decided to start buying stocks through mobile banking app Robinhood, figuring it would make for a fun way to kill time and learn a bit more about the stock market.
One of the stocks he bought was Netflix. By mid-September he was quite pleased with himself. Since the beginning of April, when he purchased his shares, the stock price had jumped by 34% to $488, from $364.
I decided to try and burst his bubble (as any good friend would).
To truly determine whether he made a good bet, my friend couldn’t just look at his gains. He had to compare them to the performance of a stock market index like the S&P 500. If Netflix hadn’t performed any better than the S&P since April 1, than Netflix wasn’t actually a particularly good investment. He could have just put his money into an index that represented the market in general.
To show him the difference, I indexed both the S&P 500 and Netflix’s stock price to 0 for April 1, and then calculated the percent change since that starting point for every day since. Had my friend sold his stock in early July, he would have beaten the stock market. But by mid-September, while Netflix had gained 34%, the S&P was actually up 37%.
Illuminating, right? I still didn’t feel my point was made.
Although it is clear the S&P was doing as well, or better, than Netflix since April, it’s hard to see by exactly how much.
To make the comparison clearer, I dug a little further into the spreadsheet toolbox by computing the “alpha” between Netflix and the S&P. The alpha is calculated by taking the difference in growth between two data series—in this case by subtracting Netflix’s percent changes from the S&P’s. The higher the alpha, the faster that data series grew than the other. So for example, if Netflix gained 10%, and the S&P 15%, the alpha would be -5%.
Netflix’s alpha versus the S&P 500 has bounced from positive to negative several times since April. If my friend had sold his stock when it was positive, he would have beaten the market. But in mid-September, so far, the stock market had beaten him.
The exercise of indexing and then looking at the difference—or alpha—between two trends is incredibly powerful. It’s useful for comparing all sorts of data, from GDP growth across countries, to changes in Google search trends.
You don’t always have to index data series to the same date. For example, you probably don’t want to use the same date when comparing Covid-19 case rates across countries, because the virus landed in some countries much earlier than others. Instead, most researchers index data to the first day that country met a certain threshold of cases (for example, one case per every 10 million people). This makes it possible to compare the trajectories of Covid-19 in China and the US, even though cases started in these countries on different dates.
The video below shows how to index stock price data in spreadsheet softwares like Microsoft Excel and Google Sheets. It’s easy when you know one fun trick: