""" excel_writer.py — Escribe datos financieros en la plantilla Excel. Mapea cada concepto financiero extraído de EDGAR a la celda exacta de las hojas TIKR_IS, TIKR_BS y TIKR_CF de la plantilla. """ from __future__ import annotations import logging import re from pathlib import Path from typing import Optional import openpyxl from openpyxl import Workbook from openpyxl.styles import Font, PatternFill, Alignment from openpyxl.utils import get_column_letter log = logging.getLogger("excel_writer") # ── Colores para celdas escritas por el agente (distinción visual) FILL_XBRL = PatternFill("solid", fgColor="1A3A5C") # azul oscuro = dato EDGAR fiable FILL_CALC = PatternFill("solid", fgColor="1A4A2A") # verde oscuro = dato calculado FILL_LLM = PatternFill("solid", fgColor="5C3A1A") # naranja oscuro = dato LLM (revisar) FONT_WHITE = Font(color="FFFFFF") # ── Mapeado: concepto → número de fila en cada hoja TIKR ───────────────────── # Basado en el análisis real de la plantilla (fila exacta con openpyxl) IS_ROW_MAP: dict[str, int] = { # Revenues "revenues": 4, # COGS "cogs": 7, "gross_profit": 8, # OpEx "sga": 11, "rd": 12, "operating_expenses": 13, "operating_income": 14, # Below the line "interest_expense": 17, "interest_income": 18, "fx_gain_loss": 20, "ebt": 28, "income_tax": 29, "net_income": 32, # Supplementary "eps_diluted": 38, "shares_diluted": 40, "shares_basic": 42, "dps": 44, "eps_basic": 48, "ebitda": 49, "rd_expense_detail": 52, "selling_marketing": 53, "ga": 54, } BS_ROW_MAP: dict[str, int] = { "cash": 4, "short_term_investments": 5, "total_cash_sti": 7, "accounts_receivable": 8, "inventory": 11, "total_current_assets": 14, "gross_ppe": 15, "accum_depreciation": 16, "net_ppe": 17, "long_term_investments": 18, "goodwill": 19, "intangibles": 20, "total_assets": 24, "accounts_payable": 25, "short_term_debt": 27, "income_taxes_payable": 30, "deferred_revenue_current": 31, "total_current_liabilities":34, "long_term_debt": 35, "capital_leases": 36, "deferred_revenue_noncurrent":37, "total_liabilities": 40, "common_stock_equity": 41, "retained_earnings": 42, "aoci": 43, "total_equity": 44, "total_liabilities_equity": 46, "shares_outstanding": 48, "tangible_book_value": 50, "total_debt": 52, "net_debt": 53, "employees": 56, } CF_ROW_MAP: dict[str, int] = { "net_income": 4, "da": 5, "da_goodwill": 6, "sbc": 11, "change_receivables": 14, "change_inventories": 15, "change_payables": 16, "change_deferred_revenue": 17, "change_income_taxes": 18, "cfo": 20, "capex": 22, "acquisitions": 23, "investment_securities": 24, "cfi": 26, "debt_issued": 27, "debt_repaid": 28, "stock_issued": 29, "buybacks": 30, "dividends_paid": 31, "cff": 35, "net_change_cash": 37, "fcf": 39, "cash_interest_paid": 44, "cash_taxes_paid": 45, } SHEET_MAPS = { "7.TIKR_IS": IS_ROW_MAP, "8.TIKR_BS": BS_ROW_MAP, "9.TIKR_CF": CF_ROW_MAP, } def _find_year_column(ws, fy_end_date: str, header_row: int = 2) -> Optional[int]: """ Busca la columna que corresponde al año fiscal dado (YYYY-MM-DD). Compara con el contenido de la fila de cabecera de la hoja. Devuelve el índice de columna (1-based) o None si no se encuentra. """ target_year = int(fy_end_date[:4]) target_month = int(fy_end_date[5:7]) for col in range(2, ws.max_column + 1): cell_val = ws.cell(row=header_row, column=col).value if cell_val is None: continue cell_str = str(cell_val) # Fecha completa tipo "2025-06-30 00:00:00" o datetime if hasattr(cell_val, "year"): if cell_val.year == target_year and cell_val.month == target_month: return col else: # Buscar año en el string years_in_cell = re.findall(r"\b(20\d{2})\b", cell_str) if str(target_year) in years_in_cell: return col return None def write_financials_to_excel( template_path: str, output_path: str, annual_data: dict[str, dict[str, Optional[float]]], guidance_data: Optional[dict] = None, ) -> None: """ Escribe los datos financieros en una copia de la plantilla Excel. Args: template_path: ruta a la plantilla original (no se modifica) output_path: ruta del archivo de salida annual_data: {fy_end_date: {concepto: valor}} del edgar_xbrl guidance_data: datos de guidance extraídos del 8-K/LLM (opcional) """ log.info("Abriendo plantilla: %s", template_path) wb: Workbook = openpyxl.load_workbook(template_path) cells_written = 0 cells_skipped = 0 for sheet_name, row_map in SHEET_MAPS.items(): if sheet_name not in wb.sheetnames: log.warning("Hoja '%s' no encontrada en la plantilla", sheet_name) continue ws = wb[sheet_name] for fy_end, data in annual_data.items(): col = _find_year_column(ws, fy_end) if col is None: log.debug( "No se encontró columna para %s en hoja %s", fy_end, sheet_name ) cells_skipped += 1 continue for concept, row_idx in row_map.items(): value = data.get(concept) if value is None: continue cell = ws.cell(row=row_idx, column=col) # Determinar el tipo de dato para el color de fondo is_calculated = concept in { "ebitda", "fcf", "total_debt", "net_debt", "total_cash_sti", "tangible_book_value", } fill = FILL_CALC if is_calculated else FILL_XBRL # Escribir valor (convertir a miles si la plantilla usa MM) # La plantilla de MSFT usa miles (MM$), EDGAR devuelve unidades # Para IS/CF: dividir entre 1000 (de $ a $MM) # Para BS: igual # EPS/shares: no dividir no_scale = concept in { "eps_diluted", "eps_basic", "dps", "shares_diluted", "shares_basic", "shares_outstanding", "employees", "effective_tax_rate", } if no_scale: cell.value = round(value, 4) else: # Valores en millones (plantilla usa miles de millones → /1e6) # EDGAR devuelve en USD absoluto para la mayoría # La plantilla MSFT tiene valores como 91154 (= $91,154M) # EDGAR devuelve 91154000000 (= $91,154B en USD) # → dividir entre 1e6 para obtener $M scaled = value / 1_000_000 if abs(value) > 1_000_000 else value cell.value = round(scaled, 2) cell.fill = fill cell.font = FONT_WHITE cells_written += 1 # ── Añadir hoja "Guidance & Notes" si hay datos de guidance ────────── if guidance_data: _write_guidance_sheet(wb, guidance_data) # ── Añadir hoja de log del agente ────────────────────────────────────── _write_agent_log_sheet(wb, annual_data) wb.save(output_path) log.info( "Excel guardado: %s | Celdas escritas: %d | Skipped: %d", output_path, cells_written, cells_skipped, ) def _write_guidance_sheet(wb: Workbook, guidance: dict) -> None: """Añade o actualiza la hoja 'Guidance & Notes' con los datos del LLM.""" sheet_name = "Guidance & Notes" if sheet_name in wb.sheetnames: del wb[sheet_name] ws = wb.create_sheet(sheet_name) ws.sheet_properties.tabColor = "FF8C00" # naranja headers = ["Campo", "Valor", "Fuente", "Fecha extracción", "Confianza LLM"] for col, h in enumerate(headers, 1): cell = ws.cell(row=1, column=col, value=h) cell.font = Font(bold=True, color="FFFFFF") cell.fill = PatternFill("solid", fgColor="333333") rows = [ ("Revenue guidance Q siguiente (bajo)", guidance.get("guidance_revenue_next_q_low"), "8-K / LLM", guidance.get("source_date"), guidance.get("confidence_score")), ("Revenue guidance Q siguiente (alto)", guidance.get("guidance_revenue_next_q_high"), "8-K / LLM", guidance.get("source_date"), guidance.get("confidence_score")), ("EPS guidance Q siguiente", guidance.get("guidance_eps_next_q"), "8-K / LLM", guidance.get("source_date"), guidance.get("confidence_score")), ("Margen operativo objetivo", guidance.get("guidance_margin"), "8-K / LLM", guidance.get("source_date"), guidance.get("confidence_score")), ("CapEx guidance", guidance.get("capex_guidance"), "8-K / LLM", guidance.get("source_date"), guidance.get("confidence_score")), ("Buyback anunciado", guidance.get("buyback_announced"), "8-K / LLM", guidance.get("source_date"), guidance.get("confidence_score")), ] for i, row_data in enumerate(rows, start=2): for col, val in enumerate(row_data, 1): ws.cell(row=i, column=col, value=val) # Key positives y risks como texto row = len(rows) + 3 ws.cell(row=row, column=1, value="Factores positivos (LLM)").font = Font(bold=True) for item in (guidance.get("key_positives") or []): row += 1 ws.cell(row=row, column=1, value=f"• {item}") row += 2 ws.cell(row=row, column=1, value="Riesgos detectados (LLM)").font = Font(bold=True) for item in (guidance.get("key_risks") or []): row += 1 ws.cell(row=row, column=1, value=f"• {item}") ws.column_dimensions["A"].width = 38 ws.column_dimensions["B"].width = 22 ws.column_dimensions["C"].width = 18 ws.column_dimensions["D"].width = 20 def _write_agent_log_sheet(wb: Workbook, annual_data: dict) -> None: """Añade hoja de log con resumen de lo que escribió el agente.""" sheet_name = "Agente_Log" if sheet_name in wb.sheetnames: del wb[sheet_name] ws = wb.create_sheet(sheet_name) ws.sheet_properties.tabColor = "0088FF" from datetime import datetime ws.cell(row=1, column=1, value="Leonex — Agente Fundamental").font = Font(bold=True, size=13) ws.cell(row=2, column=1, value=f"Actualizado: {datetime.now().strftime('%Y-%m-%d %H:%M')}") ws.cell(row=3, column=1, value="Fuente: SEC EDGAR XBRL + 8-K") ws.cell(row=4, column=1, value="Celdas en azul oscuro = EDGAR (fiable)") ws.cell(row=4, column=3, value="Celdas en verde = Calculado") ws.cell(row=5, column=1, value="Celdas en naranja = LLM (revisar antes de usar)") ws.cell(row=7, column=1, value="Periodo").font = Font(bold=True) ws.cell(row=7, column=2, value="Conceptos con datos").font = Font(bold=True) ws.cell(row=7, column=3, value="Conceptos sin datos").font = Font(bold=True) row = 7 for fy_end, data in annual_data.items(): row += 1 with_data = sum(1 for v in data.values() if v is not None) without_data = sum(1 for v in data.values() if v is None) ws.cell(row=row, column=1, value=fy_end) ws.cell(row=row, column=2, value=with_data) ws.cell(row=row, column=3, value=without_data) ws.column_dimensions["A"].width = 22 ws.column_dimensions["B"].width = 22 ws.column_dimensions["C"].width = 22