• C++ Programming for Financial Engineering
    Highly recommended by thousands of MFE students. Covers essential C++ topics with applications to financial engineering. Learn more Join!
    Python for Finance with Intro to Data Science
    Gain practical understanding of Python to read, understand, and write professional Python code for your first day on the job. Learn more Join!
    An Intuition-Based Options Primer for FE
    Ideal for entry level positions interviews and graduate studies, specializing in options trading arbitrage and options valuation models. Learn more Join!

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

Joined
5/2/06
Messages
11,750
Points
273
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.

oiescreen1wt9.jpg

Step 2
In your Class1.cs file, remove all existing code and replace it by the following code
C++:
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:

automationwk5.jpg

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).]

oieautomation2yt8.jpg

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.

oies1yd4.jpg

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
oies12vk8.jpg
 
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
 

Attachments

  • ExcelDNA.zip
    50.7 KB · Views: 55
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:
 
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.
 
Hello Andy,
I have request ,If possible to receive the codes in the c++, in addition of implied volatility for put option .?

thank you !
 
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.
 
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.
 

Attachments

  • Addin.zip
    331 KB · Views: 265
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.
 
Demyan,

Are you using your work computer?

You might need to wrap everything in an XLA
 
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
 
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
 
Back
Top