Zillow API for Excel

Zillow data in Excel with Power Query

Turn a column of Zillow links or addresses into price, status, Zestimate and rent Zestimate columns in Excel, using Power Query and one small custom function.

Base URL https://api.propertiesapi.com/v1 · auth header x-api-key. PropertiesAPI is an independent service that returns Zillow property and listing data as JSON, provided as-is.

Get a free API key Read the docs

How Excel fits

Excel's WEBSERVICE function can't send a request header, and the API key travels in a header. Power Query (Data > Get Data) can, which makes it the right tool: you write one function, point it at a table of addresses, and refresh whenever you want current listing data.

Each refresh runs a lookup per row. Repeat lookups of the same home within 24 hours come from our cache and are free. Rows that fail come back with their error code in a column instead of breaking the whole query, so one bad address doesn't stop the refresh.

A live lookup takes typically 8 seconds, up to about 20 seconds, so a long list takes a while to refresh. The function asks Power Query to wait long enough for each row.

Put your inputs in a table

Make a table (Insert > Table) named Homes with one column, Input, holding a Zillow link or a full address per row.

Excel table: Homes

Input
https://www.zillow.com/homedetails/2640-Steiner-St-San-Francisco-CA-94115/15073305_zpid/
2640 Steiner St, San Francisco, CA 94115
742 Evergreen Ter, Springfield, IL 62704

Create the lookup function

Data > Get Data > From Other Sources > Blank Query, open the Advanced Editor, paste this, and name the query ZillowLookup. Replace YOUR_KEY with your key.

Power Query (M)

(input as text) as record =>
let
    isUrl = Text.StartsWith(Text.Lower(input), "http"),
    query = if isUrl then [url = input] else [address = input],
    response = Web.Contents("https://api.propertiesapi.com/v1", [
        RelativePath = "zillow/property",
        Query = query,
        Headers = [#"x-api-key" = "YOUR_KEY"],
        Timeout = #duration(0, 0, 2, 0),
        ManualStatusHandling = {400, 401, 402, 404, 409, 422, 429, 502, 503, 504}
    ]),
    status = Value.Metadata(response)[Response.Status],
    body = Json.Document(response),
    result = if status = 200
        then [ok = true, error = null] & Record.SelectFields(body[data],
                {"address", "homeStatus", "price", "zestimate", "rentZestimate", "beds", "baths", "livingArea", "detailUrl"})
        else [ok = false, error = body[error][code]]
in
    result

Apply it to every row

Load the Homes table into Power Query (Data > From Table/Range), add a column that calls the function, then expand the record into columns and load the result to a new sheet.

Power Query (M)

let
    Source = Excel.CurrentWorkbook(){[Name = "Homes"]}[Content],
    Looked = Table.AddColumn(Source, "Result", each ZillowLookup([Input])),
    Expanded = Table.ExpandRecordColumn(Looked, "Result",
        {"ok", "error", "address", "homeStatus", "price", "zestimate", "rentZestimate", "beds", "baths", "livingArea", "detailUrl"})
in
    Expanded

Refresh, and batch when the list is long

Data > Refresh All reruns the lookups. For long lists, one batch request of up to 50 items is fewer round trips: this variant posts a JSON body and returns one row per item.

Power Query (M)

let
    Inputs = List.FirstN(Excel.CurrentWorkbook(){[Name = "Homes"]}[Content][Input], 50),
    response = Web.Contents("https://api.propertiesapi.com/v1", [
        RelativePath = "zillow/batch",
        Headers = [#"x-api-key" = "YOUR_KEY", #"content-type" = "application/json"],
        Content = Json.FromValue([items = Inputs]),
        Timeout = #duration(0, 0, 6, 0)
    ]),
    results = Json.Document(response)[data],
    rows = Table.FromRecords(List.Transform(results, each
        if [ok] then [index = [index], ok = true, address = [data][address], price = [data][price], zestimate = [data][zestimate]]
        else [index = [index], ok = false, address = null, price = null, zestimate = null]))
in
    rows

Excel gotchas

When Power Query asks how to connect to the URL, choose Anonymous: the key is already in the request header.
The key is stored inside the workbook's query. Don't share the workbook with people who shouldn't use your key, or keep the key in a parameter you clear before sharing.
Empty cells in price or zestimate mean Zillow doesn't show the value for that home. Don't fill them with zero before you average a column.
These steps are written for Excel for Windows. Power Query on Mac and on the web has fewer options, so build the query on Windows if you can.

Keep going

Zillow API and Excel: questions

Can I use a formula like =ZILLOW() in Excel?+

Not in Excel today. The formula add-on is for Google Sheets. In Excel, the Power Query function above gives you the same columns and refreshes on demand.

Why not use WEBSERVICE?+

WEBSERVICE can't send headers, and the API needs the x-api-key header. Power Query can.

Does every refresh cost credits?+

Zillow links and addresses cost the same: 1 credit per successful lookup. Repeat lookups of the same home within 24 hours come from our cache and are free. Failed rows cost nothing.

How many free lookups do I get?+

50 free lookups, once, with no card. That is enough to build and test the workbook.

Make your first call from Excel

50 free lookups, no card, no approval.

Get a free API key Read the docs

Zillow is a trademark of Zillow, Inc. PropertiesAPI is an independent service and is not affiliated with, endorsed by or sponsored by Zillow.