ApaLibNET - Advanced Portfolio Analytics

The price of the Excel add-in is 280 CHF for a lifetime license (click here to convert this amount to a currency of your choice). The license allows to install the add-in on one computer, plus example spreadsheets illustrating the use of the functions as well as a detailed PDF documentation with formulas and information on the algorithms used. Included in the license fee are also maintenance releases with function upgrades, bug fixes and performance enhancements. A growing number of video tutorials are available, providing live examples on how to work with the library efficiently.

 If you are interested in purchasing more than 10 licenses, please contact us to receive a customized offer with attractive license prices and additional services like support or training.

The add-In is an XLL component based on a compiled function library running on the .NET framework. Compared to interpreted VBA, the add-in is much faster and compared to other software, can be installed without administrator rights. Our add-in works on Microsoft Windows computers running Microsoft Office and the Microsoft .NET Framework 4.0, no other software is required.

 If you would like to test whether the Add-In works on your particular system, you can try to run the free XLL add-in provided in the download section of our educational website here. These add-in are based exactly on the same technology as our Advanced Portfolio Analytics add-in. If there are any doubts, please test the free XLL add-ins first; we are not refunding any software purchases.

By default, the add-in comes with a installer for a guided and user-friendly installation experience. The installation is completed with a very simple Internet-based activation process. New releases are detected and installed automatically whenever Excel is started. The add-in also requires access to the Internet whenever Excel is started. After starting Excel, the add-in can be used without an Internet connection as long as Excel is not closed. The add-in has been tested with firewalls and proxy servers. Privacy of users data is preserved at any time.

There are 32-bit and 64-bit versions of the add-in available, to be used with Microsoft Office 32-bit and Microsoft Office 64-bit respectively. The 64-bit version has been tested with Microsoft Office 2010 64-bit on Windows 7 64-bit.

We have successfully tested the add-in on Windows XP SP-3, Windows Vista and Windows 7 with Excel 1995, 1997, 2000, 2002 (XP), 2003, 2007 and 2010. Please contact us by email if you have additional technical questions.

We do not officially support Apple and any other non-Windows systems. We also do not officially support virtualization environments. Running the add-in on anything else than native Windows system is your own risk strictly.

There exists a detailed PDF manual covering all ApaLibNET functions. We are not making this document available for download on the Internet; prospective users can download the manual's table of content. If you are a license holder and would like to receive the latest version, please contact us by email.

We are now offering workshops in which users learn how to use the tool based on real-world case studies. More about the workshops here.

The add-in is designed to be an all-purpose tool. It is also possible to customize the functions and fine-tune them to your particular needs. You can contact us to discuss your particular requirements.

The table of content of the PDF manual gives an overview of all functions and their arguments. The functionality of the add-in includes...

• Andrew Lo's Active-Passive Ratio
• Equal-Volatility-Contribution portfolio construction (i.e. risk parity portfolio, equal risk contribution portfolio); robust and exact solutions
• LPM/ UPM, Co-LPM/Co-UPM, Asymmetrical Co-LPM/UPM Matrix, Symmetrical Co-LPM/UPM Matrix
• Parametric VaR: Normal VaR, Modified VaR (Cornish-Fisher Expansion) and NIG VaR
• Parametric CVaR: Normal CVaR
• Historical VaR, CVaR, Shorfall Probability
• Historical Value-At-Gain, Longfall Probability
• Autocorrelation, Partial Autocorrelation Function including confidence bands
• Ljung-Box Q Test for significant autocorrelations
• Normal QQ Correlation, Normal QQ Plot
• Generation of exponential weighting schemes given a halflife/lambda value
• EWMA volatility, correlation, covariance, beta, arithmetic mean
• Conversion Lambda-to-Halflife and vice versa
• Stochastic dominance of any order
• Normal Inverse Gaussian (NIG) distribution functionality: pdf, cdf, invcdf, parameter/moment conversions and maximum likelihood parameter estimation
• Efficient simulation of normal, student's T and NIG distributions for bootstrapping purposes
• Spearman Rank Correlation Coefficient
• Maximum Drawdown, Drawdowns, Underwater Returns, Nth-Non-Overlapping Drawdowns
• Maximum Runup, Runups, Overwater Returns, Nth-Non-Overlapping Drawdowns
• Losing Runs, Winning Runs
• Tracking Error, Beta, Residual Risk, Treynor/Mazui Gamma, Volatility
• Traditional Risk-Adjusted Performance Measures: Sharpe Ratio, Treynor Ratio, Jensen's Alpha, Information Ration (active returns), Information Ratio (residual returns)
• Alterantive Risk-Adjusted Performance Measures: Sortino Ratio, Upside Potential Ratio, Kappa3, Omega, Burke Ratio, Sterling Ratio, Calmar Ratio, Excess Return on VaR, Modified Sharpe Ratio, Conditional Sharpe Ratio, Adjusted Sharpe Ratio, Rachev Ratio, Generalized Rachev Ratio
• Style Analysis: average weights or rolling weights based on a certain window size
• Dynamic Histogram
• Empirical Distribution Function
• Jarque-Bera Test for Normality
• Various utility functions for time series data management
• Bull/Bear Market Returns , Up/Down Market Returns, Upper/Lower Returns
• Geometric, arithmetic average and cumulative returns, conversion of discrete/continuous returns
• Blundell/Ward filter for the "unsmoothing" of returns with first-order autocorrelation
• Simulation of uniform variables from the Clayton and Gaussian copulas
• Ex post asset covariance and correlation matrices
• Statistical factor models: factor extraction with Principal Component Analysis (PCA), factor alphas, betas, weights and portfolio variance decomposition into factor risk and residual risk
• Average pairwise correlation matrix
• Simulate values from the multivariate normal distribution
• Mahalanobis distance between two data sets
• Univariate/bivariate outliers and confidence region for the bivariate Gaussian distribution
• Rank correlation coefficients: Kendall's tau and Spearman's rank correlation coefficient
• Triangular distribution (pdf, cdf, inv, mom, rnd, sim)
• Simplex Sampling (random portfolio weight generation)
• GARCH(1,1) parameters maximum likelihood estimation, calculation of conditional volatility, forward and expected forward volatility, charting
• Factor model mathematics (portfolio risk and return, covariance matrix based on factor exposure vector and systematic and residual risk)
• Mean-variance portfolio optimization: efficient frontiers for restricted or unrestricted weights, dynamic efficient frontier, minimum variance portfolio weights
• Risk contributions (marginal, component, percentage) to portfolio volatility, normal value-at-risk and modified value-at-risk
• Eigenvalues and Eigenvectors
• Generation of return data sets that exactly reproduce defined returns, volatilities and correlations
• Bayesian risk & return estimators for portfolio optimization: Bayes-Stein, Deloit/Wolf, Jorion
• Augmented Dickey-Fuller test for unit root (useful for detecting the presence of cointegration in pairs trading)
• Hurst coefficient (an indicator for the presence of momentum or mean reversion in return time series data)
• Waterfall chart: calculation of necessary inputs such that a stacked bar chart becomes a waterfall chart
• Attribute Linking: chain-linking of absolute (constituent contributions) and relative (attribution effects) portfolio attributes.
• Tail Index estimation (Hill method)
• Two-sided t-test (test whether the sample mean return is statistically different from another value).
• Resampled Efficient Frontier (the frontier in mu/sigma space, constituent weights of portfolios on the resampled frontier
• Time aggregation of returns: conversion of time series to time series with lower frequency; a convenient way to check the validity of the "square root n" rule in the time aggregation of volatility
• Consolidation of portfolio/benchmark segment data for flexibility in the calculation of performance attribution effects
• Random portfolio weights (biased/unbiased, restricted/unrestricted versions)
• Nelson/Siegel/Svensson yield curve modeling: calculation of points on the N/S/S curve, bonds valuation based on the N/S/S curve
• Drawdown-At-Risk and Conditional Drawdown-At-Risk (both historical)
• Historical Interim Value-At-Risk
• Money-Weighted Return (Dollar-Weighted Return, Internal Rate of Return) from portfolio valuations and in-/out flows and their time weights
• Modified Dietz and Original Dietz Time-Weighted Return from portfolio valuations and in-/out flows and their time weights
• All functions can be used in VBA code, i.e. in user-defined functions as well as classical Excel macros.
• Lower and bounds for editing valid correlation matrices
• Inverse percentile function
• Population kurtosis and skewness
• Expected maximum drawdown of a Geometric Brownian Motion
• Z-Score, Modified Z-Score
• Outlier identification with Z-Score and Modified Z-Score
• Mean Absolute Deviation from Mean, Mean Absolute Deviation from Median
• Normal Mixture Distributions
• Shrinking a valid correlation matrix towards a target
• Parametric Value-At-Risk from conditional and unconditional GARCH(1,1) volatilities
• Linear Multiple Regression (OLS): parameters, R-squared, adjusted R-squared, t-test and p-values
• Chow test for structural breaks
• Conversion of price series into return time series
• Resampling from time series with the option to preserve serial dependencies
• Linear or Non-Linear Dual Alpha / Dual Beta Single-Index Model
• Logistic distributions
• Tail risk attribution of Modified Value-At-Risk
• A fast and high-quality random number generator (Multiply-With-Carry)
• CPPI (Constant Proportion Portfolio Insurance) strategy simulation
• Stochastic cash flow analysis, Monte Carlo wealth simulation for finanical planning and asset liability management (ALM)
• Contributions to portfolio skewness, kurtosis and correlation
• Average correlation and dispersion of correlations
• Surplus optimization
• Testing the validity of a correlation matrix
• Determining the weights in the Most Diversified portfolio (portfolio which maximizes the Diversification Ratio)
• Implied correlation, implied correlation matrix
• Hodrick-Prescott filter
• Moving Average Convergence Divergence (MACD) indicator
• Trade profile for portfolio return, volatility and Sharpe Ratio
• Incremental contribution to volatility and Sharpe Ratio
• Turnover calculations based on two percentage allocations

If you have additional questions, please contact us by email.