How to do Indexing of Time Series

My previous article (Yiu, 2021) discussed the use of normalized stock market indices to compare their rates of change. This article is just a supplementary for explaining how to normalise two indices to the same initial value at a certain point of time (indexing). I will use the residential average rents and prices of Hong Kong as a demonstration.

Indexing is “an effective means of normalizing data to a common starting point and observing how variables change over time relative to each other.” (Dallasfed, nd) Here I try to compare the changes in rents and prices of Class A residential properties on Hong Kong Island, as people often claims that average property prices in Hong Kong have risen faster than rents. Is it true? How can we test it?

Usually, people would calculate the increases of average rents and prices in the past three months, one year, three years, or five years to compare their growth rates. However, this approach does not allow a continuous comparison of the two series. A better way is to plot the two series to visualise the changes over time. But since the two series are of very different magnitudes, a chart cannot help compare clearly on their changes. Even if we use a secondary vertical axis for one of the series, it is still misleading because the left and right axis are of different ranges and units of measure (Figure 1). For example, from 2015 to 2019, the price of Class A residential properties on Hong Kong Island (in HK$ per square meter) rose from about $150,000 to about $200,000, and the rent rose from about $400 per square meter per month to about $500. The rates of increase of the two are similar, but from the chart, it seems that property prices (orange line) have risen much higher than rents (blue line), which can be misleading.

Figure 1 Average rent and selling price of Class A residential properties on Hong Kong Island. Source: Rating and Valuation Department https://www.rvd.gov.hk/en/property_market_statistics/index.html

Indexed residential rents and prices

If the average rent and price are indexed, that is, the rents and prices are locked at the same starting point at a certain point in time, then their continuous changes can be directly compared. The calculation method is very simple, just normalise them proportionally.

Figure 2 extracts some of the data for illustration. First, the two left columns (columns 3 and 4) show the average rents and prices used in Figure 1. The rent at the beginning of 2015 was about $400, while the price was about $150,000.

1. Convert the first data to 100

If the index for January 2015 is locked to 100 (orange cells) [it is usually converted into 100, but 1 or 1000 are also commonly used], then the cells below can be calculated by the formula shown in Figure 2. They become the monthly rental index and price index (columns 5, 6).

Figure 2 Average rents and prices to calculate the rental and price indices of Class A residential properties on Hong Kong Island. Source: Rating and Valuation Department https://www.rvd.gov.hk/en/property_market_statistics/index.html

Take the last row (May 2015) as an example, the rental index of this month is (424/407)x100 = 104.18. Copy the formula and paste to the whole column in Excel will provide the rental index.

Comparison of residential rental and price indices

Let’s plot a graph of the indices to see the advantages of indexing. Figure 3 shows that when the series are both normalised to 100 in January 2015, the increase in rent and property prices before mid-2019 can be shown clearly that they were highly similar.

Figure 3 Hong Kong Island Class A residential rental and price indices. Source: average rents and prices from Rating and Valuation Department. https://www.rvd.gov.hk/en/property_market_statistics/index.html

Take the recent peak in August 2019 as an example, the rental index was 130.96 and the price index was 138.57. That is, the rent has increased by 30.96% in this period, while the price has increased by 38.57% (this indexing method also simplify the calculation of the growth rate relative to the initial fixed point, % of increase = index- 100). On the contrary, if compared with the recent lows in February 2016, the price index was 87.93, which means that property prices have fallen by 12.07%. However, after August 2019, rents and prices have diverged. So far, rents have fallen from 130.96 to 106.88, while property prices have ramped up to 133.04. Indexing makes comparison easier and clearer.

Comparison function in google finance — indexing

Indexing is widely used in economics and finance. For example, the comparison function of google finance can also be regarded as a kind of indexing. From the indexing formula, it can be seen that the index is to calculate the ratio between the values of the series at two time points. If the index is subtracted by 100, it will equal to the growth rate, which is displayed as a percentage in google finance.

Figure 4 S&P500 and Hang Seng Index. Source: google finance

As shown in Figure 4, using the comparison function of google finance to compare the US S&P500 Index and the HK Hang Seng Index, the two indices will then be indexed by setting the growth rates to 0 on January 4, 2021 (because I choose to plot a YTD chart). The growth rates of the two indices in the period can now be easily compared.

Video: youtube — https://youtu.be/vNooiiZ7axw
References

Dallasfed (n.d.) Indexing Data to a Common Starting Point, Federal Reserve Bank of Dallas. https://www.dallasfed.org/research/basics/indexing.aspx

Yiu, C.Y. (2021) Normalised Indexes — a better way of data visualisation for investment performance comparison，Medium，November 20. https://ecyy.medium.com/normalised-indexes-a-better-way-of-data-visualisation-for-performance-comparison-33007db75d12