MS Excel: Loan Level Detail for $17+ Billion in CY 2010 Oregon GSE Loans.

Up until now all of my posts on GSE CY 2010 data have been PDF files compiled into the PDF file linked in the right pane as Picture of GSE Assisted Households.

Today, I am beginning the posting of several GSE CY 2010 MS Excel workbooks that zero in on different geographies or areas of demographic interest. I believe this is the only place on the web where you will find GSE CY 2010 data with this level of individual loan detail, including loans in both rural and metro areas. [Kudos again to Roberta Ando for helping me put together the MS Access databases that were used to extract this data].

How to Download the Excel Workbooks

1. All of these Excel posts are included in a folder I have created on a cloud data sharing service, SpiderOak.  A link to that folder is HERE and it has also been added to the right pane as GSE CY 2010 Excel Files.[Ask me sometime about what a pain it was to find a web site that can host large file sizes].
2. The link above will open a web page.
3. From that web page you will see a “download” radio file on the right side that is supposed to allow you to download all Excel files in this folder as a single compressed file-I DO NOT recommend this method as I encounter errors in the size of the downloaded compressed file and in trying to open the file. 
4. INSTEAD you should A. Left mouse click on the underlined folder name on the LEFT side of the page [GSE 2010 Public Shared Excel]  to open the folder AND THEN B. Click the “download” radio button on the right side for that file to download each file individually in an uncompressed format.
5. After downloading the file(s), navigate to the directory where you downloaded the file and double click to open. 

First Excel Workbook: GSE Loans Excel Workbook 1: All Oregon GSE Loans [29.3MB] 

This workbook includes loan level data on 84,000+ loans purchased by Fannie Mae and Freddie Mac in Oregon during CY 2010; The unpaid principal balance at acquisition for these loans was more than $17.1 billion.

In addition, to make the workbook easier to use, I created lookup formulas to add fields with NAMES for 8 of the 39 data elements; those additional columns begin at column “AN” of the Oregon CY 2010 GSE Data worksheet. These include columns with the county and MSA names, names for race and ethnicity, a name for the purpose of the loan, and a column that places the ratio of borrower income to median area income in one of 5 categories /“bins”.

The file includes a data file, a pivot table and READ ME sheet.The pivot table allows users to focus on geographies or demographics of interest; the default view is for a count of all loans by county for both GSE’s, broken out by income grouping in columns. Users can change the fields displayed in the pivot table to retrieve any combination of data using the data fields available.  

Sample questions the pivot table can answer:

1. How many loans were purchased by the GSE's in X CT, county, MSA and what percent went to borrowers with incomes below 100% of AMI? [Add race, ethnicity, gender breakouts].
2. How many investor loans were purchased and what were the borrower incomes?
3. How many home purchase vs refinance loans?
4. What was the average unpaid principal loan balance for different areas, races, ethnicity?
5. How many first time home buyer loans were purchased in CT, county, MSA, state?
6. How many loans were purchased in CT with higher minority %?

Originally created and posted on the Oregon Housing Blog.