Tuesday, April 21, 2009

Creating an Excel Add-In for Market Data - Part 1 - Excel RTD Component

Since I left my previous job, I have lost my access to Bloomberg real-time data, and so now I have a lot of spreadsheets that use the BDP function for real-time lookup of stock prices, fx rates, etc. - and I don't have the Bloomberg plugin to make them work anymore. Rather than going back to the world of manually filling this data in periodically, I decided to see if I could create my own Excel add-in, and make it look and behave just like the Bloomberg one. This way, if I get access to Bloomberg again in the future, I can continue using the same spreadsheets without changing anything.

I've had plenty of experience writing Excel add-ins in the past, in particular with ManagedXLL. Unfortunately, my license for this was limited to my PC at work, and none of that code seems to work at all from my home PC - and presumably it would violate the licensing agreement anyways. It's too bad that there isn't a free or cheap "non-profit" version of ManagedXLL available.

So now we embark on trying to make it work without the use of ManagedXLL. Some of this turns out to be pretty easy, while other parts are more challenging. So I guess that's the most valuable thing about ManagedXLL - it provides a single nice and neat package to do everything you want, without having to piece together multiple languages and technologies.

For our mock-Bloomberg add-in, the first step is to look at the RTD piece. In ManagedXLL this was really easy - you can create a RTD server by just exposing a class with a few attributes. It turns out this alone is still pretty easy to do in plain C# code. We just need a few attributes, and we need to implement the IRtdServer interface from Microsoft.Office.Interop.Excel. The code for this ends up looking pretty similar to what it would have been with ManagedXLL. Finally we need to make sure our .NET dll gets registered - either using the project properties when we compile - or using RegAsm.exe to register it after we've built it.

Here's an abbreviated version of the code for the RTD server, showing the relevant parts...
[ClassInterface(ClassInterfaceType.AutoDual)]
[ComVisible(true), ProgId(PROGID)]
public class BdpRtdServer : Excel.IRtdServer
{
public const string PROGID = "Glaze.Finance.ExcelPlugin.Rtd";
private Excel.IRTDUpdateEvent _rtdUpdateEvent;

public object ConnectData(int topicId, ref Array requestStrings, ref bool getNewValues)
{
string[] fields = requestStrings.Cast().ToArray();
string ticker = fields[0];
string field = fields[1];
// ... add new topic subscription for ticker/field ...
}

public void DisconnectData(int topicID)
{
// ... remove topic subscription and unsubscribe ...
}

public Array RefreshData(ref int topicCount)
{
// ... refresh and notify any topics that have updates ...
}

public int Heartbeat()
{
return 1;
}

public int ServerStart(Excel.IRTDUpdateEvent rtdUpdateEvent)
{
// start background processing
_rtdUpdateEvent = rtdUpdateEvent;
}

public void ServerTerminate()
{
// shut down background processing
}

private void NotifyUpdate()
{
if (_rtdUpdateEvent != null)
_rtdUpdateEvent.UpdateNotify();
}

[ComRegisterFunctionAttribute]
public static void RegisterFunction(Type t)
{
Microsoft.Win32.Registry.ClassesRoot.CreateSubKey("CLSID\\{" + t.GUID.ToString().ToUpper() + "}\\Programmable");
}

[ComUnregisterFunctionAttribute]
public static void UnregisterFunction(Type t)
{
Microsoft.Win32.Registry.ClassesRoot.DeleteSubKey("CLSID\\{" + t.GUID.ToString().ToUpper() + "}\\Programmable");
}
}

The main challenge with the RTD piece then becomes the actual application code, which is naturally a bit more complicated than a simple function because we need to implement all of the real-time server management going on behind the scenes. In my implementation, this involved some background threads waiting for updates and batching them up to notify back to Excel, and some smart dictionary caching to keep track of what we are currently "subscribed" to across all of our client calls.

No comments: