How to create UDF in Excel using C# and Visual Studio 2008

[Andy Nguyen]

Inspired by many posts on Wilmott asking how to write user defined functions (UDF) for Excel using .NET (or C++ or C#) and the lackluster responses there, I decided to put together a detailed tutorial on how to create one using C# in Visual Studio 2008 for Excel 2007 and Excel XP, Excel 2003.

I got most of the instruction from this blog

It has a good guide for Visual Studio 2005 and Excel 2003. Since I already wrote some Black-Scholes option pricer in C#, this is a good chance to reuse those code and see if it works in the latest versions of VS, Office.

If you don't have Visual Studio 2008, you can download the Express version for free. Much of this tutorial should also apply to earlier versions of Visual Studio and Excel.

Step 1
First, launch Visual Studio 2008 and create a new C# class library project called BlackScholesAddin for this example.

Step 2
In your Class1.cs file, remove all existing code and replace it by the following code

using System;
using System.Runtime.InteropServices;
using Microsoft.Win32;
 
namespace BlackScholesAddin
{
    [ClassInterface(ClassInterfaceType.AutoDual)]
    [ComVisible(true)]
    public class Functions
    {
        public Functions()
        {
        }
 
        //cumulative normal distribution function
        private double CND(double X)
        {
            double L = 0.0;
            double K = 0.0;
            double dCND = 0.0;
            const double a1 = 0.31938153;
            const double a2 = -0.356563782;
            const double a3 = 1.781477937;
            const double a4 = -1.821255978;
            const double a5 = 1.330274429;
            L = Math.Abs(X);
            K = 1.0 / (1.0 + 0.2316419 * L);
            dCND = 1.0 - 1.0 / Math.Sqrt(2 * Convert.ToDouble(Math.PI.ToString())) *
                Math.Exp(-L * L / 2.0) * (a1 * K + a2 * K * K + a3 * Math.Pow(K, 3.0) +
                a4 * Math.Pow(K, 4.0) + a5 * Math.Pow(K, 5.0));
 
            if (X < 0)
            {
                return 1.0 - dCND;
            }
            else
            {
                return dCND;
            }
        }
 
        //function phi
        private double phi(double x)
        {
            double phi = 0.0;
 
            phi = Math.Exp(-x * x / 2) / Math.Sqrt(2 * Math.PI);
            return phi;
        }
 
        //implied volatility using Newton-Raphson method
        public double blsimpvCall(double Price, double Strike, double Rate, double Time, double Value, double Yield)
        {
            const double ACCURACY = 1.0e-6;
 
            double ComputedVolatility = Math.Pow(Math.Abs(Math.Log(Price / Strike) + Rate * Time) * 2 / Time, 0.5); // initial value of volatility
            double ComputedValue = blsCall(Price, Strike, Rate, Time, ComputedVolatility, Yield);
            double Vega = blsvega(Price, Strike, Rate, Time, ComputedVolatility, Yield);
 
            while (Math.Abs(Value - ComputedValue) > ACCURACY)
            {
                ComputedVolatility = ComputedVolatility - ((ComputedValue - Value) / Vega);
                ComputedValue = blsCall(Price, Strike, Rate, Time, ComputedVolatility, Yield);
                Vega = blsvega(Price, Strike, Rate, Time, ComputedVolatility, Yield);
            }
 
            return ComputedVolatility;
        }
        public double blsimpvPut(double Price, double Strike, double Rate, double Time, double Value, double Yield)
        {
            const double ACCURACY = 1.0e-6;
 
            double ComputedVolatility = Math.Pow(Math.Abs(Math.Log(Price / Strike) + Rate * Time) * 2 / Time, 0.5); // initial value of volatility
            double ComputedValue = blsPut(Price, Strike, Rate, Time, ComputedVolatility, Yield);
            double Vega = blsvega(Price, Strike, Rate, Time, ComputedVolatility, Yield);
 
            while (Math.Abs(Value - ComputedValue) > ACCURACY)
            {
                ComputedVolatility = ComputedVolatility - ((ComputedValue - Value) / Vega);
                ComputedValue = blsPut(Price, Strike, Rate, Time, ComputedVolatility, Yield);
                Vega = blsvega(Price, Strike, Rate, Time, ComputedVolatility, Yield);
            }
 
            return ComputedVolatility;
        }
        //Call pricer
        public double blsCall(double Price, double Strike, double Rate, double Time, double Volatility, double Yield)
        {
            double d1 = 0.0;
            double d2 = 0.0;
            double Call = 0.0;
 
            d1 = (Math.Log(Price / Strike) + (Rate - Yield + Volatility * Volatility / 2.0) * Time) / (Volatility * Math.Sqrt(Time));
            d2 = d1 - Volatility * Math.Sqrt(Time);
 
            Call = Price * Math.Exp(-Yield * Time) * CND(d1) - Strike * Math.Exp(-Rate * Time) * CND(d2);
            return Call;
        }
 
        //Put pricer
        public double blsPut(double Price, double Strike, double Rate, double Time, double Volatility, double Yield)
        {
            double d1 = 0.0;
            double d2 = 0.0;
            double Put = 0.0;
 
     
            d1 = (Math.Log(Price / Strike) + (Rate - Yield + Volatility * Volatility / 2.0) * Time) / (Volatility * Math.Sqrt(Time));
            d2 = d1 - Volatility * Math.Sqrt(Time);
 
            Put = Strike * Math.Exp(-Rate * Time) * CND(-d2) - Price * Math.Exp(-Yield * Time) * CND(-d1);
            return Put;
        }
 
        //delta for Call
        public double blsdeltaCall(double Price, double Strike, double Rate, double Time, double Volatility, double Yield)
        {
            double d1 = 0.0;
 
            d1 = (Math.Log(Price / Strike) + (Rate - Yield + Volatility * Volatility / 2.0) * Time) / (Volatility * Math.Sqrt(Time));
 
            return Math.Exp(-Yield * Time) * CND(d1);
        }
 
        //delta for Put
        public double blsdeltaPut(double Price, double Strike, double Rate, double Time, double Volatility, double Yield)
        {
            double d1 = 0.0;
 
            d1 = (Math.Log(Price / Strike) + (Rate - Yield + Volatility * Volatility / 2.0) * Time) / (Volatility * Math.Sqrt(Time));
 
            return Math.Exp(-Yield * Time) * CND(d1) - 1;
        }
 
        //gamma is the same for Put and Call
        public double blsgamma(double Price, double Strike, double Rate, double Time, double Volatility, double Yield)
        {
            double d1 = 0.0;
 
            d1 = (Math.Log(Price / Strike) + (Rate - Yield + Volatility * Volatility / 2.0) * Time) / (Volatility * Math.Sqrt(Time));
 
            return Math.Exp(-Yield * Time) * phi(d1) / (Price * Volatility * Math.Sqrt(Time));
        }
 
        //vega is the same for Put and Call
        public double blsvega(double Price, double Strike, double Rate, double Time, double Volatility, double Yield)
        {
            double d1 = 0.0;
 
            d1 = (Math.Log(Price / Strike) + (Rate - Yield + Volatility * Volatility / 2.0) * Time) / (Volatility * Math.Sqrt(Time));
            return Price * Math.Exp(-Yield * Time) * phi(d1) * Math.Sqrt(Time);
        }
 
        //theta for Call
        public double blsthetaCall(double Price, double Strike, double Rate, double Time, double Volatility, double Yield)
        {
            double d1 = 0.0;
            double d2 = 0.0;
 
            d1 = (Math.Log(Price / Strike) + (Rate - Yield + Volatility * Volatility / 2.0) * Time) / (Volatility * Math.Sqrt(Time));
            d2 = d1 - Volatility * Math.Sqrt(Time);
            return -Math.Exp(-Yield * Time) * Price * phi(d1) * Volatility / (2 * Math.Sqrt(Time)) - Rate * Strike * Math.Exp(-Rate * Time) * CND(d2) + Yield * Price * Math.Exp(-Yield * Time) * CND(d1);
        }
 
        //theta for Put
        public double blsthetaPut(double Price, double Strike, double Rate, double Time, double Volatility, double Yield)
        {
            double d1 = 0.0;
            double d2 = 0.0;
 
            d1 = (Math.Log(Price / Strike) + (Rate - Yield + Volatility * Volatility / 2.0) * Time) / (Volatility * Math.Sqrt(Time));
            d2 = d1 - Volatility * Math.Sqrt(Time);
            return -Math.Exp(-Yield * Time) * Price * phi(d1) * Volatility / (2 * Math.Sqrt(Time)) + Rate * Strike * Math.Exp(-Rate * Time) * CND(-d2) - Yield * Price * Math.Exp(-Yield * Time) * CND(-d1);
        }
 
        //rho for Call
        public double blsrhoCall(double Price, double Strike, double Rate, double Time, double Volatility, double Yield)
        {
            double d1 = 0.0;
            double d2 = 0.0;
 
            d1 = (Math.Log(Price / Strike) + (Rate - Yield + Volatility * Volatility / 2.0) * Time) / (Volatility * Math.Sqrt(Time));
            d2 = d1 - Volatility * Math.Sqrt(Time);
            return Strike * Time * Math.Exp(-Rate * Time) * CND(d2);
        }
 
        //rho for Put
        public double blsrhoPut(double Price, double Strike, double Rate, double Time, double Volatility, double Yield)
        {
            double d1 = 0.0;
            double d2 = 0.0;
 
            d1 = (Math.Log(Price / Strike) + (Rate - Yield + Volatility * Volatility / 2.0) * Time) / (Volatility * Math.Sqrt(Time));
            d2 = d1 - Volatility * Math.Sqrt(Time);
            return -Strike * Time * Math.Exp(-Rate * Time) * CND(-d2);
        }
 
        //volga is the same for Call and Put
        public double blsvolga(double Price, double Strike, double Rate, double Time, double Volatility, double Yield)
        {
            double d1 = 0.0;
            double d2 = 0.0;
 
            d1 = (Math.Log(Price / Strike) + (Rate - Yield + Volatility * Volatility / 2.0) * Time) / (Volatility * Math.Sqrt(Time));
            d2 = d1 - Volatility * Math.Sqrt(Time);
            return Price * Math.Exp(-Yield * Time) * phi(d1) * Math.Sqrt(Time) * d1 * d2 / Volatility;
 
        }
 
        //vanna is the same for Call and Put
        public double blsvanna(double Price, double Strike, double Rate, double Time, double Volatility, double Yield)
        {
            double d1 = 0.0;
            double d2 = 0.0;
            double vanna = 0.0;
 
            d1 = (Math.Log(Price / Strike) + (Rate - Yield + Volatility * Volatility / 2.0) * Time) / (Volatility * Math.Sqrt(Time));
            d2 = d1 - Volatility * Math.Sqrt(Time);
 
            vanna = -Math.Exp(-Yield * Time) * phi(d1) * d2 / Volatility;
 
            return vanna;
        }
 
        //charm for Call
        public double blscharmCall(double Price, double Strike, double Rate, double Time, double Volatility, double Yield)
        {
            double d1 = 0.0;
            double d2 = 0.0;
            double charmC = 0.0;
 
            d1 = (Math.Log(Price / Strike) + (Rate - Yield + Volatility * Volatility / 2.0) * Time) / (Volatility * Math.Sqrt(Time));
            d2 = d1 - Volatility * Math.Sqrt(Time);
 
            charmC = -Yield * Math.Exp(-Yield * Time) * CND(d1) + Math.Exp(-Yield * Time) * phi(d1) * (2 * (Rate - Yield) * Time - d2 * Volatility * Math.Sqrt(Time)) / (2 * Time * Volatility * Math.Sqrt(Time));
            return charmC;
        }
 
        //charm for Put
        public double blscharmPut(double Price, double Strike, double Rate, double Time, double Volatility, double Yield)
        {
            double d1 = 0.0;
            double d2 = 0.0;
            double charmP = 0.0;
 
            d1 = (Math.Log(Price / Strike) + (Rate - Yield + Volatility * Volatility / 2.0) * Time) / (Volatility * Math.Sqrt(Time));
            d2 = d1 - Volatility * Math.Sqrt(Time);
 
            charmP = Yield * Math.Exp(-Yield * Time) * CND(-d1) - Math.Exp(-Yield * Time) * phi(d1) * (2 * (Rate - Yield) * Time - d2 * Volatility * Math.Sqrt(Time)) / (2 * Time * Volatility * Math.Sqrt(Time));
            return charmP;
        }
 
        //color is the same for Call and Put
        public double blscolor(double Price, double Strike, double Rate, double Time, double Volatility, double Yield)
        {
            double d1 = 0.0;
            double d2 = 0.0;
            double color = 0.0;
 
            d1 = (Math.Log(Price / Strike) + (Rate - Yield + Volatility * Volatility / 2.0) * Time) / (Volatility * Math.Sqrt(Time));
            d2 = d1 - Volatility * Math.Sqrt(Time);
 
            color = -Math.Exp(-Yield * Time) * (phi(d1) / (2 * Price * Time * Volatility * Math.Sqrt(Time))) * (2 * Yield * Time + 1 + (2 * (Rate - Yield) * Time - d2 * Volatility * Math.Sqrt(Time)) * d1 / (2 * Time * Volatility * Math.Sqrt(Time)));
            return color;
        }
 
        //dual delta for Call
        public double blsdualdeltaCall(double Price, double Strike, double Rate, double Time, double Volatility, double Yield)
        {
            double d1 = 0.0;
            double d2 = 0.0;
            double ddelta = 0.0;
 
            d1 = (Math.Log(Price / Strike) + (Rate - Yield + Volatility * Volatility / 2.0) * Time) / (Volatility * Math.Sqrt(Time));
            d2 = d1 - Volatility * Math.Sqrt(Time);
            ddelta = -Math.Exp(-Rate * Time) * CND(d2);
            return ddelta;
        }
 
        //dual delta for Put
        public double blsdualdeltaPut(double Price, double Strike, double Rate, double Time, double Volatility, double Yield)
        {
            double d1 = 0.0;
            double d2 = 0.0;
            double ddelta = 0.0;
 
            d1 = (Math.Log(Price / Strike) + (Rate - Yield + Volatility * Volatility / 2.0) * Time) / (Volatility * Math.Sqrt(Time));
            d2 = d1 - Volatility * Math.Sqrt(Time);
            ddelta = Math.Exp(-Rate * Time) * CND(-d2);
            return ddelta;
        }
 
        //dual gamma is the same for Call and Put
        public double blsdualgamma(double Price, double Strike, double Rate, double Time, double Volatility, double Yield)
        {
            double d1 = 0.0;
            double d2 = 0.0;
            double dgamma = 0.0;
 
            d1 = (Math.Log(Price / Strike) + (Rate - Yield + Volatility * Volatility / 2.0) * Time) / (Volatility * Math.Sqrt(Time));
            d2 = d1 - Volatility * Math.Sqrt(Time);
 
            dgamma = Math.Exp(-Rate * Time) * phi(d2) / (Strike * Volatility * Math.Sqrt(Time));
            return dgamma;
        }
 
        [ComRegisterFunctionAttribute]
        public static void RegisterFunction(Type type)
        {
 
            Registry.ClassesRoot.CreateSubKey(
 
              GetSubKeyName(type, "Programmable"));
 
            RegistryKey key = Registry.ClassesRoot.OpenSubKey(
 
              GetSubKeyName(type, "InprocServer32"), true);
 
            key.SetValue("",
 
              System.Environment.SystemDirectory + @"\mscoree.dll",
 
              RegistryValueKind.String);
        }
 
        [ComUnregisterFunctionAttribute]
        public static void UnregisterFunction(Type type)
        {
 
            Registry.ClassesRoot.DeleteSubKey(
 
              GetSubKeyName(type, "Programmable"), false);
        }
 
        private static string GetSubKeyName(Type type,
 
          string subKeyName)
        {
 
            System.Text.StringBuilder s =
 
              new System.Text.StringBuilder();
 
            s.Append(@"CLSID\{");
 
            s.Append(type.GUID.ToString().ToUpper());
 
            s.Append(@"}\");
 
            s.Append(subKeyName);
 
            return s.ToString();
 
        }
 
    }
}

Step 3
With this code written, show the properties for the project by double clicking on the properties node under the project in Solution Explorer. Click on the Build tab and check the check box that says "Register for COM Interop". Then choose "Build" to build the add-in. ]
Step 4]
Add the UDF we just created to the list of Excel addins

For Excel 2007
Launch Excel and click on the main button at the top left corner and click on Excel Options. Select Add-Ins on the left menu and at the bottom drop down menu, select Excel Add-ins and click Go. Click on the Automation button. You can find the class you created by looking for BlackScholesAddin.Functions in the list of Automation add-ins:

For Excel 2003, XP
Launch Excel and go to the Tools, Add-ins dialog in Excel and click on the Automation button. You can find the class you created by looking for BlackScholesAddin.Functions in the list of Automation add-ins:

By clicking OK in this dialog, you add BlackScholesAddin.Functions to the list of installed add-ins as shown here. You might get a dialog at this point about mscoree.dll. Click No to this dialog (Yes will delete the add-in from the list).]

Step 5
Now, let's try to use all the functions blsCall inside Excel. First create an empty spreadsheet. Click on an empty cell in the workbook and then click on the Insert Function button in the formula bar. From the dialog of available formulas, drop down the "Or select a category" drop down box and choose "BlackScholesAddin.Functions". Then click on the blsCall function as shown here:

Note: I use the same notation as used by Matlab Black-Scholes functions for option price and Greeks. More on it can be found here

Note 2: As noted by chancea if BlackScholesAddin.Functions does not appear in the Automation list (in his installation of VS2010 sp1 and Excel 2007), change the project properties from using .NET 4.0 to 3.5.

Step 6
Use it just like regular Excel builtin functions.
Option pricer
blsCall(Price, Strike, Rate, Time, Volatility, Yield)
blsPut(Price, Strike, Rate, Time, Volatility, Yield)

Greeks
Delta
blsdeltaCall(Price, Strike, Rate, Time, Volatility, Yield)
blsdeltaPut(Price, Strike, Rate, Time, Volatility, Yield)

Theta
blsthetaCall(Price, Strike, Rate, Time, Volatility, Yield)
blsthetaPut(Price, Strike, Rate, Time, Volatility, Yield)

Rho
blsrhoCall(Price, Strike, Rate, Time, Volatility, Yield)
blsrhoPut(Price, Strike, Rate, Time, Volatility, Yield)

Charm
blscharmCall(Price, Strike, Rate, Time, Volatility, Yield)
blscharmPut(Price, Strike, Rate, Time, Volatility, Yield)

Dual delta
blsdualdeltaCall(Price, Strike, Rate, Time, Volatility, Yield)
blsdualdeltaPut(Price, Strike, Rate, Time, Volatility, Yield)

Gamma
blsgamma(Price, Strike, Rate, Time, Volatility, Yield) //gamma is same for C and P

Vega
blsvega(Price, Strike, Rate, Time, Volatility, Yield) //vega is same for C and P

Vanna
blsvanna(Price, Strike, Rate, Time, Volatility, Yield) //vanna is same for C and P
Volga
blsvolga(Price, Strike, Rate, Time, Volatility, Yield) //volga is same for C and P
Color
blscolor(Price, Strike, Rate, Time, Volatility, Yield) //color is same for C and P
Dual Gamma
blsdualgamma(Price, Strike, Rate, Time, Volatility, Yield) //dual gamma is same for C and P

 

[Andy Nguyen]

Updated the first post to add implied volatility function using Newton-Raphson method

blsimpv(Price,Strike,Rate,Time,Value,Yield)

 

[Andy Nguyen]

Have read about ExcelDNA (ExcelDna) before but never tried it until today. I have to say that I'm impressed.
Without much effort, I'm able to port my C# code to a XLL that works as an addin in Excel effortlessly. I tested it with 2007 and 2002.

All I did is to bring my C# code to the dna file and run it with the provided xll file. There are little changes in the C#.

The only thing you need is .NET 2.0 (minimum). Download the 2 files into a folder in your computer. Open Excel and add this addin (or you can open the XLL with Excel) and you should be able to use all the code I have.

Do let me know if it works for you

 

[josephamarks]

thanks -- new to group

Although I'm not in NYC,
I really appreciated this page.

Your code worked the first time, which itself is a "feat".

What value (in your example) did you use for yield. This is hidden (and will result in an error, if not filled in).

Thanks.:tiphat:

 

[Andy Nguyen]

Yield is optional. You can either enter 0 or the rate (0.02 for 2%) for dividend paying stocks.

 

[balajian]

Hello Andy,
Thanks for the useful post.
Also please suggest on how to use the automated addin in a system which has only .NET framework installed(which is a min requirement).

Thanks in advance.

 

[bines]

Hello Andy,
I have request ,If possible to receive the codes in the c++, in addition of implied volatility for put option .?

thank you !

 

[Andy Nguyen]

bines,
Look at this thread SHOW ME THE CODE ! - QuantNetwork - Financial Engineering Forum
I have the implied vol for put option function there. If you want the code in C++, just copy all the functions I wrote in C# and use it with little if any modifications.

 

[Andy Nguyen]

Also please suggest on how to use the automated addin in a system which has only .NET framework installed(which is a min requirement).

I think I tried to register this dll in my coworker's computer before but couldn't do it. Either you need Visual Studio on your computer or I have to package the addin as an application the includes everything you need. I didn't have time to investigate further in either case.
If you find something online, I can try to take a look.

 

[doug reich]

I think in the original link (.NET4Office : Writing user defined functions for Excel in .NET) from which you wrote your tutorial they say how to make it portable. Rather, they imply how to make it portable, and then say "buy my book" to get the real secrets. (See the "improvements on the code" post, particularly the comments.)

 

[Andy Nguyen]

I did read that "improvement code" part this afternoon before I responded to this thread. (but I didn't read the comments part). So I guessed right that I need to create a setup project for deployment.
I'm downloading SP1 for both VS2008 and .NET 3.5 now. I'll give this a try to see what happens.

 

[Andy Nguyen]

I updated the first post with improvement from the blog. I also added a setup project. After I built the setup, I have an MSI and EXE file, none of which got the Addin to install on another computer.
I have the author's book by the way and he only mentioned it briefly.
Anyone wants to install this and let me know, feel free.

 

[sdlybug]

I've setuped the addin on my computer.But it doesn't work.

 

[sdlybug]

The ExcelDNA works~!=D>

 

[Andy Nguyen]

Sometimes, simplicity works best.

 

[Demyan]

Did not work for me, with VS 2008 and Excel 2007. Excel does not see the add-in; when I navigate to the DLL through 'Browse', I get the following error:

'The file you selected does not contain a new Automation Server, or you do not have sufficient privileges to register the Automation Server'.

I have fiddled with VS solution's security settings, to no avail.

 

[MrDonadei]

Demyan,

Are you using your work computer?

You might need to wrap everything in an XLA

 

[AlexesDad]

xlw4 C# xlls

Hi,
I thought it may be of interest to readers of this thread that xlw4 is about to be released and it will support the creating of xlls in C#, VB.NET , hybrid C++/C# as well as continuing support for C++.
You can view demonstrations of building xlls with xlw in the following clips:

Creating a C# XLL with Visual Studio 2008 & 2005

Creating a C++ XLL with Visual Studio 2008 & 2005

Creating a hybrid C++/C# XLL with Visual Studio (Professional) 2008 & 2005

Debugging a C# XLL with Visual C# Express 2008 & 2005

Creating a C++ XLL with Code::Blocks & MinGW

XLW can be downloaded from
http://sourceforge.net/project/showfiles.php?group_id=45222&package_id=37893&release_id=683127

Let us know what you think and happy XLLing

A's Dad

 

[Andy Nguyen]

When will the xlw4 stable be released? I saw 4.0 ALPHA in the download link but if the final version is about to be out, I'll wait.

 

[AlexesDad]

We're hoping for the final release in the next 2 weeks, with a Beta release in between.
xlwDotNet, the .NET part of xlw is pretty much stable and I have had it in production on a number of Traders desks for a number of months, I know one other house using it for active development.

In the mean time any feedback and suggestions from the users here would be much appreciated.

Most people already know this but just to re-iterate, xlw is free, completely free. There is no cost for its usage either in open source software or propriety software, moreover its also free in the other sense, the source code is obtainable from the subversion repo at sourceforge.

Best
A's Dad