Excel AddIn
Spreadsheets
This page contains example spreadsheets on how to use the functionality.
The spreadsheets only work if you have the addin installed on your
computer! If you have not purchased the addin yet, please have a look
at selected
screenshots
taken from the spreadsheets below.
If you do have a regular licence and encounter errors anyway, then you will most
likely have an outdated version. Please update your addin or contact us by email.
The spreasheets flagged "new" are either new or have been updated for the
current release.

Matrix / Linear Algebra: various functions related to matrices and vectors: SVD, PCA, QR, LU factorizations, Hankel & Toeplitz matrices, lagged data matrices and much more.
 Singular Spectral Analysis  Spectrum, signals, components, forecasting, multivariate SSA, wcorrelations, redrawing.

Gerber Statistic: Gerber statistic & modified Gerber statistic, Gerber matrix & tables. An interesting dependency concept in the tradition of Kendall's Tau, fully compatioble to traditional meanvariance optimizers.

OLS  Flexible functions implementing OLS linear regression analytics, univariate & multivariate, processing many Y variables at the same time.
 Linear Combinations in Correlation Matrices, MeanSquared Difference  forming weighted linear combinations of selected constituents in a correlation matrix, assessing similarity/dissimiliarty of two correlation matrices by calculaing their MSD.

PCA Yield Curve Risk Factors: calculating the level, slope and curvature factors for a given yield curve using principal component analysis.

Resampled Confidence Bands: nonparametric confidence bands for means and volatilities, correlation, skewness and excess kurtosis.

Partial Correlations: a simple approach to measuring downside and upside correlations based on either the arithmetic mean or quantiles.

Stochastic Mean Variance Frontier: resampling the efficient frontier portfolios is a reminder that the classical meanvariance frontier is not deterministic.

Turbulence Analysis: an approach to measure the degree of disturbance in an asset universe with the possitbility to isolate contributions from volatilities and correlations.

Cauchy Distribution: unimodal distribution with undefined first and second moments.

Decorrelation: removing correlations while preserving certain other characteristics of a time series matrix.

Simulating from Randomized NIG Distributions: illustration of the Central Limit Theorem when distributions averaged are not identical anymore.

Critical Line Algorithm: Applying the original Markowitz procedure to generate the exact meanvariance efficient frontier with randmized asset correlations.

Combinatorial Portfolio Construction: using cominatorics to build portfolios.
 Fraud Flags: Fraud indicators like Benford's Law, Bias Ratio and Condiditional
Serial Correlation.

Black / Litterman Portfolio Construction: a Bayesian approach to include views in meanvariance portfolios.

Ex Post Portfolio Risk Contributions: contributions to portfolio volatility, tracking error and beta when portfolio constituent weights vary over time.

Price Time Series Simulation  Geometric Brownian motion, mixed normal, GARCH(1,1), ARMA(2,2), jumpdiffusion with lognormally distributed jumps.

Normal/Lognormal Distribution Conversions  conversions for means, volatilities, correlations and covariances when switching from discrete to continuous returns and vice versa.

Return, Volatility and Sharpe Ratio Contributions  Marginal contributions,
absolute and percentage contributions to return, risk and riskadjusted return
(Sharpe Ratio).

Risk Budgeting
 portfolio construction based on risk budgets (i.e. percentage conetributions to
volatility).

Correlation Matrix Validation and Fixing  Analyze whether a correlation
matrix is valid and fix the matrix if not valid.

CornishFisher Approximation  density function, validation, moments and calibrated parameters for the
CornishFisher approximation to the Normal distribution.

HodrickPrescott Filter
 filtering of a trend and a cyclical components with econometric methods.

Trade Profile
 analyzing the impact of trading from a current portfolio on portfolio return, volatility and riskadjusted performance.
Incremental Volatility and Sharpe Ratio
 calculation of the incremental contribution of assets to portfolio volatility and riskadjusted performance.
Surplus Optimization
 restricted mean variance optimization considering liabilities.
EqualVolatilityContribution Portfolio
 construction of the risk parity (equal volatility contribution) portfolio; robust and exact versions.
Implied Correlation
 calculating the implied correlation given asset weights, asset volatilities and portfolio volatility. The constant correlation matric based on the impled correlation.
Moving Average Convergence Divergence (MACD): A classical technical indicator.

Most Diversified Portfolio
 deriving the weights of the Most Diversified Portfolio, comparsion with other riskbased strategies like minimum variance, risk party and maximum Sharpe Ratio.
Average Correlations, Dispersion of Correlations
 calculation of average correlation and dispersion of correlation coefficients from time series data directly.

Wealth Simulation
 cash flow planning with timevariable risk and return, Monte Carlo simulations useful for asset and liability management.
Contributions to Portfolio Skewness, Kurtosis and Correlation  analysis of ex ante asset contributions to advanced portfolio risk characteristics.
CPPI Strategy  simulation of a basic constant proportion portfolio insurance strategy.
Resizing array formulas  automatically resizing array formulas so that all outputs are shown, de facto making CTRL+SHIFT+ENTER obsolete.
Diversification ratio  simplistic portfolio optimizer to construct portfolios with maximum. diversification, equal risk contribution (risk parity) and other criteria.
Copula fitting  estimation of bivariate copula parameters from data.
Ichimoku chart  graphical chart analysis.
Loss analysis  various descriptive functions to analyze empirical loss data.
Generalized Pareto Distribution  cdf, pdf, inv, rnd, sim and maximumlikelihood estimation.

Exceedance Correlation, Empirical Lower Dependence  Measuring bivariate tail dependence.
Tail Risk Attribution  Attributing Modified VaR components
Conditional Returns  Bull/bear returns, upper/lower returns, up/down returns
Scores  The zscore and modified zscore
Chow Test  Testing for structural breaks in linear regression models
Resampling  Resampling time series with the option to preserve autocorrelation structures

The Normal Mixture Distribution  Implementation of a flexible and intuitive distribution to model nonnormalities.
The Logicistic distribution  implementation of an important nonnormal distribution.

RiskAdjusted Performance Measures  From Sharpe to the Generalized Rachev Ratio, via the Ulcer Performance Index.
ValueAtRisk & Conditional ValueAtRisk  Different approaches to quantifying quantile losses: Normal, NIG, Modified and Historical VaR; plus Interim VaR and DrawdownAtRisk.
Drawdowns/ Runups, Winning/Losing Runs  Various functions related to pathdependent interm risk measures, including the calculation of expected maximum drawdown for a GBM.
Normal Inverse Gaussian (NIG) Simulation  Simulation and evaluation of the "plugandplay" fourmoment NIG distribution.
Style Analysis  Constituent weights that best replicate a given portfolio; calculated average weights as well as rolling style weights (including turnover).
Time Series Analysis  Serial dependence, tests for normal distributions and more.
Copula Simulation  Generating variables drawn from the Gaussian, Clayton Independent and Symetrized JoeClayton copulae.
ExponentiallyWeighted Risk Measures  Measurement of timevarying risk characteristics à la RiskMetrics (tm).

Statistical Factor Model  Calculation of a PCAbased statistical facor model targeting the correlation or covariance matrix.
Bivariate Gaussian Outliers  Detection of univariate and bivariate outliers, drawing of 2D confidence region.
Factor Model Calculations  Building asset returns, volatilities and covariances based on the inputs from a factor model.
GARCH(1,1)  Maximum likelihood parameter estimation, conditional and unconditional GARCH volatilities.
Contributions to Ex Ante Volatility, Normal VaR & CVaR, Modified VaR & CVaR  Marginal, component and percentage contributions to Volatility, Normal VaR/CVaR as well as Modified VaR/CVaR. A contribution to "nonnormality" can be derived.
Classical MeanVariance Optimization  Restricted/unrestricted efficient frontiers, weights of frontier portfolios, minimum variance portfolio weights
Triangular Distribution  A flexible unimodel distribution defined by a min, max and modus. Very convenient for stress testing and simulations without much prior information.
Risk & return characteristics replication  Generates asset returns which exactly replicate given expected returns, volatilities and correlatio
Bayesian Shrinkage Estimators  Alternatives to estimating expected returns and covariances (James/Stein, Ledoit/Wolf, Jorion estimators).
Hurst Exponent  A summary measure indicating whether a time series exhibits mean reversion or momentum, or is a random walk
Quantile Table  2D quantiles, useful for visualizing dependence between two two time series
Portfolio Attribute Linking  Chainlink absolute (constituentreturn contributions) and relative (attribution effects) attributes over time
Augmend DickeyFuller Test  Unit root test, for example when conducting the Engle/Granger test for cointegration
Extreme Value Theory  Estimation of Tail Index (Least Squares Hill estimator) and EVT ValueAtRisk .
Waterfall Charts  Generates input data necessary to plot a waterfall chart in Excel
The Resampled Efficient Frontier  Calculation of the resampled frontier, constituent weights of portfolios on the frontier
Consolidation of portfolio/benchmark segment data  utility function for the flexible calculation of performance attribution effects
Time Aggregation of returns  coversion of time series to time series with a lower frequency; can be used to examine the validity of the "square root n" rule for the time aggregation of volatilities.
Time Series Utilities  Various utility functions related to handeling return time series in an efficient manner.

Nielson/Siegel/Svensson Yield Curve Modelling  Estimating the parameters of the extended Nielson/Siegel model from empirical yields.
Contributions to Ex Ante Tracking Error  Contributions to ex ante TE when asset returns in portfolio and benchmark are equal and when they are different. In case case of differing, a TE decomposition into contribution from allocation, selection and interaction is performed.

MoneyWeighted & TimeWeighted Returns  Consistent functions for calculating the internal rate of return (IRR), also known as MWR, as well as Orignal Dietz and Modified Dietz Returns.
External CSV Time Series Data Management  Working with time series data stored in external CSV files. You also need to download the sample CSV file.
Using the ApaLibNET in VBA  Integrating the functionality with VBA code.
Stressing a Valid Correlation Matrix  lower and upper bounds for elements in a valid correlation matrix, testing whether a given correlation matrix is valid.
