learn, grow, adapt 07/10/15 sqlrelay.co.uk/ nottingham 08/10/15 sqlrelay.co.uk/ reading 12/10/15...
TRANSCRIPT
![Page 1: Learn, Grow, Adapt 07/10/15 sqlrelay.co.uk/ Nottingham 08/10/15 sqlrelay.co.uk/ Reading 12/10/15 sqlrelay.co.uk/ London 13/10/15 sqlrelay.co.uk/ Bristol](https://reader035.vdocuments.us/reader035/viewer/2022062217/5697bfc41a28abf838ca5c9f/html5/thumbnails/1.jpg)
Learn, Grow, Adapt
07/10/15 sqlrelay.co.uk/ Nottingham
08/10/15 sqlrelay.co.uk/ Reading
12/10/15 sqlrelay.co.uk/ London
13/10/15 sqlrelay.co.uk/ Bristol
14/10/15 sqlrelay.co.uk/ Cardiff
15/10/15 sqlrelay.co.uk/ Birmingham
![Page 2: Learn, Grow, Adapt 07/10/15 sqlrelay.co.uk/ Nottingham 08/10/15 sqlrelay.co.uk/ Reading 12/10/15 sqlrelay.co.uk/ London 13/10/15 sqlrelay.co.uk/ Bristol](https://reader035.vdocuments.us/reader035/viewer/2022062217/5697bfc41a28abf838ca5c9f/html5/thumbnails/2.jpg)
Awesome SQL Server conferences on your door step!
w: sqlrelay.co.uk t: @sqlrelay_uk
Business Analytics To Manage Financial
RiskMark Wilcock, Zomalex Ltd.
![Page 3: Learn, Grow, Adapt 07/10/15 sqlrelay.co.uk/ Nottingham 08/10/15 sqlrelay.co.uk/ Reading 12/10/15 sqlrelay.co.uk/ London 13/10/15 sqlrelay.co.uk/ Bristol](https://reader035.vdocuments.us/reader035/viewer/2022062217/5697bfc41a28abf838ca5c9f/html5/thumbnails/3.jpg)
Bronze
Silver
![Page 4: Learn, Grow, Adapt 07/10/15 sqlrelay.co.uk/ Nottingham 08/10/15 sqlrelay.co.uk/ Reading 12/10/15 sqlrelay.co.uk/ London 13/10/15 sqlrelay.co.uk/ Bristol](https://reader035.vdocuments.us/reader035/viewer/2022062217/5697bfc41a28abf838ca5c9f/html5/thumbnails/4.jpg)
Agenda
• Why does Risk Management matter?• 5 easy ways to lose money• The regulators and their regulations• Data / Aggregation / Reporting – challenges &
approaches• Some interesting books & articles
![Page 6: Learn, Grow, Adapt 07/10/15 sqlrelay.co.uk/ Nottingham 08/10/15 sqlrelay.co.uk/ Reading 12/10/15 sqlrelay.co.uk/ London 13/10/15 sqlrelay.co.uk/ Bristol](https://reader035.vdocuments.us/reader035/viewer/2022062217/5697bfc41a28abf838ca5c9f/html5/thumbnails/6.jpg)
Royal Bank Of Balham
Save with our NiceSave savings rangeExcellent saving rates – best on the market!How much will you deposit?a) £0 b) £50,000c) £150,000
![Page 7: Learn, Grow, Adapt 07/10/15 sqlrelay.co.uk/ Nottingham 08/10/15 sqlrelay.co.uk/ Reading 12/10/15 sqlrelay.co.uk/ London 13/10/15 sqlrelay.co.uk/ Bristol](https://reader035.vdocuments.us/reader035/viewer/2022062217/5697bfc41a28abf838ca5c9f/html5/thumbnails/7.jpg)
What could possibly go wrong?
![Page 8: Learn, Grow, Adapt 07/10/15 sqlrelay.co.uk/ Nottingham 08/10/15 sqlrelay.co.uk/ Reading 12/10/15 sqlrelay.co.uk/ London 13/10/15 sqlrelay.co.uk/ Bristol](https://reader035.vdocuments.us/reader035/viewer/2022062217/5697bfc41a28abf838ca5c9f/html5/thumbnails/8.jpg)
Royal Bank Of Balham
• Would you deposit your money? What interest rate?
• Would you buy shares in RBB? What dividend rate?• If you were a trader in RBS, would you enter a long
running trade with RBB?• What are Mark Carney’s concerns about RBB?
![Page 9: Learn, Grow, Adapt 07/10/15 sqlrelay.co.uk/ Nottingham 08/10/15 sqlrelay.co.uk/ Reading 12/10/15 sqlrelay.co.uk/ London 13/10/15 sqlrelay.co.uk/ Bristol](https://reader035.vdocuments.us/reader035/viewer/2022062217/5697bfc41a28abf838ca5c9f/html5/thumbnails/9.jpg)
5 easy ways to lose money
• Asset prices drop (market risk)• A borrower or issuer defaults (credit risk)• Your counterparty does not pay up (counterparty
credit risk)• You run out of cash to pay immediate bills (liquidity
risk)• Rogue trader or cock-up (operational risk)
Got to keep cool in a crisis
![Page 10: Learn, Grow, Adapt 07/10/15 sqlrelay.co.uk/ Nottingham 08/10/15 sqlrelay.co.uk/ Reading 12/10/15 sqlrelay.co.uk/ London 13/10/15 sqlrelay.co.uk/ Bristol](https://reader035.vdocuments.us/reader035/viewer/2022062217/5697bfc41a28abf838ca5c9f/html5/thumbnails/10.jpg)
Types Of Data
• Trades and positions• Market prices and time-series history• Counterparties • Issuers• Firm hierarchies • External reference – countries / currencies• Results – sensitivities and risks
![Page 11: Learn, Grow, Adapt 07/10/15 sqlrelay.co.uk/ Nottingham 08/10/15 sqlrelay.co.uk/ Reading 12/10/15 sqlrelay.co.uk/ London 13/10/15 sqlrelay.co.uk/ Bristol](https://reader035.vdocuments.us/reader035/viewer/2022062217/5697bfc41a28abf838ca5c9f/html5/thumbnails/11.jpg)
A typical market risk report
Mark
CoB Book
CoB Limit Utilisation (%)
Economic Capital 100 200 50VaR 102 150 68 CoB Change CoB Change Ccy CoB Change
Delta (1) 110 105 105 BASF 1,000 100 Financial 1,000 100 EUR 100 10
Gamma 120 121 99 FTSE 100 Index -909 -83 Tech 909 -83 JPY -87 -9Vega 130 131 99 Barclays plc. 826 69 Consumer 826 69 GBP 76 8Correlation 140 150 93 Prudential plc. -751 -58 Industrial 751 -58 BRL -66 -7Theta 20 16 125 Cube Logic 683 48 Energy 683 48 HKD 57 6Dividend Risk 5 4 125 Nikkei 225 Index -621 -40 Basic Materials 621 -40 NZD -50 -5Stress Scenario ABC 10 11 91 HSBC 564 33 Pharmaceutical 564 33 AUD 43 4Stress Scenario XYZ 20 19 105 Oracle -513 -28 INR -38 -4
Zomalex plc. 467 23 CAD 33 3BP plc. -424 -19
P&L ladder
FX ExposureDelta By Issuer Delta By SectorUnderlier Issuer
22 September 2011 Global Derivatives USD'k
00
00
00
1 m
onth
2 m
onth
s
3 m
onth
s
6 m
onth
s
1 ye
ar
18 m
onth
s
2 ye
ars
3 ye
ars
5 ye
ars
10 y
ears
Thou
sand
s Dvidend sensitivityCOB COB-1
-20
-10
0
10
20
30
40
50
-40 -30 -20 -10 0 10 20 30 40
COB
COB-1
![Page 12: Learn, Grow, Adapt 07/10/15 sqlrelay.co.uk/ Nottingham 08/10/15 sqlrelay.co.uk/ Reading 12/10/15 sqlrelay.co.uk/ London 13/10/15 sqlrelay.co.uk/ Bristol](https://reader035.vdocuments.us/reader035/viewer/2022062217/5697bfc41a28abf838ca5c9f/html5/thumbnails/12.jpg)
Market Risk Report Examples
![Page 13: Learn, Grow, Adapt 07/10/15 sqlrelay.co.uk/ Nottingham 08/10/15 sqlrelay.co.uk/ Reading 12/10/15 sqlrelay.co.uk/ London 13/10/15 sqlrelay.co.uk/ Bristol](https://reader035.vdocuments.us/reader035/viewer/2022062217/5697bfc41a28abf838ca5c9f/html5/thumbnails/13.jpg)
The Regulators
BIS and BCBS
National regulators• FRB (US)• PRA (UK)• FINMA (CH)Regulatory Capital
![Page 14: Learn, Grow, Adapt 07/10/15 sqlrelay.co.uk/ Nottingham 08/10/15 sqlrelay.co.uk/ Reading 12/10/15 sqlrelay.co.uk/ London 13/10/15 sqlrelay.co.uk/ Bristol](https://reader035.vdocuments.us/reader035/viewer/2022062217/5697bfc41a28abf838ca5c9f/html5/thumbnails/14.jpg)
Fundamental Review Of The Trading Book (BCBS 265)All Change!• Change method of calculating results• For ~100 desks rather than a few legal entities• Report daily rather than annually• Check 5 times a day rather than end of day• Run both regulator’s and in-house calcs• Back test torture – put your money where your
model is
![Page 15: Learn, Grow, Adapt 07/10/15 sqlrelay.co.uk/ Nottingham 08/10/15 sqlrelay.co.uk/ Reading 12/10/15 sqlrelay.co.uk/ London 13/10/15 sqlrelay.co.uk/ Bristol](https://reader035.vdocuments.us/reader035/viewer/2022062217/5697bfc41a28abf838ca5c9f/html5/thumbnails/15.jpg)
BCBS 239 Data, Aggregation & Reporting• Governance
• Especially in a crisis
• Aggregation• Integrity & accuracy• Drill down
• Reporting• Comprehensive• Accurate• Clear• Timely• Actionable• Right audience
![Page 16: Learn, Grow, Adapt 07/10/15 sqlrelay.co.uk/ Nottingham 08/10/15 sqlrelay.co.uk/ Reading 12/10/15 sqlrelay.co.uk/ London 13/10/15 sqlrelay.co.uk/ Bristol](https://reader035.vdocuments.us/reader035/viewer/2022062217/5697bfc41a28abf838ca5c9f/html5/thumbnails/16.jpg)
Wiring Diagram Of A Bank (Anonymised)
![Page 17: Learn, Grow, Adapt 07/10/15 sqlrelay.co.uk/ Nottingham 08/10/15 sqlrelay.co.uk/ Reading 12/10/15 sqlrelay.co.uk/ London 13/10/15 sqlrelay.co.uk/ Bristol](https://reader035.vdocuments.us/reader035/viewer/2022062217/5697bfc41a28abf838ca5c9f/html5/thumbnails/17.jpg)
Data
Challenges• Data Quality• Accurate? Timely?
Complete? • Independently Verified?
Audited?
Approaches• Front end golden source
– and fewer systems• Keep it granular –
aggregate at last moment• Data dictionary• Culture and ownership• Using EDA tools like
Power BI to find quality issues
![Page 18: Learn, Grow, Adapt 07/10/15 sqlrelay.co.uk/ Nottingham 08/10/15 sqlrelay.co.uk/ Reading 12/10/15 sqlrelay.co.uk/ London 13/10/15 sqlrelay.co.uk/ Bristol](https://reader035.vdocuments.us/reader035/viewer/2022062217/5697bfc41a28abf838ca5c9f/html5/thumbnails/18.jpg)
Aggregation
Challenges• Adjustments• Losing vital information
along the way • Failures in calcs• As fast as possible • Large size of risks &
results
Approaches• Automation• Workflow, sign-off and
approvals, audit challenge
• Common Golden hierarchies
• Operational Dashboards• Lineage & Traceability
![Page 19: Learn, Grow, Adapt 07/10/15 sqlrelay.co.uk/ Nottingham 08/10/15 sqlrelay.co.uk/ Reading 12/10/15 sqlrelay.co.uk/ London 13/10/15 sqlrelay.co.uk/ Bristol](https://reader035.vdocuments.us/reader035/viewer/2022062217/5697bfc41a28abf838ca5c9f/html5/thumbnails/19.jpg)
Reporting
Challenges• Canned reports backlog• Limited analysis
capability• Ugly complex reports• Materiality?
Relevance?
Approaches• More interactive dashboards,
fewer canned reports• Self-reliant reporting in Power
BI• Good dashboard design• Forward looking reports• Moving to Scrum teams?• Hygiene: security,
performance, distribution, all reports in one place
![Page 20: Learn, Grow, Adapt 07/10/15 sqlrelay.co.uk/ Nottingham 08/10/15 sqlrelay.co.uk/ Reading 12/10/15 sqlrelay.co.uk/ London 13/10/15 sqlrelay.co.uk/ Bristol](https://reader035.vdocuments.us/reader035/viewer/2022062217/5697bfc41a28abf838ca5c9f/html5/thumbnails/20.jpg)
Interesting Reading
• http://zomalex.co.uk/a-laymans-guide-to-bcbs239/
![Page 21: Learn, Grow, Adapt 07/10/15 sqlrelay.co.uk/ Nottingham 08/10/15 sqlrelay.co.uk/ Reading 12/10/15 sqlrelay.co.uk/ London 13/10/15 sqlrelay.co.uk/ Bristol](https://reader035.vdocuments.us/reader035/viewer/2022062217/5697bfc41a28abf838ca5c9f/html5/thumbnails/21.jpg)
Thank you
zomalex.co.uk
meetup.com/london-business-analytics-group