Building a New Web Query with VBA

The problem with the previous examples is that the Web query URL is hardcoded into the VBA. Someone would be required to edit the VBA code every time that the portfolio changed.

It is fairly simple to build a Web query on the fly. The key is to build a connect string. The connect string for the Yahoo Finance stock quote is

URL;http://finance.yahoo.com/q/cq?d=v1&s=PSO,+SJM,+KO,+MSFT,+CSCO,+INTC

To build a flexible application, you need to use the concatenation character to join the first part of the connect string with the various stock symbols.

Figure 14.4 shows a simple front end for a Web query engine. Anyone can enter stock symbols of interest in the shaded area of Column A. The Get Quotes button is set up to call the CreateNewQuery macro.

Figure 14.4

This worksheet serves as a front end to allow different stock symbols to be entered in Column A. Click the Get Quotes button to run a macro that builds a Web query on the fly on a second worksheet and then copies the data to this sheet.

0 0

Post a comment