Reply to: Help wanted - extracting data from the web using Excel 2007

[xoggoth]

Down bottom of this page on my site is the current Worldpay GBP-EURO conversion.

Gatekeeper Sales

See page source, 2 elements and a tiny bit of jscript, sidepics function not relevant. Seem to recall I used the hidden and visible elements with jscript copy between due to an appearance problem with frame border, so jscript may be quite unnecessary, just bung value in visible element in 1st place. All it uses otherwise is some fairly self explanatory PHP:

<?php
$data = file_get_contents("https://select.worldpay.com/wcc/info?op=rates&instId=123456",0);
$pos = strpos($data, "rateDateString")-1;
$date = substr($data,1, $pos);
$clen = strlen($data) - strpos($data, "GBP_EUR") - 8;
$data = substr($data,-$clen);
echo "$date<br>1 British Pound = $data Euro";
?>

Only problem might be accessing some external websites from third party hosting, security and all that.

[IR35 Avoider]

I don't want to seem incredibly lazy, but it occurs to me that what I need is a web-based web data extractor, that effectively works like the Excel wizard in that it will take a supplied URL and let you point at something on that page, and generate a web page that just contains that thing. It just needs to improve on Excel by not restricting the selectable items to (most) tables.

In other words, no need to work out the syntax of a query.

Once you have that, excel can pull in the data from the resulting page, using its query wizard.

(Not sure if YQL query builder answers this description, will have to look into that.)

[MarillionFan]

It seems to me to reliably recognise a <table> tag, but I'd be interested in hearing about situations where that hasn't happened.

I use the OPs approach quite a lot and its inconsistent. Not as bad as when you want to refresh a number of them and the bloody internet slows up. I tend to loop through a number of webqueries on multiple pages and the concatenate the data.

[Zippy]

Yes, I noticed that after I posted. However in this context a table is only a table if Excel 2007 web query wizard allows you to select it. I've noticed previously that it doesn't recognise some tables.

It seems to me to reliably recognise a <table> tag, but I'd be interested in hearing about situations where that hasn't happened.

[IR35 Avoider]

Special thanks to NickFitz for recent posts - it will probably take a day or two before I understand everything you say, but I feel you are putting me on the right track.

[IR35 Avoider]

Not wishing to be picky but...

BOE are using tables for their interest rate.

Yes, I noticed that after I posted. However in this context a table is only a table if Excel 2007 web query wizard allows you to select it. I've noticed previously that it doesn't recognise some tables.

[suityou01]

Ah, so the following YQL query will do the needful:

select * from html 
    where url="http://www.bankofengland.co.uk/" 
    and xpath="//table[@id='keyfacts']/tr[1]/td[2]/p/text()"

returning the following XML:

<?xml version="1.0" encoding="UTF-8"?>
<query xmlns:yahoo="http://www.yahooapis.com/v1/base.rng"
    yahoo:count="1" yahoo:created="2011-11-19T20:13:13Z" yahoo:lang="en-US">
    <results>0.5%</results>
</query>

from which the result can easily be extracted using the MSXML DOM, or whatever similar facilities Excel might offer for importing XML over HTTP.

Edit: that YQL URL in full:

http://query.yahooapis.com/v1/public/yql?q=select%20*%20from%20html%20where%20url%3D%22http%3A%2F%2Fwww.bankofengland.co.uk%2F%22%20and%20xpath%3D%22%2F%2Ftable%5B%40id%3D'keyfacts'%5D%2Ftr%5B1%5D%2Ftd%5B2%5D%2Fp%2Ftext()%22

Wow that is art!!! You can query anything these days

I also notice that

select * from html 
    where url="http://www.contractorukcom/forums/" 
    and xpath="//table[@id='poster_type'='Bellend']/tr[1]/td[2]/p/text()"

returns

<?xml version="1.0" encoding="UTF-8"?>
<query xmlns:yahoo="http://www.yahooapis.com/v1/base.rng"
    yahoo:count="1" yahoo:created="2011-11-19T20:13:13Z" yahoo:lang="en-US">
    <results>MarillionFan</results>
</query>

[aussielong]

I've done a bit of this in the past, in Java. This is how I did it and found it quite maintainable..

Use Tagsoup to turn the HTML into well formed XML.

Use XPath expressions to address the parts of the XML document that have the data i'm after.

When the page is updated, your XPaths might need updating - I used an XPath generator plugin in Eclipse so I just reload the page, point and click the bit i'm interested in- and it tells me the XPath I need. I then update my screen scraper with the new XPath.

I've been doing this to scrape some data from a vendor website for a few years now. They have updated the site over time but my stuff still works. This would take an hour or two to knock up and then no more coding to maintain it.

(You just need a thin JNI/COM wrapper to call this from your spready.)

[pacharan]

Scroon screeping?

[NickFitz]

Ah! You've done it. I was just looking at that as well but I am afraid X-Factor beckon.

Now I was trying to see how you could format that or return it in a tabular SQL style format with a view to UNION a few different queries together. I'm not sure YSQL is quite the same as SQL output without reading up on it all?

So the results would be

Source Description Date Value
BankofEngland.co.uk Bank Base Rate 19/11/2011 20:20 0.5%
MotleyFool.co.uk/Lloyds Lloyds Share Price 19/11/2011 20:20 25.5


Pull together some queries like that and you have something very useful.

I think the appropriate approach to aggregating data from multiple sources like that is to create a YQL Open Data Table and add it to their repository; then Yahoo! will do all the heavy lifting of grabbing the data and aggregating it on their backend, and send you just the bits you need.

Your share price query doesn't specify bid or ask: you presumably want something like select Bid, Ask from yahoo.finance.quotes where symbol='lloy.l'

[MarillionFan]

Ah, so the following YQL query will do the needful:

select * from html 
    where url="http://www.bankofengland.co.uk/" 
    and xpath="//table[@id='keyfacts']/tr[1]/td[2]/p/text()"

returning the following XML:

<?xml version="1.0" encoding="UTF-8"?>
<query xmlns:yahoo="http://www.yahooapis.com/v1/base.rng"
    yahoo:count="1" yahoo:created="2011-11-19T20:13:13Z" yahoo:lang="en-US">
    <results>0.5%</results>
</query>

from which the result can easily be extracted using the MSXML DOM, or whatever similar facilities Excel might offer for importing XML over HTTP.

Ah! You've done it. I was just looking at that as well but I am afraid X-Factor beckon.

Now I was trying to see how you could format that or return it in a tabular SQL style format with a view to UNION a few different queries together. I'm not sure YSQL is quite the same as SQL output without reading up on it all?

So the results would be

Source Description Date Value
BankofEngland.co.uk Bank Base Rate 19/11/2011 20:20 0.5%
MotleyFool.co.uk/Lloyds Lloyds Share Price 19/11/2011 20:20 25.5


Pull together some queries like that and you have something very useful.

[NickFitz]

Not wishing to be picky but...

BOE are using tables for their interest rate.

Ah, so the following YQL query will do the needful:

select * from html 
    where url="http://www.bankofengland.co.uk/" 
    and xpath="//table[@id='keyfacts']/tr[1]/td[2]/p/text()"

returning the following XML:

<?xml version="1.0" encoding="UTF-8"?>
<query xmlns:yahoo="http://www.yahooapis.com/v1/base.rng"
    yahoo:count="1" yahoo:created="2011-11-19T20:13:13Z" yahoo:lang="en-US">
    <results>0.5%</results>
</query>

from which the result can easily be extracted using the MSXML DOM, or whatever similar facilities Excel might offer for importing XML over HTTP.

Edit: that YQL URL in full:

http://query.yahooapis.com/v1/public/yql?q=select%20*%20from%20html%20where%20url%3D%22http%3A%2F%2Fwww.bankofengland.co.uk%2F%22%20and%20xpath%3D%22%2F%2Ftable%5B%40id%3D'keyfacts'%5D%2Ftr%5B1%5D%2Ftd%5B2%5D%2Fp%2Ftext()%22

[mudskipper]

Not wishing to be picky but...

BOE are using tables for their interest rate.

				<h1>KEY FACTS</h1>
				<table width="245" border="0" cellspacing="0" cellpadding="0" id="keyfacts">
					<tr>

						<td width="145" valign="top" class="kflbold">Current Bank Rate </td>
						<td width="100" valign="top" class="kfrbold"><img src="/images/kfarrow.gif" width="13" height="13" border="0" />0.5%</td>
					</tr>

Data that should, arguably, be non-tabular.

Not to mention the proliferation of <h1> tags...

[TimberWolf]

For manual methods that involve putting the entire HTML page text into a string and using string functions, is a function that would retrieve only text strings that would be displayed on a web page rather than the entire, rather larger quantity of, HTML background gubbins.

[NickFitz]

WZS

I blame Nick Fitz

OI! I've always insisted that tabular data be presented in tables - you should have seen the beauty that was the 5-day weather forecast on GCap Media's local radio sites

Ooh - you can, at the Wayback Machine

Anyway, getting back to the original question: have a look at YQL, which is a SQL-like way of extracting arbitrary data from the web and returning it in a structured form. I've previously posted an example of its use to extract useful data from CUK (bit of a first, that), and Yahoo! have copious documentation.