Toomre Capital Markets LLC

Real-Time Capital Markets -- Analytics, Visualization, Event Processing, and Intelligence

ActiveX

2009 TCM Transition

As Toomre Capital Markets LLC ("TCM") starts the fourth quarter of 2009, we are cognizant that our consulting business is once again in transition. As it is sometimes said, as one door closes, another door opens. We are just not quite sure which door (professionally at least) might be opening.

For much of the past two years during the on-going credit crunch, the TCM staff has been working extensively with a major participant in the Life Settlements sector. We have used various pieces of the MATLAB mathematical modeling language together with Microsoft SQL Server relational data bases and ActiveX technology to create the calculation code for their customized portfolio management application. The resulting code is rather advanced.

This MATLAB-compiled code enables TCM's client to quickly price various individual life insurance policies and to help identify the risk/rewards in simultaneously managing several portfolios of such investments. It has moved the client away from the risks and confusion of large complicated Excel spreadsheets and onto a modern web-based platform. Alas, though, the heavy development work for that particular project is drawing to an end and we are now in the acceptance testing phase. There is unlikely to be any further enhancement work necessary until at least the code has been used in production for some time period.

Partly as a result, TCM has wondered where we should turn our attention to next. Should we turn to focusing our efforts on developing similar types of MATLAB-based code for other financial clients? Certainly there are many financial firms that enjoy the convenience and ease of data input into Excel spreadsheets. With time, though, many of these same spreadsheets become large, many times unwieldy and often contain inaccurate cell references in some of their formulae.

Depending upon the complexity of what information the spreadsheet is attempting to model, MATLAB often is an effective tool for tying together: the ease of that spreadsheet bring to data input and manipulation; easy access to data stored elsewhere in relational data bases; mathematical calculation of arrays (including good routines for various types of optimization); integration with tried and time tested C/C++ calculation libraries; and excellent visualization opportunities for understanding the results.

TCM is quite skilled in doing this advanced MATLAB development and integration work. (The reader might note the many posting on the TCM website about the term MATLAB and then appreciate why we receive so many visitors each day looking for information on such terms as ActiveX, Excel and MATLAB together.)

As we contemplated during the last few weeks which way to turn, Mathworks (the maker of the MATLAB product) contacted TCM about possibly working with a hedge of hedge funds that needed help with integrating some of their existing MATLAB models with their client-facing website. Could we help? It now appears that very shortly we will be starting an initial project focused on foreign-exchange investments.

Writing BLOBs from Matlab to SQL Server using ActiveX and Stored Procedures

Over the past few months, I’ve been working on a project that integrates sophisticated Matlab financial models with a Microsoft environment. Back in February, I wrote about writing from Matlab to Excel using ActiveX. Since then, I’ve been focusing on SQL Server and want to share those experiences.

The simplest way to access a Microsoft SQL Server from Matlab is to use the Database Toolbox. This toolbox allows for an ODBC or a JDBC connection to a database. Since our functions are running in a Microsoft environment, we went with an ODBC connection. The connection is very simple to open:

conn = database('ODBCdatasourcename','ODBCusername','ODBCpassword')

In our case, we would then ‘ping’ the connection to make sure everything has connected properly, and if not, return an error:

try
ping(conn) ;
catch ME
% insert error processing code here …
return;
end

We then execute our SQL and check the results

sql = ‘select a,b from c where d = 10’;
curs = exec(conn, sql) ;
curs = fetch( curs ) ;
NumRows = rows(curs);
if (NumRows < 1 )
% insert error processing code here about no rows returned
return ;
end

result = curs.Data;
for i=1:1:NumRows
if (~isnan(result{i,1}))
a{i} = result{i,1};
b{i} = result{i,2};
end
end
close(curs) ;
close(conn);

It has worked nicely for just about all of our needs. However, for speed and flexibility, we found it useful to use ActiveX for some of our database access.

Writing from MATLAB to Excel Using ActiveX

On a recent project we have needed to compare two versions of a complicated financial model. One is written in Excel and the other in Matlab. The model uses over 150 different input variables, so it can be a challenge to make sure that essentially the same variables are passed to both models. Today, I modified one of the routines to use ActiveX to send data from Matlab to Excel. The process was not easy to figure out, but once I figured it out, it is extremely straightforward.

Initially, we were using the Matlab command xlswrite. It is a quick and easy way to write data from Matlab into Excel. It uses a COM server, and can be a little slow, especially if you are making multiple calls. However, if you want to do anything more than simple reading and writing of data to a spreadsheet from Matlab, you need to start using the ActiveX server functionality that Matlab supplies.