| from __future__ import annotations |
|
|
| from datetime import datetime, time |
| from decimal import Decimal |
| from pathlib import Path |
|
|
| import xlsxwriter |
|
|
| from .models import FundPortfolioReport, Severity, ValidationResult |
|
|
|
|
| NAVY = "#11233F" |
| BLUE = "#246BFE" |
| PALE_BLUE = "#EAF1FF" |
| TEAL = "#0F9D8A" |
| PALE_TEAL = "#E5F7F3" |
| AMBER = "#D98E04" |
| PALE_AMBER = "#FFF4D6" |
| RED = "#C83C3C" |
| PALE_RED = "#FDECEC" |
| SLATE = "#526173" |
| LIGHT = "#F5F7FA" |
| WHITE = "#FFFFFF" |
| BORDER = "#D9E0E8" |
|
|
|
|
| def _number(value: Decimal) -> float: |
| return float(value) |
|
|
|
|
| def _as_datetime(value): |
| return datetime.combine(value, time.min) |
|
|
|
|
| def _formats(workbook: xlsxwriter.Workbook) -> dict[str, xlsxwriter.format.Format]: |
| return { |
| "title": workbook.add_format( |
| { |
| "bold": True, |
| "font_size": 18, |
| "font_color": WHITE, |
| "bg_color": NAVY, |
| "align": "left", |
| "valign": "vcenter", |
| } |
| ), |
| "subtitle": workbook.add_format( |
| { |
| "font_size": 10, |
| "font_color": SLATE, |
| "bg_color": LIGHT, |
| "align": "left", |
| "valign": "vcenter", |
| } |
| ), |
| "section": workbook.add_format( |
| { |
| "bold": True, |
| "font_size": 11, |
| "font_color": WHITE, |
| "bg_color": BLUE, |
| "align": "left", |
| "valign": "vcenter", |
| } |
| ), |
| "table_header": workbook.add_format( |
| { |
| "bold": True, |
| "font_color": WHITE, |
| "bg_color": NAVY, |
| "border": 1, |
| "border_color": NAVY, |
| "align": "center", |
| "valign": "vcenter", |
| "text_wrap": True, |
| } |
| ), |
| "label": workbook.add_format( |
| {"bold": True, "font_color": NAVY, "bg_color": LIGHT} |
| ), |
| "text": workbook.add_format({"font_color": NAVY}), |
| "text_border": workbook.add_format( |
| {"font_color": NAVY, "border": 1, "border_color": BORDER} |
| ), |
| "wrapped_text": workbook.add_format( |
| { |
| "font_color": NAVY, |
| "border": 1, |
| "border_color": BORDER, |
| "text_wrap": True, |
| "valign": "top", |
| } |
| ), |
| "integer": workbook.add_format( |
| {"num_format": "#,##0", "border": 1, "border_color": BORDER} |
| ), |
| "decimal": workbook.add_format( |
| {"num_format": "#,##0.00", "border": 1, "border_color": BORDER} |
| ), |
| "price": workbook.add_format( |
| {"num_format": "#,##0.000000", "border": 1, "border_color": BORDER} |
| ), |
| "money": workbook.add_format( |
| { |
| "num_format": '#,##0.00;[Red]-#,##0.00', |
| "border": 1, |
| "border_color": BORDER, |
| } |
| ), |
| "percent": workbook.add_format( |
| { |
| "num_format": "0.00%;[Red]-0.00%", |
| "border": 1, |
| "border_color": BORDER, |
| } |
| ), |
| "date": workbook.add_format( |
| {"num_format": "dd/mm/yyyy", "border": 1, "border_color": BORDER} |
| ), |
| "total_label": workbook.add_format( |
| { |
| "bold": True, |
| "font_color": WHITE, |
| "bg_color": NAVY, |
| "border": 1, |
| "border_color": NAVY, |
| } |
| ), |
| "total_number": workbook.add_format( |
| { |
| "bold": True, |
| "font_color": WHITE, |
| "bg_color": NAVY, |
| "num_format": "#,##0.00", |
| "border": 1, |
| "border_color": NAVY, |
| } |
| ), |
| "total_percent": workbook.add_format( |
| { |
| "bold": True, |
| "font_color": WHITE, |
| "bg_color": NAVY, |
| "num_format": "0.00%", |
| "border": 1, |
| "border_color": NAVY, |
| } |
| ), |
| "declared_label": workbook.add_format( |
| { |
| "bold": True, |
| "font_color": NAVY, |
| "bg_color": PALE_BLUE, |
| "border": 1, |
| "border_color": BORDER, |
| } |
| ), |
| "declared_number": workbook.add_format( |
| { |
| "bold": True, |
| "font_color": NAVY, |
| "bg_color": PALE_BLUE, |
| "num_format": "#,##0.00", |
| "border": 1, |
| "border_color": BORDER, |
| } |
| ), |
| "declared_percent": workbook.add_format( |
| { |
| "bold": True, |
| "font_color": NAVY, |
| "bg_color": PALE_BLUE, |
| "num_format": "0.00%", |
| "border": 1, |
| "border_color": BORDER, |
| } |
| ), |
| "status_ok": workbook.add_format( |
| {"bold": True, "font_color": TEAL, "bg_color": PALE_TEAL} |
| ), |
| "status_warning": workbook.add_format( |
| {"bold": True, "font_color": AMBER, "bg_color": PALE_AMBER} |
| ), |
| "status_error": workbook.add_format( |
| {"bold": True, "font_color": RED, "bg_color": PALE_RED} |
| ), |
| "small": workbook.add_format({"font_size": 8, "font_color": SLATE}), |
| "hash": workbook.add_format( |
| {"font_name": "Consolas", "font_size": 8, "font_color": SLATE} |
| ), |
| } |
|
|
|
|
| def _status_format(formats, validation: ValidationResult): |
| if validation.error_count: |
| return formats["status_error"] |
| if validation.warning_count: |
| return formats["status_warning"] |
| return formats["status_ok"] |
|
|
|
|
| def _add_title( |
| sheet, |
| title: str, |
| subtitle: str, |
| last_column: int, |
| formats, |
| ) -> None: |
| sheet.merge_range(0, 0, 0, last_column, title, formats["title"]) |
| sheet.merge_range(1, 0, 1, last_column, subtitle, formats["subtitle"]) |
| sheet.set_row(0, 30) |
| sheet.set_row(1, 22) |
| sheet.hide_gridlines(2) |
|
|
|
|
| def _configure_print(sheet, *, repeat_header_row: int | None = None) -> None: |
| sheet.set_landscape() |
| sheet.set_paper(9) |
| sheet.fit_to_pages(1, 0) |
| sheet.center_horizontally() |
| sheet.set_margins(left=0.25, right=0.25, top=0.45, bottom=0.45) |
| sheet.set_header("&LFinReport Agent&C&F&R&P / &N", {"margin": 0.2}) |
| sheet.set_footer( |
| "&LKaynak belgeye göre otomatik oluşturulmuştur.&R&D &T", |
| {"margin": 0.2}, |
| ) |
| if repeat_header_row is not None: |
| sheet.repeat_rows(repeat_header_row, repeat_header_row) |
|
|
|
|
| def _write_summary(workbook, report, validation, formats) -> None: |
| sheet = workbook.add_worksheet("Özet") |
| _add_title( |
| sheet, |
| "FinReport Agent · Doğrulanmış Portföy Özeti", |
| f"{report.metadata.fund_name} · {report.metadata.period}", |
| 5, |
| formats, |
| ) |
| sheet.set_column("A:A", 25) |
| sheet.set_column("B:B", 24) |
| sheet.set_column("C:E", 20) |
| sheet.set_column("F:F", 15) |
| sheet.freeze_panes(8, 0) |
|
|
| sheet.write("A3", "Doğrulama durumu", formats["label"]) |
| sheet.write("B3", validation.status, _status_format(formats, validation)) |
| sheet.write("A4", "Kaynak dosya", formats["label"]) |
| sheet.write("B4", report.metadata.source_filename, formats["text"]) |
| sheet.write("D3", "Fon kodu", formats["label"]) |
| sheet.write("E3", report.metadata.fund_code or "-", formats["text"]) |
| sheet.write("D4", "Dönem", formats["label"]) |
| sheet.write("E4", report.metadata.period, formats["text"]) |
| sheet.write("A6", "Portföy uzlaştırması", formats["section"]) |
| sheet.merge_range("B6:F6", "", formats["section"]) |
|
|
| headers = [ |
| "Kalem", |
| "Muhasebesel Sınıf", |
| "PDF Beyanı (TL)", |
| "Detay Hesabı (TL)", |
| "Fark (TL)", |
| "FPD (%)", |
| ] |
| for column, header in enumerate(headers): |
| sheet.write(7, column, header) |
|
|
| totals = report.declared_totals |
| equity_calc = sum(row.total_value for row in report.equities) |
| repo_calc = sum(row.total_value for row in report.repos) |
| collateral_calc = sum(row.total_value for row in report.cash_collaterals) |
| rows = [ |
| ( |
| "Hisse Senetleri", |
| "Portföye dahil", |
| totals.equity_value, |
| equity_calc, |
| totals.equity_fpd_pct, |
| ), |
| ( |
| "T. Repo", |
| "Portföye dahil", |
| totals.repo_value, |
| repo_calc, |
| totals.repo_fpd_pct, |
| ), |
| ( |
| "VİOP Nakit Teminatı", |
| "Portföye dahil", |
| totals.cash_collateral_value, |
| collateral_calc, |
| totals.cash_collateral_fpd_pct, |
| ), |
| ( |
| "FON PORTFÖY DEĞERİ", |
| "Uzlaştırılmış toplam", |
| totals.fund_portfolio_value, |
| equity_calc + repo_calc + collateral_calc, |
| Decimal("1"), |
| ), |
| ( |
| "Futures Pozisyon Değeri", |
| "Bilgi / maruziyet", |
| totals.derivative_exposure, |
| sum(row.total_value for row in report.derivatives), |
| Decimal("0"), |
| ), |
| ] |
| for row_index, (name, classification, declared, calculated, percentage) in enumerate( |
| rows, start=8 |
| ): |
| sheet.write(row_index, 0, name, formats["text_border"]) |
| sheet.write(row_index, 1, classification, formats["text_border"]) |
| sheet.write_number(row_index, 2, _number(declared), formats["money"]) |
| sheet.write_number(row_index, 3, _number(calculated), formats["money"]) |
| sheet.write_formula( |
| row_index, |
| 4, |
| f"=D{row_index + 1}-C{row_index + 1}", |
| formats["money"], |
| _number(calculated - declared), |
| ) |
| sheet.write_number(row_index, 5, _number(percentage), formats["percent"]) |
|
|
| sheet.add_table( |
| 7, |
| 0, |
| 12, |
| 5, |
| { |
| "name": "PortfolioSummary", |
| "style": "Table Style Medium 2", |
| "columns": [ |
| {"header": header, "header_format": formats["table_header"]} |
| for header in headers |
| ], |
| }, |
| ) |
| sheet.conditional_format( |
| "E9:E13", |
| { |
| "type": "cell", |
| "criteria": "not between", |
| "minimum": -0.01, |
| "maximum": 0.01, |
| "format": formats["status_error"], |
| }, |
| ) |
| sheet.write("A15", "Not", formats["label"]) |
| sheet.merge_range( |
| "B15:F15", |
| ( |
| "Futures pozisyon değeri maruziyettir; fon portföy değeri toplamına " |
| "eklenmez. VİOP nakit teminatı portföy uzlaştırmasına dahildir." |
| ), |
| formats["subtitle"], |
| ) |
| sheet.set_row(7, 28) |
| _configure_print(sheet) |
|
|
|
|
| def _write_equities(workbook, report, formats) -> None: |
| sheet = workbook.add_worksheet("Hisse Senetleri") |
| _add_title( |
| sheet, |
| "Hisse Senetleri", |
| f"{len(report.equities)} pozisyon · Kaynak koordinatları korunmuştur", |
| 13, |
| formats, |
| ) |
| headers = [ |
| "Kod", |
| "İhraççı Kurum", |
| "Döviz", |
| "ISIN", |
| "Nominal Değer", |
| "Birim Alış Fiyatı", |
| "Satın Alış Tarihi", |
| "Borsa Sözleşme No", |
| "Güncel Birim Fiyat", |
| "Toplam Değer (TL)", |
| "Grup (%)", |
| "FPD (%)", |
| "FTD (%)", |
| "Kaynak Sayfa", |
| ] |
| header_row = 3 |
| for column, header in enumerate(headers): |
| sheet.write(header_row, column, header) |
|
|
| for row_index, row in enumerate(report.equities, start=header_row + 1): |
| sheet.write(row_index, 0, row.code, formats["text_border"]) |
| issuer_format = ( |
| formats["wrapped_text"] if len(row.issuer) > 42 else formats["text_border"] |
| ) |
| sheet.write(row_index, 1, row.issuer, issuer_format) |
| if len(row.issuer) > 42: |
| sheet.set_row(row_index, 30) |
| sheet.write(row_index, 2, row.currency, formats["text_border"]) |
| sheet.write(row_index, 3, row.isin, formats["text_border"]) |
| sheet.write_number(row_index, 4, _number(row.nominal), formats["decimal"]) |
| sheet.write_number(row_index, 5, _number(row.purchase_price), formats["price"]) |
| sheet.write_datetime( |
| row_index, 6, _as_datetime(row.purchase_date), formats["date"] |
| ) |
| sheet.write(row_index, 7, row.contract_no or "", formats["text_border"]) |
| sheet.write_number(row_index, 8, _number(row.current_price), formats["price"]) |
| sheet.write_number(row_index, 9, _number(row.total_value), formats["money"]) |
| sheet.write_number(row_index, 10, _number(row.group_pct), formats["percent"]) |
| sheet.write_number(row_index, 11, _number(row.fpd_pct), formats["percent"]) |
| sheet.write_number(row_index, 12, _number(row.ftd_pct), formats["percent"]) |
| sheet.write_number(row_index, 13, row.source.page, formats["integer"]) |
|
|
| last_data_row = header_row + len(report.equities) |
| sheet.add_table( |
| header_row, |
| 0, |
| last_data_row, |
| len(headers) - 1, |
| { |
| "name": "EquityPositions", |
| "style": "Table Style Medium 2", |
| "columns": [ |
| {"header": header, "header_format": formats["table_header"]} |
| for header in headers |
| ], |
| }, |
| ) |
| calculated_row = last_data_row + 1 |
| declared_row = last_data_row + 2 |
| sheet.write(calculated_row, 0, "HESAPLANAN TOPLAM", formats["total_label"]) |
| sheet.write_formula( |
| calculated_row, |
| 4, |
| f"=SUM(E{header_row + 2}:E{last_data_row + 1})", |
| formats["total_number"], |
| _number(sum(row.nominal for row in report.equities)), |
| ) |
| sheet.write_formula( |
| calculated_row, |
| 9, |
| f"=SUM(J{header_row + 2}:J{last_data_row + 1})", |
| formats["total_number"], |
| _number(sum(row.total_value for row in report.equities)), |
| ) |
| for column, attr in ((10, "group_pct"), (11, "fpd_pct"), (12, "ftd_pct")): |
| sheet.write_formula( |
| calculated_row, |
| column, |
| f"=SUM({xlsxwriter.utility.xl_col_to_name(column)}" |
| f"{header_row + 2}:{xlsxwriter.utility.xl_col_to_name(column)}" |
| f"{last_data_row + 1})", |
| formats["total_percent"], |
| _number(sum(getattr(row, attr) for row in report.equities)), |
| ) |
| for column in range(1, len(headers)): |
| if column not in {4, 9, 10, 11, 12}: |
| sheet.write_blank(calculated_row, column, None, formats["total_label"]) |
|
|
| totals = report.declared_totals |
| sheet.write(declared_row, 0, "PDF BEYAN TOPLAMI", formats["declared_label"]) |
| sheet.write_number( |
| declared_row, 4, _number(totals.equity_nominal), formats["declared_number"] |
| ) |
| sheet.write_number( |
| declared_row, 9, _number(totals.equity_value), formats["declared_number"] |
| ) |
| sheet.write_number(declared_row, 10, 1, formats["declared_percent"]) |
| sheet.write_number( |
| declared_row, 11, _number(totals.equity_fpd_pct), formats["declared_percent"] |
| ) |
| sheet.write_number( |
| declared_row, 12, _number(totals.equity_ftd_pct), formats["declared_percent"] |
| ) |
| for column in range(1, len(headers)): |
| if column not in {4, 9, 10, 11, 12}: |
| sheet.write_blank(declared_row, column, None, formats["declared_label"]) |
|
|
| sheet.freeze_panes(header_row + 1, 2) |
| sheet.set_column("A:A", 12) |
| sheet.set_column("B:B", 38) |
| sheet.set_column("C:C", 9) |
| sheet.set_column("D:D", 17) |
| sheet.set_column("E:F", 17) |
| sheet.set_column("G:G", 15) |
| sheet.set_column("H:H", 20) |
| sheet.set_column("I:J", 18) |
| sheet.set_column("K:M", 12) |
| sheet.set_column("N:N", 13) |
| sheet.set_row(header_row, 34) |
| _configure_print(sheet, repeat_header_row=header_row) |
|
|
|
|
| def _write_repos(workbook, report, formats) -> None: |
| sheet = workbook.add_worksheet("T. Repo") |
| _add_title( |
| sheet, |
| "Ters Repo Pozisyonları", |
| f"{len(report.repos)} pozisyon", |
| 19, |
| formats, |
| ) |
| headers = [ |
| "Kod", |
| "Döviz", |
| "İhraççı", |
| "Vade Tarihi", |
| "Kalan Gün", |
| "ISIN", |
| "İç İskonto (%)", |
| "Faiz Ödeme Sayısı", |
| "Nominal Değer", |
| "Birim Alış Fiyatı", |
| "Satın Alış Tarihi", |
| "Repo Teminatı", |
| "Borsa Sözleşme No", |
| "Net Dönüş", |
| "Günlük Br Değer", |
| "Toplam Değer (TL)", |
| "Grup (%)", |
| "FPD (%)", |
| "FTD (%)", |
| "Kaynak Sayfa", |
| ] |
| header_row = 3 |
| for column, header in enumerate(headers): |
| sheet.write(header_row, column, header) |
| for row_index, row in enumerate(report.repos, start=header_row + 1): |
| values = [row.code, row.currency, row.issuer] |
| for column, value in enumerate(values): |
| sheet.write(row_index, column, value, formats["text_border"]) |
| sheet.write_datetime( |
| row_index, 3, _as_datetime(row.maturity_date), formats["date"] |
| ) |
| sheet.write_number(row_index, 4, row.days_to_maturity, formats["integer"]) |
| sheet.write(row_index, 5, row.isin, formats["text_border"]) |
| sheet.write_number( |
| row_index, 6, _number(row.discount_rate / 100), formats["percent"] |
| ) |
| sheet.write_number( |
| row_index, 7, row.interest_payment_count, formats["integer"] |
| ) |
| sheet.write_number(row_index, 8, _number(row.nominal), formats["money"]) |
| sheet.write_number(row_index, 9, _number(row.purchase_price), formats["price"]) |
| sheet.write_datetime( |
| row_index, 10, _as_datetime(row.purchase_date), formats["date"] |
| ) |
| sheet.write_number( |
| row_index, 11, _number(row.collateral_amount), formats["money"] |
| ) |
| sheet.write(row_index, 12, row.contract_no, formats["text_border"]) |
| sheet.write_number(row_index, 13, _number(row.net_return), formats["money"]) |
| sheet.write_number(row_index, 14, _number(row.daily_value), formats["price"]) |
| sheet.write_number(row_index, 15, _number(row.total_value), formats["money"]) |
| sheet.write_number(row_index, 16, _number(row.group_pct), formats["percent"]) |
| sheet.write_number(row_index, 17, _number(row.fpd_pct), formats["percent"]) |
| sheet.write_number(row_index, 18, _number(row.ftd_pct), formats["percent"]) |
| sheet.write_number(row_index, 19, row.source.page, formats["integer"]) |
|
|
| last_data_row = header_row + len(report.repos) |
| sheet.add_table( |
| header_row, |
| 0, |
| last_data_row, |
| len(headers) - 1, |
| { |
| "name": "RepoPositions", |
| "style": "Table Style Medium 2", |
| "columns": [ |
| {"header": header, "header_format": formats["table_header"]} |
| for header in headers |
| ], |
| }, |
| ) |
| total_row = last_data_row + 1 |
| sheet.write(total_row, 0, "HESAPLANAN TOPLAM", formats["total_label"]) |
| sheet.write_formula( |
| total_row, |
| 15, |
| f"=SUM(P{header_row + 2}:P{last_data_row + 1})", |
| formats["total_number"], |
| _number(sum(row.total_value for row in report.repos)), |
| ) |
| for column, attr in ((16, "group_pct"), (17, "fpd_pct"), (18, "ftd_pct")): |
| letter = xlsxwriter.utility.xl_col_to_name(column) |
| sheet.write_formula( |
| total_row, |
| column, |
| f"=SUM({letter}{header_row + 2}:{letter}{last_data_row + 1})", |
| formats["total_percent"], |
| _number(sum(getattr(row, attr) for row in report.repos)), |
| ) |
| for column in range(1, len(headers)): |
| if column not in {15, 16, 17, 18}: |
| sheet.write_blank(total_row, column, None, formats["total_label"]) |
| sheet.freeze_panes(header_row + 1, 2) |
| sheet.set_column(0, 19, 15) |
| sheet.set_column("C:C", 18) |
| sheet.set_column("F:F", 17) |
| sheet.set_column("M:M", 20) |
| sheet.set_row(header_row, 34) |
| _configure_print(sheet, repeat_header_row=header_row) |
|
|
|
|
| def _write_derivatives(workbook, report, formats) -> None: |
| sheet = workbook.add_worksheet("Türev") |
| _add_title( |
| sheet, |
| "Türev Pozisyonlar ve Nakit Teminatlar", |
| "Maruziyet ile portföye dahil nakit teminatı ayrı sunulur", |
| 13, |
| formats, |
| ) |
| headers = [ |
| "Tür", |
| "Kod", |
| "Döviz", |
| "Vade Tarihi", |
| "Nominal", |
| "Birim Alış Fiyatı", |
| "Satın Alış Tarihi", |
| "Borsa Sözleşme No", |
| "Güncel Fiyat", |
| "Pozisyon Değeri (TL)", |
| "Grup (%)", |
| "FPD (%)", |
| "FTD (%)", |
| "Kaynak Sayfa", |
| ] |
| header_row = 3 |
| for column, header in enumerate(headers): |
| sheet.write(header_row, column, header) |
| for row_index, row in enumerate(report.derivatives, start=header_row + 1): |
| sheet.write(row_index, 0, row.position_type, formats["text_border"]) |
| sheet.write(row_index, 1, row.code, formats["text_border"]) |
| sheet.write(row_index, 2, row.currency, formats["text_border"]) |
| if row.maturity_date: |
| sheet.write_datetime( |
| row_index, 3, _as_datetime(row.maturity_date), formats["date"] |
| ) |
| else: |
| sheet.write(row_index, 3, "-", formats["text_border"]) |
| sheet.write_number(row_index, 4, _number(row.nominal), formats["decimal"]) |
| sheet.write_number(row_index, 5, _number(row.purchase_price), formats["price"]) |
| sheet.write_datetime( |
| row_index, 6, _as_datetime(row.purchase_date), formats["date"] |
| ) |
| sheet.write(row_index, 7, row.contract_no or "", formats["text_border"]) |
| sheet.write_number(row_index, 8, _number(row.current_price), formats["price"]) |
| sheet.write_number(row_index, 9, _number(row.total_value), formats["money"]) |
| sheet.write_number(row_index, 10, _number(row.group_pct), formats["percent"]) |
| sheet.write_number(row_index, 11, _number(row.fpd_pct), formats["percent"]) |
| sheet.write_number(row_index, 12, _number(row.ftd_pct), formats["percent"]) |
| sheet.write_number(row_index, 13, row.source.page, formats["integer"]) |
|
|
| last_derivative_row = header_row + len(report.derivatives) |
| sheet.add_table( |
| header_row, |
| 0, |
| last_derivative_row, |
| len(headers) - 1, |
| { |
| "name": "DerivativePositions", |
| "style": "Table Style Medium 2", |
| "columns": [ |
| {"header": header, "header_format": formats["table_header"]} |
| for header in headers |
| ], |
| }, |
| ) |
| cash_header = last_derivative_row + 3 |
| sheet.merge_range( |
| cash_header - 1, |
| 0, |
| cash_header - 1, |
| 5, |
| "Portföye Dahil Nakit Teminatlar", |
| formats["section"], |
| ) |
| cash_headers = [ |
| "Kalem", |
| "Döviz", |
| "Nominal", |
| "Toplam Değer (TL)", |
| "Grup (%)", |
| "FPD (%)", |
| "Kaynak Sayfa", |
| ] |
| for column, header in enumerate(cash_headers): |
| sheet.write(cash_header, column, header) |
| for row_index, row in enumerate(report.cash_collaterals, start=cash_header + 1): |
| sheet.write(row_index, 0, row.name, formats["text_border"]) |
| sheet.write(row_index, 1, row.currency, formats["text_border"]) |
| sheet.write_number(row_index, 2, _number(row.nominal), formats["money"]) |
| sheet.write_number(row_index, 3, _number(row.total_value), formats["money"]) |
| sheet.write_number(row_index, 4, _number(row.group_pct), formats["percent"]) |
| sheet.write_number(row_index, 5, _number(row.fpd_pct), formats["percent"]) |
| sheet.write_number(row_index, 6, row.source.page, formats["integer"]) |
| if report.cash_collaterals: |
| sheet.add_table( |
| cash_header, |
| 0, |
| cash_header + len(report.cash_collaterals), |
| len(cash_headers) - 1, |
| { |
| "name": "CashCollaterals", |
| "style": "Table Style Medium 4", |
| "columns": [ |
| {"header": header, "header_format": formats["table_header"]} |
| for header in cash_headers |
| ], |
| }, |
| ) |
| sheet.freeze_panes(header_row + 1, 2) |
| sheet.set_column("A:B", 20) |
| sheet.set_column("C:C", 10) |
| sheet.set_column("D:J", 18) |
| sheet.set_column("K:N", 13) |
| sheet.set_row(header_row, 34) |
| sheet.set_row(cash_header, 30) |
| _configure_print(sheet, repeat_header_row=header_row) |
|
|
|
|
| def _write_findings(workbook, report, validation, formats) -> None: |
| sheet = workbook.add_worksheet("Kontroller") |
| _add_title( |
| sheet, |
| "Finansal Doğrulama ve Denetim İzi", |
| ( |
| f"{validation.error_count} hata · {validation.warning_count} uyarı · " |
| f"{validation.info_count} bilgi" |
| ), |
| 8, |
| formats, |
| ) |
| sheet.write("A3", "Durum", formats["label"]) |
| sheet.write("B3", validation.status, _status_format(formats, validation)) |
| sheet.write("A4", "SHA-256", formats["label"]) |
| sheet.merge_range("B4:I4", report.metadata.source_sha256, formats["hash"]) |
|
|
| headers = [ |
| "Seviye", |
| "Kontrol Kodu", |
| "Açıklama", |
| "Sayfa", |
| "Kayıt", |
| "Beklenen", |
| "Gerçekleşen", |
| "Çalışma Sayfası", |
| "Sonuç", |
| ] |
| header_row = 6 |
| for column, header in enumerate(headers): |
| sheet.write(header_row, column, header) |
| for row_index, finding in enumerate(validation.findings, start=header_row + 1): |
| severity_label = { |
| Severity.ERROR: "Hata", |
| Severity.WARNING: "Uyarı", |
| Severity.INFO: "Bilgi", |
| }[finding.severity] |
| severity_format = { |
| Severity.ERROR: formats["status_error"], |
| Severity.WARNING: formats["status_warning"], |
| Severity.INFO: formats["status_ok"], |
| }[finding.severity] |
| sheet.write(row_index, 0, severity_label, severity_format) |
| sheet.write(row_index, 1, finding.code, formats["text_border"]) |
| sheet.write(row_index, 2, finding.message, formats["text_border"]) |
| sheet.write(row_index, 3, finding.page or "", formats["text_border"]) |
| sheet.write(row_index, 4, finding.record_key or "", formats["text_border"]) |
| sheet.write(row_index, 5, finding.expected or "", formats["text_border"]) |
| sheet.write(row_index, 6, finding.actual or "", formats["text_border"]) |
| sheet.write(row_index, 7, finding.sheet or "", formats["text_border"]) |
| result = "İncele" if finding.severity != Severity.INFO else "Bilgilendirme" |
| sheet.write(row_index, 8, result, severity_format) |
|
|
| last_row = header_row + max(1, len(validation.findings)) |
| if validation.findings: |
| sheet.add_table( |
| header_row, |
| 0, |
| last_row, |
| len(headers) - 1, |
| { |
| "name": "ValidationFindings", |
| "style": "Table Style Medium 2", |
| "columns": [ |
| {"header": header, "header_format": formats["table_header"]} |
| for header in headers |
| ], |
| }, |
| ) |
| sheet.freeze_panes(header_row + 1, 0) |
| sheet.set_column("A:B", 22) |
| sheet.set_column("C:C", 72) |
| sheet.set_column("D:E", 13) |
| sheet.set_column("F:G", 22) |
| sheet.set_column("H:I", 20) |
| sheet.set_row(header_row + 1, 38) |
| sheet.set_row(header_row, 30) |
| _configure_print(sheet, repeat_header_row=header_row) |
|
|
|
|
| def _write_provenance(workbook, report, formats) -> None: |
| sheet = workbook.add_worksheet("Kaynak İzleri") |
| _add_title( |
| sheet, |
| "Hücre Kaynak İzleri", |
| "Her kayıt PDF sayfası ve koordinat kutusuyla eşleştirilir", |
| 8, |
| formats, |
| ) |
| headers = [ |
| "Çalışma Sayfası", |
| "Kayıt Türü", |
| "Kayıt Anahtarı", |
| "PDF Sayfası", |
| "X0", |
| "Y0", |
| "X1", |
| "Y1", |
| "Çıkarım Yöntemi", |
| ] |
| header_row = 3 |
| for column, header in enumerate(headers): |
| sheet.write(header_row, column, header) |
| records = [] |
| records.extend( |
| ("Hisse Senetleri", "Hisse", row.code, row.source) |
| for row in report.equities |
| ) |
| records.extend(("T. Repo", "Repo", row.code, row.source) for row in report.repos) |
| records.extend( |
| ("Türev", "Türev", row.code, row.source) for row in report.derivatives |
| ) |
| records.extend( |
| ("Türev", "Nakit Teminat", row.name, row.source) |
| for row in report.cash_collaterals |
| ) |
| for row_index, (sheet_name, record_type, key, source) in enumerate( |
| records, start=header_row + 1 |
| ): |
| sheet.write(row_index, 0, sheet_name, formats["text_border"]) |
| sheet.write(row_index, 1, record_type, formats["text_border"]) |
| sheet.write(row_index, 2, key, formats["text_border"]) |
| sheet.write_number(row_index, 3, source.page, formats["integer"]) |
| for offset, coordinate in enumerate(source.bbox): |
| sheet.write_number( |
| row_index, 4 + offset, coordinate, formats["decimal"] |
| ) |
| sheet.write(row_index, 8, source.method.value, formats["text_border"]) |
| sheet.add_table( |
| header_row, |
| 0, |
| header_row + len(records), |
| len(headers) - 1, |
| { |
| "name": "SourceProvenance", |
| "style": "Table Style Medium 2", |
| "columns": [ |
| {"header": header, "header_format": formats["table_header"]} |
| for header in headers |
| ], |
| }, |
| ) |
| sheet.freeze_panes(header_row + 1, 0) |
| sheet.set_column("A:C", 22) |
| sheet.set_column("D:I", 15) |
| sheet.set_row(header_row, 30) |
| _configure_print(sheet, repeat_header_row=header_row) |
|
|
|
|
| def write_workbook( |
| report: FundPortfolioReport, |
| validation: ValidationResult, |
| output_path: str | Path, |
| ) -> Path: |
| path = Path(output_path).expanduser().resolve() |
| path.parent.mkdir(parents=True, exist_ok=True) |
| workbook = xlsxwriter.Workbook( |
| path, |
| { |
| "constant_memory": False, |
| "nan_inf_to_errors": True, |
| }, |
| ) |
| workbook.set_properties( |
| { |
| "title": f"{report.metadata.fund_code or ''} Fon Portföy Raporu", |
| "subject": "FinReport Agent tarafından kaynak PDF'den oluşturuldu", |
| "author": "FinReport Agent", |
| "company": "FinReport Agent", |
| "comments": ( |
| f"Kaynak SHA-256: {report.metadata.source_sha256}. " |
| "Kontroller sayfasını inceleyin." |
| ), |
| } |
| ) |
| formats = _formats(workbook) |
| _write_summary(workbook, report, validation, formats) |
| _write_equities(workbook, report, formats) |
| _write_repos(workbook, report, formats) |
| _write_derivatives(workbook, report, formats) |
| _write_findings(workbook, report, validation, formats) |
| _write_provenance(workbook, report, formats) |
| workbook.close() |
| return path |
|
|
