Excel Monte-Carlo Pricer

I’ve updated the Monte-Carlo pricer to run in excel. Give it a go and the benefits should be obvious – no more messing around with consoles or repeated typing, excel provides a handy interface for running C++ files.

What you’ll need to do is:

  • Download the Excel pricing sheet
  • Download the .xll library (large!) – this is the equivalent of the old .exe file
  • Open the Excel file, and drag-and-drop the .xll file inside the pricing sheet
  • Click on the option to “enable this add-on for this session only”
  • The Option Price function should now re-calculate option price as you make changes to the parameters!

Try using the option pricer function yourself – it now works just like a native excel function, so you can drag it into many cells and it will adjust target cells as usual allowing for easy variation of parameters and plotting of results.

If you’ve successfully set up XLW and Code::Blocks as I described in a previous post, I’ve also made the source code available in a .zip file which you can load in Code::Blocks and fiddle with yourself. I’ll be doing a bit of updating soon to make the code slightly cleaner and allow pricing of more interesting options.

The old console project source code is still available in compiled form and with the source code in a zip file at the bottom of the MC pricers page.

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.


  • 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



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

Factoryised C++

In two of my most recent posts I’ve talked about Forward Contracts and an implementation of the Normal Quantile function. One strategic reason for this will become apparent from today’s post!

I’m going to be talking here about one of the design patterns used in the Monte Carlo code presented on my blog and discussed recently. It isn’t really my goal for this blog to dwell heavily on programming topics as there are many, many more competent people than me sharing their knowledge on the subject across the web. However, there are some topics that are quite interesting to discuss and make ideal blog topics, and a grounding in Object-Oriented programming (particularly in C++) is vital for the modern Quant.

As I stated before, the goal of the code presented to date in ‘Simple Option Pricer 1.0’ is to choose a fairly simple option payoff, choose a method for generating uniform random numbers, and then choose a method for generating gaussian variates from these (and of course to enter the relevant market parameters like discount factor and volatility). The first thing to do is to download all of the code presented on that page, compile it, and run it a few times to make sure you can get all of it to work. Check the answers correspond to the results you get from the analytical pricers for the same parameters, although don’t forget that there will be some Monte Carlo error on the answers coming for C++, and observe that answers get better (though time taken increases!) as the number of paths is increased.

A very straight-forward way of achieving selectivity would be to use a switch statement:

int option;
SimpleOption* theOptionPointer;

cout << "Select a Contract Type: Call [1], Put [2]";
cin >> option;

switch ( option ) {
 case 1 :
  theOptionPointer = new CallOption( ... );
 case 2 :
  theOptionPointer = new PutOption( ... );
 default :
  throw("You must enter [1] or [2]!");

where I’ve assumed that we’ve defined some suitable option classes elsewhere that all inherit from an abstract base class SimpleOption (if there is demand for it I might go over inheritance another time, but there are many sources on the web. This IS important for quants, but it’s rather dry to go over!).

The switch statement looks at the user-entered value and assigns the pointer to an option of the relevant type. This is a basic example of polymorphism – we’ve declared theOptionPointer to be a pointer to the abstract base class SimpleOption, but then given it the address of a specific implementation of the base class via a derived class, either CallOption or PutOption. If we were to call any functions of this pointer, they would have to be those functions defined in the base class itself (although they could be abstract and potentially over-loaded in the derived classes) – if, for example, CallOption had a method called getStrike(), there would need to be a [potentially abstract] prototype for this in the base class SimpleOption if we want to have access to it via the SimpleOption pointer..

However, the code isn’t that great as it stands. We’re going to need a switch statement for each of the choices we’re giving the user (currently Option type, RNG type and Gaussian converter type), which will lead to a bloated main(){} function. More annoyingly, every time we want to include a new option in any of these selections, as well as coding up the relevant classes for the new choice we’re going to need to go into the main function and alter the choices and control structures given by the switch statements. As well as being a bother, this will mean the whole project will need to be re-compiled, and for large projects this can be a significant time constraint. You can see roughly how much time this takes by comparing the speed of Build/Compiling your project (which checks to see if changes have been made, and then only rebuilds the files that are affected) with the time taken to Re-Build/Re-Compile your project (which throws away previously compiled files and starts again from the beginning). Give this a try!

I’ve got around this in ‘Simple Option Pricer 1.0’ by coding up a basic Factory for each of the three selections, based on the Factory presented in Chapter 10 of Joshi’s text, C++ Design Patterns and Derivatives Pricing (this is one of my favourite textbooks on the subject – I’ll do some book reviews of other gems in the future). You can see how this works by downloading the following four files:

Drop these into the same folder as the rest of the files in ‘Simple Option Pricer 1.0’, and then right-click on the Project in your development environment and add them to the project one-by-one [Nb. if you’re using Visual Studio, you may need to add

#include "stdafx.h"

to the two .cpp files before this will work – and make sure they’re in the same folder as the rest, this can cause problems!!].

Now, Build/Compile your project. If you’ve done everything correctly, it should happen very swiftly – only the forward.cpp and the gaussianCdfInverter.cpp will need to compile, and the linker will need to run, but the other files won’t need to be recompiled (this is the real strength of the method, by the way!). Now, re-run the programme and you should discover two new options available to you for the option pricer! How have we achieved this sorcery?!

In the files optionFactory.cpp and optionFactory.h (and the corresponding files for the RNG and gaussian converters), we’ve defined the factories for the different types of classes we want to use. I’ve used the Singleton Pattern (for brevity I’m not going to discuss it here, but do have a look at wikipedia) to create and access a single instance of each factory. The important point is that there will be a single instance of each factory, and we can get their addresses in memory using the following method at any point in the code


The factories themselves store a two column table (implemented with a map) which links a string (the option name) in the first column with a function that constructs an option of that type and returns a pointer to it in the second column (this will be a base class pointer, using polymorphism as discussed above).

The functions that will create these pointers are implemented using another type of polymorphism – templatisation. In the optionFactoryHelper.h header, I’ve defined a new class which is constructed by calling it with a string. It also has a method which builds a new instance of the <class T>, and returns a pointer to it – which is exactly what we said the factory table needs.

template <class T>
simpleOption* simpleOptionHelper<T>::buildOption(double strike) {
 return new T(strike);

The constructer for the helper function registers this method in the factory’s table, along with the string.

template <class T>
simpleOptionHelper<T>::simpleOptionHelper(std::string optionType){
 optionFactory& theFactoryAddress = optionFactory::factoryAddress();
 theFactoryAddress.registerOption(optionType, simpleOptionHelper<T>::buildOption);

Options are registered in their .cpp files, the simple code to register the forward is at the top of forward.cpp, creating a new instance of the helper class templatised on the specific instance of the option that we are registering through it. This will call the helper class constructor, which as we just saw contains code to register the corresponding option type with the factory

namespace {
 simpleOptionHelper<forward> registerForward("forward");

Since these are done in namespaces, they happen before main(){} starts running – and once registered, we can call the factory at any time in our code using a string and giving it the required parameters and it will create a new instance of the derived class with that name

simpleOption* thisOptionPointer = optionFactory::factoryAddress().createOption( optionType, strike );

I’ll use this pattern again and again in future as more and more options are added to the code. At some point in the future, I’ll look at a way of combining ALL of the factories into a single, templatised factory process using more polymorphism, but the complication will be that since different processes need different arguments for their constructors, I’ll need to build a generic container class first that could contain many different sorts of arguments inside it. The next thing I want to do, however, is look at easier ways of data input and output, as the current console application is very clunky and wouldn’t be suitable for a large amount of data input. I’ll discuss ways of dealing with this in future posts.

New Section: Monte Carlo in C++

Good news, this blog has a new section – Monte Carlo pricers! I’m going to experiment to find the most convenient way of making it available for download, and would like it to be available both as a compiled .exe and also as uncompiled source code to allow readers to examine and alter it.

The first iteration can be found online now in the MONTE CARLO section. It is code for a straight-forward Monte Carlo pricer that will price calls and puts, with uniform random variates selected by park-miller and converted into gaussian variates using one of the box-muller processes (for more information on these, refer to the post on Random Numbers).

I’ve used a factory pattern so in principle it should be very straight forward to add new options and also RNG processes to the code, although there are a few more improvements that I will be making in the near future, which will extend the code to basic path-dependent options and allow easier data input by the user. I’ll be going over various aspects of the code in detail in future posts, and also putting together some instructions on how to compile the code yourself using Dev C++ (a free, open-source compiler) and how to add options and processes via the factory.

As always, if you have any problems getting the code to work, please tell me!