Visualize financial data geographically
Written by Jason on August 17, 2010We’re always adding new datasets, and today we’ll highlight one of our most recent – a list of US bank failures (which is always kept updated, of course). Rather than listing the banks in a table, we’ll use an intensity map to quickly determine where the problem banks have been headquartered.
We’re going to show the results in this example by their aggregate dollar size in three different categories, but you could easily replicate this analysis to show them by number, or over a different time period, or in almost innumerable other ways.
With the tab interface at the top of the map, we can quickly look through to see the magnitude of the failures by asset size, deposit base, and estimated cost to the FDIC. California and Nevada are clearly the states with the largest bank failures by size.
This isn’t to say however, that the chart will always tell the whole story. If we looked at the results in a table, we would see that Washington Mutual dominates the assets and deposits – it accounts for 25% of the total assets and 20% of the total deposits, while IndyMac was the largest single loss and was 2.2 times the size of the second largest. We’ll demonstrate one way to deal with outliers in a followup post examining time series analysis.
One of the things that we think makes Tuhunga different is its ability to keep reports updated automatically. We’ve configured the graph below to be automatically updated every Monday and Tuesday afternoon at 6pm, and any changes in the underlying FDIC data set will be reflected here.
If you’d like to embed this chart on your own web page, you may use the following HTML:
<iframe src="http://app.tuhunga.com/embedchart?keysr=c88083c39a340add" frameborder=0 width=100% height=380px></iframe>
Building this analysis from start to finish takes about five minutes.
If you’d like to recreate this analysis, or build off it, we’ll highlight the key steps here:
1. Subscribe to the “USBankFailures” group (at no cost)
2. Create a filter to capture your desired data – we used:
EffectiveDate>="01/01/05"FailureOrAssistance="FAILURE"(this eliminates the banks that only received assistance)
3. Create a report to prepare your output. We used:
right(Location,2)(this gives us the 2-letter state code from the city, state “Location” field)rollup_sum(TotalAssets,0,right(Location,2))(rolls up all of the assets by state and sums them together)rollup_sum(TotalDeposits,0,right(Location,2))(same, but for deposits)rollup_sum(EstimatedLoss,0,right(Location,2))(again, for estimated losses)
4. Summarize the report by state – check the summarize output box and select the first line of the report
5. Graph the output and select Intensity Map as the type. Use the first line of the report as the “Location” parameter, and select the other three items to be data series in the graph. You will want to click on the “Options” tab of the settings window and set the map region to “USA (states)”.
6. That’s it. Select your filter and report, and generate the output.
To configure your analysis to be automatically updated, please see Tutorial #3, Part II, which walks through many of the automated report generation options available.
