Oregon April 2021 Update: FHA Loan Servicer Report Shows 5,547 Seriously Delinquent Loans, A Rate of 8.8%.

 I have compiled the latest end of April 2021 FHA servicing data for Oregon into a workbook with three worksheets HERE and embedded below. 

The second worksheet combines three HUD Neighborhood Watch sub reports into one worksheet that contains a servicing summary, loss mitigation, and loss mitigation incentive claims data. Filters are in place by default so users may select or sort on individual columns to find , for example, the servicer with the largest number of serious delinquencies, the highest serious delinquent rate, etc. 

The FIRST worksheet is a PIVOT table of the data worksheet. The default view in the pivot table are these statewide counts:

• All active loans (63,302)
• 90 day delinquencies (5,547) [5,547/63,302=8.8% serious delinquency rate)
• Foreclosure actions (58)
• Loss mitigation incentive claims paid (2,224)
• Forbearance claims paid (3)

To see information on individual servicers, users can pull down the servicer name in the field at the top of the pivot table or drag it into the Pivot table to see all servicers.

The third worksheet is a vertical listing of all of the data fields in the first worksheet and the HUD Neighborhood Watch sub report from which the data field is associated. 

Note: I had previously downloaded the Oregon March 31st servicing data; the April 30 serious delinquency rate of 8.8% is down from the March rate of 9.1%.

<

Detailed Oregon Building Permit Data 1997-2018.

With efforts to increase housing supply on the minds of many I thought it would be useful frame of reference to find and post consolidated historical AND current Oregon building permit data.

Historical Data:
The Excel workbook I created and posted HERE includes a breakout of monthly final building permits for all Oregon jurisdictions from 1997-2017 and a statewide total. The data is from a HUD MS Access database, to which I added the county name and jurisdiction name (instead of just codes). 

The workbook includes a pivot table that in default view lists all the jurisdictions and displays permit counts by category: ALL, SF, MF, 1-2 units, 3-4 units, and 5+ units. Pull downs at the top allow selection of years and months. 

Current Data:
The link HERE will take you to a web page that shows the most up to date preliminary permit data for 2018 for all Oregon permitting jurisdictions and a statewide total. August 2018 data is the most recent data available, but using the link in the future should include the most recent month available. 

The link HERE has the same preliminary 2018 data by month, but will produce a CSV download that will make it easier to filter, add pivot tables , and do other data analysis. 

(Note: The two 2018 links above may take a while to load, so be patient).

Originally created and posted on the Oregon Housing Blog. 

2008-2012: All State and Metro Real Personal Income and Per Capita Income Data in Excel.

Two recent posts referenced calculations of state and metro real per capita income calculations that I had done for 2008-2012. Those posts included images of two summary tables. 

I have combined all of the related state and metro data into a single Excel workbook HERE (and embedded below) that also includes links to the BEA data that I downloaded.  

The workbook opens to a index page showing all the worksheets in the workbook. (The two worksheets highlighted in red are the summary tables that I posted as pictures in the prior posts).

Posting of this workbook allows other to verify my calculations and do other calculations of interest. 


Originally created and posted on the Oregon Housing Blog.

CY 2010-CY 2012 Data Trifecta: Census Tract Level GSE SF Loan Data; $50 Billion in Loans to 252,000 Oregon Families.

I have prepared a one of a kind 95MB Excel workbook HERE that contains census tract loan level data for $50 Billion/ 252,000+ Oregon single family loans acquired by the GSE's in Oregon during CY 2012, CY 2011, and CY 2010.(I extracted this data from FHFA public use databases for these years).

The data worksheet has columns I have added to FHFA data to show names instead of codes for several selected fields including borrower and co-borrower race and ethnicity, loan purpose, and borrower income ranges; with 48 fields of data for each record, there are more than 12 million pieces of data available for these 3 years . 

A complete list of all fields in the data downloaded from FHFA (a data dictionary) for 2012 is HERE, 2011 HERE, and 2010 HERE [ Census tract definitions, minority percentages, and median incomes may vary from year to year as newer information became available]. 

Using filters and the pivot table in the Excel workbook users can research answers to questions like these:
1. How many loans were acquired that were made to purchase a home where there was a first time Hispanic Borrower or Co-borrower, and where were those loan made (A similar analysis is possible by race).
2. How many/where were loans acquired for borrowers with incomes below 80% MFI? 
3. How many /where were loans acquired that had been made to investors, and where were those loans? 
4. Who got high cost loans and where were they? 
5. How did refinance loan volume change from year to year?

Note that unlike HMDA data, the GSE data covers ALL areas of the state, not just metro areas, and also includes a first time home buyer field not found in HMDA data.

The PDF file HERE and embedded below summarizes by CY and by county and by year the unpaid loan balance at the time of GSE acquisition and the count of loans.



Originally created and posted on the Oregon Housing Blog. 

Picture of HUD Subsidized Households in Oregon , 2012.

I have combined all 2012 Oregon data from newly released HUD Picture of Subsidized Households into a single Excel workbook HERE. (Data I extracted is supposed to use 2010 Census geographies, but I have not verified).

Read the code book HERE to fully understand the values in the data fields, and data limitations. 

Data available at the State, Metro, PHA, County, Place,  Census Tract AND project level. Demographic breakouts are available for vouchers, public housing, and project based households.  Unit counts only available for LIHTC assisted households.

HIGHLY, HIGHLY Recommended.

Some quick OREGON 2012 demographic highlights: 

1. Households headed by women make up 72% of all HUD subsidized households; 32% of HUD subsidized households are headed by women with children.
2. Disabled households make up 45% of all HUD subsidized households. 
3. The elderly make up 29% of all HUD subsidized households. 
4. Minorities make up 24% of all HUD subsidized households.
5. HUD estimates there were 52,000+ HUD subsidized households in Oregon in 2012, including 33,720 housing choice vouchers.

Originally created and posted on the Oregon Housing Blog.

March 2013: Cumulative Rate of Oregon Hardest Hit Housing Fund TARP Draw Downs Is Double National Average.

A new SIGTARP report as of March 31st has been published HERE. 

In prior post HERE, I provided a Dec 2012 summary of Hardest Hit Housing Fund spending by state, including an Excel worksheet. That same worksheet is included in the updated Excel file HERE, and embedded below, but the initially visible worksheet is a new one focusing on draw downs as of March 31st. 

Some Observations on March 2013 Spending:

1. Oregon's cumulative drawn down rate of 58% was more than double the national average rate of 27%. 
2. Oregon drew down $20.5 million in the first quarter of 2013, vs a total draw down of $48 million in CY 2012.
3. While Oregon's cumulative draw down ranking slipped from 2nd to 3rd, the areas with higher drawn down rates (Rhode Island and DC) had substantially smaller amounts to spend. 

Originally created and posted on the Oregon Housing Blog.

In Advance of Fair Housing Month: One Easy Thing HUD Could Do to Promote Fair Housing in LIHTC Program.

HUD publishes an annual database of LIHTC projects placed into service HERE. It includes a data field showing the 2010 Census Tract ID as well as the zip code. (Most recent data dictionary is HERE).

ACS 5 Year poverty data is available at the census tract AND zip code level. 

My prior post HERE has an Excel file that matches up Oregon LIHTC projects with ACS 5 Year poverty rates at the census tract level.   

HUD could do a similar match each year to include the most recent poverty rate (and counts) at the zip code AND census tract level for all LIHTC projects in their database.  This would allow users and researchers to parse out the percentages and counts of LIHTC projects and units in a given locality that are located in areas with high or low poverty rates. 

In addition to assisting in evaluating access to opportunity it would also help evaluate housing choice available to voucher holders who are a key eligible population for LIHTC projects. 

Originally created and posted on the Oregon Housing Blog.

  

Graphic in Big O Story on Tax Breaks Omits MID and Property Tax Use by Highest Income Filers.

Last week the Big O published a story about efforts to pare state tax breaks. I appreciated the story, including a narrative discussion about the savings that would occur if the mortgage interest deduction was scaled back. 

However, the story contained a graphic showing tax breaks that primarily benefited the highest income bracket and neither the costs of MID or property tax deductions were included.  

I dug out data from the Oregon Tax Expenditure report and found out that the combined cost of the MID and property tax deductions :

1. Was 4X the combined cost of all other deductions included in the graphic.
2. Went disproportionately (61%) to the top 20% of Oregon tax full time filers. 

My Excel calculations, using 2009 data for MID and property taxes, and 2010 data from the Oregonian table, are HERE and in the embed below:  




While I think the cost multiple of 4 X is likely reduced closer to 3 X if 2010 estimates of MID and property tax costs were used, I consider the absence of the MID and property tax cost to be a serious omission from an otherwise good Big O story.  

I have communicated my concerns to the reporter with a suggested update to include the information that I provided and to 2010 MID and property tax data.

If I were big on conspiratorial theory (I'm not), I might think the absence of the MID and property tax from the graphic was linked to an Oregonian editorial after the story was published that called for no change in the MID. 

Originally created and posted on the Oregon Housing Blog.

Important Stuff: First Update in Decade of Oregon County "Affordable AND Available" Housing Supply by Income Brackets.

NLIHC has recently published statewide profiles of affordable and available housing supply HERE; state level data is found on page 5 . This required major data crunching from NLIHC and I am very appreciative of the amount of work that went into producing this data.

HUD has yet to update their CHAS (Comprehensive Housing Affordability Strategy) data in a user friendly format for 2010, so useable local HUD CHAS data on the supply of affordable and available housing remains stuck in a 2000 time warp. (National and regional "Affordable and available" housing supply is a key component of periodic HUD Worst Case Housing Needs Reports sent to the Congress; the 2009 report is HERE).

Why the concept of "Affordable AND Available" Housing Supply is So Important.

1. The count of a supply of units with market rents that are “affordable” to lower income families does NOT mean that those units are available or occupied by lower income households--a significant share of units with rents affordable to extremely low income and very low income renters are occupied by households with HIGHER incomes. 
2. As income increases, the "affordable AND available" supply of rental housing increases. In many cases there is a gross surplus of units affordable to households at 80% of median income, and only a very small deficit of affordable AND available units at that income level. That is one of the key reasons that subsidies for rental housing are NOT usually targeted at higher income levels.
   
3. For the lowest income families part of the solution is to create/preserve a universe of income restricted affordable housing that is reserved exclusively for use by extremely low income (less than 30% MFI) and very low income renters(less than 50% MFI)---"Project based assistance" of some kind. 
4. The graph pasted below uses 2010 Oregon data to illustrate how affordable AND available supply increases as income increases. In 2010 the Oregon estimate is that there were only 22 affordable AND available units for each 100 extremely low income (less than 30% MFI) renters, vs 95 for each 100 low income (less than 80% MFI) renters.  

PDF and Excel Versions
First Updated Oregon Affordable and Available Housing Supply Data In a Decade:
Included in a related NLIHC Oregon housing profile HERE is a map that includes county level affordable and available housing percentages (Housing profiles for Oregon Congressional Districts from NLIHC are also available HERE). 

Fortunately, I was able to obtain from Neighborhood Partnerships, a state partner of NLIHC, the Excel workbook that was used to create the county map in the NLIHC Oregon housing profile.(Tip of the hat to Janet Byrd, NP XO for providing Excel file within hours of my request).

I am posting the Excel workbook I received from NP with two additional worksheets added:
1. A worksheet with the statewide total from the NLIHC report for Oregon, along with the graph posted above.
2. A statewide and county summary by income group showing, per 100 renter households, the
A. Number/supply of units that would be affordable to that income group.
B. Number/supply of units that would be both "Affordable AND Available".

This second worksheet also includes a series of three graphs that visually show that data for households with incomes below 30%, 50%, and 80% MFI thresholds. (graphs are to the right of the summary table).

The 4 page PDF file I created HERE has only the statewide and county summary table and the three graphs that I created in the second worksheet. 

The Excel file/workbook, which contains the summary and graphs, AND all of the data for Oregon counties from NLIHC and NPF can be viewed/downloaded directly HERE and is also embedded below:



IMPORTANT NOTE: Estimates for Gilliam, Sherman, and Wheeler counties should be used with caution. These are counties with less than 100 renter households in an income category, less than 100 affordable units, or a high margin of error.

Originally created and posted on the Oregon Housing Blog.

Housing Authorities Spend 1,000+ FTE on Screening and Evictions for Criminal Activity.

Tomorrow's Federal Register will have a HUD notice of an information collection that includes the burden hours for PHA's for the screening and eviction of applicants and tenants for criminal activity.  [ Look for "Screening and Eviction for Drug Abuse and Other Criminal Activity"].

The existing reported public housing burden hours are expanded to include the voucher burden hours. The reported activity and burden hours are explained by HUD as: 

The information and collection requirements consist of PHA screening requirements to obtain criminal conviction records from law enforcement agencies to prevent admission of criminals into the public housing and Section 8 programs and to assist in lease enforcement and eviction of those individuals in the public housing and Section 8 programs who engage in criminal activity.

The Excel file embedded below and HERE shows MY calculations that indicate that PHA's : 

• Conduct more than 500,000 screenings for criminal activity and related eviction actions a year. 
• It takes an average of 4.24 hours for each action.
• 2+ Million hours/ 1,019 FTE's are spent each year on this activity. 
• IF FTE cost is $50,000 per year, annual costs for these screenings and evictions exceeds $50 million.

Note that these cost burden estimates do NOT include cost burdens for similar activity conducted for HUD project based Section 8, HUD 202 or HUD 811 programs. 

Originally created and posted on the Oregon Housing Blog. 

A First: US (2.1M) and Oregon (32K) HUD Voucher Data for All Months, CY 2009, CY 2010, and CY 2011.

I have posted to my SkyDrive an Excel workbook that contains all HUD Voucher Management System data for all months in CY 2009, CY 2010, and CY 2011. VMS data include counts of vouchers , including some program breakouts [VASH, Home Ownership etc], along with $$. Data is aggregated to the housing authority level, so no lower geographic breakout is possible.

This is the only multiple year HUD voucher data for the US that I have seen anywhere on the web.  

The workbook contains an important READ ME tab, and pivot tables for both the US and for Oregon, along with the raw data for Oregon and the US. The READ ME tab shows a listing of all data fields. 

The US data worksheet includes both a state field AND a HUD region field that I added, allowing aggregation to those geographic levels via the pivot tables.

You should be able to download this Excel workbook from my Public Excel SkyDrive HERE (File is too big to view in your browser, so it also can't be embedded in this post). [Let me know if you run into any problems, you WILL require Excel 97 or later to edit, and, in advance, I cannot provide in an earlier Excel format because of formulas used). 

I have uploaded the same file to other webspace HERE in case the SkyDrive upload doesn't work for you.

The HUD site with VMS data and reference material is HERE. 

Originally created and posted on the Oregon Housing Blog.

FHFA Data Shows HARP Refinances Above 105% LTV Are Increasing; Oregon and All State Data Available in Excel.

FHFA PR is HERE. From the PR I have constructed an Excel workbook in my public Excel SkyDrive that shows refinance and HARP activity by state, with a detailed breakout of Oregon data.  

Direct link to the Excel file is HERE and it is embedded below. (Worksheet with detailed Oregon data displayed by default; data for all states and link to data source are included in other worksheets in the workbook).

Some Oregon Observations

1. The percent of HARP loans that went to borrowers at LTV 105% and above was 28% in March compared to 10% since the start of HARP in April of 2009.
2. In March there were 515 HARP refinances with LTV above 105%; that is nearly half of the 1,044 HARP refinances with LTV above 105% so far this year and more than 1/6th of ALL HARP refinances above 105% since the inception of the program in April 2009.
3. Total FHFA refinances in March were 8,133; there were 19,252 FHFA refinances so far this year

Originally created and posted on the Oregon Housing Blog.

Final Test Excel Cloud Post: Renter Cost Burdens for 400+ Oregon Places and Counties.

In a previous post HERE I included a link to an Excel file with a lookup that allows users to select from among 400+ Oregon places and counties to find renter cost burden counts and percentages from ACS 2006-2010. 

As this workbook contained multiple formulas and looks, and multiple worksheets, I thought this would be a good  file to test embedding into a blog post after I separated one worksheet into two to make them more visible when posted on a web page.

Lessons Learned 

1. Embed codes for Excel cloud posts to web pages/blogs do not support data validation, so pull downs from a list of valid names cannot be used. For this workbook this means that users can still type in the name of an area in far left hand column, but unless it matches perfectly the area name found in the lookup table, erroneous data will be returned. [You can check an area name in the data worksheet tab if you need to].
2. If you open or download the Excel file from a web posted version (file name has suffix of "editable") data validation has been removed). The Excel file I first uploaded to my public Excel folder on my MS Skydrive  HERE continues to have data validation but you must use the "Open in Excel" tab at top left after this link initially opens the file in your browser (with "unsupported features").
3. Excel cloud posts to web pages do support multiple worksheets, via the tabs at the bottom of the workbook.  
4. Excel cloud posts to web pages do appear to support sophisticated lookup formulas from large data tables.

As before, feedback on your experience viewing and downloading would be appreciated.

Originally created and posted on the Oregon Housing Blog.

Test: Interactive Excel File in the Cloud Hints Calculator Possibilities.

This is a simple demonstration of how interactive Excel files [with formulas and lookups] can now be embedded into web pages/blogs, using data hosted in the cloud.  

What's the Big Deal?
Interactive cloud based Excel files that allow user input and use formulas/lookups could be used to directly post on web pages/blogs housing related calculators-think eligibility calculator, monthly payment calculator, affordable mortgage calculator, and affordable rent and income calculators. The big deal is that anyone with Excel can now post these interactive files/calculators to their web site or blog, without the need for highly sophisticated web programming skills.

The embedded Excel file below 
1. Has you enter a calender date in the column to the left with yellow fill.
2. Uses formulas to then insert the calendar year and two different fiscal years (starting in October 1 and July 1) in the columns to the right. 
3. The formulas used in this lookup can be used with a large data set when you have full calendar dates and you want to add fields that show only the calendar year and fiscal year so that you can sort/filter/pivot on that data.

Clicking icons at bottom of Excel embed will allow you to view full screen or download as Excel file.

As before would appreciate any feedback on appearance and especially how well you were able to enter date in left hand column and see results in columns to the right. (Interactively feels a little hit and miss when I try in preview move).

Direct link to this Excel file is HERE; All of my cloud based public Excel files can be found in the SkyDrive folder HERE. (A permanent link to this folder is now in the right pane, look for "Excel Cloud Folder").

Originally created and posted on the Oregon Housing Blog.

Test Embed of Excel File in the Cloud: GSE CY 2010 Oregon Hispanic Loans by County.

I am pasting below one of the worksheets from the GSE Excel workbook that I posted yesterday.  This worksheet shows GSE Hispanic loans in Oregon for CY 2010, by county and by loan purpose. 

The embed is from file I have posted to MS cloud service (Windows Live SkyDrive).

In addition to being able to view the Excel file in this post in your browser

• You can use the scroll bars to see columns/rows not initially visible. 
• Also note that at the very bottom of the embed you also have the option to download the actual Excel file with this worksheet or to open it full size in your browser. 

Being able to view/download Excel files in their original formats within a blog post/browser is a clear demonstration of how "the cloud" can be creatively integrated with our current desktop applications. Being able to "Save As" a file into the cloud should reduce the need to send files as email attachments and expand the ability to share and collaborate with others.

Note: A direct link to this file is HERE. 

I am still very much in a learning curve in using this new capability; would appreciate any feedback on your experience in viewing and downloading this Excel file.

Originally created and posted on the Oregon Housing Blog. 

Lake Oswego WOW: Nearly 1 in 6 Owner Occupants with a Mortgage Got a Government Assisted Home Loan in Just ONE Year; Loan Total Was $395 Million.

With my recent work on the need for government assisted RENTAL housing in Lake Oswego, including Foothills redevelopment, I thought this would be a good time to go back and see how many government assisted HOME LOANS have been provided to Lake Oswego home owners in the recent past. 

The $$ and household estimates here do NOT include home ownership related tax expenditures like home mortgage and property tax deductions or home buyer tax credits, etc, but ONLY the ONE YEAR amount of single family loans from FHA, Fannie Mae and Freddie Mac.  (My estimate also doesn't include VA home loans).

As the table pasted below shows I estimate that in CY 2010 1,400+ Lake Oswego families received nearly $395 million in loans insured or purchased by government agencies: FHA, Fannie Mae, and Freddie Mac. (My estimate is conservative as the GSE loan total ONLY includes 6 of 12 census tracts  that I count that are fully or partially located in Lake Oswego and recall that I also did NOT include VA loans):

Further Analysis Possible With Excel Workbook

You can do you own analysis by looking at the GSE FHA CY 2010 Lake Oswego workbook I have posted HERE. It includes the table above; all the loan level GSE and FHA data available; and a worksheet that shows a listing of Lake Oswego census tracts that I used and and clipped pictures of Census Tract map boundary maps.  

I recommend that you look at the READ ME worksheet when you open this workbook.

 

WOW: Nearly One in Six Lake Oswego Owner Occupied Home Owners With a Mortgage Got a Government Assisted Home Loan in Just ONE Year, CY 2010. 

The most recent 3 Year ACS estimate (Table B25081] is that there were 8,278 Lake Oswego owner occupied households with a mortgage. Using the Excel workbook "occupancy code " data field in the GSE data worksheet to filter out 77 LO GSE investor/ non owner occupant loans leaves 1,228 owner occupied GSE loans during CY 2010. 

Adding in the 122 FHA owner occupied loans means there were 1,350 owner occupied government assisted home loans in Lake Oswego during CY 2010. 

Dividing that by the 8,278 Lake Oswego owner occupied households with mortgage means that 16.3% of all LO families (nearly one in six) with a mortgage got a government assisted loan in just ONE year, CY 2010. [Note that my count of government assisted loans also does NOT include VA loans).

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.

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.

First Ever Multiple Month/Year Voucher Excel Data for 2,300+ Housing Authorities, Including All Oregon PHA's.

I have prepared a single MS Excel workbook that contains multiple months/years of Voucher Management System data for 2,300+ US public housing authorities with vouchers. (It is possible that there may be a similar multi-year voucher public workbook out there someplace, but I am the proverbial "doubting Thomas" about that).

There are 86 data fields in the HUD VMS data, and I added two fields, a state field and a HUD region number field to make analysis by those fields easy to do. (For the 33 months included in the database that's more than 6.6 million pieces of voucher data, but whose counting?)

A HUD MS Word data dictionary HERE provides details about the values found in the VMS data fields. The IMPORTANT READ ME worksheet in the MS Excel Workbook has a complete list of these data fields in the column order in which they appear in the workbook.

The data includes monthly data for all months in CY 2009 and CY 2010. For CY 2011, data is only through September 2011, the last quarterly data currently available from HUD.

The workbook includes 

1. An Important READ me worksheet.
2. An Oregon voucher pivot table [The default view shows September 2009, 2010, and 2011 sum of vouchers under lease at the end of the month for each Oregon housing authority].
3. An Oregon voucher worksheet.
4. A US voucher pivot table [Default view shows September 2009, 2010, and 2011 sum of vouchers under lease BY STATE at the end of the month].
5. A US voucher data worksheet. 
6. A HUD Regional Lookup worksheet, used in a formula to fill in the HUD Region number in a field in the US voucher data worksheet.

This 42 MB MS Excel workbook is HERE and for easy future reference a link has been added in the right pane. Look for: 

 "Voucher VMS Data, US and Oregon: 2009,2010,2011". 

As a sample of the kinds of data found in the database, I have pasted below a pic with a count of vouchers, BY HUD REGIONS, under lease at the end of September 2009,2010, and 2011. 

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.

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.

Sunshine Week Posts Will Focus on Details of $1 Trillion in GSE Loans.

To celebrate national Sunshine Week, I am planning a series of posts this week focused on the data found in the MS Access databases I recently completed (with the assistance of former HUD Oregon Field Office Director Roberta Ando).  These databases include loan level data on 4.8 million loans with loan balances that exceeded $1 Trillion when they were purchased by the GSE's in CY 2010.

Look for posts that add PDF tables to the Picture of GSE Assisted Households, CY 2010 in the right pane.  

The link to my cloud data site for Excel files in the right pane, GSE CY 2010 Excel Files , will also be updated with new Excel workbooks that provide loan level data for ALL U.S. loans purchased by the GSE's that had been made to 

• Investors, 
• Millionaires, and
• African Americans

Oh, and ONE more thing; toward the end of the week, look for one big final GSE post. 

Originally created and posted on the Oregon Housing Blog.