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:
- The Microsoft Soap Type Library (mssoap1.dll), available with one of the following software components:
- The Web Service References Tool (see MSDN Online for more information)
- The SOAP Toolkit 2.0 SP2 Redistributable Files
- Microsoft Windows® XP, any edition
- The Microsoft XML (MSXML) Parser. For information about which Microsoft products ship the MSXML Parser, see the Microsoft Knowledge Base article Q269238, INFO: Version List of the Microsoft XML Parser. Alternatively, you can download the MSXML Parser from the MSDN Online XML Developer Center.
Running the Solution
To run this solution:
- Open the Stocks.xls file (located in the \samples\Stocks folder in the Office XP Web Services Toolkit) using Microsoft Excel.
- 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.