Checking Stock Quotes over the Web Using the Web Service References Tool and Microsoft Excel

This content is no longer actively maintained. It is provided as is, for anyone who may still be using these technologies, with no warranties or claims of accuracy with regard to the most recent product version or service release.

 

Paul Cornell
Microsoft Corporation

January 2002

Applies to:
     Microsoft® Excel 2002

Summary: How to check stock quotes over the Internet using the Web Service References Tool and Microsoft Excel. (4 printed pages)

Contents

Introduction
Running the Solution
Examining the Code
Conclusion

Introduction

The solution described in this article uses the Delayed Stock Quote Web service (http://services.xmethods.net/soap/urn:xmethods-delayed-quotes.wsdl), provided by Xmethods (http://www.xmethods.net), along with the Web Service References Tool to check stock quotes over the Internet using Microsoft Excel.

This solution assumes that you already have the Web Service References Tool installed on your development computer, and that end users' computers have the following software installed:

  1. The Microsoft Soap Type Library (mssoap1.dll), available with one of the following software components:
  2. Microsoft Windows® XP, any edition

Running the Solution

To run this solution:

  1. Open the Stocks.xls file (located in the \samples\Stocks folder in the Office XP Web Services Toolkit) using Microsoft Excel.
  2. Click the Get Stock Quotes button. Notice the stock quotes update in column B.

Examining the Code

Let's examine the code behind this solution.

Three objects are declared:

  • objStocks, which represents the XML Web service
  • sngPrice, which represents the stock quote price
  • objRange, which represents a cell on the active Excel worksheet
Dim objStocks As clsws_netxmethodsservicesst
Dim sngPrice As Single
Dim objRange As Excel.Range

The objStocks object variable is initialized to an instance of the VBA proxy class clsws_netxmethodsservicesst, which was generated by the Web Service References Tool.

Set objStocks = New clsws_netxmethodsservicesst

Cell A2, which contains the first stock symbol, is set as the active cell.

Application.ActiveSheet.Range("A2").Activate

Each cell in column A, starting with cell A2, is checked for the presence of a stock symbol. If there is no stock symbol, the subroutine ends.

Do
    If Application.ActiveCell.Value = "" Then
        Exit Do

Otherwise, the stock symbol is passed as a parameter to the clsws_netxmethodsservicesst class's wsm_getQuote method, which maps to the XML Web service's getQuote method. The wsm_getQuote method passes the stock symbol to the XML Web service, and a stock quote value is returned. This stock quote value is assigned to the sngPrice variable.

    Else
        sngPrice = objStocks.wsm_getQuote _
            (str_symbol:=Application.ActiveCell.Value)

The wsm_getQuote method call corresponds to the following code in the clsws_netxmethodsservicesst class module:

Private sc_netxmethodsservicesst As SoapClient
Private Const c_WSDL_URL As String = _
    "http://services.xmethods.net/soap/urn:xmethods-delayed-
      quotes.wsdl"

Set sc_netxmethodsservicesst = New SoapClient
sc_netxmethodsservicesst.mssoapinit c_WSDL_URL
...
Public Function wsm_getQuote(ByVal str_symbol As String) As Single
...
    wsm_getQuote = sc_netxmethodsservicesst.getQuote(str_symbol)
...
End Function

The stock quote value is placed next to the stock symbol.

        Set objRange = Application.ActiveCell.Offset(ColumnOffset:=1)
        objRange.Value = FormatCurrency(Expression:=sngPrice, _
            NumDigitsAfterDecimal:=2, GroupDigits:=vbTrue)

The code moves to the next stock symbol in column A, makes the stock symbol's cell active, and repeats the process.

        objRange.Activate
        Set objRange = Application.ActiveCell.Offset(RowOffset:=1, _
            ColumnOffset:=-1)
        objRange.Activate
    End If
Loop

Finally, the date and time that the operation occurred is written to the last cell in column A of the spreadsheet.

Application.ActiveCell.Value = "Last ran: " & Now

Conclusion

In this article, you saw how to create a solution that uses the Web Service References Tool and Excel to check stock quotes over the Internet.