Sign Up for Free Weekly Newsletter

Click Here

                                 

Bookmark and Share




Search     

Printer Friendly Version


Excel Queries Snare Stock Data From Websites


Download Investing Stats Into Your Spreadsheets



 Bonnie  Biafore Bookmark and Share

Excel Web queries have been around for a decade, but they’re still a slick way to feed data to your pet investment spreadsheet. Web queries pluck data from tables in a webpage’s HTML code. Give Excel the webpage address (URL) and the tables that contain the data you want. Voila! Your spreadsheet burgeons with downloaded data.

In Excel 2010, a Web query pulls data from a webpage’s address and layout. If the webpage address changes or presents data in different tables, you have to re-create the Web query to match the new configuration.
   
Here’s how you create a Web query in Excel 2010:

1.    Choose the File tab and click Open. In the Open dialog box, select the workbook and click Open. (Office 2010 ditched the Office button and now has a File tab. But Microsoft calls the page that the File tab opens the Backstage.)

2.    Select the tab for the worksheet you want.  
3.    On the ribbon, choose the Data tab.

4.    In the Get External Data section, choose From Web. The New Web Query dialog box opens, displaying the homepage you use in Internet Explorer.

5.    Fill in the webpage URL and click Go. This example starts with the Yahoo! Finance mutual fund quote page for the Vanguard 500 Index fund (ticker VFINX), so the URL in the Address box is http://finance.yahoo.com/q?s=vfinx. The Address drop-down list automatically grabs your URL History list from Internet Explorer.
   
If you’ve recently opened the webpage you want, click the Address down arrow and select the webpage address from the drop-down list. You can also open your browser outside of Excel, use a browser bookmark to open the page you want and then copy the URL into the Excel New Web Query dialog box (see Figure 1). 
   
You can browse in the New Web Query dialog box.  For example, if you start at the Yahoo! Finance homepage, you can type a ticker symbol in the Get Quotes box and click the Get Quotes button. The browser in the New Web Query dialog box displays the quote page for the fund ticker you typed. Then to reach the performance page, in the New Web Query browser click Performance in the Yahoo! vertical navigation bar (see Figure 2).



6.    The browser in the New Web Query dialog box displays a yellow box with a black arrow next to every table on the webpage. If you don’t see yellow boxes in the browser, click the Show Icons button on the New Web Query toolbar.

7.    To select a table, point to the yellow box to the left of the data you want. Excel changes the yellow box to green and outlines the table with a thick blue line so that you can verify the data you’re grabbing. If the right table is outlined, click the box with the arrow.
   
8.    Repeat step 7 for other tables you want.
   
9.    Click Import. The Import Data dialog box appears.
   
10.    To insert the results of the query into the current worksheet, select the Existing worksheet option. The text box automatically fills in the address of the selected worksheet cell. To import the data to another location on the worksheet, type the cell address. (To create a new worksheet for the query, select the New worksheet option.)

11.    Click OK to import the data. You can reference those cells like normal cells.

12.    To flex the imported cells’ powers, right-click any cell in the web query area and choose Refresh from the shortcut menu. Excel surfs to the web page and downloads the data again.
   
Although Web queries download data in a jiffy, you don’t want to create a Web query for every stock, fund or ETF you evaluate. You can modify a query to download data based on the ticker symbol you type in a worksheet cell.
   
Here’s how:

1.    First, save the query as a file. Right-click any cell in the Web query area and choose Edit Query from the short-cut menu.

2.    In the Edit Web Query dialog box, make sure that the tables you want still show green boxes with check marks.

3.    Click the Save As icon on the toolbar. (It looks like a floppy disk with a worksheet in front of it.)

4.    In the Save As dialog box, navigate to the folder you want, type a name for the file in the File Name box, like FundQuote, and click Save.

5.    Now you have to add the ticker symbol as a parameter to the saved Web query. In Windows Explorer, right-click the saved Web query file (Fund Quote.iqy in this example) and choose Edit with Notepad from the shortcut menu.

6.    The third line in the file is the URL for the webpage. Like most webpages with data for a specific investment, the Yahoo! fund page address includes the ticker symbol of the fund: http://finance.yahoo.com/q/pms=VFINX+Performance.

7.    To insert a parameter for the ticker symbol, replace the ticker symbol in the URL with “symbol”, “Enter ticker symbol” (see Figure 3, above).

8.    To save the Web query, choose File? Save.
   
You can use this web query to retrieve data for the ticker symbol you type in a worksheet cell. First, you have to add the Import External Data command to the ribbon or the Quick Access Toolbar.  

1.    Click the down arrow to the right of the Quick Access toolbar.

2.    On the drop-down menu, choose More Commands.

3.    In the Excel Options dialog box, in the Choose commands from drop-down list, choose Commands Not in the Ribbon.

4.    In the command list, select Import External Data and click Add. Click OK to close the options dialog box.
   
Here’s how you use the query to retrieve data:

1.    Type a ticker symbol in a worksheet cell — for example, cell B1 on Sheet 1.

2.    On the Quick Access toolbar, click the Import External Data icon.

3.    In the Select Data Source dialog box, navigate to the folder that contains your saved Web query file, select the file and click Open.

4.    In the Import Data dialog box, select Existing worksheet to import the data into the current worksheet.  Select New worksheet to create a new one.

5.    For the Existing worksheet option, type the cell address for the upper-left cell where you want the data imported. Click OK.
   
6.    In the Enter Parameter Value dialog box, fill in the cell address that contains the value you want to query.  In this example, the label for the box is Enter ticker symbol and you choose the B1 cell.
   
7.    Turn on the check box for Use This Value/Reference for Future Refreshes.
   
8.        If you want the Web query to retrieve new values automatically when you enter a new ticker symbol, check the Refresh automatically when cell value changes check box.
   
9.    Click OK to import the data.
   
10.    When you type a new ticker symbol in cell B1 and press Enter or an Arrow key to navigate away from the cell, the Web query refreshes the Web query cells with values for the new ticker symbol.

Link of the Month

Microsoft Office Online provides help, demos, training courses and more. Click the Help and How-to tab.  Then, in the How-to Resources section, click Training, Demos, Webcasts or Podcasts to learn more about Office 2010. If Microsoft resources don’t do the trick, try YouTube. Type Excel 2010 in the box and click Search.

Websites of Interest

Microsoft Office Online
YouTube


Bonnie Biafore is the author of 24 books about investing, personal finance, project management, software (such as QuickBooks and Project) and the recently published novel, Fresh Squeezed. Go to BonnieBiafore.com to learn more.



Learning Events Near You:

Find a Chapter Near You

Corporate Partners

Learn more about

companies supporting

BetterInvesting's mission