ApaLibNET - Advanced Portfolio Analytics

HOME EXCEL ADD-IN .NET COMPONENT WEB APPS ABOUT

Purchase / Screenshots / Spreadsheets / Tutorials / Releases / Clients

Excel Add-In Spreadsheets

This page contains example spreadsheets on how to use the functionality. The spreadsheets only work if you have the add-in installed on your computer! If you have not purchased the add-in 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 add-in 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, w-correlations, 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 mean-variance optimizers.
OLS - Flexible functions implementing OLS linear regression analytics, univariate & multivariate, processing many Y variables at the same time.
Linear Combinations in Correlation Matrices, Mean-Squared Difference - forming weighted linear combinations of selected constituents in a correlation matrix, assessing similarity/dissimiliarty of two correlation matrices by calculaing their MSD.

Hierarchical Clusterung & Dendrogam: illustration for the single-/complete-/average-linkage clustering functions and their various helper functions.
Distance Concepts: Calculating Euclidian, Manhattan, Chebyshev distance measures for multivariate data.
Manipulating Correlation Matrices: Dropping an element from a correlation matrix, swapping two elements - without making the end result an invalid correlation matrix.
Sorting Correlations by Column Properties: Sorting a correlation matric by asset characteristics defined by its correlations with the other assets.
Lead/Lag Correlations: Calculating correlations for leading and lagging values of the variables.
Blockwise Correlations: Aggregating correlation matrices.
Confidence Intervals Shortfall Probability: Calculating upper and lower confidence values for shortfall probability with a resampling approach.
Sorting Correlations by Loadings to Principal Components: Gaining insights into the dependency structure in the correlation matrix by using Principal Component Analysis (PCA).
Eigenvalues & Eigenvectors: Illustration of the Eigenvalue and Eigenvector functionality.
Risk Measures: Traditional and alternative risk measures.
k-Means Clustering - Statistical classification with the the classical k-means clustering algorithm.
Distance Correlation: Various calculations related to distance correlation (distance correlations, covariances, variances and volatilities and so on), including (Euclidian) distance matrix of a data vectors.
Credit Rating Calculations: Calculating the expected ending allocation given an initial rating allocation and a rating transition probability matrix, compounding a rating transition matrix over several years.
Empirical Copula Resampling: resampling an empirical copula dependency structure and calculation of multivariate empirical copula PDFs.
Resampling Multivariate Time Series Data: resampling from multivariate time series data.

PCA Yield Curve Risk Factors: calculating the level, slope and curvature factors for a given yield curve using principal component analysis.
Resampled Confidence Bands: non-parametric 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 mean-variance 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 mean-variance 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.

Capture Ratio Analysis: upside and downside capture ratio analysis.

Add-In Management: various helper functions to manage the add-in.

Black / Litterman Portfolio Construction: a Bayesian approach to include views in mean-variance 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), jump-diffusion 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 risk-adjusted 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.
Cornish-Fisher Approximation - density function, validation, moments and calibrated parameters for the Cornish-Fisher approximation to the Normal distribution.
Hodrick-Prescott 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 risk-adjusted performance.
Incremental Volatility and Sharpe Ratio - calculation of the incremental contribution of assets to portfolio volatility and risk-adjusted performance.
Surplus Optimization - restricted mean variance optimization considering liabilities.
Equal-Volatility-Contribution 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 risk-based 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 time-variable 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 maximum-likelihood 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 z-score and modified z-score

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 non-normalities.
The Logicistic distribution - implementation of an important non-normal distribution.
Risk-Adjusted Performance Measures - From Sharpe to the Generalized Rachev Ratio, via the Ulcer Performance Index.
Value-At-Risk & Conditional Value-At-Risk - Different approaches to quantifying quantile losses: Normal, NIG, Modified and Historical VaR; plus Interim VaR and Drawdown-At-Risk.
Drawdowns/ Run-ups, Winning/Losing Runs - Various functions related to path-dependent interm risk measures, including the calculation of expected maximum drawdown for a GBM.
Normal Inverse Gaussian (NIG) Simulation - Simulation and evaluation of the "plug-and-play" four-moment 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 Joe-Clayton copulae.
Exponentially-Weighted Risk Measures - Measurement of time-varying risk characteristics à la RiskMetrics (tm).
Statistical Factor Model - Calculation of a PCA-based 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 "non-normality" can be derived.
Classical Mean-Variance 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 - Chain-link absolute (constituentreturn contributions) and relative (attribution effects) attributes over time
Augmend Dickey-Fuller 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 Value-At-Risk .
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.
Money-Weighted & Time-Weighted 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.