ApaLibNET - Advanced Portfolio Analytics

The Excel add-in version is sold as an annual subscription. The subscription price for using the Excel add-in on five computers is 295 CHF per year (click here to convert this amount to a currency of your choice). Using the add-in on one computer for one year is 110 CHF (click here to convert this amount to a currency of your choice). The subscription also includes example spreadsheets illustrating the use of the functions, a detailed PDF documentation with formulas and information on the algorithms used and updates with new functions and enhancements for existing ones. Additionally, a growing number of video tutorials are available, providing live examples on how to work with the library efficiently.

If you are interested in using the add-in on more than 10 computers, please contact us to receive a customized offer with attractive prices and additional services like support or training.

The add-in can be downloaded and activated immediately after purchasing. Click here to purchase.

The add-in will only work if the following technical requirements are fulfilled...

1. Microsoft Excel 64-bit or 32-bit installed
2. Microsoft .NET Framework up to 4.6
3. Write access to the folder in which the add-in is installed
4. Microsoft Excel security settings that allow the execution of XLL add-ins
5. Internet access
6. Security settings that allow unrestricted traffic to/from www.andreassteiner.net
7. Security settings that allow receiving emails from www.andreassteiner.net

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 up to version 4.6, no other software is required.

By default, the add-in comes with a setup wizard 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 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.

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

The add-in works with both the 32-bit and 64-bit versions of Microsoft Excel. Moreover, it is not necessary to buy a new license when switching from the 32-bit version of Excel to Excel 64-bit; switching only requires a re-installation of the add-in. When purchasing a license which allows using the add-in on several computers, the add-in can be used on 32-bit and 64-bit machines concurrently.

As the ad-in is based on the .NET framework and not Excel, there are no compatibility issues with Excel language versions. The add-in function names and function arguments will always show in English. Given the popularity of English in financial terminology, we have no plans to release language versions of the add-in.

After purchasing, you will receive various information by email and a link to a download page. The setup file can either be delivered by download in a web browser or sent to an email address. Activating and starting the add-in requires access to the Internet. All of these activities require unrestricted access to/from our websites and unrestricted exchange of emails.

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.

The add-in is being sold since 2011 to a large, world-wide and diverse client base. We are proud of not having experienced a single case in which the installation and operation of the add-in failed for technical reasons.

If you would like to evaluate whether the add-In works on your particular system, you can run the trial version which is available here.

This add-in is based exactly on the same technology as our Advanced Portfolio Analytics add-in. The trial version comes with four test functions only, not with the actual function library.

Note that we do not provide support for the trial version. If you cannot download, install or run the trial version, please review the system requirements. We will not respond to emails relating to the trial version under any circumstances.

Despite the fact that there are no reported compatibility issues, we recommend testing the trial version first in complex system environments; we do not refund software purchases.

There exists a detailed PDF manual covering all functions. We are not making this document available on the Internet for copyright reasons; 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...

• Drawing Dendrograms as Excel charts
• Waterfall allocation (portfolio construction based on hierarchical clustering)
• Clustering Algorithms: Hierarchical clustering , k-means Clustering
• Calculating downside and upside correlation matrices
• Resampling from multivariate time series data, considering autocorrelations
• Ulcer Index, Ulcer Performance Index
• Non-parametric confidence bands for mean, volatility, skewness, kurtosis, correlation, shortfall probability
• Cauchy distribution: inv, cfd, pdf, rnd, sim
• Critical Line Algorithm: implementation of Markkowitz procedure to determine the exact mean-variance efficient frontier
• Simulation of valid random correlation matrices
• Black / Litterman portfolio construction
• Upside/Downside Capture Ratios
• Essential matrix and linear algebra functionality not covered by Excel's built-in functions
• Ex post contributions to portfolio volatility, tracking error and beta
• Dispersion measurement: Shannon entropy
• Singular Spectral Analysis
• Simulation of price processes (GBM, jump-diffusion, ARMA(2,2), GARCH(1,1), normal mixture)
• Conversion of continuous/discrete correlation coefficients
• Portfolio Construction based on Risk Budgets (percentage contributions to volatility)
• Andrew Lo's Active-Passive Ratio
• Statistical classification: k-Means clustering
• Speed/Accuracy Modes: Fast, Balanced, Accurate
• Fixing an invalid correlation matrix
• Contributions to Sharpe 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
• Simulation of a NIG distribution with randmized parameters
• 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
• Pricipal Component Analysis (PCA): 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.