# 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** 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.