"""AMR annual residential account bills, reproducible from pinned EIA workbooks. No month/year mixing. All dollar figures are nominal, not inflation adjusted. """ from pathlib import Path from decimal import Decimal as D, ROUND_HALF_UP import csv,json,collections,sys P=Path(__file__).resolve().parent sys.path.insert(0,str(P)) from xlsx_read import rows OUT=P/'data';OUT.mkdir(exist_ok=True) def fmt(x):return str(D(x).quantize(D('.000001'),rounding=ROUND_HALF_UP)) def save(n,rs): with (OUT/n).open('w',newline='')as f: w=csv.DictWriter(f,fieldnames=list(rs[0]));w.writeheader();w.writerows(rs) def rank(rs,key,x):return 1+sum(D(r[key])>D(x[key])for r in rs) def run(): names=json.loads((P/'state-names.json').read_text()) source=[];panel=[] for i,r in rows(P/'sources/sales-2010-2025.xlsx'): if not r.get('A','').isdigit():continue y=int(r['A']);st=r['B'] rev,sales,accounts=map(D,[r['C'],r['D'],r['E']]) assert min(rev,sales,accounts)>0,(y,st) price=rev/sales*100;use=sales*1000/accounts/12;bill=rev*1000/accounts/12 source.append(dict(year=y,state=st,revenue_thousand_usd=r['C'],sales_mwh=r['D'],accounts=r['E'],published_price_cents_kwh=r['F'],source_sheet='Total Electric Industry',source_row=i)) panel.append(dict(year=y,state=st,state_name=names[st]if st!='US'else'United States',accounts=int(accounts),revenue_thousand_usd=int(rev),sales_mwh=int(sales),monthly_use_kwh=fmt(use),effective_price_cents_kwh=fmt(price),monthly_bill_usd=fmt(bill),published_price_cents_kwh=r['F'])) years=sorted({r['year']for r in panel});assert years==list(range(2010,2026)) geos={r['state']for r in panel};assert len(geos)==52 and 'US'in geos checks=[] for y in years: rs=[r for r in panel if r['year']==y];assert len(rs)==52 assert len({r['state']for r in rs})==52 us=next(r for r in rs if r['state']=='US');states=[r for r in rs if r['state']!='US'] for k in ['accounts','sales_mwh','revenue_thousand_usd']: # Preserve producer total and residual, never silently redistribute rounding. residual=sum(r[k]for r in states)-us[k] checks.append(dict(year=y,measure=k,state_sum=sum(r[k]for r in states),published_us=us[k],sum_minus_us=residual)) latest=[dict(r)for r in panel if r['year']==2025];states=[r for r in latest if r['state']!='US'];us=next(r for r in latest if r['state']=='US') for r in latest: for key,out in [('monthly_bill_usd','bill_rank'),('effective_price_cents_kwh','price_rank'),('monthly_use_kwh','usage_rank')]:r[out]='Not ranked, U.S. total'if r['state']=='US'else rank(states,key,r) r['bill_vs_us_usd']=fmt(D(r['monthly_bill_usd'])-D(us['monthly_bill_usd'])) r['bill_vs_us_pct']=fmt((D(r['monthly_bill_usd'])/D(us['monthly_bill_usd'])-1)*100) lookup={(r['year'],r['state']):r for r in panel};changes=[] for st in sorted(geos): a=lookup[2024,st];b=lookup[2025,st];p0,p1=map(D,[a['effective_price_cents_kwh'],b['effective_price_cents_kwh']]);q0,q1=map(D,[a['monthly_use_kwh'],b['monthly_use_kwh']]);v0,v1=map(D,[a['monthly_bill_usd'],b['monthly_bill_usd']]) price_effect=(p1-p0)*(q0+q1)/200;use_effect=(q1-q0)*(p0+p1)/200 assert abs((price_effect+use_effect)-(v1-v0))