Data Warehousing and Data Science

21 October 2017

Swaps and Options

Filed under: Business Knowledge — Vincent Rainardi @ 6:12 am

Working in investment world often requires us to understand various asset classes. Sometimes we come across simple asset classes, such as shares, bonds, currency and commodity. But sometimes we come across complex asset classes / derivatives, such as swaps and options. In this article I’d like to give a flavour on swaps and options. There are other derivatives such as futures and forward, but I won’t be covering them today.


Swaps are essentially an exchange of two assets. The asset produces an income, so what really being exchanged is the income (the cash flow). In fact, we don’t have to have an asset. We just need to have a cash flow. For example, if the fixed income from a bond is exchanged to a floating interest rate.

Swaps comes in many different variations, but it is always in the form of A is exchanged to B. Typically A is floating price, and B is fixed price. Here’s some of them, alphabetically:

  • Asset Swap: a bond (fixed coupon) in exchange to a floating interest rate (LIBOR plus spread). Link
  • Commodity Swap: a commodity (floating price, usually oil) in exchange to a fixed price. Link
  • Credit Default Swap (CDS): a credit protection (from a bond going default) in exchange to a fixed payment. Link
  • Cross Currency Swap (CCS): a loan in currency A (fixed interest) in exchange to a loan in currency B (fixed interest). Link
  • FX Swap: a spot FX (today) in exchange to the same amount of a forward FX (tomorrow). Link
  • Inflation Swap: the inflation rate (such as RPI) in exchange to a fixed rate. Link
    Zero Coupon Inflation Swap is one lump sum payment at the end. Link
  • Interest Rate Swap (IRS): a floating interest rate (LIBOR + spread) in exchange to a fixed interest rate. Link
  • Total Return Swap: the income of an asset, plus the capital gain, in exchange to a fixed or variable payment. Link
  • Variance Swap: a variance of a price (the volatility) in exchange to a fixed payment (the strike). Link


Options is the right to buy or sell at certain price on a certain date.
The right to buy is a call. The right to sell is a put.
American: buy/sell before a certain date. European: on a certain date.

  • Bond Option: the right to buy/sell a bond at a certain price before a certain date. Link
  • Commodity Option: the right to buy/sell a commodity (e.g. oil or metal) at a certain price before a certain date. Link
  • Equity Index Option: the right to buy/sell an equity index (such as S&P 500) at a certain price before a certain date. Link
  • FX Option (or currency option): the right to exchange a currency into another currency (fixed amount) at certain rate before a certain date. Link
  • Interest Rate Option: the right to buy/sell a time deposit (EuroDollar, CD, T-Bill, EuroSterling) with a certain rate before a certain date. Link
  • Swaption: the right to enter a swap (typically Interest Rate Swap). Link

18 September 2016

Rating Dimension

Filed under: Business Knowledge,Data Architecture,Data Warehousing — Vincent Rainardi @ 2:32 am

In an investment banking data warehouse, we have an instrument dimension (aka security dimension). This instrument dimension is used in many fact tables such as trade, risk, valuation, P&L, and performance.

In this instrument dimension we have rating attributes (columns), from the 3 agencies (S&P, Moody, Fitch), an in-house rating and many combination between them. We may have the ratings at both the issuer/obligor level, and at instrument/issue level. We could also have a hierarchy of ratings, e.g. AA+, AA and AA- are grouped into AA. There are separate ratings for cash instruments.

Usual Design

The usual design is to put all rating attributes in the instrument dimension. This is because the granularity of rating is at instrument level. In other words, rating is an attribute of an instrument.

Issues with the Usual Design

The issues with doing that are:

  1. Cash and FX may not be created as an intrument but they can have ratings.
  2. Some fact tables requires the rating outside the context of an instrument.

An example for point 1 is a USD bank balance (settled cash) in a GBP portfolio, which is given a rating of AAA. In the instrument dimension there is no instrument called USD bank balance. The other example is USD cash proceeds which is an unsettled cash.

An example for point 2 is a fact table used for analysing the average ratings of a portfolio across different date. This is called Portfolio Credit Quality analysis. On any given day, a credit portfolio has 3 attributes: highest rating, lowest rating and average rating. The average rating is calculated by converting the rating letter to a number (usually 1 to 27, or using Moody’s rating factor, link), multiply the rating number with the weight of each position, and sum them up. There can be several different criteria, for example: including or excluding debt derivatives, funds, cash, or equity, and doing lookthrough or not.

For example, portfolio X on 16th Sep could have these different attributes:

  1. Average rating: AA+
  2. Highest rating: AAA
  3. Lowest rating: BB-
  4. Average rating excluding cash: AA
  5. Average rating excluding cash and derivatives: BBB+
  6. Average rating excluding cash, derivatives and equity: BBB-
  7. Average rating including funds: AA-
  8. Average rating with lookthrough: A+

So the fact table grain is at portfolio level and date. We therefore cannot use instrument dimension in this fact table, and therefore it is necessary to have rating in its own dimension.

Another example of a fact table which requires rating not at instrument dimension is sectoral credit quality. A credit portfolio consists of many bonds and credit derivatives. Each of these bonds (or CDS) are of certain industry sector. A bond issued by BP is in Energy sector. A bond issued by Santander is in Financial sector. The average rating for each sector is calculated by converting each rating to a number, then multiplying it by the weight and sum up for all positions in that sector.

If we use GICS (link) as an example, and government, on any given day a credit portfolio can have sectoral credit quality like this: (usually excluding cash and equity, but including credit derivatives and debt funds)

  1. Consumer Discretionary: AA+
  2. Consumer Staples: AA-
  3. Energy: A+
  4. Financials:AAA
  5. Health Care: BB-
  6. Industrials: BBB-
  7. Information Technology: AA-
  8. Materials: BBB+
  9. Telecommunication Services: AA
  10. Utilities: BB+
  11. Real Estate: B+
  12. Government: AAA

Above is the average rating for each sector, for a given day, for a particular portfolio.

Design Choices for Rating Dimension

To satisfy the above requirement (fact tables which analyse rating at a level higher than instrument, e.g. at sector level or portfolio level), we can design the rating dimension in several ways:

  1. Rating schemes and hierarchies are created as attributes
  2. Rating schemes are created as rows, with hierarchies as attributes
  3. Rating schemes and hierarchies are created as rows

Before we dive into the details I’d like emphasize that we should not try to find out which approach is the best one, because different projects will require different approaches. So instead of asking “What is the best approach” we should ask “What is the most suitable approach for my project?”

And secondly I’d like to remind us that if the rating data is always used at instrument level than it should reside in the instrument dimension, not in a rating dimension on its own.

Approach 1. Rating schemes and hierarchies are created as attributes


Of course we should always have RatingKey 0 with description = Unknown. This row is distinctly difference to NR (No Rating). NR means S&P gives the bond a rating of NR. RatingKey 0 means that we don’t have any info about the rating of this bond.

The distinct advantage of this approach is that we have a mapping between different rating schemes. Moody’s Baa3 for example corresponds to S&P’s BBB-.

The Grade column is either Investment Grade (AAA down to BBB-) and High Yield (HY, from BB+ down to D).

Approach 2. Rating schemes are created as rows, with hierarchies as attributes


The distinct advantage of approach 2 over approach 1 is RatingKey 33 means Aa3, we don’t have to tell it that we meant Moody’s, like in approach 1. In Approach 1, RatingKey 4 can means Aa3 or AA-, depending whether we meant Moody’s or S&P.

The distinct disadvantage of approach 2 compared to approach 1 is we lost the mapping between S&P and Moody’s. In Approach 1 we know that Aa3 in Moody’s corresponds to AA- in S&P, but in Approach 2 we don’t know that. In the majority of the circumstances, this is not an issue, because an instrument with Moody’s rating of Aa3, may not have an S&P rating of AA- any way. Instead, its S&P rating could be AA or BBB+. So the mapping between S&P and Moody’s are the book standard, academic assumption, not the reality in the real world. In the above example, nobody cares whether the corresponding S&P rating for Aa3 is AA-. What the fund managers and analysts want to know is what Moody’s really assigned to that bond.

Approach 3. Rating schemes and hierarchies are created as rows


The distinct advantage of Approach 3 over Approach 1 and 2 is that RatingKey 31 means AA tier, it is clear and not ambiguous. In Approach 1 and 2, RatingKey 2,3,4 all means AA tier.

The other advantage of Approach 3 is the ability to create short term rating and cash rating, because they are simply different schemes.

Why do we need to keep the Tier column in Approach 3? Because we still need to convert AA+, AA and AA- to AA.

Rating Value (aka Rating Number)

In some projects we need to convert the rating letters to numbers. For this we can create an attribute in the above rating dimension (any of the 3 approaches) called Rating Value. It is usually AAA = 1, AA+ = 2, AA = 3, AA- = 4, and so on. NR (No Rating) and WR (Withdrawn Rating) got 0 rather than 23.


Note that unlike in S&P and Moody’s schemes, in Fitch scheme the D rating is differentiated into 3: DDD, DD, and D. So DDD = 22, DD = 23, D = 24.


A common mistake is that people assume there are CC+ and CC- rating. There aren’t. Only CCC is split into 3 (CCC+, CCC, CCC-), but CC and C are not.

Sometimes the relationship between a rating in one scheme and another scheme is not a one-to-one mapping. For example, the Moody’s rating of Ca corresponds to 2 ratings in S&P scheme: CC and C. This is why approach 1 is tricky, because the rating does not have one to one relationship between 1 scheme and another. Another example is as I mentioned above, the D rating in S&P corresponds to 3 ratings in Fitch: DDD, DD and D.

3 May 2016

Asset Management Business Processes and Systems

Filed under: Business Knowledge — Vincent Rainardi @ 7:46 am

I am passionate and fascinated with Asset Management sector. It is a complex industry. To work as a BA in asset management we need to know both investment banking (security, trading, credit risk, compliance) and investing (portfolio management, asset classes, hedging, performance). In this article I will try to explain asset management business from system point of view, i.e. what systems are required to run the business. But first a brief intro about the business itself.

Business Overview

An asset management business makes money from the fees they charge the clients. There are 7 main business processes in an asset management company:

  1. Product development
  2. Distribution (means sales and marketing)
  3. Client on-boarding
  4. Investment management
  5. Risk management
  6. Client reporting
  7. Compliance

To do the above 7 business processes we need these 7 departments: investment, IT, marketing, legal, finance, HR, facilities.

  1. Product development is the process of creating a product. A product in asset management is either a public fund, a pooled fund, or a managed portfolio (discretionary or mandate). The product development team must decide the product goals, asset allocation strategy (including limits), hedging policy, restrictions (not allowing repo for example), the benchmark and the fund manager. If it is a public fund, the team must decide (with the help of the legal team) the legal structure, the trustee/depositary, the registrar, and the country of domicile (for tax purposes). With the help from marketing and finance, they also need to determine, launch date (fund launch and unit launch), the sales policy, the distribution channels (including commission structure, marketing strategy, and platforms), capacity (maximum size for soft close), the share classes, ISA wrapper, minimum investment (initial and monthly), charges & fees, the spread, the pricing (single/dual), valuation point, and seed money.
  2. Distribution is basically sales and marketing. Marketing covers advertising and communications with pension funds, investment advisers, family offices, investment manager databases, brokers, asset managers, HNWIs, and financial services companies; and create marketing material and literature. Sales meet with prospective institutional clients (e.g. pension funds, pension scheme trustees), draft and review client agreements, being an account manager, manage client relationships, sorting out client needs and requests, reviewing the performance of client mandates, and proactively develop the business.
  3. Client on-boarding process is about gathering information and requirement from a new client, and enter it into the system (including background check, account setup, credit ratings), drafting and signing agreements for client mandates (legal will review first). We need to ensure that we comply with the regulations at all times, particularly KYC (Know Your Customer), AML (Anti Money Laundering), FATCA (Foreign Account Tax Compliance Act), EMIR and MiFID II. We need to agree with the client whether it is execution only, advisory or discretionary mandate; from which office we want to serve them (EU, non-EU, US), the asset allocation, portfolio strategy, portfolio limits and policy, fees structure, tax & payment withholding, before eventually we can receive their money to be invested.
  4. Investment management: once we receive client’s money, the investment process begins. The fund managers (FM) will start creating orders to purchase securities in the order management system (OMS) such as SimCorp, Charles River or ThinkFolio. The orders/trades will then be aggregated, executed, settled, and allocated. The fund managers use a portfolio management system (PMS) to construct the portfolio, manage asset allocation, monitor exposure and holdings, and manage risks, as per the investment mandates. They also use the PMS to run what-if scenarios and assess the profit & loss based on daily market prices, and create a composite portfolio with a blended benchmark. The Performance Team uses a performance and attribution system (PAS) to understand the performance of the fund against the benchmark (for each share class), attribution of performance for each factor e.g. security selection, asset allocation, duration, curve, and currency.
  5. Risk management is about understanding the sensitivities of the portfolio to interest rate changes (duration, DV01, Greeks), understanding exposure to different sectors and currencies, understanding ex-ante risks such as VaR, tracking error and volatility; hedging credit exposure, and other risk measures such as OAS, fair value, spread, and liquidity risk. These processes are usually performed a risk team which will inform the FMs of any risk found above tolerance. The risk team will also conduct stress testing by running particular scenarios against the portfolio: FX rate changes, interest rate shifts, credit spread changes, or against certain historical events such as 9/11 or an incoming event such as Brexit. It is also about managing counterparty risk and credit risk using collateral based on MtM, particularly for OTC derivatives.
  6. Client reporting is about updating clients how their portfolios are performing (against the benchmark and attribution), the asset allocation (by asset class, rating, sector, duration and maturity), and the risk profile (sensitivities to interest rates, inflation, credit risk, and other factors). The reports also contain updates on market situations, market analysis from the fund managers (and how they position the portfolio to take advantage of it), and transactions during the period (what securities were purchased/sold and why. This is usually performed not by the FMs, but by a separate client reporting team. If it is a public fund, the asset manager needs to produce KIIDs (soon to be KID) and fact sheets which contain the fund objective, investment policy, fund performance, asset allocation breakdown, top holdings, fund details, fees & changes, risk profile, and fund manager commentary.
  7. Compliance processes are about ensuring that all portfolios that the asset manager has are as per the investment objectives and restrictions. For example, if a portfolio must hedge at least 80% to Sterling, every day the compliance process will test this boundary. Other examples of the portfolio objectives and restrictions tested by compliance process are: no equity, IG fixed income only (no HY), emerging market only, no derivatives, no MBS, gilt only (no corporates), equity only (no fixed income), duration > 5y. It is important to test these boundaries every day because a) it could be regulatory (e.g. a GBP fixed income fund is not allowed to invest in equity or in other currency), b) breaking the mandate (e.g. the mandate from a pension fund which have a strict rule not to invest in derivative, because it is too risky), and c) client’s trust (e.g. if a fund manager doesn’t stick to the rules, existing clients will switch and potential clients will choose other asset managers and the AUM will dry up).

Systems Required

What systems are required to support the above 7 business processes?

  1. Investment Management System (IMS): this is a combination of order management system (aka trade management system), portfolio management system, risk management system, compliance system. Order management is a must have feature, i.e. ability to place trades with brokers and bank (Omgeo CTM and Swift messages) from origination, execution, validation, confirmation, clearing and settlement. Every good IMS must check compliance / portfolio limits before a trade is submitted, for example if High Yield is only allowed 20% and a trade will make the portfolio having 21% HY, the IMS will prevent the trade to be submitted. Examples of IMS: ThinkFolio, Charles River, SimCorp, EBIMS, IMSplus, e-AMIS, Vestio, Nirvana, FC Portfolio, VestServe.
  2. Performance Management System (PMS): it takes the daily prices of each security and daily benchmark values, calculate the NAV for each share class (value of the portfolio minus liabilities), and provide the official unit price for each share class, based on which the 1m, 3m, 6m, 1y, 3y, 5y, and SI performance is calculated. A very important feature of a PMS is performance attribution, which is the ability to determine how much each factor contributes to the performance. For example, out of 12% performance in the last 1y, 3% of it is because of security selection, 4% because of currency, and 5% because of duration. A good PMS provides GIPS compliant numbers. Examples of PMS: Barclays Point (now Bloomberg), Eagle (aka Pace), StatPro, VestServe.
  3. Risk Management System (RMS): the risk manager module within IMS usually only provides the analytics (e.g. duration, spread, yield, convexity) but it does not calculate value at risk, volatility, beta, alpha, Sharpe ratio, and tracking error because they are too complex. An RMS calculates them, as well as doing stress testing, counterparty risk and liquidity risk. A good RMS can calculate the contribution of each factor to the portfolio VaR, volatility and Beta (e.g. out of 300 bps VaR, 90 is because of government bonds, 110 because of corporate sector selection, 80 is because of curve, and 50 because of currency), or even down to the individual security, as well as calculating Component VaR, Marginal VaR and Incremental VaR. Examples of RMS: Risk Metrics, IBM Algo Risk, Risk Value, Kraytis.
  4. Client Relationship Management System (CRM): not only CRM supports the client on-boarding process (including contract/agreement, mandates, investment guidelines, AUM, KYC, AML, and detailed customer information), but also managing prospects & opportunities, deal conversion, sales targets, client meetings, reporting requests, mandate changes and revenue tracking. A good CRM system can support fundraising, product road shows, managing reporting and compliance on client-by-client basis, mass mailing campaigns, statement generation, pipeline management, client portal, and mobile access. Examples of CRM systems: SalesForce, Maximizer, Satuit, Dynamics, WDX, Communica, ProTrak, Dynamo. Mark Margolis wrote a good article on CRM for asset management.
  5. Client Reporting System (CRS): clients need to know how their investments are doing. This includes performance, attribution, risk profile, asset breakdown, and transactions. Client also need specific reports such as regulatory (e.g. Solvency II for insurance companies), duration and maturity breakdown (for fixed income houses), inflation sensitivities (for pension funds), counterparty risk (for banking clients), currency hedging, etc. We can satisfy these requests individually (fully tailored solution for each client), or we can satisfy them en-mass (create one solution that can be used by clients because it can do everything). Example of CRS are Kurtosys, Vermillion, Pulse, Comarch, and SS&C.
  6. Compliance system: checks all portfolios and positions every day for compliance breach and mandate/guideline breach.
  7. Fund administration: maintain a register of all shareholders (for investment trust) and investors (for unit trust/SICAV/OEIC), record all subscriptions and redemptions, calculate AUM for every fund share class every day taking into account all redemptions and subscriptions. An example of a fund administration system is IBS PAMS.
  8. Fund accounting: calculates the NAV for every portfolio/funds every day, based on EOD market prices, taking into account all liabilities.
  9. Collateral management: calculate mark to market valuations for every OTC derivatives (IRS, CDS, repo, etc.) and calculate the value of every single collateral with every single counterparty and determine how much over/under our counterparty position, and manage it.
  10. OTC pricing: calculate the prices of every OTC derivative contract that we have including swaps (IRS, inflation swaps, currency swaps, CDS) and options. Also calculates the derivative of price (the Greeks) e.g. Gamma, Vega, Theta.


16 March 2016

Different Measures for Different Product Types

Filed under: Business Knowledge,Data Architecture,Data Warehousing — Vincent Rainardi @ 8:33 am

What I mean by a measure here is a time-variant, numerical property of an entity. It is best to explain this by example. In the investment industry, we have different asset classes: equities, bonds, funds, ETFs, etc. Each asset class has different measures. Equities have opening and closing prices, daily volume, market capitalisation, daily high and low prices, as well as annual and quarterly measures such as turnover, pretax profit and EPS. Bonds have different daily measures: clean and dirty prices, accrued interest, yield and duration. Funds have different daily measures: NAV, alpha, sharpe ratio, and volatility, as well as monthly measures such as 3M return, 1Y return, historic yield, fund size and number of holdings. ETFs have daily bid, mid and offer prices, year high and low, and volume; as well as monthly measures such as performance. The question is: what is an appropriate data model for this situation?

We have three choices:

  1. Put all measures from different product types into a single table.
  2. Separate measures from each product types into different tables.
  3. Put the common measures into one table, and put the uncommon measures into separate tables.

My preference is approach 2, because we don’t need to join across table for each product type. Yes we will need to union across different tables to sum up across product types, but union is much more performant than join operation. The main weakness of approach a is column sparsity.

On top of this of course we will need to separate the daily measures and monthly measures into two different tables. Annual and quarterly measures for equities (such as financial statement numbers) can be combined into one table. We need to remember that measures with different time granularity usually are from different groups. For example, the prices are daily but the performance are monthly.

Static Properties

In addition to different time-variant properties (usually numerical), each asset class also different static properties (can be textual, date or numeric). For example, equities have listed exchanges, industry sectors, country of domicile, and dividend dates. Bonds have issuers, call and maturity dates, and credit ratings. Funds have benchmark, trustee, legal structure and inception date. Examples of numerical properties are minimum initial investment and annual charges for funds; outstanding shares and denomination for equities; par and coupon for bonds. Some static properties are common across asset classes, such as ISIN, country of risk, currency.

Static properties from different asset classes are best stored in separate tables. So we have equity table, bond table, fund table and ETF table. Common properties such as ISIN, country of risk, etc. are best stored in a common table (usually named security table or instrument table).

Why not store all static properties in a common table? Because the properties are different for each asset class so it is like forcing a square peg into a round hole.

Historical Data

For time variant properties it is clear that the table already stores historical data in the rows. Different dates are stored as different rows. What we are discussing here is the historical data of the static attributes. Here we have two choices:

  1. Using SCD approach: store the historical values on different rows (called versions), and each row is only valid for certain time period. SCD stands for slowly changing dimension, a Kimball approach in data warehousing.
  2. Using Audit Table approach: store the historical rows in an audit table (also called history table). This is the traditional approach in normalised modelling. The main advantage is that the main table is light weight and performant.

When to use them? Approach a is suitable for situations where the historical versions are accessed a lot, whereas approach b is suitable for situations where the historical versions are very rarely accessed.

The main issue with approach a is that we need to use “between” on the validity date columns. In data warehousing we have a surrogate key to resolve this issue, but in normalised modelling we don’t. Well, we could and we should. Regardless we are using appraoch a or b, in the time-variant tables we need to store the ID of the historical row for that date. This will make getting historical data a lot faster.

26 February 2016

Investment Performance

Filed under: Business Knowledge — Vincent Rainardi @ 5:24 am

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.

24 February 2016

Instrument Dimension

Filed under: Business Knowledge,Data Warehousing — Vincent Rainardi @ 6:15 pm

One of the core dimensions in investment banking is instrument dimension. It is also known as security dimension. It contains various different types of financial instruments or financial securities, such as bonds, options, futures, swaps, equities, loans, deposits, and forwards.

The term “securities” used to mean only bonds, stocks and treasuries. But today it means any tradable financial instruments including derivatives, cash, loans, and currencies. Well, tradable and “contract-able”.

Where it is used

In an data warehouse for an investment bank, a brokerage or an investment company, an instrument dimension is used in three primary places: in trade fact tables, in position fact tables and in P&L fact tables. Trade fact tables store all the transactions made by the bank, either for a client (aka flow trading) or for the prop desk (bank’s own money), in all their lifecycle stages from initiation, execution, confirmation, clearing, and settlement. Position fact tables store daily values of all instruments that the bank holds (long) or owes (short). P&L (profit and loss) fact tables store the daily impact of all trades and positions to each of the bank’s financial accounts, e.g. IAS 39.

The secondary usages in an asset management or an investment banking data warehouse are risk fact tables (e.g. credit risk, market risk, counterparty risk), compliance fact tables, regulatory reporting, mark-to-market accounting, pricing, and liquidity fact tables.

The niche usages are ESG-score fact tables (aka SRI, socially responsible investing), rating transition fact tables, benchmark constituent fact tables, netting position fact tables, and collateral fact tables.

Data Structure

The business key of an instrument dimension is usually the bank-wide internal instrument identifier. Every instrument that the bank gets from market data providers such as Bloomberg, Reuters, Markit, index constituents, and internal OTC deals, are mastered in a waterfall process. For example, public instruments (debts, equities, ETDs) are identified using the following external instrument identifiers, in order: ISIN, Bloomberg ID (BBGID), Reuters ID (RIC), SEDOL, CUSIP, Exchange Ticker, Markit ID (RED, CLIP), Moody’s ID (Master Issue ID). Then the internal identifiers for OTCs (e.g. CDS, IRS, FX Swaps), FX Forwards, and cash are added.

The attributes of an instrument dimension can be categorised into 9:

  1. Asset Class
  2. Currency
  3. Country
  4. Sector
  5. Issuer
  6. Rating
  7. Maturity
  8. Instrument Identifier
  9. Asset class specific attributes

1. Asset Class

Asset Class is a classification of financial instruments based on its functions and characteristics, e.g. fixed income, equities, cash, commodity. We also have real assets such as land, buildings, physical gold and oil.

It also covers the hierarchy / groupings of the asset classes, hence we have attributes such as: asset class, asset sub class, asset base class. Or alternatively asset class level 1, level 2, level 3. Or asset class, asset type, asset group.

Good starting points for asset class categorisation are ISDA product taxonomy, Barclays index guides, Academlib option pages and Wikipedia’s derivative page. Here is a list of popular asset classes:

FIXED INCOME: Government bond: sovereign, supranational, municipal/regional, index linked, zero coupon, emerging market sovereign, sukuk sovereign. Corporate bond: investment grade, high yield, floating rate note, convertible (including cocos), covered bond, emerging market corporate, sukuk corporate. Bond future: single name bond future, future on bond index. Bond option: single name bond option, option on bond index. Bond forward: single name bond forward, forward on bond index. Credit default swap: single name CDS, CDS index, CDS swaption, structured CDS. Asset backed security (ABS): mortgage backed security (including RMBS and CMBS), ABS (auto, credit card, etc), collateralised debt obligation (CDO), ABS index. Total Return Swap: single name TRS, TRS index. Repurchase agreement: repo, reverse repo.

EQUITY: Cash equity: common shares, preferred shares, warrant, equity index. Equity derivative: equity option (on single name and equity index), equity future (on single name, equity index, and equity basket), equity forward (on single name, equity index, and equity basket), equity swap (on single name and equity index).

CURRENCY: Cash currency: FX spot, FX forward. Currency derivative: cross currency swap.

RATES: Interest rate: interest rate swap, overnight index swap (OIS), interest rate cap, interest rate future, interest rate swaption, forward rate agreement (FRA), asset swap. Inflation rate: inflation swap, inflation swaption, inflation cap, zero-strike floors, inflation protected annuity.

COMMODITY: commodity future: energy (oil, gas, coal, electricity, wind turbine), base metal (copper, iron, aluminium, lead, zinc), precious metal (gold, silver, platinum, palladium), agriculture (grains: corn, wheat, oats, cocoa, soybeans, coffee; softs: cotton, sugar, butter, milk, orange juice; livestock: hogs, cattle, pork bellies). Commodity index (energy, metal, agriculture). Option on commodity future. Commodity forward.

REAL ASSET: Property: Agricultural land, residential property, commercial property. Art: paintings, antique art. Collectibles: fine wine, rare coins, antique cars, jewellery (including watches and precious stones).

FUND: money market fund, equity fund, bond fund, property fund, commodity fund, currency fund, infrastructure fund, multi asset fund, absolute return fund, exchange traded fund.

OTHER: Private equity. Venture capital.

Note on differences between asset class and asset type: asset class is usually a categorisation based on market, i.e. fixed income, equity, cash, commodity and property; whereas asset type is usually a categorisation based on time and structure, i.e. spot, forward, future, swap, repo, ETD, OTC, etc.

Note on overlapping coverage: when constructing asset class structure, we need to be careful not to make the asset classes overlapping with each other. If we do have an occurrence where an instrument can be put into two asset classes, make sure we have a convention of where to put the instrument. For example, an IRS which is in different currencies are called CCS (Cross Currency Swap). So either we don’t have CCS asset class and assigned everything to IRS (this seems to be the more popular convention), or we do have CCS and make sure that none of the swaps with different currencies are in IRS.

2. Currency

For single-legged “hard” instruments such as bonds and equities, the currency is straightforward. For multi-legged, multi-currency instruments such as FX forward and cross currency swap, we have two currencies for each instrument. In this case, we either have a column called “currency pair”, value = “GBP/USD”, or two column marked as “buy currency” and “sell currency”.

For cash instruments, the currency is the currency of the cash. For “cash like” or “cash equivalent” instruments such as CP, CoD, T-bill, the currency is straightforward, inherent in the instrument. For multi-currency CDS Index such as this (i.e. a basket of CDSes with different currencies), look at the contractual currency of the index (in which the premium leg and protection leg are settled), not the liquid currency (the currency of the most liquidly traded CDS).

For derivatives of equities or fixed income, the currency is taken from the currency of the underlying instrument.

3. Country

Unlike currency which is a true property of the instrument, country is a property of the issuer. There can be three different countries in the instrument dimension, particularly for equities, i.e. country of incorporation, country of risk (aka country of operation, country of domicile), country of listing.

Country of risk is the country where if there is a significant business changes, political changes or regulatory changes in that country, it will significantly changes the operation of the company which issues this security. This is the most popular one particularly for portfolio management, and trade lifecycle. It common for a company to operate in more than one country, in this case it is the main country (from revenue/income point of view), or set to “Multi-countries”.

Country of incorporation is the country where the issuer is incorporated, not the where the holding company (or the “group”) is incorporated. This is used for regulatory reporting, for example FATCA and FCA reporting.

Country of listing depend on the stock market where the equity instrument is listed. So there can be two different rows for the same instrument, because it is listed two different stock exchanges.

The country of risk of cash is determined by the currency. In the case of Euro instruments (not Eurobond*) it is usually set to Germany, or Euroland (not EU). *Eurobond has a different meaning, it is a bond issued not in the currency of the country where it is issued, i.e. Indonesia govt bond issued in USD.

An FX forward which has 2 different currencies has one country of risk, based on the fixed leg (not the floating leg) because that is where the risk is. The country of risk for cross currency swap is also based on the fixed leg. For floating-for-floating CCS, the convention is usually to set the country of risk to the least major currency, e.g. for USD/BRL, USD is more major than BRL, so Brazil is the country of risk. For non-deliverable forward and CCS (meaning the payment is settled in other currency because ND currency can’t be delivered offshore), the country of risk is set based on settlement currency (usually USD).

Like currency, the country of a derivative of equities or fixed income instrument is taken from the country of the underlying instrument.

4. Sector

These attributes are known with many names: sector, industrial sector, industry sector, or industry. I will use the term sector here.

There can be many sector attributes in the instrument dimension, e.g. Barclays level 1/2/3, MSCI GICS (and S&P’s), UK SIC, International SIC, FTSE ICB, Moody’s sector classification, Factset’s sector classification, Iboxx, etc. They have different coverage. Some are more geared up towards equities, some more towards fixed income.

The cash instruments and currency instruments usually have either no sector (blank), or set to “cash”. Rates instruments, commodity futures and real asset usually have no sector.

The sector of fixed income derivatives, such as options and CDSes are determined based on the sector of the underlying instrument. Ditto equity derivatives.

5. Issuer

All equity and fixed income instruments have issuers. This data is usually taken from Bloomberg, or from the index provider if the position is an index constituent.

All corporate issuers have parents. This data is called Legal Entity data, which can be obtained from Bloomberg, Thomson Reuters, Avox/FT, etc. From the Legal Entity structure (parent-child relationship between company, or ownership/subsidiary to be more precise) we can find the parent issuer, i.e. the parent company of the issuer, and the ultimate parent, i.e. the parent of the parent of the parent (… until the top) of issuer.

Legal entity data is not only used in instrument dimension. The main use LE data within an investment bank is for credit risk and KYC (know your customer), i.e. customer due dilligence. PS. LEI means Legal Entity Identifier, i.e. BBG Company ID, FATCA GIIN (Global Intermediary Identifier Number), LSE’s IEI. But LEI also means ROC’s Global LEI – the Regulatory Oversight Committee.

6. Rating

Like sector, there are many ratings. Yes there are only 3 rating providers (S&P, Moody’s, and Fitch), but combined with in-house rating, there can be 15 different permutations of them, i.e. the highest of SMF, the lowest of SMF, the second highest of SMF, the average of SMF, the highest of SM, the lowest of SM, the average of SM, the highest of SMFH, the lowest of SMFH, etc. With M = Moody’s and H = House rating.

Plus we have Rating Watch/Outlook from the 3 provider. Plus, for CDS, we can have “implied rating” from the spread (based on Markit CDS prices data).

7. Maturity

Almost all fixed income instruments have maturity date. Maturity is how far is that maturity date from today, stated in years rather than days. We also have effective maturity, which is the distance in time between today and the nearest call date, also in years.

8. Instrument Identifier

This is the security identifier as explained earlier, i.e. ISIN, Bloomberg ID, Ticker, Markit ID, Sedol, CUSIP, Reuters ID, Moody’s ID.

9. Asset Class Specific Attributes

Each asset classes have their own specific attributes.

For CDS we have payment frequency (quarterly or bi-annually), standard coupon payment dates (Y/N), curve recovery (Y/N), recovery rate (e.g. 40%), spread type (e.g. conventional), restructuring clause (Old R, Mod R, Mod-Mod R, No R), fixed coupon convention (100 or 500), succession event, auction settlement term, settlement type (cash/physical), trade compression, standard accruals (Y/N), contract type (e.g. ISDA).

For IRS we have amortising swap flag, day count convention, following convention (Y/N), no adjustment flag (Y/N), cross currency flag (Y/N), buy currency, sell currency, mark-to-market flag, non-deliverable flag, settlement currency.

Debt instruments such as bonds have these specific attributes: coupon type (e.g. fixed, floating), seniority (e.g. senior, subordinated), amortising notional, zero coupon. Funds also have their own specific attributes, such as emerging market flag, launch date, accumulation/income, base currency, trustee, fund type, etc.


The granularity of an instrument dimension can be a) one row for each instrument (this is the norm), or b) one row for each leg. This is to deal with multi-leg instruments such as CDS (3 legs) and cross currency swap (2 leg). The asset class of each leg is different.

If we opt for one row for each instrument, the asset class for each leg needs to be put in the position fact table (or transaction fact table, compliance fact table, risk fact table, etc).


There are millions of financial instruments in the market and through-out the life of an investment bank there can be millions of OTCs created in its transactions. For investment companies, there are a lot of instruments which they had holdings in the past, but not any more. Coverage of an instrument dimension means: which instruments are we going to maintain in this dimension? Is it a) everything in the market, plus all OTCs, b) only the one we ever used, c) only the one we hold in the last N years.

We can set the coverage of the instrument dimensions to cover all bonds and equities which ever existed since 1900, but this seems to be a silly idea (because of the cost, and because they are not used), unless we plan conduct specific research, e.g. analyse the quality changes over a long period. The most popular convention is to store only what we ever used.


Most of instrument dimensions are in type 2, but which attributes are type 2 are different from project to project, bank to bank (and non-bank). Most of the sector, rating, country attributes are type 2. Maturity date is type 2 but maturity (which is in years) are either type 1 or kicked out of this dimension into a fact table (this is the more popular option). Next coupon date and effective date are usually type 1.

Numerical attribute

Numerical attributes such as coupon, rating factor, effective maturity, etc. are treated depending on their change frequency. If they change almost every day, they must be kicked out of the instrument dimension, into a fact table. For example: effective maturity, maturity, holding positions.

If they change a few times a year, or almost never change (static attribute), they stay in this instrument dimension, mostly as type 2. An example of a static numerical attribute is coupon amount (e.g. 6.25%). An example of a numerical attribute is rating factor (e.g. 1 for AAA and 3 for A), and associated PD (probability of default, in bps, e.g. such as 0.033 for AAA, 0.54 for AA, and 10 for BBB), which on average changes once every 1 to 3 years.

The difference between a numerical attribute and a fact is that a numerical attribute is a property of the instrument, whereas a fact is a measurement result.

System Columns

The usual system columns in the instrument dimension are: created datetime, last updated datetime, and the SCD system columns e.g. active flag, effective date, expiry date.

27 September 2015

Credit Risk and Market Risk

Filed under: Business Knowledge — Vincent Rainardi @ 5:59 am

Broadly speaking when we talk about risk in investment banking IT, it’s about 2 things: Credit Risk and Market Risk. Other financial risks are liquidity risks, operational risks, legal risks, but they don’t usually require a large IT systems to manage them.

Credit Risk

As a bank, credit is about lending money to companies to get interest. The companies are called obligors or counterparty. These obligors have obligation to pay us certain amount at certain times. The risk here is if those companies cannot pay us the amount they need to pay, when it’s due. This is called a default.

Credit risk is about 2 things: a) to manage the credit portfolio, b) to manage credit transactions. For a), the goal is to maximise the risk-adjusted return by maintaining the credit exposure of the whole portfolio within certain parameters. This is done using economic capital, correlation and hedging. These subjects are explained in these articles:

Economic Capital:


Things that a credit risk business analyst (BA) is expected to understand are: counterparty risk, CVA, Basel II & III, credit portfolio management, PD, LGD, EAD, Expected Loss, VAR, KMV, PFE, volatility, Economic Capital, RWA, Monte Carlo, correlation, ratings, credit derivative.

A credit risk data warehouse has the following functionalities:

  • Calculate Value At Risk and volatility of the credit portfolio every single day.
  • Produce regulatory reports such as Risk Weighted Assets, capital requirements, stress tests, and Potential Future Exposure.
  • Calculate portfolio risk measures such as Exposure At Default, Expected Positive Exposure, Credit Valuation Adjustment, counterparty risk.

Market Risk

Banks, insurance companies, pension funds and hedge funds all invest their cash in various things: shares, bonds, derivative, commodity, property, or in other companies. You intend to keep them for years. This is called investment portfolio, e.g. if you have £1 million to invest, you put 20% in bond, 50% in shares, etc.

Sometimes you don’t keep it for a long time. But only a few days, or even a few hours. This is called trading portfolio. Shares, FX, commodity, derivative, etc.

The value of your portfolio (be it investment or trading) can go up or down depending on 4 factors: the share prices, FX rates, interest rate and commodity prices. These 4 factors is called market risk, because the prices of these 4 factors are determined by the market (the buyer and the seller).

23 September 2015

Investment Banking

Filed under: Business Knowledge — Vincent Rainardi @ 7:45 am

Traditionally, the core business of an investment bank (IB) was to help companies raise funds in the capital market  and doing merger & acquisition. In addition to these 2 core services, IBs also offer these services to clients: research, fund management, trading, market making and wealth management. IBs also do trading for themselves (using their own money, called prop desk).

Let’s take a look at these services one by one. But before that, let’s quickly describe sell side and buy sides, and private and public sides.

In the investment banking world there are 2 sides: the sell side and the buy side. The sell side (link) are companies that sell investment services, for example: an IB which does broking/dealing, raise funds in capital market, M&A/advisory, and research. Buy side (link) are companies that buy investment services, for example: private equity funds, mutual funds, life insurance company, hedge funds, and pension funds.

Within an investment bank, we have 2 sides: private side and public side. Private side is the part of the bank which have access to inside company information (i.e. their clients) which are not available to the public. For example: M&A division and capital market division (Debt Capital Market/DCM and Equity Capital Market/ECM). Public side is the part of the bank which only have access to public information. For example: trading and research. Between these 2 side we have a “chinese wall”, which separate the 2 parts of an investement bank. The 2 parts must not (by law) exchange information. Chinese wall is a fundamental principle that has to be considered very seriously when designing IT systems for an investment bank.

Now let’s take a look at the services of investment banking:

  1. Raising capital is basically issuing bonds or equity (IPO or secondary offering). The bank acts as underwriter, meaning that the bank (usually a syndicate) buys all the bonds or shares from the company, then sells it to the market with spread (for stock) or fee (for bond). This require a lot of corporate finance work.
  2. M&A is the original meaning of “investment banking”, i.e. to find the client a buyer, or to find the client a company to buy (takeover, acquisition). Or, to have an idea that if company A & B are merged there would be advantage for both companies, such as synergy, vertical integration, increased market share or economy of scale, then try to sell the merger idea to both companies.
    There is also spin off or de-merger, where some part of a company is detached (created as a new company), and then sold off to another company. M&A also involves a lot of corporate finance work. M&A is also called “advisory”.
  3. Research covers equity research, fixed income research, macro economic research, technical analysis, quantitative analysis. In addition to individual companies, equity research provides industry trends, market trends, sector weightings and geographical preferences. Technical analysis (link) studies the historical price to predict the future direction in a particular market or a single-name issue. Research also provides tools which enables clients to access forecasts and evaluate capital structure, and to search for a specific company/sector/year/asset class.
  4. Fund management manages clients’ money in mutual funds (open ended) or investment trusts (close ended). Covering many sectors, i.e. by asset class (equity, bond, cash, commodity), by geography (UK, US, European, Global), by type (growth, income, recovery, absolute return).
  5. Trading buys and sells securities in the capital markets, on behalf of the clients. Covers various asset classes including equity, credit, FX, commodity, securitized, prime, multi asset and tailored. Tailored brokerage offers tailored off-market transaction such as distressed situations, sale-and-leaseback and company expansions (link). Prime brokerage (link) offers services for clients to borrow securities and trade/invest on netted basis and leveraged basis (link).
  6. Market making: provide liquidity in the market by quoting both buy and sell price (simultaneously) in a share or a bond or a commodity, usually narrower than the market spread (link), hoping to make money from the bid-offer spread (link).
  7. Wealth Management: provide advisory on financials and investments to high net-worth individuals/families, as well as the work/execution. This includes retail banking, estate planning, will, tax and investment management (link). Aka private banking, which is misleading because wealth management is not only banking but also legal, tax, and investing services.

Some of the top investment banks are (link): Goldman Sachs, Morgan Stanley, JP Morgan, Bank of America Merrill Lynch, Deutsche Bank, Citigroup, Credit Suisse, Barclay, UBS, HSBC, Nomura, RBC, BNP Paribas, RBS.

19 September 2015

Investment Banking Books for BAs and Developers

Filed under: Business Knowledge — Vincent Rainardi @ 7:04 pm

A friend recently asked me to recommend books in investment banking and this was the list I came up with. The intended audience of these books are people who don’t have investment banking background or work experience, but have experience in database development or data architecture. So it is more of “I’m a BA, developer or architect with retail / healthcare / manufacturing experience and want to get into investment banking or asset management* (as a BA/developer/architect, not as a trader or financial analyst!), what books should I read?” So it’s kind of “I want to learn the business processes from IT point of view”.

There are two meanings of the words “investment banking”. Traditionally it means Merger, Acquisition and LBO (Leveraged Buyout). It is about analysing financial statements, valuation methods, and M&A modelling. These are skills and knowledge required to do the traditional business of an investment bank, which is to help clients raising capital by issuing securities as well as advising clients on M&A (link). I went to an investment banking course with IBI in 2011 and learned these traditional functions to my surprise. The second meaning is: an investment bank is a bank who trades financial securities, or acting as an intermediary in the trade as brokerage or market maker, as well as providing analysis, research and ratings (aka the “sell side” of Wall Street). The buy side of Wall Street are investment companies such as asset manager, who buy securities for investment purpose and fund management (see below).

Below I’m suggesting one book for each area of investment banking (both meanings above), as well as the buy side.

  • Introduction: Investment Banking Explained: An Insider’s Guide to the Industry, by Michel Fleuriet, link
  • Trading: The Trade Lifecycle: Behind the Scenes of the Trading Process by Robert P. Baker, link.
  • Equity: Investments: Principles of Portfolio and Equity Analysis (CFA Institute Investment Series) by Michael McMillan and Jerald Pinto, link.
  • Fixed Income: Fixed Income Analysis by Frank J. Fabozzy, link.
  • Derivatives: Derivatives Demystified: A Step-by-Step Guide to Forwards, Futures, Swaps and Options by AM Chisholm, link.
  • Merger & Acquisition: Investment Banking: Valuation, Leveraged Buyouts, and Mergers and Acquisition, by Joshua Pearl & Joshua Rosenbaum, link.
  • Asset Management*: A Guide to Fund Management by Daniel Broby, link.
  • Risk: Risk Management and Financial Institution by JC Hull, link.

*Asset Management or Investment Management is an industry sector containing investment companies, which manage pooled funds or segragated mandates from clients, and invest in stock market, bond market, currencies,  properties, cash or in other investments such as commodities, derivatives, etc. These investment companies are called Asset Managers or Fund Manager (link), e.g. Schroders, Invesco Perpetual, Fidelity, Blackrock. Investment banks like JP Morgan, Credit Suisse, HSBC, and UBS also have asset management division (link, link).

There are departments in Investment Banking which are not listed above, i.e. Finance, Compliance, Treasury, ALM. But these departments exist in all 3 types of banking (retail banking, corporate banking and investment banking), not just investment banking. So below I list the books in banking, not just investment banking, including those departments above. But again this is for IT developers (not system analyst!) or BAs or architects who wants to get banking knowledge or a job in banking.

  • Introduction: FT Guide to Banking by Glen Arnold, link.
  • Retail Banking: Retail Banking by Dr Ramamurthy Natarajan, link.
  • Corporate Banking: Corporate Banking: A guide book for novice by Dr Ramamurthy Natarajan, link.
  • Central Banking: Central Banking: Theory and Practice in Sustaining Monetary and Financial Stability by Thammarak Moenjak, link.
  • Treasury: Treasury Operations Handbook by Philip JL Parker, link.
  • Compliance: Financial Regulation and Compliance: How to Manage Competing and Overlapping Regulatory Oversight, by H David Kotz, link.
  • Finance: Accounting and Finance: An Introduction by Dr Peter Atrill & Eddie McLaney, link.
  • ALM: Bank Asset and Liability Managment: Strategy Trading Analysis by Irving Henry & Moorad Choudhry, link.

My banking experience: I was lucky to have a bit of investment banking and asset management experience since 2011 to date working at RBS (credit risk and credit portfolio management), Barclays (fixed income, CDS), UBS (finance, reporting), Bluebay (asset management, fixed income, risk), Insight Investment (asset management, LDI and fixed income). I had my retail and corporate banking experience when I was working for Andersen Consulting (now Accenture) in Jakarta, Indonesia, where we did a system project for Bank Exim (corporate banking), a BPR project also at Bank Exim (retail banking) and a Merger & Acquisition project at Bank Mandiri, the largest bank in Indonesia. I was fortunate that my father was a bank manager, worked for 3 banks in his career (Bank Ekonomi Indonesia, Bank Karman, Bank Umum National), from whom I got my banking passion and inspiration.

Magazines and news websites in Investment Management sector are listed below. This list is UK focus.

  • Investment Week, link
  • Global Investor, link
  • Institutional Investor, link
  • Portfolio Advisor, link
  • The Hedge Fund Journal, link
  • Risk.Net, link
  • What Investment, link
  • Institute of Asset Management, link
  • Fund Web, link
  • Financial Advisor, link

Magazines and news websites in Investment Banking (trading, FX, credit, equity, derivatives, M&A) are listed below. It is by no means comprehensive as I only spent like five minutes on it, with the intention to enhance the list over time, i.e. removing the one which are not so useful, and adding new ones.

6 July 2015

Securitising Cash Positions

Filed under: Business Knowledge — Vincent Rainardi @ 7:17 am

It is an old age issue in asset management industry, that not all positions consist of security. They are cash positions or cash-like positions, e.g. settled and unsettled cash balances, FX forward/swap positions, IRS positions, and transaction proceeds. One of the solutions is to securitise these positions into instruments.

So we’ll have instruments called USD Settled Cash, EUR Unsettled Cash, Buy USD Sell GBP 01/04/15, and Pay 3% GBP Receive LIBOR3m+1% JPY. And here is where the issue lies, outlined in the next 3 paragraphs. When does the cash become settled? It depends on when the settlement message from the broker is processed. Do we create a separate instrument for Repo cash? (Repurchase Agreement). Do we create a separate instrument for collateral cash? (margin requirements).

FX forward has 2 legs. In the above example, the Buy USD date is today/spot (say 26th March 2015) and the Sell GBP date is future/forward (1st April 2015). Do we create 2 instruments, one for each leg, or 1 instrument?

IRS (Interest Rate Swap) can be float-for-fix or float-for-float. It can be the same currency or different currency. To close out the exposure (but not the accounting values), an IRS usually have a contra. If in the IRS we pay fix, in the contra we pay float. So how do we securitise this? Do we create the contra as a separate instrument? An IRS has 2 legs (usually a fix leg and a float leg, but could be both float) – do we create separate instruments for each legs? Do we create a separate instrument for each of the rates? Do we create a separate instrument for each of the fixing dates?

Attributes of cash-like instruments

What is the country of risk of “USD Settled Cash” instrument? United States of course. What is its country of domicile? N/A. What is its currency? It’s obvious, USD. What’s the issuer? Also obvious, US Government.

Now try for Pay 3% GBP Receive LIBOR3m+1% JPY. What is the country of risk? Umm… UK? Japan? Well, the risk is on the fixed leg, so the country of risk is UK. What is the issuer? Hmm…. blank? What is the currency?

The most common attributes of an instrument are: country, currency, issuer, asset class/type, asset subclass/subtype, sector/industry/subsector, rating, (effective) maturity date, maturity bucket, coupon frequency. All these need to be populated. Oh and ID fields, e.g. Ticker, Sedol, ISIN, Cusip; most of which will be blank for cash or FX lines. Description however, is crucial. It is used for looking up to determine if that instrument already exists or not. So it is very important to have a consistency, e.g. “USD Settled Cash” or “USD Cash Balance”? “EUR Unsettled Cash” or “EUR Cash (Unsettled)”? “GBP Collateral Cash” or “Collateral Cash GBP”?

Analytics for cash-like instruments

Analytics are measures which are dependent on price, time to maturity, and interest rates. The most common analytics for fixed income are yield to maturity, current yield, modified duration, spread, spread duration, option adjusted spread, z-spread, convexity, gamma. Some of these will be zero for cash, but some of them have values (like yield for example).

These analytics will need to be calculated if we combine several positions into one instrument. Some of them are not simple additive, e.g. they need to be weighted with % contribution when summing up. Some of them doesn’t work with weighted sum.

The other solution: by not securitising them

The other option is not securitising cash and FX positions, and they become positions without instruments. If we take this route will need to store all security attributes in the holding table.

Next Page »

Blog at