Read from source of a Website to Excel

  • Thread starter Thread starter sermest
  • Start date Start date
S

sermest

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...ezip=cinnaminson,+nj&GOButton=<SPAN>GO</SPAN>

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..
 
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.
 
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.



- Show quoted text -
Click to expand...

That is great information.
I will try and let you know.

Thank you very much.
Mike
 
this is very cool. thanks for writing these add-ins. i have been doing this stuff by hand, but your add-ins are slick.

one question. I can pull the data from a cell. if a cell has a hyperlink associated with it, is it possible to pull out that data.

thanks,
 
Back
Top