PRDV006: Spreadsheets II: Formatting and Functions

Web queries allow you to import external data from a website into an Excel worksheet. We can enhance the personal investment portfolio demonstrated in this chapter through the use of web queries to import current stock prices from a website. Once a stock price is imported into the workbook, a cell reference can be added to the Investment Detail worksheet to reflect the most current price for an investment. It is important to note that you must establish an Internet connection before proceeding with this exercise. The following steps explain how to retrieve the current stock price for Microsoft from the Yahoo! Finance website and import it into the Price Data worksheet:

1. Click cell A2 on the Price Data worksheet.
2. Click the Data tab of the Ribbon.
3. Click the From Web button located in the Get External Data group of commands. This will open the New Web Query dialog box.
4. Type the following Internet address (URL) in the Address input box (see Figure 1 New Web Query Dialog Box): http://www.yahoo.com. Press the ENTER key on your keyboard after entering the URL.

5. Use the scroll bar on the right side of the New Web Query dialog box to locate the Finance link on the left navigation pane of the website. Click the Finance link (see Figure 1 New Web Query Dialog Box).

   

   Figure 1 New Web Query Dialog Box
   
   

6. Locate the Get Quotes input box by using the scroll bar to scroll to the top of the website (see Figure 2 Get Quotes Input Box on Yahoo! Finance).

7. Click in the Get Quotes input box, type the stock symbol MSFT, and press the ENTER key on your keyboard (see Figure 2 Get Quotes Input Box on Yahoo! Finance).

   

   Figure 2 Get Quotes Input Box on Yahoo! Finance
   
   

8. When viewing the stock price data for MSFT, you will see several yellow boxes or tags with arrows (see Figure 3 Selecting a Tag to Import Data from a Website). These tags indicate blocks or tables of data that can be imported into a worksheet from a website. Click the yellow tag next to the Last Trade price. The yellow tag will turn green with a check mark inside of it.

   

   Figure 3 Selecting a Tag to Import Data from a Website
   
   

9. Click the Import button at the bottom of the New Web Query dialog box. This will open the Import Data dialog box, as shown in Figure 4 Import Data Dialog Box.

   

   Figure 4 Import Data Dialog Box
   
   

10. Click the Properties button at the bottom of the Import Data dialog box (see Figure 4 Import Data Dialog Box). This will open the External Data Range Properties dialog box.
11. Click in the Name input box on the External Data Range Properties dialog box and delete the query name that is provided. Type the new query name Microsoft Stock Price.

12. Click the box next to the “Refresh every” property. Change the refresh minutes to 5. This means the query will automatically search the website every 5 minutes to retrieve an updated stock price for Microsoft (see Figure 5 Final Settings for the External Data Range Properties Dialog Box).

    

    Figure 5 Final Settings for the External Data Range Properties Dialog Box

    
    

13. Click the option at the bottom of the External Data Range Properties dialog box that reads “Overwrite existing cells with new data, clear unused cells” (see Figure 5 Final Settings for the External Data Range Properties Dialog Box). It is important to select this option if you intend to add more than one web query to a worksheet or if you are using formulas in adjacent columns to a web query. This option will prevent the web query from deleting entire rows of data from the worksheet.
14. Click the OK button at the bottom of the External Data Range Properties dialog box.
15. Click the OK button at the bottom of the Import Data dialog box.
16. After the data is imported, click cell H17 on the Investment Detail worksheet and type an equal sign (=).
17. Click the Price Data worksheet tab.
18. Click cell B2 on the Price Data worksheet and press the ENTER key on your keyboard. This will display the price that is imported from the web query on the Investment Detail worksheet.
19. Figure 6 Stock Price for Microsoft Imported into the Price Data Worksheet shows the results of importing the Microsoft stock price from the Yahoo! Finance website. It is important to note that the data you retrieve from the website will be different from what is shown in this figure.

Figure 6 Stock Price for Microsoft Imported into the Price Data Worksheet

 This text was adapted by Saylor Academy under a Creative Commons Attribution-NonCommercial-ShareAlike 3.0 License without attribution as requested by the work's original creator or licensor.