ApaLibNET

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, you can look at the spreadsheets by setting "Calculation Options" to "Manual" in Excel before opening a spreadsheet.

If you do have a regular licence and encounter errors anyway, then you most likely have an outdated version. Please update your add-in first.

The spreasheets flagged "new" are either new or have been updated for the current release.

• Concentration Measurement - Herfindahl and Normalised Herfindahl Index.
• Conditional Correlations - correlation beyond the symmetrical Pearson correlation coefficient: tail, downside, local, conditional correlations and correlation matrices based on different types of threshold (quantiles, arithmeticmean, sigmas, boxes).
• Machine Learning (ML), Artificial Intelligence (AI) & Data Science (DS) Methods - Boosted Hodrick-Prescott Filter.
• Plotting Cones - calibrating upper and lower bands ("cones of uncertainty") to time series data.
• Logistic Function - implementation of a useful mathematical function.
• Gerber Statistic - Gerber statistic, modified and altered Gerber statistic, Gerber matrix & tables. A new dependency concept in the tradition of Kendall's Tau, fully compatible with traditional mean-variance optimizers.
• 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.
• 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 Logistic 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.