# Charting A Mashup Stress Scenario using R

From the previous post we have our mashup stress scenario data in R and now we’d like to plot it. We could of course save the R data to database or file, import into a data visualisation tool such as Tableau and chart it there. However, R has some powerful charting capabilities and this article will demonstrate these.

Let’s create a lattice chart with each of the five scenarios in a different panel. In each panel, we’ll show the stress P&L by counterparty. Firstly, we need to summarise the data. Figure 1 shows the data frame.

Figure 1

Figure 2 shows the R code to sum the stress P&L and group by the two categories we want to see in our plot (counterparty and scenario) and to build the plot.

Figure 2

Figure 3 shows the resulting plot.

Figure 3

We may like to see a different perspective on the data – perhaps as a clustered bar chart showing the stress P&L by scenario for each counterparty. Figure 4 shows the code to generate the plot and Figure 5 the result.

Figure 4

Figure 5

This article just scratches the surface of R’s plotting capabilities. It’s also possible to create box plots, line charts and scatter plots, to have a great deal of precision on the detail of each chart, to layout several charts on a single page and to produce charts in various formats including pdf.

# Calculating A Mashup Stress Scenario Using R

A previous article described the business case for creating a mashup scenario quickly.  Often, this needs to be a self-service solution created by risk managers or business analysts themselves. One approach to doing this is to use R, an open source language for statistical use that has very good capabilities for transforming data. This article will use R to calculate the mashup scenario. The next article will use R to provide graphs of the results.

The first step is to load the stress data of the four original scenarios into an R data frame (R’s equivalent of a database table).  Figure 1 shows some of the stress data in an R data frame.

Figure 1

Now we can build the mashup scenario in a few steps.  The previous post described the business rules.  The first rule is to copy over trades from the Grexit scenario where either the issuer or counterparty is from Greece.  Figure 2 shows the single line of code necessary.

Figure 2

The next step is to add trades from the Rally scenario where the instrument currency is in Euros.  However, as this is not the first step, we have to be careful not to copy over any trades already in the mashup scenario.  Figure 3 shows the code.

Figure 3

The following steps to append the trades from the other two scenarios are similar.  The final steps are just tidying up the scenario names and appending the mashup data set onto the original data set.  Figure 4 shows the newly created mashup scenario rows

Figure 4

Figure 5 shows the code in its entirety.  It shows how R can perform quite complicated operations very concisely.

Figure 5

# Mashing Up An Urgent Stress Test

A stress test group estimates how the their bank’s positions and risks would change under a set of economically plausible but possibly unlikely scenarios. They may on occasion need to create a new scenario urgently, perhaps in response to a recent news event.

Let’s imagine that it looks like Greece will exit the Euro but the latest analysis is that the Euro area will survive without further repercussion. At the same time, the UK finances look shot and so Moody’s has just announced it will downgrade 1 notch. The stress test group need to create a new scenario to reflect these conditions. The bank is already running many scenarios on a regular basis. Let’s say that their inventory already includes four scenarios;

• Grexit – a Greek exit from the Euro,
• Unexpected Rally,
• Severe Market Slide and
• Global Recession.

The stress test results for these and all other scenarios are generated from valuations performed by the front-office systems which are then loaded into the stress test system for analysis and reporting. However, it’s usually difficult to create a new scenarios quickly. The stress test team needs to work with several fromt-office teams to organize this. The introduction of the new scenario needs to fit in with planned releases of all the front-office systems and space needs to be allocated in the processing window to generate that new scenario. It may take one month or two to achieve this whereas the results are needed in a day or two – or even for a board meeting that evening!

One alternative is to mash up existing scenarios if an approximation to the new scenario can be expressed in terms of assembling together existing scenarios. This is obviously not as rigorous or as accurate as creating the scenario in the usual way but timeliness is everything.

There are two characteristics of stress test data (assuming it is already in a well structured database) that are of great help here. Firstly, the facts have only three essential properties; the unique trade identifier, the scenario identifier and the P&L value. Once we know the unique trade identifier, we know all the other information about the trade (e.g. trader, book, counterparty, trade-date) and instrument (product type, country of the issue and rating, currency etc). Similarly, the scenario identifier will provide all the other information about the scenario (name, description, market stocks. All this data is held in the dimension tables of the stress text database. Secondly, there is exactly one row in the fact table for each trade for a given scenario.

Let’s say that the business specification of our mashup scenario is as follows

1. Apply the ‘Grexit’ scenario to any trades issued in Greece or with a counterparty
2. On the remaining trades, apply the ‘Unexpected Rally’ scenario to any trade with an instrument currency of Euro
3. On the remaining trades, apply the ‘Severe Market Slide’ to any trade where issuer country is the UK
4. Apply the ‘ Global Recession’ scenario to all other trades in the portfolio

Figure 1 shows the details for a rather small fictitious portfolio of 4 trades. We can apply the rules to select and create the 4 facts that will comprise our new mashup scenariousing some straight-forward SQL. The new Mashup rows are highlighted in green and the facts of tha were selected from the existing scenarios the new scenarios in blue.
[Figure 1]

Figure 2 shows a snsapshot of a visualisation, created with Tableau, with the mashup scenario on the right.
[Figure 2]

Of course, I’ve ignored a few small matters; scaling up (since a typical portfolio may have 4 million rows rather than just 4), testing, providing an environment separate from the regular production environment that we can use to create the urgent scenario, deployment, approvals and so on). The purpose however is to show that at the core this is a very simple procedure that is capable of providing a “good-enough” results in a short time.

# Visualising the EBA stress results

In July 2011, the European Banking Authority (EBA) published results of a stress test of 90 of the biggest banks in the EU.  The purpose of the stress test was to test the resilience of the banks in an “adverse but plausible” scenario to give investors good data and transparency about the stability of these banks.  The details are here http://www.eba.europa.eu/EU-wide-stress-testing.aspx

The data provided for each bank included the risk weighted assets (RWAs), core tier 1 (CT1) capital and ratios, exposures at default (EADs) to various sectors of the economy and also a breakdown of sovereign exposures by country and residual maturity.

The EBA provided a report into each bank but also thoughtfully made the raw data available.  I have mashed this up and made some visualisations using Tableau Public data visualisation software.  (See http://www.tableausoftware.com/public/ for more details).   These provide great insight into the data.

The first visualisation shows the exposures to each sovereign country.  Use the ‘bank’ selector to look at all banks or for a particular bank. You can ask questions such as “what is RBS’s exposure to sovereigns?

The second visualisation shows the exposures by bank.  Use the ‘country’ selector to look at all sovereign exposure or for a particular country.   You can ask questions such as “which banks are most exposed to Greece?”

I find this “heat map” style of visualisation useful for an “at-a-glance” overview of the data. Much of the power of the visualisation is the interactivity so I suggest that you have a play.

This next visualisation shows the the exposure to the PIIGS – the five countries that have been of most concern (Portugal, Ireland, Italy, Greece and Spain) during the credit crisis.  I have grouped the banks by their home country. Obviously banks have a lot of domestic exposure but it’s interesting to see that French, German and Belgian banks have a significant amount of Greek debt – and even more Spanish debt.

Of course, capital adequacy data as of December 2010 are very old news. A lot has happened in the year since the EBA published these results last July. Many of the banks have improved their CT1 ratios (indeed, one purpose of the stress tests was to galvanise banks into exactly this action). Bankia, a Spanish bank has been re-structured. Over the next few weeks, I will be bringing the data more up-to-date and providing more visualisations.