// Builds the Washington hydropower fact-check tables from EIA (and one Washington Commerce) source files. // Usage: node build.js // Requires the SheetJS "xlsx" package (npm i https://cdn.sheetjs.com/xlsx-0.20.3/xlsx-0.20.3.tgz). // // Inputs in (downloaded 2026-10-09, see source-manifest.json for URLs and SHA-256): // annual_generation_state.xlsx EIA State Historical Tables, net generation 1990-2025 (Final) // sep_wa.xlsx EIA State Electricity Profile tables for Washington (data year 2024) // eia860/3_1_Generator_Y2025.xlsx from eia8602025.zip (EIA-860, 2025 final, operable generators) // eia923/EIA923_Schedules_2_3_4_5_M_12_2025_Final.xlsx from f923_2025.zip (EIA-923, 2025 final) // The Commerce fuel-mix shares are transcribed from the labeled chart on Commerce's fuel-mix page (see COMMERCE_2024). const fs = require('fs'); const path = require('path'); const X = require('xlsx'); const [rawDir, outDir] = process.argv.slice(2); if (!rawDir || !outDir) { console.error('usage: node build.js '); process.exit(1); } fs.mkdirSync(outDir, { recursive: true }); const FIRST_YEAR = 2001; const LAST_YEAR = 2025; const r1 = (x) => Math.round(x * 10) / 10; const r2 = (x) => Math.round(x * 100) / 100; const round0 = (x) => Math.round(x); const csv = (rows) => { const cols = Object.keys(rows[0]); const cell = (v) => (v == null ? '' : /[",\n]/.test(String(v)) ? '"' + String(v).replace(/"/g, '""') + '"' : String(v)); return cols.join(',') + '\n' + rows.map((r) => cols.map((c) => cell(r[c])).join(',')).join('\n') + '\n'; }; const write = (name, data) => fs.writeFileSync(path.join(outDir, name), data); // Short, stable source codes for the EIA energy-source labels. const SOURCE_CODES = { 'Hydroelectric Conventional': 'hydro', 'Natural Gas': 'natural_gas', 'Nuclear': 'nuclear', 'Wind': 'wind', 'Coal': 'coal', 'Solar Thermal and Photovoltaic': 'solar', 'Wood and Wood Derived Fuels': 'wood', 'Other Biomass': 'other_biomass', 'Other Gases': 'other_gases', 'Petroleum': 'petroleum', 'Pumped Storage': 'pumped_storage', 'Geothermal': 'geothermal', 'Other': 'other', }; // ---------- 1. State net generation by source (EIA State Historical Tables) ---------- const genWb = X.readFile(path.join(rawDir, 'annual_generation_state.xlsx')); const genRows = X.utils.sheet_to_json(genWb.Sheets[genWb.SheetNames[0]], { header: 1, range: 1 }).slice(1) .filter((r) => r[2] === 'Total Electric Power Industry') .map((r) => ({ year: r[0], state: String(r[1]).trim().toUpperCase(), source: r[3], mwh: Number(r[4]) })); const isState = (s) => /^[A-Z]{2}$/.test(s); // excludes US-TOTAL / US-Total rows const val = (year, state, source) => { const hit = genRows.filter((r) => r.year === year && r.state === state && r.source === source); if (hit.length > 1) throw new Error(`duplicate ${year} ${state} ${source}`); return hit.length ? hit[0].mwh : null; }; const genBySource = []; const annual = []; const rankRows = []; for (let y = FIRST_YEAR; y <= LAST_YEAR; y++) { const total = val(y, 'WA', 'Total'); for (const [label, code] of Object.entries(SOURCE_CODES)) { const v = val(y, 'WA', label); if (v == null) continue; genBySource.push({ year: y, source: code, eia_energy_source: label, net_generation_mwh: round0(v), share_of_total_pct: r2((100 * v) / total) }); } const hydro = val(y, 'WA', 'Hydroelectric Conventional'); // State ranking for conventional hydro in year y. const states = [...new Set(genRows.filter((r) => r.year === y && isState(r.state)).map((r) => r.state))]; const hydroByState = states.map((s) => ({ state: s, mwh: val(y, s, 'Hydroelectric Conventional') || 0 })).sort((a, b) => b.mwh - a.mwh); const usHydro = hydroByState.reduce((a, b) => a + b.mwh, 0); const waRank = hydroByState.findIndex((s) => s.state === 'WA') + 1; rankRows.push({ year: y, hydroByState, usHydro }); annual.push({ year: y, total_net_generation_mwh: round0(total), hydro_net_generation_mwh: round0(hydro), hydro_share_pct: r1((100 * hydro) / total), non_hydro_net_generation_mwh: round0(total - hydro), wa_hydro_rank_among_states: waRank, us_hydro_net_generation_mwh: round0(usHydro), wa_share_of_us_hydro_pct: r1((100 * hydro) / usHydro), second_ranked_state: hydroByState[waRank === 1 ? 1 : 0].state, second_ranked_state_hydro_mwh: round0(hydroByState[waRank === 1 ? 1 : 0].mwh), }); } // Hydro by state in the latest year (top 10). const latest = rankRows[rankRows.length - 1]; const stateRank = latest.hydroByState.slice(0, 10).map((s, i) => ({ year: LAST_YEAR, rank: i + 1, state: s.state, hydro_net_generation_mwh: round0(s.mwh), share_of_us_hydro_pct: r1((100 * s.mwh) / latest.usHydro), })); // ---------- 2. Supply and disposition (EIA State Electricity Profile, Table 10) ---------- const sepWb = X.readFile(path.join(rawDir, 'sep_wa.xlsx')); const t10 = X.utils.sheet_to_json(sepWb.Sheets['10. Source-Disposition'], { header: 1 }); const hdr = t10.find((r) => r[0] === 'Category'); const yearCols = hdr.map((h, i) => [i, Number(String(h).replace(/\D/g, ''))]).filter(([i, y]) => i > 0 && y >= FIRST_YEAR); const t10row = (label) => { const r = t10.find((x) => x[0] === label); if (!r) throw new Error('missing T10 row ' + label); return r; }; const num = (v) => (typeof v === 'number' ? v : null); const genCrossCheck = []; const supply = yearCols.map(([i, y]) => { const gen = num(t10row('Total net generation')[i]); const sales = num(t10row('Total electric industry sales')[i]); const trade = num(t10row('Net interstate trade')[i]); const a = annual.find((x) => x.year === y); if (a) genCrossCheck.push({ year: y, table10_mwh: gen, historical_tables_mwh: a.total_net_generation_mwh, difference_mwh: round0(gen - a.total_net_generation_mwh) }); return { year: y, total_net_generation_mwh: gen, retail_sales_mwh: sales, direct_use_mwh: num(t10row('Direct use')[i]), estimated_losses_mwh: num(t10row('Estimated losses')[i]), international_imports_mwh: num(t10row('Total international imports')[i]), international_exports_mwh: num(t10row('Total international exports')[i]), net_interstate_trade_mwh: trade, generation_to_retail_sales_ratio: r2(gen / sales), hydro_to_retail_sales_pct: a ? r1((100 * a.hydro_net_generation_mwh) / sales) : null, }; }).sort((a, b) => a.year - b.year); // ---------- 3. Largest U.S. plants by capacity (EIA-860 2025, operable generators) ---------- const g860 = X.readFile(path.join(rawDir, 'eia860', '3_1_Generator_Y2025.xlsx'), { sheets: ['Operable'] }); const gens = X.utils.sheet_to_json(g860.Sheets['Operable'], { range: 1 }); const plants = new Map(); for (const g of gens) { const code = g['Plant Code']; if (code == null || g['Plant Name'] == null) continue; // skip footnote rows const p = plants.get(code) || { plant_code: code, plant_name: g['Plant Name'], state: g['State'], operator: g['Utility Name'], nameplate_mw: 0, summer_mw: 0, winter_mw: 0, generators: 0, techMw: {} }; const np = Number(g['Nameplate Capacity (MW)']) || 0; p.nameplate_mw += np; p.summer_mw += Number(g['Summer Capacity (MW)']) || 0; p.winter_mw += Number(g['Winter Capacity (MW)']) || 0; p.generators += 1; p.techMw[g['Technology']] = (p.techMw[g['Technology']] || 0) + np; plants.set(code, p); } const plantList = [...plants.values()].map((p) => ({ ...p, primary_technology: Object.entries(p.techMw).sort((a, b) => b[1] - a[1])[0][0] })); const bySummer = [...plantList].sort((a, b) => b.summer_mw - a.summer_mw); const summerRank = new Map(bySummer.map((p, i) => [p.plant_code, i + 1])); const capTop = [...plantList].sort((a, b) => b.nameplate_mw - a.nameplate_mw).slice(0, 10).map((p, i) => ({ rank_by_nameplate: i + 1, rank_by_summer_capacity: summerRank.get(p.plant_code), plant_code: p.plant_code, plant_name: p.plant_name, state: p.state, operator: p.operator, primary_technology: p.primary_technology, operable_generators: p.generators, nameplate_capacity_mw: r1(p.nameplate_mw), summer_capacity_mw: r1(p.summer_mw), winter_capacity_mw: r1(p.winter_mw), })); // ---------- 4. Largest U.S. plants by 2025 net generation (EIA-923 2025 final, page 1) ---------- const w923 = X.readFile(path.join(rawDir, 'eia923', 'EIA923_Schedules_2_3_4_5_M_12_2025_Final.xlsx'), { sheets: ['Page 1 Generation and Fuel Data'] }); const p1 = X.utils.sheet_to_json(w923.Sheets['Page 1 Generation and Fuel Data'], { header: 1 }); const hi = p1.findIndex((r) => r[0] === 'Plant Id'); const H = p1[hi]; const col = (name) => { const i = H.findIndex((h) => String(h).replace(/\s+/g, ' ').trim() === name); if (i < 0) throw new Error('923 column ' + name); return i; }; const cId = col('Plant Id'), cName = col('Plant Name'), cState = col('Plant State'), cNet = col('Net Generation (Megawatthours)'), cYear = col('YEAR'); const gen923 = new Map(); for (const r of p1.slice(hi + 1)) { if (r[cId] == null || r[cYear] !== LAST_YEAR) continue; const p = gen923.get(r[cId]) || { plant_code: r[cId], plant_name: r[cName], state: r[cState], mwh: 0 }; p.mwh += Number(r[cNet]) || 0; gen923.set(r[cId], p); } const genTop = [...gen923.values()].sort((a, b) => b.mwh - a.mwh).slice(0, 10).map((p, i) => { const cap = plants.get(p.plant_code); return { rank_by_net_generation: i + 1, plant_code: p.plant_code, plant_name: p.plant_name, state: p.state, primary_technology: cap ? plantList.find((x) => x.plant_code === p.plant_code).primary_technology : null, net_generation_2025_mwh: round0(p.mwh), nameplate_capacity_mw: cap ? r1(cap.nameplate_mw) : null, capacity_factor_pct: cap ? r1((100 * p.mwh) / (cap.nameplate_mw * 8760)) : null, }; }); const gc = gen923.get(6163); const gcCap = plants.get(6163); const gcGenRank = [...gen923.values()].sort((a, b) => b.mwh - a.mwh).findIndex((p) => p.plant_code === 6163) + 1; const gcConvHydroNameplate = gens.filter((g) => g['Plant Code'] === 6163 && g['Technology'] === 'Conventional Hydroelectric').reduce((a, g) => a + Number(g['Nameplate Capacity (MW)']), 0); // ---------- 5. Washington utilities' 2024 delivered fuel mix (Commerce) ---------- // Transcribed from the labeled bar chart "Washington electric utilities aggregate 2024 fuel mix" on // https://www.commerce.wa.gov/energy-policy/electricity-policy/fuel-mix-disclosure/ (image 2024_fuel-mix-chart.png). const COMMERCE_2024 = [ ['Hydro', 48.73], ['Unspecified', 15.44], ['Natural Gas', 13.33], ['Wind', 9.6], ['Coal', 5.91], ['Nuclear', 5.13], ['Solar', 0.86], ['Biomass', 0.68], ['Other Fuel Sources', 0.34], ]; const fuelMix = COMMERCE_2024.map(([fuel, pct]) => ({ year: 2024, fuel, share_pct: pct })); const fuelMixSum = r2(COMMERCE_2024.reduce((a, b) => a + b[1], 0)); // ---------- Saved-analysis results ---------- const shares = annual.map((a) => a.hydro_share_pct); const yearsBelow60 = annual.filter((a) => a.hydro_share_pct < 60).map((a) => a.year); const minA = annual.reduce((a, b) => (b.hydro_share_pct < a.hydro_share_pct ? b : a)); const maxA = annual.reduce((a, b) => (b.hydro_share_pct > a.hydro_share_pct ? b : a)); const mean = (xs) => xs.reduce((a, b) => a + b, 0) / xs.length; const results = { generatedFrom: 'build.js', hydroShareSeries: annual.map((a) => ({ year: a.year, date: `${a.year}-07-01`, series: 'Hydro share of in-state net generation', hydro_share_pct: a.hydro_share_pct })), hydroShareSummary: { years: `${FIRST_YEAR}-${LAST_YEAR}`, min_share_pct: minA.hydro_share_pct, min_year: minA.year, max_share_pct: maxA.hydro_share_pct, max_year: maxA.year, mean_share_pct: r1(mean(shares)), mean_share_2001_2010_pct: r1(mean(annual.filter((a) => a.year <= 2010).map((a) => a.hydro_share_pct))), mean_share_2016_2025_pct: r1(mean(annual.filter((a) => a.year >= 2016).map((a) => a.hydro_share_pct))), years_below_60_pct: yearsBelow60, years_at_or_below_50_pct: annual.filter((a) => a.hydro_share_pct <= 50).map((a) => a.year), share_2023_pct: annual.find((a) => a.year === 2023).hydro_share_pct, share_2024_pct: annual.find((a) => a.year === 2024).hydro_share_pct, share_2025_pct: annual.find((a) => a.year === 2025).hydro_share_pct, hydro_2025_mwh: annual.find((a) => a.year === 2025).hydro_net_generation_mwh, total_2025_mwh: annual.find((a) => a.year === 2025).total_net_generation_mwh, }, hydroRank: annual.map((a) => ({ year: a.year, wa_hydro_rank_among_states: a.wa_hydro_rank_among_states, wa_share_of_us_hydro_pct: a.wa_share_of_us_hydro_pct, second_ranked_state: a.second_ranked_state, second_ranked_state_hydro_mwh: a.second_ranked_state_hydro_mwh, wa_hydro_mwh: a.hydro_net_generation_mwh })), stateHydroRank2025: stateRank, largestPlantsCapacity2025: capTop, largestPlantsGeneration2025: genTop, grandCoulee2025: { plant_code: 6163, nameplate_capacity_mw: r1(gcCap.nameplate_mw), nameplate_conventional_hydro_only_mw: r1(gcConvHydroNameplate), summer_capacity_mw: r1(gcCap.summer_mw), operable_generators: gcCap.generators, net_generation_2025_mwh: round0(gc.mwh), rank_by_nameplate: capTop.find((p) => p.plant_code === 6163)?.rank_by_nameplate ?? null, rank_by_summer_capacity: summerRank.get(6163), rank_by_net_generation_2025: gcGenRank, share_of_wa_2025_net_generation_pct: r1((100 * gc.mwh) / annual.find((a) => a.year === 2025).total_net_generation_mwh), }, supplyVsSales: supply.map((s) => ({ year: s.year, total_net_generation_mwh: s.total_net_generation_mwh, retail_sales_mwh: s.retail_sales_mwh, net_interstate_trade_mwh: s.net_interstate_trade_mwh, generation_to_retail_sales_ratio: s.generation_to_retail_sales_ratio, hydro_to_retail_sales_pct: s.hydro_to_retail_sales_pct })), generationCrossCheck: { max_abs_difference_mwh: Math.max(...genCrossCheck.map((c) => Math.abs(c.difference_mwh))), max_abs_difference_pct: Math.max(...genCrossCheck.map((c) => Math.abs(c.difference_mwh) / c.historical_tables_mwh * 100)), years: genCrossCheck.length }, utilityFuelMix2024: { rows: fuelMix, sum_of_shares_pct: fuelMixSum }, }; // ---------- Write files ---------- write('wa-generation-by-source.csv', csv(genBySource)); write('wa-hydro-annual.csv', csv(annual)); write('state-hydro-rank-2025.csv', csv(stateRank)); write('wa-supply-disposition.csv', csv(supply)); write('largest-plants-capacity-2025.csv', csv(capTop)); write('largest-plants-generation-2025.csv', csv(genTop)); write('wa-utility-fuel-mix-2024.csv', csv(fuelMix)); write('results.json', JSON.stringify(results, null, 2) + '\n'); console.log(JSON.stringify({ hydroShareSummary: results.hydroShareSummary, grandCoulee2025: results.grandCoulee2025 }, null, 2)); console.table(capTop.map((p) => ({ n: p.rank_by_nameplate, s: p.rank_by_summer_capacity, name: p.plant_name, st: p.state, tech: p.primary_technology, np: p.nameplate_capacity_mw, su: p.summer_capacity_mw }))); console.table(genTop.map((p) => ({ n: p.rank_by_net_generation, name: p.plant_name, st: p.state, tech: p.primary_technology, mwh: p.net_generation_2025_mwh, cf: p.capacity_factor_pct }))); console.table(results.hydroRank.map((r) => [r.year, r.wa_hydro_rank_among_states, r.wa_share_of_us_hydro_pct, r.second_ranked_state])); console.table(results.supplyVsSales); console.log('commerce fuel mix sum', fuelMixSum);