March 6, 2010, 12:03 p.m.
posted by neverloop
Download Financial Data Using Excel Web Queries
When a web site doesn't offer a downloadable file of the data you want, avoid manual data entry by using Microsoft Excel's web query feature.
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.
|
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 a URL 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 in a worksheet. With one shortcut command, you can refresh the data from the Web. To illustrate a web query, let's use an easy examplethe price quote for a stock or mutual fund from Yahoo! Finance.
|
To add a web query to an existing worksheet in an Excel workbook, open the workbook and select the tab for the worksheet you want. To create a new web query in Excel XP, follow these steps:
Choose Data
Import External Data New Web Query. The New Web Query dialog box opens, displaying the home page you use in Internet Explorer. The toolbar in the New Web Query dialog box includes an Address drop-down list, which is automatically populated with yourURL History list from Internet Explorer. The toolbar has other frequently used browser commands, such as Back, Forward, and Refresh.If you've recently accessed the web page you want to query, click the Address arrow to display your URL History list and select the desired web page. Otherwise, you can type the URL for the web page in the Address box and click Go. For example, to use Yahoo! Finance, type http://finance.yahoo.com in the Address box.
You can browse in the New Web Query dialog box, so you don't have to enter the URL for a specific page. For example, after you navigate to the Yahoo! Finance home page, you can type the ticker symbol for the stock you want in the Enter Symbol(s) box and click Goall on the Yahoo! Finance web page in the dialog box browser. The browser in the dialog box displays the quote page for the stock ticker you typed.
Make sure you click the Go button on the Yahoo! Finance web page, not the Go button in the New Web Query toolbar.
In the New Web Query dialog box, the browser positions a yellow box with a black arrow next to every table on the web page. To select a table, position the mouse pointer over the yellow box to the left of the data you want. Excel outlines the table with a thick line so you can verify the data that the query will return. If you picked the correct table, click the yellow box. It changes to a green box with a checkmark, as shown in Figure. Continue to click tables until you've selected all the ones you want.
If you don't see yellow boxes in the browser, click the Show Icons button on the New Web Query toolbar.
To insert the results of the query into the current worksheet, select the Existing Worksheet option. By default, the address for the currently selected cell appears in the box. Type another cell address to specify where you want to import the data on the worksheet.
To create a new worksheet for the query, select the New Worksheet option.
Click OK to import the data into the worksheet.
Although the cells don't look like they have superpowers, they are associated with your web query. You can refresh the data from the associated web page by right-clicking any cell in the web query and choosing Refresh Data from the shortcut menu, as illustrated in Figure.
Visual feedback helps you select tables for a web query
Refresh web query data with the Refresh Data command
Making a Web Query Work for Any Ticker Symbol
The web query you just created is pretty handy. You can update the quote for a stock you own or watch by refreshing its data. However, as you manage your investment portfolio, you must constantly evaluate new stocks and mutual funds. Creating a new web query for each investment prospect would become downright tiresome. Wouldn't it be cool if you could make this query download data for a new prospect simply by typing its ticker symbol into a worksheet cell? Well, you can, and it's easy when you follow these steps:
To customize a web query, first save it as a file. Right-click any cell for the web query in the worksheet and choose Edit Query from the shortcut menu. In the Edit Web Query dialog box, make sure that the tables you want show green boxes with checkmarks, and then click the Save As icon on the toolbar (see Figure). In the Save As dialog box, navigate to the folder in which you want to store the web query, type a name for the file (such as Yahoo_Price_Quote) in the File Name box and click Save.
Click the Save As icon to save a web query to a file
Next, add the ticker symbol as a parameter to the saved web query file. Navigate to the saved web query file in Windows Explorer (Yahoo_ Price_Quote.iqy in this example), right-click it, and choose "Edit with Notepad" from the shortcut menu. The third line in the file specifies the URL for the web page. The URL for a Yahoo! Finance quote page, like most web pages with data for a specific investment, includes the ticker symbol of the stock or mutual fund quoted. In Figure, the ticker symbol low represents Lowe's.
The URL for a web page with financial data often includes the investment's ticker symbol
http://finance.yahoo.com/q?s=low
To make the web query download the data for the ticker symbol you specify, replace the ticker symbol in the URL with ["symbol", "Enter Symbol"]. The URL in the web query should look like the line in Figure.
Modifying the URL in a web query to prompt for the ticker symbol
http://finance.yahoo.com/q?s=["symbol", "Enter ticker symbol"]
To save the web query, choose File
Save.
Now, use this new web query in a spreadsheet to retrieve data based on the ticker symbol you specify:
Type a ticker symbol in a worksheet cellfor example, use cell A2 on Sheet1.
In the Import Data dialog box, select an option to specify whether to use the current worksheet or create a new one. If you select the Existing Worksheet option, type the cell address that denotes the upper-left corner of the cell range where you want the data imported in the Existing Worksheet box.
Click Parameters. In the Parameters dialog box, select the "Get the value from the following cell" option, click the box immediately below the option, and then select the worksheet cell that contains the ticker symbol (in this example, A2).
The "Get the value from the following cell" option does not accept named cells. You must either select the cell that contains the parameter value in the worksheet or type the cell address using column and row references, such as A2.
If you want the web query to retrieve new values automatically from the Web when you enter a new ticker symbol, check the "Refresh automatically when cell value changes" checkbox.
Click OK twice to import the data. In this example, when you type a new ticker symbol in cell A2 and press Enter or an arrow key to navigate away from the ticker symbol cell, the web query refreshes the web query cells with values for the new ticker symbol.
When a web query uses parameters, you can use the same web query text file to import data for multiple companies into different areas of a worksheet. To reuse a web query text file, select the cell in the upper-left corner of the cell range into which you want to import data, and then repeat steps 17.
Hacking the Hack
After your web query successfully grabs the data you want from the Web, you can feed those values into a data summary worksheet or into formulas on other worksheets. You could name the cells within the web query cell range, but that could throw your calculations off if the query returns values in a different order. By using a function such as VLOOKUP instead, you can find the text label that identifies the value you want, regardless of the cell. For example, suppose you want to use values from a price query to compare the current price to the 52-week high and low prices. In Figure, the Current Price and 52-week range cells use VLOOKUP to find values based on labels, as the formula bar shows.
The VLOOKUP function finds values based on labels, not cell addresses
Take a look at the worksheets in Figure to see how this works. To obtain the current price from the Yahoo! price web query, find the row that has the label Last Trade: in column A on the Price Query worksheet. Then, retrieve the value in that row from column B. The formula to perform these tasks is in cell B3 on the Price Check worksheet and is also shown in Figure.
Using VLOOKUP to find a value in one column based on the label in another column
Current Price = VLOOKUP("Last Trade:",'Price Query'!$A:$B,2,FALSE)
|
To find the 52-week price range, use 52wk Range: as the lookup_value, as shown in Figure.
Another example of VLOOKUP
Price Range = VLOOKUP("52wk Range:",'Price Query'!$A:$B,2,FALSE)
Finally, you can use some nested text functions to extract the high and low values out of the 52-week price range, as shown in Figurefor instance, to calculate the percentage change from the stock's high price to its current value. You can use functions such as RIGHT, LEFT, LEN, and FIND to parse the prices out of the price range. The VALUE function converts the text to a numeric value.
Using text functions to extract information from text strings
High Price = VALUE(RIGHT(price_range,LEN(price_range)-FIND("- ",price_range)-1))
Low Price = VALUE(LEFT(price_range,FIND("- ",price_range)-1))
Percent Change = (Current Price - High Price)/High Price
- Comment