Stress Test Results – A Classic Visualization Challange

The results of the “Stress Test – Housing Crisis of Shame” are out. I just saw NY Times publish the stress test figures this morning. The first thing that came to my mind when I saw the results was, well…….not that they require another $75 billion, that’s pretty well known to most of us by now, was ….gosh…..what an opportunity wasted. Here was a important and relevant piece of data that could have really been presented better using a graphic….and NYT botched the chance. Now where they feared to tread, I go fearlessly….

So here’s the data:

US stress test results in a table

To get a copy of the stress stress results tabulated in excel click here.

I’ve taken only the most important figures and left out some of the others. In essence we have three data points – The asset base of a list of companies, their additional capital requirement and their projected losses should the economy worsen as per given indicators.

The dilemma here is to represent these numbers is a manner which is easy to understand (if you don’t get everything in 30 seconds, you’re out) and follows good chart design principles (show important data not everything, show analysis rather than plain data, simplification etc.)

I took a shot at this. My first attempt had two of the more important figures – the additional capital requirement and asset base. (Why – well the first one is of the utmost concern but the second one shows what asset base they have and hence, in an oblique way, indicating their capital adequacy and by corollary, risk of failure)

Limitations of this approach
1. Due to sheer difference in magnitude of two data points, you can’t represent both data points on the same axis so the user has to toggle between axis to get an idea.
2. It omits another additional data point, which is the projected losses if the economy worsens.

So I included the third data series and this is what came up:

Limitations of this approach
1. It’s cluttered. Too many data points and no information value.

So we take another try at it and come up with:

Limitations of this approach
1. Though it does manage to do a half decent job, the devil is in the details. If most data points cluster around the same X-Y region, you really have more of a swarm rather than a bubble chart.

How do you overcome that – well let’s move the axis onto a logarithmic scale. Ouch ! The log scale is no good when it comes to representing negative values or zeros. So we compromise and set the minimum value to 1. But even with that, as you can see, it’s difficult to make out what’s what.

Ok so we go back and here’s another attempt:

Limitations of this approach
1. If you really wanted to know which financial institution needs capital most urgently, you’ll not really be able to calculate that quickly. (This is an important aspect of this entire exercise). One thing that can be done is to plot the projected losses and additional capital requirements as a % of asset size and hence eliminate the need to calculate it. Which yields this:

What’s wrong with this, well nothing apart from the fact that if you wanted to blame the chief executive of the largest recipient of federal aid, you’ll have to spend some time to know which one is it !!!

Data visualization experts….SOS

Excel Formula, Excel Chart, Excel Macro, Excel VBA, Pivot Table Excel, Excel Dashboard

What Do You Think ?

XHTML: You can use these tags: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

Comments and Trackbacks

  1. nkowk wrote:

    Very Good Stuff. Please add me as subscriber


Keep up with the latest stories - Delivered right to your inbox


English flagItalian flagKorean flagChinese (Simplified) flagPortuguese flagGerman flagFrench flagSpanish flagJapanese flagArabic flagRussian flagDutch flagHindi flagFilipino flagIndonesian flagThai flagTurkish flagPersian flag
treeemap software for excel