In time for the OHCS Housing Conference this week I have put together an Excel workbook HERE with HMDA data for all Oregon MSA's for 2010 [Database also includes Clark and Skamania counties in Washington state as they are in Portland MSA].
This Excel 2007/2010 file includes data on 233,512 Oregon residential loan applications in 2010. (NOTE: File is large at 100+ MB's; that's because it contains 71 fields for each loan application/record -more than 16.5 MILLON pieces of data.
To download the file do NOT attempt to open file within the browser but instead right click file link and save to your PC; be patient as it will take a few minutes to download. A PC with lots of memory and a higher processor speed is recommended.
This database starts with 46 fields of HMDA 2010 data that I downloaded for each Oregon MSA from FFIEC. I then added 25 fields of data to the right of the HMDA data that substitute NAMES values for CODE values that appear in several HMDA fields. [The code sheet for Loan Application Register data from FFIEC is HERE].
Example: HMDA data uses a minority race code of "3" for African American applicants, I added a NAME race code field that that shows "African American".
I ALSO merged the HMDA Oregon MSA data with several fields from the "Reporter" HMDA database to add NAMES to codes used to show which lender reported the data, where the lender was located etc.
The end result is a database that shows at census tract, county, MSA, and state levels:
This Excel 2007/2010 file includes data on 233,512 Oregon residential loan applications in 2010. (NOTE: File is large at 100+ MB's; that's because it contains 71 fields for each loan application/record -more than 16.5 MILLON pieces of data.
To download the file do NOT attempt to open file within the browser but instead right click file link and save to your PC; be patient as it will take a few minutes to download. A PC with lots of memory and a higher processor speed is recommended.
This database starts with 46 fields of HMDA 2010 data that I downloaded for each Oregon MSA from FFIEC. I then added 25 fields of data to the right of the HMDA data that substitute NAMES values for CODE values that appear in several HMDA fields. [The code sheet for Loan Application Register data from FFIEC is HERE].
Example: HMDA data uses a minority race code of "3" for African American applicants, I added a NAME race code field that that shows "African American".
I ALSO merged the HMDA Oregon MSA data with several fields from the "Reporter" HMDA database to add NAMES to codes used to show which lender reported the data, where the lender was located etc.
The end result is a database that shows at census tract, county, MSA, and state levels:
- Demographic data for each loan applicant, including income, race, ethnicity, and sex.
- Demographic data for the census tract and metro area for the property.
- The loan type [FHA etc], purpose [purchase/refinance etc], loan amount/spread.
- The outcome of each loan application and reasons for denial.
- The lender who reported the loan application result.
As a tease of what's in the workbook the legal sized PDF HERE [worksheet 3 in the workbook] shows for Oregon only, owner occupied home purchase originations by loan type [FHA etc] by ethnicity and then by race. The table shows :
To go deeper I have provided a PIVOT table in the workbook allowing users to EASILY slice and dice the HMDA data in countless ways.
- That 61.6% of owner occupied home purchase loan originations for Hispanics were FHA loans, compared to an FHA share of owner occupied home purchase orginations to non Hispanics of 39.8%.
- That FHA had 61.9% of the owner occupied home purchase African American loan originations, but only 41.3% of owner occupied home purchase white loan originations
To go deeper I have provided a PIVOT table in the workbook allowing users to EASILY slice and dice the HMDA data in countless ways.
The Pivot default in the workbook is set for Oregon ONLY to retrieve by borrower race, the number of Owner occupied, Home Purchase Principle Residence Loan Originations by loan program (FHA, VA, Conventional, RD).
A short summary of some of the additional data the Pivot table can reveal:
Subtotal, OREGON Only Owner Occupied Loan Originations Summary, Excluding Manufactured Housing:
Subtotal, Oregon Only Manufactured Home Loan Originations
Subtotal, Washington State ONLY(Clark County+Skamania County) [Skamania county loans have a Washington State Code and a "59" county code or a "N/A" code in the County Name Field].
A short summary of some of the additional data the Pivot table can reveal:
Subtotal, OREGON Only Owner Occupied Loan Originations Summary, Excluding Manufactured Housing:
- 23,334 Owner Occupied Principle Residence Home Purchase Loan Originations
- 64,128 Owner Occupied Principle Residence Home Refinance Originations
Subtotal, Oregon Only Manufactured Home Loan Originations
- 1,599 Manufactured Home Loan Originations; of those only 576 were Owner Occupied Principle Residence Home Purchase Loan Originations.
Subtotal, Washington State ONLY(Clark County+Skamania County) [Skamania county loans have a Washington State Code and a "59" county code or a "N/A" code in the County Name Field].
- Excluding Manufactured Housing, 30,276 Loan Applications.
- 450 Manufactured Housing Loan Applications.
While these are STATE level examples, similar data can be retrieved at the census tract, county, and MSA levels. (Database doesn't include a city data field, but CT data could be combined with other data sources to arrive at summaries at the City level).
The complete list of worksheets in the workbook :
READ ME
1 Pivot
2 Oregon MSA HMDA Data 2010
3 OO Home Purchase Race OR
4 Race Ethnic by County
5. Race Eth Originations Cnty
6. 2010 Reporter
Originally created and posted on the Oregon Housing Blog.
No comments:
Post a Comment