Manually Creating a Web Query and Refreshing with VBA

The easiest way to get started with Web queries is to create your first one manually. Using any Web browser, navigate to a Web site and enter the settings needed to display the information of interest to you. In the case of Figure 14.1, the URL to display that portfolio is http://finance.yahoo.com/q/cq?d=v1&s=PSO,+SJM,+KO,+MSFT,+CSCO,+INTC

Open Excel. Find a blank area of the worksheet. From the Excel menu, choose Data, Import External Data, New Web Query. Excel shows the New Web Query dialog with your Internet Explorer home page displayed. Copy the preceding URL to the Address text box and click Go. In a moment, the desired Web page will be displayed in the dialog box. Note that in addition to the Web page, there are a number of yellow squares with a black arrow. These squares are in the upper-left corner of various tables on the Web page. Click the square that contains the data that you want to import to Excel. In this case, we want the portfolio information, so in Figure 14.2, I have clicked the square by the table of quotes. While clicking, a blue border confirms the table that will be imported. After clicking, the yellow arrow changes to a green check mark.

Click the Import button on the New Web Query dialog. Click OK on the Import Data dialog. In a few seconds, you will see the live data imported into a range on your spreadsheet, as shown in Figure 14.3.

Figure 14.2

Use the New Web Query dialog to browse to a Web page.Highlight the table that you want to import to Excel by clicking on a yellow arrow adjacent to the table.

Figure 14.2

Use the New Web Query dialog to browse to a Web page.Highlight the table that you want to import to Excel by clicking on a yellow arrow adjacent to the table.

Figure 14.3

Data from the Web page is automatically copied to your worksheet.You can now use VBA to automatically refresh this data at your command or periodically.

0 0

Post a comment