How do I install an add-in to Excel's Automation Add-in List?
I have a C# class library project for an automation add-in for which I have created a Visual Studio set-up project.
When I run the installer, I want the add-in to appear in the Excel automation add-in list (Tools-->Addins-->Automation Addin) so that I can directly include it in my Excel application.
How do I go about it?
I created a setup project following the link here http://dreamincode.net/forums/showtopic58021.htm, but the add-in does not appear in the Automation add-in list.
Am I missing somethi开发者_如何学运维ng here?
Project properties -> Debug -> Enable the Visual Studio hosting process after run it, you will find addin that can be selected in Excel
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Runtime.InteropServices;
using Microsoft.Win32;
namespace LongAddin
{
[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);
}
[ComRegisterFunctionAttribute]
public static void RegisterFunction(Type type)
{
Registry.ClassesRoot.CreateSubKey(GetSubKeyName(type));
}
[ComUnregisterFunctionAttribute]
public static void UnregisterFunction(Type type)
{ Registry.ClassesRoot.DeleteSubKey(GetSubKeyName(type), false); } private static string GetSubKeyName(Type type) { string s = @"CLSID\{" + type.GUID.ToString().ToUpper() + @"}\Programmable"; return s; }
[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();
}
}
}
精彩评论