Monday, July 6, 2020

New HUD Affirmatively Furthering Raw Data Released: I Add a Focus on Oregon BLM Households.

I  have downloaded (and added to) HUD Affirmatively Furthering Fair Housing raw data newly posted on June 19th to help find answers to these and other related BLM housing related questions. 

  • How many Black households live in HUD housing choice voucher units in Oregon? 
  • What is the percentage of all Black households who live in HUD housing choice voucher units in Multnomah County?
  • What is the number and percentage of all Black households below 30% of adjusted median family income in the city of Portland?
  • For different Oregon geographies what are the ratings for Black households on a  opportunity indices related to education, transit, employment, and environmental factors and how do they compare to White, Non Hispanic ratings?  
I have uploaded a folder HERE that contains all Oregon data from the HUD June 2020 update to affirmatively furthering fair housing raw data. 

The raw data underlies HUD affirmatively furthering fair housing maps located HERE.
While the maps can be useful to look at one geography or one parameter at a time, the consolidated raw data may be more useful to look at multiple geographies and multiple dimensions and/or to make comparisons over time or across geographies. 

Data in my Oregon AFFH 2020 folder includes these 11 Oregon worksheets in a single "BLM Oregon AFFH 2020"  Excel workbook HERE.  

In addition there are two other Excel workbooks in the folder. The second workbook helps navigate to specific data fields in the Oregon BLM workbook. The third worksheet is a HUD posted crosswalk reference document I haven't yet used in my research. 

2. A BLM VERSION OF THE HUD Public Use data dictionary I constructed HERE 
3. HUD's Public Use Crosswalk is HERE
4. Finally, there is a PDF file HERE which explains the data sources used for the June 2020 HUD AFFH raw data files (and the associated maps). Different data sources and dates are used for different variables so this is an important resource document. 

Highlighting Oregon Black Household AFFH Data 

A. In the BLM Oregon AFFH 2020 workbook
To help users interested in AFFH Oregon data for Black households navigate through each worksheet, I have highlighted in GREEN, ALL column headers with the work "Black" in all 11 worksheets.

For example in the County worksheet by scrolling to the right to GREEN highlighted column 259, one can see the count of Black households living in HUD housing choice voucher units. Columns adjacent show the count of Black households living in public housing, project based rental housing, ETC.  

Only the state worksheet has data outside of Oregon; all other worksheets focus only on Oregon geographies and properties. 

B. In the modified data dictionary workbook (BLM AFFH Public Use Data Dictionaries AFFHT0005 June2020V1) BLM Variables Have Been Filtered As the Default View

This public use data dictionary can help identify variables that may be of interest in the 11 worksheets included in the Oregon BLM AFFH 2020 Excel workbook.

In this HUD data dictionary workbook I ADDED a column with a text lookup; the label for that column is "Black". 

IF second column's "Attribute Description" for that worksheet includes the text "Black" in my added "Black" column the text "BLM" will appear; if "Black" doesn't appear in the "Attribute Description" column the text "N/A" appears.   

By DEFAULT each worksheet in my BLM Public Use Data Dictionary is filtered to show ONLY BLM variables in my added column. IF you want to see ALL variables for ALL races and ethnicities then simply remove the filters for that worksheet. 

Navigating to Specific BLM Data Fields in Worksheets within BLM Oregon Workbook
The NUMBER in the "INDEX" (first column) of the BLM AFFH Public Use Data Dictionaries AFFHT0005 June2020V1 workbook for each geography type corresponds to the related COLUMN number reference in the BLM Oregon AFFH 2020 workbook in the worksheet for that same geography. 

[When you open Excel workbooks the default view is to reference columns as letters instead of numbers. I have changed my Excel default view in the BLM Oregon AFFH 2020 workbook to reference columns as numbers instead of letters to make finding specific fields easier. To see this view you would need to change your Excel default A1 cell reference to the alternative R1C1 referencing format. This is easy to do and easy to switch back from; the post HERE shows how ]. 

  1. In Excel 2010 or a later version display the File tab of the ribbon and then click Options.)
  2. Click Formulas at the left side of the dialog box. (See Figure 1.)
  3. Clear (or set) the R1C1 Reference Style check box, under the "Working with Formulas" heading.
  4. Click OK.]
IF you choose to NOT switch to R1CI referencing instead of using the column index number in the far left column you will need to navigate using the column letter reference I have added in the far right column; so for example for the first column instead of index number "1:, the column letter is "A", the second column has an index number "2" or a column letter "B", ETC].

An Example: Using the column numerical referencing format in the STATE worksheet (in the BLM AFFH Public Use Data Dictionaries AFFHT0005 June2020V1 workbook the "Total number of black population in housing choice voucher" field value in the (first) far left column (labelled "Index") is "243" and in the far right column (labelled "Index column letter reference") the alphabetical column reference is "II".  [While the HUD data dictionary says "population" I checked the data and the count is actually  "households".]

If you then switch to the OR AFFH State June2020 worksheet within the BLM Oregon AFFH 2020 workbook and scroll/navigate to row 1, column 243 (cell reference R1C243) [or row1 in column "II" if you did not change to R1C1 referencing] you will find that variable (highlighted in green). [Make sure you use the same geography in the BLM data dictionary workbook as you use in the BLM Oregon  workbook]. 

Spoiler alert: Column 243/Column II in the state worksheet in the BLM Oregon workbook indicates there were 4,360 Black households living in HUD housing choice voucher units in Oregon. 

The Number of BLM Variables Change with Each Worksheet
The number of BLM variables declines with smaller geographic areas. 
I count more than 100 BLM variables at the state level, and 59 at the county level. 
There is only ONE BLM variable in the Oregon Housing project worksheet, and NO BLM variables in the LIHTC worksheet.

ZIP file of ALL HUD June 19th 2020 AFFH Raw Data
The  BLM AFFH 2020 Data Dictionary Excel file I modified can be used to locate BLM relevant data for any state. Substitute it for the HUD AFFH 2020 Excel file included in the Data Dictionary Excel file included in the HUD posted zip file posted HERE

NOTE: Perhaps to reduce file size HUD choose to use a non standard WinX file format to zip the 2020 data which may require special (not free) software to unzip.   

I downloaded and converted the HUD data to a more standard albeit LARGER file size in zip format (260MB); you can download this version of the HUD 2020 data from my web space HERE.  

Add my modified HUD AFFH 2020 Excel file to the folder created when the HUD file is unzipped and you can then navigate to BLM related data in any geography in the country. 

Originally created and posted on the Oregon Housing Blog.

Tuesday, June 30, 2020

1st Qtr 2020 State UI Overpayment and Recovery Summary: Recoveries $55M, but $1.2 Billion in Overpayments Remain.

The federal DOL has a report (ETA 227) which provides a quarterly detailed breakout of unemployment insurance overpayments and recoveries. Unfortunately, the download available is formatted without any field names and includes 329 columns, making it extremely unfriendly to use.

[I have previously suggested that the Oregon Employment Department at least post this data on the form they submit to DOL, but the last report I could find from them is from Sept 2019]. 
I thought it would be useful to see how much in overpayments were outstanding and how states differed in the methods they used to claw back overpayments. 

So I downloaded the report , painfully added some field names, and can highlight at least some of the data.

I created a 4 page PDF HERE  that shows the results for the US and all states in the first quarter 2020.  (A snapshot of the first row of the first page with national and Oregon totals is pasted below).

Some observations
  1. There were $1.317 billion in overpayments at the start of the quarter, $1.261 billion at the end of the quarter, for a reduction of $55.5 million.
  2. Overpayments are adjusted quarterly by a number of activities, including but not limited to recoveries. 
  3. Recoveries totaled $206 million. 
  4. Recoveries from federal income taxes were the highest share of total recoveries at 32%/$67.7 million. 
  1. There were $14.9 million in overpayments at the start of the quarter; $13.4 million at the end of the quarter, for a reduction of $635,000.
  2. Overpayments are adjusted quarterly by a number of activities, including but not limited to, recoveries. 
  3. Recoveries totaled $3.3 million. 
  4. Recoveries from "other" were the highest share of total recoveries at 32%/$1 million. [ "Other" includes disability insurance, workers compensation), court actions (civil, criminal), and outsourcing (collection by other components of the state government or by private collection agencies)].
  5. The share of Oregon recoveries from federal income taxes were HALF the national average, 16% vs 32%.
  6. The share of recoveries from unemployment benefit offsets were only 16% of Oregon's total recoveries vs 26% nationally. 

Monday, June 29, 2020

Sam Galbreath, An Appreciation

Last week I learned of the passing of Sam Galbreath, the former Housing Director for the Portland Development Commission.  His obituary is pasted below.

I am sure Sam played a role in many accomplishments while at PDC; three of those accomplishments, as I remember them:
  1. His work to get HUD, for the first time in the country, to approve the use of rent vouchers for SIngle Room Occupancy units in downtown Portland.  (In fact I met Sam for the first time at a Sunday 1980 KATU town hall broadcast at  downtown SRO, The Governor Hotel.  That has long been converted to what is now the Sentinel Hotel).
  2. His work to get the state legislature to adopt a distressed housing tax deduction that allowed 10 year property tax reductions for new and rehab homeownership units in North and Northeast Portland.
  3. His work to get HUD to approve a 1989 application for the Nehemiah housing program that provided $15,000 HUD up front grant funds and ultimately resulted in the development of more than 150+ single-family new construction and rehab homes in Northeast Portland by a Black lead nonprofit group, NECDC. (Much more about the Portland Nehemiah program, including how much property values have changed, can be found in my blog post HERE. )
I hope others who may be able to add additional details about Sam feel free to do so in the comment section below.

I will miss Sam and his enthusiasm for all things housing. 

Portland is a better place because of his long years of public service.

Originally created and posted on the Oregon Housing Blog.

Data Envy: Iowa Unemployment Dashboard Includes Up to Date UI Payments and Recipients to County Level.

I have been frustrated for months with the absence of up to date county level Oregon UI payments information. (Getting even a current week or month state level summary of UI benefits paid has been a struggle also). 

While the Oregon quality info page HERE has made some recent improvements that highlight county level demographics the main Oregon unemployment data download information available HERE consist of PDF files, many of which end with data from in 2019

The last county level UI benefit table HERE ENDS with CY 2019 data; a snapshot is pasted below.

I stumbled upon the data available from Iowa HERE and I have serious data envy. A snapshot of one example shows monthly UI payments at the county level for May 2020. Note this includes a county map with benefits paid, and a county list sorted by highest to lowest payments. Also note that this view can be changed to show a sortable list of recipients by counties. 

Since the Oregon Quality info dashboard and the Iowa dashboard both use Tableau I would hope the Oregon could reach out to Iowa to see if there is any opportunity to replicate the county level UI data reporting system in Oregon.

Originally created and posted on the Oregon Housing Blog.

Friday, June 26, 2020

CARES Act $600 Supplemental+Regular UI Provides $1,024 Monthly for Oregon Housing Expenses, But WIll Drop by 63% In One Month Unless $600 Supplement is Extended.

In my prior post HERE I pointed out that the $600 unemployment supplement program (FPUC) provided 56% of all unemployment benefits paid in Oregon for the week June 12-June 19. 

To see how much the $600 supplemental benefit + the regular insurance benefit together provide for housing expenses, I dug out the average weekly benefit for Oregon regular unemployment insurance for May. That was $360. 

So in combination a recipient of regular unemployment insurance in Oregon at the average weekly benefit amount plus the $600 supplemental would have received a weekly total of $960.  

After deducting an assumed combined federal and Oregon income tax rate of 16%, the net combined average regular unemployment insurance+ $600 supplemental benefit would have been $806 weekly and $3,494 MONTHLY.

The table below shows that math and also adds calculations that show much is available for housing expense, using 30% of net income after taxes.  

Bottom Line: Loss of $600 UI Supplemental Would Reduce Amount Available for Housing Expenses By 63%, From $1,048 to $393.
On a MONTHLY basis in May the average regular unemployment insurance and $600 bonus would have provided $1,048 for housing expenses after taxes. ($242 weekly).

IF the $600 bonus goes away on July 25 as scheduled the Oregon MONTHLY amount available for housing expense after taxes would drop to $393, a decline of 63%.

Originally created and posted on the Oregon Housing Blog,