Help wanted - extracting data from the web using Excel 2007

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

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

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

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

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

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