Thursday, March 15, 2012

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.

No comments:

Post a Comment