## Tuesday, March 25, 2014

### Creating C# NAG random numbers to Excel

There has already been a couple of blog postings on generating and using random numbers in VBA. However, VBA is not efficient tool for this task and the grass might be a bit greener on the other side of the fence. Moreover, world of randomness always seems to get more interesting and lately, I have discovered low-discrepancy sequences offering some great remedy for slow convergence disease in Monte Carlo applications.

In this posting, we will be
• getting familiar with NAG .NET library, specifically its class G05 for generators for creating pseudo-random numbers and low-discrepancy sequences.
• creating easy-to-use C# IRandom interface and its two implementations, which are effectively wrapping specific NAG methods for generating random numbers.
• interfacing our C# program with Excel by using Excel-DNA.

## COOLED BY RANDOMNESS

Generating random numbers is a serious business. A fundamental difference in random number generation is the one between pseudorandom numbers and low-discrepancy sequence. Statistical properties of pseudo-random numbers are very close to "true" random numbers. However, low-discrepancy sequences are designed to give a more even distribution in space, because each number in sequence is designed to be maximally avoiding of the others. For this reason, they are preferred choice for Quasi Monte Carlo methods. This method may use less sample paths to converge to a true value faster than method using pseudo-random numbers.

## NAG

Numerical Algorithms Group has been developing impressive collection of numerical libraries with already proven industrial strength. The company itself has over forty years of experience and staffed with experts from the fields of mathematics and statistics. The documentation of class methods is, without a doubt, the best I have seen. Also, there are a lot of useful "copy-paste-run" examples, just to get you started. Also, NAG customer support is one of the best I have experienced. Support people were studying and solving my problem, instead of just trying to shake me off.

As a teaser, NagLibrary namespace for .NET is presented in the picture below.

Unfortunately, as with all the best things in our life, also NAG is not free. However, the company is offering a trial period, before you make any purchasing decision for the product.

## NAG random  number generators

At this posting, we are concentrating on NAG .NET library class G05 for generating random numbers. Class G05 contains numerous different methods for this purpose, but we are using only two specific methods in this posting. We are going to create easy C# wrapper classes for the following methods.
• g05sk  which generates a vector of pseudorandom numbers taken from a normal distribution. We will use Mersenne Twister algorithm for creating pseudorandom numbers.
• g05ym which generates a uniformly distributed low-discrepancy sequence. We will use Sobol sequence for creating low-discrepancy sequence.
After we have created those C# wrapper classes for using these methods, we are going to interface everything back to Excel with Excel-DNA. The scheme what we are using here for Excel interfacing, has been thoroughly covered and explained in this posting.

## STEP ONE: C# program

Create a new C# class project (RandomGenerator). First, we are going to create interface (IRandom) for random class implementations as shown in the picture below. This interface has only one public method - getRandom. For this method, we define the size of random matrix (two dimensional array) we would like to receive (number of random numbers and number of dimensions). This method is then returning required matrix, filled with random numbers.

```using System;
//
namespace RandomGenerator
{
public interface IRandom
{
double[,] getRandom(int rows, int cols);
}
}
```

Next, we are going to create two different interface implementations for our IRandom interface. The first implementation is using Mersenne Twister algorithm for creating a set of pseudo-random numbers. Class implementation is shown in the picture below. Insert a new class (NAG_mersenneTwister) into your project and copyPaste the code into it. Remember also to add reference to NagLibrary32.dll file which you will find in your NAG folder.

```using System;
using NagLibrary;
//
namespace RandomGenerator
{
// wrapper for NAG mersenne twister pseudorandom numbers generator
public class NAG_mersenneTwister : IRandom
{
private const int generator = 3; // hard-coded mersenne twister generator type
private const int subGenerator = 1;
private const double mean = 0.0;
private const double var = 1.0;
//
public double[,] getRandom(int nRows, int nCols)
{
double[,] rnd = new double[nCols, nRows];
int errorState = 0;
G05.G05State g05State = new G05.G05State(generator, subGenerator, out errorState);
if (errorState != 0) throw new Exception("g05State error");
//
double[] temp = new double[nRows];
for (int i = 0; i < nCols; i++)
{
G05.g05sk(nRows, mean, var, g05State, temp, out errorState);
if (errorState != 0) throw new Exception("g05sk error");
//
for (int j = 0; j < nRows; j++)
{
rnd[i, j] = temp[j];
}
}
return rnd;
}
}
}
```

The second implementation is using Sobol sequence for creating a set of low-discrepancy numbers. Class implementation is shown in the picture below. Insert a new class (NAG_sobolSequence) into your project and copyPaste the code into it.

```using System;
using NagLibrary;
//
namespace RandomGenerator
{
// wrapper for NAG sobol sequence generator
public class NAG_sobolSequence : IRandom
{
private const int generatorType = 1; // hard-coded sobol sequence generator type
private const int skipFirstItems = 2500;
private const int returnOrder = 1;
//
// estimated coefficients for rational approximations
// used when transforming uniform variates to normal
private const double a1 = -39.6968302866538;
private const double a2 = 220.946098424521;
private const double a3 = -275.928510446969;
private const double a4 = 138.357751867269;
private const double a5 = -30.6647980661472;
private const double a6 = 2.50662827745924;
//
private const double b1 = -54.4760987982241;
private const double b2 = 161.585836858041;
private const double b3 = -155.698979859887;
private const double b4 = 66.8013118877197;
private const double b5 = -13.2806815528857;
//
private const double c1 = -0.00778489400243029;
private const double c2 = -0.322396458041136;
private const double c3 = -2.40075827716184;
private const double c4 = -2.54973253934373;
private const double c5 = 4.37466414146497;
private const double c6 = 2.93816398269878;
//
private const double d1 = 0.00778469570904146;
private const double d2 = 0.32246712907004;
private const double d3 = 2.445134137143;
private const double d4 = 3.75440866190742;
//
// break points for transformation function
const double p_low = 0.02425;
const double p_high = 1 - p_low;
//
public double[,] getRandom(int nRows, int nCols)
{
int errorStatus = 0;
int n = 32 * nCols + 7;
int[] initializationInformation = new int[n];
double[,] rnd = new double[nCols, nRows];
//
// initialize sobol quasi-random numbers generator
G05.g05yl(generatorType, nCols, initializationInformation, skipFirstItems, out errorStatus);
if (errorStatus != 0) throw new Exception("g05yl error");
//
// generate uniformly-distributed quasi-random numbers
G05.g05ym(nRows, returnOrder, rnd, initializationInformation, out errorStatus);
if (errorStatus != 0) throw new Exception("g05ym error");
//
// transformation into normally-distributed quasi-random numbers
for (int i = 0; i < nCols; i++)
{
for (int j = 0; j < nRows; j++)
{
rnd[i, j] = normsinv(rnd[i, j]);
}
}
// return result matrix
return rnd;
}
//
private double normsinv(double u)
{
// transform uniformly-distributed number into normal
double q, r;
double n = 0.0;
//
// throw an error if a given uniform number is out of bounds
if ((u <= 0.0) || (u >= 1.0)) throw new Exception("given uniform number is out of bounds");
//
if (u < p_low)
{
// rational approximation for lower region
q = Math.Sqrt(-2.0 * Math.Log(u));
n = (((((c1 * q + c2) * q + c3) * q + c4) * q + c5) * q + c6) /
((((d1 * q + d2) * q + d3) * q + d4) * q + 1);
goto exitPoint;
}
//
if (u <= p_high)
{
// rational approximation for mid region
q = u - 0.5;
r = q * q;
n = (((((a1 * r + a2) * r + a3) * r + a4) * r + a5) * r + a6) * q /
(((((b1 * r + b2) * r + b3) * r + b4) * r + b5) * r + 1);
goto exitPoint;
}
//
if (u < 1.0)
{
// rational approximation for upper region
q = Math.Sqrt(-2.0 * Math.Log(1.0 - u));
n = -(((((c1 * q + c2) * q + c3) * q + c4) * q + c5) * q + c6) /
((((d1 * q + d2) * q + d3) * q + d4) * q + 1);
}
exitPoint:
return n;
}
}
}
```

The numbers received from NAG Sobol generator are mapped to uniform space. For normalizing these numbers, I have used an algorithm for computing the inverse normal cumulative distribution function, developed by Peter Acklam. At this point, we have created the parts of the program, in which are creating the actual random numbers. Next, we are going to interface our C# program with Excel by using Excel-DNA.

## STEP TWO: Interfacing C# to Excel with Excel-DNA

Interfacing is done by using Excel-DNA. Insert a new class (RandomGenerator) into your project and copyPaste the code below into it. The complete example for this particular scheme has already been fully covered and explained in here.

```using System;
using ExcelDna.Integration;
using System.Windows.Forms;
//
namespace RandomGenerator
{
public static class RandomGenerator
{
public static void execute()
{
try
{
// create Excel application object
dynamic Excel = ExcelDnaUtil.Application;
//
// use mersenne twister algorithm
NAG_mersenneTwister mt = new NAG_mersenneTwister();
double[,] MT_random = mt.getRandom(1000, 2);
Excel.Range["_mersenneTwister"] = Excel.WorksheetFunction.Transpose(MT_random);
//
// use sobol sequence
NAG_sobolSequence sobol = new NAG_sobolSequence();
double[,] MT_sobol = sobol.getRandom(1000, 2);
Excel.Range["_sobolSequence"] = Excel.WorksheetFunction.Transpose(MT_sobol);
}
catch (Exception e)
{
MessageBox.Show(e.Message.ToString());
}
}
}
}
```

At this point, we have been creating NAG random number generator wrapper classes as implementations of IRandom interface. Moreover, we have created interfacing program, which connects C# program with Excel. In this project, we are practically using Excel only as a platform for data output for C#.

## STEP THREE: Excel and VBA

Open a new Excel workbook and set the following named ranges into worksheet (Sheet1).
• Range "_mersenneTwister" (A1:B1000)
• Range "_sobolSequence" (D1:E1000)
Finally, we need to have a "triggering program", which will start the actual C# program. Insert ActiveX commandbutton into worksheet and create the following event handling program for this button.

```Option Explicit
'
Private Sub CommandButton1_Click()
Application.Run ("execute")
End Sub
'
```

Now, while this workbook is still open, doubleClick RandomGenerator.xll file in your \\RandomGenerator\bin\Release folder. After this, xll can be used by Excel and our C# function (execute) is available to be called from VBA program (Application.Run). VBA event handler program will call and start C# program execute, which will create and send the both sets of random numbers into named Excel ranges.

## STEP FOUR: Scatter plots

Next, I have plotted the both sets of random numbers into a two-dimensional space. Scatter plots are shown in the picture below. On the left side, we have random numbers created with NAG pseudo-random numbers generator (Mersenne Twister). On the right side, we have random numbers created with NAG Low-discrepancy numbers generator (Sobol sequence). The upper part is showing normalized data and the lower part is showing data mapped back to uniform plane by using Excel NORMSDIST worksheet function.

The picture clearly shows the difference between the two types of generators. The both generators are filling two-dimensional space randomly, but low-discrepancy sequence (on the right side) fills the space more uniformly, avoiding that undesired clustering effect what is appearing on the left side (pseudo-random generator).

-Mike

## Sunday, March 16, 2014

### Interfacing C# and VBA with Excel-DNA (with intellisense support)

In my previous posting, I was presenting how to create C# COM Server class and use that class in VBA without intellisense support. If this topic is completely new for you, I suggest that you check out first that posting before starting to work with this one. This posting will present a bit more complex way to do the same thing, but also including full intellisense support for your COM server class to be used in VBA.

PROJECT RESULT

The end result of this small example project will be a C# COM Server class, which can be used in VBA with full intellisense support. For this project, I have been using Visual Studio 2010 Express with Framework 4.0.

Download and unzip Excel-DNA Version 0.30 zip file to be ready when needed. There is also a step-by-step word documentation file available within the distribution folder. In this project, we are going to follow these instructions.

## STEP ONE: C# program

Create a new class project and name it to be XLServer. Also, change the class name to be XLServer. CopyPaste the following code into your XLServer.cs and save the project. At this point, do not mind about all those error messages. As we add reference to ExcelDnaIntegration.dll file, those errors will disappear.

```using System;
using ExcelDna.Integration;
using ExcelDna.ComInterop;
using System.Runtime.InteropServices;
//
namespace XLServer
{
[ComVisible(true)]
[ClassInterface(ClassInterfaceType.AutoDual)]
public class COMLibrary
{
public double add(double x, double y)
{
return x + y;
}
}
//
[ComVisible(false)]
{
public void AutoOpen()
{
ComServer.DllRegisterServer();
}
public void AutoClose()
{
ComServer.DllUnregisterServer();
}
}
}```

By adding class ExcelAddin (implements IExcelAddin interface), we are enabling the registration and unregistration of our dll file to be done programmatically, without the use of regsvr32.exe from command prompt.

Note also, that ClassInterfaceType attribute for our public COMLibrary class has been set to be AutoDual. This means, that a dual class interface is generated for this class and also exposed to COM. Class type information is produced for the class interface and published in type library file. As a part of this posting, we will create this type library file.

## STEP TWO: Excel-DNA

Add reference to Excel-DNA library (Project - Add reference - Browse - \\ExcelDna.Integration.dll) and click OK. This dll file is inside the distribution folder what we just downloaded from Excel-DNA website. From the properties of this reference, set Copy Local to be True.

Add new file as text file to project (Project - Add new item - Text file) and name it to be XLServer.dna. CopyPaste the following xml code into this file.

```<DnaLibrary Name="XLServer" RuntimeVersion="v4.0">
<ExternalLibrary Path="XLServer.dll" ComServer="true" />
</DnaLibrary>```

From the properties of this dna file, set Copy to Output Directory to be Copy if newer.

Next, from the downloaded Excel-DNA folder (Distribution), copy ExcelDna.xll file into your project folder (\\Projects\XLServer\XLServer) and rename it to be XLServer.xll. Then, add this xll file into your current project (Project - Add existing item). At this point, it might be that you do not see anything else, except cs files on this window. From drop down box on the bottom right corner of this window, select All files and you should see XLServer.xll file what we just pasted into this XLServer folder. Select this file and press Add. Finally, from the properties of this xll file, set Copy to Output Directory to be Copy if newer.

Build the solution. Everything should have gone well, without any errors or warnings. At this point, my \\XLServer\bin\Release folder looks like the following.

At this point, we are practically done with C#.

You may also notice, that ExcelDna.Integration.dll file has been copied into our release folder. This has not been an accident. To enable intellisense to be used, we need to create type library file (tlb) for our COM Server class by using Type library exporter in Visual Studio Command Prompt. Type library exporter (tlbexp.exe) needs to have that dll file in order to find all program parts it needs when creating type library file.

## STEP THREE: Creating Type Library

Next, we have to open our VS command prompt (Start - All Programs - Microsoft Visual Studio 2010 Express - Visual Studio Command Prompt 2010). Before opening, move your cursor over the name (Visual Studio Command Prompt 2010) and by right-clicking, select and open properties window. On properties, replace the existing string in Start in text box with the complete address of our current project release folder (\\VS2010\Projects\XLServer\XLServer\bin\Release).

Press Apply and OK. By setting this start path string, we are effectively opening VS command prompt directly in our project release folder. Next, open VS command prompt and write the command "tlbexp XLServer.dll" in order to create type library into project release folder.

Press enter and the following information will confirm that type library file (XLServer.tlb) has been created into current project release folder.

At this point, we can close our Visual Studio command prompt. Let us take a look at our release folder. My folder has the following files.

We can see, that XLServer.tlb file has been successfully created.

## STEP FOUR: VBA

Open a new Excel workbook, open VBA editor and create reference to COM class (Tools - References - Browse).

Select XLServer.tlb (from our release folder) and press Open. Insert a new standard VBA module and copyPaste the following code.

```Option Explicit
'
Sub tester()
'
Dim lib As New COMLibrary
Set lib = Nothing
End Sub
'
```

If we run this small program, it prints the value 25 into editor immediate window. This confirms, that our COM Server is working correctly. Moreover, we have full intellisense support for COM Server class methods now available. At this point, we are done.

## Early-binding scheme

In our example VBA program above, we are creating instance of our COM Server class by creating an object of type COMLibrary (C# class name) without using VBA CreateObject function. This scheme is called early-binding. The biggest advantage is intellisense support. Another advantage of this scheme is fast execution time (no compiler run-time checkings made). The first downside of this scheme is complex implementation compared to Late-binding scheme. Even bigger downside comes with the issues, concerning version handling safety.

## STEP FIVE: Version handling issues - a fair warning

Let us assume, that we have done everything successfully and our COM Server is working well. Now, we would like to implement a new subtract method to our COM Server class. Let us open our project and implement this method. The content of our new XLServer.cs is given below.

```using System;
using ExcelDna.Integration;
using ExcelDna.ComInterop;
using System.Runtime.InteropServices;
//
namespace XLServer
{
[ComVisible(true)]
[ClassInterface(ClassInterfaceType.AutoDual)]
public class COMLibrary
{
public double add(double x, double y)
{
return x + y;
}
public double subtract(double x, double y)
{
return x - y;
}
}
//
[ComVisible(false)]
[ClassInterface(ClassInterfaceType.None)]
{
public void AutoOpen()
{
ComServer.DllRegisterServer();
}
public void AutoClose()
{
ComServer.DllUnregisterServer();
}
}
}
```

Build the project and save results. Now, by using Excel what we created before for testing purposes, let us open its VBA editor again and try to run the program (using add method). Remember to doubleClick XLServer.xll file in your \\XLServer\bin\Release folder after opening your Excel. Now, when running that VBA program, compiler will throw run-time error 430 (Class does not support Automation or does not support expected interface).

If you check your VBA code, you can confirm that intellisense is still alive and showing all class methods for our previous version (before changes). To be able to fix this error, we need to re-create our Type Library file again. After I created my type library file again, my intellisense was showing all methods of current version.

Just for the curious, I made exactly the same changes for late-binding scheme presented in previous posting. It turned out, that after those changes, VBA was able to use both class methods (add, subtract) without any further changes or modifications. Moreover, we did not need to create any type library files either.

Conclusions: using Early-binding scheme with COM Server class is providing intellisense support and fast execution time (without run-time compiler checkings), but whenever there will be any changes or modifications made in COM Server class source, type library file needs to be re-created. Late-binding scheme does not provide intellisense support and execution time is a bit slower (run-time compiler checkings), but whenever there will be any changes or modifications made in COM Server class source, no further modifications are needed to get VBA program working.

Now, interesting question is, that exactly how much slower (due to those run-time compiler checkings) late-bound version really is, compared to early-bound version and does it really matter on our program? If this difference is not substantial, it is hard to find any reason to implement COM Server class as early-bound object. It is nice to have intellisense support, but as we see, it comes with the price. Anyway, more information on this particular topic and also possible solution for version handling issues can be found from here and from here.

## Final notes

For learning more things about Excel-DNA, check out its homepage. Getting more information and examples with your problems, the main source is Excel-DNA google group. For those who would like to see useful examples using Excel-DNA in financial programs, there is an excellent book C# for Financial Markets (chapter 22) written by Daniel Duffy and Andrea Germani (published 2013). Finally, Excel-DNA is an open-source project, and we (the happy users) can invest its future development by making a donation.

Thanks for reading again and good luck!

-Mike

## Saturday, March 15, 2014

### Interfacing C# and VBA with Excel-DNA (no intellisense support)

Some readers might be aware, that there used to be a posting under this name before. However, after getting some comments and advices directly from Govert Van Drimmelen (inventor, developer and author of Excel-DNA), and further studying the issue just a bit more, I soon realized that there is lot more than meets the eye, when dealing with COM Server classes.

Even my original implementation was technically working correctly, it was potentially dangerous way to implement this scheme. Finally, I made a decision to re-implement and re-write the whole thing again. Now, I have divided everything to two separate postings.
• This first posting presents an easy way to create interface between C# and VBA, but having no intellisense support for your COM server class used in VBA.
• The second posting will present "not so easy" way to do the same thing and having full intellisense support for your COM server class used in VBA.

PROJECT RESULT

The end result of this small example project will be a C# COM Server class, which can be used in VBA without intellisense support. For this project, I have been using Visual Studio 2010 Express with Framework 4.0.

Download and unzip Excel-DNA Version 0.30 zip file to be ready when needed. There is also a step-by-step word documentation file available within the distribution folder. In this project, we are going to follow these instructions.

## STEP ONE: C# program

Create a new class project and name it to be ExcelInterface. Also, change the class name to be InterfaceFunctions. CopyPaste the following code into your InterfaceFunctions.cs and save the project. At this point, do not mind about all those error messages. As we add reference to ExcelDnaIntegration.dll file, those errors will disappear.

```using System;
using ExcelDna.Integration;
using ExcelDna.ComInterop;
using System.Runtime.InteropServices;
//
namespace ExcelInterface
{
[ComVisible(true)]
[ClassInterface(ClassInterfaceType.AutoDispatch)]
[ProgId("CSharp_functionLibrary")]
public class InterfaceFunctions
{
public double add(double x, double y)
{
return x + y;
}
}
//
[ComVisible(false)]
{
public void AutoOpen()
{
ComServer.DllRegisterServer();
}
public void AutoClose()
{
ComServer.DllUnregisterServer();
}
}
}
```

A couple of notes about the class implementations. Note the use attributes and enumeration in our InterfaceFunctions class. ProgIDs are automatically generated for a class by combining the namespace with the type name. However, with ProgID attribute we can set our own ProgId to be used, when calling class from VBA. With ComVisible attribute, we can control the class visibility to COM. With ClassInterfaceType enumeration we can set the type of class interface that is generated for a class. It might be worth of checking those MSDN links if this topic is completely new. Finally, by adding class ExcelAddin (implements IExcelAddin interface), we are enabling the registration and unregistration of our dll file to be done programmatically, without the use of regsvr32.exe from command prompt.

## STEP TWO: Excel-DNA

Add reference to Excel-DNA library (Project - Add reference - Browse - \\ExcelDna.Integration.dll) and click OK. This dll file is inside the distribution folder what we just downloaded from Excel-DNA website. From the properties of this reference, set Copy Local to be False.

Add new file as text file to project (Project - Add new item - Text file) and name it to be ExcelInterface.dna. CopyPaste the following xml code into this file.

```<DnaLibrary Name="ExcelInterface" RuntimeVersion="v4.0">
<ExternalLibrary Path="ExcelInterface.dll" ComServer="true" />
</DnaLibrary>```

From the properties of this dna file, set Copy to Output Directory to be Copy if newer.

Next, from the downloaded Excel-DNA folder (Distribution), copy ExcelDna.xll file into your project folder (\\Projects\ExcelInterface\ExcelInterface) and rename it to be ExcelInterface .xll. Then, add this xll file into your current project (Project - Add existing item). At this point, it might be that you do not see anything else, except cs files on this window. From drop down box on the bottom right corner of this window, select All files and you should see ExcelInterface.xll file what we just pasted into this ExcelInterface folder. Select this file and press Add. Finally, from the properties of this xll file, set Copy to Output Directory to be Copy if newer.

Build the solution. Everything should have gone well, without any errors or warnings. At this point, my \\ExcelInterface\bin\Release folder looks like the following.

At this point, we are done with C#.

## STEP THREE: VBA

Open a new Excel workbook. While this workbook is open, doubleClick ExcelInterface.xll file in your \\ExcelInterface\bin\Release folder. After this, our C# function (add) is available to be used in VBA. Next, open VBA editor, insert a new standard module and copyPaste the following program.

```Option Explicit
'
Sub tester()
'
Dim lib As Object: Set lib = CreateObject("CSharp_functionLibrary")
Set lib = Nothing
End Sub
'
```

If we run this small program, it prints the value 25 into editor immediate window. This confirms, that our COM Server is working correctly. The only thing really missing is intellisense support for COM Server class methods. If you are able to live without it, then congratulations - we are done.

## LATE-BINDING SCHEME

In our example VBA program above, we are creating instance of our COM Server class by creating an object of type "CSharp_functionLibrary" (set with our custom ProgID attribute) by using VBA CreateObject function. In the case we would not be using ProgID attribute, we would create an object of type "NamespaceName.ClassName".

This scheme is called late-binding. Lacking intellisense support is one downside of this scheme. Another downside is a bit slower execution time, due to some run-time checkings made by compiler. This issue might or might not be relevant for your program. Anyway, even there is no intellisense support available, we have an access to all COM-visible methods just by calling those with the correct syntax. If the syntax is not correct, compiler throws an error. Big advantage of this scheme is, that it is relatively easy to implement. Even bigger advantage comes with the issues concerning version handling safety. This advantage will be explicit after you check out my next posting (intellisense support).

If you are unable to live without intellisense support, things are getting a bit more complicated. I try to open up and present how to add such intellisense support to be used in VBA in the next posting.

FINAL NOTES

For learning more things about Excel-DNA, check out its homepage. Getting more information and examples with your problems, the main source is Excel-DNA google group. For those who would like to see useful examples using Excel-DNA in financial programs, there is an excellent book C# for Financial Markets (chapter 22) written by Daniel Duffy and Andrea Germani (published 2013). Finally, Excel-DNA is an open-source project, and we (the happy users) can invest its future development by making a donation.

Thanks for reading again and good luck!

-Mike

## Tuesday, March 11, 2014

### Using VBA BCOM wrapper to retrieve Bloomberg surface data

I always feel a bit guilty about not responding to my readers, who might be having issues with Bloomberg API stuff. This has been clearly the most popular topic on this blog. I have been planning to release a couple of new postings concerning Bloomberg market data API, based on some new things what I have learned, while working and doing things for other people.

One such an issue was also asked by one of my blog readers a couple of months ago:

"Lets say I want to request a 6x10 FX volatility surface. I usually use BDP functions with override fields. I tried the same with your code and all I managed to do is one request per maturity/strike, which means 60 separate requests. It works but it's really time consuming. Do you have any advice for a one request code, that could allow me to build one array with all my values?"

In a nutshell, the user wants to retrieve Bloomberg volatility surface into Excel with BCOM API in a single request. With Bloomberg curves, such as USD swaps curve, you have Bloomberg ID for that curve and you can retrieve all members of that curve by using Bulk reference request and field name INDX_MEMBERS to retrieve all securities within that curve. However, for volatility surfaces, there is no such scheme available and all you have, is N amount of individual security tickers.

This means, that for a single BCOM wrapper query, we have to read all those securities from some source into a one-dimensional array. Alternatively, if we would like to process those tickers one by one (I assume the reader did this), the time elapsed for such query will be intolerable. Why? Because for each query iteration, BCOM wrapper is opening connection and starting session with BCOM server. BCOM wrapper was not originally meant to be used in repetitive data queries because of this reason.

Now, back to our original problem (60 separate requests, intolerable processing time) which is not BCOM wrapper issue, but a consequence caused by the way we might handle the input data. Let us find a way to handle this input data (tickers) in a way, which enables us to create a single request for BCOM server.

## STEP ONE: Bloomberg surface data in VCUB

I have been using Bloomberg VCUB function to get volatility surface data from Bloomberg. For example, Cap volatility surface matrix for SEK currency is presented in the picture below.

## STEP TWO: Excel data range configurations

Now, let us retrieve tickers first. In this screen, go to Actions - Export to Excel - Tickers. CopyPaste
Cap market tickers into a new Excel workbook (blue area in the picture below).

Blue area has all security tickers what we just downloaded from VCUB. Pink area will be filled with values retrieved from Bloomberg with BCOM wrapper for each security ticker. Define Excel range name for the blue area as "_input" and Excel range name for the pink area as "_output".

## STEP THREE: VBA program

Next, add new standard VBA module into your VB editor and copyPaste the following program.

```Option Explicit
'
Private Declare Function GetTickCount Lib "kernel32.dll" () As Long
Private startTime As Long
'
Private r_input As Range ' input range for Bloomberg tickers
Private r_output As Range ' output range for Bloomberg data
Private matrix As Variant ' input tickers matrix
Private result As Variant ' bloomberg results
'
Public Sub tester()
'
' start counter
startTime = GetTickCount()
'
' set range for input matrix in Excel and read values into variant array
Set r_input = Sheets("Sheet2").Range("_input")
matrix = r_input.Value2
'
' export variant array content into 2-dim array of strings
' and define Bloomberg field (only one!) to be retrieved
Dim s() As String: s = matrixToSecurities(matrix)
Dim f() As String: ReDim f(0 To 0): f(0) = "PX_MID"
'
' create BCOM instance and retrieve market data
Dim b As New BCOM_wrapper
result = b.referenceData(s, f)
'
writeMatrixToRange result, Sheets("Sheet2").Range("_output")
'
' stop counter and release BCOM object
Debug.Print ((GetTickCount - startTime) / 1000)
Set b = Nothing
End Sub
'
Private Function matrixToSecurities(ByRef matrix As Variant) As String()
'
' read values from variant array into 1-dim array
Dim nRows As Integer: nRows = UBound(matrix, 1)
Dim nCols As Integer: nCols = UBound(matrix, 2)
Dim nSecurities As Integer: nSecurities = (nRows * nCols)
Dim s() As String: ReDim s(0 To (nSecurities - 1))
Dim i As Integer, j As Integer, securityCounter As Integer
'
For i = 1 To nCols
For j = 1 To nRows
'
' yellow key is hard-coded here
s(securityCounter) = VBA.Trim(matrix(j, i)) & " Curncy"
securityCounter = securityCounter + 1
Next j
Next i
matrixToSecurities = s
End Function
'
Public Function writeMatrixToRange( _
ByRef m As Variant, _
ByRef r As Range)
'
' clear output range and write values from result matrix
' take into account rows and columns of 'original matrix'
' for this we use the information of matrix output range
' having the same dimensions as input matrix range
Dim nRows As Integer: nRows = r.Rows.Count
Dim nCols As Integer: nCols = r.Columns.Count
r.ClearContents
Dim i As Integer, j As Integer, securityCounter As Integer
'
For i = 1 To nCols
For j = 1 To nRows
r(j, i) = m(securityCounter, 0)
securityCounter = securityCounter + 1
Next j
Next i
End Function
'
```

## STEP FOUR: test run

Remember also to include BCOM wrapper into your program. Follow all instructions given in here. When we run this example program, the result should be the following.

If we cross check the corresponding values for SEK Cap volatilities (PX_MID) with Bloomberg BDP Excel worksheet functions, we should get pretty much the same values what we will get from BCOM server with wrapper. Now, if you would like to change your volatility feed (different currency or instrument type), just import new tickers from Bloomberg VCUB into your Excel, define new input range ("_input") and new output range ("_output") and run the program. In VBA program, define correct Bloomberg field name. At the moment, this has been hard-coded to be PX_MID. That's basically all you have to do.

I am not saying that this is absolutely the best way for handling this data, but it is working well and it is efficient enough, even there are some additional twists needed with data input and output. I ran 10 separate data retrieving processes with BCOM wrapper and calculated average of those processing times. For those 70 SEK tickers, processing time (from reading data to writing data) was 1.5 seconds and for example, for USD Cap volatility data (255 tickers), average processing time was 2.2 seconds.

-Mike

## Monday, March 10, 2014

### Using Excel as input/output platform for C# with Excel-DNA

This posting is presenting one cool way to use Excel-DNA. Within this scheme, we are not going to create XLL Addin worksheet function to be used in Excel, but instead we will use Excel only as a platform for data input/output for C#. This might be very useful option, if we have C# program and just want to feed it with input parameters from Excel worksheet and then return C# calculation results back to Excel for further use or analysis.

PROJECT RESULT

The end result of this small example project will be a simple C# program, which reads data from named Excel ranges, uses this data in C# program and finally returns calculation results back to named Excel ranges. For this project, I have been using Visual Studio 2010 Express with Framework 4.0.

Download and unzip Excel-DNA Version 0.30 zip file to be ready when needed. There is also a step-by-step word documentation file available within the distribution folder. In this project, we are going to follow these instructions.

STEP ONE : C# function

Create a new C# Class project "ExcelInterface". Rename created default "Class1.cs" to be "ExcelInterface.cs". For the class ExcelInterface.cs, copy the code in the box below and replace the existing code with this one.

Since we might need to use Windows Forms object in our program (MessageBox in Catch block), we need to create reference to System.Windows.Forms library (Project - Add reference - .NET - System.Windows.Forms). After this, build solution and save it.

You'll see a lot of errors at this point, but do not mind - those will disappear completely, as we create reference to Excel-DNA library next.

As we see, the only static class method execute (void) is not returning any value. Instead, it creates Excel application, reads data from named Excel ranges and after performing some calculations (discount factor df from r and t) or modifications with the data (multiplying matrix items with the value of pi), it writes results back to named Excel ranges. Note, that for objects typed with C# keyword  dynamic, we are not going enjoy any intellisense support. This means, that you have to be comfortable with Excel Object model (Application, Workbooks, Worksheets, Range, etc).

```using System;
using ExcelDna.Integration;
using System.Windows.Forms;
//
namespace ExcelInterface
{
public static class ExcelInterface
{
static double pi = Math.PI;
//
public static void execute()
{
try
{
// create Excel application object
dynamic Excel = ExcelDnaUtil.Application;
//
// read values from named ranges
double r = (double)Excel.Range["_r"].Value2;
double t = (double)Excel.Range["_t"].Value2;
//
// calculate discount factor and return it to named range
double df = Math.Exp(-r * t);
Excel.Range["_df"] = df;
//
// read matrix from named range, define its dimensions and
// create output 2-dim array
dynamic Matrix = Excel.Range["_matrix"].Value2;
int rows = Matrix.GetUpperBound(0);
int columns = Matrix.GetUpperBound(1);
double[,] result = new double[rows, columns];
//
// loop through matrix items and multiply each item with the value of pi
for (int i = 0; i < rows; i++)
{
for (int j = 0; j < columns; j++)
{
result[i, j] = (double)Matrix.GetValue(i + 1, j + 1) * pi;
}
}
//
// return result array into named range
Excel.Range["_matrix"] = result;
}
catch (Exception e)
{
MessageBox.Show(e.Message.ToString());
}
}
}
}
```

STEP TWO: Excel-DNA

Add reference to Excel-DNA library (Project - Add reference - Browse - \\ExcelDna.Integration.dll) and click OK. This dll file is inside the distribution folder what we just downloaded from Excel-DNA  website. From the properties of this reference, set Copy Local to be False.

Add new file as text file to project (Project - Add new item - Text file) and name it to be ExcelInterface.dna. CopyPaste the following xml code into this file.

```<DnaLibrary Name="ExcelInterface" RuntimeVersion="v4.0">
<ExternalLibrary Path="ExcelInterface.dll" />
</DnaLibrary>
```

From the properties of this dna file, set Copy to Output Directory to be Copy if newer.

Next, from the downloaded Excel-DNA folder (Distribution), copy ExcelDna.xll file into your project folder (\\Projects\ExcelInterface\ExcelInterface) and rename it to be ExcelInterface.xll. Then, add this xll file into your current project (Project - Add existing item). At this point, it might be that you do not see anything else, except cs files on this window. From drop down box on the bottom right corner of this window, select All files and you should see ExcelInterface.xll file what we just pasted into this ExcelInterface folder. Select this file and press Add. Finally, from the properties of this xll file, set Copy to Output Directory to be Copy if newer.

Build the solution. Everything should have gone well without any errors or warnings. At this point, my \\ExcelInterface\bin\Release folder looks like the following.

At this point, we are done with C# and Excel-DNA.

STEP THREE: Excel named ranges and "trigger program" for starting C# program

Open a new Excel workbook. From Excel Name Manager (Formulas - Name Manager), set range names (_r, _t, _df and _matrix) and initial values to these ranges as shown in the picture below.

Finally, we need to have "triggering program", which will start our actual C# program. In Excel workbook, open VBA editor. Insert a new standard VBA module and copyPaste the following code into this module.

```Option Explicit
'
Sub tester()
Application.Run ("execute")
End Sub
'
```

While this workbook is still open, doubleClick ExcelInterface.xll file in your \\ExcelInterface\bin\Release folder. After this, xll file content can be used by Excel and our C# function is available to be called from VBA program (Application.Run). VBA program tester will call and start C# program execute, which reads all input data, performs calculations and sends result data back to worksheet. We could also have one ActiveX button in our worksheet and then use Application.Run for starting C# program inside button event handler.

Run the program and we are done.

For learning more things about Excel-DNA, check out its homepage. Getting more information and examples with your problems, the main source is Excel-DNA google group. For those who would like to see useful examples using Excel-DNA in financial programs, there is an excellent book C# for Financial Markets (chapter 22) written by Daniel Duffy and Andrea Germani (published 2013). Finally, Excel-DNA is an open-source project, and we (the happy users) can invest its future development by making a donation.

-Mike

## Saturday, March 8, 2014

### Creating C# Excel Addin with Excel-DNA

In the last posting, I was presenting how to create interface between C# and VBA by using Excel-DNA. As a result, we were able to write a function in C# and use that function inside VBA with full support for intellisense. For the sake of completeness, I will also present how create Excel XLL Addin with Excel-DNA and use C# function in Excel, just like an ordinary Excel worksheet function. So let us start, once again.

PROJECT RESULT

The end result of this small example project will be a simple C# function, which can be used in Excel just like an ordinary Excel worksheet function. For this project, I have been using Visual Studio 2010 Express with Framework 4.0.

Download and unzip Excel-DNA Version 0.30 zip file to be ready when needed. There is also a step-by-step word documentation file available within the distribution folder. In this project, we are going to follow these instructions.

STEP ONE : C# function

Create a new C# Class project "FunctionLibrary". Rename created default "Class1.cs" to be "VBAFunction.cs". For the class VBAFunction.cs, copyPaste the following code and replace the existing code with this one.

```using System;
//
namespace FunctionLibrary
{
public static class VBAFunction
{
public static double add(double x, double y)
{
return x + y;
}
}
}
```

Next, build solution and remember to save this project.

STEP TWO: Excel-DNA

Add reference to Excel-DNA library (Project - Add reference - Browse - \\ExcelDna.Integration.dll) and click OK. This dll file is inside the distribution folder what we just downloaded from Excel-DNA website. From the properties of this reference, set Copy Local to be False.

Add new file as text file to project (Project - Add new item - Text file) and name it to be FunctionLibrary.dna. CopyPaste the following xml code into this file.

```<DnaLibrary Name="FunctionLibrary" RuntimeVersion="v4.0">
<ExternalLibrary Path="FunctionLibrary.dll" />
</DnaLibrary>
```

From the properties of this dna file, set Copy to Output Directory to be Copy if newer.

Next, from the downloaded Excel-DNA folder (Distribution), copy ExcelDna.xll file into your project folder (\\Projects\FunctionLibrary\FunctionLibrary) and rename it to be FunctionLibrary.xll. Then, add this xll file into your current project (Project - Add existing item). At this point, it might be that you do not see anything else, except cs files on this window. From drop down box on the bottom right corner of this window, select All files and you should see FunctionLibrary.xll file what we just pasted into this FunctionLibrary folder. Select this file and press Add. Finally, from the properties of this xll file, set Copy to Output Directory to be Copy if newer.

Build the solution. Everything should have gone well without any errors or warnings. At this point, my \\FunctionLibrary\bin\Release folder looks like the following.

At this point, we are done with C# and Excel-DNA.

STEP THREE: Excel

Open a new Excel workbook. While this workbook is open, doubleClick FunctionLibrary.xll file in your \\FunctionLibrary\bin\Release folder. After this, xll file content can be used by Excel and our C# function (add) is available like an ordinary worksheet function in Excel.

Job done. We have now successfully created Excel XLL worksheet function Addin with Excel-DNA.

STEP FOUR: Packing

Even our Addin is now fully operational, we would be facing variable amount of problems with all separate files included in the project, in the case we would like to distribute this for our clients. Frankly speaking, other people usually do not have time or interest for settling any potential issues involved with all these files received. For this purpose, we can pack all our project files into a neat package, which then can be distributed.

In C#, open FunctionLibrary.dna file, and replace its content with the following xml code.

```<DnaLibrary Name="FunctionLibrary" RuntimeVersion="v4.0">
<ExternalLibrary Path="FunctionLibrary.dll" Pack="true"/>
</DnaLibrary>
```

Now, build the project again. Next, open command prompt and write the following command:

Replace my address strings with your own address strings. In a nutshell, within the strings what I have been writing into command prompt above, there are two addresses joined (a space character between the strings): first address is to ExcelDnaPack.exe (in \\ExcelDna-0.30\Distribution folder) and second address is to FunctionLibrary.dna (in \\FunctionLibrary\bin\Release folder). After writing the strings and pressing enter, the following information appears in command prompt. If you do not see the following information, then something has gone wrong.

This information tells us, that ExcelDnaPack.exe has now successfully created fully distributable and stand-alone Excel XLL Addin. Let us take a look at my project folder \\FunctionLibrary\bin\Release.

We can see that FunctionLibrary-packed.xll file has been created into this folder. Now, this packed xll file can be freely re-named, distributed for your clients and run with no other files required. Both FunctionLibrary.dna and FunctionLibrary.dll are packed inside xll file as resources, and will be loaded at runtime. If you repeat step three (using FunctionLibrary-packed.xll), you will see that this Excel XLL Addin is working as expected.

For learning more things about Excel-DNA, check out its homepage. Getting more information and examples with your problems, the main source is Excel-DNA google group. For those who would like to see useful examples using Excel-DNA in financial programs, there is an excellent book C# for Financial Markets (chapter 22) written by Daniel Duffy and Andrea Germani (published 2013). Finally, Excel-DNA is an open-source project, and we (the happy users) can invest its future development by making a donation.