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.

In order to be able to download the spreadsheets, JavaScript has to be enabled in your browser.

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.
Market Data: Retrieve price and return time series data, for example from Yahoo Finance.

Fraud Flags: Fraud indicators like Benford's Law, Bias Ratio and Condiditional Serial Correlation.

Risk Measures: Traditional and alternative risk measures.

Capture Ratio Analysis: upside and downside capture ratio analysis.

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

Matrix / Linear Algebra: various functions related to matrices.
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.
Singular Spectral Analysis - Spectrum, components, reconstructed signal and signals from redrawing.
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).
k-Means Clustering - Statistical classification with the the classical k-means clustering algorithm.
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 and valudation 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 performanceo.
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 - stochastic cash flow planning, 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
OLS - Flexible functions implementing linear regression.
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.
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.