CALYON – Conception and realization of information system for credit risk management reporting
septembre 2004 - juillet 2007
Summary:
Evolution in the work environment of CB´s validation of the merger of Lyonnais-CA, having great autonomy and responsibilities, direct cooperation with the Risk-Management department.
Conception and implementation of the Credit-Risk Management reporting system using OLAP Cube, Excel Pivot Table, TransactSQL (stored procedure), focusing on credit risk indicators analysis: Rate, Credit and Correlation Sensitivities, Jump to Default, Stresses, P&L and VaR. Indicators calculated by connexes systems, namely Murex.
Design, conception and implementation of the SYBASE 12.53 SGBDR and Data warehouse. Organization of flux necessary to maintain the data bases: market data (spreads, ratings, spots…) – Calculated data: Sensitivities, Jump to default, Gamma, Stresses, Var…) – Referential data…Architecture and technologies choice. Conception of the client-application of Excel reports generation. Automation of data integration processes.
Credit Instruments management: CDS, CDS of ABS, CDO, CDO2, FtD, NtD, IDT… and Rate Instruments management: Bonds, ABS, ASWP, FUT, REPO…
Role : Developer – Business Analyst
Realization :
Reporting system on Sensitivities Analysis – Credit Risk Management and Business Analyst team (6 months)
Reports on Rate and Credit sensitivities aggregation analysis. Business analyst and programming rule. Prototype release using Access 2000 – Excel - VBA, using the Murex software package and producing Excel reports for the Risk-management department.
Reporting on Sensitivities aggregation among numerous analysis axis: Portfolio, Issuer, Currency, Country, Sector…
Study of and participation in the issuing of specifications.
Conception, development, installation of the application and training of users.
Reporting system on Sensitivities - Stresses,- Jump to Default and P&L analysis - Credit Risk IT Team
Set up a more robust version with a SYBASE 12.5.3 of 80 Go basis, a VB6 client connected through ODBC allowing the integration and the exploration using reports generation of calculated data (originating from internal systems and the Murex software package): Credit Sensitivities, Stresses, Jump To Default, P&L Calculus, Ratings…Issue of numerous synthetically and analytical Excel reports based on different risk indicators.
VAR Analysis - Credit Risk IT Team
Considering the volumetric of the VAR history data (5M lines /day – 261 Scenarios) in order to be able to allow Excel reporting via TCD, the solution I came up with was to use a pivot table not being supported by SQL but by OLAP (On Line Analytical Processing) cubes. More precisely, the Data warehouse is built on the SGBDR SQL Server 2005 from which OLAP Cubes are generated under Analysis Services 2005 allowing the production of different Excel (Pivot Table) reporting.
I carried out a vast study of different OLAP market solutions in the form of test and comparison (SYBASE IQ / Mondrian Java project / SQLServer 2005 Analysis services).
Creation of the end user application in C# (VStore) using Excel Interop.
Design of the SGBDR and OLAP cubes of VAR reporting under SQL Server & Analysis Services 2005
Installation of the application and training of users.
Techniques used : SQL Server 2005 – Analysis Services 2005 (OLAP, MDX, XMLA) – Reportings Services 2005 – DTS - OLE DB – C# –.NET 2.0 – Sybase 12.5.3 (TransactSQL – Power Designer 8 & 10) – ODBC – Access 2000 – VBA – VB6 – Excel (TCD, Pivot Table).
