Excel Web Queries
You frequently need to bring data from a web page into Excel. Your
company may keep product, customer or vendor data on an intranet web
page. If you work with stocks or foreign exchange you may need to
frequently download from the same pages for analysis.
Web Queries in Excel allow you to pull the data directly into Excel
without copying and pasting. If you need to download from the same page
on a periodic basis, your query in Excel will refresh the data
each time you open your workbook/spreadsheet.
You can also build in some flexibility into your web query - instead of
writing a query that downloads the last year of stock prices for MSFT,
you can build your query so that it asks you for a date range and a
ticker symbol. Any graphs you may have built based on the range of data
will dynamically update to reflect the current ticker symbol and data.
Let's try a simple query first. The following stock quotes are from I
Get It! Development's publicly traded stock, IGID. (Not really, just an
example!)
| Date | Volume | Open | High | Low | Close |
| 1/5/2009 11:05:32 PM | 198279 | 34.5 | 48.5 | 28.5 | 48.5 |
| 1/5/2009 11:05:32 PM | 122239 | 36.25 | 45.25 | 24.25 | 45.25 |
| 1/5/2009 11:05:32 PM | 153032 | 44.25 | 52.25 | 34.25 | 51.25 |
| 1/5/2009 11:05:32 PM | 170063 | 46.125 | 60.125 | 39.125 | 58.125 |
| 1/5/2009 11:05:32 PM | 199351 | 45.4375 | 50.4375 | 38.4375 | 49.4375 |
| 1/5/2009 11:05:32 PM | 184963 | 41.4375 | 49.4375 | 35.4375 | 49.4375 |
| 1/5/2009 11:05:32 PM | 165526 | 43.3125 | 54.3125 | 30.3125 | 53.3125 |
| 1/5/2009 11:05:32 PM | 152699 | 45.5 | 55.5 | 40.5 | 53.5 |
| 1/5/2009 11:05:32 PM | 133462 | 57.25 | 62.25 | 43.25 | 62.25 |
| 1/5/2009 11:05:32 PM | 143116 | 34.375 | 39.375 | 25.375 | 39.375 |
You could easily copy and paste this into Excel, but tomorrow you'd
have to do it all over again. If you create a web query, the results
will update themselves, as will any analysis, PivotTables or charts
based on the range of data.
Go into Excel and choose Data, Import External Data, New Web
Query. You'll see a slightly stunted version of Internet Explorer. You can
use the Address bar and the browser to navigate to the page that
contains the data you want to capture. In our case, navigate to this
same page:
http://www.igetit.net/newsletters/Y06_02/webqueries.aspx
and click once on the yellow arrow that points to the table above. The
yellow arrow will turn into a green check mark indicating that only that
table is to be downloaded,
not the whole page! Then click
on the Import at the button at the bottom right corner of the window.
Not done yet... You need to decide a few things before the data goes into
the spreadsheet like, where does it go? You can create a new worksheet,
put it at the top left corner (A1) of the current worksheet or you can navigate
to the bottom of the current worksheet to place new data there.
You also have the option of making
this a 'live' query by clicking on Properties... at the bottom of the dialog
box above.
Change the name at the top to
something sensible in case you ever want to use this in a VBA program or from
another part of your worksheet. Then in the refresh control, you can set the
query to refresh every so many minutes (or hours) or whenever you open the
Excel file.
Under data formatting and layout, decide whether you want new
data coming in to overwrite the data already present or if you want new data
to be inserted. Also, if you analyze the data using formulas, you may want
to click the box that automatically fills in formulas as new rows are added.
That way you don't have to drag down the formulas yourself.
Next issue
we'll look at the text files that are generated by web queries and how to
modify them to make them more powerful.