FIN957: Building Time Series Data Stores for Portfolio and Risk Analytics

1. FIN957: Building Time Series Data Stores for Portfolio and Risk Analytics Sinan Baskan Area Lead Architect, Sybase, Inc. sinan.baskan@sybase.com August 15-19, 2004

2. Contents • Time Series Data and Applications • Definition & properties • Application and usage profile • Time Series Data Management • Issues with RDBMS & SQL • Available Platforms • Constraints vs. Future Requirements • Capital Markets and Requirements • Historical Time Series • Market data feeds and streaming data • Reference data integration • Time Series and Analytics Platform • Real time analytics • Reference architectures

3. Time Series Data • Definitions & Properties • Time series - sequence of values recorded at regular and increasing intervals • Regularity of time series – frequency is the same for the entire series • Non-regularity – frequency may change at some point (due to an event such as stock split, for instance) • Historicity – past behavior is an indicator of future behavior (weighted historicity: recent history is more representative that distant history). • Normalization – consistency of value/time pair along the series Note – temporal rules and relative positions in the sequence do not yield true time series without these properties. • Applications • Statistical analysis – moving averages, autocorrelations, auto regression etc. • Portfolio Analysis – asset allocation, efficient frontier etc. • Trading & Hedging – strategies, arbitrage, scenario generation, security pricing (derivatives, collateralized debt obligations) • Econometric models

4. Time Series Data Management • RDBMS and Time Series Data • Relational model does not leverage the ordered structure of time series • “order by” returns data sets that require manipulation with C/C++ or other languages than SQL (Select from, where not useful). • Example - correlate the price history of IBM and HP stock and call options, also correlate moving returns (t vs. t+1) on trades on the four securities. Repeat over successive time intervals. What would SQL look like to do this? • Structuring and Normalization of T/S Data • Time Series data set has a frequency and may have no value in certain positions • Two Time Series sets may have shifts in time scale in identical positions (e.g. 10AM quote in Tokyo is not the same as 10AM quote in NY. • Different value types – static (level) values remain same over successive periods when no events occur, dynamic (streaming) values are zero when no event occurs. • Correlating time series with different frequencies (minutes vs. hours) • Synthetic time series – e.g convertible bonds continue life as stocks (what is the consistent time series that represents the volatility of the returns since issuance?).

5. Time Series Data Management Platforms • Major Vendors • Most vendors have offered Historical Time Series servers and Query Tools • Fame • Thomson Financial • SAS • SPLUS • Asset Control • Lym • SPSS • Real Time Series and Market Data Feeds • In memory and cache management platforms • Kx Systems (kdb) • Intersystems (Cache/Ensemble)

6. Current State • Time Series Technology Today • Data architecture dating from late 1970sto early 1980s • Proprietary file structures and, query tools and analytical libraries • Data organization optimized to specific query tools and models • Very little interoperability between data servers and tool sets • Expensive to maintain, difficult to implement resilience models • Data preparation and presentation is mostly batch driven • New Entrants • Asset Control, Jrisk, • ASP models from Reuters, Thomson, Murex • Consolidator/Aggregator of content as a value added service from T/S and analytical software vendors (Soliton, Barra, Haver, etc.) • New data architectures based on RDBMS or vector based database technology w/ C/C++, Java embedded extensions to SQL.

7. Future Requirements • The goal is to enable near real time decision making • Rigorous, thorough, post-mortem analysis yes, but ….. • Near real time decisions to respond to emerging trends effectively first then post mortem analysis • Financial markets (trading, credit/market risk, compliance monitoring) • Emergency care and assistance (epidemics, weather related events) • Pattern recognition (fraud detection, internet security) • The capacity to collect and disseminate data in large volumes has outstripped the capacity to transform data into information before some of the data grows stale. • Inbound data flow management (including data validation, normalization) and content aggregation are major bottlenecks due to the large volumes of data • The Time Series data must be maintained in a multi-site Business Continuity/Disaster Recovery Architecture in some cases • Time series as streaming data across a messaging backbone for presentation of selected subsets to different user communities is becoming critical in banking, energy, commodities and securities trading.

8. Capital Markets and Time Series Data • Time Series Data Sources • Market data from securities/commodities exchanges worldwide • Trading and Trade order management & settlement systems • Economic/econometric forecasts and models –worldwide public, private and international institutions (IMF, World Bank) • An increase of 34-fold in the past five years • An increase of 4-fold in the number of financial instruments traded worldwide • Use of Time Series Data • Historical Time Series – end of day batch processing produces a complete data set up to the end of the last trading session for post-mortem analysis of risk, P&L, effectiveness of trading/hedging strategies • Market data integration – TAQ data from exchanges are consolidated continuously as streaming data to build a “TAQ” warehouse and select data sets are published to different user groups for intra-day analysis (very rare practice today) • Reference data integration – reference data and market data in integrated in one risk data warehouse.

9. Capital Markets Trends • Decimalization, increased competition, cross-border trading and lower trading volume are imposing pressure on gross margins in securities trading • Increased regulation is imposing limits on the amount of capital available for trading • Capital inflows into capital markets are decreasing in light of the recent downturn • Future profitability depends on better utilization of available capital resources rather than expansion of the capital base or appreciation of asset values due to economic expansion

10. Risk, Return , Volatility and Time Series Data Value of Collateral Credit Risk Market Volatility Trading Risk Portfolio Risk Market Risk RaRoC Global Exposure Trade Volume Operational Risk

11. Margin Drivers Profits • P = (Turnover of Capital)*(RaRoC)*(Economic Capital) - costs • RaRoC is risk adjusted return on capital • Under current market conditions the growth rate of E/C, the investments returns and fee based income will likely be very low or nil. • In the current environment, financial institutions need to increase their control over capital turnover and RaRoC to drive profits. • RaRoC components – risk & cost of capital (~ interest and exchange rates)

12. New Requirements • Drivers for Streaming Time Series Data • Program trading and Algorithmic trading • Asset base growth in hedge funds – increase in trading volume • Tighter risk management practices – Basel II • Risk-adjusted Return on Capital as the basis for managing economic capital, profitability, and P&L and compliance reporting • Decimalization reduces trading profits • Capital turnover at a lower RaRoC improves profits • Intra-day risk and P&L monitoring requires access to up-to-date time series (state of the market view with 20-30 minute latencies)

13. Time Series and Analytics Platform • Technical requirements for Intra-day processing of T/S Data • Multiple sources of reference data • Multiple streams of time series data (real-time and historical) with varying degrees of inconsistency (temporal, accuracy, format) • Aggregation of real time position data from front office systems • Complexity of presenting trading risk at the desk and global risk in the course of trading day due to differences in temporal and content • Computational model size and throughput performance

14. Managing Inbound data • Integration and Normalization • Data with different time lines • Data with different format • Presentation of real-time and historical time series with different periodicity • Volume and rate of inbound data • Multiple currencies • Corporate actions data

15. V a R engine Real Time Analytics Analysis Matrix/Cube Time Series Real Time Volatility vectors Time Series Hist. Initial Load Cash-Flow Mapping Price Changes Reference Databases Positions Mark to Market Global Exposure Map

16. Architecture for Intra-day Time Series Analysis Data Flow Engine Risk Analytics Time Series & Data Engine Market Data VaR Models Data Structure Module (Constructs the analysis cube, the matrices, validates numerical consistency etc.) Co-variance matrix size up to ~5000 Each column is a separate vector Market Data Formatter Stress Testing Corporate RDBMS Gateway ADR Models Risk Engine API DB Portfolio Models T/S Data Formatter & Processor Trading Models Time Series Historical time series data Result Set Management Intra-day Management Reports Alerts & Reports R/A Results Report Router Rules DB

17. Managing Outbound Information • Manage result sets of a series of analyses • Provide context and time frame • Comparative evaluation of alternative strategies • Filter and categorize results • Store for historical analysis, stress testing, scenario generation • Several levels of summation and aggregation levels • Distribute within a short cycle for decision makers.

18. Global Exposure report • Exposure - positions, P&L • Funding Costs - operations, borrowing and margin • Risk Profile – forward looking, estimated volatility, forecast • Capital Charge – P&L, capital reserve requirements

19. Advantages • Higher Risk-adjusted Returns on Economic Capital • Improved liquidity in Capital Market • Enhanced allocation models for portfolios • Compliance with Basel II and III accords without substantially reducing E/C • Opportunity to meet operational risk requirements through improved corrective action