Zillow API Excel Example

I recently tested the Zillow API to add estimates of property value (Zestimates) next to an Excel list of addresses.

You can see how using the Zillow API would be extremely valuable. One quick example: Given a mailing list, you could target only homes with an estimated value over $50k.

You should also know that this probably violates the terms of service for using the Zillow API. It would especially seem to violate these terms if you were to resell an existing dataset with a Zesstimate included.

If there is a strong interest in this topic I’ll post the code I wrote. Basically, it was a formula with a web services call that pulled and parsed the data.

Update 12/20/12

Per request, here’s an update.

Zillow API Background

You’ll need Excel and a valid Zillow API key. You’re going to be using formulas to concatenate strings (if you want to do in bulk) and an Excel macro.

Building Query Strings

First, build a list of web query strings to send to Zillow. These look something like:

>http://www.zillow.com/webservice/GetSearchResults.htm?zws-id=[enter_your_api_here]&address=1600+Pennsylvania+Ave&citystatezip=Washington%2C+DC

If you have to build a large list, you’ll want to make the address piece a formula and use the concatenate function.

Getting Results From Zillow API to Excel

For this would use a QueryTables function for each URL.

Sheet2 has the list of URLs from A1 down. You’ll want to change the range (rg1) to process more than 1 at a time. Get the output working first, then increase as needed.

Sheet3 stores the results. The Zillow API sends back a huge amount of data. Example: Message codes, latitude, longitude, neighborhood, etc. We’re only interested in the Zesstimate so will ignore the other columns.

The macro looks like this:

Sub Zillow_API_Macro()

Dim rg1 As Range
 Sheets("Sheet2").Select
 Set rg1 = Range("A1:A1")

Dim rgOutput As Range
 Dim last_row_mean As Integer
 Dim url_value As String

'find the input
 For Each cell In rg1
 'Get value of web address needed
 url_value = cell.Value

'find the last cell to store output in
 Sheets("Sheet3").Select
 last_row_mean = Range("A65536").End(xlUp).Row + 5

'Run the query
 With ActiveSheet.QueryTables.Add(Connection:= _
 "URL;" & url_value, Destination:=Range("$A" & last_row_mean))
 .Name = "101"
 .FieldNames = False
 .RowNumbers = False 'True
 .FillAdjacentFormulas = False
 .PreserveFormatting = False 'True
 .RefreshOnFileOpen = False
 .BackgroundQuery = True
 .RefreshStyle = xlOverwriteCells 'xlInsertEntireRows ' xlInsertDeleteCells
 .SavePassword = False
 .SaveData = True
 .AdjustColumnWidth = True
 .RefreshPeriod = 0
 .WebSelectionType = xlEntirePage 'xlSpecifiedTables
 .WebFormatting = xlWebFormattingNone
 '.WebTables = "1,2"
 .WebPreFormattedTextToColumns = True
 .WebConsecutiveDelimitersAsOne = True
 .WebSingleBlockTextImport = False
 .WebDisableDateRecognition = False
 .WebDisableRedirections = False
 .Refresh BackgroundQuery:=False
 End With

Sheets("Sheet2").Select
 Next cell

End Sub

Conclusion

Hope that helps someone out there. Any questions let me know in the comments. Zillow also reached out and wanted me to remind readers to check out their site, such as the Austin, TX listings.

Enjoy this post? Get New Posts by Email!

4 thoughts on “Zillow API Excel Example

  1. This works great! thank you very much. wondering if there is a method to save the data to database or store xml for parsing to database.

  2. I am trying to run this in excel 2010 and am getting the following error:

    “System Error &H80070057 (-2147024809). The parameter is incorrect”.

    Is there any way someone could take a look at this and figure out what is wrong with it?

Leave a Reply

Your email address will not be published. Required fields are marked *