Data Warehousing and Business Intelligence

16 March 2016

Different Measures for Different Product Types

Filed under: Data Architecture,Data Warehousing,Investment Banking — 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.

24 February 2016

Instrument Dimension

Filed under: Data Warehousing,Investment Banking — 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,Investment Banking — 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,Investment Banking — 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,Investment Banking — 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.

15 February 2014


Filed under: Data Warehousing,Investment Banking — Vincent Rainardi @ 5:23 pm

This little word often puzzled many people. What is it? Is it de-duplication? Removing the duplicates? What duplicates? In which table, dimension or fact? Is it only used in data warehousing? I came into this word / situation again yesterday and as this is the 4th time I came across it I thought I’d write it down. Probably useful for other people, while I still got the chance.

Dedup is the short for de-duplication. In data warehousing, in the source tables we sometimes found a table without a primary key. Or a unique index for that matter. This means that this table has a potential to have duplicate rows. Meaning that two or more rows with the same value across all columns. Or at least across the columns which we are interested in.

For example, in a hedge fund database you come across these 2 rows (this is a made-up data, not real):


Each of the 2 rows above contains data about a fund called Good Income B. They are identical.

So must we do to bring duplicate data into the warehouse? We de-duplicate them, by doing select distinct (or using group by). From 2 rows, we make them 1 row.

What is the de-duplication criteria? We need to have sufficient business knowledge to be able to determine the de-dup criteria. It is usually not all columns, but only a few of them. In the case of fund dimension like above, usually the business identifier is SEDOL. In the absence of Sedol or Fund ID, we use Fund Name and Sub Type (which is either Income or Accumulation).

To bring different share classes (clean vs not), we need to include AMC column. Because the clean share classes have lower Annual Management Charge e.g. 0.75% instead of 1.5%. But to differentiate between retail and institution share classes we need to include Min Initial Amount. Because usually the retail minimum amount is £500 or £1000, whereas Institution minimum amount is £50k or £100k.

Once we determine the de-dup columns, we can run group by over those columns to find the duplicates. Now we need to have another business decision, about which row we would like to bring to the Data Warehouse. In the above case, if the Fund Name + Sub Type + AMC + Min Initial Amount are the same between 2 rows, but their Lipper Preservations are different, which one are we going to bring? They lower, the higher, or any?

In this case the business knowledge (asset management industry) is required. In the above case the row with the latest Lipper scores needs to be brought into the data warehouse.

16 June 2013

Off Balance Sheet Items

Filed under: Business Knowledge,Finance,Investment Banking — Vincent Rainardi @ 8:02 am

What are off balance sheet items in investment banking? This short article tries to answer this question. In a finance data warehouse for investment banking, we have to know what are off B/S items because we have to report on it.

Balance Sheet and Profit & Loss

In finance or accounting we have five account types: asset, liability, equity, revenue and cost. The first 3 are balance sheet accounts (B/S), the last 2 are profit & loss accounts (P&L). Asset is a debit account (Dr), whereas liability and equity are credit account (Cr). Revenue is Cr, cost is Dr.

In banking the examples are:

  • Assets: balances at central banks, trading portfolio assets, loans to banks, loans to customers, repurchase agreements, debt securities, equity shares, derivatives, property, settlement balances
  • Liabilities: banks deposits, customer deposits, repurchase agreements, short positions, debt securities, trading portfolio liabilities, derivatives
  • Revenues: net interest income, fees and commissions, income from trading activities, other operating income
  • Costs: staff costs, indirect costs

Off-Balance Sheet (Off B/S)

Off balance sheet items consists of 2 things: guarantees, derivatives and managed assets.

Guarantees are basically: if the client can’t pay, we pay (“we” as in: “the bank”). And we get paid a fee for this. There are various form, the most popular ones are standby letter of credit and loan guarantee.

  • Letter of Credit (L/C) is basically: we pay the seller, regardless the buyer pays us or not. And we get paid a fee for this (from the buyer). L/C is used for payment in a international trade, i.e. a buyer buy something from a seller in another country.
    Standby L/C (SLC) is not L/C. L/C is a form of payment. SLC is a form of guarantee. There are two kinds of SLC:
    1. Performance SLC: we guarantee a construction contract. If the contractor failed to build it in time and in satisfactory quality, we pay.
    2. Financial SLC: we guarantee a company paying money. If they can’t pay, we pay. Example: insurance company paying claims, hedge fund can’t settle the end of day trade balance (lack of cash flow), company can’t pay employee’s salary.
  • Loan guarantee is basically if the obligor can’t pay, we pay. Example: an SME can’t secure funding from a bank (3y FT loan) due to shortfall of security i.e. insufficient collateral. We guarantee the SME will be able to meet their obligation (make the payment everytime it’s due). For a fee of course, from the SME.

There’s also another type of guarantees: note issuance facilities (NIF). When a company issues notes (debt securities), and they can’t sell it, we buy it. Usually we do this in syndication. This is off B/S because the guarantee is only a contigency, so it doesn’t go into B/S. When we have to buy the debt securities, at that time we put it on B/S. Similar to NIF is revolving underwriting facilities (RUF).

Off B/S derivatives covers 5 areas: FX derivatives, interest rate derivatives, credit derivatives, equity derivatives and commodity derivatives.

  • FX derivatives include forward FX contracts (buying or selling FX in the future at a rate agreed today), FX swaps (exchange of interest rates between fix interest rate and floating interest rate, in different currencies), FX options (right to buy or sell FX at a rate agreed today, both OTC and Exchange Traded)
  • Interest rate derivatives include IR swaps (exchange of IR between fix and floating in the same currency), forward rate agreement (like IR swap but starting in the future), OTC options (bought and sold), exchange traded future and options, and IR derivatives cleared by central counterparty (CCP).
    Note: in CCP trade, the OTC contract is replaced by 2 contracts: 1 between the buyer and CCP, and 1 between the seller and CCP. This is to reduce counterparty risk. Example of CCP for IR swap is SwapClear.
  • Credit derivatives include both OTC swaps and the ones cleared by CCP, and both single name and index. Credit derivatives consists of Credit Default Swaps, Credit Linked Note, Collateraized Debt Obligation, Constant Proportion Debt Obligation.
  • Equity derivatives include both equity and stock index. OTC equity options (incl. warrants), equity swaps and forwards, OTC derivatives, and exchange traded equity futures and options.
  • Commodity derivatives include OTC commodity options, commodity swaps and forwards, OTC derivatives, and exchange traded equity futures and options.

Managed assets are client assets, which we manage as a fund manager or asset management firm. We invest the client’s money into equities, cash and bonds, with or without using derivatives. Of course it belong to the clients hence it’s off balance sheet, but the bank has liability to report it.

15 March 2013

Treasury in Investment Banking

Filed under: Business Knowledge,Investment Banking — Vincent Rainardi @ 7:20 am

If you are asking yourself “I’m a data (warehouse) architect, why on earth do I have to know about Treasury?”, well, you don’t, if you are not in banking. If you are in banking, then you need to know treasury because it is a supporting function that all banks have. Whether you are in Fixed income, FX, equity, credit risk, market risk, commodity, or any areas of the bank, one way or another you will come across Treasury. Working in banking without knowing Treasury is like working in general insurance without knowing Underwriting. Yes, in investment banking Risk and Trading are important, but Treasury is equally important because it is one of the 2 divisions that underpins all other areas in the bank (the other is finance). A data architect must understand the subject area. Let’s start.

The core functions of treasury division in investment banking are Asset Liability Management (ALM) and Liquidity Management.

ALM manages 4 risks which occur because mismatches between assets and liabilities:

  1. Interest Rate risk
  2. FX Rate risk
  3. Credit risk
  4. Operational risk

Liquidity management ensures that the bank can pay all payment obligations when they are due.

  1. Maturity profile (all liabilities – funding and issuance)
  2. Set the limits (for funding and bond issuance)
  3. Cash flow – monitor and forecast (incl central bank)
  4. Monitor funding (secured and unsecured)
  5. Stress testing – baseline, adverse, severe scenarios
  6. Asset liquidity (assessment)
  7. MTM (Mark To Market) basis, not book values

Stress test factors: FX rates, prices, interest rates, economic.

  • Baseline scenario (moderate expansion): GDP 2.75% up YoY, unemployment 6.75% in 2015, property up 3% per year, 1Y T bill up 20 bps per quarter to 2% in 2015.
  • Adverse scenario (moderate recession): GDP -2%, unemployment 9.75%, property down 6% per year, 10Y T bill 4% by end 2013.
  • Severe scenario (severe recession): GDP -5%, unemp 12%, property -20%, int rates 0%, 10Y T bill 1.25%

A treasury division can have these trading desks: fixed income desk, equity desk, FX desk, money market desk, proprietary desk. These desks trade those asset classes not to take a certain position to make money, but to hedge risks.

Difference maturity profile in fixed income credit liabilities — trades CDS. For example, if the overall horizon of the bank position in FI credit is too long term (say 4.4 years) compared to asset maturity profile (say 3.3 years as the majority is corporate credit facilities), then they might buy and sell CDS indices (or single names) to pull the maturity profile of the FI closer, i.e. to 3.8 years.

FX trades are used to maintain stability relative to the balance sheet currency. For example, if the 30% of asset is in GBP and the B/S is in CHF, and the GBP is weakening to CHF, then the bank may consider closing FX swap trades. Treasury does not take FX position in order to benefit from FX directional trends nor from FX volatility, e.g doing Delta or other Greeks.

Props (proprietary desks) are desk which trades with the banks own asset. It can trade various instrument classes, e.g. FI, equity, FX or money market, even commodity.

By far, the busiest treasury desk is money market. It monitors the bank’s positions within central banks (not only position/balances, but also access to Central Bank’s facilities, i.e. limits), and against all counterparties. The bank trades money market instruments (e.g. CPs, T bills, CoD, repos, Eurodollar, etc) in order to minimize the liquidity risk. For example, if outflows are forecast to make liquidity low in the coming weeks, it is treasury primary job function to eliminate the cash flow risk by tapping funding from money markets, at responsible costs. The funding profile also triggers money market trades, e.g. bond and equity issuance (capital markets) are long terms, hence treasury go to money markets for short term “fill in”. Treasury can also issue CPs, in addition to trading CPs, as investment bank’s ratings are usually triple As or one notch under.

Create a free website or blog at