Ralph Arvesen
Vertigo Software, Inc.
November 1999
Summary: This article describes the design and implementation of the Fitch & Mather Stocks 2000 (FMStocks 2000) and Microsoft Office 2000 integration. It also covers use of Office Web Components and Excel as a connected client. (16 printed pages)
Overview
Office Web Components
A Rich Online and Offline Client: Excel
Code Librarian
Summary
About the Author
For More Information
This article discusses how several Office 2000 technologies were integrated into the Fitch & Mather 2000 (FMStocks 2000) application to support new features. FMStocks 2000 includes a new Web page that uses Office Web Components and a remote Excel client that downloads portfolio and historical stock information. The technologies discussed are Office Web Components, Excel VBA code, XML, MSDE, and Code Librarian.
The Office Web components were not tested under the same load as the ASP pages. These pages, and Excel file, were not load tested at all. Office 2000 clients are nothing like Web pages from a testing point of view. They are not created via ASP and they require much less server processing for similar results. The greatest amount of processing for Office 2000 clients is done at the local client machines.
The server only generates XML Objects from ADO Recordsets to stream to the client. This workload is much less than the corresponding ASP pages that also generate HTML for the browser.
The Microsoft Office Web Components are a collection of ActiveX controls that allow you to publish and interact with data on the Web. The collection consists of a spreadsheet, chart, pivot table, and data source components.
The Office Web Components are summarized as follows:
| Component | CLSID | Description |
| Spreadsheet | 0002E510-0000-0000-C000-000000000046 |
Similar to an Excel spreadsheet. Can use as a visible spreadsheet component or hidden as a recalculation engine. |
| Chart | 0002E500-0000-0000-C000-000000000046 |
Similar to an Excel two-dimensional chart. |
| Pivot table | 0002E520-0000-0000-C000-000000000046 |
Similar to PivotTable reports in Excel. Allows user to analyze multidimensional data by sorting, filtering, outlining, and charting. |
| Data source | 0002E530-0000-0000-C000-000000000046 |
Hidden component that manages underlying data connections for other component. |
Each component is fully programmable with interfaces similar to Excel objects. Because the components are ActiveX controls, they can be used in Web pages and other hosting environments such as Visual Basic and Visual C++. They require IE 4.01 or later.
To use the components in your application, make a reference to Msowc.dll, which is located in the folder Program Files\Microsoft Office\Office. The online help file is very useful and provides information on the object model and snippets of code samples. The help file is Msowcvba.chm, located in the folder Program Files\Microsoft Office\Office\1033.
Because Web Components are considered part of Office, users are required to have Office 2000 installed or be licensed to use components. See the links in "For More Information" at the end of this document.
We added the ASP page ChartPortolio.asp to allow interactivity with the portfolio data. This page contains four areas: spreadsheet and chart components, client-side script, XML data island, and HTML elements. The full page is shown in Figure 1.
Figure 1. Chart portfolio page with Office Web Components
Note To view this page you must have the Office Web Components installed, and a license for Microsoft Office 2000.
The spreadsheet component displays the user's portfolio in an Excel-like interface. The chart component is bound to the spreadsheet and displays different views of the portfolio. Client-side script is used to initialize and populate the Web components as well as handle events such as different chart views, bar or pie chart selection, and tracking mouse events.
An XML data island is embedded in the HTML page so the user can work with the data offline. In essence, the Web site takes a snapshot of the user's portfolio, embeds the portfolio content in the page, and sends it down to the client. The data island is shown below. Notice there is one outer <portfolio> block with a <stock> block for each security in the portfolio.
<XML ID=xmlid>
<portfolio>
<stock>
<symbol>MSFT</symbol>
<name>Microsoft Corporation</name>
<qty>5</qty>
<last_price>101.25</last_price>
<avg_price>103.24</avg_price>
</stock>
<stock>
<symbol>SBUX</symbol>
<name>Starbucks Corporation</name>
<qty>10</qty>
<last_price>32.5625</last_price>
<avg_price>33.5575</avg_price>
</stock>
</portfolio>
</XML>
We use the XML object's recordset property to navigate the records. The following script shows how to navigate through the XML recordset and populate the spreadsheet with stock ticker symbols.
‘ loop through portfolio records
xmlid.recordset.moveFirst()
do while not xmlid.recordset.eof
‘ populate cell with ticker symbol
SheetActiveSheet.Cells(row, 1) = xmlid.recordset("symbol")
‘ move to the next row in sheet
row = row + 1
xmlid.recordset.moveNext()
loop
By using an XML Data Island on the client, in Internet Explorer (IE), we can dynamically bind this information with the Office Web Components. This gives us much more flexibility than streaming simple HTML from the server, or from creating a new data structure to pass the data in. XML is a great way to seamlessly transport and encapsulate data through HTML for use by the client or the Web browser. IE then gives us the ability to use the XML Object Model to analyze, access, and use, the data for presentation with the OWCs.
Some of our corporate users wanted to conduct financial research offline; they needed a snapshot of their portfolio and historical data on selected stocks to play out different scenarios and make investment decisions. Excel offers more analytic capabilities than we could hope to integrate into our Web site. One spreadsheet tab lets the user download and view their portfolio, and the other allows them to download historical information.
Although this is a remote client, we wanted to leverage all existing business logic middle-tier components. First we needed an easy and secure way to transfer data from the Web site to the client. We settled on XML as a platform-neutral data transport, as documented in the next section.
Microsoft Active Data Object (ADO) 2.5 introduced new objects that allow an ADODB.Recordset data to be saved to XML files or text streams. These same objects also can convert the XML Object back into a Recordset. These new features allow XML data to pass from our server to our client without using DCOM or losing the flexibility of the Recordset.
How does the data get from the database to the client?
Steps 3, 4, and 5 are important in this process. Server components and clients care only about Recordsets. They don't care how they get the data, as long as they get what they expect.
The next section of code is the ASP page that returns the XML Stream for a given ticker for steps 3 and 4 of the preceding process.
_GetXMLTickerHistory.asp
<%@ Language=VBScript %>
<% Option Explicit %>
<% Response.Buffer = true%>
<OBJECT RUNAT=server PROGID=FMStocks_ext.History id=objHistory> </OBJECT>
<OBJECT RUNAT=server PROGID=ADODB.Stream id=stream> </OBJECT>
<%
dim rs, ticker
ticker = Request("ticker")
rs = objHistory.GetHistory (ticker)
WriteXML_Recordset rs
sub WriteXML_Recordset(rs)
if isobject(rs) then rs.Save stream, 1 'adXMLStream Response.Write stream.ReadText
end sub
%>The Excel VBA code uses a special helper class called WebHelper. WebHelper automates Internet Explorer and retrieves a Web page from the server. It also knows how to post data using a simple multidimensional array.
After the post, it returns the string contents of the tagName requested using the DHTML Object. The WebHelper class is a general component, reused from an earlier, unrelated project. Below is a section of code from the Portfolio Sheet in the workbook.
Note The Function below is not complete. Only part of the Function was taken to show the functionality we are discussing.
'
' Get the portfolio info from the remote server and
' fill in the spreadsheet cells.
'
Sub DownloadPortfolioXML()
'--CODE MISSING--
'The code below is taken out of context for documentation only
' objects to call middle-tier methods using IE
Dim wh As New WebHelper
' this is the recordset that will contain the portfolio
' returned from the remote component
Dim rs As Recordset
Dim xml As String
'this will be the secure post data in the SSL stream
'otherwise we would have to put it on the url
Dim postData
ReDim postData(1, 1)
postData(0, 0) = "email"
postData(1, 0) = txtEmail.text
postData(0, 1) = "password"
postData(1, 1) = txtPassword.text
' get the portfolio
frm.AddStatus ("Connecting to server...")
xml = wh.PostToPage(GetServerName() & _
"/fmstocks/_getXMLPortfolio.asp", _
postData, "xml", 30, False)
'check to make sure we get back XML data
frm.AddStatus ("Retrieving portfolio...")
If xml <> "" Then frm.FinishedStep
frm.FinishedStep
Set rs = XMLtoADOrecordset(xml)
'--CODE MISSING--
'The code above is taken out of context for documentation onlyNote The Virtual Directory and ASP Page names are embedded in this code. If you change the URL for the Web site in any way, note the dependencies here.
Now that the XML Stream has been returned to Excel, we need to convert it back into a Recordset. Following is the XMLtoADORecordset helper function used to do that.
'This function takes an XML String that has been converted from a 'recordset, then it returns the original recordset
Function XMLtoADORecordset(xml As String) As Recordset
' build an ADO recordset from an XML string
On Error Resume Next
Dim rs As New ADODB.Recordset
Dim st As New ADODB.Stream
st.Open
st.WriteText xml
st.Position = 0
rs.Open st
If Err.Number <> 0 Then
MsgBox Err.Description
Exit Function
End If
Set XMLtoADOrecordset = rs
End Function
Note The Excel client must be run with ADO 2.5 (currently installed only with Windows 2000). We use the Stream Object, which is new to ADO 2.5.
The Portfolio tab of the Excel client is shown in Figure 2.
Figure 2. Portfolio spreadsheet tab in Excel
The user gets a snapshot of their portfolio by entering their e-mail address and password and clicking the Download Portfolio button. The VBA code uses the WebHelper class to invoke the VerifyLogin and ListPositions methods on the remote FMStocks_Bus.Account object. It loops through the returned recordset and populates the cells with the portfolio information. The significant portion of the VBA code was shown above to illustrate the use of XML with ADO.
Another feature the corporate users wanted was a report of their portfolio. We decided to use Word as the report generator. The VBA code loops through the portfolio information on the Portfolio tab and automates Word to generate a report as shown in Figure 3.
Figure 3. Word 2000 report generated from Excel
An interesting challenge was displaying charts in Word—Word does not support charts directly but uses Graph 2000. The Excel client also automates Graph 2000 as an embedded OLE object in the Word report.
We added the Historical tab for those savvy corporate users who wanted to evaluate historical information. This tab allows them to download historical information on stocks and view the data at their leisure. The data is stored in a local MSDE database so other tools can easily access it as well. We detect if the database is available and create the database and table if necessary. The Historical tab of the Excel client is shown in Figure 4.
Figure 4. Historical spreadsheet tab in Excel
The Microsoft Data Engine (MSDE) is a new technology that provides local data storage that is compatible with Microsoft SQL Server 7.0. Because the MSDE shares the same data engine as SQL Server, most Microsoft Access projects or client/server applications run on either version unchanged. It does not limit the number of users who can connect but is optimized for five users. It has a 2-gigabyte database size limit and cannot be a replication publisher. You can read more information about installing MSDE at http://support.microsoft.com/support/kb/articles/q218/8/12.asp.
Two additions were added to the server to support historical stock data; a table to hold the data and a middle-tier component to return the data. The table is shown in Figure 5.
Figure 5. New History table to database
To keep with our multi-tier design, we created a new object called FMStocks_Ext.History that runs on the Web server to access the History table. The object has one method that takes in a stock ticker and returns a recordset that contains the historical data for that stock. The code for the GetHistory method is shown below.
Public Function GetHistory(ByVal Ticker As String) As ADODB.Recordset
On Error GoTo errorHandler
' the recordset that will be returned
Dim rs As ADODB.Recordset
' fmstocks database component
Dim db As FMStocks_DB.DBHelper
Set db = New FMStocks_DB.DBHelper
‘call a Stored Procedure to return the Stock history
Set GetHistory = db.RunSPReturnRS("History_ListByTicker", _
mp("Ticker", adVarChar, 12, Ticker))
' done using the fmstocks component
Set db = Nothing
Exit Function
errorHandler:
Set db = Nothing
Set rs = Nothing
' display the error
Err.Raise Err.Number,SetErrSource(g_modName,"History"), Err.Description
End Function
Clicking the Get Data button shows the dialog in Figure 6. It allows you to enter a set of stock tickers to download from the Web site to your computer. The VBA code uses RDS to retrieve recordset for each stock and writes the data to a local MSDE database. If the local database does not exist, it creates one and adds the History table to the database.
In Excel we have created many forms to enable the use of the familiar Windows interface to select and download stocks from our server.
Figure 6. Downloading stocks to local MSDE database
Note The only tickers that have valid historical data are MSFT, T, UBID, IBM, and PIXR.
The complete GetTickers function is shown below. It uses the WebHelper and XML-enhanced ASP pages to get a history recordset for each stock, add it to the local database, and update the progress indicator.
'
' Download the tickers specified in the list and add
' to the local database.
'
Private Sub GetTickers()
On Err GoTo onError
' used to call methods on remote middle-tier components
Dim wh As New WebHelper
' init progress
barProgress.Max = list.ListItems.count + 1
barProgress.Value = 0
ShowProgress True
barProgress.Value = barProgress.Value + 1
DoEvents
' recordset that contains the ticker history data
Dim rs As ADODB.Recordset
Dim xml As String
' number of tickers in list
Dim nCount As Integer
nCount = list.ListItems.count
' how many records were added to local database
Dim nRecordCount As Integer
' loop through each ticker in the list
Dim Item As ListItem
For Each Item In list.ListItems
' make sure we can see the item and highlight it
' while were working with it
Item.EnsureVisible
Item.bold = True
' update the status
Item.SubItems(1) = csRetrieve
DoEvents
' make sure ticker is not in the local database
RemoveStock Item.text
' download the ticker history
xml = wh.PostToPage(GetServerName() & _
"/fmstocks/_getXMLTickerHistory.asp?ticker=" & Item.text, _
returnTag:="xml", InnerOnly:=False)
Set rs = XMLtoADOrecordset(xml)
' add data to local database
nRecordCount = AddRecord(rs, Item)
' update status
If nRecordCount > 0 Then
Item.SubItems(1) = csLocal
Item.SmallIcon = 2
Else
Item.SubItems(1) = csError
Item.SmallIcon = 3
End If
barProgress.Value = barProgress.Value + 1
Item.bold = False
DoEvents
' done with recordset
Set rs = Nothing
Next
onError:
ShowProgress False
End Sub
Note The Virtual Directory and ASP Page names are embedded in this code. If you change the URL for the Web site please note the dependencies here.
Now that some stocks are in the local database, you can view historical data. The Stock ticker combobox contains all of the stocks in the local database and the following code populates this list.
Private Sub PopulateTickerList()
On Error GoTo onError
' first clear the list
comboTicker.Clear
' make sure we have a local database
If gbLocalAvailable = True Then
' the select statement
Dim sql As String
sql = "SELECT DISTINCT Ticker FROM History ORDER BY Ticker"
' get the list of stocks from the local database
Dim rs As New ADODB.Recordset
rs.Open sql, GetConnectionString
' populate the drop list
Do While rs.EOF = False
comboTicker.AddItem UCase(Trim(rs("Ticker")))
rs.MoveNext
Loop
End If
Exit Sub
onError:
ReportError "Error populating the ticker list."
End Sub
The quick dates combo box is for convenience and allows you to quickly select a specific year. Otherwise, you enter the start and end date you want to view in the date fields. Clicking the Update button updates the charts with the specified stock for the dates entered as shown in the following code.
Private Sub ChartStock(sStock As String, sStartDate, sEndDate)
On Error GoTo onError
' create the query string
Dim sql As String
sql = "select * from History where Ticker = '" & _
Trim(sStock) & "' and (Date between '" & _
Trim(sStartDate) & "' and '" & Trim(sEndDate) & _
"') order by Date"
' get the records
Dim rs As New ADODB.Recordset
rs.Open sql, GetConnectionString
' write the records to hidden data tab
Dim nLastRow As Integer
nLastRow = WriteData(rs)
' only continue if actually have data to chart
If nLastRow > 0 Then
With Worksheets(csHistorySheet)
' set the source data for the main chart
.ChartObjects(1).chart.SetSourceData _
Source:=Sheets("Data").range( _
"A1:A" & nLastRow & ",C1:E" & nLastRow)
' adjust the ticker marks based on how many points
' are in the chart (how many dates we are plotting)
If nLastRow >= 6 Then
.ChartObjects(1).chart.Axes(xlCategory). _
TickLabelSpacing = nLastRow / 6
.ChartObjects(1).chart.Axes(xlCategory). _
TickMarkSpacing = nLastRow / 3
Else
.ChartObjects(1).chart.Axes(xlCategory). _
TickLabelSpacing = 1
.ChartObjects(1).chart.Axes(xlCategory). _
TickMarkSpacing = 1
End If
' set the soruce date for the volume chart
.ChartObjects(2).chart.SetSourceData _
Source:=Sheets("Data").range( _
"A1:A" & nLastRow & ",F1:F" & nLastRow)
End With
End If
' set chart data source
' set the chart label
lblTicker.Caption = UCase(Trim(sStock))
Exit Sub
onError:
ReportError "Error charting stock."
End Sub
Code Librarian (shown in Figure 7) is a useful tool shipped with Office 2000 Developer. It allows you to easily share code by storing code snippets in a centralized database (it uses an Access database as the storage). When you have some useful code you want to share, you add it to Code Librarian so others can reuse it. You can browse the code snippets, search by keyword, or perform full text searching.
Figure 7. Code Librarian
Writing the Office 2000 clients was a totally cool experience. We were able to leverage the Office 2000 applications to do some things that we couldn't do with pure HTML, or script. We found that the development environment for Excel was very similar and familiar to Visual Basic. Here are the lessons, or things, to remember from this task.
Ralph Arvesen is a Software Developer at Vertigo Software, Inc. Vertigo Software is a San Francisco Bay area-based consulting firm that specializes in the design and development of Windows DNA applications and components. He can be reached at ralph@vertigosoftware.com or on the Web at http://www.vertigosoftware.com/.
There are some excellent resources on creating Office 2000 applications. I list a few here.