← back to Commercialrealestate
scripts/fetch-hmda-market.js
204 lines
#!/usr/bin/env node
// fetch-hmda-market.js — CRCP "Mortgage Market" builder for the lending-intelligence layer.
//
// Pulls REAL public data only ($0) from the official CFPB HMDA Data Browser API and aggregates it
// to ONE ROW PER LENDER for Los Angeles County (FIPS 06037): who originates mortgages here, how many,
// and how much dollar volume. This is the institution-level MARKET view — never per-borrower.
//
// SOURCE ROUTES (both official CFPB HMDA Data Browser, verified reachable 2026-07-31, $0, no key):
// 1. /view/csv — loan-record extract for years=<Y> states=CA counties=06037 actions_taken=1
// (originations only). This is the ONLY route that actually respects the county filter (the
// /view/aggregations route was probed and found to IGNORE `counties`, returning CA-statewide
// totals — so it is deliberately NOT used here). We read ONLY two columns from each record —
// `lei` (the filing institution) and `loan_amount` — and aggregate locally by LEI. No
// demographic / borrower field is ever read (DATA_POLICY.md §5: HMDA is institution-level only;
// demographic fields are never used to rank or target).
// 2. /view/filers — per-institution {lei, name, count} list for the same geo, used only to join a
// human lender NAME onto each LEI. No amounts, no demographics.
//
// Aggregated per lender: loan_count (originations), total_volume (whole $), avg_loan_amount, year.
// Every row is source-cited: source="CFPB / HMDA", source_url=public HMDA Data Browser filer/geo link,
// identifier=the lender LEI, retrieved_at=now. NO personal data.
//
// If the API cannot be reached, writes an HONEST manual_review_only file (empty "market":[] + a meta
// note + the public deep link) — it NEVER invents lenders.
//
// Idempotent + re-runnable. Logs real source URLs + row counts. $0, public, no key.
//
// Run: node scripts/fetch-hmda-market.js
const fs = require('fs');
const path = require('path');
const { execFileSync } = require('child_process');
const ROOT = path.join(__dirname, '..');
const OUT = path.join(ROOT, 'data', 'hmda-market.json');
// ---- real public source (CFPB HMDA Data Browser API, verified reachable 2026-07-31, $0, no key) ----
const API = 'https://ffiec.cfpb.gov/v2/data-browser-api/view';
const HOME = 'https://ffiec.cfpb.gov/data-browser/'; // public human data browser
const STATE = 'CA';
const COUNTY = '06037'; // Los Angeles County FIPS
const ACTION_ORIGINATED = '1'; // HMDA action_taken=1 → loan originated
// Most-recent-first candidate years — first one that returns records wins.
const YEARS = ['2023', '2022', '2021', '2020'];
function log(...a) { console.log('[hmda-market]', ...a); }
// GET a URL to a Buffer via curl (-L follows redirects, --compressed handles gzip, hard timeout). $0.
function getBuf(url) {
return execFileSync('curl', ['-sL', '--compressed', '--max-time', '240', url], {
maxBuffer: 512 * 1024 * 1024, // the LA-County CSV extract is ~30MB
});
}
function getText(url) { return getBuf(url).toString('utf8'); }
function csvUrl(year) {
return `${API}/csv?years=${year}&states=${STATE}&counties=${COUNTY}&actions_taken=${ACTION_ORIGINATED}`;
}
function filersUrl(year) {
return `${API}/filers?years=${year}&states=${STATE}&counties=${COUNTY}`;
}
// A public HMDA Data Browser deep link scoped to this lender (LEI) + geo — where a human can verify.
function filerDeepLink(lei, year) {
return `${HOME}maps/${year}?geoids=${COUNTY}&leis=${lei}`;
}
function geoDeepLink(year) {
return `${HOME}maps/${year}?geoids=${COUNTY}`;
}
function atomicWrite(file, obj) {
fs.writeFileSync(file + '.tmp', JSON.stringify(obj));
fs.renameSync(file + '.tmp', file);
}
// Parse a HMDA CSV extract, reading ONLY the `lei` and `loan_amount` columns, and aggregate by LEI.
// Every other column (incl. all demographic/borrower fields) is ignored by construction.
function aggregateByLei(csvText) {
const lines = csvText.split('\n');
if (!lines.length) return {};
const header = lines[0].split(',');
const leiCol = header.indexOf('lei');
const amtCol = header.indexOf('loan_amount');
if (leiCol < 0 || amtCol < 0) {
throw new Error(`HMDA CSV missing expected columns (lei=${leiCol}, loan_amount=${amtCol})`);
}
const agg = {}; // lei -> { count, volume }
for (let i = 1; i < lines.length; i++) {
const line = lines[i];
if (!line) continue;
const f = line.split(',');
const lei = f[leiCol];
if (!lei) continue;
const amt = Number(f[amtCol]); // HMDA loan_amount is whole dollars (may be in E-notation)
const a = agg[lei] || (agg[lei] = { count: 0, volume: 0 });
a.count += 1;
if (Number.isFinite(amt)) a.volume += amt;
}
return agg;
}
// Best-effort LEI → lender-name map from the filers endpoint (name join only; no amounts read).
function fetchFilerNames(year) {
const names = {};
try {
const d = JSON.parse(getText(filersUrl(year)));
(d.institutions || []).forEach(i => { if (i.lei) names[i.lei] = i.name || null; });
} catch (e) {
log(`filers name-join unavailable for ${year} (${e.message}) — LEIs will show without a name`);
}
return names;
}
function writeManual(reason) {
const retrieved_at = new Date().toISOString();
atomicWrite(OUT, {
meta: {
source: 'CFPB / HMDA',
source_home: HOME,
source_url: geoDeepLink(YEARS[0]),
access_method: 'manual_review_only',
personal_data: false,
year: null,
county: 'Los Angeles (06037)',
count: 0,
note: `CFPB HMDA Data Browser API could not be reached (${reason}). No lenders were fabricated — verify manually at the official deep link.`,
retrieved_at,
},
market: [],
});
log(`WROTE manual_review_only file (${reason}) → ${path.relative(ROOT, OUT)}`);
}
function main() {
const retrieved_at = new Date().toISOString();
// 1) Find the most-recent year whose CSV record route returns LA-County originations.
let year = null, csvText = null, url = null;
for (const y of YEARS) {
url = csvUrl(y);
log(`probing ${y}: ${url}`);
let text;
try { text = getText(url); }
catch (e) { log(` ${y}: fetch failed (${e.message})`); continue; }
// A real extract starts with the HMDA header line "activity_year,lei,...".
if (/^activity_year,lei,/.test(text) && text.split('\n').length > 2) {
year = y; csvText = text;
log(` ${y}: OK — ${text.split('\n').length - 1} records`);
break;
}
log(` ${y}: no records / unexpected shape`);
}
if (!year) { writeManual('no year returned a usable CSV extract'); return; }
// 2) Aggregate to one row per LENDER (LEI), reading only lei + loan_amount.
const agg = aggregateByLei(csvText);
const names = fetchFilerNames(year);
log(`distinct lenders (LEI) originating in LA County ${year}: ${Object.keys(agg).length}`);
const market = Object.keys(agg).map((lei, idx) => {
const a = agg[lei];
const avg = a.count ? Math.round(a.volume / a.count) : null;
return {
id: idx + 1,
lender: names[lei] || lei,
lei,
loan_count: a.count, // originations (action_taken=1)
total_volume: Math.round(a.volume), // whole dollars
avg_loan_amount: avg, // whole dollars
year: String(year),
// provenance (DATA_POLICY §5 — institution-level only, no personal data)
source: 'CFPB / HMDA',
source_url: filerDeepLink(lei, year),
identifier: lei,
retrieved_at,
};
}).sort((x, y2) => (y2.total_volume - x.total_volume) || (y2.loan_count - x.loan_count));
// Re-id in sorted order so the id is a stable rank.
market.forEach((r, i) => { r.id = i + 1; });
const meta = {
source: 'CFPB / HMDA',
source_home: HOME,
source_url: geoDeepLink(year),
api: `${API}/csv`,
scope: `HMDA RESIDENTIAL mortgage originations (action_taken=1) in Los Angeles County (06037), ${year}, aggregated by lender (LEI)`,
coverage: 'RESIDENTIAL ONLY. HMDA covers dwelling-secured home mortgages (1-4 family + multifamily dwellings). It does NOT cover commercial-property loans (office, retail, industrial, land) — this is home-lending market share, not commercial-RE lending.',
access_method: 'official_api',
personal_data: false,
year: String(year),
county: 'Los Angeles (06037)',
count: market.length,
retrieved_at,
};
atomicWrite(OUT, { meta, market });
log(`wrote ${market.length} lender-market rows → ${path.relative(ROOT, OUT)}`);
log('source:', geoDeepLink(year), '($0, official CFPB HMDA Data Browser)');
log('csv route:', url);
}
main();