import os
import uuid
import warnings
import math
import json
import sys
import subprocess
from datetime import datetime
import numpy as np
import pandas as pd
warnings.filterwarnings('default')
def ensure_package(module_name, pip_name):
    try:
        return __import__(module_name)
    except ImportError:
        cmd = [sys.executable, '-m', 'pip', 'install', pip_name]
        proc = subprocess.run(cmd, capture_output=True, text=True, timeout=900)
        if proc.returncode != 0:
            raise ImportError('Failed to install ' + pip_name + ': ' + proc.stderr)
        return __import__(module_name)
plotly_mod = ensure_package('plotly', 'plotly')
go = plotly_mod.graph_objs
ql_mod = ensure_package('QuantLib', 'QuantLib')
ql = ql_mod
ensure_package('openpyxl', 'openpyxl')
base_path = '/mnt/z/B011/'
os.makedirs(base_path, exist_ok=True)
settle_date_str = '14-Dec-2007'
recovery = 0.40
LGD = 0.60
alpha = 0.2
sigma = 0.015
n_scenarios = 1000
zero_curve_tenors = ['3M','6M','1Y','5Y','7Y','10Y','20Y','30Y']
zero_curve_rates = [0.0330,0.0340,0.0350,0.0400,0.0420,0.0440,0.0480,0.0475]
day_count_convention = 'Thirty360'
assert n_scenarios == 1000, 'n_scenarios must be 1000'
assert abs(recovery - 0.40) < 1e-12, 'Recovery must be 0.40'
assert abs(alpha - 0.2) < 1e-12, 'alpha must be 0.2'
assert abs(sigma - 0.015) < 1e-12, 'sigma must be 0.015'
report_progress('Executed: 1% - Initialized parameters')
excel_path = os.path.join(base_path, 'file-W2tu9oTd3knhEGso43TzbG.xlsx')
assert os.path.exists(excel_path), 'Input Excel file not found at ' + excel_path
swaps_df = pd.read_excel(excel_path, sheet_name='Swap Portfolio', engine='openpyxl')
cds_df = pd.read_excel(excel_path, sheet_name='CDS Spreads', engine='openpyxl')
expected_swap_cols = ['CounterpartyID','NettingID','Principal','Maturity','LegType','LegRateReceiving','LegRatePaying','LatestFloatingRate','Period']
assert list(swaps_df.columns) == expected_swap_cols, 'Swap Portfolio columns mismatch'
expected_cds_cols = ['Date','cp1','cp2','cp3','cp4','cp5']
assert list(cds_df.columns) == expected_cds_cols, 'CDS Spreads columns mismatch'
def parse_maturity_series(series):
    dates = []
    base_date = pd.to_datetime('1899-12-30')
    for v in series:
        dt = pd.NaT
        if isinstance(v, (int, float)) and not (isinstance(v, float) and math.isnan(v)):
            try:
                dt = base_date + pd.to_timedelta(int(v), unit='D')
            except Exception:
                dt = pd.NaT
        if pd.isna(dt):
            dt = pd.to_datetime(v, errors='coerce')
        dates.append(dt)
    dt_series = pd.to_datetime(pd.Series(dates), errors='coerce')
    return dt_series
maturity_dt = parse_maturity_series(swaps_df['Maturity'])
assert not maturity_dt.isna().any(), 'Some swap maturities could not be parsed'
swaps_df['MaturityDate'] = maturity_dt
cds_dates = pd.to_datetime(cds_df['Date'], errors='coerce')
assert not cds_dates.isna().any(), 'Some CDS dates could not be parsed'
report_progress('Executed: 5% - Loaded and parsed input data')
def datetime_to_ql(d):
    return ql.Date(d.day, d.month, d.year)
settle_dt = pd.to_datetime(settle_date_str, format='%d-%b-%Y')
settle_ql = datetime_to_ql(settle_dt)
calendar = ql.TARGET()
day_counter = ql.Thirty360(ql.Thirty360.USA)
ql.Settings.instance().evaluationDate = settle_ql
alive_mask = maturity_dt > settle_dt
alive_swaps = swaps_df.loc[alive_mask].reset_index(drop=True)
assert alive_swaps.shape[0] > 0, 'No swaps alive at settle date'
alive_swaps['MaturityQL'] = [datetime_to_ql(d) for d in alive_swaps['MaturityDate']]
zero_rates_base = np.array(zero_curve_rates, dtype=float)
n_tenors = len(zero_curve_tenors)
dates_curve = [settle_ql]
discounts_curve = [1.0]
tenor_years = []
for i in range(n_tenors):
    tenor_str = zero_curve_tenors[i]
    if tenor_str.endswith('M'):
        n_months = int(tenor_str[:-1])
        period = ql.Period(n_months, ql.Months)
        tenor_year = n_months / 12.0
    elif tenor_str.endswith('Y'):
        n_years = int(tenor_str[:-1])
        period = ql.Period(n_years, ql.Years)
        tenor_year = float(n_years)
    else:
        raise ValueError('Unknown tenor format: ' + tenor_str)
    maturity_ql = calendar.advance(settle_ql, period)
    yf = day_counter.yearFraction(settle_ql, maturity_ql)
    r = zero_rates_base[i]
    df = 1.0 / pow(1.0 + r / 2.0, 2.0 * yf)
    dates_curve.append(maturity_ql)
    discounts_curve.append(df)
    tenor_years.append(tenor_year)
curve = ql.DiscountCurve(dates_curve, discounts_curve, day_counter, calendar)
curve.enableExtrapolation()
yield_curve_times = [0.0]
yield_curve_rates = [zero_rates_base[0]]
for i in range(n_tenors):
    maturity_ql = dates_curve[i+1]
    yf = day_counter.yearFraction(settle_ql, maturity_ql)
    yield_curve_times.append(yf)
    yield_curve_rates.append(zero_rates_base[i])
yield_curve_times_arr = np.array(yield_curve_times, dtype=float)
yield_curve_rates_arr = np.array(yield_curve_rates, dtype=float)
yield_curve_df = pd.DataFrame({'Tenor': ['0Y'] + zero_curve_tenors, 'Years': yield_curve_times_arr, 'ZeroRate': yield_curve_rates_arr, 'DiscountFactor': discounts_curve})
fig_yield = go.Figure(data=[go.Scatter(x=tenor_years, y=zero_rates_base, mode='lines+markers', name='Zero Curve')])
fig_yield.update_layout(title='Yield Curve at Settle Date', xaxis_title='Maturity (years)', yaxis_title='Zero Rate', template='plotly_white')
yield_curve_html = 'yield_curve.html'
fig_yield.write_html(os.path.join(base_path, yield_curve_html), include_plotlyjs='cdn')
report_progress('Executed: 10% - Built initial yield curve')
max_swap_maturity_dt = alive_swaps['MaturityDate'].max()
cds_df_sorted = cds_df.copy()
cds_df_sorted['Date_dt'] = cds_dates
cds_df_sorted = cds_df_sorted.sort_values('Date_dt').reset_index(drop=True)
max_cds_maturity_dt = cds_df_sorted['Date_dt'].max()
horizon_dt = max(max_swap_maturity_dt, max_cds_maturity_dt)
horizon_ql = datetime_to_ql(horizon_dt)
simulation_dates_ql = [settle_ql]
simulation_dates_dt = [settle_dt]
one_year_ql = calendar.advance(settle_ql, ql.Period(12, ql.Months))
if one_year_ql <= horizon_ql:
    first_phase_end_ql = one_year_ql
else:
    first_phase_end_ql = horizon_ql
current_ql = settle_ql
while True:
    current_ql = calendar.advance(current_ql, ql.Period(1, ql.Months))
    if current_ql > first_phase_end_ql:
        break
    simulation_dates_ql.append(current_ql)
    dt_py = datetime(current_ql.year(), current_ql.month(), current_ql.dayOfMonth())
    simulation_dates_dt.append(dt_py)
if horizon_ql > first_phase_end_ql:
    current_ql = simulation_dates_ql[-1]
    while True:
        current_ql = calendar.advance(current_ql, ql.Period(3, ql.Months))
        if current_ql > horizon_ql:
            break
        simulation_dates_ql.append(current_ql)
        dt_py = datetime(current_ql.year(), current_ql.month(), current_ql.dayOfMonth())
        simulation_dates_dt.append(dt_py)
n_dates = len(simulation_dates_ql)
year_fractions = np.zeros(n_dates, dtype=float)
for i in range(n_dates):
    year_fractions[i] = day_counter.yearFraction(settle_ql, simulation_dates_ql[i])
dt_years = np.zeros(n_dates, dtype=float)
dt_years[0] = 0.0
for i in range(1, n_dates):
    dt_years[i] = max(year_fractions[i] - year_fractions[i-1], 0.0)
report_progress('Executed: 15% - Built simulation date grid')
np.random.seed(12345)
n_scen = n_scenarios
x_paths = np.zeros((n_dates, n_scen), dtype=float)
short_rates = np.zeros((n_dates, n_scen), dtype=float)
r0_base = float(zero_rates_base[0])
short_rates[0, :] = max(r0_base, 1e-6)
for t in range(1, n_dates):
    dt_step = dt_years[t]
    if dt_step <= 0.0:
        dt_step = 1e-6
    exp_term = math.exp(-alpha * dt_step)
    var_term = (sigma ** 2.0) / (2.0 * alpha) * (1.0 - math.exp(-2.0 * alpha * dt_step))
    if var_term < 0.0:
        var_term = 0.0
    std_dev = math.sqrt(var_term)
    z = np.random.standard_normal(n_scen)
    x_paths[t, :] = x_paths[t-1, :] * exp_term + std_dev * z
    step_rates = r0_base + x_paths[t, :]
    short_rates[t, :] = np.maximum(step_rates, 1e-6)
discount_factors = np.ones((n_dates, n_scen), dtype=float)
for t in range(1, n_dates):
    dt_step = dt_years[t]
    discount_factors[t, :] = discount_factors[t-1, :] * np.exp(-short_rates[t, :] * dt_step)
scenario_zero_rates = np.zeros((n_dates, n_tenors, n_scen), dtype=float)
for t in range(n_dates):
    base_row = zero_rates_base.reshape(n_tenors, 1)
    x_row = x_paths[t, :].reshape(1, n_scen)
    scenario_zero_rates[t, :, :] = np.maximum(base_row + x_row, 1e-6)
scenario0_zero = scenario_zero_rates[:, :, 0]
tenor_years_arr = np.array(tenor_years, dtype=float)
fig_surface = go.Figure(data=[go.Surface(x=tenor_years_arr, y=year_fractions, z=scenario0_zero, colorscale='Viridis')])
fig_surface.update_layout(title='Scenario Yield Curve Evolution (Scenario 0)', scene=dict(xaxis_title='Tenor (years)', yaxis_title='Time (years)', zaxis_title='Zero Rate'))
scenario_yield_surface_html = 'scenario_yield_surface.html'
fig_surface.write_html(os.path.join(base_path, scenario_yield_surface_html), include_plotlyjs='cdn')
report_progress('Executed: 25% - Simulated Hull-White factor and curves')
swap_notional = alive_swaps['Principal'].astype(float).to_numpy()
swap_cp_ids = alive_swaps['CounterpartyID'].astype(int).to_numpy()
swap_netting_raw = alive_swaps['NettingID'].to_list()
swap_leg_type = alive_swaps['LegType'].astype(int).to_numpy()
swap_rate_recv = alive_swaps['LegRateReceiving'].astype(float).to_numpy()
swap_rate_pay = alive_swaps['LegRatePaying'].astype(float).to_numpy()
swap_latest_float = alive_swaps['LatestFloatingRate'].astype(float).to_numpy()
swap_period = alive_swaps['Period'].astype(int).to_numpy()
swap_maturity_ql = alive_swaps['MaturityQL'].tolist()
n_trades = alive_swaps.shape[0]
swap_payment_dates = []
swap_accrual_factors = []
for j in range(n_trades):
    maturity_ql = swap_maturity_ql[j]
    pay_dates = []
    accruals = []
    current_ql = settle_ql
    while current_ql < maturity_ql:
        next_ql = calendar.advance(current_ql, ql.Period(1, ql.Years))
        if next_ql > maturity_ql:
            next_ql = maturity_ql
        if next_ql <= current_ql:
            break
        pay_dates.append(next_ql)
        delta = day_counter.yearFraction(current_ql, next_ql)
        accruals.append(delta)
        current_ql = next_ql
    swap_payment_dates.append(pay_dates)
    swap_accrual_factors.append(accruals)
zero_interp_times = yield_curve_times_arr
zero_interp_rates = yield_curve_rates_arr
swap_mtm = np.zeros((n_dates, n_trades, n_scen), dtype=float)
report_progress('Executed: 40% - Start swap valuation')
for t in range(n_dates):
    sim_date_ql = simulation_dates_ql[t]
    for j in range(n_trades):
        pay_dates_j = swap_payment_dates[j]
        accruals_j = swap_accrual_factors[j]
        if len(pay_dates_j) == 0:
            swap_mtm[t, j, :] = 0.0
            continue
        if pay_dates_j[-1] <= sim_date_ql:
            swap_mtm[t, j, :] = 0.0
            continue
        future_indices = []
        for k in range(len(pay_dates_j)):
            if pay_dates_j[k] > sim_date_ql:
                future_indices.append(k)
        n_fut = len(future_indices)
        if n_fut == 0:
            swap_mtm[t, j, :] = 0.0
            continue
        tau_fut = np.zeros(n_fut, dtype=float)
        delta_fut = np.zeros(n_fut, dtype=float)
        for m in range(n_fut):
            idx = future_indices[m]
            tau_fut[m] = day_counter.yearFraction(sim_date_ql, pay_dates_j[idx])
            delta_fut[m] = accruals_j[idx]
        zero_base_fut = np.interp(tau_fut, zero_interp_times, zero_interp_rates)
        notional = float(swap_notional[j])
        if swap_leg_type[j] == 1:
            fixed_rate = float(swap_rate_recv[j])
        else:
            fixed_rate = float(swap_rate_pay[j])
        leg_type_j = int(swap_leg_type[j])
        for s in range(n_scen):
            shift = x_paths[t, s]
            zz = zero_base_fut + shift
            P_scen = np.exp(-zz * tau_fut)
            A = float(np.sum(delta_fut * P_scen))
            if A <= 0.0:
                mtm_val = 0.0
            else:
                P_T = float(P_scen[-1])
                S_par = (1.0 - P_T) / A
                if leg_type_j == 1:
                    mtm_val = notional * (fixed_rate - S_par) * A
                else:
                    mtm_val = notional * (S_par - fixed_rate) * A
            swap_mtm[t, j, s] = mtm_val
    if n_dates > 0:
        prog_val = 40 + int(30 * float(t + 1) / float(n_dates))
        if prog_val > 70:
            prog_val = 70
        report_progress('Executed: ' + str(prog_val) + '% - Valuing swaps')
portfolio_mtm = np.sum(swap_mtm, axis=1)
scenario_indices_plot = list(range(min(50, n_scen)))
fig_portfolio_mtm = go.Figure()
for s in scenario_indices_plot:
    fig_portfolio_mtm.add_trace(go.Scatter(x=simulation_dates_dt, y=portfolio_mtm[:, s], mode='lines', name='Scenario ' + str(s)))
fig_portfolio_mtm.update_layout(title='Total Portfolio MTM Across Scenarios', xaxis_title='Date', yaxis_title='MTM', template='plotly_white')
portfolio_mtm_html = 'portfolio_mtm_scenarios.html'
fig_portfolio_mtm.write_html(os.path.join(base_path, portfolio_mtm_html), include_plotlyjs='cdn')
swap_mtm_scen0 = swap_mtm[:, :, 0]
fig_swaps_s0 = go.Figure()
for j in range(n_trades):
    fig_swaps_s0.add_trace(go.Scatter(x=simulation_dates_dt, y=swap_mtm_scen0[:, j], mode='lines', name='Swap ' + str(j)))
fig_swaps_s0.update_layout(title='Swap Prices Along Scenario 0', xaxis_title='Date', yaxis_title='MTM', template='plotly_white')
swap_prices_scenario_html = 'swap_prices_scenario.html'
fig_swaps_s0.write_html(os.path.join(base_path, swap_prices_scenario_html), include_plotlyjs='cdn')
report_progress('Executed: 70% - Completed swap valuation')
unique_cp_ids = sorted(list(set(int(x) for x in swap_cp_ids)))
n_cps = len(unique_cp_ids)
cp_id_to_index = {}
for idx_cp in range(n_cps):
    cp_id_to_index[unique_cp_ids[idx_cp]] = idx_cp
net_map = {}
net_cp_idx = []
net_trade_indices = []
for j in range(n_trades):
    cp = int(swap_cp_ids[j])
    cp_idx = cp_id_to_index[cp]
    raw_net = swap_netting_raw[j]
    net_code = ''
    if isinstance(raw_net, float) and math.isnan(raw_net):
        net_code = 'CP' + str(cp) + '_Trade' + str(j)
    else:
        try:
            net_int = int(raw_net)
            net_code = 'CP' + str(cp) + '_Net' + str(net_int)
        except Exception:
            net_code = 'CP' + str(cp) + '_Trade' + str(j)
    if net_code not in net_map:
        idx_net = len(net_cp_idx)
        net_map[net_code] = idx_net
        net_cp_idx.append(cp_idx)
        net_trade_indices.append([])
    idx_net = net_map[net_code]
    net_trade_indices[idx_net].append(j)
n_net_sets = len(net_cp_idx)
exposure_cp = np.zeros((n_dates, n_cps, n_scen), dtype=float)
portfolio_exposure = np.zeros((n_dates, n_scen), dtype=float)
for nset in range(n_net_sets):
    trade_idx_list = net_trade_indices[nset]
    if len(trade_idx_list) == 0:
        continue
    trade_idx_arr = np.array(trade_idx_list, dtype=int)
    net_mtm = np.sum(swap_mtm[:, trade_idx_arr, :], axis=1)
    exposure_set = np.maximum(net_mtm, 0.0)
    cp_idx = net_cp_idx[nset]
    exposure_cp[:, cp_idx, :] += exposure_set
    portfolio_exposure += exposure_set
report_progress('Executed: 80% - Computed netting and exposures')
EE_portfolio = np.mean(portfolio_exposure, axis=1)
PFE95_portfolio = np.quantile(portfolio_exposure, 0.95, axis=1)
MPFE_portfolio = float(np.max(PFE95_portfolio))
horizon_years = float(year_fractions[-1]) if year_fractions[-1] > 0.0 else 1.0
EPE_portfolio = float(np.sum(EE_portfolio * dt_years) / horizon_years)
Effective_EE_portfolio = np.maximum.accumulate(EE_portfolio)
Effective_EPE_portfolio = float(np.sum(Effective_EE_portfolio * dt_years) / horizon_years)
EE_cp = np.mean(exposure_cp, axis=2)
PFE95_cp = np.quantile(exposure_cp, 0.95, axis=2)
fig_exposure_port = go.Figure()
fig_exposure_port.add_trace(go.Scatter(x=simulation_dates_dt, y=EE_portfolio, mode='lines', name='EE'))
fig_exposure_port.add_trace(go.Scatter(x=simulation_dates_dt, y=PFE95_portfolio, mode='lines', name='PFE 95%'))
fig_exposure_port.add_trace(go.Scatter(x=simulation_dates_dt, y=Effective_EE_portfolio, mode='lines', name='Effective EE'))
fig_exposure_port.update_layout(title='Portfolio Exposure Profiles', xaxis_title='Date', yaxis_title='Exposure', template='plotly_white')
exposure_profiles_portfolio_html = 'exposure_profiles_portfolio.html'
fig_exposure_port.write_html(os.path.join(base_path, exposure_profiles_portfolio_html), include_plotlyjs='cdn')
cp1_idx = 0
if 1 in cp_id_to_index:
    cp1_idx = cp_id_to_index[1]
EE_cp1 = EE_cp[:, cp1_idx]
PFE95_cp1 = PFE95_cp[:, cp1_idx]
Effective_EE_cp1 = np.maximum.accumulate(EE_cp1)
fig_exposure_cp1 = go.Figure()
fig_exposure_cp1.add_trace(go.Scatter(x=simulation_dates_dt, y=EE_cp1, mode='lines', name='EE cp1'))
fig_exposure_cp1.add_trace(go.Scatter(x=simulation_dates_dt, y=PFE95_cp1, mode='lines', name='PFE 95% cp1'))
fig_exposure_cp1.add_trace(go.Scatter(x=simulation_dates_dt, y=Effective_EE_cp1, mode='lines', name='Effective EE cp1'))
fig_exposure_cp1.update_layout(title='Counterparty Exposure Profiles (cp1)', xaxis_title='Date', yaxis_title='Exposure', template='plotly_white')
exposure_profiles_counterparty_html = 'exposure_profiles_counterparty.html'
fig_exposure_cp1.write_html(os.path.join(base_path, exposure_profiles_counterparty_html), include_plotlyjs='cdn')
discounted_exposure_cp = exposure_cp * discount_factors[:, np.newaxis, :]
discounted_EE_cp = np.mean(discounted_exposure_cp, axis=2)
discounted_EE_portfolio = np.sum(discounted_EE_cp, axis=1)
fig_discEE_port = go.Figure()
fig_discEE_port.add_trace(go.Scatter(x=simulation_dates_dt, y=discounted_EE_portfolio, mode='lines', name='Portfolio Discounted EE'))
fig_discEE_port.update_layout(title='Discounted Expected Exposure Portfolio', xaxis_title='Date', yaxis_title='Discounted EE', template='plotly_white')
discounted_EE_portfolio_html = 'discounted_EE_portfolio.html'
fig_discEE_port.write_html(os.path.join(base_path, discounted_EE_portfolio_html), include_plotlyjs='cdn')
fig_discEE_cp = go.Figure()
for idx_cp in range(n_cps):
    fig_discEE_cp.add_trace(go.Scatter(x=simulation_dates_dt, y=discounted_EE_cp[:, idx_cp], mode='lines', name='cp' + str(unique_cp_ids[idx_cp])))
fig_discEE_cp.update_layout(title='Discounted Expected Exposure by Counterparty', xaxis_title='Date', yaxis_title='Discounted EE', template='plotly_white')
discounted_EE_counterparties_html = 'discounted_EE_counterparties.html'
fig_discEE_cp.write_html(os.path.join(base_path, discounted_EE_counterparties_html), include_plotlyjs='cdn')
report_progress('Executed: 85% - Computed exposure profiles and discounted EE')
cds_df_sorted = cds_df_sorted.reset_index(drop=True)
cds_times = []
for dt_cds in cds_df_sorted['Date_dt']:
    cds_times.append(day_counter.yearFraction(settle_ql, datetime_to_ql(dt_cds)))
cds_times_arr = np.array(cds_times, dtype=float)
n_cds = len(cds_times_arr)
default_probability = np.zeros((n_dates, n_cps), dtype=float)
for cp_idx in range(n_cps):
    cp_id = unique_cp_ids[cp_idx]
    col_name = 'cp' + str(cp_id)
    assert col_name in cds_df_sorted.columns, 'Missing CDS column for counterparty ' + str(cp_id)
    spreads_bps = cds_df_sorted[col_name].astype(float).to_numpy()
    spreads_dec = spreads_bps / 10000.0
    lambda_arr = spreads_dec / LGD
    for ti in range(n_dates):
        tval = year_fractions[ti]
        if tval <= 0.0:
            default_probability[ti, cp_idx] = 0.0
            continue
        H = 0.0
        prev = 0.0
        for k in range(n_cds):
            end = cds_times_arr[k]
            lam = lambda_arr[k]
            if tval <= end:
                H = H + lam * (tval - prev)
                break
            else:
                H = H + lam * (end - prev)
                prev = end
        if tval > cds_times_arr[-1]:
            H = H + lambda_arr[-1] * (tval - cds_times_arr[-1])
        S = math.exp(-H)
        PD = 1.0 - S
        if PD < 0.0:
            PD = 0.0
        if PD > 1.0:
            PD = 1.0
        default_probability[ti, cp_idx] = PD
fig_default = go.Figure()
for cp_idx in range(n_cps):
    fig_default.add_trace(go.Scatter(x=simulation_dates_dt, y=default_probability[:, cp_idx], mode='lines', name='cp' + str(unique_cp_ids[cp_idx])))
fig_default.update_layout(title='Default Probability Curve', xaxis_title='Date', yaxis_title='Cumulative Default Probability', template='plotly_white')
default_probabilities_html = 'default_probabilities.html'
fig_default.write_html(os.path.join(base_path, default_probabilities_html), include_plotlyjs='cdn')
incremental_PD = np.zeros((n_dates, n_cps), dtype=float)
incremental_PD[0, :] = default_probability[0, :]
for t in range(1, n_dates):
    diff_pd = default_probability[t, :] - default_probability[t-1, :]
    incremental_PD[t, :] = np.maximum(diff_pd, 0.0)
CVA_by_cp = []
for cp_idx in range(n_cps):
    cva_val = LGD * float(np.sum(discounted_EE_cp[:, cp_idx] * incremental_PD[:, cp_idx]))
    CVA_by_cp.append(cva_val)
Total_CVA = float(np.sum(CVA_by_cp))
fig_cva_cp = go.Figure()
for cp_idx in range(n_cps):
    fig_cva_cp.add_trace(go.Bar(x=['cp' + str(unique_cp_ids[cp_idx])], y=[CVA_by_cp[cp_idx]], name='cp' + str(unique_cp_ids[cp_idx])))
fig_cva_cp.update_layout(title='CVA by Counterparty', xaxis_title='Counterparty', yaxis_title='CVA', template='plotly_white', barmode='group')
cva_by_counterparty_html = 'cva_by_counterparty.html'
fig_cva_cp.write_html(os.path.join(base_path, cva_by_counterparty_html), include_plotlyjs='cdn')
report_progress('Executed: 95% - Computed default probabilities and CVA')
initial_portfolio_mtm = float(portfolio_mtm[0, 0])
total_notional = float(np.sum(swap_notional))
if total_notional != 0.0:
    initial_mtm_to_notional = float(initial_portfolio_mtm / total_notional)
else:
    initial_mtm_to_notional = 0.0
inputs_df = pd.DataFrame({'Parameter': ['SettleDate','Recovery','LGD','Alpha','Sigma','NumScenarios','DayCount'], 'Value': [settle_date_str, str(recovery), str(LGD), str(alpha), str(sigma), str(n_scenarios), day_count_convention]})
yield_curve_export_df = yield_curve_df.copy()
sim_dates_df = pd.DataFrame({'Date': simulation_dates_dt, 'YearFraction': year_fractions})
scenario_export_index = 0
swap_cols = []
for j in range(n_trades):
    swap_cols.append('Swap' + str(j))
swap_mtm_export_df = pd.DataFrame(swap_mtm[:, :, scenario_export_index], columns=swap_cols)
swap_mtm_export_df.insert(0, 'Date', simulation_dates_dt)
cp_cols = []
for cp_id in unique_cp_ids:
    cp_cols.append('cp' + str(cp_id))
exposures_cp_s0 = exposure_cp[:, :, scenario_export_index]
portfolio_exposure_s0 = portfolio_exposure[:, scenario_export_index]
exposure_export_df = pd.DataFrame({'Date': simulation_dates_dt, 'PortfolioExposure': portfolio_exposure_s0})
for idx_cp in range(n_cps):
    exposure_export_df['cp' + str(unique_cp_ids[idx_cp])] = exposures_cp_s0[:, idx_cp]
exposure_profiles_export_df = pd.DataFrame({'Date': simulation_dates_dt, 'EE_Portfolio': EE_portfolio, 'PFE95_Portfolio': PFE95_portfolio, 'Effective_EE_Portfolio': Effective_EE_portfolio})
if n_cps > 0:
    exposure_profiles_export_df['EE_cp1'] = EE_cp1
discounted_EE_export_df = pd.DataFrame({'Date': simulation_dates_dt, 'Portfolio': discounted_EE_portfolio})
for idx_cp in range(n_cps):
    discounted_EE_export_df['cp' + str(unique_cp_ids[idx_cp])] = discounted_EE_cp[:, idx_cp]
default_probs_export_df = pd.DataFrame({'Date': simulation_dates_dt})
for idx_cp in range(n_cps):
    default_probs_export_df['cp' + str(unique_cp_ids[idx_cp])] = default_probability[:, idx_cp]
CVA_export_df = pd.DataFrame({'CounterpartyID': unique_cp_ids, 'CVA': CVA_by_cp})
chart_files = [yield_curve_html, scenario_yield_surface_html, swap_prices_scenario_html, portfolio_mtm_html, 'portfolio_exposure_scenarios.html', exposure_profiles_portfolio_html, exposure_profiles_counterparty_html, discounted_EE_portfolio_html, discounted_EE_counterparties_html, default_probabilities_html, cva_by_counterparty_html]
portfolio_exposure_fig = go.Figure()
for s in scenario_indices_plot:
    portfolio_exposure_fig.add_trace(go.Scatter(x=simulation_dates_dt, y=portfolio_exposure[:, s], mode='lines', name='Scenario ' + str(s)))
portfolio_exposure_fig.update_layout(title='Portfolio Exposure Across Scenarios', xaxis_title='Date', yaxis_title='Exposure', template='plotly_white')
portfolio_exposure_scenarios_html = 'portfolio_exposure_scenarios.html'
portfolio_exposure_fig.write_html(os.path.join(base_path, portfolio_exposure_scenarios_html), include_plotlyjs='cdn')
if 'portfolio_exposure_scenarios.html' not in chart_files:
    chart_files[4] = portfolio_exposure_scenarios_html
charts_export_df = pd.DataFrame({'ChartFile': chart_files, 'Description': ['Yield Curve at Settle Date', 'Scenario Yield Curve Evolution (Scenario 0)', 'Swap Prices Along Scenario 0', 'Total Portfolio MTM Across Scenarios', 'Portfolio Exposure Across Scenarios', 'Portfolio Exposure Profiles', 'Counterparty Exposure Profiles (cp1)', 'Discounted Expected Exposure Portfolio', 'Discounted Expected Exposure by Counterparty', 'Default Probability Curve', 'CVA by Counterparty']})
excel_filename = 'cva_swap_portfolio_results.xlsx'
excel_full_path = os.path.join(base_path, excel_filename)
with pd.ExcelWriter(excel_full_path, engine='openpyxl') as writer:
    inputs_df.to_excel(writer, sheet_name='Inputs', index=False)
    yield_curve_export_df.to_excel(writer, sheet_name='Yield Curve', index=False)
    sim_dates_df.to_excel(writer, sheet_name='Simulation Dates', index=False)
    swap_mtm_export_df.to_excel(writer, sheet_name='Swap MTM', index=False)
    exposure_export_df.to_excel(writer, sheet_name='Exposures', index=False)
    exposure_profiles_export_df.to_excel(writer, sheet_name='Exposure Profiles', index=False)
    discounted_EE_export_df.to_excel(writer, sheet_name='Discounted EE', index=False)
    default_probs_export_df.to_excel(writer, sheet_name='Default Probabilities', index=False)
    CVA_export_df.to_excel(writer, sheet_name='CVA', index=False)
    charts_export_df.to_excel(writer, sheet_name='Charts', index=False)
metrics = {}
metrics['TotalCVA'] = round(Total_CVA, 2)
for idx_cp in range(n_cps):
    key_name = 'CVA_cp' + str(unique_cp_ids[idx_cp])
    metrics[key_name] = round(CVA_by_cp[idx_cp], 2)
metrics['MPFE_Portfolio'] = round(MPFE_portfolio, 2)
metrics['EPE_Portfolio'] = round(EPE_portfolio, 2)
metrics['EffectiveEPE_Portfolio'] = round(Effective_EPE_portfolio, 2)
metrics['InitialPortfolioMTM'] = round(initial_portfolio_mtm, 2)
metrics['InitialMTM_to_Notional'] = round(initial_mtm_to_notional, 6)
tables = {}
tables['CVA_by_counterparty'] = {'CounterpartyID': [int(x) for x in unique_cp_ids], 'CVA': [round(float(x), 2) for x in CVA_by_cp]}
tables['PortfolioExposureSummary'] = {'Date': [simulation_dates_dt[0], simulation_dates_dt[-1]], 'EE_Portfolio': [round(float(EE_portfolio[0]), 2), round(float(EE_portfolio[-1]), 2)], 'PFE95_Portfolio': [round(float(PFE95_portfolio[0]), 2), round(float(PFE95_portfolio[-1]), 2)]}
result = {}
result['status'] = 'ok'
result['description'] = 'Replicated the MATLAB example "Counterparty Credit Risk and CVA" in Python using QuantLib Hull-White 1F, simulated exposures, computed unilateral CVA, and exported results and charts.'
result['metrics'] = metrics
result['tables'] = tables
result['images'] = []
result['caption'] = []
file_list = [excel_filename, yield_curve_html, scenario_yield_surface_html, swap_prices_scenario_html, portfolio_mtm_html, portfolio_exposure_scenarios_html, exposure_profiles_portfolio_html, exposure_profiles_counterparty_html, discounted_EE_portfolio_html, discounted_EE_counterparties_html, default_probabilities_html, cva_by_counterparty_html]
result['files'] = file_list
report_progress('Executed: 100% - Pipeline complete')