Investment Performance

One of the fundamental functions of a data warehouse in an investment company (wealth management, asset management, brokerage firm, hedge funds, and investment banking) is to explain the performance of the investments.

If in Jan 2015 we invest $100m and a year later it becomes $112m, we need to explain where this $12m is from. Is it because 40% of it was invested in emerging market? Is it because 30% of it was invested in credit derivative and 50% in equity? Is it because of three particular stocks? Which period in particular contributed the most to the performance, is it Q3 or Q4?

Imagine that we invested this $100m into 100 different shares, each of them $1m. These 100 shares are in 15 different countries, i.e. US, UK, France, Canada, India, China, Indonesia, Mexico, etc. These 100 shares are in 10 different currencies, e.g. USD, CAD, EUR, CNY, MXN, IDR, etc. These 100 shares are in 15 different sectors, e.g. pharmaceutical, banking, telecommunication, retail, property, mining, etc. These 100 shares have different P/E multiples, i.e. 8x, 12x, 15x, 17x. These 100 shares have different market capitalisation, i.e. small cap, mid cap and large cap. And we have 50 portfolios like that, each for a different client, some are for open funds. In the case of a fixed income investment (bonds), there are other attributes such as credit rating (AAA, A, BBB, etc.), maturity profile (0-1 year, 1-3 years, 3-5 years, etc.), and asset class, e.g. FRN, MBS, Gilt, Corporate, CDS, etc.

Every day we value each of these 100 shares, by taking the closing prices from the stock exchanges (via Bloomberg EOD data, Thomson Reuters, or other providers) and multiply them by the number of shares we hold. So we have the value of each share for every single working day. They are in different currencies of course, but we use the FX rate (closing rate) to convert them to the base currency of the portfolio.

A portfolio has a benchmark. The performance of the portfolio is compared to the performance of the benchmark. A portfolio manager is measured against how well they can outperform the benchmark.

The mathematics of performance attribution against the benchmark is explained well in this Wikipedia page: link. That is the basic. Now we need to do the same thing, but not just on 2 rows, but on 100 rows. Not just on asset allocation and stock selection, but also on all the other factors above. Not only against the benchmark, but also comparing the same portfolios month-to-month, or quarter-to-quarter.

The resulting data is a powerful tool for a portfolio manager, because they can understand what caused the outperformance. And more importantly, what caused the under performance, against the benchmark, and between time-points.

This month we beat the benchmark by 1%. That’s good, but what caused it? Why? It is important to know. This month we grow by 1%. That is good, but what caused it? This month we are down 2%. We obviously need to know why. Our client would demand an explanation why their money which was $100m is now $98m.

That would be a good reason for having a data warehouse. The value of each and every positions*, from each and every portfolio, for each and every working day, is stored in the data warehouse. And then on top of it, we apply mathematical calculations to find out what caused the up and down, not only at portfolio/fund level, but for each currency, country, industry sector, etc., for any given day, week, month and quarter. That is worth paying $500k to develop this analytical tool. We from the BI industry may be calling it a BI tool. But from the portfolio manager point of view that is an investment analytic system.

*Position: a position is an financial instrument that we purchased, and now hold in our portfolio. For example, a bond, a share, or a derivative. In addition, we also have cash positions, i.e. the amount of money we have with the broker/custodian, as well as MTM margins, repo, FX forwards, and money market instruments, such as cash funds. A position is time-valued, i.e. its value depends on time.

This tool enables the portfolio managers (PMs) in an investment company not only to know the breakdown of their portfolios at any given day, but how each section of the portfolio moved from month-to-month, day-to-day. In addition, if we also put risk measures in there, such as stresses, risk analytics, or SRIs* (I’ll explain all 3 in the next paragraph), the PMs (and their financial analysts) will be able to know the individual risk type for each section of the portfolio, on any given date, and how those risks moved from month-to-month, day-to-day.

*Stresses, risk analytics and SRIs: a stress is a scenario that we apply to all positions in the portfolio. For example, what if the interest rate is up by 0.1%? By 0.25%? By 1%? What if the FX rate is down by 0.1%? By 1%? And also other factors, such as oil price, inflation rate, equity prices, etc. We can also apply an “event”, i.e. during September 11, the S&P moved by X%, FTSE 100 by X%, Gilts by X%, EMD bonds by X%, and FX rates by X%. There are also other historical dates when the market went south. If we apply those “events” into our portfolios, what happens to the value of each position? Value of the overall portfolios? Value of each section of the portfolio, i.e. Asia stocks, EM, or Small Caps?

Risk analytics are numbers which reflect a certain risk to an investment portfolio. For example, duration reflect how much each position will be impacted by an interest rate raise. For fixed income the risk analytics are: PV01, DV01, IE01, CR01, duration (modified duration, spread modified duration, effective duration), credit spread (spread to Libor, spread to benchmark security, spread to Treasury), yield (yield to maturity, effective yield, yield to call, yield to put, yield to worst, running yield, simple yield), convexity (how sensitive the duration is to the change of interest rates). For an equity portfolio we have different risk analytics (they are mainly financial ratios of the issuer).

SRIs means socially responsible investing. The theory is, the value of a share (or a bond, or a derivative) is affected by how much the company care about the environment, by how well the company (or group of companies) is governed/managed, by how much the company promote human rights and social justice, how much it avoid alcohol, gambling and tobacco. Some data providers such as Barclays, MSCI and Verisk Maplecroft provide this SRI data in the form of scores, ratings and indices in each area.

The PMs will be able to know each of the 3 risk categories above (stresses, analytics and SRIs) for each positions within their portfolio, on any given day, and how those risks moved from month-to-month, day-to-day. That is a very powerful tool, and is worth creating. And that is one reason why we create a data warehouse (DW) for investment companies.

Not only for managing the performance (which is the most important thing in a PM’s career) but also to manage the risks. Because the DW is able to inform them how every part of their portfolios react to each event, each day/week/month (react in the context of the valuation, performance, attribution, and risk), the PMs will able to tell (at least predict) what will happen to the value, return, and risk on each section of their portfolio if such event happens again.