Streaming realtime data to Excel

• Download RTDServer.zip - 15.8 KB

Introduction

In business, Excel is loved my (almost) all because of the way it let's end users get things done. It does have it's drawbacks though, one of them being that it a relative standalone application. This project suggest a way to connect Excel to a server so it can receive real time updates from that server. This can be used for the canonical stock ticker apps, but there are many more applications. Although I can't honestly call it simple, it sure is a lot easier than writing the server and RTD classes in c++.

Background

This project combines a few technologies to come up with the result:

• For communication, it uses Duplex WCF that let's the server send data to the client.
• On the client (Excel), it uses an Add-In that was written in C# and then wrapped in Excel-DNA to use it in Excel.
• Inside the the Add-In, it relies on Rx to expose the data points that are sent over WCF calls as a stream of values that Excel uses to update a cell.

Using the code

To see it in action, do the following:

1. Rebuild the total RTDExcelAddIn solution. This will bring in all the NuGet packages and makes sure the latest version of the AddIn gets packaged with Excel-DNA.
2. Press F5 to start the Server (this should be the default startup project). Alternatively you can right-click on the RTDServer project and select Debug > Launch new instance.
3. Navigate to ...\Projects\RTDExcel\RealTimeData\bin\Release (or Debug) and double-click RealTimeData-AddIn-packed.xll. This will launch Excel with the Add-In loaded.
4. With Excel open, open a new Workbook by hitting CTRL-N and type =GetValues() in any cell to see the updates flow in. You can also find the function in the Insert Function dialog.

How it works

Let's begin with a high level overview of all the parts involved, then we will detail these all in separate paragraphs.

The diagram below shows the basic building blocks of the application.

Beginning on the left we have the server. This is a C# Console application that hosts the RTDServer class. This exposes a WCF Endpoint based on the IServer interface that we will discuss later.

On the right we have the Client Add-In that is also written in C#, it hosts the IClient Endpoint. The two communicate through Duplex WCF. When the client calls Register on the server, the server will open and store a callback channel to the client that will allow it to send data as soon as it becomes available (as opposed to the client polling the server for new data).

The orange block represents Excel-DNA, a library that allows you to write a Class Library in C# and then use it in Excel as an Add-In. It provides the boilerplate glue code that will allow your C# code to be called very efficiently by Excel.

Excel-DNA leverages the Real Time Data (RTD) functionality in Excel to notify it of changes, and uses Rx to propagate these changes. Rx is a natural fit here because we actually want to react to this stream of values coming in.

In the following paragraphs I will highlight the interesting bits in each project.

RTD Server

The RTDServer should be reasonably familiar if you have done WCF before. It's sole purpose is to receive and honor incoming requests for connections and send a random number to all clients every 100 milliseconds.

The project contains the two interfaces that are used to implement the Server and Client endpoints and implements the IRTDServer interface in the RTDServer class:

 [ServiceBehavior(InstanceContextMode = InstanceContextMode.Single)]
    class RTDServer : IRTDServer {
... 
}

Please note that the InstanceContextMode is set to single. Because we want the server to not just respond to requests but actively send out random numbers every 100ms, we can't have WCF new up a new RTDServer for every incoming call as it would do normally, instead we want WCF to host a singleton instance that it keeps alive with the timer and connections inside.

There are two interesting methods here, Register creates a callback channel to the client that called it and the SendData EventHandler that responds to the timer.Elapsed event to send a new random number to all the clients.

// for clients to make themselves known
public void Register() {
    Console.WriteLine("We have a guest...");
    var c = OperationContext.Current.GetCallbackChannel<IRTDClient>();
    if (!clients.Contains(c)) 
        clients.Add(c);
}

The client will call the Register method on the IRTDServer interface to setup the connection to the server. In this method the server goes through the OperationContext class to get a CalBakcChannel that it stores in a List<IRTDClient> called clients.

Every time another 100ms is elapsed, a timer will raise the Elapsed event that is handled by SendData. This generates a new random number and goes over all the clients in the List and send them the new value. This seems unimportant but just imagine that this is the new price for a stock that you're heavily invested in. Then you'd want to know about it too.

void SendData(object sender, ElapsedEventArgs e) {
            
    // create random number
    var next = rnd.NextDouble();
    Console.WriteLine("Sending " + next);
            
    // send number to clients
    int ix = 0;
    while (ix < clients.Count) {// can't do foreach because we want to remove dead ones
        var c = clients[ix];
        try { 
            c.SendValue(next);
            ix++;
        }
        catch (Exception e2) { Unregister(c); }
    } 
}

The reason I don't just go over all the clients with foreach is that clients may fault or disappear without letting us know. If this is the case we'll get an error and we want to remove it from the list. Can't do that from within a foreach that's why we use this approach.

RTD Excel Add-In

This is where things get a little more interesting. To begin, the ExcelAddIn project depends on 2 NuGet packages: Excel-DNA and Rx.

The Excel-DNA package lets us create a standard .Net (can be any of the languages) Class Library and leta the methods be called by Excel very efficiently. Yes that's right, you can build very advanced calculations in C# and use them in Excel. This will get you code that is orders of magnitude faster than doing the same calculation in VBA and it brings in of all the goodness of .Net into Excel, as we will happily exploit in this project.

In this project it is actually pretty transparent, we give our method an ExcelFunction attribute and Excel-DNA takes care of the rest automagically. The only thing that requires a little more effort is setting up the Real Time Data through Rx.

Rx and Excel-DNA

Ok. After going over the basics of pushing random numbers to the clients, things are getting interesting. What we will do is convert the method calls to events, convert the events to a stream of events and then connect Excel to that stream.

The first step is to create an EventHandler and then call that every time the SendData method is called from the client:

// event boilerplate stuff
public delegate void ValueSentHandler(ValueSentEventArgs args);
public static event ValueSentHandler OnValueSent;
public class ValueSentEventArgs : EventArgs {
    public double Value { get; private set; }
    public ValueSentEventArgs(double Value) {
        this.Value = Value;
    }
}
// this gets called by the server if there is a new value
public void SendValue(double x) {
    // invert method call from WCF into event for Rx
    if (OnValueSent != null)
        OnValueSent(new ValueSentEventArgs(x));
}

The next step is to convert these separate events into a stream of events, a so called Observable. This is done with a function that takes an observer as an argument and connects that to the OnDataSent event we declared above:

static Func<IObserver<double>, IDisposable> Event2Observable = observer => {
    OnValueSent += d => observer.OnNext(d.Value);   
    return Disposable.Empty;                        
};

Here's how it works. Func<IObserver<double>, IDisposable> means that this is a function delegate that takes an IObserver<double> and returns an IDisposable. The IDisposable is returned so that after the sequence is completed, this can be called to clean up whatever needs cleaing up. Since we're going to continue forever, we'll return Disposable.Empty.

Inside there is one other thing it does: OnValueSent += d => observer.OnNext(d.Value); This is the interesting bit, every time the DataSent event is raised, it will call OnNext on the observer. This is how the individual event are chained together into a stream.

The final mile is performed here, where this the function above is passed to Excel-DNA:

 [ExcelFunction("Gets realtime values from server")]
public static object GetValues() {
            
    // a delegate that creates an observable over Event2Observable
    Func<IObservable<double>> f2 = () => Observable.Create<double>(Event2Observable);
    //  pass that to Excel wrapper   
    return RxExcel.Observe("GetValues", null, f2);          
}

The RxExcel.Observe method needs a Func<IObservable<T>>to perform it's RTD magic, so that's what we'll give it. The first statement creates the Func that returns an Observable created with the Event2Observable delegate.

The final step is to pass that into RxExcel.Observe, together with the function name that was called to create it. This is used by Excel-DNA to setup the connection to the cell that called the function.

Points of Interest

This project was intended to be minimal, with just the prototype to make the idea work and nothing to distract from the essence.

In the end I was positively surprised with the amount of code it took to string all of this together, think I can swap the events with plain delegates since we're not exposing anything outside of the Add-In class. I'll keep that for a next iteration of this article.

Please let me know if it helped you. If you have any questions you can of course leave then in the comments. And, if you liked it, please don't forget to rate it.

History

October 02, 2013: initial upload