Wednesday, March 14, 2012

New GSE Excel Post: Details for 117,000 Loans to African American Borrowers.

I have put together a new GSE Excel workbook with loan level details for $22.529 Billion in CY 2010 GSE acquired loans for 117,524 African American Borrowers and Co Borrowers. 

How to Download These GSE Excel Workbooks
  1. All of these GSE related Excel workbooks 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.
GSE Loans Excel Workbook 4: GSE Loans to African Americans [47 MB]
The data in this Excel workbook consists of data for 117,524/$22.529 billion in single family loans purchased by the GSE’s during CY 2010 where the borrower OR Co borrower was an African American.  
The GSE CY 2010 data dictionary with all 39 field names and values can be found HERE (and has also been included in the SpiderOak Excel folder).

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”. 
This workbook also includes a pivot table, allowing users to focus on geographies or demographics of interest; the default view is for a count of all loans by state showing by GSE the number of loans to African Americans. Users can change the fields displayed in the pivot table to retrieve any combination of data using the data fields available. 
The workbook also includes a state summary of the share of all loans that went to African Americans, in ranked order by the percentage of all loans to African Americans. (This state level African American loan summary also appears as Table 10 in the PDF file with my series of GSE CY 2010 tables, Picture of GSE Assisted Households, CY 2010
Finally, this workbook contains a second detailed state worksheet summary of African American loans counts by GSE and by borrower/co borrower.
Originally created and posted on the Oregon Housing Blog.

No comments:

Post a Comment