Closed Thread
Results 1 to 46 of 46

Zillow xml API extraction

  1. #1
    Registered User
    Join Date
    06-10-2009
    Location
    Sandy Eggo
    MS-Off Ver
    Excel 2010
    Posts
    65

    Zillow xml API extraction

    I'm trying to use the Zillow API to pull information from their site and can't seem to figure out how to extract a specific xml field. In this particular example, the field I'm trying to extract is <zestimate><amount currency="USD">57698</amount>

    Please Login or Register  to view this content.
    I'd like to replace the MsgBox "Found" with the values extracted and then an Else statement that returns an error code. Eventually, I'll loop this through a list but I'm taking it one step at a time.

    Thanks in advance for all the help!
    Last edited by sdgenxr; 01-14-2014 at 07:44 PM.

  2. #2
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,643

    Re: Zillow xml API extraction

    What is it you want to return?

    Is it '57698'?
    If posting code please use code tags, see here.

  3. #3
    Registered User
    Join Date
    06-10-2009
    Location
    Sandy Eggo
    MS-Off Ver
    Excel 2010
    Posts
    65

    Re: Zillow xml API extraction

    Yes, for the below url, the return should be "57698"
    http://www.zillow.com/webservice/Get...ncinnati%2C+OH

  4. #4
    Registered User
    Join Date
    06-10-2009
    Location
    Sandy Eggo
    MS-Off Ver
    Excel 2010
    Posts
    65

    Re: Zillow xml API extraction

    Can anyone recommend a direction or links to a good VBA XML tutorial?

  5. #5
    Registered User
    Join Date
    06-10-2009
    Location
    Sandy Eggo
    MS-Off Ver
    Excel 2010
    Posts
    65

    Re: Zillow xml API extraction

    OK, after some more digging around here's where I'm at now. I know that I'm missing some "Dim variable As IXMLDOMNodeList" or something. Just not sure what or how to do it.

    New code for review and suggestions:
    Please Login or Register  to view this content.

  6. #6
    Registered User
    Join Date
    06-10-2009
    Location
    Sandy Eggo
    MS-Off Ver
    Excel 2010
    Posts
    65

    Re: Zillow xml API extraction

    OK, after many days of working through this, here is a working macro. I'm completely open to critique and further suggestions to make this a better piece of code for anyone to use.
    Please Login or Register  to view this content.

  7. #7
    Registered User
    Join Date
    07-26-2011
    Location
    Miami, FL
    MS-Off Ver
    Excel 2011
    Posts
    7

    Re: Zillow xml API extraction

    Copy the macro that you want to use, then open Excel and pres Alt+F11 to open the VBA Script Editor. Paste the code into the editor and edit the lines that define your data location and where you intend to return the Zillow information and close the script editor. Hopefully they are easy enough for you to figure out. Back in Excel, make a button to call the macro you just created and press it to run.

    Please post any other questions about that particular macro in that thread as not to hijack this one. Hope it works well for your needs.
    Thanks for the clear instructions. I'm still getting this error:

    Compile error: User-defined type not defined

    I am working on a mac and from what I have seen around the web, I won't be able to run Microsoft XML v6.0 (unless you can advise how to).

    If there is a way for me to run this macro on a Google Spreadsheet, that would be even better for me!

  8. #8
    Registered User
    Join Date
    06-10-2009
    Location
    Sandy Eggo
    MS-Off Ver
    Excel 2010
    Posts
    65

    Re: Zillow xml API extraction

    I'm assuming you're trying to enable the MS XML 6.0 from the VBA script editor?

    I can't really comment on running MS XML on a Mac. Try posting a new thread with your specific problem and maybe someone has experience with Excel on Mac can help out? It might have something to do with the fact that VBA uses Internet Explorer to access the requested pages. Just a stab in the dark though.

  9. #9
    Registered User
    Join Date
    07-26-2011
    Location
    Miami, FL
    MS-Off Ver
    Excel 2011
    Posts
    7

    Re: Zillow xml API extraction

    I was able to get it to run on a PC and it is awesome! Works perfectly for what I need. Thanks so much!

    One issue (no biggie) is that though my list currently has about 65 properties, data is only being pulled for the first 57 properties. Does this have to do with the Zillow API or can I change something in the script?

  10. #10
    Registered User
    Join Date
    06-10-2009
    Location
    Sandy Eggo
    MS-Off Ver
    Excel 2010
    Posts
    65

    Re: Zillow xml API extraction

    Glad to hear it was working for you. What errors, if any, are you getting from the properties that it does not pull information for?

  11. #11
    Registered User
    Join Date
    07-26-2011
    Location
    Miami, FL
    MS-Off Ver
    Excel 2011
    Posts
    7

    Re: Zillow xml API extraction

    There is no error being returned. It seems that the script is only counting 58 cells since I see that as it searches it is showing " Retrieving: # of 58".

    The rest of the cells are just left blank.

    UPDATE: I just tried on a different spreadsheet and am having the same issue, count gets stuck at row 58.
    Last edited by yosseld; 02-24-2014 at 03:11 PM.

  12. #12
    Registered User
    Join Date
    06-10-2009
    Location
    Sandy Eggo
    MS-Off Ver
    Excel 2010
    Posts
    65

    Re: Zillow xml API extraction

    Is there a blank row in there somewhere? If not, can you post your spreadsheet and I'll take a look at what's going on.

  13. #13
    Registered User
    Join Date
    07-26-2011
    Location
    Miami, FL
    MS-Off Ver
    Excel 2011
    Posts
    7

    Re: Zillow xml API extraction

    Thank you so much again! This has been soo helpful to me!

    I apologize I didn't respond, but I didn't know you had responded to me.

    There aren't any blank rows but I was able to just sort the properties in a different order and ran the macro as many times as neccessary for my list until all data was pulled.

    I was trying to edit the macro myself to add these data fields to be pulled but was unsuccessful...

    It would be the biggest help to me if I could pull the number of bedrooms, bathrooms, square-footage, year built, last sold date & price etc.

    Is there anyway of doing that?

    Thanks for any help in advance!

  14. #14
    Registered User
    Join Date
    06-10-2009
    Location
    Sandy Eggo
    MS-Off Ver
    Excel 2010
    Posts
    65

    Re: Zillow xml API extraction

    Those fields are part of a different API that I've been slowly working on. It requires using the Get Updated Property Details API call.

    Check out all the different searches you can do with the API. I would be great if you could add to the macro!
    http://www.zillow.com/howto/api/APIOverview.htm

  15. #15
    Registered User
    Join Date
    07-26-2011
    Location
    Miami, FL
    MS-Off Ver
    Excel 2011
    Posts
    7

    Re: Zillow xml API extraction

    I tried playing around with that API but I wish I knew more about VBA and API's because I'm completely stuck. If you figure out a way of pulling that data from the API or can give me some pointers on how to do it, please would you let me know since I have been searching for a working solution but with no luck so far...

  16. #16
    Registered User
    Join Date
    06-10-2009
    Location
    Sandy Eggo
    MS-Off Ver
    Excel 2010
    Posts
    65

    Re: Zillow xml API extraction

    Basically, what you're trying to accomplish with the API is create a url specific to the property and API call you want to make. Looking at the GetUpdatedPropertyDetails API we can see that for each search, the URL must look like:

    http://www.zillow.com/webservice/GetUpdatedPropertyDetails.htm?zws-id=<ZWSID>&zpid=<ZPID>

    Where <ZWSID> & <ZPID> are your variables. The ZWSID is your own API Key that you probably already have. The ZPID can be found using the GetSearchResults API call as completed in the above macro. You just need to store it for use in the property details API call.

    Once you have the URL for the property you're looking for, just extract out the data similar to the below example:
    Please Login or Register  to view this content.
    Hope that helps and doesn't completely confuse you. At some point in the distant future, I may release a macro that will pull all relevant Zillow information for one specific address. But I wouldn't wait on it as it's a low priority for me at this time.

  17. #17
    Registered User
    Join Date
    01-19-2015
    Location
    Phoenix, AZ
    MS-Off Ver
    2013
    Posts
    1

    Re: Zillow xml API extraction

    I'm using the zestimate macro and it saves SO much time. Thank you! Has anyone made any progress on pulling the full property details?? This would be a game changer.

  18. #18
    Registered User
    Join Date
    06-10-2009
    Location
    Sandy Eggo
    MS-Off Ver
    Excel 2010
    Posts
    65

    Re: Zillow xml API extraction

    @bwysel, what API call are you trying to retrieve? I can help guide you to pull the information your looking to pull.

    Here is a list of what information is available: http://www.zillow.com/howto/api/APIOverview.htm
    Last edited by sdgenxr; 02-25-2015 at 02:30 AM.

  19. #19
    Registered User
    Join Date
    02-27-2015
    Location
    NY
    MS-Off Ver
    2013
    Posts
    1

    Re: Zillow xml API extraction

    Does anyone have an update?

    I can't for the life of me get it to work. I need a step by step, please...

  20. #20
    Registered User
    Join Date
    06-10-2009
    Location
    Sandy Eggo
    MS-Off Ver
    Excel 2010
    Posts
    65

    Re: Zillow xml API extraction

    aT1034 - It would help if you can tell us where you are stuck. Do you need help on where to place the macro code, is it not running the way you expect or are you getting errors?

    Maybe this will help for a step by step. http://www.excel-easy.com/vba/create-a-macro.html

  21. #21
    Registered User
    Join Date
    04-28-2015
    Location
    Miami, FL
    MS-Off Ver
    2013
    Posts
    6

    Re: Zillow xml API extraction

    Hello,

    I'm getting "Runtime error '91': Object variable or With block variable not set

    This happens when I get to record 57.

    Any advice?

  22. #22
    Registered User
    Join Date
    06-10-2009
    Location
    Sandy Eggo
    MS-Off Ver
    Excel 2010
    Posts
    65

    Re: Zillow xml API extraction

    I've heard this from someone else before and I'm not exactly sure what the cause of it could be. Have you been able to debug the code and figure out what line is giving you the error? What OS are you using?

  23. #23
    Registered User
    Join Date
    04-28-2015
    Location
    Miami, FL
    MS-Off Ver
    2013
    Posts
    6

    Re: Zillow xml API extraction

    I'm using Windows 7 64 bit, Excel 2013.

    I'm 100% new to VBA. How do I debug?

  24. #24
    Registered User
    Join Date
    06-10-2009
    Location
    Sandy Eggo
    MS-Off Ver
    Excel 2010
    Posts
    65

    Re: Zillow xml API extraction


  25. #25
    Registered User
    Join Date
    04-28-2015
    Location
    Miami, FL
    MS-Off Ver
    2013
    Posts
    6

    Re: Zillow xml API extraction

    Thank you. I will give the link a try.

    I found this from your website. I'm also in real estate. I am pulling active listings from the MLS and putting them into a spreadsheet to calculate an offer based on after repaired value. If I can get this to work I can make 1000 offers a day all over the state.

    Thanks again.

  26. #26
    Registered User
    Join Date
    04-28-2015
    Location
    Miami, FL
    MS-Off Ver
    2013
    Posts
    6

    Re: Zillow xml API extraction

    Is using this API to evaluate multiple properties a violation of the Terms of Use?

  27. #27
    Registered User
    Join Date
    06-10-2009
    Location
    Sandy Eggo
    MS-Off Ver
    Excel 2010
    Posts
    65

    Re: Zillow xml API extraction

    Not that I can tell. What line in their TOS makes you think that?

  28. #28
    Registered User
    Join Date
    04-28-2015
    Location
    Miami, FL
    MS-Off Ver
    2013
    Posts
    6

    Re: Zillow xml API extraction

    "You may not retain any copies of the Zillow Data. Your license to Zillow Data is limited to making direct server calls to the Zillow API for the Zillow Data and distributing the Zillow Data to your end user(s) on your Site(s), immediately upon receipt by your servers."

    Also, did you ever find a resource for using VBA and XML?

  29. #29
    Registered User
    Join Date
    06-10-2009
    Location
    Sandy Eggo
    MS-Off Ver
    Excel 2010
    Posts
    65

    Re: Zillow xml API extraction

    I believe that line refers to the "end user" which is the person utilizing this macro. Also, it refers to "your Site(s)" which this macro does not display any information to a website for third party consumption.

    Regarding using VBA to read through an XML doc, this was one of my resources:
    https://msdn.microsoft.com/en-us/library/aa468547.aspx

  30. #30
    Registered User
    Join Date
    04-28-2015
    Location
    Miami, FL
    MS-Off Ver
    2013
    Posts
    6

    Re: Zillow xml API extraction

    Thanks for the resource. So it looks like we're good. What about storing the data locally?

  31. #31
    Registered User
    Join Date
    06-10-2009
    Location
    Sandy Eggo
    MS-Off Ver
    Excel 2010
    Posts
    65

    Re: Zillow xml API extraction

    I can't speak for others but once I've completed my analysis, the data accumulated from the Zillow API is deleted on my end.

  32. #32
    Registered User
    Join Date
    07-27-2015
    Location
    Dallas, Texas
    MS-Off Ver
    2010
    Posts
    1

    Re: Zillow xml API extraction

    Hello @ sdgenxr

    Im following the steps you provided and cant really get it to work. I followed the tutorial on "http://www.excel-easy.com/vba/create-a-macro.html" and was able to create my first macro. I take it I have to replace the code:
    Please Login or Register  to view this content.
    and replace it with the code you initially provided correct?

  33. #33
    Registered User
    Join Date
    06-10-2009
    Location
    Sandy Eggo
    MS-Off Ver
    Excel 2010
    Posts
    65

    Re: Zillow xml API extraction

    Basically, yes. However, it might be easier to insert the macro into your VB editor then insert a shape, right click on it and assign the Zillow macro you pasted into the VB editor.

  34. #34
    Registered User
    Join Date
    01-28-2013
    Location
    atlanta
    MS-Off Ver
    Excel 2013
    Posts
    10

    Re: Zillow xml API extraction

    It has been some time since this macro was written and I am trying to get it to work. ANy suggestions?

  35. #35
    Registered User
    Join Date
    06-10-2009
    Location
    Sandy Eggo
    MS-Off Ver
    Excel 2010
    Posts
    65

    Re: Zillow xml API extraction

    What kind of problems are you having?

  36. #36
    Registered User
    Join Date
    11-27-2015
    Location
    bay area
    MS-Off Ver
    2016
    Posts
    1

    Re: Zillow xml API extraction

    Thanks very much for posting this sdgenxr!

    I am having issues though plugging this all, could you post a sample xlsm that is self contained and working?

  37. #37
    Forum Guru
    Join Date
    03-02-2006
    Location
    Los Angeles, Ca
    MS-Off Ver
    WinXP/MSO2007;Win10/MSO2016
    Posts
    12,570

    Re: Zillow xml API extraction

    @csmba,
    Unfortunately your post does not comply with Rule 2 of our Forum RULES. Do not post a question in the thread of another member -- start your own thread.

    If you feel an existing thread is particularly relevant to your need, provide a link to the other thread in your new thread.

    Old threads are often only monitored by the original participants. New threads not only open you up to all possible participants again, they typically get faster response, too.
    Ben Van Johnson

  38. #38
    Registered User
    Join Date
    06-10-2009
    Location
    Sandy Eggo
    MS-Off Ver
    Excel 2010
    Posts
    65

    Re: Zillow xml API extraction

    Quote Originally Posted by csmba View Post
    Thanks very much for posting this sdgenxr!

    I am having issues though plugging this all, could you post a sample xlsm that is self contained and working?
    Could you be more specific on the problems you're having? I can guide you to a working solution if you tell me where you're stuck.

  39. #39
    Registered User
    Join Date
    06-10-2009
    Location
    Sandy Eggo
    MS-Off Ver
    Excel 2010
    Posts
    65

    Re: Zillow xml API extraction

    Duplicate post.

  40. #40
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Zillow xml API extraction

    sdgenxr, csmba was asked to start a new thread, so keep an eye open for their new thread. Members are not allowed to post their questions on someone elses thread
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  41. #41
    Registered User
    Join Date
    01-09-2016
    Location
    Williamsport, Pennsylvania
    MS-Off Ver
    2015
    Posts
    1

    Re: Zillow xml API extraction

    I've registered for a ZWSID and input inside of quotations. Try to run the code but debug shows a 'Compile Error: Variable not defined' for the ZWSID as well as all other data above the do not edit line.

    Is this an XML v6.0 issue? I have set it as a reference.

    Thanks for the help!

  42. #42
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Zillow xml API extraction

    bhighs2 welcome to the forum

    Unfortunately your post does not comply with Rule 2 of our Forum RULES. Do not post a question in the thread of another member -- start your own thread.

    If you feel an existing thread is particularly relevant to your need, provide a link to the other thread in your new thread.

    Old threads are often only monitored by the original participants. New threads not only open you up to all possible participants again, they typically get faster response, too.

  43. #43
    Registered User
    Join Date
    03-29-2016
    Location
    Seattle
    MS-Off Ver
    2013
    Posts
    1

    Re: Zillow xml API extraction

    The Macro with not run through all properties if one of the property does not have zestimate or rent zestimate on the website.
    Error: "Run-time error '91': Object variable or With Block variable not set"

    Any suggestion on solving the error?


    Regards,
    Jason

  44. #44
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Zillow xml API extraction

    wangjas welcome to the forum

    Perhaps you missed post #42?

    Unfortunately your post does not comply with Rule 2 of our Forum RULES. Do not post a question in the thread of another member -- start your own thread.

    If you feel an existing thread is particularly relevant to your need, provide a link to the other thread in your new thread.

    Old threads are often only monitored by the original participants. New threads not only open you up to all possible participants again, they typically get faster response, too.

  45. #45
    Registered User
    Join Date
    05-10-2016
    Location
    United States
    MS-Off Ver
    2013
    Posts
    12

    Re: Zillow xml API extraction

    Hey guys,

    I'm not sure if this is still an active thread, but I am starting from scratch on a project extremely relevant to this topic in regards to API, data extraction, as well as Zillow.

    What I have:

    zpid
    Zillow Web Service ID

    My problem is is that I literally have NO idea on how to set my Excel spreadsheet up. What do I put in the workbook? What other information needs to be provided?

  46. #46
    Forum Guru
    Join Date
    03-02-2006
    Location
    Los Angeles, Ca
    MS-Off Ver
    WinXP/MSO2007;Win10/MSO2016
    Posts
    12,570

    Re: Zillow xml API extraction

    cmanlongat,
    Unfortunately your post does not comply with Rule 2 of our Forum RULES. Do not post a question in the thread of another member -- start your own thread.

    If you feel an existing thread is particularly relevant to your need, provide a link to the other thread in your new thread.

    Old threads are often only monitored by the original participants. New threads not only open you up to all possible participants again, they typically get faster response, too.

Closed Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Pull zestimate from zillow with address and zip code
    By tiderider03 in forum Excel Programming / VBA / Macros
    Replies: 62
    Last Post: 09-26-2020, 03:56 PM
  2. VBA script to pull "ZEstimates" from Zillow.com
    By Veradusit in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 11-11-2019, 12:50 AM
  3. Need to pull data from zillow into excel file.
    By jtaylor13 in forum Excel General
    Replies: 11
    Last Post: 11-06-2012, 08:34 PM
  4. Name Extraction
    By ernestgoh in forum Excel General
    Replies: 1
    Last Post: 03-25-2009, 01:26 PM
  5. extraction
    By rowena in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 10-09-2006, 11:28 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1