Spaces:
Sleeping
Sleeping
| #!/usr/bin/env python3 | |
| """ | |
| builders/build_excel.py -- Enhanced Excel workbook builder. | |
| Reads the skill's original builder and adds DuPont, SWOT, and Indian unit support. | |
| """ | |
| import json, sys, datetime | |
| from pathlib import Path | |
| try: | |
| from openpyxl import Workbook | |
| from openpyxl.styles import Font, PatternFill, Alignment, Border, Side | |
| from openpyxl.utils import get_column_letter | |
| from openpyxl.chart import BarChart, LineChart, Reference | |
| except ImportError: | |
| print("ERROR: pip install openpyxl", file=sys.stderr); sys.exit(1) | |
| # -- Style constants ----------------------------------------------------------- | |
| HDR_FILL = PatternFill("solid", fgColor="1F4E78") | |
| HDR_FONT = Font(name="Calibri", size=11, bold=True, color="FFFFFF") | |
| SEC_FILL = PatternFill("solid", fgColor="D9E1F2") | |
| SEC_FONT = Font(name="Calibri", size=11, bold=True, color="1F4E78") | |
| LBL_FONT = Font(name="Calibri", size=11) | |
| TTL_FONT = Font(name="Calibri", size=16, bold=True, color="1F4E78") | |
| DAT_FONT = Font(name="Calibri", size=11) | |
| GRN_FILL = PatternFill("solid", fgColor="C6EFCE") | |
| RED_FILL = PatternFill("solid", fgColor="FFC7CE") | |
| YLW_FILL = PatternFill("solid", fgColor="FFEB9C") | |
| THIN = Border( | |
| left=Side(style="thin", color="BFBFBF"), right=Side(style="thin", color="BFBFBF"), | |
| top=Side(style="thin", color="BFBFBF"), bottom=Side(style="thin", color="BFBFBF"), | |
| ) | |
| CENTER = Alignment(horizontal="center", vertical="center", wrap_text=True) | |
| LEFT = Alignment(horizontal="left", vertical="center", wrap_text=True) | |
| RIGHT = Alignment(horizontal="right", vertical="center") | |
| FMT_MONEY = '#,##0;(#,##0);"--"' | |
| FMT_DEC = '#,##0.00;(#,##0.00);"--"' | |
| FMT_PCT = '0.0%;(0.0%);"--"' | |
| FMT_RATIO = '0.00;(0.00);"--"' | |
| FMT_DAYS = '0.0;"--"' | |
| # -- Helpers ------------------------------------------------------------------- | |
| def _safe(arr, i, default=None): | |
| try: return arr[i] if arr and arr[i] is not None else default | |
| except: return default | |
| def _safe_div(num, den, default=None): | |
| try: | |
| if num is None or den is None: return default | |
| if den == 0: return default | |
| return num / den | |
| except: return default | |
| def _hdr(ws, row, cols, start=1): | |
| for i, h in enumerate(cols): | |
| c = ws.cell(row=row, column=start+i, value=h) | |
| c.font, c.fill, c.alignment, c.border = HDR_FONT, HDR_FILL, CENTER, THIN | |
| def _sec(ws, row, label, ncols, start=1): | |
| ws.cell(row=row, column=start, value=label).font = SEC_FONT | |
| ws.cell(row=row, column=start).fill = SEC_FILL | |
| ws.cell(row=row, column=start).alignment = LEFT | |
| for col in range(start+1, start+ncols): | |
| ws.cell(row=row, column=col).fill = SEC_FILL | |
| def _data_row(ws, row, label, values, fmt=FMT_MONEY, start=1): | |
| c = ws.cell(row=row, column=start, value=label) | |
| c.font, c.alignment, c.border = LBL_FONT, LEFT, THIN | |
| for i, v in enumerate(values): | |
| cell = ws.cell(row=row, column=start+1+i, value=v if v is not None else "--") | |
| cell.font, cell.alignment, cell.border = DAT_FONT, RIGHT, THIN | |
| if v is not None and isinstance(v, (int, float)): | |
| cell.number_format = fmt | |
| def _title(ws, title, subtitle=None): | |
| ws.cell(row=1, column=1, value=title).font = TTL_FONT | |
| if subtitle: | |
| ws.cell(row=2, column=1, value=subtitle).font = Font(name="Calibri", size=11, italic=True, color="595959") | |
| def _widths(ws, widths): | |
| for i, w in enumerate(widths): | |
| ws.column_dimensions[get_column_letter(i+1)].width = w | |
| def _freeze(ws, hdr_row): | |
| ws.freeze_panes = ws.cell(row=hdr_row+1, column=2) | |
| def _last(arr): | |
| if not arr: return None | |
| for v in reversed(arr): | |
| if v is not None: return v | |
| return None | |
| # -- Statement section helper -------------------------------------------------- | |
| def _stmt_section(ws, row, title, items, data_dict, years, fmt=FMT_MONEY): | |
| _sec(ws, row, title, 1+len(years)) | |
| row += 1 | |
| for label, key in items: | |
| vals = data_dict.get(key, [None]*len(years)) | |
| vals = (vals + [None]*len(years))[:len(years)] | |
| _data_row(ws, row, label, vals, fmt) | |
| row += 1 | |
| return row | |
| # -- Sheet builders ------------------------------------------------------------ | |
| def _summary_sheet(wb, data): | |
| ws = wb.create_sheet("Summary") | |
| co = data.get("company", {}) | |
| units_label = data.get("units_label", data.get("units", "Crores")) | |
| currency = data.get("currency", co.get("currency", "INR")) | |
| _title(ws, f"{co.get('name','')} ({co.get('ticker','')}) -- Financial Analysis", | |
| f"Report date: {co.get('report_date', datetime.date.today().isoformat())}") | |
| r = 4 | |
| for label, val in [ | |
| ("Company", co.get("name","")), | |
| ("Ticker", co.get("ticker","")), | |
| ("Exchange", co.get("exchange","")), | |
| ("Industry", co.get("industry","")), | |
| ("Sector", co.get("sector","")), | |
| ("Currency", currency), | |
| ("Reporting Units", units_label), | |
| ("Fiscal Year End", co.get("fiscal_year_end","")), | |
| ("Analysis Period", f"{len(data.get('years',[]))} years ({', '.join(data.get('years',[]))})"), | |
| ("Share Price", co.get("share_price")), | |
| ("Market Cap", co.get("market_cap")), | |
| ("Data Source", data.get("source", "Yahoo Finance (yahooquery)")), | |
| ("Models Used", ", ".join(data.get("models_used", []) or ["None"])), | |
| ("Warnings", " | ".join(data.get("warnings", []))), | |
| ]: | |
| ws.cell(row=r, column=1, value=label).font = LBL_FONT | |
| ws.cell(row=r, column=2, value=val).font = DAT_FONT | |
| r += 1 | |
| _widths(ws, [28, 50]) | |
| def _income_sheet(wb, data): | |
| ws = wb.create_sheet("Income Statement") | |
| co, years = data.get("company",{}), data.get("years",[]) | |
| units_label = data.get("units_label", data.get("units", "Crores")) | |
| currency = data.get("currency", co.get("currency", "INR")) | |
| _title(ws, f"Income Statement -- {co.get('ticker','')}", | |
| f"All figures in {units_label}") | |
| _hdr(ws, 4, [f"({units_label})"] + years) | |
| inc = data.get("income_statement", {}) | |
| r = _stmt_section(ws, 5, "Revenue & Profitability", [ | |
| ("Revenue", "revenue"), | |
| ("Cost of Revenue", "cost_of_revenue"), | |
| ("Gross Profit", "gross_profit"), | |
| ("Operating Expenses", "operating_expenses"), | |
| ("Operating Income (EBIT)","operating_income"), | |
| ("D&A", "depreciation_amort"), | |
| ("EBITDA", "ebitda"), | |
| ("Interest Expense", "interest_expense"), | |
| ("Pretax Income", "pretax_income"), | |
| ("Tax Expense", "tax_expense"), | |
| ("Net Income", "net_income"), | |
| ], inc, years) | |
| r += 1 | |
| _sec(ws, r, "Per-Share Data", 1+len(years)); r += 1 | |
| _data_row(ws, r, "Diluted EPS", inc.get("diluted_eps",[None]*len(years))[:len(years)], FMT_DEC); r += 1 | |
| _data_row(ws, r, "Diluted Shares (M)", inc.get("diluted_shares",[None]*len(years))[:len(years)], FMT_MONEY) | |
| # Add revenue bar chart | |
| if len(years) >= 2: | |
| chart = BarChart() | |
| chart.title = f"Revenue Trend ({units_label})" | |
| chart.style = 10 | |
| chart.y_axis.title = units_label | |
| rev_row = 6 | |
| data_ref = Reference(ws, min_col=2, max_col=1+len(years), min_row=rev_row, max_row=rev_row) | |
| cats_ref = Reference(ws, min_col=2, max_col=1+len(years), min_row=4, max_row=4) | |
| chart.add_data(data_ref) | |
| chart.set_categories(cats_ref) | |
| chart.shape = 4 | |
| ws.add_chart(chart, f"A{r+3}") | |
| _widths(ws, [34]+[16]*len(years)); _freeze(ws, 4) | |
| def _balance_sheet(wb, data): | |
| ws = wb.create_sheet("Balance Sheet") | |
| co, years = data.get("company",{}), data.get("years",[]) | |
| units_label = data.get("units_label", data.get("units", "Crores")) | |
| _title(ws, f"Balance Sheet -- {co.get('ticker','')}", | |
| f"As of fiscal year-end. {units_label}") | |
| _hdr(ws, 4, [f"({units_label})"] + years) | |
| bs = data.get("balance_sheet", {}) | |
| r = 5 | |
| for sec_title, items in [ | |
| ("Current Assets", [ | |
| ("Cash & Equivalents","cash_and_equivalents"),("Short-term Investments","short_term_investments"), | |
| ("Receivables","receivables"),("Inventory","inventory"),("Other Current Assets","other_current_assets"), | |
| ("Total Current Assets","total_current_assets"), | |
| ]), | |
| ("Non-Current Assets", [ | |
| ("PP&E (Net)","ppe_net"),("Goodwill","goodwill"),("Intangibles","intangibles"), | |
| ("Other LT Assets","other_long_term_assets"),("Total Assets","total_assets"), | |
| ]), | |
| ("Current Liabilities", [ | |
| ("Accounts Payable","accounts_payable"),("Short-term Debt","short_term_debt"), | |
| ("Other Current Liabilities","other_current_liabilities"), | |
| ("Total Current Liabilities","total_current_liabilities"), | |
| ]), | |
| ("Non-Current Liabilities & Equity", [ | |
| ("Long-term Debt","long_term_debt"),("Other LT Liabilities","other_long_term_liabilities"), | |
| ("Total Liabilities","total_liabilities"),("Total Equity","total_equity"), | |
| ("Total Liabilities + Equity","total_liab_and_equity"), | |
| ]), | |
| ]: | |
| r = _stmt_section(ws, r, sec_title, items, bs, years) | |
| _widths(ws, [36]+[16]*len(years)); _freeze(ws, 4) | |
| def _cashflow_sheet(wb, data): | |
| ws = wb.create_sheet("Cash Flow") | |
| co, years = data.get("company",{}), data.get("years",[]) | |
| units_label = data.get("units_label", data.get("units", "Crores")) | |
| _title(ws, f"Cash Flow Statement -- {co.get('ticker','')}", | |
| f"All figures in {units_label}") | |
| _hdr(ws, 4, [f"({units_label})"] + years) | |
| cf = data.get("cash_flow", {}) | |
| r = 5 | |
| for sec_title, items in [ | |
| ("Operating Activities", [ | |
| ("Net Income","net_income"),("D&A","depreciation_amort"),("Stock-Based Comp","stock_based_comp"), | |
| ("Working Capital Changes","working_capital_changes"),("Cash from Operations","cash_from_operations"), | |
| ]), | |
| ("Investing Activities", [ | |
| ("Capital Expenditures","capex"),("Acquisitions","acquisitions"),("Cash from Investing","cash_from_investing"), | |
| ]), | |
| ("Financing Activities", [ | |
| ("Debt Issuance/(Repayment)","debt_issuance_repayment"),("Buybacks","stock_buybacks"), | |
| ("Dividends Paid","dividends_paid"),("Cash from Financing","cash_from_financing"), | |
| ]), | |
| ("Summary", [ | |
| ("Net Change in Cash","net_change_in_cash"),("Free Cash Flow","free_cash_flow"), | |
| ]), | |
| ]: | |
| r = _stmt_section(ws, r, sec_title, items, cf, years) | |
| _widths(ws, [36]+[16]*len(years)); _freeze(ws, 4) | |
| RATIO_SECTIONS = [ | |
| ("Profitability", [ | |
| ("gross_margin","Gross Margin",FMT_PCT),("operating_margin","Operating Margin",FMT_PCT), | |
| ("ebitda_margin","EBITDA Margin",FMT_PCT),("net_margin","Net Margin",FMT_PCT), | |
| ("roe","ROE",FMT_PCT),("roa","ROA",FMT_PCT),("roic","ROIC",FMT_PCT), | |
| ]), | |
| ("Liquidity", [ | |
| ("current_ratio","Current Ratio",FMT_RATIO),("quick_ratio","Quick Ratio",FMT_RATIO), | |
| ("cash_ratio","Cash Ratio",FMT_RATIO), | |
| ]), | |
| ("Solvency / Leverage", [ | |
| ("debt_to_equity","Debt-to-Equity",FMT_RATIO),("debt_to_assets","Debt-to-Assets",FMT_RATIO), | |
| ("net_debt_to_ebitda","Net Debt/EBITDA",FMT_RATIO),("interest_coverage","Interest Coverage",FMT_RATIO), | |
| ]), | |
| ("Efficiency", [ | |
| ("asset_turnover","Asset Turnover",FMT_RATIO),("inventory_turnover","Inventory Turnover",FMT_RATIO), | |
| ("days_inventory","Days Inventory (DIO)",FMT_DAYS),("days_sales_outstanding","DSO",FMT_DAYS), | |
| ("days_payables","DPO",FMT_DAYS),("cash_conversion_cycle","Cash Conversion Cycle",FMT_DAYS), | |
| ]), | |
| ("Cash Flow Quality", [ | |
| ("ocf_to_net_income","OCF/Net Income",FMT_RATIO),("fcf_margin","FCF Margin",FMT_PCT), | |
| ("fcf_conversion","FCF Conversion",FMT_RATIO),("capex_intensity","Capex/Revenue",FMT_PCT), | |
| ]), | |
| ] | |
| def _ratios_sheet(wb, data): | |
| ws = wb.create_sheet("Ratios") | |
| years = data.get("years", []) | |
| _title(ws, f"Financial Ratios -- {data.get('company',{}).get('ticker','')}", "5-year trend") | |
| _hdr(ws, 4, ["Ratio"] + years) | |
| ratios = data.get("ratios", {}) | |
| r = 5 | |
| for sec_name, items in RATIO_SECTIONS: | |
| _sec(ws, r, sec_name, 1+len(years)); r += 1 | |
| for key, label, fmt in items: | |
| vals = ratios.get(key, [None]*len(years)) | |
| vals = (vals + [None]*len(years))[:len(years)] | |
| _data_row(ws, r, label, vals, fmt); r += 1 | |
| _widths(ws, [40]+[14]*len(years)); _freeze(ws, 4) | |
| def _dupont_sheet(wb, data): | |
| ws = wb.create_sheet("DuPont Analysis") | |
| years = data.get("years", []) | |
| _title(ws, "DuPont Decomposition", "ROE = Net Margin × Asset Turnover × Equity Multiplier") | |
| _hdr(ws, 4, ["Component"] + years) | |
| dp = data.get("dupont", {}) | |
| r = 5 | |
| for label, key, fmt in [ | |
| ("Net Margin", "net_margin", FMT_PCT), | |
| ("× Asset Turnover", "asset_turnover", FMT_RATIO), | |
| ("× Equity Multiplier","equity_multiplier",FMT_RATIO), | |
| ("= ROE (DuPont)", "roe_computed", FMT_PCT), | |
| ("ROE (Reported)", "roe_reported", FMT_PCT), | |
| ]: | |
| vals = dp.get(key, [None]*len(years)) | |
| vals = (vals + [None]*len(years))[:len(years)] | |
| _data_row(ws, r, label, vals, fmt) | |
| r += 1 | |
| _widths(ws, [28]+[14]*len(years)) | |
| def _growth_sheet(wb, data): | |
| ws = wb.create_sheet("Growth Metrics") | |
| years = data.get("years", []) | |
| yoy_hdrs = [f"{years[i]} vs {years[i-1]}" for i in range(1, len(years))] if len(years) > 1 else [] | |
| _title(ws, "Growth Metrics", "YoY growth and CAGRs") | |
| _hdr(ws, 4, ["Metric"] + yoy_hdrs + ["CAGR"]) | |
| g = data.get("growth_metrics", {}) | |
| r = 5 | |
| for label, yoy_key, cagr_key in [ | |
| ("Revenue", "revenue_yoy", "revenue_cagr_5y"), | |
| ("Operating Income","operating_income_yoy","operating_income_cagr_5y"), | |
| ("Net Income", "net_income_yoy", "net_income_cagr_5y"), | |
| ("Diluted EPS", "eps_yoy", "eps_cagr_5y"), | |
| ("Free Cash Flow", "fcf_yoy", "fcf_cagr_5y"), | |
| ]: | |
| yoy = g.get(yoy_key, [])[1:] # skip first None | |
| cagr = g.get(cagr_key) | |
| vals = (yoy + [None]*len(yoy_hdrs))[:len(yoy_hdrs)] + [cagr] | |
| _data_row(ws, r, label, vals, FMT_PCT); r += 1 | |
| _widths(ws, [28]+[16]*(len(yoy_hdrs)+1)) | |
| def _peers_sheet(wb, data): | |
| ws = wb.create_sheet("Peer Comparison") | |
| co = data.get("company", {}) | |
| _title(ws, f"Peer Comparison -- {co.get('ticker','')}", "Latest fiscal year") | |
| cols = ["Company","Ticker","Revenue","Gross Margin","Op Margin","Net Margin", | |
| "ROE","ROIC","D/E","Current Ratio","FCF Margin","P/E","EV/EBITDA","Rationale"] | |
| _hdr(ws, 4, cols) | |
| r = 5 | |
| years = data.get("years", []) | |
| rat = data.get("ratios", {}); inc = data.get("income_statement", {}) | |
| val = data.get("valuation_snapshot", {}) | |
| # Target row | |
| target = [ | |
| co.get("name",""), co.get("ticker",""), | |
| _last(inc.get("revenue",[])), _last(rat.get("gross_margin",[])), | |
| _last(rat.get("operating_margin",[])), _last(rat.get("net_margin",[])), | |
| _last(rat.get("roe",[])), _last(rat.get("roic",[])), | |
| _last(rat.get("debt_to_equity",[])), _last(rat.get("current_ratio",[])), | |
| _last(rat.get("fcf_margin",[])), val.get("pe_ttm"), val.get("ev_ebitda"), | |
| "Target company", | |
| ] | |
| fmts = ["","","money","pct","pct","pct","pct","pct","ratio","ratio","pct","ratio","ratio",""] | |
| _write_peer_row(ws, r, target, fmts, is_target=True); r += 1 | |
| for p in data.get("peers", []): | |
| peer_row = [ | |
| p.get("name",""), p.get("ticker",""), p.get("revenue"), | |
| p.get("gross_margin"), p.get("operating_margin"), p.get("net_margin"), | |
| p.get("roe"), p.get("roic"), p.get("debt_to_equity"), p.get("current_ratio"), | |
| p.get("fcf_margin"), p.get("pe_ttm"), p.get("ev_ebitda"), p.get("rationale",""), | |
| ] | |
| _write_peer_row(ws, r, peer_row, fmts); r += 1 | |
| _widths(ws, [24,10,16,12,12,12,10,10,10,12,12,10,12,50]) | |
| def _write_peer_row(ws, row, values, fmts, is_target=False): | |
| fmt_map = {"money":FMT_MONEY,"pct":FMT_PCT,"ratio":FMT_RATIO} | |
| fill = PatternFill("solid", fgColor="FFF2CC") if is_target else None | |
| for i, v in enumerate(values): | |
| c = ws.cell(row=row, column=i+1, value=v if v is not None else "--") | |
| c.border = THIN | |
| c.font = Font(name="Calibri", size=11, bold=is_target) | |
| if fill: c.fill = fill | |
| fmt = fmts[i] if i < len(fmts) else "" | |
| if fmt in fmt_map and isinstance(v, (int, float)): | |
| c.number_format = fmt_map[fmt] | |
| c.alignment = RIGHT | |
| else: | |
| c.alignment = LEFT | |
| def _swot_sheet(wb, data): | |
| ws = wb.create_sheet("SWOT Analysis") | |
| _title(ws, "SWOT Analysis", "Strengths, Weaknesses, Opportunities, Threats") | |
| swot = data.get("swot", {}) | |
| r = 4 | |
| colors = {"Strengths":"C6EFCE","Weaknesses":"FFC7CE","Opportunities":"DDEBF7","Threats":"FFEB9C"} | |
| for category, key in [("Strengths","strengths"),("Weaknesses","weaknesses"), | |
| ("Opportunities","opportunities"),("Threats","threats")]: | |
| fill = PatternFill("solid", fgColor=colors[category]) | |
| hdr_cell = ws.cell(row=r, column=1, value=category) | |
| hdr_cell.font = Font(name="Calibri", size=12, bold=True) | |
| hdr_cell.fill = fill | |
| r += 1 | |
| for item in swot.get(key, []): | |
| c = ws.cell(row=r, column=1, value=f"• {item}") | |
| c.font = Font(name="Calibri", size=11) | |
| c.alignment = Alignment(horizontal="left", vertical="top", wrap_text=True) | |
| ws.row_dimensions[r].height = 32 | |
| r += 1 | |
| r += 1 | |
| _widths(ws, [90]) | |
| def _dcf_sheet(wb, data): | |
| ws = wb.create_sheet("DCF Valuation Model") | |
| _title(ws, "Interactive Discounted Cash Flow (DCF)", "Change the yellow highlighted cells to update assumptions") | |
| currency = data.get("currency", "USD") | |
| units = data.get("company", {}).get("units", "Millions") | |
| # 1. Historical data | |
| rev_hist = data.get("income_statement", {}).get("revenue", []) | |
| fcf_hist = data.get("cash_flow", {}).get("free_cash_flow", []) | |
| last_rev = _last(rev_hist) or 0 | |
| last_fcf = _last(fcf_hist) or 0 | |
| # Default assumptions | |
| default_wacc = 0.09 | |
| default_tg = 0.025 | |
| # Historical FCF Margin & Growth | |
| rev_cagr = data.get("growth_metrics", {}).get("revenue_cagr_5y") | |
| if rev_cagr is None: | |
| rev_cagr = 0.05 | |
| rev_cagr = max(0.02, min(0.15, rev_cagr)) # Cap between 2% and 15% | |
| fcf_margins = [_safe_div(f, r) for f, r in zip(fcf_hist, rev_hist) if f is not None and r is not None and r != 0] | |
| avg_fcf_margin = sum(fcf_margins[-3:]) / len(fcf_margins[-3:]) if len(fcf_margins) > 0 else 0.10 | |
| shares_out = _last(data.get("income_statement", {}).get("diluted_shares", [])) or 1 | |
| if shares_out <= 0: shares_out = 1 # Prevent division by zero | |
| net_debt = _last(data.get("net_debt", [])) or 0 | |
| # --- Assumptions Block --- | |
| r = 4 | |
| _sec(ws, r, "1. Model Assumptions", 3) | |
| ws.cell(row=r, column=4, value="Yellow cells are editable").font = Font(italic=True, color="595959") | |
| r += 1 | |
| def add_assumption(row_idx, label, default_val, is_pct=True): | |
| ws.cell(row=row_idx, column=1, value=label).font = LBL_FONT | |
| c = ws.cell(row=row_idx, column=2, value=default_val) | |
| c.fill = YLW_FILL | |
| c.font = Font(name="Calibri", size=11, bold=True, color="1F4E78") | |
| c.border = THIN | |
| c.alignment = RIGHT | |
| c.number_format = FMT_PCT if is_pct else FMT_DEC | |
| return c.coordinate | |
| wacc_cell = add_assumption(r, "Weighted Average Cost of Capital (WACC)", default_wacc); r += 1 | |
| tg_cell = add_assumption(r, "Terminal Growth Rate", default_tg); r += 1 | |
| rev_gr_cell = add_assumption(r, "Projected Revenue Growth (Years 1-5)", rev_cagr); r += 1 | |
| fcf_m_cell = add_assumption(r, "Projected FCF Margin", avg_fcf_margin); r += 1 | |
| r += 1 | |
| # --- Projections Block --- | |
| _sec(ws, r, "2. Free Cash Flow Projections", 7) | |
| r += 1 | |
| _hdr(ws, r, ["Metric", "Year 0 (Current)", "Year 1", "Year 2", "Year 3", "Year 4", "Year 5"]) | |
| r += 1 | |
| # Revenue row | |
| ws.cell(row=r, column=1, value=f"Revenue ({currency} {units})").font = LBL_FONT | |
| ws.cell(row=r, column=2, value=last_rev).number_format = FMT_MONEY | |
| ws.cell(row=r, column=2).font = DAT_FONT | |
| rev_cells = [] | |
| for i in range(1, 6): | |
| c = ws.cell(row=r, column=2+i, value=f"={get_column_letter(2+i-1)}{r}*(1+{rev_gr_cell})") | |
| c.number_format = FMT_MONEY; c.font = DAT_FONT | |
| rev_cells.append(c.coordinate) | |
| r += 1 | |
| # FCF row | |
| ws.cell(row=r, column=1, value=f"Free Cash Flow ({currency} {units})").font = LBL_FONT | |
| ws.cell(row=r, column=2, value=last_fcf).number_format = FMT_MONEY | |
| ws.cell(row=r, column=2).font = DAT_FONT | |
| fcf_cells = [] | |
| for i in range(1, 6): | |
| c = ws.cell(row=r, column=2+i, value=f"={rev_cells[i-1]}*{fcf_m_cell}") | |
| c.number_format = FMT_MONEY; c.font = DAT_FONT | |
| fcf_cells.append(c.coordinate) | |
| r += 1 | |
| # Discount Factor | |
| ws.cell(row=r, column=1, value="Discount Factor").font = LBL_FONT | |
| ws.cell(row=r, column=2, value=1.0).number_format = FMT_DEC | |
| ws.cell(row=r, column=2).font = Font(italic=True, color="595959") | |
| df_cells = [] | |
| for i in range(1, 6): | |
| c = ws.cell(row=r, column=2+i, value=f"=1/((1+{wacc_cell})^{i})") | |
| c.number_format = FMT_DEC; c.font = Font(italic=True, color="595959") | |
| df_cells.append(c.coordinate) | |
| r += 1 | |
| # PV of FCF | |
| ws.cell(row=r, column=1, value="Present Value of FCF").font = Font(name="Calibri", bold=True) | |
| ws.cell(row=r, column=2, value="--").alignment = RIGHT | |
| pv_fcf_cells = [] | |
| for i in range(1, 6): | |
| c = ws.cell(row=r, column=2+i, value=f"={fcf_cells[i-1]}*{df_cells[i-1]}") | |
| c.number_format = FMT_MONEY; c.font = Font(name="Calibri", bold=True) | |
| pv_fcf_cells.append(c.coordinate) | |
| sum_pv_fcf_cell = f"SUM({pv_fcf_cells[0]}:{pv_fcf_cells[-1]})" | |
| r += 2 | |
| # --- Valuation Bridge --- | |
| _sec(ws, r, "3. Valuation Bridge", 3) | |
| r += 1 | |
| # TV | |
| ws.cell(row=r, column=1, value="Terminal Value (TV)").font = LBL_FONT | |
| tv_formula = f"={fcf_cells[-1]}*(1+{tg_cell})/({wacc_cell}-{tg_cell})" | |
| c = ws.cell(row=r, column=2, value=tv_formula); c.number_format = FMT_MONEY; c.font = DAT_FONT | |
| tv_cell = c.coordinate | |
| r += 1 | |
| # PV of TV | |
| ws.cell(row=r, column=1, value="PV of Terminal Value").font = LBL_FONT | |
| pv_tv_formula = f"={tv_cell}*{df_cells[-1]}" | |
| c = ws.cell(row=r, column=2, value=pv_tv_formula); c.number_format = FMT_MONEY; c.font = DAT_FONT | |
| pv_tv_cell = c.coordinate | |
| r += 1 | |
| # EV | |
| ws.cell(row=r, column=1, value="Implied Enterprise Value").font = Font(name="Calibri", bold=True, size=12) | |
| ev_formula = f"={sum_pv_fcf_cell}+{pv_tv_cell}" | |
| c = ws.cell(row=r, column=2, value=ev_formula); c.number_format = FMT_MONEY; c.font = Font(name="Calibri", bold=True, size=12) | |
| ev_cell = c.coordinate | |
| r += 1 | |
| # Net Debt | |
| ws.cell(row=r, column=1, value="Less: Net Debt").font = LBL_FONT | |
| c = ws.cell(row=r, column=2, value=net_debt); c.number_format = FMT_MONEY; c.font = DAT_FONT | |
| nd_cell = c.coordinate | |
| r += 1 | |
| # Equity Value | |
| ws.cell(row=r, column=1, value="Implied Equity Value").font = Font(name="Calibri", bold=True, size=12) | |
| eq_formula = f"={ev_cell}-{nd_cell}" | |
| c = ws.cell(row=r, column=2, value=eq_formula); c.number_format = FMT_MONEY; c.font = Font(name="Calibri", bold=True, size=12) | |
| eq_cell = c.coordinate | |
| r += 1 | |
| # Shares | |
| ws.cell(row=r, column=1, value="Diluted Shares Outstanding").font = LBL_FONT | |
| c = ws.cell(row=r, column=2, value=shares_out); c.number_format = FMT_DEC; c.font = DAT_FONT | |
| shares_cell = c.coordinate | |
| r += 1 | |
| # Implied Share Price | |
| ws.cell(row=r, column=1, value="Implied Share Price").font = Font(name="Calibri", bold=True, size=14, color="1F4E78") | |
| share_price_formula = f"={eq_cell}/{shares_cell}" | |
| c = ws.cell(row=r, column=2, value=share_price_formula); c.number_format = FMT_DEC; c.font = Font(name="Calibri", bold=True, size=14, color="1F4E78") | |
| c.fill = GRN_FILL | |
| r += 2 | |
| # Format and fit | |
| _widths(ws, [35, 18, 18, 18, 18, 18, 18]) | |
| def _narrative_sheet(wb, data): | |
| ws = wb.create_sheet("Industry & AI Insights") | |
| _title(ws, "AI Narrative Analysis", "Generated by AI Models") | |
| r = 4 | |
| exec_summary = data.get("executive_summary", "") | |
| if exec_summary: | |
| _sec(ws, r, "Executive Summary", 2); r += 1 | |
| c = ws.cell(row=r, column=1, value=exec_summary) | |
| c.font = LBL_FONT; c.alignment = Alignment(wrap_text=True, vertical="top") | |
| ws.merge_cells(start_row=r, start_column=1, end_row=r, end_column=4) | |
| ws.row_dimensions[r].height = 100 | |
| r += 2 | |
| industry_trends = data.get("industry_trends", "") | |
| if industry_trends: | |
| _sec(ws, r, "Industry Trends", 2); r += 1 | |
| c = ws.cell(row=r, column=1, value=industry_trends) | |
| c.font = LBL_FONT; c.alignment = Alignment(wrap_text=True, vertical="top") | |
| ws.merge_cells(start_row=r, start_column=1, end_row=r, end_column=4) | |
| ws.row_dimensions[r].height = 120 | |
| r += 2 | |
| _widths(ws, [100]) | |
| def _sources_sheet(wb, data): | |
| ws = wb.create_sheet("Sources") | |
| _title(ws, "Sources", "All data sources and retrieval dates") | |
| _hdr(ws, 4, ["#","URL","Description","Retrieved"]) | |
| r = 5 | |
| for i, src in enumerate(data.get("sources",[]), start=1): | |
| ws.cell(row=r, column=1, value=i).alignment = CENTER | |
| url = src.get("url","") | |
| uc = ws.cell(row=r, column=2, value=url) | |
| uc.font = Font(name="Calibri", size=10, color="0563C1", underline="single") | |
| uc.alignment = LEFT | |
| if url: | |
| try: uc.hyperlink = url | |
| except: pass | |
| ws.cell(row=r, column=3, value=src.get("description","")).alignment = LEFT | |
| ws.cell(row=r, column=4, value=src.get("retrieved","")).alignment = CENTER | |
| for col in range(1,5): ws.cell(row=r,column=col).border = THIN | |
| ws.row_dimensions[r].height = 28; r += 1 | |
| _widths(ws, [6,70,50,16]) | |
| # -- Orchestrator -------------------------------------------------------------- | |
| def build_workbook(data: dict, output_path: str) -> None: | |
| wb = Workbook() | |
| wb.remove(wb.active) | |
| _summary_sheet(wb, data) | |
| _income_sheet(wb, data) | |
| _balance_sheet(wb, data) | |
| _cashflow_sheet(wb, data) | |
| _ratios_sheet(wb, data) | |
| _dupont_sheet(wb, data) | |
| _dcf_sheet(wb, data) | |
| _growth_sheet(wb, data) | |
| _peers_sheet(wb, data) | |
| _swot_sheet(wb, data) | |
| _sources_sheet(wb, data) | |
| wb.save(output_path) | |
| if __name__ == "__main__": | |
| import argparse | |
| p = argparse.ArgumentParser() | |
| p.add_argument("--input", "-i", required=True) | |
| p.add_argument("--output", "-o", required=True) | |
| args = p.parse_args() | |
| with open(args.input, encoding="utf-8") as f: | |
| data = json.load(f) | |
| Path(args.output).parent.mkdir(parents=True, exist_ok=True) | |
| build_workbook(data, args.output) | |
| print(f"Wrote: {args.output}") | |