The Gateway to Algorithmic and Automated Trading

Spreadsheet Shortcut

Published in Automated Trader Magazine Issue 26 Q3 2012

Connecting spreadsheets to other applications in real time is an area where things have improved dramatically in recent years. Nevertheless, it's still a task that diverts time and resources from developing the trading strategies that actually capture alpha. Which is a masterly understatement that prompted us to take a look at KaiTrade's K2RTDKit...

High frequency trading inevitably tends to grab many of the headlines these days, which might lead you to believe that the humble spreadsheet is by implication no longer relevant for automated trading. Far from it. Plenty of Automated Trader readers use spreadsheets - and not just as impromptu trade blotters or containers for derivative pricing models. Where the success of your automated trading doesn't depend on shaving the last microsecond, spreadsheets can provide a simple and flexible platform for automated trading.

Features such as conditional formatting make building a simple GUI for real time trade management trivial. However, the snag is that if you don't have any real time data populating your spreadsheet, your automated trading career is likely to prove decidedly uneventful. By the same token, while there are plenty of analytical add-ins available for spreadsheets in addition to any built-in functions, it can often still be more efficient to offload any heavyweight or proprietary number crunching to a third party application. As with raw real time data, the result of that number crunching somehow needs to find its way into a spreadsheet.

Which begs the question: how? Microsoft's replacement of DDE with RTD technology some ten years back triggered a fair few whoops of joy among Excel™ users in the trading community (trying to trade off a data stream where random items go AWOL for no apparent reason is not much fun). However, while RTD is a massive improvement on DDE, some lucky soul still has to write the RTD server in the first place...

Off the shelf alternative

All of which brings us to KaiTrade and its K2RTDKit, which enables any .NET application to exchange data in real time with Microsoft Excel. The basic idea is that rather than building your own RTD server from scratch, you can simply buy K2RTDKit instead, leaving you more time free to focus on the alpha capture.

On that point, the coding required to invoke K2RTDKit is claimed to be minimal, thereby raising the possibility of getting your implementation up and running with literally a few lines of code. Furthermore, the deliberately generic nature of the technology also raises the prospect of adding further applications to the mix at minimal effort - especially in comparison with the alternative of writing your own generic RTD server from scratch, or writing multiple application-specific RTD servers.

MATLAB Excel

In addition to the generic K2RTDKit, KaiTrade also supplied us with the various bits and bobs for connecting MATLAB and Excel. There are of course a variety of other ways to connect MATLAB and Excel for bilateral communication, which we explored earlier this year (see "Mashup!" in Automated Trader Q1 2012). However, as we hadn't previously taken a look at MATLAB's support for .NET, we still felt that checking out the K2RTDKit implementation would be well worth the effort. We started out by following the very simple example provided with K2RTDKit.

To get this up and running you need unzip the eventRequestHandler.m function (or create it from the code provided - see below) and add it to the MATLAB path.

function eventRequestHandler(source,arg)
disp(arg.rtdTopicID)
topicID = arg.parameters(2);
reqType = arg.parameters(3);
subject = arg.parameters(4);
headerName = arg.parameters(5);
disp(topicID)
disp(reqType)
disp(subject)
disp(headerName)
reqType =char(reqType);
disp(reqType)
if strcmp(reqType,'PX')
    source.Subscribe(subject, headerName,
arg.rtdTopicID, arg.accessID);
elseif strcmp(reqType,'WPUB')
    dataValue = arg.parameters(6);
    disp(dataValue)
end
end

Then you need to load the K2RTDKit assembly into MATLAB as follows:

try
    NET.addAssembly('C:UsersUserDownloadsKaiTradeK2RTDHandlerTestbuildbinK2RTDServerKit.dll')
catch e
    e.message 
        if(isa(e,'NET.NetException')) 
        e.ExceptionObject
    end
end

If all goes well and you have remembered to put the correct path to the K2RTDKit DLL in line two of the code above (which of course we didn't at first) you should receive the following output at the MATLAB command prompt:

ans =
  NET.Assembly handle
  Package: NET
Properties for class NET.Assembly:
    AssemblyHandle
    Classes
    Structures
    Enums
    GenericTypes
    Interfaces
    Delegates

Next step is to create an instance of the K2RTDKit Factory (Windows Firewall will probably throw a wobbly at this - it did for us - and will need reassuring):

    classObj = K2RTDServerKit.Factory.Instance()

Then create an empty server object:

    cloRTDSvr = classObj.GetRTDServer('')

Then start the server up:

    cloRTDSvr.StartHandler('')

...and finally add a listener to receive events:

addListener(cloRTDSvr, 'RTDRequestEvent',
@eventRequestHandler);

Whenever anything actually happens, the eventRequestHandler.m function (mentioned earlier) will be called to process it via the @eventRequestHandler function handle. For example, if you open the sample spreadsheet provided by KaiTrade, which includes a number of cells containing RTD functions with the "WPUB" topic 1 , then the data in those cells are automatically pushed back into the MATLAB command window (see fragment of command window in Figure 1 and corresponding cell in Excel in Figure 2).

Figure 1

Figure 1

Figure 2

Figure 2

Moving in the opposite direction - so that something in MATLAB appears in Excel - involves using the publish method of the cloRTDSvr object (created earlier) as follows:

    cloRTDSvr.Publish('HPQ', 'ASKPX', '5555')

...with the result as follows in Figure 3.

Figure 3

Figure 3

The approach for publishing an array of data is broadly the same:

rng('default')
excelData = rand(1,5)
cloRTDSvr.Publish('ITEST',
excelData)

excelData =
    0.8147    0.9058    0.1270
0.9134    0.6324

...with the resulting Excel output as shown in Figure 4.

Figure 4

Figure 4

(Un)real world

Needless to say, at this point the temptation to try and break something proved irresistible to the Wrecking Crew, who immediately started plotting the various ways they could lock up both Excel and MATLAB by initiating a completely ridiculous rate of message traffic between the two applications. However, we immediately hit a hiccup. As you might expect, we began by creating a pointless large matrix of random numbers and tried to publish it via K2RTDKit from MATLAB into Excel, but it wasn't interested:

Figure 5

Figure 5

After hacking around in the rough for a bit trying various permutations of commands, we admitted defeat and called up KaiTrade. Turns out we weren't being as dopey as we thought - apparently K2RTDKit doesn't as yet support the transmission of MATLAB matrices, only vectors. We did toy with the idea of writing a loop that would produce a 1 x 30,000 vector on each iteration and dynamically naming each vector, but having already skilfully generated Figure 6 with an earlier attempt to produce a 100,000 x 100,000 matrix, we decided to play the sensible card for once and settled for just ten 1 x 5000 vectors.

Figure 6

Figure 6

We also introduced a random delay so that instead of firing over ten complete vectors we sent random points from each vector and intermittently sent them over as "sub-vectors". This not very convincing attempt to emulate a real time feed was rather less than high performance - until we remembered that we had left Excel's Application.RTD.ThrottleInterval value at the default of 2000 milliseconds. We reset this to zero, so Excel would check for updates at every opportunity, and performance picked up sharply.

Next step was try shunting larger blocks of data back into MATLAB at reasonably high data rates. We did this by taking the random numbers being sent to Excel, multiplying them by a number and sending them back (see Figures 7a and 7b), so once the whole process was initiated in MATLAB we would have an iterative two way data flow between the two applications until the original loop in MATLAB completed.

Figure 7a

Figure 7a

Figure 7b

Figure 7b
Figure 8
Figure 8

This worked fine, although when it came to handling returning data in MATLAB, we were reminded of the importance of using proper subject and header names in our spreadsheet and including them in the RTD formulas. As Figure 8 reveals, just because you put things in a nice neat column in Excel doesn't mean that they preserve that row order as they make their way through the .NET framework. Getting that lot into a nice neat matrix wouldn't happen without some unique references...

We set everything running and - rather to the disappointment of certain Crew members - everything chugged along nicely. You certainly wouldn't be trying anything high frequency with this, but nothing fell over and all the right data ended up going in the right direction.

Our final test for MATLAB/K2RTDKit was to connect CQG to MATLAB (we used the CQG API for this), use the data from that as inputs to a Kalman filter function, send the output from that via K2RTDKit to Excel, do some pointless calculations there to generate some (rubbish) trading signals, pump those back into MATLAB (also via K2RTDKit) before finally using them to trigger orders via IB-MATLAB into Interactive Brokers' real time simulated trading environment for "execution". Much to our delight (and to be honest also our amazement) this convoluted process worked without a hitch.

.NET

In addition to the MATLAB plumbing, KaiTrade also provides as standard the source files for a sample C# application intended to demonstrate how to shunt data to and from Excel (the compiled application GUI is shown in Figure 9) in conjunction with other applications. We simply fired up Microsoft Visual Studio 2010 and built the project from the source files without any problems (see Figure 10).

Figure 9

Figure 9

Figure 10

Figure 10

The sample application works with the same sample Excel workbook used with MATLAB, so once the application opens, the first step is to hit the "Start Handler" button so it starts listening for connections. Opening the sample Excel workbook completes the connection so that any data relating to valid RTD functions (containing "WPUB") find their way to the C# sample app and are displayed on its "InboundRequest" tab. This is shown in Figure 11a, along with the source area of the spreadsheet (rows two to seven and row 17).

Figure 11a

Figure 11a

To get data flowing in the opposite direction, the sample app has a "Publish Data" tab, which lets you acquire available subjects and use these in conjunction with appropriate header and data values to test publish data back to Excel. Figure 11c shows a sample setup which corresponds to the published value in cell I2 of Figure 11b.

Figure 11b

Figure 11b

Figure 11c

Figure 11c

To inject a bit more reality, we hooked up Excel to CQG (via CQG's own RTD server) and then republished the incoming data back out to the C# sample app (see Figures 12a and 12b). Finally, just for the sheer hell of it, we republished it from the sample app back into another Excel workbook without any issues. In fact, the only problem we encountered during this phase of testing was that exiting the sample app's GUI didn't also terminate the associated background process. Until the penny dropped, we couldn't quite figure out why everything had stopped working. We kept closing and reopening the GUI and restarting Excel to no effect - until we looked on the process tab of Windows task manager and found about twenty orphaned "K2RTDHandlerTest.exe" processes...

Figure 12a

Figure 12a

Figure 12b

Figure 12b

Having the source code for the sample app and a rather useful KaiTrade screen movie to help us meant that one of the more adventurous members of the Wrecking Crew was prepared to have a go at hooking an existing C# application he was working on up to K2RTDKit. The screen movie is pretty much on the button when it refers to only "a few lines of code" being required - we were up and running in about 20 minutes at most. In this case, the C# app was effectively acting as a scoring mechanism for different trading models (or the same trading model running with different parameter sets) that in our slightly clunky test implementation was set up to fire score updates into Excel, so that a trader monitoring a portfolio of models would know when to switch which model(s) in/out of real time operation. The trader would then acknowledge the instruction with a confirmation signal that was routed back via K2RTDKit so the C# app would know which models were currently deployed in the market. In real time, this C# app will probably be controlling potentially thousands of candidate models (or variants thereof) but obviously wouldn't be doing this in conjunction with Excel, so in a rare fit or reasonableness we dialled this back to just 100 candidate models.

It quickly became apparent that a human trader wouldn't be able to keep pace with the number of changes in a timely manner, so we re-jigged the spreadsheet to auto respond to each model change instruction after a short random delay. Again, all rather contrived, but it did have the merit of establishing that K2RTDKit is certainly versatile as well as reliable.

Emboldened by our success in getting everything going on a single machine and also by the fact we had an .exe file to work with 2 , at the last minute we thought we'd have a stab at using K2RTDKit across a network. So we fired up a separate workstation and modified the RTD command string to include the name of the machine running K2RTDKit (inside the empty quotes in Figure 7a).

We then tried to register some of the K2RTDKit DLLs on the client machine. No chance - just a string of "entry-point DLLRegisterServer not found" errors. We could, of course, have called KaiTrade to check whether K2RTDKit was even intended to run across a network, but with our usual impeccable timing we'd chosen to make our last minute attempt to test this on the Fourth of July...

Conclusion

No question, K2RTDKit is a versatile beast. For lower frequency automated trading involving Excel, it's definitely a viable tube of glue. However, during the course of the review several of the team expressed the view that it's a really handy generic kitchen implement to have in the tech-drawer when you're trying out new ideas (again, obviously not HF ones). If you want to quickly chuck a diverse set of data and analytic ingredients into the Excel cook pot and see what happens, it's a major time saver. The amount of code/effort involved in implementing it is minimal (unless like us you choose to waste time trying to get it do things it never claimed to do) - and then there's price. K2RTDKit costs $49.95, which is completely ridiculous (in a very nice way).

1. Under KaiTrade's naming convention, "WPUB" denotes something that should be pushed to an external application (in this case MATLAB), while "PX" denotes a subscription request.

2. The crucial and often forgotten (well, by us anyway) sentence from the Microsoft RTD support docs: "Note When RTD server runs on a remote server, the RTD server must be an .exe.". No .exe = no DCOM operation.