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, you have outdated versions. Please update your add-in first. Then download the latest spreadsheet versions or adjust changes in function names and arguments manually.

You can download a Zip File (ca. 40MB) containing all spreadsheets below. Just click the green buttons. Spreadsheets added in the latest release are labelled NEW, modified spreadsheets UPDATED.

• Vector Autoregressive (VAR) Models NEW
  VAR model estimation (OLS), impulse/response analysis, forecasting.
• k-Nearest-Neighbour Algorithms NEW
  kNN regression, classification, smoothing, outlier detection (LOF) and more.
• First Passage Time NEW
  Calculations related to first passage time: resampled empirical CDF and PDF.
• Distances & Similarities NEW
  Distance and similarity calculations: distance matrices based on correlations, euclidian distances, angular distance and many more
• Summary Statistics NEW
  Descriptive summar statistics for data matrices: moments & more, Five Number Summary, Seven Number Summary.
• Detrending NEW
  Linear and polynomial detrending.
• Data Management NEW
  Various helper functions to manage data issues: matching dates between datasets, missing/zero/non-numeric data detection, number of digits analysis, data types analysis, gaps/repeats analysis, proxy methods, earliest/latest dates in time periods and more.
• Clustering Algorithms NEW
  k-Means, hierarchical, spectral and DBSCAN clustering.
• Tail Dependence
  Calculating left, right and combined left-right tail dependence.
• Jagged Statistics
  Various calculations using jagged input data, i.e. time series of different lengths.
• Five Number Summary
  Joint calculation of various quantiles for so-called "five number summary" of especially large datasets.
• Algorithms UPDATED
  Various rithms, e.g. envelope, convex hull.
• Categories
  Categorisation functionality for various applications, e.g. construction of block correlation matrices.
• Quantile Regression
  Quantile regression analysis.
• Holding Period Returns
  Holding period returns allow analysis of investment horizon results and entry points.
• Portfolio Weights
  Functionality relating to portfolio constituent weights, e.g. buy-and-hold and rebalanced weights over time.
• Correlations - Resampling Methods
  Applying resampling to correlations, e.g. confidence bands for correlation coefficients, estimation risk of overall correlation matrix and more.
• Random Weights
  Functionality to generate random portfolio constituent weights and use them for analytics like portfolio construction, risk management.
• Charts UPDATED
  Various functions to push the frontier of Excel's built-in chart functionality.
• Forward Stagewise Regression
  Perform forward stagewise regression analayis. Useful when building linear models from a large set of possible explanatory variables. Includes an example in which the optimal stagewise model is found based on out-of-sample performance.
• Forward Stepwise Regression
  Perform stepwise regression analayis. Useful when building linear models from a large set of possible explanatory variables.
• Ridge Regression
  Linear ridge regression. Includes out-of-sample calibration of the lambda parameter.
• k-Fold Cross-Validation
  Various functions to perform k-fold data manipulation for cross-validation and out-of-sample performance evaluation, parameter calibration and more.
• Rolling Window Analysis
  Various functions to perform analytics based on rolling windows. In-sample and out-of-sample applications.
• Polynomial Roots
  Finding real and complex roots of polynomial expressions. With an application to ARMA process.
• Matrix Mainpulations & Linear Algebra UPDATED
  Various functions related to matrices and vectors: SVD, PCA, QR, LU factorizations, Hankel & Toeplitz matrices, lagged data matrices and much more.
• OLS
  Flexible functions implementing OLS linear regression analytics, univariate & multivariate, processing many Y variables at the same time.
• Process Simulations UPDATED
  Geometric Brownian motion, Brownian Bridge, mixed normal, GARCH(1,1), ARMA(2,2), jump-diffusionwith lognormally distributed jumps. For prices and returns.
• Mean Variance Frontiers
  Family of functions relating to mean variance frontier calculations in Modern Portfolio Theory (MPT). Efficient frontiers, linear equality constraint, IR frontier, TE frontiers and restrictions. And more.
• Validate Allocations
  Determine whether allocations fulfill restriction: group inequality constraints, TE limits.
• Bayesian Shrinkage Estimators
  Estimators for expected returns and the covariance matrix (James/Stein, various by Ledoit/Wolf, Jorion estimators).
• Black / Litterman Portfolio Construction
  A Bayesian approach to include views in mean-variance portfolios.
• Time Series Analysis
  Serial dependence, tests for normal distributions and more: autocorrelations (Pearson & Spearman), partial autocorrelations and autocovariances.
• Non-Normality Contributions & nObs-to-Drop
  Contributions of observations to non-normality and an overall indicator for degree of non-normality of data.
• Performance Attribution
  Utility function for the flexible calculation of performance attribution effects
• Return, Volatility & Sharpe Ratio Contributions
  Marginal contributions, absolute and percentage contributions to return, risk and risk-adjusted return (Sharpe Ratio).
• Trade Profile
  Analyzing the impact of trading from a current portfolio on portfolio return, volatility and risk-adjusted performance.
• Add-In Management
  Various helper functions to manage the add-in.
• Graph Theory UPDATED
  Functions relating to the mathematical field of graph theory.
• Temporal Disaggregation UPDATED
  Function family for temporal aggregation and disaggregation of data, i.e. turnung quarterly data into monthly observations, for example.
• Temporal 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. Legacy functions only. See file "Temporal Disaggregation" for more functionality of this type.
• Removing Serial Correlation
  Blundell/Ward filter to remove first-oder autorcorrelation.
• Imputation Methods
  Function family for "filling the gaps", i.e. generating data points for incomplete datasets.
• Numerical Contribution Analysis
  Functions calculating numerical contributions to measures like for example Conditiona Value-At-Risk or Drawdown.
• Empirical Copula Resampling
  Resampling an empirical copula with and without shrinkage to a Gaussian or Clayton copula.
• Empirical Copula
  Various functions to handle multivariate empirical copulas.
• Tail Risk Matched Volatilites
  Modify volatilities such that they match observed tail risk statistics like VaR or CVaR.
• Manipulating Correlation Matrices
  Dropping an element from a correlation matrix, swapping two elements, shifting left/right, reorder and similar operations.
• Sorting Correlations by Column Properties
  Sorting a correlation matric by asset characteristics defined by its correlations with the other assets.
• Stochastic Mean Variance Frontier
  Resampling points on the the efficient frontier portfolios is a reminder that the classical mean-variance frontier is not deterministic.
• Risk Budgeting
  Portfolio construction based on risk budgets (i.e. percentage conetributions to volatility).
• Mean Variance Strategies
  Findung the Risk Parity, Robust Risk Parity, Most Diversified Portfolio, Minimum Variance and Maximum Sharpe Ratio Portfolios.
• Markov Regime Switching Simulations
  Simulating state tranjectories, assset returns, portfolio returns and hit rates
• Exponentially-Weighted Correlation Matrices
  A practical dynamix model for the correlation matrix
• Transform Correlations
  Useful transformations of correlation coefficients with applications.
• Empirical CDF
  Functionality related to the empirical distribution function.
• Block Correlations UPDATED
  Functions for working with block structures in correlation matrices.
• Correlation Shrinkage
  Shrinking correlations towards various targets.
• Diversificartion Charts
  Graphical analysis of Markowitz-stlye diversification for portfolio volatility and beyond.
• Piecewise Linear Regression
  Also known as dummy variable regressions, or conditional linear regressions.
• Covariance & Volatility Calculations
  Basic covariance matrix and volatility calculations.
• 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).
• Concentration Measurement
  Herfindahl and Normalised Herfindahl Index.
• Machine Learning (ML), Artificial Intelligence (AI) & Data Science (DS)
  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.
• Singular Spectral Analysis
  Spectrum, signals, components, forecasting, multivariate SSA, w-correlations, redrawing.
• 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.
• Lead/Lag Correlations
  Calculating correlations for leading and lagging values of the variables.
• Confidence Intervals Shortfall Probability
  Calculating upper and lower confidence values for shortfall probability with a resampling approach.
• Sorting Correlations by 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.
• 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.
• 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.
• 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 Randomized NIG Distributions
  Illustration of the Central Limit Theorem when distributions averaged are not identical anymore.
• 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.
• Ex Post Portfolio Risk Contributions
  Contributions to portfolio volatility, tracking error and beta when portfolio constituent weights vary over time.
• Normal / Lognormal Distribution Conversions
  Conversions for means, volatilities, correlations and covariances when switching from discrete to continuous returns and vice versa.
• Correlation Matrix Validation & 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.
• Incremental Volatility & Sharpe Ratio Contributions
  Calculation of the incremental contribution of assets to portfolio volatility and risk-adjusted performance.
• 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.
• 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 & Correlation
  Analysis of ex ante asset contributions to advanced portfolio risk characteristics.
• CPPI Strategy
  Simulation of a basic constant proportion portfolio insurance strategy.
• 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
  Ggraphical 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
• Normal Mixture Distribution
  Implementation of a flexible and intuitive distribution to model non-normalities.
• 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, Winning/Losing Runs UPDATED
  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).
• 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 / 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.
• 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 Replication
  Generates asset returns which exactly replicate given expected returns, volatilities and correlatio
• 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
• 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.