|
|
! |
|
 |
Web Queries 2 Be selective about the data you retrieve from the Web. In the second part of this mini series, Ian Waugh shows you how to be choosy... In the first part of this series we saw how to access a web page from within Excel and retrieve data from tables displayed there. In this part we see how to select the exact data we want. Again, the exact process will vary according to the web page. However, some pages have an option to download the data in spreadsheet format which is obviously very useful. We'll use a generic example from a mythical site called loadsamoney. After entering the symbol - we'll stick with BT from the previous part - the URL might look something like this: http://uk.finance.loadsamoney.com/q?d=v1&s=BT
It looks quite complicated but worry not for we shall decipher it. The exact format may vary from site to site but here's a typical example: http://uk.finance.loadsamoney.com/d/quotes.csv?s=BT&f=sl1d1t1c1ohgv&e=.csv Deciphering The first part is simply the URL and you can probably tell that it's pointing to an area which contains .csv quotes. Here's what the other bits mean: s= this is the symbol. We're using BT but to access the data for any other company simply substitute the company's symbol. Don't forget to look this up on the site because different sites may use different symbols for the same company. &f= This is the format and the following characters determine which bits of data are retrieved. s= this is the symbol again and can be usually be omitted l1 (that's a lower-case "L") last trade (the close) d1 date t1 time c1 change ohgv open, high, low, volume &e=.csv the extension, in this case .csv If you find a site with a different format or symbols, you should be able to work out what they represent by saving and opening the spreadsheet and comparing it with the display on the web page. Open all hours A quick word of explanation for those not familiar with share quotes. Most people who follow the market do so on a daily or weekly basis and the main price of interest to them is the last price. If a quote for a particular trading period such as a day, a week or an hour, for example, is given, it will often have four parts. The open is the price at which the trading period started. The high and low are the highest and lowest prices it reached during that period and the close, of course, is the last price it traded at. The four parts may be of most interest to day traders but by looking at the high and low for example, you can see how much the price has been fluctuating. Okay, back to the plot. Let's say we just want a company's last price. The URL to use would then be: http://uk.finance.loadsamoney.com/d/quotes.csv?s=BT&f=l1 Notice that we have omitted the s before the l1. If we didn't we'd get the symbol which we don't want. So, following on from that we can retrieve individual pieces of data with the following (we show just the last part here for brevity): s=BT&f=l1 last (close) s=BT&f=d1 date s=BT&f=t1 time s=BT&f=c1 change So, here's the procedure for retrieving these items of data. 1. Select the cell in which you want the data to appear. 2. Select Data>Get External Data>New Web Query. 3. Enter the URL in the box and click OK then click OK again. 4. The program retrieves the data from the Web and puts it in the box. 5. Repeat for other data items and other companies. In the next part we'll see how to refresh the data, edit Web Queries and look at some ways to access the data you're collecting.
|
There's
a link to Download a Spreadsheet. Clicking on it will probably
produce a message offering to save it to disk. Don't do that.
Instead, right-click on it and select Copy Shortcut. Save it to
Notepad or WordPad as you'll need to pinch bits from it from time to
time.