This question often arises, when you need to provide pricing functions for end-users and connect them to data sources, especially real-time data feeds. The answer heavily depends on your existing code you would like to include.

Excel User-Defined Function (UDF) or Macro?

The application you want to write can either provide a user-defined function (UDF) of act as a macro. A UDF is an Excel function which takes Excel cells as arguments and returns some value as a result of the computation to the cell. This way, the UDF changes exactly one cell.

Example of an Excel UDF

An Excel macro is different. The Excel macro can modify values and styles across different Excel cells and different sheets. Using a macro, you can also create sheets, create menus change coloring etc.

An Excel Macro example: Solver

In short: A UDF is right for you if you want to add some computational functionality to Excel. For styling and automation, an Excel macro is the right choice.

No existing code: MS Excel plugin and UDF

The easiest way to create a UDF in MS Excel is VBA. VBA is build-in into Excel and can easily be used. But, VBA is not suitable for larger projects since code maintainability and debugging are rather limited. If you want to create a larger project connection to Excel, you should go for a .Net alternative as described below. A Java connection is possible, but not that well supported and sometimes the performance is limited.

Connect .Net to MS Excel as plugin or UDF

The main technologies for connecting MS Excel with .Net e.g. C# are Visual Studio Tools for Office (VSTO) and Primary Interop Assemblies (PIA). VSTO has a great integration into Visual Studio, e.g. for customization of the Excel Ribbon. But, VSTO and PIA require an additional runtime on the user’s Desktop. And, this runtime if Version dependent. I recommend NetOffice, which basically delivers the same functionality as VSTO and PIA, but without an Excel version dependent runtime requirement.

None of the above can create a UDF for MS Excel. For that, I recommend Excel DNA. Excel DNA can create UDFs and can serve as macro technology including Ribbon integration. Excel DNA does this by creating Excel Addins, so called .XLL files. They are compatible with all Excel versions, even a 64Bit version can be created.

Connect Java code to MS Excel as plugin or UDF

Since Java and Microsoft never became close friends, it is still tricky to connect Excel and Java. For this combination, you have one great option for a macro integration and a good one for UDFs:

• Macro Integration: This can be done using the Excel COM interface. There are many Java COM bridge providers. The best for me seems to be Groovy Scriptom, a cool groovy-COM bridge. By the way, Groovy is very much the same as Java, but simpler in many cases. It also runs in the JVM and the class files are 100% compatible with Java.
• UDF Integration: The UDF integration is a lot harder than just calling COM. A good open source project for this is XLLoop. It allows the Excel integration of Java, C, python, Ruby and many more. A problem associated with this approach is that the user has to start the XLLoop server manually and not only Excel.
• Update 2011-12-11: Connect Java and .Net with SOAP allows you to integrate .Net Addins as described in the previous section with Java functionality.

Connect C – or other native code – to MS Excel as UDF

Connecting C to MS Excel is a long proven technology. Programming in C is often hard, but doing it right your UDF will be really fast. A introduction for MS Excel addins using an XLL is described here. The C API of MS Excel allows you to do macro programming in C, too. But, I do not recommend C for macro programming: It’s just a waste of time. Go to .Net or Java.

Connect Matlab code to MS Excel as plugin or UDF

Matlab is a great program for integration of other languages. It is easy to call C, Java or .net from Matlab. But, the integration into other platforms is less easy. If you want to call Matlab from MS Excel, you have basically three options:

1. Matlab Spreadsheet Link EX from the MathWorks: This Excel addin allows to use your .m code for computations and UDFs. The drawback is that this addin requires an active Matlab license on the user desktop.
2. Matlab Builder EX from MathWorks: This is a code signing tool together with a Matlab Compiler. That means, you can easily connect Matlab from Excel, even without a Matlab license on the user’s desktop.
3. Use the Matlab COM interface. This can be used from almost any language. An example for calling Matlab from VB.NET is here. This comes automatically with Matlab, but also requires an active license – except if you use the Matlab Builder .net, which can create COM components.

Connect ThetaML code to MS Excel as UDF

ThetaML is great for pricing options using Monte Carlo simulations. It is basically a Monte Carlo toolbox for Matlab. Since it is tightly integrated with Matlab, we can use the same mechanisms as from connection Excel and Matlab. The easiest way for integration is using the Matlab Link EX with Matlab_fcn(…) in the Excel cell. If you now combine this with Theta Proxy XL, you can create a Monte-Carlo pricing with real-time speed.

Conclusion

The top technologies for an Excel UDF customization are VBA and Excel DNA. And VBA, NetOffice and Groovy Scriptom for macro programming. If you do not want to re-implement your logic programmed in Matlab or ThetaML, you can also use the tools from MathWorks to access it from Excel.