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
              );

11 thoughts on “Running C++ in Excel via XLW”

  1. Thanks for your post which helped me solving the problem that “Execution make -f RunInterfaceGenerator.mak …… failed”

    However, in building the Template.cbp, the following came up, it seems I need to include a large bunch of header and source files for it to work. Can you please kindly advise? (I had not restored to default in the codeblocks->settings->compiler part, as I am using the following mingw x86_64-w64-mingw32-gcc-4.8.0-win64_rubenvb.7z and had my codeblocks set according to the different directory.)

    Thanks in advance!!

    Running target pre-build steps
    make -f RunInterfaceGenerator.mak
    make: Nothing to be done for `all’.

    ————– Build: Debug in Template (compiler: GNU GCC Compiler)—————

    g++.exe -shared -Wl,–dll -L..\..\..\lib obj\Debug\source\source.o obj\Debug\source\xlwWrapper.o -o bin\Debug\Template.xll -static -static-libgcc -luser32 “C:\Program Files (x86)\XLW\xlw-5.0.0f0\xlw\include\xlw\xlw.def” “C:\Program Files (x86)\XLW\xlw-5.0.0f0\xlw\lib\libxlw-gcc-s-gd-5_0_0f0.a” “C:\Program Files (x86)\XLW\xlw-5.0.0f0\xlw\lib\libxlw-gcc-s-5_0_0f0.a”
    Cannot export xlAutoClose: symbol not defined
    Cannot export xlAutoOpen: symbol not defined
    Cannot export xlAutoRemove: symbol not defined
    Cannot export xlwGenDoc: symbol not defined
    obj\Debug\source\xlwWrapper.o: In function `xlEchoShort’:
    C:/Users/ChernYang/Documents/XLL_Project/source/xlwWrapper.cpp:59: undefined reference to `xlw::XlfExcel::Instance()’
    C:/Users/ChernYang/Documents/XLL_Project/source/xlwWrapper.cpp:59: undefined reference to `xlw::XlfExcel::IsCalledByFuncWiz() const’
    obj\Debug\source\xlwWrapper.o: In function `__static_initialization_and_destruction_0′:
    C:/Users/ChernYang/Documents/XLL_Project/source/xlwWrapper.cpp:33: undefined reference to `xlw::XLRegistration::XLFunctionRegistrationHelper::XLFunctionRegistrationHelper(std::string const&, std::string const&, std::string const&, std::string const&, xlw::XLRegistration::Arg const*, int, bool, bool, std::string const&, std::string const&, bool, bool, bool)’
    obj\Debug\source\xlwWrapper.o: In function `xlw::UsesTempMemory::UsesTempMemory()’:
    C:/Program Files (x86)/XLW/xlw-5.0.0f0/xlw/include/xlw/TempMemory.h:158: undefined reference to `xlw::TempMemory::EnterExportedFunction()’
    obj\Debug\source\xlwWrapper.o: In function `xlw::UsesTempMemory::~UsesTempMemory()’:
    C:/Program Files (x86)/XLW/xlw-5.0.0f0/xlw/include/xlw/TempMemory.h:162: undefined reference to `xlw::TempMemory::LeaveExportedFunction()’
    obj\Debug\source\xlwWrapper.o: In function `xlw::impl::XlfOperProperties::getXlType(xlw::XLFOPER*)’:
    C:/Program Files (x86)/XLW/xlw-5.0.0f0/xlw/include/xlw/XlfOperPropertiesDynamic.inl:35: undefined reference to `xlw::impl::XlfOperProperties::m_version’
    C:/Program Files (x86)/XLW/xlw-5.0.0f0/xlw/include/xlw/XlfOperPropertiesDynamic.inl:35: undefined reference to `xlw::impl::XlfOperProperties::m_version’
    obj\Debug\source\xlwWrapper.o: In function `xlw::impl::XlfOperProperties::setXlType(xlw::XLFOPER*, unsigned long)’:
    C:/Program Files (x86)/XLW/xlw-5.0.0f0/xlw/include/xlw/XlfOperPropertiesDynamic.inl:39: undefined reference to `xlw::impl::XlfOperProperties::m_version’
    C:/Program Files (x86)/XLW/xlw-5.0.0f0/xlw/include/xlw/XlfOperPropertiesDynamic.inl:39: undefined reference to `xlw::impl::XlfOperProperties::m_version’
    obj\Debug\source\xlwWrapper.o: In function `xlw::impl::XlfOperProperties::setDouble(xlw::XLFOPER*, double)’:
    C:/Program Files (x86)/XLW/xlw-5.0.0f0/xlw/include/xlw/XlfOperPropertiesDynamic.inl:50: undefined reference to `xlw::impl::XlfOperProperties::m_version’
    obj\Debug\source\xlwWrapper.o:C:/Program Files (x86)/XLW/xlw-5.0.0f0/xlw/include/xlw/XlfOperPropertiesDynamic.inl:50: more undefined references to `xlw::impl::XlfOperProperties::m_version’ follow
    obj\Debug\source\xlwWrapper.o: In function `xlw::impl::XlfOper::AsShort(char const*, int*) const’:
    C:/Program Files (x86)/XLW/xlw-5.0.0f0/xlw/include/xlw/XlfOperImpl.h:603: undefined reference to `xlw::XlfOperImpl::MissingOrEmptyError(int, char const*, char const*)’
    C:/Program Files (x86)/XLW/xlw-5.0.0f0/xlw/include/xlw/XlfOperImpl.h:619: undefined reference to `xlw::XlfOperImpl::ThrowOnError(int, char const*, char const*)’
    obj\Debug\source\xlwWrapper.o: In function `xlw::XLFOPER* xlw::TempMemory::GetMemory(unsigned long long)’:
    C:/Program Files (x86)/XLW/xlw-5.0.0f0/xlw/include/xlw/TempMemory.h:61: undefined reference to `xlw::TempMemory::GetBytes(unsigned long long)’
    collect2.exe: error: ld returned 1 exit status
    Process terminated with status 1 (0 minutes, 0 seconds)
    14 errors, 0 warnings (0 minutes, 0 seconds)

    1. I’m not sure if it’s the same with MinGW, but Visual Studio projects find their headers/libraries through the $(XLW) environment variable. Have you checked this? With this environment variable, you shouldn’t need to hardcode your file locations, and its clever enough to deal with multiple versions of Xlw.

      I find the interface builder to be simply not worth the hassle. Just code the whole thing from scratch yourself. Much more logical, and less dependent on the ‘magic’ of interface builder (which does all sorts of things, and is quite opaque and a bit fragile too). I know InterfaceBuilder is meant to help newbies set up their Xlw code, but given you end up coding Xlw calls in C++ anyway, you need that knowledge to write add-ins, and might as well do it yourself from the start anyway. If you really need InterfaceBuilder to set the boilerplate code for your C++ Excel functions, your knowledge of C++ is probably not up to the standard to write them in the first place! Xlw is a framework, not a wizard – you still need in-depth knowledge of C++ to write useful functions.

  2. I successfully build the program and the template.xll finally was generated, by when I try to import it into excel, the excel just try to open it as ordinary excels file, how can I fix this issue?

    1. Hi, sorry for the slow reply. Do you get a pop-up asking if you’d like to run add-ins? Click enable. Then, press the button with the f_x (‘Insert Function’) that’s beside the formula bar, choose ‘all’ in the drop-down bar, and see if you can find the function ‘priceOption’. If it’s there, you’ve been successful.

      If this doesn’t work, let me know and I’ll try and work out what’s going on.

      Best,
      QuantoDrifter

      1. ello,
        Thanks a lot to quantodrifter for this website.

        I successfully build the program and gerated the template.xll. But when I tried to use it, it shows “The file format and extension of ‘Template.xll’ don’t match. The file could be corrupted or unsafe. Unless you trust its source, don’t open it. Do you want to open it anyway?”

        Please tell me how to fix this problem. Thanks.

    2. Are you sure you’ve built it for the right platform for Excel? For example. Excel 2007 is a 32-bit app, and requires 32-bit .xll files. Did MinGW build a 64-bit version by default? These will not open in 32-bit versions of Excel. Othewrise, an .xll file should open automatically as an add-in, without any user input. An easier option might be to download Visual Studio (the free/express versions) and do it that way – should work first time, given it’s by Microsoft, and uses the default Windows library by default without you having to specify anything.

  3. Hello,
    Thanks a lot to quantodrifter for this website.
    I found it looking for help. I an using XLW 5.0.1f0, Code::Blocks 13.12 with MingW under Windows seven 64 bits. After changing to make.exe, I launch Code::Blocks and Build. But at the end, I obtained the following errors :
    — Build: Debug in Template (Compiler : GNU GCC Compiler) —-
    mingw32-g++.exe -shared -Wl,-dll, -L..\..\lib obj\debug\source\source.o ……..
    D:\Programs\xlw-5.0.1f0\xlw\lib\libxlw-gcc-s-gd-5_0_1f0.a (XlfOperProperties.o) : In function “XlfOutOfBounds’:
    c:\Hudson\workspace\xlw\xlw\xlw\build\gcc-make/../../include/xlw/XlfException.h:111: undefined reference to ‘_Unwind_Resume’

    and so on for a total of 60 errors !!
    Could you help me ?
    Problem of version ?
    Thanks for your help.

    1. You need to compile your .xll add in as a 32-bit file. You can’t mix and match 64-bit and 32-bit .xll files. Your version of Excel sounds like it needs 32-bit files – you must compile your add-in accordingly.

Leave a Reply

Your email address will not be published. Required fields are marked *