Note: this document was written in 1996-1997.

Financial applications are very expensive tactical and strategic toys.

The choices you make can have a long lasting impact on the bottom line of your trading book.

As the working environment shifts more and more from the Unix Workstations to the Office / Intel platform, Excel® is taking on the role of the analytics repository of choice in order to glue together legacy systems with the latest languages.

To make your life a lot easier, we have set-up a comparative analysis between most of the choices available.

This section also includes some examples demonstrating the technology.

Client Server technology is the most widespread architecture today for Risk Management Systems as well as other Calculation Servers. Things are nevertheless changing rapidly thanks to democratization of networks thanks to the web.

Spreadsheet servers are actually not really new. They have been used in cutting edge application centers of large information providers at least for the past 15 to 20 years.

However, the setup and costs involved are very different today.

In the past, this paradigm belonged to the Mainframe/Unix world where workstations were connected to vast arrays of concentrators and repositories.

This configuration handled large quantities of data processed on calculation server or more generically Spreadsheet such as Applix®, EffixCalc®, etc to and from Data dictionary servers (DDS), Databases, third party application, standalone shared analytic libraries, multi-feed contribution concentrators and back to the calculation servers.

This setup was indeed the precursor to the Spreadsheet Server paradigm and the n-tier calculation server model.

Today, the most common scenario  is a vast array of executables and shared libraries that reside in dedicated applications which are then shared across profit centers.

Unfortunately, very few firms can fully capitalize on these libraries. 

Instead they tend to port existing libraries and executables to one "strategic" platform geared around one type of business. Obviously, this is a godsend for many consulting firms, but it does not solve the problem. 

Instead of integrating and porting analytics from one application to another which does not favor reuse and drains valuable resources, Spreadsheet Servers can be used as function factories or  central analytic repositories.

When dealing with this technology, many managers believe they will actually need to port their existing routines to NT or worse, Excel®.

In the spreadsheet server model, the spreadsheet acts as a common placeholder for data, not the analytics, thereby creating a bridge between data and analytics. The data can reside in a database, it can come from feeds, from manual input,  third party application, from ASCII files or Middle-ware. Additionally, it can be raw or pre-processed.

For most senior managers, the biggest problem is that there are actually too many options, which has often led to costly mistakes. Here we hopefully provide you with all the necessary information to make the right strategic decision.

XLL

You've seen plenty of those if you have perused around this site. Definitely the "snappiest bang for the buck for both user and developer". Provides implicitly the user with a free graphical user interface and on-line help facility, with menus, toolbars and dialog boxes. 

This approach is very powerful in terms of resources. So what more can want? Well, this also comes at a cost. Although the technology is extremely robust it is also arcane. There is indeed very little "public" documentation on the subject. Worse, there are very few XLL experts out there and they tend to command steep prices. Even Microsoft has experienced huge problems solving bugs due to the lack of expertise.

Pro: 

Best "bang for the buck". Extremely Robust. User interface comes for free. On-Line Help is also provided (Excel 9 on-line help was "broken". Ms has just reactivated it with XP version, however, you cannot register functions temporarily anymore).

Cons:Old technology. Very little documentation on the subject. Requires  expertise.

Microsoft has tried, for a long time now, to deprecate XLLs but they've taken a life of their own in the financial and military community due to their real-time efficiency and portability. 
Although no official statement has ever been made, Ms developers now admit xlls are probably here to stay. 

Top

The DLL via Register is the poor man's XLL. Actually, it is exactly the same as using a standard XLL, except that you do not have to include the add-in registration portion, access to menus nor toolbars. This method is actually very straightforward and though less robust as a package than the XLL, it does offer easier maintenance. Requires an Excel® MacroSheet (Excel® V4) or VBA and the correct directory to Load.

Pro   :

 Just as good as a standard XLL. Function Wizard and User interface comes for free.
            Easy to maintain.

Cons: 

Not easy to load. You must define the directory where the dll resides (can result in a
            maintenance nightmare if not implemented correctly).   Old technology.

Top

VBA DLL via Declare

You can load any library through a VBA declare, but your exported functions will only be available through VBA code. This means you will not be able to access the function from the spreadsheet directly. You can however create an additional Macro Wrapper or better a function, but this will result in one more layer of inefficiency.

Pro: Simple. Low skills and knowledge required. Usually easy maintenance.

Cons: Requires an additional VBA wrapper around your C/C++ code. The result will be lower performance than standard XLL. Cannot handle Ranges directly. Poor Matrix  handling capabilities. Old technology. There have been incompatibility nightmares. You  might indeed experience version conflict due to a completely unrelated application loading the latest control. 

Top

Automation is an extremely powerful solution to handle large quantities of data, especially matrices and real time snapshots.

Microsoft did a great job breaking down the Excel® application into basic components. This means you can take control of Excel® via automation. You can thus steer Excel® from VB, Perl, Java, C and C++ without even touching Excel®.

Since May 99, you can translate directly your VB code into C++ without producing a single line of C++ thanks to a free tool provided by ms.

Lowest level and complete control over object instantiation.

The most common. Full support and "coaching" available from ms.

The latter is the easiest to program and looks very much like Visual Basic Code. Unfortunately, #import does not offer multi-threading, which can be hugely problematic since you cannot control multiple running instance of Excel®. On the other hand, this method can be used directly from Unix to steer Excel® provided you have the right toolkits.

You must define an Interfaces wrapper for each exportable function via IDL. You then create a "Reference" within the Office app. The methods and properties of your objects can then be seen via the object browser and are instantiated directly from VB through the CreateObject function.

Pros : Your application becomes MS-Office Type Library compliant.  Your object becomes scriptable, which means you will be able to invoke your functions from a browser (If so your functions must be stateless, slightly annoying for c++). This is emerging technology, especially combined with WTS and XML sounds very promising.

Cons : This approach still does NOT give you access to cells and ranges. At least not directly. Also, your application will never be as fast and efficient as the XLL because VB lies between your code the Type Library and Excel® and must interpret each command at run time. 

Very few people seem to be aware you can bridge CORBA and Excel® seamlessly and without generating a single line of code in C++ or COM ((yes!, that's a single line). Every CORBA vendor supplies an Active-X broker or Com bridge with his toolkit.

You provide the IDL file and it churns out the Microsoft IDL files for you!

You then compile the resulting files with MIDL (Microsoft’s IDL compiler) and compile & link the project. The broker actually generates the project files for you, so you just wind up typing a few commands to narrow down specific behavior. You then register the server either directly or through the naming service. You can then call any function defined in your interface via VBA and your CORBA code becomes COM compliant (It simply create a proxy-skeleton with a type-library see above).

Pros : Your Unix Orbs becomes MS-Office compliant without producing a single line of code.

Cons : This approach does NOT give access to cells and ranges. At least not directly. Your app becomes an object (which you can view through object browser), This is not as user friendly as the XLL. See above.  This route offers much slower performance.

This is the method of the pros in ORBs. Access to this type of caliber does not come cheap but then, there is no reason why it should!

This approach can be summarized in two steps.

1) Create a C++ proxy stub for the services you want to publish or subscribe to.

2) Create a proxy skeleton in C or C++ to pass arguments directly from the dll or xll to your IDL and from the IDL to the dll or xll.  This is usually extended to connection points in ATL.

Implementation usually comes down to one or two line of code per function called, but obviously depends on your architecture and the design you have followed in the interface as well as the user interface you want to provide to users.

This technique can be combined with DDE servers and COM to provide a complete callback mechanism for marshalling class factories. This works especially well for Matrices and large chunks of Data (well Excell is .

Com Bridges, Type-Libraries as well as C++, COM / DCOM and Java compliance are free  by-products of this technology. (You can invoke Excel(R) function directly from Java at run time!

This is exactly the steps you would follow if you wanted to bridge Applix(R)  on Solaris with Excel and perhaps Quattro (since there is no limitation in terms of columns and rows which is currently Excel's biggest flaw). This approach gives you complete control over the office suite, from Access to each and every event in Excel.