OBJECTIVES: The objective of this practical exercise is to practice the preparation and integration of open data available on-line ready for analysis. The exercise brings together individual transactions data and aggregated 'base population' data.

TASK 1: Download the latest data on property transaction prices in England & Wales from here: https://www.gov.uk/government/statistical-data-sets/price-paid-datadownloads where it says "current month as CSV file". This is a large file. Open in Excel and inspect - this is typical transactions data.

TASK 2: By inspecting the fields, column M District is complet and a likely candidate for a join to other data. Various look-up tables can be found at this portal: https://geoportal.statistics.gov.uk. You now collect the dwelling stock data fromhttps://www.ons.gov.uk/

TASK 3: Prepare census dwelling data so the dwelling types match the Land Registry data.

TASK 4: Return to the property price data in Excel. Sort by column C (Date). Delete transactions outside the latest complete month of transactions. Check the last column (Record status) that all records are 'A' - remove any 'C' or 'D'. You should now pivot on District to get the count of transactions by property type for entries type "D", "F", "S" and "T" only (ignore "O"). Copy and Paste Special (values only) from the pivot table to a new worksheet. Cut and paste this table into the look-up, check the match by district name and manually adjust for any mismatches due to no sales in a district. Use pivot tables to make table that sums the four property types by region (region_name). Save the file.

TASK 5: Bring the two tables of dwellings by region and property sales by region into a single table. Calculate the percentage of each property type sold by region and nationally for England and Wales. Highlight maxima.