It's an accepted fact that most Microsoft Office users only ever use about 20 percent of the suite's capabilities, often because they don't have the time to learn about all of the goodies it contains. Excel, with its complicated interface and feature overload is notorious for baffling even experienced users. However, many of its features are very useful in their own right.
Querying Excel
David Hellaby shows you how to tailor your spreadsheets with Excel's
little-known Web importer.
It's an accepted fact that most Microsoft Office users only ever use about 20 percent of the suite's capabilities, often because they don't have the time to learn about all of the goodies it contains. Excel, with its complicated interface and feature overload is notorious for baffling even experienced users. However, many of its features are very useful in their own right.
One of these features includes a built-in function to import data from selected sources. The Web Query tool, found in the past few versions of Excel, allows you to import data into a worksheet directly from the Internet or a corporate intranet. While XML and Web Services (coming in Office 2003) are likely to be the killer apps for importing and using data from external sources, Excel's Web Query is a functional, albeit rudimentary system.
The beauty of it is that you don't have to know how to write scripts or interpret HTML tags with the entire procedure requiring a few simple steps. Once imported the data can be dynamically updated so it's ideal for tracking share portfolios, fuel prices, interest rates and a host of other data that can make life a lot easier.
The disadvantage of using Web Queries is that a lot of data cannot be imported. For example, Excel will not import data from security enhanced sites such as bank Websites that don't use Windows NT security, so you cannot import your bank statement and dynamically update it. Nor will it import graphics or data from sites where scripts are used to generate or populate tables with data, even though it will display such data in the Web Query dialog box.
However, it can be used for importing a range of single and multiple tables as well as formatted or plain text, and once the data is imported it can be analysed using Excel's basic features. For that reason it's best suited for corporate intranet use, but can also be used to monitor a wide range of data at home.
Microsoft offers a free add-on for Excel 2002 that provides refreshable stock quote data in a worksheet cell. The MSNStockQuote function allows you to calculate formulas based on the function's return values so you can create your own financial analysis. Once downloaded and installed from http:// office.microsoft.com/downloads/2002/Msnsq.aspx it can be configured to return a company's stock price or other stock information, such as day's high, day's low, ask, bid, close price, and so on.
Alternatively you can create your own refreshable portfolio monitor in a single worksheet using imported data from multiple sites. For example, we created a worksheet that monitored multiple share indices, individual share prices and currency fluctuations, the cheapest place to find petrol, news headlines and the local weather. In other words, we created our own newspaper front page with specific, tailored information.
Find the source
There are three main methods of importing Web Query data into Excel: using the Import Data command; cutting and pasting using Smart Tags (in Office XP) or; exporting from Internet Explorer. We used all three as each has advantages in certain situations, but you do need IE 5.0 or higher.
You can also use the Edit command in Internet Explorer; however, it is better suited to intranet use or for Websites where you have control over the content.
We selected the sources of our data by trial and error because many of the sites we initially connected to made use of scripting so the data couldn't be imported.
To create a Web Query, open a new worksheet and click on the cell where you want to position the imported data. Next, head to Data | Import External Data | New Web Query. The New Web Query browser will – depending on your version of Excel – open at either the home page you have set in Internet Explorer or sample MSN page. You can navigate to the Website you want by typing in a new URL and clicking 'Go'.
Once you reach your selected site, you can navigate using its standard hyperlinks from within the Web Query browser. Elements of each Webpage appear with yellow and black arrow icons, which when clicked will turn blue, change to a tick and highlight the part of the Webpage containing the data you want to import. By clicking on the Options tab (top right of the Web Query browser) you can select whether you want the data imported without formatting (raw text or data), as rich text only or full HTML formatting.
The latter works best when importing XML (eXtensible Markup Language) data, but there's no guarantee that the rich text or full HTML will make any difference to how the data is imported, nor that the data will be imported at all – it depends on how the Webpage has been created and whether the data has been scripted or encrypted.
If all goes well you'll get a nicely formatted table complete with preserved hyperlinks. Even if you only get raw data, the good news is that it's still updatable. You can then place more Web Queries on a single worksheet by clicking on a vacant cell and repeating the procedure above.
Cutting and pasting from a Webpage into an Excel worksheet also works well. Highlight the text or data you want to cut and paste by holding down the left mouse button and dragging the cursor over it. Once highlighted, press <Ctrl+C> to copy it.
Open your worksheet and click on the cell where you want to position the data and press <Ctrl+V> to paste it. If you're using Office XP, before you do anything else look for the Smart Tag (a small clipboard icon) that will be at one corner of the material you have just pasted.
Click on the down arrow beside the Smart Tag to open a Paste Options menu and select Create Refreshable Web Query to open the Web Query Browser and import the data. If the data cannot be imported as a Web Query that option will not be available in the Smart Tag menu but you should still be able to paste it as static data.
Because exporting or editing from Internet Explorer automatically creates a new worksheet, it is best to begin a multiple Web Query work sheet with either one of them.
If you are searching for data on the Internet, exporting from IE to Excel has a slight advantage over importing from the worksheet because it is easier to search the Web from your native browser rather than the Web Query browser. IE also has an advantage in cases where you have to log in to a site to access the data you're after, because you may not be able to do so through the Web Query browser. However, its disadvantage is that it exports data as raw text compared to the Web Query browser which can be set to import in Rich Text Format or with full HTML formatting (if the site allows it).
To export from IE, right click on the table or text you want and select Export to Microsoft Excel in the drop down menu. A new worksheet will be opened, and by default the data will be positioned from cell A1.
If no data is downloaded and a box appears saying 'This Web query returned no data. . .' click OK. Now click on the name of the external data range in the top left of the worksheet, open the Data menu on the toolbar, select Import External Data and click on Edit Query. It will open the Web Query browser at the page you have attempted to export data from with the arrows tabs indicating where you can potentially get the data you want. Select what you want then click on Import.