public static object acq_interpolator_tension_create(
[ExcelArgument(Description = "Nodes")] double[] x,
[ExcelArgument(Description = "Values")] double[] y,
[ExcelArgument(Description = "Tension")] double[] tension,
[ExcelArgument(Description = "Out of range value: false (num error), true (closest)")] object bounds)
{
if (ExcelDnaUtil.IsInFunctionWizard())
{
return(ExcelError.ExcelErrorRef);
}
else
{
return(ACQ.Excel.Handles.GlobalCache.CreateHandle(m_tag, new object[] { x, y, tension, bounds, "acq_interpolator_tension_create" },
(objectType, parameters) =>
{
ACQ.Math.Interpolation.InterpolationInterface interpolator = new ACQ.Math.Interpolation.ExpTensionInterpolation(x, y, tension);
interpolator.Bounds = ExcelHelper.CheckValue(bounds, true);
if (interpolator == null)
{
return ExcelError.ExcelErrorNull;
}
else
{
return interpolator;
}
}));
}
}
public static String CleanAsNMToken_ViaJni(
[ExcelArgument(Name = "InputString",
Description = "A general string")] String aInputString
)
{
if (ExcelDnaUtil.IsInFunctionWizard())
{
return("In Function Wizard, holding calls to Java");
}
if (aInputString == null || aInputString.Length == 0)
{
return("");
}
String aClassName = "com/WDataSci/WDS/Util";
IntPtr aClassID = Java.FindClassID(aClassName);
String aMethodName = "CleanAsNMToken";
String aSignatureString = "(Ljava/lang/String;)Ljava/lang/String;";
List <object> cmargs = new List <object> {
aInputString
};
String rv = "Err";
unsafe {
IntPtr aMethodID = Java.FindStaticMethodID(aClassID, aMethodName, aSignatureString);
rv = Java.CallMethod <string>(aMethodID, true, aSignatureString, cmargs);
}
cmargs = null;
return(rv);
}
public static object GetLogFileMessageTypes()
{
try
{
if (ExcelDnaUtil.IsInFunctionWizard())
{
return(String.Empty);
}
// var msgTypes = Logger.GetMessageTypes();
// int count = msgTypes.Count();
int count = 1;
object[,] excelOutput = new object[count + 1, 3];
excelOutput[0, 0] = XLResources.LogFileClassificationHeader;
excelOutput[0, 1] = XLResources.LogFileNameHeader;
excelOutput[0, 2] = XLResources.LogFileDescriptionHeader;
int i = 1;
//foreach (var msgType in msgTypes)
//{
// excelOutput[i, 0] = msgType.Classification.ToFormatString();
// excelOutput[i, 1] = msgType.Name.String;
// excelOutput[i, 2] = msgType.LongName.String;
// i++;
//}
return(ExcelDataConverter.GetExcelRangeOutput(excelOutput));
}
catch (Exception e)
{
return(ExcelDataConverter.GetExcelRangeErrorMessage(e.Message));
}
}
public static object acq_interpolator_create(
[ExcelArgument(Description = "Array of nodes")] double[] x,
[ExcelArgument(Description = "Array of values")] double[] y,
[ExcelArgument(Description = "linear, quadratic, cubic, hermite, akima, steffen etc")] object method,
[ExcelArgument(Description = "Out of range value: false (num error), true (closest)")] object bounds)
{
if (ExcelDnaUtil.IsInFunctionWizard())
{
return(ExcelError.ExcelErrorRef);
}
else
{
return(ACQ.Excel.Handles.GlobalCache.CreateHandle(m_tag, new object[] { x, y, method, bounds, "acq_interpolator_create" },
(objectType, parameters) =>
{
ACQ.Math.Interpolation.InterpolationInterface interpolator = construct_interpolator(x, y, method, bounds);
if (interpolator == null)
{
return ExcelError.ExcelErrorNull;
}
else
{
return interpolator;
}
}));
}
}
public static object acq_interpolator2d_create(
[ExcelArgument(Description = "Array of horizontal nodes")] double[] x1,
[ExcelArgument(Description = "Array of vertical nodes ")] double[] x2,
[ExcelArgument(Description = "Table of function values ")] double[,] y,
[ExcelArgument(Description = "bilinear")] object method)
{
if (ExcelDnaUtil.IsInFunctionWizard())
{
return(ExcelError.ExcelErrorRef);
}
else
{
return(ACQ.Excel.Handles.GlobalCache.CreateHandle(m_tag, new object[] { x1, x2, y, method, "acq_interpolator2d_create" },
(objectType, parameters) =>
{
ACQ.Math.Interpolation.InterpolationInterface2D interpolator = construct_interpolator(x1, x2, y, method);
if (interpolator == null)
{
return ExcelError.ExcelErrorNull;
}
else
{
return interpolator;
}
}));
}
}
// ReSharper disable UnusedMember.Global
public static object Open([ExcelArgument("Logical identifier for the connection.")] string alias,
// ReSharper restore UnusedMember.Global
[ExcelArgument(
"Hostname, fqdn or ip of the machine running q process to which connection should be established."
)] string hostname,
[ExcelArgument("Port number of the q process.")] object port,
[ExcelArgument("Username (optional).")] string username = null,
[ExcelArgument("Password (optional).")] string password = null,
// ReSharper disable UnusedParameter.Global
[ExcelArgument("reEval (optional).")] object reEval = null)
// ReSharper restore UnusedParameter.Global
{
if (ExcelDnaUtil.IsInFunctionWizard())
{
return(ExcelEmpty.Value);
}
try
{
return(_qXL.qOpen(alias, hostname, port, username, password));
}
catch (Exception e)
{
return("ERR: " + e.Message);
}
}
public static object MortgageLoan_create(
[ExcelArgument(Description = @"Balance Notional")] double Balance,
[ExcelArgument(Description = @"Loan Rate")] double Rate,
[ExcelArgument(Description = @"Loan Spread, Zero if It is a Fixed Rate Loan")] double Spread,
[ExcelArgument(Description = @"Maturity Period")] int Maturity,
[ExcelArgument(Description = @"Rate Resetting Period, the same as Maturity if it is a Fixed Rate Loan")] int Resetting,
[ExcelArgument(Description = @"Fixed (Fixed) or ARM (ARM)")] string FixedOrARM,
[ExcelArgument(Description = @"Principal and Interest (PI) or Interest Only (IO)")] string PIOrIO,
[ExcelArgument(Description = @"Libor Curve as an name")] string LiborCurve)
{
if (ExcelDnaUtil.IsInFunctionWizard())
{
return(ExcelError.ExcelErrorRef);
}
else
{
return(GlobalCache.CreateHandle(m_tag, new object[] { Balance, Rate, Spread, Maturity, Resetting, FixedOrARM, PIOrIO, LiborCurve, "MortgageLoan_create" },
(objectType, parameters) =>
{
IMortgageLoan loan = construct_loan(Balance, Rate, Spread, Maturity, Resetting, FixedOrARM, PIOrIO, LiborCurve);
if (loan == null)
{
return ExcelError.ExcelErrorNull;
}
else
{
return loan;
}
}));
}
}
public static object[,] DisplaySampleOptionDetails([ExcelArgument("the option handle", Name = "option handle")] string optHandle)
{
if (ExcelDnaUtil.IsInFunctionWizard())
{
return(new object[, ] {
{ ExcelError.ExcelErrorRef }
});
}
RandClass value;
if (GlobalCache.TryGetObject(optHandle, out value))
{
try
{
RandClass obj = (RandClass)value;
return(TradeFactory.GetInstanceAsync.Task.Result.DisplayRandClassDetails(obj).Result);
}
catch (Exception e)
{
return(new object[, ] {
{ e.Message }
});
}
}
object[,] ret = { { "!Invalid Handle!" } };
return(ret);
}
public static object acq_numdict_create_fake_data(string name)
{
if (ExcelDnaUtil.IsInFunctionWizard())
{
return(ExcelError.ExcelErrorRef);
}
else if (name == null)
{
return(ExcelError.ExcelErrorRef);
}
else
{
return(ACQ.Excel.Handles.GlobalCache.CreateHandle(Tag, new object[] { name, "acq_numdict_create_fake_data" },
(objectType, parameters) =>
{
NumDictionary dict = new NumDictionary();
System.Threading.Thread.Sleep(2000); //simulate loading from DB
for (int i = 0; i < 100; i++)
{
dict[String.Format("{0}-{1}", name, i)] = i;
}
return dict;
}));
}
}
public static object JarqueBeraTest(
[ExcelArgument(Name = "Asset Returns", Description = "Range of Asset Returns", AllowReference = false)] double[] assetReturns)
{
if (ExcelDnaUtil.IsInFunctionWizard())
//This is required because Function Wizard repeatedly calls the function and will cause an error on partial range entry for second var
//The check on lengths means that the Function Wizard will show a correct result when the lengths are equal
{
return(ExcelError.ExcelErrorValue); //Return a placeholder value until both ranges are fully entered
}
else //Try the calculation
{
try
{
double n = assetReturns.Length;
if (n > 2)
{
double skew = Helpers.Skewness_P(assetReturns);
double kurtExcess = Helpers.Kurtosis_P_Excess(assetReturns);
return(n / 6 * (Math.Pow(skew, 2) + Math.Pow(kurtExcess, 2) / 4));
}
else
{
return(ExcelError.ExcelErrorDiv0);
}
}
catch (Exception)
{
return(ExcelError.ExcelErrorValue);
}
}
}
public static object ComputeWithNetwork(
[ExcelArgument(Name = "Network", Description = "The trained neural network")]
object networkXl,
[ExcelArgument(Name = "Input", Description = "The input on which to perform the computation")]
object inputXl)
{
if (!ExcelDnaUtil.IsInFunctionWizard())
{
try
{
//Parameter resolution
var network = Arg(networkXl, "Network").GetFromStoreAs <BasicNetwork>();
var inputs = Arg(inputXl, "Inputs").As1DArray <double>();
//Computation and return
var result = network.Compute(new BasicMLData(inputs));
var retVal = new double[result.Count];
result.CopyTo(retVal, 0, result.Count);
return(retVal);
}
catch (Exception exp)
{
return("#Err - " + exp.Message);
}
}
else
{
return("...");
}
}
public static object LiborCurve_create([ExcelArgument(Description = @"Libor Curve as an array")] double[] Libor_Array_)
{
if (ExcelDnaUtil.IsInFunctionWizard())
{
return(ExcelError.ExcelErrorRef);
}
else
{
// Libor Curve
int len = Libor_Array_.Length;
Debug.Assert(len <= GlobalVar.GlobalMaxMortgageLoanMaturity, "Libor Curve should be EXACTLY of 360 data points" + GlobalVar.GlobalMaxMortgageLoanMaturity);
return(GlobalCache.CreateHandle(m_tagLibor, new object[] { Libor_Array_, len, "LiborCurve_create" },
(objectType, parameters) =>
{
LiborRates BoERate_Array = construct_LiborCurve(Libor_Array_);
if (BoERate_Array == null)
{
return ExcelError.ExcelErrorNull;
}
else
{
return BoERate_Array;
}
}));
}
}
public static double[,] DupireSsviGeneratePaths(
[ExcelArgument(Description = "the current risky asset price")] double S0,
[ExcelArgument(Description = "constant continuously compounded rate of return")] double riskFreeRate,
[ExcelArgument(Description = "parameter in theta(t) := alpha^2(e^(beta^2 t) - 1)")] double alpha,
[ExcelArgument(Description = "parameter in theta(t) := alpha^2(e^(beta^2 t) - 1)")] double beta,
[ExcelArgument(Description = "SSVI parameter")] double gamma,
[ExcelArgument(Description = "SSVI parameter")] double eta,
[ExcelArgument(Description = "SSVI parameter")] double rho,
[ExcelArgument(Description = "option maturity (time to expiry)")] double maturity,
[ExcelArgument(Description = "number of time steps e.g. 252")] int numSteps,
[ExcelArgument(Description = "number of trials e.g. 10 000")] int numTrials)
{
if (ExcelDnaUtil.IsInFunctionWizard())
{
return(null);
}
try
{
double[] ssviParams = new double[] { alpha, beta, gamma, eta, rho };
double[,] paths = MonteCarloPaths.GenerateMcPaths(numTrials, numSteps, maturity, riskFreeRate, S0, ssviParams);
return(paths);
}
catch (Exception e)
{
XLInterfaceBase.AddErrorMessage("DupireSsviGeneratePaths error: " + e.Message);
}
return(null);
}
public static object DupireSsviVolatility([ExcelArgument(Description = "the current risky asset price")] double underlyingPrice,
[ExcelArgument(Description = "constant continuously compounded rate of return")] double riskFreeRate,
[ExcelArgument(Description = "parameter in theta(t) := alpha^2(e^(beta^2 t) - 1)")] double alpha,
[ExcelArgument(Description = "parameter in theta(t) := alpha^2(e^(beta^2 t) - 1)")] double beta,
[ExcelArgument(Description = "SSVI parameter")] double gamma,
[ExcelArgument(Description = "SSVI parameter")] double eta,
[ExcelArgument(Description = "SSVI parameter")] double rho,
[ExcelArgument(Description = "option maturity (time to expiry)")] double maturity,
[ExcelArgument(Description = "option strike")] double strike)
{
// This is useful for methods that take a while to run.
if (ExcelDnaUtil.IsInFunctionWizard())
{
return(null);
}
try
{
double k = Math.Log(strike * Math.Exp(-riskFreeRate * maturity) / underlyingPrice);
Ssvi SsviSurface = new Ssvi(alpha, beta, gamma, eta, rho);
return(Math.Sqrt(SsviSurface.OmegaSsvi(maturity, k) / maturity));
}
catch (Exception e)
{
XLInterfaceBase.AddErrorMessage("DupireSsviVolatility error: " + e.Message);
}
return(FunctionError);
}
public static object HttpGet(string url, object query, object headers, object authentication, object timeout, object allowRedirects)
{
if (ExcelDnaUtil.IsInFunctionWizard())
{
return("");
}
try
{
url = Schema.Trim(url);
var httpHeaders = headers is ExcelMissing ?
new Dictionary <string, string>() :
ExcelParams.AsDictionary <string>(headers as object[, ]);
if (!(authentication is ExcelMissing))
{
var encoded = System.Convert.ToBase64String(System.Text.Encoding.GetEncoding("ISO-8859-1").GetBytes(authentication.ToString()));
httpHeaders["Authorization"] = "Basic " + encoded;
}
if (!cache.ContainsKey(url))
{
cache.Set(url, httpProvider.Get(url, httpHeaders));
}
return(url);
}
catch (Exception e)
{
return(e.Message);
}
}
public static object acq_regression_lowess_create(
[ExcelArgument(Description = "x")] double[] x,
[ExcelArgument(Description = "y")] double[] y,
[ExcelArgument(Description = "smoother span [0, 1] (optional, default 2/3)")] object span,
[ExcelArgument(Description = "number of robust iterations (optional, default 3) ")] object nsteps,
[ExcelArgument(Description = "regression step (optional)")] object delta)
{
if (ExcelDnaUtil.IsInFunctionWizard())
{
return(ExcelError.ExcelErrorRef);
}
else
{
return(ACQ.Excel.Handles.GlobalCache.CreateHandle(m_tag, new object[] { x, y, span, nsteps, delta, "acq_regression_lowess_create" },
(objectType, parameters) =>
{
ACQ.Math.Regression.Lowess lowess = construct_lowess(x, y, span, nsteps, delta);
if (lowess == null)
{
return ExcelError.ExcelErrorNull;
}
else
{
return lowess;
}
}));
}
}
public static object DupireSsviLookbackOptionPriceMC(
[ExcelArgument(Description = "the current risky asset price")] double S0,
[ExcelArgument(Description = "constant continuously compounded rate of return")] double riskFreeRate,
[ExcelArgument(Description = "parameter in theta(t) := alpha^2(e^(beta^2 t) - 1)")] double alpha,
[ExcelArgument(Description = "parameter in theta(t) := alpha^2(e^(beta^2 t) - 1)")] double beta,
[ExcelArgument(Description = "SSVI parameter")] double gamma,
[ExcelArgument(Description = "SSVI parameter")] double eta,
[ExcelArgument(Description = "SSVI parameter")] double rho,
[ExcelArgument(Description = "option maturity (time to expiry)")] double maturity,
[ExcelArgument(Description = "number of time steps e.g. 252")] int numSteps,
[ExcelArgument(Description = "number of trials e.g. 10 000")] int numTrials)
{
if (ExcelDnaUtil.IsInFunctionWizard())
{
return(null);
}
try
{
double[] ssviParams = new double[] { alpha, beta, gamma, eta, rho };
MonteCarloPricingLocalVol McPricer = new MonteCarloPricingLocalVol(riskFreeRate, numTrials, numSteps, ssviParams);
return(McPricer.CalculateLookbackOptionPrice(S0, maturity));
}
catch (Exception e)
{
XLInterfaceBase.AddErrorMessage("DupireSsviLookbackOptionPriceMC error: " + e.Message);
}
return(null);
}
public static object SetValue(string Name, object[,] Range, bool Shared, object Trigger)
{
if (Trigger is ExcelError)
{
return(Trigger);
}
Name = Name?.Trim().ToUpperInvariant();
if (String.IsNullOrEmpty(Name))
{
return(Strings.ERR("Invalid Name"));
}
if (Range.Length == 1 && Range[0, 0] is ExcelMissing)
{
return(Strings.ERR("Invalid input range"));
}
if (ExcelDnaUtil.IsInFunctionWizard())
{
return(Strings.FUNC_WIZARD);
}
for (int i = 0; i < Range.GetLength(0); ++i)
{
for (int j = 0; j < Range.GetLength(1); ++j)
{
if (Range[i, j] is ExcelError xe && xe != ExcelError.ExcelErrorNA)
{
return(Strings.ERR($"Invalid value at ({i + 1},{j + 1})"));
}
public static object acq_regression_lars_create(
[ExcelArgument(Description = "x")] double[,] x,
[ExcelArgument(Description = "y")] double[] y)
{
if (ExcelDnaUtil.IsInFunctionWizard())
{
return(ExcelError.ExcelErrorRef);
}
else
{
return(ACQ.Excel.Handles.GlobalCache.CreateHandle(m_tag, new object[] { x, y, "acq_regression_lars_create" },
(objectType, parameters) =>
{
ACQ.Math.Regression.Lars lars = new Math.Regression.Lars(x, y);
if (lars == null)
{
return ExcelError.ExcelErrorNull;
}
else
{
return lars;
}
}));
}
}
public static object acq_regression_linear_create(
[ExcelArgument(Description = "x")] double[,] x,
[ExcelArgument(Description = "y")] double[] y,
[ExcelArgument(Description = "Intercept, optional(default=true)")] object intercept,
[ExcelArgument(Description = "Weights, optional(default = none)")] object weights)
{
if (ExcelDnaUtil.IsInFunctionWizard())
{
return(ExcelError.ExcelErrorRef);
}
else
{
return(ACQ.Excel.Handles.GlobalCache.CreateHandle(m_tag, new object[] { x, y, intercept, weights, "acq_regression_linear_create" },
(objectType, parameters) =>
{
bool include_intercept = ExcelHelper.CheckValue(intercept, true);
double[] reg_weights = ExcelHelper.CheckArray <double>(weights);
ACQ.Math.Regression.LinearRegression regression = new Math.Regression.LinearRegression(x, y, reg_weights, include_intercept);
if (regression == null)
{
return ExcelError.ExcelErrorNull;
}
else
{
return regression;
}
}));
}
}
public static object acq_interpolator_scattered_create(
[ExcelArgument(Description = "nodes")] double[,] x,
[ExcelArgument(Description = "function values ")] double[] y,
[ExcelArgument(Description = "scales")] object scales,
[ExcelArgument(Description = "multiquadrics")] object method)
{
if (ExcelDnaUtil.IsInFunctionWizard())
{
return(ExcelError.ExcelErrorRef);
}
else
{
return(ACQ.Excel.Handles.GlobalCache.CreateHandle(m_tag, new object[] { x, y, scales, method, "acq_interpolator_scattered_create" },
(objectType, parameters) =>
{
ACQ.Math.Interpolation.ScatteredInterpolationInterface interpolator = construct_interpolator(x, y, scales, method);
if (interpolator == null)
{
return ExcelError.ExcelErrorNull;
}
else
{
return interpolator;
}
}));
}
}
public static object ModifiedParametricVaR(
[ExcelArgument(Name = "Asset Returns", Description = "Range of Asset Returns", AllowReference = false)] double[] assetReturns,
[ExcelArgument(Name = "Confidence Level", Description = "(Optional) The confidence level (e.g., 0.95 for 95%). If omitted, 0.95 is used.", AllowReference = false)] object confLevel)
{
if (ExcelDnaUtil.IsInFunctionWizard())
//This is required because Function Wizard repeatedly calls the function and will cause an error on partial range entry for second var
//The check on lengths means that the Function Wizard will show a correct result when the lengths are equal
{
return(ExcelError.ExcelErrorValue); //Return a placeholder value until both ranges are fully entered
}
else //Try the calculation
{
try
{
double alpha = (confLevel is ExcelMissing) ? 0.05d : (1 - (double)confLevel); //Set the alpha
double invCDF = Helpers.NormalCdfInverse(alpha, 0, 1);
double adjFactor = invCDF + ((Math.Pow(invCDF, 2) - 1) / 6) * Helpers.Skewness_P(assetReturns) +
((Math.Pow(invCDF, 3) - 3 * invCDF) / 24) * Helpers.Kurtosis_P_Excess(assetReturns) -
(2 * (Math.Pow(invCDF, 3) - 5 * invCDF) / 36) * Math.Pow(Helpers.Skewness_P(assetReturns), 2);
//Note that we use excess kurtosis here:
return(assetReturns.Average() + adjFactor * Helpers.StdDev_P(assetReturns)); //We add because the inverse cdf will be negative
}
catch (Exception)
{
return(ExcelError.ExcelErrorValue);
}
}
}
public static object HistoricalSimulationVaR(
[ExcelArgument(Name = "Asset Returns", Description = "Range of Asset Returns", AllowReference = false)] double[] assetReturns,
[ExcelArgument(Name = "Confidence Level", Description = "(Optional) The confidence level (e.g., 0.95 for 95%). If omitted, 0.95 is used.", AllowReference = false)] object confLevel)
{
if (ExcelDnaUtil.IsInFunctionWizard())
//This is required because Function Wizard repeatedly calls the function and will cause an error on partial range entry for second var
//The check on lengths means that the Function Wizard will show a correct result when the lengths are equal
{
return(ExcelError.ExcelErrorValue); //Return a placeholder value until both ranges are fully entered
}
else //Try the calculation
{
try
{
double alpha = (confLevel is ExcelMissing) ? 0.05d : (1 - (double)confLevel); //Set the alpha
Array.Sort(assetReturns);
double targetPos = alpha * (assetReturns.Length + 1) - 1; //Position that we want
if (targetPos < 0 || targetPos > (assetReturns.Length - 1))
{
//Can't interpolate if the position is less than 0 or greater than n - 1.
//Return #NUM! consistent with Percentile.Exc().
return(ExcelError.ExcelErrorNum);
}
Int32 nextPos = (Int32)Math.Floor(targetPos); //Nearest integer position less than targetPos
return(assetReturns[nextPos] + (assetReturns[nextPos + 1] - assetReturns[nextPos]) * (targetPos - nextPos)); //Interpolated result
}
catch (Exception)
{
return(ExcelError.ExcelErrorValue);
}
}
}
public static object UniqueRisk(
[ExcelArgument(Name = "Asset Returns", Description = "Range of Asset Returns", AllowReference = false)] double[] assetReturns,
[ExcelArgument(Name = "Market Returns", Description = "Range of Market Returns", AllowReference = false)] double[] mktReturns,
[ExcelArgument(Name = "Data Frequency", Description = "(Optional) Number of periods per year (annual = 1, monthly = 12, etc)", AllowReference = false)] object frequency)
{
if (ExcelDnaUtil.IsInFunctionWizard() && assetReturns.Length != mktReturns.Length)
//This is required because Function Wizard repeatedly calls the function and will cause an error on partial range entry for second var
//The check on lengths means that the Function Wizard will show a correct result when the lengths are equal
{
return(ExcelError.ExcelErrorValue); //Return a placeholder value until both ranges are fully entered
}
else //Try the calculation
{
try
{
double freq = (frequency is ExcelMissing) ? 1d : (double)frequency; //Set the frequency
double assetVariance = Helpers.Variance_P(assetReturns) * freq;
double mktRisk = (double)MarketRisk(assetReturns, mktReturns, (object)freq);
return(assetVariance - mktRisk);
}
catch (Exception)
{
return(ExcelError.ExcelErrorValue);
}
}
}
public static object UpperPartialMoment(
[ExcelArgument(Name = "Asset Returns", Description = "Range of Asset Returns", AllowReference = false)] double[] assetReturns,
[ExcelArgument(Name = "Target Return", Description = "(Optional) The target return. Only returns greater than the target are used in the calculation. " +
"If omitted, the mean is used as the target.", AllowReference = false)] object target,
[ExcelArgument(Name = "Degree", Description = "(Optional)The degree of the UPM (default is 2 for semi-variance above the target). Must be greater than or equal to 0.", AllowReference = false)] object degree,
[ExcelArgument(Name = "Data Frequency", Description = "(Optional) Number of periods per year (annual = 1, monthly = 12, etc)", AllowReference = false)] object frequency)
{
if (ExcelDnaUtil.IsInFunctionWizard())
//This is required because Function Wizard repeatedly calls the function and will cause an error on partial range entry for second var
//The check on lengths means that the Function Wizard will show a correct result when the lengths are equal
{
return(ExcelError.ExcelErrorValue); //Return a placeholder value until both ranges are fully entered
}
else //Try the calculation
{
try
{
double freq = (frequency is ExcelMissing) ? 1d : (double)frequency; //Set the frequency
double tgt = (target is ExcelMissing) ? assetReturns.Average() : (double)target; //Set the target return, by default mean
double deg = (degree is ExcelMissing) ? 2.0d : (double)degree; //Set the target degree, by default 2 for semi-variance
if (deg < 0)
{
return(ExcelError.ExcelErrorValue);
}
return(Helpers.UpperPartialMoment_P(assetReturns, tgt, deg) * freq);
}
catch (Exception)
{
return(ExcelError.ExcelErrorValue);
}
}
}
public static object Beta(
[ExcelArgument(Name = "Asset Returns", Description = "Range of Asset Returns", AllowReference = false)] double[] assetReturns,
[ExcelArgument(Name = "Market Returns", Description = "Range of Market Returns", AllowReference = false)] double[] mktReturns)
//Calculate the beta as cov(assetReturns, mktReturns)/var(mktReturns)
{
if (ExcelDnaUtil.IsInFunctionWizard() && mktReturns.Length != assetReturns.Length)
//This is required because Function Wizard repeatedly calls the function and will cause an error on partial range entry for second var
//The check on lengths means that the Function Wizard will show a correct result when the lengths are equal
{
return(ExcelError.ExcelErrorValue); //Return a placeholder value until both ranges are fully entered
}
else
{
try
{
double cov = Helpers.Covariance_P(assetReturns, mktReturns);
double mktVar = Helpers.Variance_P(mktReturns);
if (mktVar > 0)
{
return(cov / mktVar);
}
else
{
return(ExcelError.ExcelErrorDiv0);
}
}
catch (Exception)
{
return(ExcelError.ExcelErrorValue);
}
}
}
public static object acq_hashtable_create(object[] keys, object[] values)
{
if (ExcelDnaUtil.IsInFunctionWizard())
{
return(ExcelError.ExcelErrorRef);
}
else if (keys == null || values == null)
{
return(ExcelError.ExcelErrorRef);
}
else
{
return(ACQ.Excel.Handles.GlobalCache.CreateHandle(ExcelHashtable.Tag, new object[] { keys, values, "acq_hashtable_create" },
(objectType, parameters) =>
{
Hashtable htable = new Hashtable();
for (int i = 0; i < keys.Length; i++)
{
htable[keys[i]] = values[i];
}
return htable;
}));
}
}
public static object acq_numdict_create(object[] keys, double[] values)
{
if (ExcelDnaUtil.IsInFunctionWizard())
{
return(ExcelError.ExcelErrorRef);
}
else if (keys == null || values == null)
{
return(ExcelError.ExcelErrorRef);
}
else
{
return(ACQ.Excel.Handles.GlobalCache.CreateHandle(Tag, new object[] { keys, values, "acq_numdict_create" },
(objectType, parameters) =>
{
NumDictionary dict = new NumDictionary();
for (int i = 0; i < keys.Length; i++)
{
dict[keys[i].ToString()] = values[i];
}
return dict;
}));
}
}
public static object LogSubPeriodReturns(
[ExcelArgument(Name = "Prices", Description = "The range of prices for the asset/portfolio.", AllowReference = false)] double[] prices,
[ExcelArgument(Name = "Cash Flows", Description = "(Optional) The range of cash flows for the asset/portfolio. " +
"Note: a cash flow at period 0 is ignored.", AllowReference = false)] object[] cashFlows)
{
if (ExcelDnaUtil.IsInFunctionWizard() && prices.Length == 0)
//This is required because Function Wizard repeatedly calls the function and will cause an error on partial range entry for second var
//The check on lengths means that the Function Wizard will show a correct result when the lengths are equal
{
return(ExcelError.ExcelErrorValue); //Return a placeholder value until both ranges are fully entered
}
else //Try the calculation
{
try
{
double[,] logReturns = new double[prices.Length - 1, 1]; //The array that is returned - a 2D array so that we can return a column
double[,] tempReturns = (double[, ])SubPeriodReturns(prices, cashFlows);
for (int i = 0; i < logReturns.Length; i++)
{
logReturns[i, 0] = Math.Log(1 + tempReturns[i, 0], Math.E);
}
return(logReturns);
}
catch (Exception)
{
return(ExcelError.ExcelErrorValue);
}
}
}
public static object SubPeriodReturns(
[ExcelArgument(Name = "Prices", Description = "The range of prices for the asset/portfolio.", AllowReference = false)] double[] prices,
[ExcelArgument(Name = "Cash Flows", Description = "(Optional) The range of cash flows for the asset/portfolio. " +
"Note: a cash flow at period 0 is ignored.", AllowReference = false)] object[] cashFlows)
{
if (ExcelDnaUtil.IsInFunctionWizard() && prices.Length == 0)
//This is required because Function Wizard repeatedly calls the function and will cause an error on partial range entry for second var
//The check on lengths means that the Function Wizard will show a correct result when the lengths are equal
{
return(ExcelError.ExcelErrorValue); //Return a placeholder value until both ranges are fully entered
}
else //Try the calculation
{
try
{
if (cashFlows.Length > prices.Length)
{
return(ExcelError.ExcelErrorValue); //Too many cash flows, return error
}
double[,] returns = new double[prices.Length - 1, 1]; //The array that is returned - a 2D array so that we can return a column
//No cash flows given
if (cashFlows.Length == 1 && cashFlows[0] is ExcelMissing)
{
for (int i = 1; i < prices.Length; i++)
{
returns[i - 1, 0] = prices[i] / prices[i - 1] - 1;
}
return(returns);
}
double[] cf = Helpers.ObjToDouble(cashFlows);
//Cash flows same length as prices
if (cf.Length == prices.Length)
{
for (int i = 1; i < prices.Length; i++)
{
returns[i - 1, 0] = (prices[i] + cf[i]) / prices[i - 1] - 1;
}
}
//Fewer cash flows than prices, so assume that cf[0] goes with prices[1], and so on.
else
{
for (int i = 1; i < prices.Length; i++)
{
returns[i - 1, 0] = (prices[i] + cf[i - 1]) / prices[i - 1] - 1;
}
}
return(returns);
}
catch (Exception)
{
return(ExcelError.ExcelErrorValue);
}
}
}
