M
Matchbook Labs
Guest
HUD publishes Fair Market Rent numbers for every ZIP code in the country. Landlords use them for Section 8 pricing, analysts use them for rent benchmarks, and the data is public domain, free to download, no signup. It is also some of the most spreadsheet-hostile government data I have parsed. The numbers are good. The files fight you on the way in.
I run data-vault, a sample-first dataset catalog at Matchbook Labs, and HUD's FY2026 rent publication was a natural fit for it. This is what broke during the build, what the cleaned version looks like, and why I ended up publishing it with an MCP server and a DOI instead of a plain CSV.
Two Excel workbooks on huduser.gov:
Between them, that is every metro and small-area FMR for FY2026, five bedroom sizes each, with 90% and 110% payment standard variants. Everything below comes from real runs against the files HUD serves today, pandas 3.0.5, September 2026.
Hard line breaks inside the header. The ZIP sheet's first cell is not
and the rent columns read like
Three columns per number you want. The sheet is 18 columns wide: ZIP, area code, area name, then five bedroom sizes times three variants. The base rent sits at every third column starting from the fourth. A stride-3 walk gets you the values; the other twelve columns are payment standards you probably do not need. Sensible for program staff reading a spreadsheet, pure noise in a dataframe.
A workbook that refuses to open. The county file
A sheet that lies about its size. The county sheet's dimension reports 1,048,576 rows because the grid runs to the Excel maximum. Real data is a few thousand rows. Trusting
The output is three flat CSVs: 51,895 ZIP rows, 3,229 county rows, 52 state rows. Column names are one flat schema (
Two details cost real time. HUD stores state implicitly: the MSA name ends in
Then verification, because a pipeline that transforms 51,895 rows without spot checks is a guess. Reading the finished CSV back: ZIP 10001 (Manhattan) shows a 2BR FMR of $4,370, 90210 shows $4,350, 60614 (Chicago) shows $2,670. California's highest county median is Santa Cruz at $4,214 and the lowest is Modoc at $1,108. All plausible against published FY2026 figures, and every value traces back to a specific cell in the source workbooks.
The cleaned CSV loads in one
Government Excel is written for humans reading cells, not for code. Check the raw header strings before writing a single parsing line, never trust sheet dimensions, and verify the output against a handful of values you can check by eye. The whole build, including the two source downloads with their SHA256 hashes documented, ran to a few hundred lines of Python and one regex patch on a ZIP member. The data was always free. Making it usable is the actual work.
One honest limit: this pack covers FMRs only. HUD's income limits are a separate publication with a separate set of quirks, and I have not merged them. If your use case needs both, they join on FIPS, carefully.
I run data-vault, a sample-first dataset catalog at Matchbook Labs, and HUD's FY2026 rent publication was a natural fit for it. This is what broke during the build, what the cleaned version looks like, and why I ended up publishing it with an MCP server and a DOI instead of a plain CSV.
What HUD actually ships
Two Excel workbooks on huduser.gov:
FY26_FMRs.xlsx(368 KB): 4,764 county-level rows across 2 sheets.fy2026_safmrs.xlsx(4.2 MB): 51,895 ZIP-level rows, one sheet.
Between them, that is every metro and small-area FMR for FY2026, five bedroom sizes each, with 90% and 110% payment standard variants. Everything below comes from real runs against the files HUD serves today, pandas 3.0.5, September 2026.
Four ways the workbooks resist code
Hard line breaks inside the header. The ZIP sheet's first cell is not
ZIP Code. It is:
Code:
'ZIP\nCode'and the rent columns read like
'SAFMR\n2BR - 90%\nPayment\nStandard'. HUD wraps header text inside the cells, so any code that matches columns by name has to strip newlines first. Until you do, nothing matches.Three columns per number you want. The sheet is 18 columns wide: ZIP, area code, area name, then five bedroom sizes times three variants. The base rent sits at every third column starting from the fourth. A stride-3 walk gets you the values; the other twelve columns are payment standards you probably do not need. Sensible for program staff reading a spreadsheet, pure noise in a dataframe.
A workbook that refuses to open. The county file
FY26_FMRs_revised.xlsx carries a malformed timestamp in its document properties: 2026- 2-19T18:17:31Z, a space where the month padding should be. openpyxl dies on load with Unable to read workbook: could not read properties before you see a single row. The fix is unglamorous: unzip the xlsx, regex-patch the two property files, rezip, then load. The original never gets touched.A sheet that lies about its size. The county sheet's dimension reports 1,048,576 rows because the grid runs to the Excel maximum. Real data is a few thousand rows. Trusting
max_row gives you five million empty iterations. And while you are in there: rent cells mix text and numbers in the same column ('850' next to 1090.0), and a default CSV import strips the leading zeros off 3,533 ZIP codes in Northeastern states, which silently corrupts every join on ZIP.What the cleaned version looks like
The output is three flat CSVs: 51,895 ZIP rows, 3,229 county rows, 52 state rows. Column names are one flat schema (
zip, hud_area_code, metro, area_name, state, fmr_0br..fmr_4br), every rent value is a bare integer string, and the metro flag is derived from the HUD area code prefix because the ZIP file does not mark it.Two details cost real time. HUD stores state implicitly: the MSA name ends in
Abilene, TX MSA, so state comes from the name tail. Four areas have no state in the name at all (Kusilvak AK, Petersburg AK, Oglala Lakota SD, Puerto Rico nonmetro zips), and those fall back to the FIPS prefix of the area code. Zero rows left without a state after both passes.Then verification, because a pipeline that transforms 51,895 rows without spot checks is a guess. Reading the finished CSV back: ZIP 10001 (Manhattan) shows a 2BR FMR of $4,370, 90210 shows $4,350, 60614 (Chicago) shows $2,670. California's highest county median is Santa Cruz at $4,214 and the lowest is Modoc at $1,108. All plausible against published FY2026 figures, and every value traces back to a specific cell in the source workbooks.
Getting it to agents, and to analysts
The cleaned CSV loads in one
pd.read_csv with correct types, which was the point. But most of the traffic I care about now is agents, so the pack ships three ways:- Sample-first pages: every dataset page on data-vault shows real rows before asking anything, and a 22-row sample CSV is live with no download step at
https://jayjex.github.io/data-vault/data/hud-fmr-2026/sample.csv. - An MCP server (
npx -y github:jayjex/dataset-mcp) exposingquery_dataset(slug, {where, columns, limit, offset}), so an agent can filter 51,895 rows without downloading the file. - A DOI (10.5281/zenodo.22643149) on Zenodo, so the build has a citable, timestamped record.
What I would tell anyone doing the same
Government Excel is written for humans reading cells, not for code. Check the raw header strings before writing a single parsing line, never trust sheet dimensions, and verify the output against a handful of values you can check by eye. The whole build, including the two source downloads with their SHA256 hashes documented, ran to a few hundred lines of Python and one regex patch on a ZIP member. The data was always free. Making it usable is the actual work.
One honest limit: this pack covers FMRs only. HUD's income limits are a separate publication with a separate set of quirks, and I have not merged them. If your use case needs both, they join on FIPS, carefully.