Archivos Mensuales: abril 2011

A function defined in C++ and passed on to Excel : a DLL example

Imagine  the function you need is not available in Excel, moreover you like better programming with  C++ than with Excel VBA. In this case, you shall want to encapsulate the function defined by yourself into a piece of C++ code, and write an interface so that to use it with ExcelThis way you would have the best of both world, that is the power and fast-computing of  C++, and the user-friendliness of Excel.

This is the purpose of this post. We are going to write a DLL made of a standard *.cpp file containing  the definition of the function and a *.def file (that is a module-definition file). And, we will tell Excel about this DLL thanks to few Excel VBA lines of codes.

Our simple example of a DLL integrated together with Excel (version 2007) is derived from Options, futures and other derivatives 7th (J.C Hull), p.109. The author there explains how to calculate the value of a forward contract on a investment asset providing no income. We are going to write the code corresponding.

J.C Hull gives the example of an investor who entered a long forward position on a non-dividend-paying underlying, the delivery price being $24 (K=24). The time length to maturity is now 6 months (T=0,5) . The price of the underlying as of today is $25 (S0=25), and the risk-free interest (continuous compounding) rate is 10% per annum (R = 10%).

Let f be the value of a long forward contract today, f = (F0 – K ) e –rT where F0 = S0 erT

S0 = 25
K = 24
r = 10%
T = 0,5

Please, refer to Creating a DLL project with MS Visual Basic 2010 to get started up with the creation of a DLL project with MS Visual Studio 2010.

First, our *.cpp file will contain the definition of the function that computes f is :


double _stdcall discountingLongForward(
	double underlying,
	double K,
	double rate,
	double timeToMaturity)
double forward = underlying * (exp (rate * timeToMaturity));
double discountedLongForward = (forward - K) * (exp (-rate * timeToMaturity));
return discountedLongForward;

The keyword _stdcall is a function calling keyword. Here, discountingLongForward is the function to be called by Excel.

Now, the *.def file lists the  function that have to be passed on to Excel.
This is straightforward


The line LIBRARY is automatically written at the time of adding the *.def. Just let it.

At this stage, you have both your *.cpp and *.def files. It is time to compile in the usual way.

We are now turning to Excel. We have to tell it that our DLL exists, where it locates, and the nature of its inputs and output.

Declare Function discountingLongForward Lib _
"C:\ [folder] ... [your_DLL_file_name.dll]" _
(ByVal underlying As Double, ByVal K As Double, _
ByVal rate As Double, ByVal timeToMaturity As Double) As Double

Notice here the ByVal keyword, as ByRef is the default mode of Excel. It is to ensure the calculations gets right. But, one can argues that it is not necessary for this very basic example.

Here it is!

To conclude with, below we show our DLL at work. With no surprise, it gets the same calculation result as read in the J.C Hull’s book.