By Peter Vinella

Wolfram Research
MathLink for Excel, Version 1.0

When I was asked to review Wolfram Research’s new add-in to its Mathematica product, called MathLink for Excel, I was a little worried. My last two reviews were...well, let’s just say the products didn’t live up to the vendors’ promises.

But I am happy to report that MathLink was a pleasant surprise and is well worth every penny of its $189 suggested list price.

At first blush, MathLink may appear to be a somewhat trivial tool geared toward attracting new Mathematica users—and selling more licenses. The product, after all, is essentially a simple way to call Mathematica functions from Excel without having to learn and employ the Mathematica front end. But even the most experienced Mathematica users will find many surprises in this well-conceived and targeted product. In fact, it helps answer one of the most common complaints of using spreadsheets on the trading desk: lack of control and “auditability.”

Before we get too far, we had better take a little detour and talk about Mathematica, the powerful mathematical engine behind MathLink. Mathematica is an extremely powerful mathematical programming environment. It has grown considerably since I first used it in 1989, and its latest release (Mathematica, Version 3) incorporates even more complex and arcane mathematical structures and constructs.

As a former math professor, I am struck by the scope and flexibility of the system in terms of its user interface, the presentation of results and the array of mathematical paradigms the system supports. While common derivatives spreadsheet add-ins such as Tech Hackers and FinCad are narrowly focused to support financial applications, Mathematica is a much more general and comprehensive mathematical computing environment. The system incorporates the rudiments of many basic pure and applied mathematical disciplines such as algebra, calculus, functional analysis, geometry and numerical analysis, as well as common engineering and statistical applications. It also contains a vast array of visualization tools, such as fractal analysis, which can produce some cool displays like complex plots and 3-D surfaces.

The user interface is based on a unique and powerful symbolic interpreter that allows extremely complex mathematical relationships to be defined and manipulated. Best of all, this interpreter supports a wide variety of mathematical conventions and notations.

To me, Mathematica’s user interface is an almost perfect extension of a white board. I had no problem transferring some tensor algebra I was using to model a complex risk relationship straight into the system. It even supported my inner and outer product calculations directly. In other words, rather than performing the analysis in one school of mathematics, translating that into another (which lends itself more to programming) and then translating it once more into a programming language to produce the results, I could work directly in Mathematica using the most appropriate mathematical representation.

Naturally, there is a learning curve associated with Mathematica’s front-end, but any user who is proficient in mathematical notation and who understands the underlying mathematics should have no trouble getting up to speed in a few hours. And for those who would like to remove the need for mathematicians altogether, Mathematica incorporates the CRC integral and series tables so that equations can be entered and solved by the system.

Mathematica runs in a variety of environments such as Microsoft Windows 95/NT and UNIX, and requires a fairly standard hardware configuration. Since the front end is independent from the computer kernel, users can configure Mathematica to run in client-server mode. In addition, Mathematica programs can be compiled, allowing Mathematica functions to be called from external programs written in a number of languages, including C. Moreover, a financial application library that supplies pretty much all the basic financial calculations one would ever need is available for $395. And for Internet junkies, yes, Mathematica is even web-enabled.

You might be thinking, “OK, if you’re a math freak, Mathematica is great, but is it really necessary in a capital markets business?” As derivatives and risk modeling become more complex and use more exotic tools such as PDEs, neural nets and genetic algorithms, the need for true mathematical modeling is becoming more acute. Of course, this doesn’t mean that Mathematica is going to replace all the financial spreadsheet applications on the trading desk. What it does mean, however, is that banks which don’t use tools such as Mathematica will be at a severe disadvantage. Complex derivatives and risk relationships cannot be modeled effectively entirely within a spreadsheet. With a single Microsoft NT license price of $1,495 and volume discounts available, Mathematica should be used in analyzing every derivatives desk’s or risk department’s portfolio. So the basic question is not whether to use Mathematica, but how to use the power of Mathematica best without needing an army of mathematicians and programmers running around.

As many of you might have already guessed, MathLink for Excel is Wolfram’s attempt to do just that—take Mathematica out of the research domain and make it an enterprise application without significant custom programming.

Basically, MathLink is an optimized interprocess communication protocol that allows Mathematica’s canned functions as well as user-written Mathematica programs to be called directly from Excel, thereby avoiding the need for Mathematica’s front end or custom programming by all but a small group of developers. Users familiar with standard math libraries such as IMSL will feel right at home making traditional FORTRAN-like function calls, while the more theoretical types can enter more mathematically based notations such as vectors directly into Excel. And yes, pure Excel jockeys will be able to treat MathLink just like any other spreadsheet add-in or macro.

In addition, MathLink allows new or existing Excel spreadsheets to call Mathematica functions either locally or over the network with only minor modifications. This is normally limited only to the Mathematica function calls themselves. The output, moreover, can easily be produced in a manner consistent with the expected Excel format. These can be Mathematica routines, graphics or even sounds. All results, including complex Mathematica graphics, can be displayed directly in an Excel worksheet, thereby vastly improving the graphic capability normally associated with spreadsheets. Since the input structure is quite flexible, users who are familiar with the Mathematica programming language can jump right in, while even inexperienced users can begin to harness the power of the full Mathematica environment with little or no knowledge beyond Excel. This means that spreadsheets can be easily modified to use Mathematica with only minor changes and at minimal cost.

The product is a simple way to call Mathematica functions from Excel without having to learn and employ the Mathematica front-end.

Beyond these obvious benefits, MathLink’s biggest advantages are much subtler. Excel is a terrible programming environment for complex mathematics. Spreadsheets were intended to perform basic arithmetic functions and not generate probability distributions. To be fair, from a mathematical point of view, standard structured programming languages such as C are even more obscure. I won’t even try to count the number of times I have worked out a problem in mathematical notation only to spend hours trying to force it into Excel. And God help the person who has to convert the Excel code back into mathematics in order to debug the spreadsheet.

Simply put, MathLink allows the mathematics to be retained while presenting the results in a familiar and inexpensive spreadsheet format. Moreover, since Mathematica and the spreadsheets can run in true client-server mode, the Mathematica kernel can be used to warehouse the enterprise books-and-records pricing and risk models in a single location. Yes, that means that all the audited spreadsheets can easily use standard and approved models.

Be careful, however. The results may vary from spreadsheet to spreadsheet, since the data used in each of the independent spreadsheets will be specific to that particular spreadsheet and will not be shared. Unfortunately, Mathematica is not a data warehouse. But at least the models themselves will be consistent. This, in itself, is no small achievement and should make every regulator happy.

Finally, since the models can be kept in a central location, upgrades can be made to the models without users having to change dozens or even hundreds of spreadsheets. Given the fact that I can’t even copy and paste without creating at least one bug along the way, this will be a big time and money saver, not to mention a great model and operational risk hedge. This alone should pay for the Mathematica and MathLink Excel licenses many times over. Given the price, power and control the products offer, a derivatives desk would be foolish not to want to use them.

For information about this product, see www.wolfram.com/products/excel_link.

Was this information valuable? Subscribe to Derivatives Strategy by clicking here!