pixel.gif (43 bytes)


Portfolix Technical Whitepaper

Keith Siilats
Version 06/13/2002

  1. Executive overview

  2. Fixed Income Money management with $1bn+ size managers

  3. Technology Primer on Microsoft.NET/OLAP

  4. Portfolix extensions for fixed income OLAP

  5. What is the current situation in companies?

  6. An example implementation

  7. Developing and extending Portfolix

  8. Portfolix User interfaces

  9. Contents of the Portfolix Cubes

  10. Sample Canned reports

  11. References and further reading

Executive Overview                                                                           

This document is an overview of Portfolix (www.portfolix.com) an Enterprise tool for fixed income portfolio management.

The whitepaper will first give an overview of the particular functionality required by fixed income portfolio management tools viewed from the users perspective. Paper will then explore the existing OLAP technology implementations in a large data warehouse setting. Some examples will be given from CRM and other areas.

Finally I will show how OLAP is applied in particular by Portfolix to manage fixed income portfolios of any size. In particular, I will discuss how dimensions need to be grouped into two hierarchies: bond and account. I will also talk about the most important measure in a fixed income cube – the duration, and how OLAP is an extremely effective way to slice and dice duration automatically.

The paper will conclude with links to evaluation and demo software, which will allow you to read more about the topic and test some of the functionality of Portfolix/

Fixed Income Money management with $1bn+ size managers         UP

There are about 200 money managers with over $1bn of assets under management in fixed income in the United States. The way they operate is by getting mandates from clients (normally in the thousands, each of which is few to few hundred million dollars in size) and will buy bonds with this money (normally a few thousand different ones at any given time). Now an account will typically contain a few hundred bonds, so most bonds are held in several accounts. Trading is done in lumps (say thousand bonds) that are then allocated to accounts (say to 10 different accounts, 100 units each).

Each account will have a benchmark, chosen by the client. There are about 10 standard benchmarks, but clients can create combinations of the standard ones (40% Lehman Mortgage, 60% Lehman Corporate). The benchmark is like an account that holds every single bond in existence. Now the accounts have to perform very similar to the benchmark, however, buying a small quantity of every bond is not practical because of transaction costs. So money manager task is to buy a few bonds to the account so that the account will mimic the behavior of the benchmark as closely as possible.
In order to do that all bonds are grouped together by some characteristic. Then as long as the account has the same weights in those characteristics as the benchmark, then the account will behave like a benchmark.

The most important characteristics are the credit rating of a bond (AAA), its sector (Utility) and its duration (5 years), although Portfolix includes more than 20 characteristics for different mortgage, corporate, paper, junk bond, different rating agency ratings etc. These will be called the bond based dimensions.

The account based dimensions are the portfolio managers, product lines, benchmarks and sub-benchmarks by common restrictions. Because there are too many accounts to look at them individually every day, the portfolio managers group accounts together and look at the averages in any group. If the average is too different from the benchmark, then they need to do trades.
The difference from the benchmark can either be viewed in terms of straight market value, the percentage weight or the duration difference. These are the measures in OLAP. Portfolix includes more than 15 measures including changes in durations over past quarter or month, minimum, maximum and average based rating measures, yield and effective durations etc.

Portfolio managers also view time series reports as to how the allocations have changed. They will also want to group accounts by restriction categories (accounts that limit high yield to maximum 10% for example) and whether they are in compliance or not.

Finally portfolio managers will take bets and differ the allocation from the benchmark. The view is normally expressed in terms of durational yield curve movements for example, and portfolio managers will need a way to translate this into the bond quantities that need to be bought or sold.
Duration is essentially a measure of how sensitive the bond price is to the movements in the interest rate. As interest rate is normally exogenous, then 1000 bonds with duration of 2 are about as risky as 2000 bonds with duration 1, so they are given similar weighting in portfolios. Mathematically, duration is the partial derivative of bond price with respect to the interest rate expressed as a percentage. Bonds with longer maturity have longer duration (because if the interest rate rises you are expected to get a higher return for longer time, however, because the final payback amount of the bond is always specified, the price will drop more) however, duration is normally less than the time to maturity because some part of the bond is paid before in coupons.
Technology Primer on Microsoft.NET/OLAP                                    UP

Since 1980-s most actions, like buys and sells are recorded in a transactional SQL based system. It can store numbers and textual data. OLAP is a system on top of these servers (OLTP servers) that became into wider use first in 1995. There are number of OLAP vendors, most notably Hyperion and Cognos (www.olapreport.com).
Most portfolio managers use excel extensively for managing their ideas. However, it is hard to automate or "productionize" excel and hard to integrate it with SQL servers from large vendors like Sybase, Oracle and DB2.

So Microsoft released his own version of SQL server and a host of other servers inside its .NET strategy. Within a year those have become the fastest servers in the market (www.tpc.com), most widely implemented and cost effective. They integrate very well with excel and other office tools.
Portfolix is build on top of Microsoft Analysis Server, the OLAP server that comes with Microsoft SQL server license ($1500 dollars and up, www.microsoft.com/sql). However, it does not need the Microsoft SQL server to operate, any server will do and indeed Portfolix has been implemented on Oracle as well as Sybase.

OLAP is thus a server that deals with data. Whereas SQL organizes data in a relational format in tables, OLAP organizes data into multidimensional cubes consisting of dimensions and measures. SQL tables look like an excel sheet with data, Cubes are like PivotTables in excel that are hierarchical and contain lots of calculations and access the data from underlying relational database.

Why OLAP is so successful is that is extremely flexible and has very high performance. It pre-calculates most of the "interesting" numbers and then uses those intermediate numbers to calculate the answers to business questions. In technical terms, a standard database has to recalculate every "group by" query whereas OLAP server stores aggregations.
In addition, by modeling only dimensions and measures, the end user is later on allowed to combine them visually in excel into any type of report. With straight OLTP type systems one will have to use Microsoft Query type SQL editing tool which has a lot steeper learning curve and smaller performance.

Finally Portfolix is also a set of data warehouse technologies which support OLAP and are popularized by Kimball books. Portfolix makes business data into 5th normal form and snowflake schema. It also adds scheduling and automatic data validation routines.

Clearly OLAP server is an additional computer with additional administrative costs so it is not suitable for everyone. If the present SQL database is fast enough and the excel reports are not too big and there is no need to source control and document multiple developers, then there are few benefits to learning OLAP. In practice the portfolios of over $1bn will clearly benefit from OLAP, $100m to $1bn will probably benefit and for portfolios less than $100m it is less clear that they will benefit.
Portfolix extensions for fixed income OLAP                                      UP

There are no successful OLAP implementations in Fixed Income so far because of the complexity in calculating duration based measures and the non-aggregatable features of cubes that require aggregations of accounts to be treated differently from aggregations of bonds (averages vs. sums). However with extensive industry background and groundbreaking research from AST members in NYU Courant Mathematics Institute, members of AST were able to solve those problems very elegantly creating a fast, flexible and self documenting system of cubes. Those cubes with the data warehouse design and maintenance tools form the core of Portfolix.

So in order to effectively manage fixed income accounts against a benchmark one needs to be able to slice and dice the data. Real-time slicing and dicing using traditional OLTP (SQL servers) is extremely slow. OLAP is then a hierarchical database solution created to facilitate fast real-time slicing of very large databases.
What is the current situation in companies?                                       UP

  1. Companies have databases where they keep data about what they have done. Normally you already have databases of what bonds where bought for which account and what are they worth today. Its either in a text file, excel file or a database. Portfolix can work with any source data.
  2. Retrieving that data in a useable version is difficult. If you want it to be accurate, fast and in an excel format, it’s pretty much impossible.
  3. Online analytical processing (OLAP) is an increasingly popular technology that can dramatically improve business analysis, that has been characterized historically by expensive tools, difficult implementation, and inflexible deployment. Microsoft has tackled the OLAP problem and created a solution that makes multidimensional analysis accessible to a broader audience and potentially at a significantly lower cost of ownership.
  4. So OLAP makes the retrieving of data in a useful way possible (e.g., "show me exposures by sectors for this account").                    

An example implementation                                                                UP 
  1. Make daily accounting data from mainframe systems accessible in a SQL server (Oracle, Sybase, Microsoft).
  2. On a separate computer, run an OLAP server that manipulates that SQL data every night and summarizes it in a series of useful formats.
  3. Define most important dimensions that people might wish to look at. Presently the dimensions are sectors, ratings, duration buckets, maturities, accounts by their restrictions, and benchmarks that they use. The complete list follows at the end.
  4. Make that all available in an interactive excel spreadsheet format and create a few reports that people use most often (e.g. duration in MBS positions by account).
  5. Test to make sure numbers look the same as in mainframe.
  6. Obtain the benchmark information from Lehman SunBond, Merrill Lynch and others.                                                                        
  7. Return attribution is inserted into the same SQL database from the return attribution tools and joined into a virtual cube.
  8. Risk measures are similarly pre calculated in the proprietary risk tools and inserted into the database.
  9. Once a nice report is created in excel that needs PDF or html formatting, the MDX is copied from Excel and inserted into either a webpage or reporting tool supporting ADO.
  10. Once an excel sheet is created that needs daily updating it is copied to a specified shared network folder and Portfolix will update its PivotTables every night.
  11. Documentation is automatically generated in XML format from the cubes whenever a measure is added or changed
  12. Portfolix server is integrated to the DBA-MIS type of framework with emails on successful processing, log file storage etc.

Developer and extending Portfolix.                                                  UP 

Portfolix is easily extendible by in-house developers.
OLAP implementation consists of 3 parts: SQL server, OLAP server and Excel sheets. SQL server part needs to be created (probably looking like "views" of your existing database), the OLAP fixed income cubes will be licensed out of the box, and similarly the server that does nightly processing and standard excel reports. Portfolio managers will then customize the Excel reports to suit their reporting needs.
1.    SQL Server Part:

2.    OLAP server part:                                                                       


3.    Excel, Visual Basic applications, Web pages and other reporting part:                                                                                                     UP 

Portfolix User interfaces                                                                     UP 

 Excel PivotTables
 Web based interfaces (ASP, JSP)
 MDX query displaying clients (Cognos, Intellibrowser)

Contents of the Portfolix Cubes

 Accounts – real time account information
 Yields – real-time yield curve + historical information by ratings and sectors
 HistoricAccounts – a monthly snapshot of the account data allowing plotting of timeseries of sector weights, differences from benchmark etc.

 Account Based Dimensions
     by team (growth portfolios) and portfolio manager
     by benchmark, sub benchmark, account number and name
     by restriction Categories
         Allow Junk,
         Allow CMBS,
         Allow International
         Allow Split Ratings
         Etc
 Security Based Dimensions
     Single Security
         Bond adp/bond cusip/bond issuer– various informational convenience breakdowns
     Bond duration
     Bond maturity
     Bond Ratings                                                                                   
         Bond rating (Moody’s)– starts from investment grade/non investment grade/treasury, goes down to A/AA/B and then down to the actual rating, then to issuer and finally cusip
         Bond rating min and max same as above, except instead of moody it uses minimum rating.
     Bond sector, starts from top level, like spread product and cash and breaks down to industry level
     Bond country – this includes the development index as top level allowing to separate out developed and non developed countries
     Bond currency – starts from us / non-us distinction
     Mortgage sector specific
         loan types (i.e., agency)
         term (30, 15, 7)
         production
         coupon buckets
Measures                                                                                             UP 

 Market value (account or security)
 Par amount (security)
Original Face – (security - some of this data is suspect (and not just for mortgages))
 Eduration (weighted effective duration) - (account or security)
 Yduration (weighted spread duration) - (account or security) Weight – (security)
 Ratingmoody, rating sp, rating average, rating min, rating unrated market value
 Leverage is market value of leverage less than 1 year and Us bond
 Last_update – when the cube was last updated, in mmdd format
 Holding count – how many positions

Sample Canned reports                                                                       UP 

 Duration bucketing
 Rating distribution
 Sector distribution
 Individual Positions
 Mortgage Report

References and further reading                                                         

  1. www.portfolix.com has a working test server. All you need to do is open an excel file from that webpage, and it will automatically connect securely to the central Portfolix server with sample data.
  2. To learn the backend you need to start with a database.www.embarcadero.com has an evaluation version DBArtisan product to create databases and www.microsoft.com/sql has an evaluation version of the SQL server. A Kimball or Microsoft Press book from www.amazon.com plus the articles on msdn.microsoft.com will get you started.
  3. www.olapreport.com talks about OLAP vendors and the industry in general
  4. To learn about duration calculations and fixed income start from http://www.prufn.com/learningcenter/
    and typing "fixed income tutorial" into www.google.com