Using Excel Web Queries to Retrieve Data
by Wayne A. Thorp, CFA
Spreadsheets allow us a great deal of flexibility when it comes to analyzing and manipulating financial data. However, a big hurdle is simply getting that data to manipulate and analyze.
While several Web sites, such as Yahoo! Finance and Google Finance, provide price and volume data that is easily downloadable to a spreadsheet, finding other data—and importing it into a spreadsheet—poses more of a challenge. Manually entering selected financial data for a few companies may not be overly time-consuming, but attempting to enter in detailed financial statement data for even a single company would be tedious and laborious.
One under-publicized feature in Excel that greatly speeds up this process by allowing you to pull data directly from the Web into a spreadsheet is the Web query. This installment of Spreadsheet Corner will introduce Excel Web queries and show how you can use them to collect financial data from a Web page for further analysis.
The Web Query Defined
Excel Web queries provide an easy way to capture data from a Web site and import it into a worksheet by taking advantage of tables embedded in a Web page’s HTML source code. HTML—hypertext markup language—is the predominant programming language used to create Web pages. Web queries are available in Excel 1997 or later for Windows and Excel 2001 or later for the Mac.
Getting Started
Creating a Web query is relatively easy—all you need is the Web address (URL) of the Web site from which you want to collect data and the tables on that page containing the data you wish to capture. Since Web queries are tied to a given URL and the structure of the data on that page, any subsequent changes a site may make to URLs or data format will require you to recreate your Web query.
When you specify the URL or address of the Web page with the desired data, the Web site will appear in a dialog box with arrows pointing to each table on the page. You can select multiple tables on the same page to query and Excel then extracts the data labels and the underlying data and places them in the cells of your spreadsheet.
Let’s begin with a basic example—retrieving current stock or mutual fund quote data from Yahoo! Finance. Note that the examples here are performed using Excel 2007 for Windows. Your experience may differ if you use an older version of the program.
Begin by opening your Excel spreadsheet program. To create a Web query in Excel 2007 for Windows, follow these steps:
Click on the Data tab on the Ribbon and select “From Web” under Get External Data (Figure 1). The New Web Query dialog box will open to the home page you use in Internet Explorer. The dialog box has many of the functions of a typical Web browser, with the address at the top and browser command buttons such as Back, Forward, and Refresh.
Type, or copy and paste, the address or URL of the Web site from which you wish to retrieve data in the Address box and click the Go button. For this example, we will use the Yahoo! Finance address http://finance.yahoo.com.
After you navigate to the Yahoo! Finance home page, type the ticker symbol of the stock or mutual fund in the box at the top-left of the home page and then click on the Get Quotes button. We are using GOOG (Google) in our example.
In the New Web Query dialog box, a yellow box with a black arrow is positioned at the top left of every table on the page. By positioning your mouse cursor over one of these arrows, the corresponding table becomes outlined in blue. If this is the table you wish to import into your spreadsheet, click on the arrow so that it changes to a green box with a checkmark and the table is highlighted in blue (Figure 2). Continue clicking on tables to select all the ones you want.
When you are finished selecting tables to import, click on the Import bottom at the bottom right of the New Web Query dialog box. This will open the Import Data dialog box.
You can either insert the results of your Web query into an existing spreadsheet or have the data placed in a new spreadsheet. You can also specify the cell in the spreadsheet where you want the data to begin.
Click OK to import the data into the worksheet (Figure 3).
Once the data is in your spreadsheet, you can manually refresh it by right-clicking any cell of the Web-queried data and selecting Refresh from the shortcut menu (Figure 4).
Similarly, you can instruct Excel to automatically refresh the Web query data at regular intervals by again right-clicking any cell of the Web-queried data and selecting Data Range Properties from the shortcut menu. This will open the External Data Range Properties dialog box. Under the Refresh control options, you can specify that the data is refreshed automatically as often as once every minute.
Creating Custom Queries
The example we just covered is an easy way to retrieve and refresh data for a single stock. However, most of us have a portfolio or watchlist of several stocks and mutual funds, so creating a new Web query for many different tickers can become rather time-consuming.
Luckily, you can create custom queries that will download the data for a ticker you enter into a spreadsheet cell. Here are the steps to follow to create such a custom query:
- To begin customizing a Web query, save it to a file by right-clicking any cell of Web-queried data in a worksheet and select Edit Query from the shortcut menu (Figure 5). This will open the Edit Web Query dialog box, which looks just like the New Web Query dialog box from the previous example. After making sure the tables you want have green box checkmarks and are highlighted in blue, click on the Save Query icon in the toolbar at the top right of the Edit Web Query dialog box. In the Save As dialog box, choose the folder where you wish to save the Web query, type in a name in the File name box, and click Save. Be sure to remember where you saved this file, as you will need to find it for the next step!
- Now you need to add a new ticker symbol prompt to your saved Web query file. Find the file on your computer, right-click on it, and select Edit with Notepad from the shortcut menu. This will open Notepad and display the contents of the Web query file (Figure 6). The third line of the query file is the URL of the Yahoo! Finance quote page for ticker GOOG (Google).
-
To edit the query so that it downloads the data for another ticker symbol, make the following change to the third line of the query file using Notepad:
Before:
http://finance.yahoo.com/q?s=goog
After:
http://finance.yahoo.com/q?s=[“symbol”, “Enter ticker symbol”]
- We can now use this modified Web query to retrieve the data of any ticker symbol we enter into an Excel worksheet by following these steps:
- Type a ticker symbol into an Excel worksheet cell.
- Click on the Data tab on the Ribbon and select “Existing Connections” under Get External Data. The Existing Connections dialog box will open with a list of saved query files on your computer (Figure 7). In this example, we click on our Yahoo! price quote query and then click on the Open button at the bottom right of the Existing Connection dialog box. This will open the Import Data dialog box.
- You can either insert the results of your Web query in an existing spreadsheet or have the data placed in a new spreadsheet. You can also specify the cell in the spreadsheet where you want the data to begin. If you choose to import the data into the same worksheet in which you typed the ticker in Step 5 of this example, make sure you specify a cell that is to the right of the cell containing the ticker symbol. Once you have done this, click Ok.
- In the Enter Parameter Value dialog box, click in the worksheet cell that contains the ticker symbol we entered in Step 5 (Figure 8).
- If you want the Web query to automatically retrieve data from the Web site whenever you enter in a new ticker, click the box next to “Use this value/reference for future refreshes” so that a checkmark appears in the box. Also click on the box next to “refresh automatically when cell value changes” so that a checkmark appears in that box.
- Click OK when you are finished. Now, whenever you type in a different ticker symbol in cell A1, the Web query will retrieve the latest quote data for that ticker from the Yahoo! Finance Web site.
Web Queries for Financial Statement Data
Beyond retrieving current quotes from the Internet, another area where Web queries can be useful is for collecting financial statement data. It is rare to find sites that will allow you to download spreadsheet files of this type of data, and manually importing the detailed financials of even a single company probably isn’t worth the time and hassle. However, one Web site I recommend for detailed financial statement data is Reuters.com (www.reuters.com).
Unlike the Yahoo! Finance Web queries we ran earlier that involved a single parameter (the ticker symbol), creating a custom Web query to retrieve financial statement data from Reuters is a little more complicated.
|
You begin by creating a Web query that retrieves the financial statement data table from the Reuters' Web site (Figure 9), following the same procedure outlined in Steps 1 through 6 of the "Getting Started" section earlier in this article. For our example, we are again using the stock Google (GOOG). Go to www.reuters.com/finance and select Stocks under News & Markets at the top of the page. On the Stocks page, type in the ticker, select the radio button in front of Financials to the right of the search box and click on the Search button. On the Financials page, select View Financial Statements, which will by default take you to the interim (quarterly) income statement page. It is very important that you click on the radio button to the left of Annual before selecting this table and importing it.
After importing the data into your spreadsheet, right-click on the Web-queried data in the worksheet, select Edit Web Query from the shortcut menu, and then click on the Save Query icon on the toolbar at the top right of the Edit Web Query dialog box (as outlined in Step 1 of the "Custom Queries" section earlier in the article).
You now need to edit the Web query to prompt for which financial statement to download, the period type of the statement and, lastly, what company's financials to retrieve.
|
The parameters you must specify are found by looking at the URL from the query file we saved in Step 2: http://www.reuters.com/finance/stocks/incomeStatement?stmtType=INC&perType=ANN&symbol=GOOG.O
As a rule, the parameters are anything that falls after an equal sign ("=") in the URL. Here we see three equal signs after:
- stmtType;
- perType; and
- symbol.
Statement Type
In the URL above, stmtType refers to the type of financial statement being displayed or retrieved. Reuters uses the following abbreviations for the three types of financial statements:
- INC for income statement
- BAL for balance sheet
- CAS for cash flow statement
Period Type
In the URL, perType refers to period type. This can either be an annual statement (ANN) or quarterly/interim period (INT).
Symbol
Just as in the quote Web query for Yahoo! Finance, symbol refers to the stock ticker symbol.
The next step is to modify the URL in the Web query file to account for the parameters we want to specify, as shown in Figure 10, and then resave the Web query file.
Following Steps 5 through 10 under "Custom Queries," you arrive at the finished product shown in Figure 11. However, this time, unlike the previous example, there are three parameters you must enter (highlighted in yellow on the worksheet). In this example, the data displayed is the quarterly (INT) balance sheet (BAL) data for Ford Motor Company (F). Each time the inputs in cells A2, B2, and C2 are changed, the Web query will retrieve the corresponding financial statement data from the Reuters Web site.
|
Conclusion
We have only scratched the surface of the types of queries you can perform, but hopefully this offers a good starting point for you to perform your own Web queries. If you have any Web queries you have created and would like to share with us, please e-mail them to ci@aaii.com.