export/xlsx_builder.py - master - SimpleSpace/AutoInvestDataSync

export/xlsx_builder.py
362 строки16 KB
Alex Just
Start commit for mvp 0.0.5 version
04 май 2026, 22:47
04 май 2026, 22:47e12863e
from __future__ import annotations

import os
from datetime import datetime

from openpyxl import Workbook
from openpyxl.styles import Font, PatternFill, Alignment, Border, Side, numbers

from sync.portfolio import PositionData, AccountPortfolio
from sync.operations import PurchaseRecord
from sync.analytics import Allocation

HEADER_FILL = PatternFill(start_color="D6EAF8", end_color="D6EAF8", fill_type="solid")
HEADER_FONT = Font(bold=True, size=10)
MONEY_FORMAT = '#,##0.00'
PCT_FORMAT = '0.00%'
DATE_FORMAT = 'YYYY-MM-DD'
THIN_BORDER = Border(
    left=Side(style="thin"),
    right=Side(style="thin"),
    top=Side(style="thin"),
    bottom=Side(style="thin"),
)


def _style_header(ws, row: int, max_col: int):
    for col in range(1, max_col + 1):
        cell = ws.cell(row=row, column=col)
        cell.fill = HEADER_FILL
        cell.font = HEADER_FONT
        cell.alignment = Alignment(horizontal="center", wrap_text=True)
        cell.border = THIN_BORDER


def _style_data_cell(cell, is_money=False, is_pct=False, is_date=False):
    cell.border = THIN_BORDER
    if is_money:
        cell.number_format = MONEY_FORMAT
    elif is_pct:
        cell.number_format = PCT_FORMAT
    elif is_date:
        cell.number_format = DATE_FORMAT


def build_data_sheet(ws, all_portfolios: list[AccountPortfolio], analytics: dict):
    headers = [
        "Имя", "Идентификатор", "Кол-во", "Базовая цена, ₽",
        "Валюта", "Посредник", "Прибыль/убыток, ₽", "Доходность, % годовых",
        "Ожидаемая дох-ть, %", "Ср. ежемес. доход",
        "Дата поступления", "Размер поступления",
        "Текущая цена", "Текущая стоимость, ₽",
        "Дата покупки", "Класс актива", "Сектор",
        "FIGI", "ISIN", "Счёт", "Вес в портфеле, %",
    ]
    ws.title = "Данные"

    for col, h in enumerate(headers, 1):
        ws.cell(row=1, column=col, value=h)
    _style_header(ws, 1, len(headers))

    row = 2
    for ap in all_portfolios:
        for pos in ap.positions:
            values = [
                pos.name or pos.ticker or pos.figi,
                pos.ticker or pos.isin or pos.figi,
                pos.quantity,
                pos.base_price_rub,
                pos.currency or pos.current_price_currency,
                pos.account_name,
                pos.profit_loss_rub,
                (pos.yield_annual_pct / 100) if pos.yield_annual_pct else None,
                (pos.expected_dividend_yield_pct / 100) if pos.expected_dividend_yield_pct else None,
                pos.avg_monthly_income,
                pos.next_payout_date,
                pos.next_payout_amount,
                pos.current_price,
                pos.current_value_rub,
                pos.buy_date,
                pos.instrument_type_display,
                pos.sector,
                pos.figi,
                pos.isin,
                pos.account_id[:8],
                (pos.weight_in_portfolio_pct / 100) if pos.weight_in_portfolio_pct else None,
            ]
            for col, val in enumerate(values, 1):
                cell = ws.cell(row=row, column=col, value=val)
                is_money = col in (4, 7, 10, 12, 13, 14)
                is_pct = col in (8, 9, 21)
                is_date = col in (11, 15)
                _style_data_cell(cell, is_money=is_money, is_pct=is_pct, is_date=is_date)
            row += 1

    col_widths = [30, 15, 12, 15, 8, 20, 15, 15, 15, 15, 14, 15, 15, 18, 14, 15, 20, 15, 15, 12, 12]
    for i, w in enumerate(col_widths, 1):
        ws.column_dimensions[ws.cell(row=1, column=i).column_letter].width = w


def build_currencies_sheet(ws, currency_alloc: dict[str, Allocation]):
    ws.title = "Валюты"
    headers = ["Валюта", "Стоимость покупки, ₽", "Текущая стоимость, ₽", "P&L, ₽", "Доля, %", "Кол-во позиций"]
    for col, h in enumerate(headers, 1):
        ws.cell(row=1, column=col, value=h)
    _style_header(ws, 1, len(headers))

    row = 2
    for alloc in sorted(currency_alloc.values(), key=lambda a: a.current_value, reverse=True):
        values = [alloc.name, alloc.buy_value, alloc.current_value, alloc.profit_loss,
                  alloc.weight_pct / 100 if alloc.weight_pct else 0, alloc.count]
        for col, val in enumerate(values, 1):
            cell = ws.cell(row=row, column=col, value=val)
            _style_data_cell(cell, is_money=col in (2, 3, 4), is_pct=col == 5)
        row += 1


def build_brokers_sheet(ws, broker_alloc: dict[str, Allocation]):
    ws.title = "Посредники"
    headers = ["Посредник", "Кол-во бумаг", "Стоимость покупки, ₽", "Текущая стоимость, ₽",
               "P&L, ₽", "Доходность, %", "Доля, %"]
    for col, h in enumerate(headers, 1):
        ws.cell(row=1, column=col, value=h)
    _style_header(ws, 1, len(headers))

    row = 2
    for alloc in sorted(broker_alloc.values(), key=lambda a: a.current_value, reverse=True):
        yield_pct = ((alloc.current_value / alloc.buy_value) - 1) if alloc.buy_value > 0 else 0
        values = [alloc.name, alloc.count, alloc.buy_value, alloc.current_value,
                  alloc.profit_loss, yield_pct, alloc.weight_pct / 100 if alloc.weight_pct else 0]
        for col, val in enumerate(values, 1):
            cell = ws.cell(row=row, column=col, value=val)
            _style_data_cell(cell, is_money=col in (3, 4, 5), is_pct=col in (6, 7))
        row += 1


def build_classes_sheet(ws, class_alloc: dict[str, Allocation]):
    ws.title = "Классы активов"
    headers = ["Класс актива", "Кол-во", "Стоимость покупки, ₽", "Текущая стоимость, ₽",
               "P&L, ₽", "Доля, %"]
    for col, h in enumerate(headers, 1):
        ws.cell(row=1, column=col, value=h)
    _style_header(ws, 1, len(headers))

    row = 2
    for alloc in sorted(class_alloc.values(), key=lambda a: a.current_value, reverse=True):
        values = [alloc.name, alloc.count, alloc.buy_value, alloc.current_value,
                  alloc.profit_loss, alloc.weight_pct / 100 if alloc.weight_pct else 0]
        for col, val in enumerate(values, 1):
            cell = ws.cell(row=row, column=col, value=val)
            _style_data_cell(cell, is_money=col in (3, 4, 5), is_pct=col == 6)
        row += 1


def build_purchases_sheet(ws, purchases: list[PurchaseRecord]):
    ws.title = "Покупки"
    headers = ["Месяц", "Сумма покупок, ₽", "Кол-во сделок"]
    for col, h in enumerate(headers, 1):
        ws.cell(row=1, column=col, value=h)
    _style_header(ws, 1, len(headers))

    row = 2
    for p in sorted(purchases, key=lambda x: x.month):
        for col, val in enumerate([p.month, p.total_rub, p.count], 1):
            cell = ws.cell(row=row, column=col, value=val)
            _style_data_cell(cell, is_money=col == 2)
        row += 1


def build_capital_sheet(ws, history: list[dict]):
    ws.title = "Капитал"
    headers = ["Дата", "Стоимость покупки, ₽", "Текущая стоимость, ₽"]
    for col, h in enumerate(headers, 1):
        ws.cell(row=1, column=col, value=h)
    _style_header(ws, 1, len(headers))

    row = 2
    for entry in sorted(history, key=lambda x: x.get("date", "")):
        for col, key in enumerate(["date", "buy_value", "current_value"], 1):
            cell = ws.cell(row=row, column=col, value=entry.get(key, ""))
            _style_data_cell(cell, is_money=col in (2, 3), is_date=col == 1)
        row += 1


def build_report_sheet(ws, analytics: dict, positions: list[PositionData], payouts: list, macro=None):
    ws.title = "Отчет"
    ws.cell(row=1, column=1, value="СВОДНЫЙ ОТЧЁТ").font = Font(bold=True, size=14)
    ws.merge_cells(start_row=1, start_column=1, end_row=1, end_column=4)

    row = 3
    ws.cell(row=row, column=1, value="Общая стоимость покупки, ₽").font = Font(bold=True)
    ws.cell(row=row, column=2, value=analytics.get("total_buy", 0)).number_format = MONEY_FORMAT
    row += 1
    ws.cell(row=row, column=1, value="Текущая стоимость, ₽").font = Font(bold=True)
    ws.cell(row=row, column=2, value=analytics.get("total_current", 0)).number_format = MONEY_FORMAT
    row += 1
    ws.cell(row=row, column=1, value="Прибыль/убыток, ₽").font = Font(bold=True)
    pnl = analytics.get("total_pnl", 0)
    cell = ws.cell(row=row, column=2, value=pnl)
    cell.number_format = MONEY_FORMAT
    if pnl >= 0:
        cell.font = Font(color="006400")
    else:
        cell.font = Font(color="8B0000")

    if macro:
        row += 2
        ws.cell(row=row, column=1, value="МАКРОЭКОНОМИКА (Россия)").font = Font(bold=True, size=12)
        ws.merge_cells(start_row=row, start_column=1, end_row=row, end_column=4)
        row += 1
        for col, h in enumerate(["Показатель", "Значение", "Год", "Источник"], 1):
            ws.cell(row=row, column=col, value=h)
        _style_header(ws, row, 4)
        row += 1

        macro_rows = []
        if macro.inflation_imf_pct is not None:
            macro_rows.append(("Инфляция (прогноз МВФ)", macro.inflation_imf_pct / 100, macro.inflation_imf_year, "IMF"))
        if macro.inflation_cpi_wb_pct is not None:
            macro_rows.append(("Инфляция ИПЦ (факт)", macro.inflation_cpi_wb_pct / 100, macro.inflation_cpi_wb_year, "World Bank"))
        if macro.gdp_growth_pct is not None:
            macro_rows.append(("Рост ВВП", macro.gdp_growth_pct / 100, macro.gdp_growth_year, "IMF"))
        if macro.lending_rate_pct is not None:
            macro_rows.append(("Ставка по кредитам", macro.lending_rate_pct / 100, "2023", "World Bank"))
        if macro.deposit_rate_pct is not None:
            macro_rows.append(("Ставка по депозитам", macro.deposit_rate_pct / 100, "2023", "World Bank"))

        for label, val, year, src in macro_rows:
            ws.cell(row=row, column=1, value=label).font = Font(bold=True)
            c = ws.cell(row=row, column=2, value=val)
            c.number_format = PCT_FORMAT
            ws.cell(row=row, column=3, value=year)
            ws.cell(row=row, column=4, value=src)
            for col in range(1, 5):
                ws.cell(row=row, column=col).border = THIN_BORDER
            row += 1

        pnl_pct = 0
        if analytics.get("total_buy", 0) > 0:
            pnl_pct = analytics.get("total_pnl", 0) / analytics.get("total_buy", 0)
        inflation = macro.inflation_imf_pct or macro.inflation_cpi_wb_pct or 0
        real_yield = (pnl_pct * 100) - inflation
        row += 1
        ws.cell(row=row, column=1, value="Реальная доходность портфеля").font = Font(bold=True, size=11)
        ws.cell(row=row, column=2, value=f"{real_yield:+.1f}%").font = Font(
            bold=True, color="006400" if real_yield >= 0 else "8B0000"
        )
        ws.cell(row=row, column=3, value=f"(доходность {pnl_pct*100:.1f}% − инфляция {inflation:.1f}%)")

        mi = macro.market_index
        if mi and mi.current_price > 0:
            row += 2
            ws.cell(row=row, column=1, value="БЕНЧМАРК: ИНДЕКС РЫНКА").font = Font(bold=True, size=12)
            ws.merge_cells(start_row=row, start_column=1, end_row=row, end_column=4)
            row += 1
            for col, h in enumerate(["Показатель", "Значение", "Период", ""], 1):
                ws.cell(row=row, column=col, value=h)
            _style_header(ws, row, 4)
            row += 1

            ws.cell(row=row, column=1, value=mi.name).font = Font(bold=True)
            c = ws.cell(row=row, column=2, value=mi.current_price)
            c.number_format = MONEY_FORMAT
            ws.cell(row=row, column=3, value=f"на {mi.current_date}")
            for col in range(1, 5):
                ws.cell(row=row, column=col).border = THIN_BORDER
            row += 1

            ws.cell(row=row, column=1, value="Изменение за год").font = Font(bold=True)
            c = ws.cell(row=row, column=2, value=mi.change_year_pct / 100 if mi.change_year_pct else 0)
            c.number_format = PCT_FORMAT
            c.font = Font(color="006400" if mi.change_year_pct >= 0 else "8B0000")
            ws.cell(row=row, column=3, value=f"{mi.year_ago_date} → {mi.current_date}")
            for col in range(1, 5):
                ws.cell(row=row, column=col).border = THIN_BORDER
            row += 1

            if mi.change_year_pct != 0 and pnl_pct != 0:
                alpha = pnl_pct * 100 - mi.change_year_pct
                ws.cell(row=row, column=1, value="Альфа портфеля vs индекс").font = Font(bold=True)
                ws.cell(row=row, column=2, value=f"{alpha:+.2f}%").font = Font(
                    bold=True, color="006400" if alpha >= 0 else "8B0000"
                )
                ws.cell(row=row, column=3, value=f"(портфель {pnl_pct*100:.1f}% − индекс {mi.change_year_pct:.1f}%)")
                for col in range(1, 5):
                    ws.cell(row=row, column=col).border = THIN_BORDER

    row += 2
    ws.cell(row=row, column=1, value="ТОП-5 ЛИДЕРОВ РОСТА").font = Font(bold=True, size=12, color="006400")
    row += 1
    for col, h in enumerate(["Имя", "Тикер", "Доходность, % годовых", "P&L, ₽"], 1):
        ws.cell(row=row, column=col, value=h)
    _style_header(ws, row, 4)
    row += 1
    for pos in analytics.get("top_gainers", []):
        ws.cell(row=row, column=1, value=pos.name or pos.ticker or pos.figi)
        ws.cell(row=row, column=2, value=pos.ticker)
        c = ws.cell(row=row, column=3, value=(pos.yield_annual_pct / 100) if pos.yield_annual_pct else 0)
        c.number_format = PCT_FORMAT
        c = ws.cell(row=row, column=4, value=pos.profit_loss_rub)
        c.number_format = MONEY_FORMAT
        row += 1

    row += 1
    ws.cell(row=row, column=1, value="ТОП-5 ЛИДЕРОВ ПАДЕНИЯ").font = Font(bold=True, size=12, color="8B0000")
    row += 1
    for col, h in enumerate(["Имя", "Тикер", "Доходность, % годовых", "P&L, ₽"], 1):
        ws.cell(row=row, column=col, value=h)
    _style_header(ws, row, 4)
    row += 1
    for pos in analytics.get("top_losers", []):
        ws.cell(row=row, column=1, value=pos.name or pos.ticker or pos.figi)
        ws.cell(row=row, column=2, value=pos.ticker)
        c = ws.cell(row=row, column=3, value=(pos.yield_annual_pct / 100) if pos.yield_annual_pct else 0)
        c.number_format = PCT_FORMAT
        c = ws.cell(row=row, column=4, value=pos.profit_loss_rub)
        c.number_format = MONEY_FORMAT
        row += 1

    ws.column_dimensions["A"].width = 35
    ws.column_dimensions["B"].width = 15
    ws.column_dimensions["C"].width = 20
    ws.column_dimensions["D"].width = 18


def build_xlsx(
    all_portfolios: list[AccountPortfolio],
    analytics: dict,
    purchases: list[PurchaseRecord],
    payouts: list,
    capital_history: list[dict],
    output_path: str,
    macro=None,
) -> str:
    wb = Workbook()

    all_positions = []
    for ap in all_portfolios:
        all_positions.extend(ap.positions)

    build_data_sheet(wb.active, all_portfolios, analytics)

    ws2 = wb.create_sheet()
    build_currencies_sheet(ws2, analytics.get("currency_allocation", {}))

    ws3 = wb.create_sheet()
    build_brokers_sheet(ws3, analytics.get("broker_allocation", {}))

    ws4 = wb.create_sheet()
    build_classes_sheet(ws4, analytics.get("class_allocation", {}))

    ws5 = wb.create_sheet()
    build_purchases_sheet(ws5, purchases)

    ws6 = wb.create_sheet()
    build_capital_sheet(ws6, capital_history)

    ws7 = wb.create_sheet()
    build_report_sheet(ws7, analytics, all_positions, payouts, macro)

    os.makedirs(os.path.dirname(output_path) or ".", exist_ok=True)
    wb.save(output_path)
    return output_path