#!/usr/bin/env python3 """Generate full SupplierPulse QAF comparison Excel + data for 1-pager.""" import sys, re, json, statistics, warnings import openpyxl from openpyxl.utils import column_index_from_string as CI, get_column_letter from openpyxl.styles import Font, PatternFill, Alignment, Border, Side warnings.filterwarnings("ignore") METRICS = [('W','Material total','Material'),('Y','Raw material surcharge','Material'), ('BB','Manufacturing total','Manufacturing'),('AS','Personnel (Lohn)','Manufacturing'), ('AT','Machine','Manufacturing'),('AV','FEK','Manufacturing'),('BD','Scrap (Mfg)','Manufacturing'), ('BF','HK (Total prod. costs)','HK'),('DI','Surcharges','Surcharges'), ('DJ','Quotation Price','Price'),('DG','Total Costs','Total Costs')] ROW=41 MASTERS={'SBM_Matrix','SBM_Dropdown','Preise 02','Serie 02'} def num(v): try: return float(v) except: return None def natkey(s): m=re.match(r'(\d+)',s); n=int(m.group(1)) if m else 999 return (n, s) def extract(fn): wb=openpyxl.load_workbook(fn,data_only=True) dets=sorted([s for s in wb.sheetnames if '_2' in s and s not in MASTERS], key=natkey) data={} for t in dets: ws=wb[t] rec={'part':ws['C15'].value or ''} for col,head,ber in METRICS: rec[head]=num(ws.cell(ROW,CI(col)).value) rec['machine_rate']=num(ws.cell(15,CI('AT')).value) rec['ineff']=num(ws.cell(15,CI('AS')).value) data[t]=rec # input card ic={} wsI=wb['INPUT'] for r in range(20,60): lab=wsI.cell(r,2).value if lab not in (None,''): ic[f"C{r}|{str(lab).strip()}"]=wsI.cell(r,3).value wb.close() return data, ic B,BIC = extract("basis_QAF.xlsm") R,RIC = extract("repricing_QAF.xlsm") common=[t for t in B if t in R] # global machine lever ratios=[R[t]['machine_rate']/B[t]['machine_rate'] for t in common if B[t]['machine_rate'] and R[t]['machine_rate']] GLOB=statistics.median(ratios) if ratios else None def flag(t): b,r=B[t]['machine_rate'],R[t]['machine_rate'] if b and r and GLOB and abs((r/b)-GLOB)/GLOB>0.05: return "PART-SPECIFIC" return "" # ---------- build rows ---------- rows=[] for t in common: pf=flag(t) for col,head,ber in METRICS: b=B[t][head]; r=R[t][head] if b is None and r is None: continue d=(r-b) if (b is not None and r is not None) else None dp=(d/b*100) if (d is not None and b not in (None,0)) else None rows.append([t,B[t]['part'] or R[t]['part'],f"{col}{ROW}",head,ber,b,r,d, (dp/100 if dp is not None else None), pf if head in ('Machine','Manufacturing total','FEK','HK (Total prod. costs)','Quotation Price','Total Costs') else ""]) # ---------- Excel ---------- wb=openpyxl.Workbook() thin=Side(style='thin',color='D0D0D0') border=Border(left=thin,right=thin,top=thin,bottom=thin) HEAD=PatternFill('solid',fgColor='037493'); HFONT=Font(color='FFFFFF',bold=True) RED=PatternFill('solid',fgColor='F8C9C9'); ORA=PatternFill('solid',fgColor='FCE3C0') GRN=PatternFill('solid',fgColor='CDEBD3'); YEL=PatternFill('solid',fgColor='FFF3B0') ws=wb.active; ws.title="Vergleich" hdr=["Reiter","Bauteil","Zelle","Position","Bereich","Basis","RePricing","Delta","Delta %","Flag"] ws.append(hdr) for c in range(1,len(hdr)+1): cell=ws.cell(1,c); cell.fill=HEAD; cell.font=HFONT; cell.alignment=Alignment(horizontal='center') for row in rows: ws.append(row) rr=ws.max_row ws.cell(rr,9).number_format='+0.0%;-0.0%' for cc in (6,7,8): ws.cell(rr,cc).number_format='0.0000' dp=row[8] if dp is not None: cell=ws.cell(rr,9) if dp>=1.0: cell.fill=RED elif dp>=0.3: cell.fill=ORA elif dp<0: cell.fill=GRN if row[9]=="PART-SPECIFIC": ws.cell(rr,10).fill=YEL for col,w in zip("ABCDEFGHIJ",[12,26,7,22,14,11,11,11,9,15]): ws.column_dimensions[col].width=w ws.freeze_panes="A2" ws.auto_filter.ref=f"A1:J{ws.max_row}" # Treiber sheet wt=wb.create_sheet("Treiber (INPUT)") wt.append(["INPUT","Beschreibung","Basis","RePricing","Veränderung","Wirkung"]) for c in range(1,7): wt.cell(1,c).fill=HEAD; wt.cell(1,c).font=HFONT WIRK={'C20':'Importmaterial USD','C21':'CN-Kaufteile','C22':'Gemeinkosten Fertigung', 'C23':'Gemeinkosten Material','C24':'Gewinn Fertigung','C25':'Gewinn Material', 'C30':'Material-GK','C31':'Schrott','C32':'Sekof-Zins','C35':'ABS-Granulat','C36':'ABS-PC', 'C37':'PA-Granulat','C38':'PP-Granulat','C40':'Lohn Serbien','C42':'Lohn Deutschland','C43':'Lohn Ungarn'} for k in BIC: code=k.split('|')[0]; lab=k.split('|')[1] a=BIC.get(k); b=RIC.get(k) try: fa,fb=float(a),float(b) if abs(fa-fb)>1e-9: chg=(fb/fa-1) if fa!=0 else None wt.append([code,lab,fa,fb,chg,WIRK.get(code,'')]) wt.cell(wt.max_row,5).number_format='+0.0%;-0.0%' if chg is not None and chg>=0.5: wt.cell(wt.max_row,5).fill=RED elif chg is not None and chg>0: wt.cell(wt.max_row,5).fill=ORA elif chg is not None and chg<0: wt.cell(wt.max_row,5).fill=GRN except: if a!=b: wt.append([code,lab,a,b,'',WIRK.get(code,'')]) for col,w in zip("ABCDEF",[8,28,12,12,13,24]): wt.column_dimensions[col].width=w wt.freeze_panes="A2" # Summary sheet — package totals + by-area wsum=wb.create_sheet("Zusammenfassung") tot_b=sum(B[t]['Quotation Price'] or 0 for t in common) tot_r=sum(R[t]['Quotation Price'] or 0 for t in common) wsum.append(["Kennzahl","Basis","RePricing","Delta","Delta %"]) for c in range(1,6): wsum.cell(1,c).fill=HEAD; wsum.cell(1,c).font=HFONT def addrow(name,fnc): b=sum(B[t][fnc] or 0 for t in common); r=sum(R[t][fnc] or 0 for t in common) wsum.append([name,b,r,r-b,(r-b)/b if b else None]) wsum.cell(wsum.max_row,5).number_format='+0.0%;-0.0%' for cc in (2,3,4): wsum.cell(wsum.max_row,cc).number_format='0.00' addrow("Angebotspreis gesamt (Σ Quotation Price)","Quotation Price") addrow("Material gesamt","Material total") addrow("Fertigung gesamt","Manufacturing total") addrow("davon Maschine","Machine") addrow("davon Personal","Personnel (Lohn)") addrow("Herstellkosten gesamt","HK (Total prod. costs)") addrow("Zuschläge gesamt","Surcharges") for col,w in zip("ABCDE",[36,12,12,12,10]): wsum.column_dimensions[col].width=w wsum.freeze_panes="A2" # Anomalies sheet — biggest movers + part-specific wa=wb.create_sheet("Anomalien") wa.append(["Top-Preistreiber je Bauteil (nach Δ Angebotspreis, absolut)"]) wa.append(["Reiter","Bauteil","Basis","RePricing","Delta","Delta %","Flag"]) for c in range(1,8): wa.cell(2,c).fill=HEAD; wa.cell(2,c).font=HFONT movers=sorted(common,key=lambda t:abs((R[t]['Quotation Price'] or 0)-(B[t]['Quotation Price'] or 0)),reverse=True)[:20] for t in movers: b=B[t]['Quotation Price'];r=R[t]['Quotation Price'] wa.append([t,B[t]['part'],b,r,(r or 0)-(b or 0),((r-b)/b if b else None),flag(t)]) wa.cell(wa.max_row,6).number_format='+0.0%;-0.0%' if flag(t): wa.cell(wa.max_row,7).fill=YEL for col,w in zip("ABCDEFG",[12,26,11,11,11,9,14]): wa.column_dimensions[col].width=w wb.save("QAF_Vergleich_G60_DP.xlsx") # stats for 1-pager stats=dict( reiter=len(common), tot_b=tot_b, tot_r=tot_r, tot_pct=(tot_r-tot_b)/tot_b, mat_b=sum(B[t]['Material total'] or 0 for t in common), mat_r=sum(R[t]['Material total'] or 0 for t in common), mach_b=sum(B[t]['Machine'] or 0 for t in common), mach_r=sum(R[t]['Machine'] or 0 for t in common), pers_b=sum(B[t]['Personnel (Lohn)'] or 0 for t in common), pers_r=sum(R[t]['Personnel (Lohn)'] or 0 for t in common), sur_b=sum(B[t]['Surcharges'] or 0 for t in common), sur_r=sum(R[t]['Surcharges'] or 0 for t in common), glob=GLOB, partspec=[t for t in common if flag(t)], top=[(t,B[t]['part'],B[t]['Quotation Price'],R[t]['Quotation Price']) for t in movers[:8]], ) json.dump(stats,open("stats.json","w"),indent=2) print("OK rows=",len(rows),"reiter=",len(common)) print(f"PACKAGE TOTAL Quotation: {tot_b:.2f} -> {tot_r:.2f} ({(tot_r-tot_b)/tot_b*100:+.1f}%)") print(f" Material {stats['mat_b']:.1f}->{stats['mat_r']:.1f} Machine {stats['mach_b']:.1f}->{stats['mach_r']:.1f} Surcharges {stats['sur_b']:.1f}->{stats['sur_r']:.1f}") print("part-specific:",stats['partspec'])