Getting realtime data into Excel

Starting with Excel 2002, Microsoft have included the RTD interface which allows Excel to poll for data. Polling seems to be a much more efficient way of getting data into Excel than the classic approach which is pushing – it’s not uncommon to find yourself pushing when Excel isn’t ready which results in lockups, inconstitencies and other undesirable effects.

The RTD interface is small (6 methods) and Kenny Kerr has a great series of articles about RTD on his blog so I won’t repeat that information here and instead concentrate on integration to StreamLink.NET and pulling in real time data from a Caplin platform deployment.

The following walkthrough is a short example of how to do this, there’s no error checking, logging and certainly the example is nowhere near production quality, however it does demonstrate that linking up Excel and data from the Liberator is straightforward.

ServerStart is the entry point and in here we can instantiate StreamLink.NET and setup a map between the RTD topic ids and the Subject/Field information.

public int ServerStart(IRTDUpdateEvent callback) {
    sl = new StreamLinkInterop();
    m_callback = callback;
    m_timer = new Timer();
    m_timer.Tick += new EventHandler(TimerEventHandler);
    m_timer.Interval = 1000;
    m_topics = new Dictionary<int, KeyValuePair<string,string>>();
    return 1;
}

The ConnectData method validates the arguments passed through and then attempts to subscribe to the data:

public object ConnectData(int topicId, ref Array strings, ref bool newValues) {
    if (2 != strings.Length) {
        return "Subject and field is required";
    }
    string subject = strings.GetValue(0).ToString();
    string field = strings.GetValue(1).ToString();
    m_topics[topicId] = new KeyValuePair(subject, field);
    m_timer.Start();
    sl.Subscribe(subject, field);
    return "NO_DATA_YET";
}

And DisconnectData will unsubscribe:

public void DisconnectData(int topicId) {
    KeyValuePair pair = m_topics[topicId];
    sl.Unsubscribe(pair.Key, pair.Value);
    m_topics.Remove(topicId);
}

RefreshData will retrieve the current image for the subscribed data:

public Array RefreshData(ref int topicCount) {
    object[,] data = new object[2, m_topics.Count];
    int index = 0;
    foreach (int topicId in m_topics.Keys) {
        data[0, index] = topicId;
        KeyValuePair pair = m_topics[topicId];
        data[1, index] = sl.GetData(pair.Key, pair.Value);
        ++index;
    }
    topicCount = m_topics.Count;
    m_timer.Start();
    return data;
}

Because Excel polls for the data we’ve got to have a cache within our RTD server so that data is available when Excel calls in, for the purposes of this exercise we’re only considering Record data without market depth or history so our cache can be really simple:

The only missing part is the StreamLinkInterop class which integrates with StreamLink, this needs to implement the public methods we saw earlier: Subscribe, Unsubscribe and GetData as well as the IRecordSubscriptionListener interface from StreamLink.NET since we want to receive record updates. When an update is received we put it into our cache and return immediately.

There’s a couple of maps which allows finding the RecordType1Cache object from both the StreamLink side (using the IRecordSubscription as the key) and from the RTD side (using a subscription name as the key).

The connection to the server is setup in the constructor for code simplification purposes. The servername is hard coded so you’ll probably need to change it or find a way to configure it if you use this code as a base for your own project.

public class StreamLinkInterop : IRecordSubscriptionListener
    {
         StreamLink myStreamLink;
         Dictionary cache = new Dictionary();
         Dictionary subscriptionLookup = new Dictionary();
         public StreamLinkInterop()
         {
             IStreamLinkConfiguration config = SimpleConfiguration.CreateType2Connection("liberator", 8080);
             myStreamLink = new StreamLink(config);
             myStreamLink.CredentialsProvider = new PasswordCredentialsProvider("admin", "admin");
             myStreamLink.StreamLinkProvider.Connect();
         }
         public void Terminate()
         {
             myStreamLink.StreamLinkProvider.Disconnect();
         }
         public void Subscribe(string subject, string field)
         {
             string name = subject + ";" + field;
             IRecordSubscriptionParameters rparams = myStreamLink.StreamLinkProvider.ParametersFactory.CreateRecordSubscriptionParameters(new string[] { field } );
             ISubscription sub = myStreamLink.StreamLinkProvider.CreateRecordSubscription(this, subject , rparams);
             cache[sub] = new RecordType1Cache();
             subscriptionLookup[name] = sub;
             sub.Subscribe();
         }
         public void Unsubscribe(string subject, string field)
         {
             string name = subject + ";" + field;
             if (subscriptionLookup.ContainsKey(name))
             {
                 ISubscription sub = subscriptionLookup[name];
                 sub.Unsubscribe();
                 cache.Remove(sub);
                 subscriptionLookup.Remove(name);
             }
         }
        public string GetData(string subject, string field)
         {
             string name = subject + ";" + field;
             if (subscriptionLookup.ContainsKey(name))
             {
                 ISubscription sub = subscriptionLookup[name];
                 return cache[sub].GetFieldValue(field);
             }
             return "UNKNOWN+SUBJECT";
         }
         #region IRecordSubscriptionListener Members
         public void RecordType2Updated(Caplin.StreamLink.Subscription.ISubscription subscription, IRecordType2Event ev)
         {
         }
         public void RecordType3Updated(Caplin.StreamLink.Subscription.ISubscription subscription, IRecordType3Event ev)
         {
         }
         public void RecordUpdated(Caplin.StreamLink.Subscription.ISubscription subscription, IRecordEvent ev)
         {
             if (cache.ContainsKey(subscription))
             {
                 cache[subscription].Update(ev);
             }
         }
         #endregion
         #region ISubscriptionListener Members
         public void SubscriptionErrorReceived(Caplin.StreamLink.Subscription.ISubscription subscription, Caplin.StreamLink.Subscription.ISubscriptionErrorEvent ev)
         {
         }
         public void SubscriptionStatusUpdated(Caplin.StreamLink.Subscription.ISubscription subscription, Caplin.StreamLink.Subscription.ISubscriptionStatusEvent ev)
         {
         }
         #endregion
     }

To pull data into excel into excel use a formula along the following lines: =RTD("com.caplin.excel",,"/I/MSFT.O", "BID"). The data in the cell will update periodically (by default every 2000ms). This period can be changed using either a registry setting or using VBA, details can be found at MSDN.