Online Investing Hacks

Download Data Using Excel Web Queries

These days, you can find most of the data you need to analyze investments on the Web, and much of it for little or no cost. However, putting that data into a spreadsheet where you can use it is another matter. Some web sites include links to download spreadsheets, text files of prices, or other financial data to your computer. Other sites offer subscriptions to downloadable data. However, if you can accept with equanimity the inevitable web page changes and the subsequent rework of your Excel spreadsheets that those changes require, you can create your own tools to download data from the Web. Excel web queries are easy to use and capture data by taking advantage of tables in a web page’s HTML source. You can use web queries to feed your financial formulas, and produce new investment studies or update portfolio management tables in a matter of seconds.

Tip

Web queries are available in Excel for Windows 2000 and later, or in Excel v.X for Macintosh.

Creating an Excel Web Query

Web queries are pretty slick. The only information a web query needs is the address (URL) of the web page and the tables on that page that contain the data that you want. When you specify the address of the desired web page in the New Web Query dialog box, the web page appears in the dialog box. In Excel XP and 2003, arrows point to each table on the page. After you select the tables to query, Excel extracts the labels and values from those tables and adds them to cells ...