Mining and Visualizing Municipal Real Estate Data with Wizard.app

While chatting with a local real estate agent here in Milford, Connecticut, he mentioned that in his experience, Colonial-style houses usually command the highest price (the mix here varies by neighborhood, but is fairly typical of suburban New England — dominated by Colonials, Cape Cods, Ranches, and Raised Ranches.)

Sites like Zillow or Realtor.com often provide useful data points like that, but rarely at the micro level of a single (non-metropolitan) city or town, where it’s perhaps most useful to a buyer, seller, or investor.

That agent’s comment was fresh on my mind as I was looking up some tax records on the city’s website, and came across their Sales Search portal, and when I noticed that they include house style, I wondered if I could actually validate the agent’s hypothesis.

The particular analysis I wanted to do could be done easily enough using a Pivot Table in Excel, but several years back I discovered an absolutely fantastic data analysis app called Wizard that not only makes Pivot Tables much friendlier to work with than Excel, but also includes loads of useful visualization tools that can really bring data alive. Wizard is perfect for this sort of quick-and-dirty data mining and visualization. The images below show two graphs of my final results, and the rest of this post shows how I got there (it took about 30 minutes from start to finish):

The city’s sales records are easy enough to search (if you enjoy early-2000s-era web forms) but there’s no easy export tool, and you can only view about 20 per page:

(It’s great that many cities and counties provide online access to this kind of data, but I do wish they would try and find vendors that had some notion of UX (user experience) and included requirements with an eye toward data portability.)

But I was intrigued enough to plod forward. I did a search covering all price ranges and styles (but only single-family) going back three years, and then slowly paged through the results, copying and pasting each set of results into a spreadsheet. I used the Mac spreadsheet app Numbers, but Excel or Google Sheets would work just as well:

Once I got all the results into a spreadsheet, I exported it out as a simple CSV file (making sure to include a header row with the field names).

Next I fired up Wizard and imported the raw data:

The really useful bits with Wizard are behind those buttons at the top, labeled “Pivot” and “Summary”. Pivot brings up a powerful but user-friendly interface for constructing pivot tables, and with a few clicks I had the raw data I was looking for:

And you can see that among the most common styles, Colonials do indeed have the highest median sale price (the overall highest was for the “Modern/Contemp” style, but there were only 8 of those sold during the period I searched, and those skew high-end in Milford, including several $1M+ homes).

While the raw numbers are interesting, the Summary tab in Wizard lets you construct visualizations that really tell a story. In addition to the “bubble” cluster chart, you can also see here a distribution by sale price, showing how in Milford sales have clustered around $330K, with a sprinkling of outliers at the very high and low ends:

You can export the charts in a variety of formats, and you can also easily export the pivot tables as well, for further analysis back in Excel or Numbers. For example, I took the raw pivot table back out and added a column to show price per square foot:

Looked at that way, you can see that Colonials actually have a significantly lower median price per square foot than other styles, but make up for that by being, on average, quite a bit larger.

There’s loads of different ways to slice and dice the data, and those of you with some background in statistics will find some of the advanced tools in Wizard even more compelling. Wizard costs $79.99 to buy (and is Mac-only), but is worth every penny in the hours it can save you if you do a lot of pivot tables or analyze large tables of data. There is also a free trial available.