2011 Portland Metro Affordable Housing Inventory Excel File and More.

Metro staff has previously sent me a zip file with the data that I used to prepare my recent analysis [HERE] of problems and suggestive correction actions to their 2011 Regional Inventory of Regulated Affordable Housing report [HERE].  

Kudos to Metro staff for providing that data so readily.

Excel File I Created Has Merged Data in One Worksheet
Using data from three separate data sources in the Metro zip file, I created an Excel workbook HERE. In addition to the merged data worksheet, I added a pivot table and two county level summary worksheets, as well as a worksheet that shows the complete field list for the merged data. When you open the Excel workbook take a look at the READ ME worksheet for more background on the data in the workbook.  

The graph below is from the "County Shares by Income" worksheet in the workbook, and shows that in 2011 in the 3 County Portland metro area, Multnomah County had 47% of all the housing stock, but 82% of the regulated affordable housing units available to households below 50% of median family income. 

I have recommended that Metro provide a merged Excel worksheet similar to the one I prepared to make it easier for the public to explore this important data.

MORE Data Options Including MS Access and Map Files: 
For those of you who want more and the raw data to compile your own analysis, I have also uploaded HERE, the zip file of ALL data that Metro staff sent to me. 

It contains PDF files, map files, MS Access files, and a CSV file. The MS Access file was the source of two 2011 tables that I merged with the CSV census track block FIPS ID file , and it also includes some 2007 tables and a series of queries that may be of interest to some.

Excel Downloading Tip-This workbook was created in Excel 2007/2010 format. Some users report they cannot direct view Excel files in this format from within their browser and/or that Excel files they save end up with a compressed .zip file extension.

My suggestion is to RIGHT CLICK and save the file to your PC. Then navigate to the file you downloaded and look at its file extension. IF it appears as .ZIP extension, change the .ZIP extension to an Excel 2007/2010 extension (.xlsx), and THEN open the file with Excel 2007/2010.

Originally created and posted on the Oregon Housing Blog.

Success! FHFA Posts Annual GSE SF Data In MS Access Format for the First Time.

Sometimes at HUD and writing this blog it has felt like I "plow the ocean", doing a lot but never quite sure what progress I've made. 

Fortunately, there are a few times when the results of my work are exactly what I hoped they would be.

I am VERY pleased to report that with critical help from Senator Merkley's Senior Advisor Will White, FHFA has agreed to post, for the FIRST time, their annual single family loan level database in MS Access format. 

This is a step that can greatly increase public understanding of who benefits geographically and demographically from GSE single family lending and I think is a proverbial "Win-Win" for all involved. 

(Readers will recall that, with the help of my friend and fellow HUD Oregon Field Office Director retiree Roberta Ando, I was able to put together a MS Access database of GSE CY 2010 loans and a series of related "Picture of GSE Assisted Households, CY 2010 " reports to demonstrate the value of getting GSE SF data into a MS Access format). 

The FHFA CY 2010 GSE MS Access database (4.8+ million records) is already posted on their website HERE (look under "Single Family Census Tract File"). 

FHFA has also committed to posting the GSE CY 2011 database in MS Access format in September of this year.  

Thanks much to Will, Senator Merkley, Roberta, and to FHFA staff for their help and responsiveness in making this important information more transparent to the public. 

Before You Download the MS Database......
My prior caveat that you have a PC capable of dealing with a LARGE database remains.  FHFA has combined both Fannie and Freddie data into a file that expands to 1.6 GB MS Access file when unzipped, so you WILL need a PC with a fast processor and LOTS of memory to work with this file. 

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.