I was on a research to figure how how the Hong Kong housing price changed from years and compare by region. Although there is the chart provided by the Rating and Valuation Department. They only provide the number with three different(Hong Kong, Kowloon and New Tertiary. And the data was come with the five category by the area of the house. But there is no index that I could use to represent the overall price change for each region, and later on to the whole SAR. In order to get the overall price change for each region, I used the data from the Rating and Valuation Department, and by weighted arithmetic to create create an overall price for each region, they I can add them together to create a overall index of whole Hong Kong. And compare that data to with another data showing the overall data.

there is a note on the data I used for this is “Changes in average prices between different periods may be due to variations in the characteristics of different properties being analysed, and should not be taken as necessarily indicating a general change in value over the period. To measure price changes over the relevant periods, please refer to the price indices.” The problem about the price indices is that price indices only show whole Hong Kong as a group and it don’t have data by district. The reason I calculator an overall data of Hong Kong is to look at how different will the price index and my calculation are. and that will be the error of my calculation to on district. The last step is to use the error to adjust my final district output.

The raw data come with monthly(after 1999). In order to fit the total size of the chart, I first need to add thing together to come up a quarterly. Another benefit to do this is since some of the data come with less than 20 transactions(especially for the property 160 m2 or above), so when I put three month data together it makes whole data reflect the market more(data come with less than 20 transactions are easily been influenced by the extreme value).

Process:
The first thing I found out here is that there excel is great by how it looks but that don’t means it good in the system. There are a lot of rows and columns are used just for place-hold in order to make the whole excel looks good. My guess for that is this is an old table coming from 1998(they don’t have much data visualization to do at that time and people look up data on print) and the publisher keep updating on it.

So the first step is to clear the sheet. Make the sheet easier for us to use. I removed all of the description cells and the blank cells. Now it is the data clear and only with necessory cells. The A/B/C/D/E stand for the area less than 40m2/40-69.9/70-99.9/100-159.9/160m2 and above.

Then next step I work was to make some simple calculation. Calculate the average number for each three month, and output the results in a new table. and then we can create a chart reflecting the price change by time of three regions.

and finally, I will use the weighted arithmetic mean to get the overall price. later compare with the price index.