POW! Automation

Advanced Applications

The POW! Frontier and POW! Toolbox Wizards are, by design and because they are hosted within Excel, extremely flexible tools. However some users may find that they have an application that cannot readily be implemented within a spreadsheet. This may be because they wish to:

Make intensive use of the POW! functions for purposes such as
- back-testing
- investigating the effects of varying constraint values
- calculating resampled efficient frontiers to assess sensitivity to risk estimation errors
- testing different weighting schemes
Extract data from an existing database, price feed or other source
Integrate POW! with their own analysis functions and routines for
- Integer programming heuristics
- Forced beta and other specialised regressions
- Mixed use of price and return time series
Create a customised user interface

To meet these and similar needs we provide Automation versions of POW! which give direct access to the software functions by means of an OLE Automation library.

Using Automation to harness the full power of POW!

A POW! Automation application can be written in a number of OLE Automation compatible languages including:

Microsoft Visual Basic, Stand-alone or VBA
Microsoft Visual C
Borland Delphi

Setting a constraint in POW! Frontier using Visual Basic

Automation versions of POW! Frontier and POW! Toolbox are not sold under an additional licence but are provided at no extra charge with a Site Licence. POW! Automation users receive:

An automation enabled version of POW! Toolbox or POW! Frontier
Documentation of the relevant Automation functions
Sample VB code that can be used as a template for their own application.

See below for specific information about Frontier Automation and Toolbox Automation

Architecture

The diagram below shows the layered architecture of POW! Automation with the C libraries (blue box) at the lowest level and POW! Frontier and POW! Toolbox Wizards within Excel (pink box) or, alternatively, some other application using one of the POW! Automation modules (green box) at the top level facing the user.

POW! Frontier Automation

The POW! Frontier Automation library consists of a group of functions which are called sequentially to define the problem to be solved and then to retrieve the required results from the optimiser. An optimiser could be invoked by a single function call, passing all the parameters, and returning, in some form, all the results; but using multiple sequential calls adds greatly to the flexibility, making it possible to repeat the optimisation a number of times, calling a few functions to change a subset of the parameters before each optimisation.

The POW! Frontier Automation library handles portfolio optimisation problems in their natural form with factor correlations, transactions costs, relative constraints and other features which could not be passed directly to a generic quadratic optimiser which might be part of a mathematical subroutine library.

The following VB code examples show how the POW! Frontier Automation library is used. There is a full list of POW! Frontier Automation functions in a separate table.

Before Optimisation .... Setting the benchmark weights and some Constraints

'* Set the Benchmark weights
If POWF.SetPortfolio(BMWeightsArray, powPBenchmark) <> powOK Then Error PowError

'* Set the Holdings limits
RetCode = POWF.SetHoldingsLimits(LLimsArray, powCLB powCRBM)
If RetCode <> powOK Then Error PowError
RetCode = POWF.SetHoldingsLimits(ULimsArray, powCUB powCRBM)
If RetCode <> powOK Then Error PowError

'* Set the limits of the General Constraints
'* ConstraName is a array of strings containing the names of the constraint coefficients
For ColCount = 1 < STRONG > To NGenconstrs
     RetCode = POWF.SetConstraint(ConstrName(ColCount), _
           MandConstrRange.Cells(NAssets ColCount, colBM).Value, _
           MandConstrRange.Cells(NAssets ColCount, colRes).Value, _
           powCRBM)
     If RetCode <> powOK Then Error PowError
Next

After optimisation .... Retrieving results from the optimiser

'* Get the other properties of the optimal portfolio
'* Optimal portfolio specified by a relative return value OptRetRBM

OptRskRBM = POWF.FrFigure(OptRetRBM, powTRET + powTRBM,_
                                      powTSD + powTRBM, NAssets, False)(1)
OptRetAbs = POWF.FrFigure(OptRetRBM, powTRET + powTRBM, _
                                                  powTRET + powTABS, NAssets, False)(1)
OptRskAbs = POWF.FrFigure(OptRetRBM, powTRET + powTRBM, _
                                       powTSD powTABS, NAssets, False)(1)
OptHoldingsAbs = POWOpt.FrWeights(OptRetRBM, powTRET powTRBM, _
                                      powTABS, NAssets, False)
OptHoldingsRBM = POWOpt.FrWeights(OptRetRBM, powTRET powTRBM, _
                                      powTRBM, NAssets, False)

POW! Toolbox Automation

The POW! Toolbox Automation library is a set of three classes: Toolbox, History and Analysis. A Toolbox object is used to create histories of time series and to run analyses on histories. Each history is represented by a History object which is loaded with time series. Each analysis is represented by an Analysis object which takes a history and runs the statistical analysis on it. The Analysis object then has methods (or functions) to retrieve results and labels. Breaking up the statistical sequence into logical blocks like this allows a high degree of flexibility and makes running multiple analyses, to test weighting schemes or to look for auto-correlation, far more flexible.

The POW! Toolbox Automation library is designed to handle financial time series and facilitates use of statistical functions.

The following VB code examples show how the POW! Toolbox Automation library is used. There is a full list of POW! Toolbox Automation functions in a separate table.

The coding is very simple to implement.

Setting and analysing a time series

' previously defined
' Dim MyToolbox As Toolbox, MyHistory As History, MyAnalysis As Analysis

' load an asset series from Excel ranges into a History object
MyHistory.AddSeries rangeName, rangeData, SeriesParams

' load history into an analysis object and run an ordinary least squares analysis
Set MyAnalysis = MyToolbox.AnalyseHistory(tbf_OLS, MyHistory, SeriesParams)

This example shows how to retrieve results from the Analysis class:

Placing results in Excel ranges

' paste asset names as a row vector (tbi_... and tbf_... are library defined constants)
MyAnalysis.PlaceResults rangeNames, tbi_AssetNames, tbf_AsRow

' paste correlations as left/lower triangular matrix
MyAnalysis.PlaceResults rangeCorrels, tbi_AssetCorr, tbf_Left

' paste skewnesses as a column vector
MyAnalysis.PlaceResults rangeSkews, tbi_AssetSkew, tbf_AsCol