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.

Using the New Microsoft Office File Format

Starting with the 1997 release, all Microsoft Office programs have used a binary file format that computers (but not humans) can read. Excel 2007, Microsoft Office Word 2007, and Microsoft Office PowerPoint® 2007 have a new and improved file format that, in addition to being somewhat readable, creates much smaller files than the older binary format.

The new Microsoft Office Open XML Formats combine the XML and the Zip file compression format to create robust files that (on average) are about half the size of similar Excel 972003 files. You can open and save Excel 972003 files in Excel 2007, of course. If you want to open Excel 2007 files in Excel 2000, Excel 2002, or Excel 2003, you can install the Microsoft Office Compatibility Pack for Office Word 2007, Excel 2007, and Office PowerPoint 2007 file formats from this book's companion CD.

Besides smaller file sizes, the 2007 Microsoft Office system file formats offer several other advantages:

• Improved interoperability. Because the new file formats use XML as their base, it is much easier for organizations to share and exchange data between the Microsoft Office system programs and other applications. The older binary file format was difficult to read and wasn't standards-based.
• Enhanced customization. The letter "X" in XML stands for "extensible," which means that information professionals and developers can create custom document structures, or schema, that meet their organization's needs.
• Improved automation. The Excel 2007 file format is based on open standards, which means that any program written to process data based on those standards will work with Excel 2007. In other words, you don't need to write special routines or use another program in the Microsoft Office system to handle your Excel 2007 data programmatically.
• Compartmentalizing information. The new Microsoft Office system file format separates document data, macro code, and header information into separate containers, which Excel 2007 then combines into the file you see when you open your workbook. Separating macro code (automated program instructions) from your worksheet data improves security by identifying that a workbook contains a macro and enables you to prevent Excel 2007 from executing code that could harm your computer or steal valuable personal or business information.

Some capabilities found in Excel 2007

The table below summarizes the expanded data storage and other capabilities found in Excel 2007.

Limit	Excel 2003	Excel 2007
Columns in a worksheet	256	16,384
Rows in a worksheet	65,536	1,048,576
Number of different colors allowed in a workbook	56	4.3 billion
Number of conditional format conditions applied to a cell	3	Limited by available memory
Number of sorting levels of a range or table	3	64
Number of items displayed in an AutoFilter list	1,024	32,768
Total number of characters displayed in a cell	1,024	32,768
Total number of characters per cell that Excel can print	1,024	32,768
Total number of unique cell styles in a workbook	4,000	65,536
Maximum length of a formula, in characters	1,024	8,192
Number of nested levels allowed in a formula	7	64
Maximum number of arguments in a formula	30	255
Number of characters that can be stored and displayed in a cell with a text format	255	32,768
Number of columns allowed in a PivotTable	255	16,384
Number of fields displayed in the PivotTable Field List task pane	255	16,384