Running C++ in Excel via XLW

Anyone who has downloaded and compiled the console version of my Monte Carlo Pricer will know that it’s not incredibly user-friendly. You have to double-click on an .exe file to run it, and then each time select input parameters which involves a fair amount of typing. The output is given as a single number on a console window, which isn’t very convenient for plotting multiple results or for further data analysis.

In fact, console applications aren’t that commonly used by quants. There are a few solutions used in the industry, and the one I’m going to look at today is an excel wrapper. The idea is that we will write C++ code as normal, then use a programme called XLW to wrap it as a link library and allow it to be called from inside excel. This combines the advantages of excel’s flexibility with C++’s speed and power. This post is going to be a step-by-step guide to getting things up and running (I’ve struggled with it a bit while trying to set things up) with a very simple example function – in future posts I’ll discuss shifting the old pricer over to an excel implementation.

I’m going to go through with screenshots from a clean start, hopefully this should be fairly general but if things change in future it may become outdated and require me to update or re-write these guidelines. I will discuss how to set XLW up using the open source Code::Blocks IDE, it’s a bit more advanced than the Dev-C++ that I’ve been using before and I’m shifting to it from now on for this blog.

Instructions:

  • Download and install Code::Blocks, WITHOUT a built in compiler

Auto-installers are available here, but it’s important that you choose the version WITHOUT the built in compiler, as this is out of date

Choose this download!
Choose this download!

Double-click on the downloaded file to auto-install; agree to the terms and choose default options (by default it should do a full install needing about 60MB and install to directory “C:\Program Files (x86)\CodeBlocks”)

 

  • Download and install the latest version of MinGW compiler

The latest version of the MinGW compiler can be found here, download and double-click on the .exe file to install. Click through the screens, choosing the following option on the third screen: “Use pre-packaged repository catalogues”; by default the compiler will install to “C:\MinGW”. On the ‘select components’ screen, be sure to select “C++ Compiler”! This WASN’T checked for me by default!

Check C++ compiler on this screen
Check C++ compiler on this screen

 

  • Register MinGW with Code::Blocks

In order to register the compiler that we’ve just downloaded, run Code::Blocks (there should now be a shortcut from your start bar).

How to register MinGW with Code::Blocks
How to register MinGW with Code::Blocks

In the toolbar, select Settings->Compiler Settings; and in the window that opens select the “Toolchain Executables” tab. Click on “Auto-detect”, and the IDE should find MinGW successfully.

 

  • Download and install XLW

The latest version of XLW is available via the “Download” link on the front page of the project here. At the time of writing, this is version 5.0 (released Feb’ 2013).

Once again, double-click on the downloaded .exe to install, but pay attention to the screens that pop up as I had to make some modifications to the default choices, as follows:

  1. XLW auto-detected Code::Blocks on the third page (Installed Development Environments) – if you see a blank white box here, you’ve not installed the IDE correctly
  2. On the 4th screen, I had to expand the grey ‘X’s and selected the option shown in the image below
Expand the selections, and check 'Code::Blocks' under 'Source'
Expand the selections, and check ‘Code::Blocks’ under ‘Source’

By default, XLW will install to “C:\Program Files (x86)\XLW\xlw-5.0.0f0”

 

  • Run the XLW template generator

Now we’re getting close. In your start menu, you should now find an XLW option. Go to XLW>xlw-5.0.0f0>xlw>Extract XLW xll template; select “Code::Blocks” on the window that appears, by default the template will be created in the directory “C:\Users\….\Documents\XLL_Project”

Select Code::Blocks in the template extractor options
Select Code::Blocks in the template extractor options

 

  • FIX REQUIRED!!

Unfortunately, there seems to be a bug which has something to do with how XLW and MinGW are talking to one-another. After fiddling with many install configurations without success, I found the same issue discussed here (the second paragraph with a dark background), along with the following fix:

  1. Go to C:\MinGW\bin and locate “mingw32-make.exe”
  2. Copy this to the directory C:\Users\….\Documents\XLL_Project which you created in the previous step (it should contain “RunInterfaceGenerator.mak” among other files)
  3. Rename “mingw32-make.exe” to “make.exe”
  4. Run Code::Blocks, in the toolbar go to Settings->Compiler again, and in the window that pops up select ‘Restore Defaults’. Click ‘OK’ on both of the warning pop-up windows.

 

  • Open the Template project in Code::Blocks

If it isn’t already running from the last step, run Code::Blocks. Go to the directory C:\Users\….\Documents\XLL_Project that was created before, and open the project file “Template.cbp”. If all of the previous steps have been done correctly, you should simply be able to click “Build” (it’s the cog-shaped icon in one of the task bars) and have it compile without problems. Note that after compilation, a file called “Template.xll” appears in the directory C:\Users\….\Documents\XLL_Project\bin\Debug\

To test that everything is working, add an additional function to “source.cpp” and declare it in “cppinterface.h”, such as the example given in the picture below and at the bottom of this post, and check that the project still compiles.

I've added a trivial additional function here to add two doubles - of course, I've also updated the header file with a declaration of the function. XLW will do the rest!
I’ve added a trivial additional function here to add two doubles – of course, I’ve also updated the header file with a declaration of the function. XLW will do the rest!

 

  • Call our functions in Excel!

Open an instance of Excel. In order to load the functions that we’ve created into Excel, find the file “Template.xll” in the directory C:\Users\….\Documents\XLL_Project\bin\Debug\ and drag-and-drop it into the Excel window.

Here we can see the trivial example we made before in action - our function is adding two doubles in different cells together. I will present more advanced examples in future posts.
Here we can see the trivial example we made before in action – our function is adding two doubles in different cells together. I will present more advanced examples in future posts.

You should now find that the additional function you’ve written is available alongside Excel’s in-built functions: congratulations, you’ve made your C++ scripts run with excel as a front end! I’ll be looking at the advantages of this a lot in the near future.

 

Additional example function code

In source.cpp:

double // adds two doubles
AddTwoDoubles( double x, // The first double
               double y  // The second double
              )
{
  return x + y;
}

In cppinterface.h (before the #endif ):

double // adds two doubles
AddTwoDoubles( double x, // The first double
               double y  // The second double
              );