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.