# ApaLibNET

## Advanced Portfolio Analytics Library .NET

Home > Downloads > 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, 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 containing all spreadsheets below. Just click the green buttons. Dark green buttons are for spreadsheets which have been added or were modified with the latest release.

- Temporal Disaggregation

Function family for temporal aggregation and disaggregation of data, i.e. turnung quarterly darta into monhtly observastions, for example. - Matrix Mainpulations & Linear Algebra

Various functions related to matrices and vectors: SVD, PCA, QR, LU factorizations, Hankel & Toeplitz matrices, lagged data matrices and much more. - Graph Theory

Functions relating to the mathematical field of graph theory. - 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. - 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. - Tail Risk Matched Volatilites

Modify volatilities such that they match observed tail risk statistics like VaR or CVaR. - Quick & Easy Summary Time Series Statistics

Functions to quickly produce important descriptive statistis for time series data. - Manipulating Correlation Matrices

Dropping an element from a correlation matrix, swapping two elements, shifting left/right, reorder and similar operations. - Distance Concepts

Calculating Euclidian, Manhattan, Chebyshev, Minowski distance measures for multivariate data. - 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

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. - 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. - 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. - k-Means Clustering

Statistical classification with the the classical k-means clustering algorithm. - 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. - 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 & Sharpe Ratio Contributions

Marginal contributions, absolute and percentage contributions to return, risk and risk-adjusted return (Sharpe Ratio). - 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. - Trade Profile

Analyzing the impact of trading from a current portfolio on portfolio return, volatility and risk-adjusted performance. - 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. - Resize 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

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. - LogisticDistribution

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 / 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 - 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 - Consolidation of Portfolio Segment Data

Utility function for the flexible calculation of performance attribution effects - 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.