VBA Get Real Estate Valuation

Excel Macro that will pull the current real estate value for a address using the Zillow API.  The Zillow API returns an XML response with the real estate details for the property.  The macro extracts the necessary values from the XML.

VBA Get Real Estate Valuation (145 downloads)

 

 

Sub zestimate()
'
' Zestimate Macro
'
Dim address, zipcode As String
Dim idURL, zesURL As String

address = Cells(5, 3).Value
address = Replace(address, " ", "+")
address = Replace(address, "#", "")
zipcode = Cells(5, 4).Value

idURL = "http://www.zillow.com/webservice/GetSearchResults.htm?zws-id=X1-ZWz1g14y0hebyj_6h6e1&address=" & address & "&citystatezip=" & zipcode

Dim IE As SHDocVw.InternetExplorer
Set IE = CreateObject("InternetExplorer.Application")

While IE.busy
 DoEvents
 Wend

IE.Visible = True
IE.Navigate idURL

While IE.busy
 DoEvents
 Wend
 
Set doc = IE.Document

'Get XML Node
 Set objHTTP = CreateObject("MSXML2.ServerXMLHTTP")
 Url = idURL
 objHTTP.Open "GET", Url, False
 objHTTP.send ("")
 sourceHTMLText = objHTTP.responseText
 zpid = objHTTP.responseXML.getElementsByTagName("zpid")(0).FirstChild.NodeValue

valuationURL = "http://www.zillow.com/webservice/GetDeepComps.htm?zws-id=X1-ZWz1g14y0hebyj_6h6e1&zpid=" & zpid & "&count=5"

IE.Navigate idURL

While IE.busy
 DoEvents
 Wend

'Get XML Node
 Set objHTTP = CreateObject("MSXML2.ServerXMLHTTP")
 Url = valuationURL
 objHTTP.Open "GET", Url, False
 objHTTP.send ("")
 sourceHTMLText = objHTTP.responseText
 'MsgBox sourceHTMLText
 Dim zestimate As String
 Range("G5").Value = "$" & objHTTP.responseXML.getElementsByTagName("amount")(0).FirstChild.NodeValue
Range("I5").Value = objHTTP.responseXML.getElementsByTagName("yearBuilt")(0).FirstChild.NodeValue
Range("J5").Value = objHTTP.responseXML.getElementsByTagName("bedrooms")(0).FirstChild.NodeValue
Range("K5").Value = objHTTP.responseXML.getElementsByTagName("bathrooms")(0).FirstChild.NodeValue

Range("L5").Value = objHTTP.responseXML.getElementsByTagName("yearBuilt")(0).FirstChild.NodeValue
Range("M5").Value = objHTTP.responseXML.getElementsByTagName("lastSoldDate")(0).FirstChild.NodeValue
low = objHTTP.responseXML.getElementsByTagName("low")(0).FirstChild.NodeValue
high = objHTTP.responseXML.getElementsByTagName("high")(0).FirstChild.NodeValue
Range("H5").Value = "$" & high & "-" & low
End Sub