Openpyxl Financial Workbook Design - 01/08/2026 03:44 EDT
Publicada el 2026-08-01
Descripción de la oferta
from openpyxl import Workbook from openpyxl.styles import Font, PatternFill, Protection wb=Workbook() ws=wb.active; ws.title="Inputs" dash=wb.create_sheet("Dashboard") cf=wb.create_sheet("Cash Flow") fill=PatternFill("solid", fgColor="FFF2CC") hdr=PatternFill("solid", fgColor="D9EAD3") for s in [ws,dash,cf]: s["A1"]="Instructions"; s["A2"]="Enter values in yellow cells only. Gray/other cells contain formulas." s["A1"].font=Font(bold=True) items=[("Opening Balance",0),("Sales",0),("COGS",0),("Operating Expenses",0),("Financing Inflow",0),("Financing Outflow",0)] ws["A4"]="Item"; ws["B4"]="Amount" ws["A4"].fill=hdr; ws["B4"].fill=hdr r=5 for it,val in items: ws.cell(r,1).value=it c=ws.cell(r,2); c.value=val; c.fill=fill; c.protection=Protection(locked=False); r+=1 dash["A4"]="Metric"; dash["B4"]="Value" for c in ["A4","B4"]: dash[c].fill=hdr metrics=[("Revenue","=Inputs!B6"),("Operating Expenses","=SUM(Inputs!B7:B8)"),("Net Cash Movement","=Inputs!B6-Inputs!B7-Inputs!B8+Inputs!B9-Inputs!B10+SUM('Cash Flow'!B12:B20)"),("Ending Balance","=Inputs!B5+B7")] # correct refs dash["A5"]="Revenue";dash["B5"]="=Inputs!B6" dash["A6"]="Operating Expenses";dash["B6"]="=SUM(Inputs!B7:B8)" dash["A7"]="Net Cash Movement";dash["B7"]="=Inputs!B6-Inputs!B7-Inputs!B8+Inputs!B9-Inputs!B10+SUM('Cash Flow'!B12:B20)" dash["A8"]="Ending Balance";dash["B8"]="=Inputs!B5+B7" cf["A4"]="Cash Flow Item";cf["B4"]="Amount" cf["A4"].fill=hdr;cf["B4"].fill=hdr rows=[("Opening Balance","=Inputs!B5"),("Cash from Sales","=INDEX(Inputs!B:B,MATCH(\"Sales\",Inputs!A:A,0))"), ("COGS","=-INDEX(Inputs!B:B,MATCH(\"COGS\",Inputs!A:A,0))"), ("Operating Expenses","=-SUMIFS(Inputs!B:B,Inputs!A:A,\"Operating Expenses\")"), ("Financing Inflow","=INDEX(Inputs!B:B,MATCH(\"Financing Inflow\",Inputs!A:A,0))"), ("Financing Outflow","=-INDEX(Inputs!B:B,MATCH(\"Financing Outflow\",Inputs!A:A,0))"), ("Manual Adjustment 1",0),("Manual Adjustment 2",0),("Ending Balance","=SUM(B5:B12)")] rr=5 for name,val in rows: cf.cell(rr,1).value=name cell=cf.cell(rr,2) cell.value=val if isinstance(val,int): cell.fill=fill; cell.protection=Protection(locked=False) rr+=1 for sh in [ws,dash,cf]: for row in sh.iter_rows(): for cell in row: if cell.fill.fgColor.rgb!="00FFF2CC": cell.protection=Protection(locked=True) sh.protection.sheet=True path="/mnt/data/Financial_Budget_Cashflow_Template.xlsx" wb.save(path) print(path)
Skills
Fuente original: freelancer