Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Read from source of a Website to Excel
I have searched in this group about reading from Web source but codes
i find did not work for me. Sample web page I am trying to get data is below: http://www.zillow.com/search/Search.... 3C%2FSPAN%3E In the web page you can not see the data I am looking for but if you right click and view the source. Then you can search and find "pinnedProperty" and under that you find informations about the house like bedroom, sqft, bathroom etc. I need those information. Is there anyway I can read that information to excel sheet for a given address. Thank you very much.. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Read from source of a Website to Excel
I have a free open-source add-in that I think might work for you. It
provides a number of user-defined functions that allow you to extract data off of most web pages. For example, if I use this function invocation from the add-in: =RCHGetTableCell("http://www.zillow.com/HomeDetails.htm? zprop=38082416",1,"Lot size:") ....I get back this result in the worksheet cell: 7,501 sq ft / 0.17 acres I wrote the add-in primarily to access stock market data, but have used it on other web pages. The add-in and documentation on its functions can be found in the Files area of this Yahoo group: http://finance.groups.yahoo.com/group/smf_addin/ If you prefer not to use an add-in, I do have a "stand alone" version of that add-in function. See this file in the Yahoo group: Files SMFLite smfGetTableCell Templates smfGetTableCell-Template- MSN-Historical-Data.xls You can just delete everything in the current worksheet, then use this formula instead of the previous one (only difference is the "smf" prefix instead of "RCH"): =smfGetTableCell("http://www.zillow.com/HomeDetails.htm? zprop=38082416",1,"Lot size:") Basically, here's how the function works with those parameters: -- Get the source code of web page "http://www.zillow.com/ HomeDetails.htm?zprop=38082416" -- Look for a string of "Lot size:" within that source code -- Return the data in the table cell following (i.e. the "1") that string The function has other parameters -- more than one search string can be used to position yourself on the page, and rows or cells can be skipped (backwards or forwards) to get to the desired table cell. Alternatively, you could grab the whole table with this 28-row by 2- column array-entered function of the add-in: =RCHGetHTMLTable("http://www.zillow.com/HomeDetails.htm? zprop=38082416","Lot size:",-1," ",1) ....which grabs the whole table in one shot. I usually prefer the GetTableCell function, though, since it doesn't need to be array- entered and gives me more control over what I am having returned. You could also use an EXCEL web query to grab that table. However, I wrote the add-in because of various issues I have with web queries. But it may be a better way for you to go? Only you can answer that. On Jun 19, 11:26 am, sermest wrote: I have searched in this group about reading from Web source but codes i find did not work for me. Sample web page I am trying to get data is below: http://www.zillow.com/search/Search....14+pennsauken+... In the web page you can not see the data I am looking for but if you right click and view the source. Then you can search and find "pinnedProperty" and under that you find informations about the house like bedroom, sqft, bathroom etc. I need those information. Is there anyway I can read that information to excel sheet for a given address. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Read from source of a Website to Excel
On Jun 19, 7:13 pm, Randy Harmelink wrote:
I have a free open-source add-in that I think might work for you. It provides a number of user-defined functions that allow you to extract data off of most web pages. For example, if I use this function invocation from the add-in: =RCHGetTableCell("http://www.zillow.com/HomeDetails.htm? zprop=38082416",1,"Lot size:") ...I get back this result in the worksheet cell: 7,501 sq ft / 0.17 acres I wrote the add-in primarily to access stock market data, but have used it on other web pages. The add-in and documentation on its functions can be found in the Files area of this Yahoo group: http://finance.groups.yahoo.com/group/smf_addin/ If you prefer not to use an add-in, I do have a "stand alone" version of that add-in function. See this file in the Yahoo group: Files SMFLite smfGetTableCell Templates smfGetTableCell-Template- MSN-Historical-Data.xls You can just delete everything in the current worksheet, then use this formula instead of the previous one (only difference is the "smf" prefix instead of "RCH"): =smfGetTableCell("http://www.zillow.com/HomeDetails.htm? zprop=38082416",1,"Lot size:") Basically, here's how the function works with those parameters: -- Get the source code of web page "http://www.zillow.com/ HomeDetails.htm?zprop=38082416" -- Look for a string of "Lot size:" within that source code -- Return the data in the table cell following (i.e. the "1") that string The function has other parameters -- more than one search string can be used to position yourself on the page, and rows or cells can be skipped (backwards or forwards) to get to the desired table cell. Alternatively, you could grab the whole table with this 28-row by 2- column array-entered function of the add-in: =RCHGetHTMLTable("http://www.zillow.com/HomeDetails.htm? zprop=38082416","Lot size:",-1," ",1) ...which grabs the whole table in one shot. I usually prefer the GetTableCell function, though, since it doesn't need to be array- entered and gives me more control over what I am having returned. You could also use an EXCEL web query to grab that table. However, I wrote the add-in because of various issues I have with web queries. But it may be a better way for you to go? Only you can answer that. On Jun 19, 11:26 am, sermest wrote: I have searched in this group about reading from Web source but codes i find did not work for me. Sample web page I am trying to get data is below: http://www.zillow.com/search/Search....14+pennsauken+... In the web page you can not see the data I am looking for but if you right click and view the source. Then you can search and find "pinnedProperty" and under that you find informations about the house like bedroom, sqft, bathroom etc. I need those information. Is there anyway I can read that information to excel sheet for a given address.- Hide quoted text - - Show quoted text - That is great information. I will try and let you know. Thank you very much. Mike |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Why won't vlookup read newly entered data from a source file? | Excel Discussion (Misc queries) | |||
Read Content From A Website | Excel Programming | |||
Read VBA source code as input | Excel Programming | |||
Query starts a "read only" copy of the source sheet | Excel Programming | |||
Excel doc on a website | Excel Programming |