Excel AddIn
Releases
Current release version: 2.6
Version 2.6 (from December 17, 2019)
New and updated functionality...
 new function apaCVaR.Modified(Returns, ConfidenceLevel): Modified CVar based on a return time series.
 apaMarketData.Yahoo(): retired due to changes on the side of the data provider.
Enhancements and changes...
 Compatible with .NET framework versions up to 4.6
 Updates relating to the integration of the function library with Excel
 Various minor optimizations
Version 2.5 (from December 8, 2014)
Implementation of additional functionality (23 new functions)...
 apaTradeProfile.ConditionalDrawdown(), apaTradeProfile.Drawdown(), apaTradeProfile.CVaR(), apaTradeProfile.VaR(): trade profiles for various lossbased risk measures.
 apaCorrels.Partial.Quantile(), apaCorrels.Partial.ArithmeticMean(): calculating conditional correlation matrices.
 apaMean.ConfidenceInternvals(), apaVolatility.ConfidenceIntervals(), apaSkewness.ConfidenceIntervals(), apaKurtosis.ConfidenceIntervals(), apaCorrelation.ConfidenceIntervals(): nonparametric confidence intervals for various risk characteristics based on a resampling/bootstrapping approach.
 apaCLA.Resample(): resampling efficient frontier portfolios illustrates the stochasic nature of the efficient frontier.
 apaCauchyInv(), apaCauchyPdf(), apaCauchyCdf(), apaCauchyRnd(), apaCauchySim(): our implementation of the location/scale Cauchy distribution, a distribution with infinite first and second moments.
 apaUlcerIndex(), apaUlcerPerformanceIndex(): yet another risk measure and riskasjusted performance measure.
 apaAbsorptionRatio(): a PCAbased methodology to measure degree of diversification in an asset universe.
 apaTurbulence(), apaCorrelationSurprise(), apaMagnitudeSurprise(): a turbulence indicator for an asset universe, including a decomposition of contributions from innovations in volatilities and correlations.
Enhancements and changes...
 apaWealthSimulation(): various enhancements to the wealth simulator, noteably the possibility to model timevarying strategy returns and volatilities.
 apaCVaR.Historical(), apaCDaR.Historical(): introduction of a smoothing parameter. Also affects trade profile and other functions.
 Bugfix affecting apaTrnd() and apaTsim().
 Adjusted percentile calculations in order to handle degenerative inputs. Various functions are affected.
Version 2.4 (from December 11, 2013)
Implementation of additional functionality (5 new functions)...
 apaNIGarg.randomized(), apaNIGsim.randomized(): generate randomized NIG arguments, simulate fom a randomized NIG distribution
 apaBL.PosteriorReturns.Confidence(), apaBL.ImpliedWeights: Black/Litterman posterior returns with explicit viewspecific confidence levels. Black/Litterman "implied weights" analogeous to "implied returns".
 apaSystem.Deactivate.ComputerName(): deactivate a computer from another computer
 apaSystem.ComputerName(): name of current computer
 apaSystem.ComputerName.All(): name of all computers of current license
 apaMatrix.Inverse(), apaMatrix.PseudoInverse(): inverse and pseudoinverse of a matrix
 apaMatrix.Ones(), apaMatrix.Zeros(): generate matrix of ones and zeros.
Enhancements and changes...
 Introduction of computer names in order to simplify activation of new computers replacing existing ones. IMPORTANT NOTE: users will be prompted for a computer name when starting this version of the addin on already activated computers the first time.
 All coderelated files are now digitally signed. Being a "Trusted Publisher" will contribute to a better installation experience.
 Calculation of pseudoinverse in all internal functions, addressing issues related to singular and nearsingular matrices.
 Bugfix apaCorrels.Shrink: "average" target did not work properly.
 apaBL.<...>: name changes affecting various Black/Litterman functions, various BL functions now accept tau values as arguments.
 Bugfix apaCorrel.Average: now accepts a correlation matrix as an input, not a return matrix anymore.
 Bugfix apaWeights.Combinatorial.TwoAssets: missing results on last row under certain cirumstances.
 Bugfix affecting apaNelsonSiegel.PV and apaNelsonSiegelSvensson.PV
 Various lowlevel adjustments affecting calculation speed and robustness of calculations.
Version 2.3 (September 13, 2013)
Implementation of additional functionality (31 new functions)...
 apaNelsonSiegel.ConstantTau.Estimate(): estimation of the Diebold/Li version of the Nelson/Siegel yield curve model
 apaNelsonSiegel.ForwardRate(), apaNelsonSiegel.PV(), apaNelsonSiegel.Yield(), apaNelsonSiegel.Estimate(): construction, estimation and valuation functions related to the Nelson/Siegel yield curve model.
 apaNelsonSiegelSvensson.ForwardRate(), apaNelsonSiegelSvensson.PV(), apaNelsonSiegelSvensson.Yield(), apaNelsonSiegelSvensson.Estimate(): construction, estimation and valuation functions related to the Nelson/Siegel/Svensson yield curve model.
 apaWeights.MVE.Unrestricted.ReturnTarget(), apaWeights.MVE.Restricted.VolaTarget(): portfolio composition of points on the restricted and unrestricted efficient meanvariance frontiers
 apaMVE.Unrestricted.Risk.FromReturn(), apaMVE.Unrestricted.Return.FromRisk(): calculation of points on the unrestricted meanvariance efficient frontier
 apaWeights.MVE.Restricted.MSR(), apaWeights.MVE.Restricted.MSR(): weights of the restricted and unrestricted maxiimum Sharpe Ratio portfolio
 apaMVE.Unrestricted.Coefficients(), apaMVE.Unrestricted.Hyperbola.Center(), apaMVE.Unrestricted.Hyperbola.AsymptoteSlope(): shape parameters of the unrestricted efficient meanvariance frontier
 apaNinv(): inverse normal distribution
 apaUsim.correlated(): Simulate correlated uniform distributions
 apaNIGsim.correlated(): Simulate correlated NIG distributions
 apaCFsim.correlated(): Simulate correlated CornishFisher distributions
 apaCorrels.SpearmanToPearson(), apaCorrels.PearsonToSpearman(): conversions for multivariate normal distributions
 apaMatrix.Cholesky(): Cholesky decomposition
 apaSharpeRatio.Contributions.FromCovarsExpRets(): Calculate contributions to portfolio Sharpe Ratio from expected returns and covariances
 apaNormalMixture.sim.correlation(): simulate correlated twocomponent Normal Mixture distributions
 apaNormalMixture.match: Determine twocomponent Normal Mixture parameters such that the resulting distributions exhibits desired first four moments
 apaIsValidNIG(): test whether skewness/kurtosis inputs are within feasible range
 apaCFmom(): moments of the CornishFisher expansion
 apaCFcal(): calibrate parameters of CornishFisher expension to replicate desired moments
 apaCVaR.Modified.Moments(): Calculate Modified CVaR from the first four moments
 apaWLS(): helper function to perform weighted least squares regressions
 apaManual.Update(): Download of latest PDF manual
Enhancements and changes...
 apaHistogram(): histogram methodology slightly altered, such that histogram is centered on bin centers
 apaStatFactorModel...(): Optional flag to force unit component variances
 apaUPM, apaLPM: enhanced calculation methodology
 apaReplicateRiskReturn(), apaNIGrnd(): minor bug fixes
 apaShrinkageEstimator.Returns.JamesStein(): accepts a manual shrinkage factor
 apaFixedIncome... functions: have been renamed to apaNelsonSiegel... or apaNelsonSiegelSvensson
 apaMVE.Restricted.MRP.RT(): renamed function for consistency reasons
 Further alignment of accuracy/performance characteristics between functions, additional functions affected by apaSpeed.Set()
 PDF manual is now part of the installation package; the latest version is downloaded automatically when updating the addin automatically.
Version 2.2 (March 25, 2013)
Implementation of additional functionality (8 new functions)...
 apaCLA(): Critical Line Algorithm (original Markowitz procedure to determine the exact efficient frontier)
 apaMRP.RT(): risk tolerance of the restricted maximum return portfolio
 apaWeights.MSR(): Weights of the maximum Sharpe Ratio portfolio
 apaCorrels.Random(), apaCorrels.Const(): generate correlation matrices with random and constant values
 apaMatrix.Standardize(): Standardize a time series matrix
 apaSystem.Deactivate(): Transfer your license from an old computer to a new one
 apaLicense.UserID(): for users not remembering their user id (because they have too many already)
Enhancements and changes...
 Various enhancements related to license management, update and similar operational functionality
 Enhanced algorithms for calculating correlation matrices from inputs/data, rank correlations
 Further alignment of accuracy/performance characteristics between functions, additional functions affected by apaSpeed.Set()
Version 2.1 (January 30, 2013)
Implementation of additional functionality (19 new functions)...

apaMVE(): direct calculation of mean, volatility and composition of portfolios on th meanvariance frontier

apaWeights.Combinatorial.TwoAssets(), apaWeights.Combinatorial.EqualWeighted(): helper functions for combinatorical portfolio construction
 apaTimePoints.FromDates(), apaTimePoints.Rounded(): helper functions to handle conversion of actual dates to time points / time periods for analytical purposes

apaReturn.IRR.All(): calculation of all real and imaginary solutions for the internal rate of return problem

apaMarketData.Yahoo(): retrieve price/return data from Yahoo Finance

apaGainLossSpread(),
apaGiniMeanDifference(): alternative risk measures

apaCovar.Semi(), apaCovars.Semi(), apaCoLPMs.Asymmetrical.Symmetrized(), apaCoUPMs.Asymmetrical.Symmetrized(): additional partial moment functions

apaConditionalSerialCorrelation(), apaBenfordLaw(), apaBiasRatio(): fraud indicators

apaActiveShare(): an exposurebased tracking measure

apaNormalMixture.StateProbabilities(), apaNormalMixture.States(): additional normal mixture distribution functionality
Enhancements and changes...
 Fixed a bug affecting the existing asymmetrical LPM/UPM
 In return calculation formulas, the argument "CFWeights" has been renamed "CFTime"
 Additional functions are now covered by the speed mode settings, further fine tuning of the speed modes
 Selected lowlevel performance enhancements
Version 2.0 (October 11, 2012)
Implementation of additional functionality (9 new functions)...

apaCaptureRation.Downside.Continuous(), apaCaptureRation.Downside.Discrete(), apaCaptureRation.Upside.Discrete(), apaCaptureRation.Upside.Continuous()  various capture ratios

apaLicense.ExpiryDate(), apaLicense.Puchased(), apaLicense.InUse()  software license helper functions

apaMatrix.Rank(), apaMatrix.ConditionNumber()  matrix / linear algebra functionality
Enhancements and changes...
 Fixed a bug in the installation process which affected automatic updating in certain system environments. IMPORTANT NOTE: the fix works on new installations only; existing installations should be 1) uninstalled and 2) all files and folders deleted manually; release 2.0 can then be downloaded and installed.
 Redesign of the installation wizard such that the same setup file can be used for Excel 32bit and 64bit.
 Introduction of a subscriptionbased licensing system: New users now purchase the addin on a one year subscription basis. IMPORTANT NOTE: existing users can continue using the addin and are eligible for all future updates at no additional costs.
Version 1.7 (September 30, 2012)
Implementation of additional functionality (9 new functions)...

apaBL.PosteriorReturns(), apaBL.PosteriorReturns.FullConf(), apaBL.ImpliedConfidence(), apaBL.ImpliedReturns(), apaBL.ImpliedWeights()  Blacl/Litterman portfolio construction

apaVolatility.ExPost.Contributions(), apaTrackingError.ExPost.Contributions(), apaBeta.ExPost.Contributions()  ex post contributions to various portfolio risk measures

apaEntropy.Shannon()  an alternative dispersion measure
Enhancements and changes...
 Fixed some compatibility issues of version 1.6 with older Excel versions.
Version 1.6 (September 17, 2012)
Implementation of additional functionality (23 new functions)...

apaSSA.Signal(), apaSSA.Signal.Redrawn(), apaSSA.Components(), apaSSA.Spectrum()  several methods to conduct singular spectral analysis

apaPriceSim.GBM(), apaPriceSim.GARCH11(), apaPriceSim.MN(), apaPriceSim.ARMA22(), apaPriceSim.JDLN  simulation of univaruate price processes; geometric Brownian motion, GARCH(1,1), Mmxed normal, ARMA(2,2) and JumpDiffusion with lognormally distributed jumps

apaPcdf(), apaPpdf(), apaPinv(), apaPrnd(), apaPsim()  implementation of the Poisson distribution

apaUsim()  simulation of the uniform distribution, much faster and better than Excel's internal RAND()

apaLognormalToNormal.Correlation(), apaLognormalToNormal.Covariance(), apaLognormalToNormal.Volatility(), apaLognormalToNormal.Mean() and apaNormalToLognormal.Correlation(), apaNormalToLognormal.Covariance(), apaNormalToLognormal.Volatility(), apaNormalToLognormal.Mean()  conversion of the first two moments and dependence between the normal and corresponding lognormal distributions
Enhancements and changes...
 Various minor enhancements affecting all functions or selected function groups
Version 1.5 (May 29, 2012)
Implementation of additional functionality (13 new functions)...
 apaWeights.RB, apaWeights.RB.FromCovars: Determine composition of
the risk budgeting portfolio (i.e. portfolio with risk contributions that
correspond to a given risk budget)
 apaSpeed.Set and apaSpeed.Get: Management of calculation speed and calculation
accuracy
 apaReturn.Contributions: contributions to portrfolio return from
portfolio constitutents
 apaSharpeRatio.Contributions: contributions to Sharpe Ratio from
portfolio constitutents
 apaSharpeRatio.FromConst: calculate portfolio Sharpe Ratio
directly from constituent exces returns and constituent weights
 apaIsValidCF: validates skeweness and kurtosis values for
CornishFisher expansion
 apaKMeans: kmeans clustering algorithm
 apaCorrels.Analyze: analysis of the validity of a correlation
matrix
 apaCorrels.Fixed: Makes a correlation matrix semipositive
definite (Eigenvalue method)
 apaWeights.MD.FromCovars: Maximum Diversification portfolio from
covariances
 apaWeights.EVC.FromCovars: Equal Risk Contributiuon (Risk Parity)
portfolio from covariances
Enhancements and changes...
 Simplified startup procedure resulting in faster startup times
 Increased secruity, the addin only connects to our domain
www.andreasssteiner.net
 More robust determination of maximum diversification and risk
partiy portfolios
 Implemented a workaround for a multithreading issue in Excel
Version 1.4
Implementation of additional functionality (13 new functions)...
 apaWeights.MD: determines the Most Diversified portfolio.
 apaCorrels.Implied: correlation matrix based on implied correlation coefficient,
 apaCorrel.Implied: Implied correlation coefficient.
 apaCorrels.IsValid: checks whether a matrix is a valid correlation matrix
 apaWeights.EVC.Robust: determines the robust risk parity portfolio.
 apaHPFilter: HodrickPrescott filter.
 apaMACD: Moving Average Convergence Divergence indicator (technical analysis).
 apaTradeProfile: impact analysis of trades on selected return, risk and riskadjusted performance measures.
 apaSharpe.Incremental: incremental asset contributions to portfolio Sharpe Ratio.
 apaVolatility.Incremental: incremental asset contributions to portfolio volatility.
 apaTSGetRow: extract a row from a matrix.
 apaTurnover: calculates tunrover based on changes in precentage allocations
 apaVolatility.ExAnte.Contributions.FromCovars: ex ante volatility contributions from asset exposures and a covariance matrix.
Enhancements and changes....
 New argument IsCorrelOtherwiseCovar: All functions related to statistical factor modeling (apaStatFactors...) can now either target the correlation or the covariance matrix.
 Changed versioning: new format is Major.Minor(.Build)
 Set user agent to operate the function library / Excel addin in high security environments.
Version 1.3.4
Implementation of additional functionality (1 new function)...
 apaWeights.MVE.Liabilities.Restricted.RiskTolerance: constituent weights of a portfolio on the meanvarianceliability efficient frontier,
Enhancements and changes....
 Webbased activation/licensing system
 Various minor performance enhancements affecting many functions indirectly
Version 1.3.3
Implementation of additional functionality (7 new functions)...
 apaWeights.EVC: Weights of the fully invested longonly equalvolatilitycontribution portfolio.
 apaActivePassiveRatio: a portfolio's activepassive ratio
 apaGARCH11.Volatility.Conditional.Forward: GARCH(1,1) forward volatility
 apaGARCH11.Volatility.Conditional.ExpectedForward: GARCH(1,1) expected forward volatility
 apaCorrel.Average: Average correlation coefficient from returns
 apaCorrel.Dispersion: Dispersion of orrelation coefficient from returns
 apaCorrels.Dispersion: Dispersion of orrelation coefficient from correlations
Enhancements and changes....
 Name changes in all GARCh(1,1) functions
 Changes in GARCH(1,1) methodology: unconditional volatility is now set to the sample volatility of the return time series (volatility targeting)
 Various minor performance enhancements affecting many functions indirectly
 Generalized licensing algorithm
Version 1.3.2
Implementation of additional functionality (7 new functions)...
 apaWealthSimulation: Stochastic wealth simulation that can be used for financial planning purposes or asset/liability management
 apaSkewness.Contributions: Contributions to portfolio skewness
 apaKurtosis.Contributions: Contributions to portfolio kurtosis
 apaCorrels.Contributions: Contributions to correlation
 apaCFinv, apaCFrnd, apaCFsim: a partial implementation of the distribution based on the CornishFisher Expansion for simulation purposes
Enhancements and changes....
 Introduction of a setup file, making the activcation process more user friendly
 apaTSGet() returns a zero value if no numerical values are found in a range
Version 1.3.1
Implementation of additional functionality (3 new functions)...
 apaStrategy.CPPI: CPPI strategy simulation
 apaVersion.Current: Latest available version number
 apaUrnd: Fast generation of a highquality uniform random number
Enhancements and changes....
 Automatic version check and download whenever a new release is available
 Fast and highquality random number generation with the MultiplyWithCarry method
 Various numerically intense functions were given an optional DoSwitchOff parameter (e.g. apaVaR.GP)
 Various numerically intense functions are not calcualted anymore in the Excel function wizard
 Renamed apaVersion to apaVersion.Installed
 Various minor performance enhancements affecting several functions
Version 1.3.0
Implementation of additional functionality (28 new function in total)...
 Automatically resize array formulas such that all results are shown
 Several functions related to empirical loss analysis (e.g. mean excess loss)
 Exceedance correlations
 Empirical tail dependence
 Generalized Pareto distribution: cdf, pdf, inv, simulation, estimation
 EVT VaR estimate based on the Generalized Pareto distribution
 Symetrized JoeClayton copula: cdf, pdf, simualtion, estimation
 Empirical copulas: pdf, cdf
 Simulation of the independence copula
 Ichimoku chart
 Diversification ratio
 Random allocations with restrictions and bias
Enhancements and changes....
 Requires the .NET 4.0 framework
 Returns lowlevel error messages, not just #VALUE anymore
 Various performance enhancement
 Faster and more accurate numerical optimization algorithm (NelderMead)
 Renamed apaSystemPerformance() to apaSystem.Performance()
Version 1.2.6
Implementation of additional functionality (38 new functions in total)...
 Expected maximum drawdown of a Geometric Brownian Motion
 ZScore, Modified ZScore
 Outlier identification with ZScore and Modified ZScore
 Mean Absolute Deviation from Mean, Mean Absolute Deviation from Median
 Normal Mixture Distributions (two components): Cdf, pdf, maximum likelihood estimation, inv cdf, simulation, theoretical first four moments from distribution arguments
 Shrinking a valid correlation matrix towards a target (perfect correlation, zero correlation, minimium correlation)
 Parametric ValueAtRisk from conditional and unconditional GARCH(1,1) volatilities
 Nonlinear serial dependency: autocorrelations calculated with Spearman's rank correlation coefficient
 Linear Multiple Regression (OLS): parameters, Rsquared, adjusted Rsquared, ttest and pvalues
 Upper and Lower returns relative to a threshold
 Chow test for structural breaks
 Conversion of price series into return time series
 Resampling from time series with the option to preserve serial dependencies
 Linear or NonLinear Dual Alpha / Dual Beta SingleIndex Model
 Logistic distribution: cdf, pdf, inv cdf, moments, arguments from moments
 Tail risk attribution of Modified ValueAtRisk
Enhancements...
 Various minor changes enhancing stability (Excel crashes due to memory overflows)
 Different, more mainstream, formula for Clayton copula
 Renamed BiNormal and BiClayton simulation functions
 Rolling style analysis: the first n weights are not zero anymore, but N/A
Version 1.2.5
Implementation of additional functionality...
 Population versions of the third and fourth moment: apaKurtosis.Population, apaSkewness.Population
 Inverse percentile function: apaPercemtile.Inverse
 Lower and upper bounds on the entries in a valid correlation matrix: apaCorrels.Bounds
Enhancements...
 Various performance and stability enhancements
 Adjusted definition of CVaR and CVaG not to include VaR/VaG
 Introducing the license file
 Deleted the VaG function because it is redundant
 Changed CVaR, CDaR functions to accept confidence rather than a threshold
 Changed Conditiona Sharpe Ratio, Rachev Ratio and Generalized Rachev Ratio functions to accept confidence level rather than a threshold
Version 1.2.4
Implementation of additional functionality...
 Historical Interim ValueAtRisk
 Contributions to Ex Ante Tracking Error when asset returns in portfolio and benchmark are identical or different.
 Loading external CSV files as time series
 Calculation of all rolling returns with one function call
 Portfolio kurtosis/skewness from returns or cokurtosis/coskewness matrix
 MoneyWeighted Return (IRR) and TimeWeighted Return (Original Dietz, Modified Dietz)
Enhancements...
 Several functions return #N/A values, which simplifies making charts
 The XLA component is not needed anymore
Version 1.2.3
Implementation of additional functionality...
 High quality random number generation with the Mersenne Twister algorithm
 Function to create unbiased restricted random weight
 DrawdownAtRisk/Conditional DrawdownAtRisk functions
 System Performance Test function
Version 1.2.2
Implementation of additional functionality...
 Nelson/Siegel/Svensson yield curve modeling functions
 Biased and unbiased functions to generate random weights
 Calculation of the weights of the maximum return portfolio on the meanvariance efficient frontier with inequality constraints on the weights.
Enhancements
 Performance increase of all functions related to empirical percentiles.
 Fixed a bug in the functions simulating random distributions (Normal, Multivariate Normal, NIG, T etc.)
Version 1.2.1
Implementation of additional functionality...
 Student T distribution: cdf, pdf, inv
 Time aggregation of returns
 Consolidation of segment data for performance attribution purpose
