Sunshine Week Notes My GSE Posts.

I was pleased to see that the national Sunshine Week web site has noted my GSE posts this week in a Special Reports section of their website:

A New Database on $1 Trillion in Housing Loans
Tom Cusack, the retired director of the HUD field office in Oregon, has published a series of Sunshine Week posts on his Oregon Housing Blog that highlight the geographic and demographic distribution of 4.8 million government sponsored housing loans totaling  $1 trillion purchased by Fannie Mae and Freddie Mac.

My GSE CY 2010 Links (Also in Right Pane)

Picture of GSE Assisted Households CY 2010 (PDF)

GSE CY 2010 Excel Files

GSE CY 2010 Access Databases

To see other Sunshine Week reporting go to their reading room HERE.

Originally created and posted on the Oregon Housing Blog.

Sunshine Week Capstone: Posting of Large GSE MS Access Databases Covering $1 Trillion in CY 2010 Loans.

Readers will recall that I have previously posted [to date] 10 tables in a PDF file titled A Picture of GSE Assisted Households, CY 2010 and also [to date] 4 GSE related Excel workbooks in an on line SpiderOak data storage folder.

To cap off my national Sunshine Week posts I am pleased to announce today that I have created a new on line SpiderOak data storage folder that contains the source data for all of my recent GSE posts-- two VERY large GSE MS Access databases (As I have noted before those databases would not have been possible without the assistance of my friend (and also a retired HUD Oregon Field Office Director) Roberta Ando). 

Caution--BEFORE You Download these MS Access Databases

These MS Access databases are VERY LARGE [Freddie's is 383 MB's and Fannie's 588 MB's]. I recommend that you do not download them unless you have a broadband connection, AND a fast PC, AND substantial memory/RAM and disk space. (I use a notebook PC with 8GB of memory and an Intel Core i7 processor and working with these files maxes out my system).

How to Download These MS Access Databases

1. The MS Access databases and other related documents can be found in a folder located on a cloud storage site, SpiderOak.
2. The web page  for that folder is HERE.
3. I have also added a link to this folder in the right pane of my blog; look for GSE CY 2010 Access Databases.
4. The folder contains two MS Access files (one for each GSE), and two PDFs; AN IMPORTANT READ ME PDF, and a PDF data dictionary that has codes for  the 39 data fields in each record.
5. The initial view of this folder/web page will allow you to click on the “download” radio button to the right to download both databases and a “READ ME” files as a compressed  zip file [ I DO NOT recommend this method as I encounter errors in the size of the downloaded file and in trying to open the file]. 
6. INSTEAD I recommend that you A. Left click on the folder name on the left side of the page  to open the folder and look for the file name that is of interest to you [I would select the READ ME file first].  B. Then on the right side of the web page, left click the “download” radio button  for each file to download that file in an uncompressed format.
7. After downloading the file(s), navigate to the directory where you downloaded the file and double click the file to open. [To conserve memory, before you try to open one of these MS Access databases I would recommend that you shut other programs and perhaps also your browser].

A Similar Cloud Folder Has (Smaller) MS Excel Files for Downloading

I have also previously posted links to a second cloud folder similar to the one above that contains only MS Excel files that I have created using the MS Access databases as a source. 

Files in this MS Excel folder are substantially smaller than the MS Access files because they focus  on subsets of data in the  MS Access databases, but some are still in the 100 MB range, so don't download them unless you have the PC resources (processor speed and memory) to work with them once downloaded. 

You can use the same How to Download instructions from above; a link to the GSE MS Excel folder is in the right pane of my blog as GSE CY 2010 Excel Files 

Future Annual GSE Transparency Will Require New FHFA Cooperation
FHFA currently publishes CT level loan level GSE data ONLY in text formats, and as a result very little public analysis has been done. These databases demonstrate that it IS possible to create GSE files in formats that are more likely to be used by the general public and that clearly will make the geographic and demographic distribution of GSE loans more transparent.

As I have noted elsewhere,  I will be working with elected officials and FHFA to insure that the CY 2011 GSE data posted in September 2012 is published in these MS Access formats. 

I am offering to share with FHFA free of charge the MS Access databases I created AND and also a small MS Access Data Import  Spec file that could be posted that would allow others to import text files into MS Access (this would eliminate need for FHFA to directly post files in MS Access format; users would use the Data Import Spec to correctly import the text file into MS Access).

[None of the GSE Access, Excel or PDF files would have been possible without the help of my friend, and fellow retired HUD Oregon HUD Office Director, Roberta Ando).

Originally created and posted on the Oregon Housing Blog.

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.

New Table Added to Picture of GSE Assisted Households CY 2010: $22.5 Billion in Loans to 117,000+ African Americans.

I have added a new table to the GSE PDF table series, with a link in the right pane, Picture of GSE Assisted Households, CY 2010.

Table 10 shows a count of loans and $$ by state, ranked by the share of all single family GSE loans made to African American borrowers or co borrowers
 

Some observations:

1. Oregon ranked 42nd in the share of all GSE loans in the state that went to African Americans.
2. Among top 20 states in share of loans to African Americans only two (Nevada and Texas ) were west of the Mississippi river.
3. In Michigan and California, states with substantial African American populations (6.67% and 14.27%), only 1.6% of GSE loans went to African Americans. [Wikipedia state African American population table is HERE].
4. GSE CY 2010 loans to investors [prior post] at $40.2 billion/243,000 loans were substantially higher than $22.5 billion/117,000 loans to African Americans.

Note: 

In this table and in my coming Excel workbook "African American borrowers" includes all loans where an African American was borrower AND all loans where African American was a co borrower (even if borrower was not African American). 

Excel Workbook Coming  
A scheduled posting later will include a link to my Excel cloud space folder where the complete Excel workbook with loan level records for African American borrowers and co borrowers will be uploaded so users may do further analysis.

Originally created and posted on the Oregon Housing Blog.

In CY 2010 GSE's Purchased 8,100 Loans Made to Millionaires.

I have put together an Excel workbook with loan level detail for $3 billion/8,139 loans purchased by the GSE's in 2010 that were to borrowers with incomes of $1 million or more.  

( I have also added the state level summary of loans to Millionaires to my PDF compilation in the right pane, Picture of GSE Assisted Households, CY 2010). Look for Table 9.  

Millionaires make up a VERY small percentage (2/10ths of 1%) of the more than 4.8 million loans purchased by the GSE's in CY 2010 and a substantial number of these loans could have been refinances of existing loans (users can research that more closely using this Excel workbook). 

In Oregon in CY 2010 there were a total of 104 GSE loans that went to millionaires, totaling $25.8 million; this represented only 1/10th of 1 percent of all loans in Oregon.

How to Download GSE 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.

GSE Loans Excel Workbook 3: GSE Loans to Millionaires [3.7 MB]

The data in this Excel workbook consists of $3 billion/8,139 loans purchased by the GSE’s during CY 2010 where borrower income of $1 million or more was reported.  The GSE CY 2010 data dictionary with all 39 field names and values can be found HERE:

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”.  [For this workbook only I added one additional column that shows the ratio of borrower annual income to the median income for the census tract where the loan was purchased].
 

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 millionaires. Users can change the fields displayed in the pivot table to retrieve any combination of data using the data fields available.  A summary worksheet in the workbook includes a ranked order by state of the % of all GSE loans that went to millionaires.

NOTE:
Loan records in this workbook are limited to ONLY borrowers with incomes of $1 million or more.

Originally created and posted on the Oregon Housing Blog.