One of the new features introduced in the Streams 4.0  is Streams for Microsoft Excel. This feature allows an Excel user to quickly and easily identify and access streaming data, to enable analysis and visualization on continually updating data with the full power of Excel.  Streams for Excel is an Excel add-in which uses the Excel Real Time Data (RTD) functionality to make Streaming data available in Excel. Getting streaming data in Excel is as easy as 1-2-3:
  1. In the application SPL code, annotate the streams that you want to make available in Excel.
  2. In Excel, use the Streams for Excel custom task pane contributed by the Streams for Excel add-in to get the list of (annotated) streams.
  3. Drag and drop the streams into an Excel worksheet to get streaming data.
 

Installing the Streams for Excel add-in

The Streams for Excel add-in supports both the 32-bit and 64-bit versions of Excel 2010 and Excel 2013 on Windows 7 64-bit. The installers for the Streams for Excel add-in are included with the Streams product in:
<product_installation_directory>/etc/Excel
It is important to check the version of Microsoft Excel that you are using. Even if you run a 64-bit Windows operating system, you might have a 32-bit version of Microsoft Excel. You can download the installer in one of the following ways:
  1. Use the Streams Console
    • Click on the Downloads link and select Download Streams for Excel Add-in downloadbutton
  2. File transfer
Once downloaded simply unzip and run setup.exe as an administrator on Windows. The installation process may also prompt you to install the following two prerequisites if they are not already installed on your machine:
  • Microsoft .Net Framework version 4.0
  • Visual Studio Tools for Office (VSTO) Runtime
Once Streams for Excel is installed, start Excel and you should see the Streams for Excel custom task pane contributed by the add-in. Now we need to make some streams in our application accessible from Excel.

Annotating the stream that you want to see in Excel

In Streams 4.0, a new SPL @view annotation was introduced. The @view annotation allows you to identify an output stream that you want to create a view for. The resulting view makes the data from that stream available in Excel. Here is an example of the @view annotation defined on an operator:
@view(name = "MyView", port = MyOutputStream, sampleSize = 50, bufferSize = 500, description = "Stream containing some data that I want to use in Excel", activateOption = firstAccess)
stream<int32 i> MyOutputStream = Functor(SomeStream) { ... }
To add a view using the Graphical Editor in Streams Studio: AddView   CreateViewDialog When this application is submitted, a view by the name of MyView will be created that buffers data for the stream MyOutputStream. The view will have a buffer capable of holding 500 tuples and 50 tuples will be captured (or sampled) per second. Here is the complete list of parameters you can specify for a view annotation:
  • name – name of the view. This is the name that will be displayed in the Streams for Excel task pane in Excel.
  • port – name of the output stream (or the stream alias)
  • sampleSize – the number of tuples per second to sample
  • buffer option – specify one of
    • bufferSize – number of tuples the view buffer can hold
    • bufferTime – number of seconds the tuples stay in the buffer
  • description – view description. The description will be visible in Excel.
  • attributes – list of attributes that should be included in the view. The default is all attributes.
  • filter – an attribute-regular expression pair to filter the tuples included in the view
  • activeOption – specifies when the view should start buffering data — when the job starts (automatic) or upon first access (firstAccess). The default is firstAccess.
When you submit an application with @view annotations, views are created for the annotated streams. You can then see these views/streams in the Streams for Excel task pane in Excel. The attached sample application has @view annotations to make some of the streams available. Build and launch the application to try it out.

Working with streaming data in Excel

The Streams for Excel custom task pane lists the streams that were made available by the @view annotation in your SPL code. Use the log-in panel to log in to a Streams domain. Once you are logged in, you should see the list of available streams. Excel-Login In the log-in panel, specify the following information:
  • Host name or IP address* – host name or IP address of the host where Streams is running.
  • Port number* – the port number used by the Streams Web Service (SWS)
  • User name – user name
  • Password – password
  • Use certificate authentication – select this option if the domain requires a client certificate for authentication. You will be prompted to select an existing certificate to use.
*Tip – this is the same host name (or IP address) and port number used by the Streams Console. The streamtool geturl command returns the Streams Console URL which contains this information. Once authenticated, you should see a list of streams that are available. Expanding the stream shows the attributes for that stream. StreamsForExcel_CustomTaskPane Drag and drop a stream (or an attribute) to the worksheet. By default, 20 rows of data is dropped into the worksheet. If you drag a stream, all the attributes for that stream are dragged onto the worksheet. Each attribute will occupy a column. If you inspect the formula for each cell, you will notice that it uses an Excel RTD formula that identifies the Streams for Excel RTD server as well as the Streams data source details including the Streams instance, job, view and attribute:
RTD("streams.rtdserver", "", "", "<streams_instance_name>", "<streams_job_name>", "<view_name>", <data_buffer_row_index>, "<stream_attribute_name>")
Once you drop a stream or some attributes into the worksheet, the Streams for Excel add-in starts getting data from the Streams runtime. The default data sampling rate is 500 milliseconds. You can change this in the Settings panel. If you have a slow flowing stream, then you may not need to sample the data as often. As the add-in receives new data from the Streams runtime, the worksheet is continuously updated to show the new data. You can now work with the streaming data in the worksheet as you would any other data. If you plot the streaming data on a chart, the chart updates automatically as the streaming data updates. Likewise, if you reference the streaming data in a formula, the result of the calculation updates automatically as the streaming data updates. BargainIndex In the Streams for Excel task pane, hover the mouse cursor over a stream and then hover over the i to get more information on the stream. You can also click on the blue star icon emptyStar_16x to mark a stream or an attribute as a favorite. Items marked as favorite are listed in the Favorites list. StreamsForExcel_InfoPane You can save the workbook that contains streaming data and share the workbook with other users. When the workbook is reopened, the streaming data continues to update once you log in to the Streams domain using the Streams for Excel custom task pane — of course, assuming that the Streams domain that you log in to contains the instances, the jobs, the views and the attributes referenced by the RTD formulas in the workbook. One of the new features in Streams 4.0 is also the ability to specify a job name when you submit your application. This feature ties in very well with the Streams for Excel feature in that if you have to resubmit an application that is referenced in an Excel worksheet, then you don’t have to update the worksheet as long as the instance name, the job name and the view name referenced in the RTD formulas remain the same.

Try it out!

The Streams product ships with a number of sample applications that can get you started quickly. The example I used in this post (as seen in the screen shots) is the Vwap sample application. Import the sample into Streams Studio and modify it slightly to simulate continuous data flow. For example, insert a Beacon operator to repeatedly read from the input data file. I also removed the monitoredTickers parameter to allow all stock tickers to be processed. And don’t forget to add @view annotations on the streams that you want to see in Excel. Here is the sample code with my modifications:
// begin_generated_IBM_copyright_prolog                             
//                                                                  
// This is an automatically generated copyright prolog.             
// After initializing,  DO NOT MODIFY OR MOVE                       
// **************************************************************** 
// THIS SAMPLE CODE IS PROVIDED ON AN "AS IS" BASIS. IBM MAKES NO   
// REPRESENTATIONS OR WARRANTIES, EXPRESS OR IMPLIED, CONCERNING    
// USE OF THE SAMPLE CODE, OR THE COMPLETENESS OR ACCURACY OF THE   
// SAMPLE CODE. IBM DOES NOT WARRANT UNINTERRUPTED OR ERROR-FREE    
// OPERATION OF THIS SAMPLE CODE. IBM IS NOT RESPONSIBLE FOR THE    
// RESULTS OBTAINED FROM THE USE OF THE SAMPLE CODE OR ANY PORTION  
// OF THIS SAMPLE CODE.                                             
//                                                                  
// LIMITATION OF LIABILITY. IN NO EVENT WILL IBM BE LIABLE TO ANY   
// PARTY FOR ANY DIRECT, INDIRECT, SPECIAL OR OTHER CONSEQUENTIAL   
// DAMAGES FOR ANY USE OF THIS SAMPLE CODE, THE USE OF CODE FROM    
// THIS [ SAMPLE PACKAGE,] INCLUDING, WITHOUT LIMITATION, ANY LOST  
// PROFITS, BUSINESS INTERRUPTION, LOSS OF PROGRAMS OR OTHER DATA   
// ON YOUR INFORMATION HANDLING SYSTEM OR OTHERWISE.                
//                                                                  
// (C) Copyright IBM Corp. 2010, 2012  All Rights reserved.         
//                                                                  
// end_generated_IBM_copyright_prolog                               

/** Computes the volume-weighted average price (vwap) for a stream of stock 
transactions. Given trades and quotes, this application produces a *bargain index* 
for the last 4 transaction related to a particular stock.  The bargain index 
identifies possible bargains, which occur when the volume-weighted average price 
for recent trades exceeds the current asking price.

This algorithm may be used to identify opportunities to pick up well priced shares 
in the stream of current transaction data.

The application gets input from a data file containing a snapshot of transaction 
data. The data file is read repeatedly to simulate continuous data flow. 
*/
namespace sample ;

/** Returns true if the transaction type is a quote, and false otherwise.
@param ttype transaction type
@return true if the transaction is a quote and false otherwise.
*/
boolean isQuote(rstring ttype)
{
    return ttype == "Quote" ;
}

/** Returns true if the transaction type is a trade, and false otherwise.
@param ttype transaction type
@return true if the transaction is a trade and false otherwise.
*/
boolean isTrade(rstring ttype)
{
    return ttype == "Trade" ;
}

/** Given an input file containing trades and quotes, this composite operator calculates 
the VWAP and bargain index for the last 4 trades. The trades included in the VWAP 
computation are grouped by the Aggregate operator, which uses a sliding window to sum the 
weighted price and volume for the 4 most recent transactions.

The bargain index identifies the magnitude of the bargain, where a greater values implies 
a better bargain. A value of 0 indicates that the VWAP is not greater than the asking price, 
and is therefore not a bargain.
*/
composite Vwap
{    
    type
        TQRecT = rstring ticker, rstring date, rstring time, int32 gmtOffset,
            rstring ttype, rstring exCntrbID, decimal64 price, decimal64 volume,
            decimal64 volp, rstring buyerID, decimal64 bidprice, decimal64 bidsize,
            int32 numbuyers, rstring sellerID, decimal64 askprice, decimal64 asksize,
            int32 numsellers, rstring qualifiers, int32 seqno, rstring exchtime,
            decimal64 blockTrd, decimal64 floorTrd, decimal64 PEratio, decimal64 yield,
            decimal64 newprice, decimal64 newvol, int32 newseqno, decimal64 bidimpvol,
            decimal64 askimpcol, decimal64 impvol ;
        TradeInfoT = decimal64 price, decimal64 volume ;
        QuoteInfoT = decimal64 bidprice, decimal64 askprice, decimal64 asksize ;
        TradeQuoteT = TradeInfoT, QuoteInfoT, tuple<rstring ticker, rstring date,
            rstring time, rstring ttype> ;
        TradeFilterT = TradeInfoT, tuple<timestamp ts, rstring ticker> ;
        QuoteFilterT = QuoteInfoT, tuple<timestamp ts, rstring ticker> ;
        VwapT = rstring ticker, decimal64 minprice, decimal64 maxprice,
            decimal64 avgprice, decimal64 vwap ;
        BargainIndexT = rstring ticker, decimal64 vwap, decimal64 askprice,
            decimal64 asksize, rstring ts, decimal64 index ;
    graph
    
        stream<rstring filename> InputFileName = Beacon()
        {
            param
                period : 1.5 ;
            output
                InputFileName : filename = "TradesAndQuotes.csv.gz" ;
        }
        
        @view(name = "TradeQuote", port = TradeQuote, sampleSize = 1000, bufferSize = 1000, 
             description = "Stream containing all trade and quote type transactions")
        stream<TQRecT> TradeQuote = FileSource(InputFileName)
        {
            param
                format : csv ;
                compression : gzip ;
        }

        @view(name = "Trades", port = TradeFilter, sampleSize = 1000, bufferSize = 1000,
            description = "Stream containing only trade type transactions")
        stream<TradeFilterT> TradeFilter = Functor(TradeQuote as inPort0Alias)
        {
            param
                filter : isTrade(ttype);
            output
                TradeFilter : ts = timeStringToTimestamp(date, time, false) ;
        }

        @view(name = "Quotes", port = QuoteFilter, sampleSize = 1000, bufferSize = 1000,
            description = "Stream containing only quote type transactions")
        stream<QuoteFilterT> QuoteFilter = Functor(TradeQuote)
        {
            param
                filter : isQuote(ttype);
            output
                QuoteFilter : ts = timeStringToTimestamp(date, time, false) ;
        }

        stream<VwapT, tuple<decimal64 sumvolume>> PreVwap = Aggregate(TradeFilter)
        {
            window
                TradeFilter : sliding, count(4), count(1), partitioned ;
            param
                partitionBy : ticker ;
            output
                PreVwap : ticker = Any(ticker), vwap = Sum(price * volume), minprice =
                    Min(price), maxprice = Max(price), avgprice = Average(price), sumvolume =
                    Sum(volume) ;
        }

        @view(name = "Vwap", port = Vwap, sampleSize = 500, bufferSize = 500,
            description = "Stream containing calculated volume weighted average price")
        stream<VwapT> Vwap = Functor(PreVwap)
        {
            output
                Vwap : vwap = vwap / sumvolume ;
        }

        @view(name = "Bargain", port = BargainIndex, sampleSize = 500, bufferSize = 500,
            description = "Stream containing bargain index")
        stream<BargainIndexT> BargainIndex = Join(Vwap as V ; QuoteFilter as Q)
        {
            window
                V : sliding, count(1), partitioned ;
                Q : sliding, count(0) ;
            param
                partitionByLHS : V.ticker ;
                equalityLHS : V.ticker ;
                equalityRHS : Q.ticker ;
            output
                BargainIndex : index = vwap > askprice ? asksize * exp(vwap - askprice) :
                    0d, ts =(rstring) ctime(ts) ;
        }        

        () as Custom_8 = Custom(BargainIndex)
        {
        }

}
 

Join The Discussion