Source data from 3 websites, populate Excel sheet, create a Google Map of the results

General information for the business: We are a property investment company.
Geographical areas: Bournmouth UK (we are based in London though).
Data to include: Starting from a spreadsheet I will provide the job is to populate missing data in the sheet and to then create PINs on a new Google Map. Missing data includes Type, Floor, Square Meters and 3 URLs (hyperlinks). There are 99 lines in the spreadsheet representing 99 unique properties.
Extra notes: Creation of two artifacts:

1. A master excel spreadsheet showing house price data and supporting attributes (see below).
2. A Google Map showing location of the properties in the excel master spreadsheet.

The start point of the analysis will be a spreadsheet provided by me. This spreadsheet will contain:

1. A list of 99 properties – 1 per line.
2. For each property, the spreadsheet will contain:
a. Property address (including Post Code).
b. Sold Price.
c. Other various pre-populated columns.
d. Blank columns for:
i. Type. Type will be either:
1. Studio.
2. 1-Bed.
3. 2-Bed
4. 3-Bed
5. 4-Bed
6. +4-Bed
7. Other
ii. Floor (this is the floor of the building ie 4th floor, 5th floor etc). Appropriate if the apartment is in a block.
iii. Square Metres (Sqm).
iv. Rightmove URL for the specific property.
v. Zoopla URL for the specific property.
vi. EPC Register URL

Analysis steps.

Populate the blank columns in the Master Excel spreadsheet:

For each of the 99 properties:

1. Using the property post code and address.
2. Lookup the property on the following websites in turn. NOTE you will need to search for SOLD PRICES as the analysis is for sold properties only.
a. www.rightmove.co.uk then
b. www.zoopla.com
3. Populate ALL the blank columns in the master spreadsheet.
a. If you find all the information you need on rightmove.co.uk you still need to go onto zoopla.co.uk and populate the Zoopla URL.
4. If you are unable to source Square Metres from either rightmove.co.uk or zoopla.co.uk lookup the property on www.epcregister.com
5. If you are unable to source all the required excel attributes from the above websites mark the appropriate cell(s) as “Not found” in the master spreadsheet.
a. You need to still populate the URL attributes in the excel sheet if they exist/can be found.

Create Google Map.

Preparation notes:

1. I will provide you with a google account and sign in.
2. I will create and provide you with a Google Map with an example pre-populated.

Main work required:

For each of the 99 properties

1. Using the property postcode drop in a pin in the Map.
2. Save the Pin.
3. Edit the Pin and populate:
a. The title: to contain:
i. Sold Price
ii. Sold Date
iii. Type
iv. Floor
v. Sqm
vi. Example: “£225k 03/17 2-Bed 4th 60sqm”
1. Where an attribute cannot be found use the letters NF instead.
4. Edit the description and populate:
a. Rightmove property URL.
b. Zoopla property URL.
c. EPC register URL.
d. Where any of these URLs are not found populate with:
i. Rightmove NF.
ii. Zoopla NF.
iii. EPC Register NF.
iv. Land Registry URL (this is pre-populated in the template I will give you)
5. Colour code each of the Map Pins according to type as follows:
a. Studio - Green
b. 1-Bed - Blue
c. 2-Bed - Red
d. 3-Bed - Orange
e. 4-Bed - Purple
f. +4-Bed - Yellow
g. Other – White

General Notes:

1. All above referenced websites are free to use.
2. All data is public.
3. I will provide you with an example of a rightmove.co.uk, zoopla.co.uk and epcregister.co.uk URL reference.
4. Note: URLs in the spreadsheet and the Google Map must be functioning hyperlinks.