Setting up the web query

Excel provides a very useful option to gather data from websites, called web queries. These have been introduced with Excel 97 and have been further enhanced in the versions released after Excel 97. This article shows how you can setup a web query so that you can make the worksheet dynamically update based on values in so called parameter cells.

Setting up a web query is as simple as clicking the Data tab and then clicking the “From Web button on the Get External Data tab.

You’ll get this screen:

Enter http://moneycentral.msn.com/companyreport?symbol=MSFT and click the Go Button.

Don’t do anything yet, first click the “Options” button and set the webquery to return full html formatting results (if so desired):

After changing these settings, select the part you want to be imported into your sheet by clicking the appropriate black-on-yellow arrow:

Now we’re ready to click “Import”. If the page takes time to load, you’ll see a progress screen:

Next Excel asks where to put the results; put them in cell A3 so we have room above the table:

If you want an interactive result in your sheet, enabling you to enter new search criteria in a cell, you have to go through a little VBA. Don’t worry, this is easy.

These steps are needed:

1. Click any cell in the new table.
2. Open the Visual Basic Editor by hitting the Alt+F11 key.
3. Hit control+g to open the immediate window.
4. Now type the following line:
   
   activecell.QueryTable.Connection="URL;http://moneycentral.msn.com/companyreport?symbol=[""Ticker""]"
5. Click Enter to confirm.
6. Close the Visual Basic Editor.

You have now made part of the url work as a parameter, by replacing that part of the url with some text between quotes and square brackets (we used a bit of VBA because with the MSN site you can’t do this through the user interface). The string you entered will be used as both the name of the parameter and the prompt.

Click the refresh all button on the data tab of the ribbon, in the connections group. Excel will interpret the part between the square brackets as a parameter and prompt you for a value:

Excel detects we have a parameter and now asks what value you want.