Willow Tips

Tip #2001-11: Income Statements from the Internet

How It Works - Steps to Create a Web Query in MS Excel

1. Use your web browser to go to the web site which contains the information you want to retrieve into Excel. For example, we want to retrieve the items available in the quarterly financial statements for IBM. The website where we found this information displayed was at the Microsoft Investor web site, it is supplied by Media General Financial Services. The web site address is:
   
   http://moneycentral.msn.com/investor/invsub/results/statemnt.asp?lstStatement=Income&Symbol=IBM&stmtView=Qtr
   
   

2. Highlight and copy the URL (Uniform Resource Locator) for the site from the web browser's address bar.
   
   

3. Select Get External Data from Excel's Data menu and choose New Web Query.
   
   
   
   

4. In the New Web Query dialog box, click in the text box and press Ctrl V to paste the URL you copied from the address bar in you web browser. You could have also clicked on the Browse Web… button and browsed to the site you want to query.
   
   

5. Locate the data you want to retrieve. You usually do not want the entire web page but only the information which is in a particular table on the web page. The Internet Explore web browser allows you to right click on a web page and view the HTML code. Tables start with the <TABLE> tag and end with the </TABLE> tag. Through a combination of counting and trial and error the income statement information which we needed was located in table 14.
   
   

6. The last step is to decide how to format the data. This is a matter of personal preference. In our web query we chose full HTML formatting.

If your web query does not return the data the way you want it, you can change it by right clicking in a cell in the external data range and then clicking on Edit Query.

The Income Statements Mini App

You are welcome to use or modify the macro code which performs the web query. It uses Visual Basic's QueryTable object to do the web query. The QueryTable object represents a worksheet table built from data returned from an external data source and automates the steps used to create the web query.

Willow Solutions has extensive experience in retrieving and publishing financial information on the internet. Contact us if you would like to employ our services to help you enhance this model or help you design financial models or customized applications.