`scripts/fetch-fha-loans.js`

322 lines
#!/usr/bin/env node
// fetch-fha-loans.js — CRCP "FHA / low-rate loans" builder.
//
// Pulls REAL public data only ($0):
//   1. HUD/FHA Multifamily Insured Mortgages (ACTIVE) — property name, city/state/zip,
//      original mortgage amount, INTEREST RATE, initial-endorsement (origination) date.
//      This is the realistic public dataset that carries a per-loan ORIGINAL INTEREST RATE.
//      FHA SINGLE-FAMILY per-loan rate+address data is NOT public (borrower privacy), so the
//      deliverable is scoped to HUD/FHA MULTIFAMILY (commercial) and clearly labeled as such.
//   2. Freddie Mac PMMS 30-yr fixed weekly average (history back to 1971) — the market benchmark.
//
// Processing:
//   - keep loans whose ORIGINAL interest rate <= 3.5%
//   - belowMarket   = loan rate is >= 0.75 pct-pt below the PMMS 30-yr avg for its origination month
//   - historicLowEra= origination month falls inside a PMMS rock-bottom-rate trough window
//                     (derived from the data, not hardcoded)
//   - RED = belowMarket OR historicLowEra (either signal fires red); we keep both reasons.
//
// Idempotent + re-runnable. Logs real source URLs + row counts. Writes data/fha-loans.json.
//
// Run: node scripts/fetch-fha-loans.js

const fs = require('fs');
const os = require('os');
const path = require('path');
const { execFileSync } = require('child_process');
const XLSX = require('xlsx');

const ROOT = path.join(__dirname, '..');
const OUT = path.join(ROOT, 'data', 'fha-loans.json');
const RAW = path.join(ROOT, 'data', 'raw');

// ---- real public source URLs (verified reachable 2026-06-30, all $0) ----
const SRC = {
  hudActiveXlsx:
    'https://www.hud.gov/sites/default/files/Housing/documents/FHA-BF90-RM-A.xlsx',
  hudPage: 'https://www.hud.gov/hud-partners/multifamily-fhasl-active',
  // Freddie Mac PMMS 30-yr fixed weekly history (canonical origin)
  pmmsFreddie: 'https://www.freddiemac.com/pmms/docs/historicalweeklydata.xlsx',
  // FRED republishes the same PMMS 30-yr series as a clean CSV — used as the machine-readable feed
  pmmsCsv: 'https://fred.stlouisfed.org/graph/fredgraph.csv?id=MORTGAGE30US',
};

const RATE_CAP = 3.5;          // keep loans originally at or below this
const BELOW_MARKET_GAP = 0.75; // pct-pt below the month's PMMS benchmark to flag belowMarket

function log(...a) { console.log('[fha-loans]', ...a); }

// Download a URL to a Buffer via curl (follows redirects, hard timeout). Public, unauthenticated, $0.
// curl is used rather than node https because some gov/data hosts (FRED) stall node's TLS client.
function download(url) {
  const tmp = path.join(os.tmpdir(), 'crcp-dl-' + Math.random().toString(36).slice(2) + '.bin');
  try {
    // --http1.1: FRED intermittently resets HTTP/2 streams (curl err 92); HTTP/1.1 is stable.
    execFileSync(
      'curl',
      ['-sSL', '--fail', '--http1.1', '--retry', '2', '--max-time', '90',
        '-A', 'CRCP/1.0 (+local FHA loan explorer)', '-o', tmp, url],
      { stdio: ['ignore', 'ignore', 'pipe'] }
    );
    return fs.readFileSync(tmp);
  } finally {
    try { fs.unlinkSync(tmp); } catch (_) {}
  }
}

// Excel serial date -> 'YYYY-MM-DD' (HUD endorsement dates are stored as Excel serials).
function excelSerialToISO(n) {
  if (typeof n !== 'number' || !isFinite(n) || n <= 0) return null;
  const d = XLSX.SSF.parse_date_code(n);
  if (!d || !d.y) return null;
  return `${d.y}-${String(d.m).padStart(2, '0')}-${String(d.d).padStart(2, '0')}`;
}

// Build month->[rates] from the FRED CSV (observation_date,MORTGAGE30US).
function pmmsFromCsv(csv) {
  const acc = {};
  const lines = csv.trim().split('\n').slice(1); // drop header
  for (const line of lines) {
    const [date, val] = line.split(',');
    if (!date || val == null || val === '.') continue;
    const r = parseFloat(val);
    if (!isFinite(r)) continue;
    (acc[date.slice(0, 7)] = acc[date.slice(0, 7)] || []).push(r);
  }
  return { acc, rows: lines.length };
}

// Fallback: build month->[rates] from the Freddie Mac PMMS xlsx (col0=Week serial, col1=30yr FRM).
function pmmsFromXlsx(buf) {
  const _warn = console.error; console.error = () => {};
  const wb = XLSX.read(buf, { type: 'buffer' });
  console.error = _warn;
  const ws = wb.Sheets[wb.SheetNames[0]];
  const rows = XLSX.utils.sheet_to_json(ws, { header: 1, defval: null });
  const acc = {};
  let rowcount = 0;
  for (const r of rows) {
    if (!r) continue;
    const serial = Number(r[0]);
    const rate = Number(r[1]);
    if (!isFinite(serial) || serial < 20000 || !isFinite(rate) || rate <= 0 || rate > 25) continue;
    const iso = excelSerialToISO(serial);
    if (!iso) continue;
    (acc[iso.slice(0, 7)] = acc[iso.slice(0, 7)] || []).push(rate);
    rowcount++;
  }
  return { acc, rows: rowcount };
}

// ---- 1. PMMS benchmark: month -> avg 30yr fixed. FRED CSV primary, Freddie Mac xlsx fallback. ----
function loadPmms() {
  let acc = null, rows = 0;
  try {
    const csv = download(SRC.pmmsCsv).toString('utf8');
    ({ acc, rows } = pmmsFromCsv(csv));
    log('PMMS: fetched FRED CSV', SRC.pmmsCsv, `(${rows} weekly obs)`);
  } catch (e) {
    log('PMMS FRED CSV failed (' + e.message.split('\n')[0] + '); trying Freddie Mac xlsx…');
    try {
      const buf = download(SRC.pmmsFreddie);
      ({ acc, rows } = pmmsFromXlsx(buf));
      log('PMMS: fetched Freddie Mac xlsx', SRC.pmmsFreddie, `(${rows} weekly obs)`);
    } catch (e2) {
      log('PMMS fetch FAILED (both sources):', e2.message.split('\n')[0]);
      return { byMonth: {}, troughs: [], rows: 0 };
    }
  }
  const byMonth = {};
  for (const [m, arr] of Object.entries(acc)) {
    byMonth[m] = +(arr.reduce((s, x) => s + x, 0) / arr.length).toFixed(3);
  }
  const troughs = deriveTroughs(byMonth);
  log(`PMMS: ${Object.keys(byMonth).length} monthly benchmarks (1971→present); derived ${troughs.length} historic-low windows`);
  troughs.forEach((t) => log(`  trough window ${t.start}..${t.end} (min PMMS ${t.minRate}%)`));
  return { byMonth, troughs, rows };
}

// Derive "historic low" windows: take the months whose PMMS avg is within ~0.6 pct-pt of the
// all-time minimum, then collapse contiguous runs into [start,end] windows. Data-driven, not
// hardcoded — this naturally yields the 2020–2021 sub-3% trough and the 2012–2013 ~3.3-3.5% trough.
function deriveTroughs(byMonth) {
  const entries = Object.entries(byMonth).sort((a, b) => a[0].localeCompare(b[0]));
  if (!entries.length) return [];
  const minRate = Math.min(...entries.map((e) => e[1]));
  const THRESH = minRate + 0.85; // within 0.85 pct-pt of the all-time low counts as "rock-bottom era"
  const lowMonths = entries.filter((e) => e[1] <= THRESH).map((e) => e[0]);
  // collapse contiguous (month-adjacent) runs
  const windows = [];
  let run = null;
  const monthNum = (m) => { const [y, mm] = m.split('-').map(Number); return y * 12 + (mm - 1); };
  for (const m of lowMonths) {
    if (run && monthNum(m) - monthNum(run.end) <= 1) {
      run.end = m;
      run.min = Math.min(run.min, byMonth[m]);
    } else {
      if (run) windows.push(run);
      run = { start: m, end: m, min: byMonth[m] };
    }
  }
  if (run) windows.push(run);
  // only keep windows of >=3 months (ignore one-off blips), tag with min rate
  return windows
    .filter((w) => monthNum(w.end) - monthNum(w.start) >= 2)
    .map((w) => ({ start: w.start, end: w.end, minRate: +w.min.toFixed(2) }));
}

function inTrough(month, troughs) {
  return troughs.some((t) => month >= t.start && month <= t.end);
}

// ---- 2. HUD active multifamily insured mortgages ----
function loadHud() {
  let buf;
  try {
    buf = download(SRC.hudActiveXlsx);
    log('HUD: fetched', SRC.hudActiveXlsx, `(${(buf.length / 1e6).toFixed(1)}MB)`);
  } catch (e) {
    log('HUD fetch FAILED:', e.message);
    return [];
  }
  // cache the raw xlsx for provenance / re-runs
  try { fs.mkdirSync(RAW, { recursive: true }); fs.writeFileSync(path.join(RAW, 'hud-mf-active.xlsx'), buf); } catch (_) {}

  // xlsx may emit "Bad uncompressed size" warnings on HUD's files; data still parses. Silence them.
  const _warn = console.error; console.error = () => {};
  const wb = XLSX.read(buf, { type: 'buffer' });
  console.error = _warn;
  const ws = wb.Sheets[wb.SheetNames[0]];
  const rows = XLSX.utils.sheet_to_json(ws, { header: 1, defval: null });

  // header is the first row whose first cell is "HUD PROJECT NUMBER"
  let h = rows.findIndex((r) => r && String(r[0]).toUpperCase().includes('HUD PROJECT NUMBER'));
  if (h < 0) h = 1;
  const head = rows[h].map((c) => String(c == null ? '' : c).trim().toUpperCase());
  const col = (name) => head.indexOf(name);
  const cName = col('PROPERTY NAME');
  const cCity = col('PROPERTY CITY');
  const cState = col('PROPERTY STATE');
  const cZip = col('PROPERTY ZIP');
  const cUnits = col('UNITS');
  const cInit = col('INITIAL ENDORSEMENT DATE');
  const cAmt = col('ORIGINAL MORTGAGE AMOUNT');
  const cRate = col('INTEREST RATE');
  const cHolder = col('HOLDER NAME');

  const out = [];
  for (let i = h + 1; i < rows.length; i++) {
    const r = rows[i];
    if (!r || r.length === 0) continue;
    const rate = Number(r[cRate]);
    if (!isFinite(rate) || rate <= 0) continue;
    const name = r[cName] != null ? String(r[cName]).trim() : '';
    if (!name) continue;
    out.push({
      property: name,
      city: r[cCity] != null ? String(r[cCity]).trim() : '',
      state: r[cState] != null ? String(r[cState]).trim() : '',
      zip: r[cZip] != null ? String(r[cZip]).trim() : '',
      units: Number(r[cUnits]) || null,
      originalAmount: Number(r[cAmt]) || null,
      rate: +rate.toFixed(3),
      originationDate: excelSerialToISO(Number(r[cInit])),
      holder: cHolder >= 0 && r[cHolder] != null ? String(r[cHolder]).trim() : '',
    });
  }
  log(`HUD: parsed ${out.length} active insured multifamily mortgages with a real interest rate`);
  return out;
}

function main() {
  log('start — fetching REAL public HUD + PMMS data ($0, no paid APIs)');
  const { byMonth, troughs, rows: pmmsRows } = loadPmms();
  const hud = loadHud();

  // filter to original rate <= 3.5%
  const kept = hud.filter((l) => l.rate <= RATE_CAP);
  log(`filter: ${kept.length} of ${hud.length} loans have an original rate <= ${RATE_CAP}%`);

  let redCount = 0, belowOnly = 0, eraOnly = 0, both = 0;
  const rowsOut = kept.map((l) => {
    const month = l.originationDate ? l.originationDate.slice(0, 7) : null;
    const bench = month && byMonth[month] != null ? byMonth[month] : null;
    const belowMarket = bench != null && l.rate <= bench - BELOW_MARKET_GAP;
    const historicLowEra = month != null && inTrough(month, troughs);
    const redReasons = [];
    if (belowMarket) {
      redReasons.push(
        `Rate ${l.rate}% is ${(bench - l.rate).toFixed(2)} pts below the ${month} market avg of ${bench}%`
      );
    }
    if (historicLowEra) {
      const t = troughs.find((x) => month >= x.start && month <= x.end);
      redReasons.push(
        `Originated in a historic rock-bottom-rate window (${t.start}..${t.end}, market floor ~${t.minRate}%)`
      );
    }
    const red = belowMarket || historicLowEra;
    if (red) redCount++;
    if (belowMarket && historicLowEra) both++;
    else if (belowMarket) belowOnly++;
    else if (historicLowEra) eraOnly++;
    return {
      property: l.property,
      address: '', // HUD MF mortgage file carries property name + city/state/zip, not a street line
      city: l.city,
      state: l.state,
      zip: l.zip,
      units: l.units,
      originalAmount: l.originalAmount,
      rate: l.rate,
      originationDate: l.originationDate,
      pmmsBenchmark: bench,
      belowMarket,
      historicLowEra,
      red,
      redReasons,
      holder: l.holder,
    };
  });

  // sort: red first, then lowest rate, then largest loan
  rowsOut.sort((a, b) =>
    (b.red - a.red) || (a.rate - b.rate) || ((b.originalAmount || 0) - (a.originalAmount || 0))
  );

  const payload = {
    generatedAt: new Date().toISOString(),
    scope:
      'HUD/FHA MULTIFAMILY (commercial) insured mortgages — active. FHA single-family per-loan ' +
      'rate+address data is NOT public (borrower privacy), so single-family loans are out of scope.',
    filter: `original interest rate <= ${RATE_CAP}%`,
    redRule:
      'RED = belowMarket OR historicLowEra. belowMarket = loan rate >= ' +
      `${BELOW_MARKET_GAP} pct-pt below the PMMS 30-yr avg for its origination month; ` +
      'historicLowEra = origination month falls in a PMMS rock-bottom-rate trough (derived from the data).',
    sources: [
      { name: 'HUD/FHA Multifamily Insured Mortgages (Active)', url: SRC.hudActiveXlsx, page: SRC.hudPage },
      { name: 'Freddie Mac PMMS 30-yr fixed weekly history (canonical)', url: SRC.pmmsFreddie },
      { name: 'PMMS 30-yr series machine-readable feed (FRED MORTGAGE30US)', url: SRC.pmmsCsv },
    ],
    benchmarkMonths: Object.keys(byMonth).length,
    historicLowWindows: troughs,
    totalParsed: hud.length,
    count: rowsOut.length,
    redCount,
    redSplit: { belowMarketOnly: belowOnly, historicLowEraOnly: eraOnly, both },
    rows: rowsOut,
  };

  fs.mkdirSync(path.dirname(OUT), { recursive: true });
  fs.writeFileSync(OUT, JSON.stringify(payload, null, 2));
  log(`wrote ${OUT}`);
  log(
    `RESULT: ${rowsOut.length} loans <= ${RATE_CAP}% | ${redCount} RED ` +
    `(belowMarket-only ${belowOnly}, historicLowEra-only ${eraOnly}, both ${both}) | ` +
    `PMMS rows ${pmmsRows}`
  );
}

try { main(); } catch (e) { console.error('[fha-loans] FATAL', e); process.exit(1); }