8.1. Analyzing stock quotes with a REST Web service Worldwide Widget Corporation is known for its award-winning pension plan. Each month, N. Ron Ponzi, the plan's investment advisor, analyzes dozens of securities before investing 100% of the pension fund in Worldwide's own stock. Ron would like us to create an Excel worksheet to help him analyze stock price information. He wants to be able to enter a stock symbol, month and year, and retrieve the quotes for that month into the worksheet. These quotes can easily be retrieved from a Web service. An example of the stock quote worksheet is shown in Figure 8-1. Figure 8-1. Stock quote worksheet 
Importing the quote data into Excel allows Ron to take advantage of Excel's data analysis features, creating charts and graphs of the data such as the one shown in Figure 8-2. Figure 8-2. Stock quote graph 
Our solution requires several steps: - 1. Save a sample of the Web service output as a local XML file.
- 2. Create a worksheet that is mapped to the schema that Excel infers from the sample service output.
- 3. Write some Visual Basic for Applications (VBA) code that accesses the Web service and reimports the map with the XML data returned by the Web service. This code will be executed every time the user clicks a "Refresh" button that we will add to the worksheet.
8.1.1 The stock quote Web service To access the data, we will use the Web service located at http://www.xignite.com/xquotes.asmx. This Web service consists of a set of operations that retrieve stock quote data. If you browse the above URL, you will see a list of those operations. We will use one called GetQuotesHistorical. Selecting that operation will bring up a page that describes the operation and provides examples. About half way down the page is the syntax for calling the service using the HTTP GET method, which is a REST interface. It says: Example 8-1. Syntax for Web service request GET http://www.xignite.com/xquotes.asmx/GetQuotesHistorical? Symbol=string&Month=string&Year=string HTTP/1.1 Host: www.xignite.com
As we are using a REST interface to this Web service, rather than its SOAP interface, we can invoke the service simply by specifying a URL that includes the necessary parameters. For example, if we want the quotes for Microsoft Corporation, for March of 2003, we can specify the following URL: Example 8-2. Web service request URL for MSFT data http://www.xignite.com/xquotes.asmx/GetQuotesHistorical? Symbol=MSFT&Month=3&Year=2003
The result will be returned as an XML document, as shown on that page. An example is shown in Example 8-3. Example 8-3. Sample output of the stock quote Web service <?xml version="1.0" encoding="utf-8"?> <ArrayOfHistoricalQuote xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns="http://www.xignite.com/services/"> <HistoricalQuote> <Outcome>Success</Outcome> <Date>3/31/2003</Date> <Open>24.25</Open> <High>24.71</High> <Low>24.01</Low> <Last>24.21</Last> <Volume>71384000</Volume> <PercentChange>-0.165</PercentChange> <SplitRatio>1</SplitRatio> <OpenAdjusted>24.25</OpenAdjusted> <HighAdjusted>24.71</HighAdjusted> <LowAdjusted>24.01</LowAdjusted> <LastAdjusted>24.21</LastAdjusted> <ChangeAdjusted>-0.04</ChangeAdjusted> <VolumeAdjusted>71384000</VolumeAdjusted> <Change>-0.04</Change> </HistoricalQuote> <HistoricalQuote> <Outcome>Success</Outcome> <Date>3/28/2003</Date> <Open>24.67</Open> <!-- ... --> </HistoricalQuote> </ArrayOfHistoricalQuote>
You can test this out yourself by entering the URL in Example 8-2 into your browser and viewing the source. 8.1.2 Setting up the XML map To create the XML map in the worksheet, we will need a sample of the XML data that will be returned from the Web service. This can be obtained by executing the Web service in a browser and copying the result, or by copying the sample output from the page describing the Web service. We have provided sample output in the file sampledata.xml. It does not matter which ticker symbol or month you choose right now; we simply care about the structure. To set up the map: - Open the worksheet stock quotes start.xls. This is a mostly empty worksheet.
- On the Data menu, point to XML, then click XML Source. This brings up the XML Source task pane.
- Click XML Maps to bring up a list of XML maps in the worksheet (currently there are none).
- Click Add and select sampledata.xml from the list. Alternatively, you can enter a URL request to execute the Web service and return a result; e.g. the URL in Example 8-2.
- Click Open.
- Click OK when Excel warns you that it will infer a schema, then OK again to return to the main dialog.
- This brings up the XML Source task pane. From it, drag the nsl:HistoricalQuote element type to the top left corner of the location where you want to put the quote data in the worksheet; for example, cell A7.
You can customize the map to meet your needs. For example, you can change the column headers, or delete columns that contain data that you do not need (for example, Outcome). You now have a map that will import the data returned by the GetQuotesHistorical operation of the Web service. 8.1.3 Refreshing the data Because the user wants to pass different parameters to the Web service each time he refreshes the data in the map, he cannot simply use the Refresh XML Data command on the XML submenu. He could use the Import command on the XML submenu, but only if he were willing to enter a new URL (with different parameters) for each import. However, N. Ron Ponzi felt (not unreasonably) that constructing a URL would be cumbersome and error-prone. Instead, we decided to write a macro that constructed the URL from parameters entered in the worksheet. Clicking a "Refresh" button would call the macro, which would invoke the Web service with the constructed URL and refresh the data. To create the button and macro: - Right-click the toolbar at the top of the window and click Control Toolbox. This will display the small Control Toolbox toolbar shown in Figure 8-3.
Figure 8-3. The Control Toolbox toolbar 
- Click the picture that looks like a button.
- Click the location in the worksheet where you want the button placed.
- Select the button and right-click. On the shortcut menu, click Properties.
- Change both the (Name) and the Caption properties to Refresh.
- Close the Properties dialog.
- Right-click the button again. On the context menu, click View Code. This will bring up the Visual Basic Editor with the cursor positioned in a function that will be executed each time the button is clicked. This is shown in Figure 8-4.
Figure 8-4. Visual Basic Editor 
The code entered into the Visual Basic Editor should look like that shown in Example 8-4. You can either type it in or insert it from the example file refresh_quotes.txt. Example 8-4. Function to import from a constructed URL Private Sub Refresh_Click() Dim theURL, ticker, month, year, beginningOFURL As String Dim theMap As XmlMap Set theMap = ActiveWorkbook.XmlMaps(1) beginningOFURL = "http://www.xignite.com/xquotes.asmx/GetQuoteHistorical?Symbol=" ticker = Range("B3").Text month = Range("E3").Text year = Range("G3").Text theURL = beginningOFURL+ticker+"&Month="+month+"&Year="+year theMap.Import (theURL) End Sub
The code takes the parameters entered by the user from the worksheet, strings them together to construct the URL, and then refreshes the data in the map with the results. -
Lines 3-5 of the example declare the variables and assign the appropriate (first and only) map to the variable theMap. -
Lines 7 to 12 concatenate the various parts of the URL based on the contents of the cells that contain the parameters. -
Line 14 performs the actual import, using the constructed URL. To test your solution, you must first exit design mode by clicking the top left icon on the Control Toolbox toolbar. A complete working version of this example can be found in the sample file stock quotes.xls. | 
