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 :

#include<math.h>

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

LIBRARY
EXPORTS
	discountingLongForward

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.



10 pensamientos en “A function defined in C++ and passed on to Excel : a DLL example

    1. édouard Autor de la entrada

      Thank you for your comment John ! This blog site is all about providing with working code and (some) explanation. I mean I have read so many times articles on C++/Quantlib saying “do this .. then take it and turn it into .. “. Many times I have been overwhelmed ang somehow frustated. BTW, it is nice to hear my post is helpful.🙂

      Responder
  1. édouard Autor de la entrada

    Hello Thom.

    *Start your Excel application.
    *Click on Alt+F11 to open the window of the VBA editor (VBE).
    *In the “Insert” menu of the VBE click on “Module”.
    *Paste the code in the blank space it now reads.

    Thus, get back to Excel windows. Choose a cell, and start typing “=discoun….” . Excel should now retrieve the function you just added via the VBE.

    HTH.
    Édouard

    Responder
  2. Pingback: excel guru's please help c++ dll not found error #value! on target machine

Responder

Por favor, inicia sesión con uno de estos métodos para publicar tu comentario:

Logo de WordPress.com

Estás comentando usando tu cuenta de WordPress.com. Cerrar sesión / Cambiar )

Imagen de Twitter

Estás comentando usando tu cuenta de Twitter. Cerrar sesión / Cambiar )

Foto de Facebook

Estás comentando usando tu cuenta de Facebook. Cerrar sesión / Cambiar )

Google+ photo

Estás comentando usando tu cuenta de Google+. Cerrar sesión / Cambiar )

Conectando a %s