NICAR Database Library

Showing the big picture around a story sometimes takes more than an interview. This is especially the case when it comes to the U.S. housing crisis, where federal data can be a useful resource for reporters.
Home Mortgage Disclosure Act data, recently updated available through the IRE-NICAR Database Library, can show, on the state and even the Census tract level, the number of subprime loans in a community – who received those loans (at least, their race, gender and ethnicity) and for how much.
Such information is ripe for a dynamic mapping project, and there are many different ways you can illustrate your housing story.
For example, the Orange County Register’s Ronald Campbell used HMDA data to show the “subprime penetration rate” -- or the number of subprime loans compared to the total number of loans, he said.
“While subprime comprised less than a quarter of total US home loan volume in 2005, its peak year, there are some big counties (including Miami-Dade, FL, and San Bernardino, CA) where subprime was upwards of 35 [percent] of total volume,” Campbell wrote in an email.
Jack Gillum, a business reporter at the Arizona Daily Star, said his newspaper took the “HMDA data and overlaid percentage of subprime loans to total loans per” census tract in order to produce a Google Map for the Star’s Web site.
Robert Gebeloff, of the New York Times, used the data like this while at the Newark Star Ledger.
Home loan applications have decreased more than 25 percent in at least nine states since 2004, according to the latest federal housing data.
The drop in applications in states, such as California, Nevada, Michigan and Minnesota, is a result of bankers shying away from practices that gave way to the housing boom in previous years, according to Reuters.
To illustrate how this data can be used, we here at NICAR drew up a simple map.
Here’s how we did it:
First, you’ll need a suitable shapefile of the U.S. from census.gov's TIGER files. An ESRI Shapefile describes the data that it relates to, in this case the HMDA data we will join to it. In Arcmap a shapefile looks like a geographic location (in this case, the U.S.).
Second, after downloading and unzipping the NICAR's copy of the 2004 and 2007 HMDA files from data.nicar.org, I move those into Foxpro. After opening a command window I run a query, which should look something like this:

Select state, count(*) from table where Spread is Null And Loan Type = "1" and Occupancy = "1" group by state 

This query gives the number of owner occupied, conventional loan applications in each state. This is a simple example, and there’s room for more filtering. You can, for example, include subprime loans, or even loans that were not approved.
Then, move the data into a CSV or comma-separated values file, which you can import into Excel.
In Foxpro, the command looks something like this:

 Copy to NAME OF LOCATION type CSV 

Now, after you have run that command on each of the 4 tables in the 2004 and 2007 sets of the NICAR data, open it in Excel. From here you can copy and paste all your queries into one set of table. Put your state fields in an ascending order.
There may be repeats in the state field -- which are represented by numbers – if a state had so many applications that it takes up two tables. If there are, just combine those counts to get one count in each state field in Excel.
Write an Excel formula to create a field for percent change

=(A2-B2)/B2

Now, it’s time for some mapping.
Save your data as a DBF file. It should now be open in Excel into a Dbase file format that you can open in Arcmap.
After you've opened Arcmap, open your shapefile’s attribute table that you found on census.gov, and join the FIPS state field to your data's state field.
Go into properties of that now joined U.S. base shape file, and then enter to symbology tab and quantify how you'd like the percent change of each respective state to be colored.
The result should look something like we've posted.