House Price Tracker

A collection of programming scripts that generate a graph of house price data for Cressingham Gardens Estate. The scripts, documented on github at https://github.com/SpecialPurposeVehicle/priceIndex, generate the graph seen at http://plot.db-estate.co.uk. The graph combines data from five different sources and is automatically updated on a daily basis. These sources include:

Anxiety

As of February 2018 around 14 homeowners had sold their homes to Lambeth. A number of Cressingham residents were concerned that homes had been under-valued which might set a precedent for further valuations. I wrote the script to address a niggling anxiety that Lambeths proposed regeneration was affecting property values - a graph promised to provide some insight. I first wrote a small command-line script called “prices.sh” to obtain house price data from the Land Registry (see extract below).

A Cressingham resident suggested that I incorporate average house price values for the whole of the London borough of Lambeth. Cressingham house prices appeared to track Lambeth’s average house prices until around 2012 which is when Lambeth’s consultation over the future of Cressingham commenced. After 2012 one, two, and three bed properties appear to suffer from ‘regeneration blight’.

TOWN="London"
DISTRICT="lambeth" # needs to be lower case
ESTATE="Cressingham"
STREETS="HARDEL+WALK PAPWORTH+WAY"
HEADER="&header=true"
ESTATECSVFILE=$DATADIR/$DISTRICT-$ESTATE.hpi.csv

# Grab the latest house price data & stripout colums of uneeded data
rm $ESTATECSVFILE
echo Getting HPI data for streets on \"$ESTATE\" in "$DISTRICT"
for STREET in $STREETS
do
    URL="http://landregistry.data.gov.uk/app/ppd/ppd_data.csv?\
    district=$DISTRICT&limit=all&street=$STREET&town=$TOWN$HEADER"
    wget $URL -O /tmp/data.csv &> /dev/null
    cut -d, -f2,3,4,9,10,12,13 /tmp/data.csv |\
    awk -F"," 'BEGIN { OFS = "," } {$8="dataset"; print}' \
    >> $ESTATECSVFILE
    echo "     Grabbed: $STREET"
    HEADER=""
done
H=$(head -n1 $ESTATECSVFILE)
echo -e "Generated \"$DISTRICT-$ESTATE.hpi.csv\" file: \n    $H"

Beds - error checking

The Central Government Land Registry does not hold information pertaining to the number of bedrooms within each property. However I had already created a database of all properties in Lambeth which recorded the number of bedrooms so I could merge data-sets. This merger required that I write some code that looped through each line of data provided by

I discovered that some of the property data provided by Lambeth was either incorrect or missing. I came to understand these errors through a mixture of personal knowledge of Cressingham Gardens, and an SQL query. The SQL query was unable to locate data for some properties so it helped me identify individual properties within a list of over 30,000 - without the database searching through the list would have been impossible.

# EXAMPLE SQL QUERY
SELECT DISTINCT address1, street, beds FROM properties  WHERE street='HARDEL WALK' AND address1='1'

# EXAMPLE NULL RESULTS
NOT FOUND: "HARDEL WALK 70"
NOT FOUND: "HARDEL WALK 88"
NOT FOUND: "BODLEY MANOR WAY 35"
NOT FOUND: "BODLEY MANOR WAY 49"
NOT FOUND: "BODLEY MANOR WAY 26"
NOT FOUND: "BODLEY MANOR WAY 6"
NOT FOUND: "CROSBY WALK 11"
NOT FOUND: "CROSBY WALK 19"
NOT FOUND: "CROSBY WALK 45"
NOT FOUND: "UPGROVE MANOR WAY 10"
NOT FOUND: "UPGROVE MANOR WAY 12"
NOT FOUND: "UPGROVE MANOR WAY 2"
NOT FOUND: "UPGROVE MANOR WAY 18"

Some properties were missing from Lambeths housing data-set, and some property numbers were missing. However, I noticed that Lambeths unique property reference id’s incremented by one, so I could deduce the missing house numbers as data had been input at some point following a logical sequence.