Category Archives: Risk Data

Using The R Script Visual in Power BI To Build Charts With Precise Layouts

Financial regulators are unreasonable people – at least that’s the impression those of us who report a bank’s risk numbers to them would have you believe. Not only do they demand daily reporting but they also require the charts that we provide to have a defined layout and format. This does raise a problem for Power BI in that while it has very good visual practices, these are not exactly those of the regulator. However, we can use the R visual to create a chart in exactly the fashion that a regular demands.

One very important chart for regulators is the market risk back test chart. It is realy two charts in one; a bar and area chart. It compares the profit and loss (P&L) that the bank, or part of the back such as a legal entity or desk, against a margin of safety, known as value-at-risk or VaR, that we promise that the P&L will stay within most of the time. On those days that the P&L exceeds the VaR, that’s a breach. If we have too many breaches, the regulator will take action.

The regulator wants to see the VaR as an area chart with VaR as a mirror image both above and below the x (date) axis since a breach occurs if the bank makes either a profit or a loss outside this VaR envelope. Regulators want this VaR envelope to be partly transparent so that we can superimpose the P&L on it. The daily P&L must be shown as blue bars with breaches shown in red.

In Power BI chart we can get close to these requirements – close, but no cigar.

Backtest built in native PBI

There are three problems

    • The VaR envelope is shown as two boundary lines rather than area chart. The Power BI combo chart visual combines a bar chart (used for the P&L) with a line chart but we can’t combine with any other chart
      • The P&L bars are all blue; there is no means of changing the colour of individual bars based on a condition
      •  The snapshot below show synchronized axes for P&L and VaR but if the the ranges of these two measures are sufficient different, the chart will automatically have different scales for P&L and VaR, which is not what we want and is potentially misleading.

However, we can meet all requirements if we use the R visual. We can use the fine tuning allowed in the plotting package to define our chart elements precisely. The snapshot below shows the R chart.

Backtest built in PBI R Script Visual

The R visual creates a script editor in a lower pane. It provides the first 2 lines of R code to create an R dataframe (R’s equivalent of a table) that contains all unique values of the columns in the values well. From here we can add a few lines of R code firstly to determine if there is a breach on a given day and then to plot the chart. The R code uses two very popular R packages. The dplyr package enables us to manipulate dataframes and the ggplot2 package gives us the ability to plot exactly the chart we require. The ggplot call first creates the P&L bar chart. It selects the colour of the P&L bars based on the breach condition. It then overlays this with partially transparent area charts – one for the upper VaR bound, then one for the lower (negative) VaR bound.

PBI R Script Editor Window

Even with a small amount of R code like this, it is best to build and test the R code within the RStudio, the typical development environment for R, then copy it into the code window for the visual once it is fully working.

The ggplot2 package allows fine tuning of a charts appearance – enough to satisfy the most unreasonable regulator.

This article first appeared on the Microsoft MVP Technical Tuesdays blog  here
The source data is here.

A layman’s guide to BCBS239

This is a draft for a talk I’m giving in a few months – comments welcome

In January 2013, the Basel Committee on Banking Supervision (BCBS) issued a document titled “Principles for effective risk data aggregation and risk reporting”. It is generally known as “BCBS 239”.The document sets out guidelines for effective reporting of risk to the board and senior management of banks.

So does BCBS 239 matter? Well, if a bank is a SIFI (systematically important financial institution), big enough so that its failure could cause instability in the financial system then it does matter since such banks need to comply with the guidelines, some as early as 2016. But I would argue these regulations are important even if a firm is not a SIFI simply because BCBS 239 describes the best practice of this important topic and does it concisely and clearly. The document says that one of its objectives is enhanced risk management and decision making, and to improve strategic planning – for the ultimate good of the firm and its shareholders.

The BCBS has the interests of the taxpayers rather than shareholders in mind. Its motivation for issuing this document arise from the crisis of 2007 when some Risk departments could not cope and it was impossible to get good risk information for proper decision. In any future crisis, the regulators want the good time risk information to be able to wind-up the bank or perhaps organise a recovery.

The document defines 14 principles and groups those into 4 areas; governance, aggregation, reporting and supervisory review. The table below summarises these.

Area Principle   # Summary
Governance and Infrastructure 12 Strong governance “in the round”Risk IT must be able to cope in a crisis
Aggregation 34



Achieve integrity and accuracy via automationCompleteness at group level but also drill down to entity

Timeliness-especially in the crisis

Adaptability-for changing circumstances

Reporting 78




Accuracy through reconciliation and validationComprehensiveness – include all material risks

Clarity – to facilitate informed decision making

Frequency of reporting-appropriate to nature of risk

Distribution to right people (and only them)

Supervisory Review 1213


Periodic review and spot testsSupervisor’s power to require remedial action

Supervisor home/host co-operation

Governance and management controls and processes are the overriding preoccupation; these include controls, roles, ownership of data and independent validation. The document draws a rather arbitrary distinction between aggregation and reporting. Aggregation is the gathering all the data together which is a pre-requisite for reporting – providing useful, relevant summaries to inform better decisions. Supervisory review describes the supervisors’ powers to do periodic assessments (including spot tests) and to require the banks to perform remedial action if they fail these tests.

These are several themes that resonate through the document. There are many references to ensure risk reporting not only works well in normal circumstances but also in a crisis situation – clearly this reflects the experience of the 2007 crisis.

The board and senior management must be aware and in control of all aspects of risk reporting. They must define the information they need and must be aware of the weaknesses and omissions in the reports they receive. This holds true even if they delegate parts of the process to third parties. The buck stops with the board.

The document states the ‘what’ but not the ‘how’. There are very few hard and fast rules on how the guidelines should be implemented. The only occasions when the guidelines are explicit are to state that banks must be able to report certain risks intra-day in a crisis situation and to stipulate the minimum content for a risk report. But for the rest, well it depends on what’s appropriate in the firm’s situation.

The BCBS prefer automation of risk data processes. They have a strong suspicion of any manual process – these should be the temporary (and well documented and independently validated) exception rather than the rule.

“Forward looking” reports are encouraged. These include stress test and scenario-based reports.

The entire BCBS239 can be read from start to end in an hour – it is less than 30 pages. For those in hurry, I would suggest starting with Annex 1 which very clearly defines the terms – so you appreciate BSBS’s distinction between key terms e.g. the difference between accuracy and precision or between completeness and comprehensiveness. Annex 2 is a summary of the 14 principles. For most people, this will be enough but for more detail, the initial sections cover the motivation behind these regulations, the context and then more background to the principles.

BCBS 239 reflects a traditional reporting situation – it assumes that senior management receives their risk data as canned reports – hence their distinction between aggregation and reporting. It assumes a world where the summary risk data is selected as communicated to the board by risk management and risk IT. It does not consider the emerging world of selfreliant and self-service reporting where risk managers have good tools to visually explore and analyse the data. This is probably a reflection of the current status quo in most of the banks.

In summary, BCBS 239 is a very useful document and not only for those firms for which the regulations apply. It provides well thought-out, well drafted guidelines with implications for all processes involved in ensuring that the board of a firm can take well informed decisions about the management of their financial risk.