Updated: First Evah Oregon Database of 40,000+ Current HAMP Records.

Update: I added the data dictionary worksheet to the smaller Excel file below, link stays the same.
------
For several months the Treasury has been releasing loan level data on HAMP loan modifications. Frankly it is a HUGE pain to deal with this data but on a one time basis I successfully extracted Oregon data for more than 40,000 HAMP records (applications and loans). (Data is of March 2011, new data is scheduled for release this Friday if you are a glutton for punishment).

The Oregon data I extracted is in an Excel workbook HERE. It is a very LARGE file (334 MB's) and your PC will require significant resources to open; even with my 8 GB memory notebook opening the file takes a long time after file has been downloaded. (see also Excel downloading tip at bottom of this post). 

If you want a MUCH smaller file, I have uploaded JUST the 1st worksheet with all 40,294 records HERE; it is just 25 MB's (In update I added second worksheet, the data dictionary). This might be better option for many, as once downloaded you can add filters, sort, or add pivot table to extract data of interest.

The full Excel file has a total of 6 worksheets, the number of records in each worksheet is shown in parenthesis.

1. All Oregon HAMP Records (40,294 records)
2. All Active Payment Records (7,629 records)
3. PIVOT All Active Payment Records (7,629 records)
4. All records with NPV data (5,478 records) [These are all active payment records].
5. Summary Active Loan Savings; this worksheet shows the total and average monthly and annual savings by metro area. (Because Oregon only Portland PMSA data cannot be isolated, the statewide total for Oregon is somewhat inflated by the inclusion of Clark County Wa. HAMP activity).
6. Data Dictionary. This includes names for each of the 148 date fields in the merged loan modification and net present value files.

Observations on the Summary Worksheet

1. For Active Payment loans

• The average monthly savings from active payment HAMP loans in Oregon is $563 per family; that's an average of $6,756 on an annual basis. Total annual savings from Oregon active payment HAMP loans is $36.994 million. (This is NOT cost to government, gov pays only 50% of cost to get family to 31% front ratio, plus incentives for continued timely payments).

• Highest average monthly savings were in Bend at $650, lowest in Salem at $464.

The summary worksheet I have include only begins to scratch the surface of the Oregon data available in the HAMP database; I encourage more in depth analysis from others. Treasury could make data a LOT easier to use by posting state level CSV files that combined the two data files and by posting data field descriptive names as a CSV file. 

To help others understand how I put together my workbook I have outlined the process I used below.

Process Used to Create Workbook.

1. I copied data field names from Treasury PDF file, for 148 data fields, and downloaded two .CSV files from Treasury website HERE. 
2. In loan mod file, Treasury combines data for several states in one file so I had to extract Oregon MSA data AND Oregon data for loans without MSA's. NPV file did not contain geographic fields so using the Financial Asset ID Number field I had to merge with loan mod file to link NPV data to loan mod data.
3. Using new Oregon loan mod file I added lookup formulas to find values for all Oregon loan mods where NPV data was available. (This took a long long time because formulas had to search for data in 40+ NPV data fields for each of the 40,000+ Oregon HAMP records). (Linking an Oregon only loan mod table with NPV table using Financial Asset ID Number field in MS Access might accomplish same task more efficiently).

Downloading Tip-This workbook was created in Excel 2007 format. Some users report they cannot direct view Excel files in this format from within their browser and that Excel files they save end up with a compressed .zip file extension. My suggestion is to RIGHT CLICK and save the file to your PC. Then navigate to the file you downloaded and look at its file extension. IF it appears as .ZIP extension, change the .ZIP extension to an Excel 2007 extension (.xlsx), and THEN open the file with Excel 2007/2010

Originally created and posted on the Oregon Housing Blog.