An important announcement from the founder of property-bee.com: The future of Property Bee is assured.

Exporting data from Property Bee into a spreadsheet

Anything and everything to do with property prices, from news reports and press releases to your comment and theories.

Exporting data from Property Bee into a spreadsheet

Postby Beerhunter on Fri Mar 04, 2011 1:04 am

I'm trying to simplify the format used when export data from the toolbar (using Wizards > Export Data to CSV file) as the current format is horrendous to use (its just a straight dump of the database with multiple rows for each property and sparsly filled with data)

Thoughts about the new format, 1 row per property listing with columns for;
* the latest agent details (name, address, location, tel no), descriptions, status, subtitle and title - as I don't think people doing stats are interested in how these have changed
* the latest price
* the date/price the property was put on the market
* the date/price the property was sold
* the date/price the property was withdrawn
* the number of false starts (gone from sold back to available)
* the number of times the price has changed
* the latitude/longitude (if known)
* the postcode (if known)

(and of course like at the moment you will be able to select which of the above columns are exported)

pb_export.csv
(63.41 KiB) Downloaded 396 times

Attached is a preliminary example of the format, any comments or thoughts on how to improve it would be appreciated - after all its for you to use so I might as well make it useable!

My concerns on the format;
* The price fields are the complete text - so difficult to process in a spreadsheet, so I guess "£100,000" would be better than "Guide Price £100,000"
* Would be useful to indicate whether the property is for sale or rent

Some ideas;
* Would "Sold Date" / "Withdrawn Date" be better as number of days since the property was listed?
* Does it need percentage drops for sold / withdrawn prices? - tho they are easy to add to a spreadsheet.
* Would a last viewed date be of any use?
User avatar
Beerhunter
Site Admin
 
Posts: 1788
Joined: Tue Jan 22, 2008 12:05 am

Re: Exporting data from Property Bee into a spreadsheet

Postby Graaam101 on Tue Mar 08, 2011 11:05 am

Hi BH

The data is most useful if it is in the following format:

Prop ref First seen address postcode (i dont have this yet in my version) agent no. beds prop type (date1 price1) (date2 price2)

etc. up until 10/12 date, price columns

Then 4/5 columns of (date1.1 status 1.1)

Let me give you an example to make it more clear (real property for prices&dates but with actual property hidden) (rows are columns just for clarification...i.e. whole prop history on 1 row only)

Prop ref RMxxxxx
Address 99 A STREET,SOME PLACE
postcode RR9 9RR
agent A.N.OTHER
date1 27/01/2010
price1 89950
date2 30/04/2010
price2 82000
date3 13/09/2010
price 81950
date4 08/02/2011
price4 74950
beds 2
type terrace
dates1 12/11/2010
status1 sold
dates2 20/01/2011
status2 available

From the above data I can tell you

57.9 weeks on market
average reduction amount £5000
Total reduction £15000
Price reductions in last 10 weeks 1
Sales fallen through 1
price reduction since marketing 16.7%
no. beds 2
property type terraced
etc.

I have converted the raw dumped data to that format using simple VBA.

I broke the type column into 2 (probably very badly using several poorly executed lines as I cant programme for toffee) but having the no. beds and property type seperate is MUCH more useful when comparing whole market data or individual market data.


Pretty much the data you have now but with that format it is much easier when you want to get data for an agent, agent for a property etc. without going online.

There are a couple of extra columns I have ommitted and there are annoyances with the fact that you need about 50 columns per property to ensure that there is sufficient space to fit it all in (11 price reductions for example takes a lot of space) but overall the stats are much easire to manipulate and study in this format.

Dont know if you read my other post, and as you can see I only post when i deem it necessary.

Did you find a new backer for the site?


Regards


Graaam101
Graaam101
 
Posts: 6
Joined: Wed Jun 02, 2010 12:05 pm

Re: Exporting data from Property Bee into a spreadsheet

Postby Graaam101 on Tue Mar 08, 2011 11:20 am

Sorry I didnt address some of your questions:


* the number of false starts (gone from sold back to available)

AKA a 'fall through' easily calculated using the format I suggested by a simple 'if Status1=sold&status2=available then sales fallen through = 1 (and yes I know thats utter B**LO*KS but you get the idea)

* the number of times the price has changed

Yet again that is a simple count statement for each of the price change columns if in the format i suggested.

* the latitude/longitude (if known)

very useful

* the postcode (if known)
Even more useful

My concerns on the format;
* The price fields are the complete text - so difficult to process in a spreadsheet, so I guess "£100,000" would be better than "Guide Price £100,000"


Take it out, offers over, guide price etc. are pretty useless for the stats

* Would be useful to indicate whether the property is for sale or rent

Definately, even more useful would be to have 2 seperate databases one for rentals one for sales as most people will only be searching for one at any given time.


Some ideas;
* Would "Sold Date" / "Withdrawn Date" be better as number of days since the property was listed?


If you use the format I suggested sold date and withdrawn date as a number of days is a simple 'date1-date2' calculation so I think it uneccessary

* Does it need percentage drops for sold / withdrawn prices? - tho they are easy to add to a spreadsheet.


No I dont think so, any1 exporting CSV data is probably going to know how to calculate this if they need it.

* Would a last viewed date be of any use?

Yes as if you are using the data and a property hasnt been viewed for say 1 month, then the data will almost definately be out-dated and therefore skew any results that you have
Graaam101
 
Posts: 6
Joined: Wed Jun 02, 2010 12:05 pm

Re: Exporting data from Property Bee into a spreadsheet

Postby luvadealme on Wed Mar 09, 2011 10:47 am

Hi Guys,

Where did you get the 'Sold date' form or is this based on when the property is withdrawn?

I had not seen that an export of the data was available. How many rows of data would it be if you wnated a raw data dump of all data ever collected?

thanks,

J
luvadealme
 
Posts: 2
Joined: Thu Apr 22, 2010 5:26 pm

Re: Exporting data from Property Bee into a spreadsheet

Postby luvadealme on Wed Mar 09, 2011 11:00 am

sorry to sound really dumb, but I can't work out how to export data in the first place, beerhunter can you point me in the right direction?
luvadealme
 
Posts: 2
Joined: Thu Apr 22, 2010 5:26 pm

Re: Exporting data from Property Bee into a spreadsheet

Postby Beerhunter on Wed Mar 16, 2011 11:03 am

luvadealme wrote:Hi Guys,

Where did you get the 'Sold date' form or is this based on when the property is withdrawn?

I had not seen that an export of the data was available. How many rows of data would it be if you wnated a raw data dump of all data ever collected?

thanks,

J


Its based on when the property has gone to Sold STC (or any of the other various like, Sold / SSTC etc)

The export is accessed from the toolbar; Wizards -> Export Data to CSV file

It exports all the property information stored locally on your PC - ie all the properties you have looked at with property-bee.
User avatar
Beerhunter
Site Admin
 
Posts: 1788
Joined: Tue Jan 22, 2008 12:05 am

Re: Exporting data from Property Bee into a spreadsheet

Postby rlph on Wed Mar 16, 2011 4:31 pm

Nice one Beerhunter - I've gotten used to doing dumps with sqlite directly from the Property Bee database file. Good to see there will be an easier-to-use alternative for the less demented^H^H^H^H^H^H^H^Htechnically inclined.

My extract has the following fields:

  • several fields from your extract: ID, title/subtitle, agent name, current price, current status, postcode, latitude/longitude
  • date of the most recent sample (i.e. when the property was last seen)
  • date of last status change
  • date of last price change

I'm much more interested in current rather than historical information - I use my extract to feed a spreadsheet that tracks my search for my next home. So the more esoteric historical fields weren't of much use to me, other than to see how recently a particular property had seen any movement.

It would be nice if one of your fields were the price with all the non-numeric characters stripped out - this was a bear to do in my spreadsheet formulas, especially for PrimeLocation. Would make life easier for less-wizardly spreadsheet users. Same with the property URL - I once had it as part of my extract, but ended up using a formula to derive it from the property ID instead - less cr*p like session ID's in the URL that way.

Overall I'd say err on the side of having too many fields rather than too few. :-D
rlph
 
Posts: 6
Joined: Mon Dec 13, 2010 6:05 pm


Return to Property Prices

Who is online

Users browsing this forum: No registered users and 1 guest

cron