VaR_IA / codigo_v3_api.py
Patience1643's picture
Upload 4 files
5598fa0 verified
Raw History Blame Contribute Delete
55.6 kB
import pandas as pd import numpy as np from scipy.stats import norm from scipy.interpolate import interp1d, CubicSpline from scipy.optimize import brentq, fsolve import warnings import time import os as _os # ========================================================================= # 1. MOTOR DE VOLATILIDAD SKEW Y PRICING # ========================================================================= def blsdelta(price_asset, strike, rate, time_in_year, volatility): if time_in_year <= 0: return 1.0 if price_asset > strike else 0.0 d1 = (np.log(price_asset / strike) + (rate + 0.5 * volatility**2) * time_in_year) / (volatility * np.sqrt(time_in_year)) return norm.cdf(d1) def optimize_delta(x, D, price_asset, rate, time_in_year, volatility): delta = blsdelta(price_asset, abs(x), rate, time_in_year, volatility) return 100000 * (D - delta) def get_volatility_by_skew(df_filtered, derivative_type, days_to_expirity, price_asset, price_strike, rate): if df_filtered.empty or str(derivative_type).strip().lower() not in ['call', 'put']: return np.nan H = df_filtered['Plazo'].values deltas_cols = ['X10DCall', 'X20DCall', 'X30DCall', 'X40DCall', 'X45DCall', 'ATM', 'X45DPut', 'X40DPut', 'X30DPut', 'X20DPut', 'X10DPut'] A = df_filtered[deltas_cols].values interpolator_time = interp1d(H, A, axis=0, kind='linear', fill_value='extrapolate') V = interpolator_time(days_to_expirity) if V.ndim > 1: V = V[0] D = np.array([10, 20, 30, 40, 45, 50, 55, 60, 70, 80, 90]) V1, V99 = 2 * V[0] - V[1], 2 * V[-1] - V[-2] Dmin, Dmax = 0.5 * (1 + D[0]), 0.5 * (D[-1] + 99) m = 0.5 * ((V[0] - V1) / (D[0] - 1) + (V[1] - V[0]) / (D[1] - D[0])) n = 0.5 * ((V[-1] - V99) / (D[-1] - 99) + (V[-2] - V[-1]) / (D[-2] - D[-1])) Vmin, Vmax = V[0] - (m * (D[0] - Dmin)), V[-1] - (n * (D[-1] - Dmax)) D_ext = np.array([1, Dmin] + D.tolist() + [Dmax, 99]) / 100.0 V_ext = np.array([V1, Vmin] + V.tolist() + [Vmax, V99]) time_in_year = days_to_expirity / 365.0 RowK = [] with warnings.catch_warnings(): warnings.simplefilter("ignore") for nCol in range(len(V_ext)): func = lambda x: optimize_delta(x, D_ext[nCol], price_asset, rate, time_in_year, V_ext[nCol]) low_k, high_k = price_asset * 0.0001, price_asset * 50.0 try: x_sol = brentq(func, low_k, high_k, maxiter=1000) RowK.append(abs(x_sol)) except: try: x_sol = fsolve(func, price_strike)[0] RowK.append(abs(x_sol)) except: RowK.append(np.nan) Ki, Vi = np.array(RowK), V_ext.copy() valid_idx = ~np.isnan(Ki) Ki_clean, Vi_clean = Ki[valid_idx], Vi[valid_idx] Ki_unique, unique_indices = np.unique(Ki_clean, return_index=True) Vi_unique = Vi_clean[unique_indices] if len(Ki_unique) < 2: return np.nan if price_strike < Ki_unique[0]: Ki_to_use, Vi_to_use = Ki_unique[0:2], Vi_unique[0:2] elif price_strike > Ki_unique[-1]: Ki_to_use, Vi_to_use = Ki_unique[-2:], Vi_unique[-2:] else: Ki_to_use, Vi_to_use = Ki_unique, Vi_unique if len(Ki_to_use) == 2: m_slope = (Vi_to_use[1] - Vi_to_use[0]) / (Ki_to_use[1] - Ki_to_use[0]) vol_final = Vi_to_use[0] + m_slope * (price_strike - Ki_to_use[0]) else: spline_interpolator = CubicSpline(Ki_to_use, Vi_to_use, extrapolate=True) vol_final = spline_interpolator(price_strike) return float(vol_final) # ========================================================================= # 2. MOTORES DE VALORIZACIΓ“N MTM # ========================================================================= def value_futures(df): """ValorizaciΓ³n de futuros: P&L = (precio_actual - strike) * long_short""" df = df.copy() df["Price"] = (df["AssetPrice"] - df["StrikePrice"]) * df["LongShortType"] df["Delta"] = 1 * df["LongShortType"] for col in ["Gamma", "Vega", "Rho", "Theta", "Lambda"]: df[col] = 0 df["DF"] = 1 return df def value_swaps(df): """ValorizaciΓ³n de swaps con descuento por factor DF.""" df = df.copy() T = df["T"] rate = df["Rate"] df["DF"] = 1 / ((1 + rate) ** T) df["Price"] = (df["AssetPrice"] - df["StrikePrice"]) * df["DF"] * df["LongShortType"] for col in ["Gamma", "Vega", "Rho", "Theta", "Lambda"]: df[col] = 0 df["Delta"] = df["DF"] * df["LongShortType"] return df def optstocksensbyblk_replica(futures_price, strike, time_to_maturity, risk_free_rate, volatility, is_call_option, output_spec=None): """ Black-76 vectorizado. Devuelve dict con Price, DF y gregas completas (Delta, Gamma, Vega, Rho, Theta, Lambda) cuando output_spec es None. Si output_spec es una lista de keys, devuelve solo esas. """ F, K, T, r, sigma = map(lambda x: np.asarray(x, dtype=float), [futures_price, strike, time_to_maturity, risk_free_rate, volatility]) results = {} valid_mask = (T > np.finfo(float).eps) & (sigma > np.finfo(float).eps) d1, d2 = np.full_like(F, np.nan), np.full_like(F, np.nan) if np.any(valid_mask): d1[valid_mask] = (np.log(F[valid_mask] / K[valid_mask]) + 0.5 * sigma[valid_mask]**2 * T[valid_mask]) / \ (sigma[valid_mask] * np.sqrt(T[valid_mask])) d2[valid_mask] = d1[valid_mask] - sigma[valid_mask] * np.sqrt(T[valid_mask]) N_d1, N_d2 = norm.cdf(d1), norm.cdf(d2) N_minus_d1, N_minus_d2 = norm.cdf(-d1), norm.cdf(-d2) phi_d1 = norm.pdf(d1) df_disc = np.exp(-r * T) results['DF'] = df_disc price, delta, gamma, vega, rho, theta, lam = [np.full_like(F, np.nan) for _ in range(7)] call_mask_valid = is_call_option & valid_mask put_mask_valid = ~is_call_option & valid_mask price[call_mask_valid] = df_disc[call_mask_valid] * (F[call_mask_valid] * N_d1[call_mask_valid] - K[call_mask_valid] * N_d2[call_mask_valid]) price[put_mask_valid] = df_disc[put_mask_valid] * (K[put_mask_valid] * N_minus_d2[put_mask_valid] - F[put_mask_valid] * N_minus_d1[put_mask_valid]) intrinsic_mask = ~valid_mask if np.any(intrinsic_mask): price[intrinsic_mask & is_call_option] = np.maximum(0, F[intrinsic_mask] - K[intrinsic_mask])[is_call_option[intrinsic_mask]] price[intrinsic_mask & ~is_call_option] = np.maximum(0, K[intrinsic_mask] - F[intrinsic_mask])[~is_call_option[intrinsic_mask]] results['Price'] = price if np.any(valid_mask): delta[call_mask_valid] = df_disc[call_mask_valid] * N_d1[call_mask_valid] delta[put_mask_valid] = -df_disc[put_mask_valid] * N_minus_d1[put_mask_valid] results['Delta'] = delta gamma[valid_mask] = df_disc[valid_mask] * phi_d1[valid_mask] / (F[valid_mask] * sigma[valid_mask] * np.sqrt(T[valid_mask])) results['Gamma'] = gamma vega[valid_mask] = F[valid_mask] * df_disc[valid_mask] * phi_d1[valid_mask] * np.sqrt(T[valid_mask]) * 0.01 results['Vega'] = vega * 100 rho[call_mask_valid] = K[call_mask_valid] * T[call_mask_valid] * df_disc[call_mask_valid] * N_d2[call_mask_valid] * 0.01 rho[put_mask_valid] = -K[put_mask_valid] * T[put_mask_valid] * df_disc[put_mask_valid] * N_minus_d2[put_mask_valid] * 0.01 results['Rho'] = rho * 100 theta_t1 = -(F[valid_mask] * phi_d1[valid_mask] * sigma[valid_mask] * df_disc[valid_mask]) / (2 * np.sqrt(T[valid_mask])) theta[call_mask_valid] = theta_t1[is_call_option[valid_mask]] \ - r[call_mask_valid] * K[call_mask_valid] * df_disc[call_mask_valid] * N_d2[call_mask_valid] \ + r[call_mask_valid] * F[call_mask_valid] * df_disc[call_mask_valid] * N_d1[call_mask_valid] theta[put_mask_valid] = theta_t1[~is_call_option[valid_mask]] \ + r[put_mask_valid] * K[put_mask_valid] * df_disc[put_mask_valid] * N_minus_d2[put_mask_valid] \ - r[put_mask_valid] * F[put_mask_valid] * df_disc[put_mask_valid] * N_minus_d1[put_mask_valid] results['Theta'] = theta * 100 valid_p = np.abs(price) >= 1e-14 lam[call_mask_valid & valid_p] = (F[call_mask_valid & valid_p] / price[call_mask_valid & valid_p]) * delta[call_mask_valid & valid_p] lam[put_mask_valid & valid_p] = (F[put_mask_valid & valid_p] / price[put_mask_valid & valid_p]) * delta[put_mask_valid & valid_p] results['Lambda'] = lam if output_spec is not None: return {k: results[k] for k in output_spec if k in results} return results def value_derivatives_portfolio(df_in): """ Valoriza un DataFrame de posiciones (futuros, swaps, opciones Black-76). Devuelve el DataFrame enriquecido con Price, Vol_Toneladas, CashToday, CashNetToday. """ df = df_in.copy() df["DateValuation"] = pd.to_datetime(df["DateValuation"], errors='coerce') df["DateMaturity"] = pd.to_datetime(df["DateMaturity"], errors='coerce') df["T"] = (df["DateMaturity"] - df["DateValuation"]).dt.days / 365.0 outputs = ["Price", "Delta", "Gamma", "Vega", "Rho", "Theta", "Lambda", "DF"] for col in outputs: df[col] = np.nan mask_fut = df["DerivativeType"].str.lower().str.strip() == "future" mask_swap = df["DerivativeType"].str.lower().str.strip() == "swap" mask_blk76 = df["DerivativeSubSpec"].str.lower().str.strip() == "black76" if mask_fut.any(): df.loc[mask_fut, outputs] = value_futures(df.loc[mask_fut].copy())[outputs] if mask_swap.any(): df.loc[mask_swap, outputs] = value_swaps(df.loc[mask_swap].copy())[outputs] if mask_blk76.any(): df_b = df.loc[mask_blk76] res = optstocksensbyblk_replica( df_b["AssetPrice"].values, df_b["StrikePrice"].values, df_b["T"].values, df_b["Rate"].values, df_b["Volatility"].values, df_b["DerivativeType"].str.lower().str.strip().values == "call" ) ls = df_b["LongShortType"].values for m in ['Price', 'Delta', 'Gamma', 'Vega', 'Rho', 'Theta']: if m in res: df.loc[mask_blk76, m] = res[m] * ls df.loc[mask_blk76, "DF"] = res['DF'] mask_others = ~(mask_fut | mask_swap | mask_blk76) if mask_others.any(): for c in ["Price", "Delta", "Gamma", "Vega", "Rho", "Theta", "Lambda"]: df.loc[mask_others, c] = 1.0 df.loc[mask_others, "DF"] = 1.0 dict_factores = {'CPO': 25, 'SM': 100, 'S': 5000, 'SBO': 60000, 'W': 5000, 'KW': 5000, 'MW': 5000, 'C': 5000, 'BLW': 5000, 'CL': 1000} df['FactorToneladas'] = df['TypeVolUnder'].str.strip().map(dict_factores) if df['FactorToneladas'].isnull().any(): raise ValueError(f"Faltan factores para: {df[df['FactorToneladas'].isnull()]['TypeVolUnder'].unique()}") df['Price'] = df['Price'] * df['LongShortType'] df['Vol_Toneladas']= df['CAKS'] * df['FactorToneladas'] df['CashPaid'] = df['PremiumPaid'] * df['CAKS'] * df['FactorToneladas'] df['CashToday'] = df['Price'] * df['Vol_Toneladas'] * df['LongShortType'] df['CashNetToday'] = df['CashToday'] + df['CashPaid'] return df def calc_mtm_scenario(df_base, row_portafolio, col_price, col_rate, col_vol): """Aplica un escenario de mercado a una fila del portafolio y devuelve CashNetToday.""" df_eval = pd.DataFrame(index=df_base.index) df_eval['AssetPrice'] = df_base[col_price] df_eval['Rate'] = df_base[col_rate] df_eval['Volatility'] = df_base[col_vol] cols_static = ["StrikePrice", "LongShortType", "CAKS", "PremiumPaid", "DerivativeType", "DerivativeSubSpec", "DateValuation", "DateMaturity", "TypeVolUnder"] for c in cols_static: df_eval[c] = row_portafolio[c] return value_derivatives_portfolio(df_eval)['CashNetToday'] # ========================================================================= # 3. PROCESAMIENTO CENTRAL β€” SIMULACIΓ“N E INCREMENTAL (CachΓ© en Memoria) # ========================================================================= _DIR = _os.path.dirname(_os.path.abspath(__file__)) INPUT_PATH_DEFAULT = _os.environ.get("VAR_INPUT_PATH", _os.path.join(_DIR, "Inputs_4.xlsx")) OUTPUT_PATH_DEFAULT = _os.environ.get("VAR_OUTPUT_PATH", _os.path.join(_DIR, "OUTPUT_VaR_v3.xlsx")) _DATOS_MERCADO = None # dict con df_precio, df_tasa, df_volat _PORTAFOLIO_BASE = None # DataFrame del portafolio actual _SERIES_BASE = None # Lista de dicts de _procesar_operacion() para el portafolio base def _cargar_datos_mercado(input_path=INPUT_PATH_DEFAULT): """ Lee el Excel de inputs y deja los datos de mercado en memoria. Se llama UNA SOLA VEZ al arrancar el servidor Flask. Devuelve (df_port, df_precio, df_tasa, df_volat) ya limpios. """ global _DATOS_MERCADO, _PORTAFOLIO_BASE, _SERIES_BASE _SERIES_BASE = None # resetear cachΓ© de series al recargar datos df_port = pd.read_excel(input_path, sheet_name='PORTAFOLIO') df_precio = pd.read_excel(input_path, sheet_name='PRECIO') df_tasa = pd.read_excel(input_path, sheet_name='TASA') df_volat = pd.read_excel(input_path, sheet_name='VOLATILIDAD') for df in [df_port, df_precio, df_tasa, df_volat]: df.columns = df.columns.str.strip() column_mapping_skew = { "date": "Date", "underlying": "Underlying", "plazo": "Plazo", "x10dcall": "X10DCall", "10dcall": "X10DCall", "x20dcall": "X20DCall", "20dcall": "X20DCall", "x30dcall": "X30DCall", "30dcall": "X30DCall", "x40dcall": "X40DCall", "40dcall": "X40DCall", "x45dcall": "X45DCall", "45dcall": "X45DCall", "atm": "ATM", "x45dput": "X45DPut", "45dput": "X45DPut", "x40dput": "X40DPut", "40dput": "X40DPut", "x30dput": "X30DPut", "30dput": "X30DPut", "x20dput": "X20DPut", "20dput": "X20DPut", "x10dput": "X10DPut", "10dput": "X10DPut" } df_volat.rename(columns=lambda x: column_mapping_skew.get(str(x).strip().lower(), str(x).strip()), inplace=True) df_port['DateValuation'] = pd.to_datetime(df_port['DateValuation']) df_port['DateMaturity'] = pd.to_datetime(df_port['DateMaturity']) df_precio['Date'] = pd.to_datetime(df_precio['Date']) df_volat['Date'] = pd.to_datetime(df_volat['Date']) df_tasa['Fecha'] = pd.to_datetime(df_tasa['Fecha'].astype(str), format='%Y%m%d') _PORTAFOLIO_BASE = df_port.copy() _DATOS_MERCADO = {'df_precio': df_precio, 'df_tasa': df_tasa, 'df_volat': df_volat} print(f"βœ… Datos de mercado cargados en memoria ({len(df_port)} operaciones base)") return df_port, df_precio, df_tasa, df_volat def _procesar_operacion(row, df_precio, df_tasa, df_volat): """ Dado un row del portafolio y los DataFrames de mercado, calcula la serie histΓ³rica de escenarios MTM para ESA operaciΓ³n. MEJORA v3-nueva: vectorizaciΓ³n de tasa y vol con np.searchsorted (elimina el loop interno por fecha histΓ³rica β†’ ~3x mΓ‘s rΓ‘pido). Devuelve un dict con nro_oper, base_mtm, base_price, commodity, instrumento, contrato, posicion, volumen, strike, y las series pnl_all / pnl_price / pnl_tasa / pnl_volat. Devuelve None si no hay precios histΓ³ricos suficientes. """ nro_oper = str(row['NroOper']) ticker = row['Ticker'] tipo_subyacente_vol = row['TypeVolUnder'].strip() deriv_type = row['DerivativeType'] strike = row['StrikePrice'] d_val = row['DateValuation'] d_mat = row['DateMaturity'] plazo_fijo = max((d_mat - d_val).days, 1) es_opcion = str(deriv_type).strip().lower() in ['call', 'put'] or \ str(row.get('DerivativeSubSpec', '')).strip().lower() == 'black76' precios_op = df_precio[ (df_precio['Ticker'].str.strip() == ticker.strip()) & (df_precio['Date'] <= d_val) ].copy() precios_op = precios_op.sort_values('Date').tail(254) if precios_op.empty: return None # ── VectorizaciΓ³n de Tasa ──────────────────────────────────────────── fechas_tasa = np.sort(df_tasa[df_tasa['Fecha'] <= d_val]['Fecha'].dropna().unique()) tasa_dict = {} for ft in fechas_tasa: curva = df_tasa[df_tasa['Fecha'] == ft] if len(curva) > 1: curva = curva.drop_duplicates(subset=['Plazo']).sort_values('Plazo') f = interp1d(curva['Plazo'], curva['Tasa'], kind='linear', fill_value='extrapolate') tasa_dict[ft] = float(f(plazo_fijo)) else: tasa_dict[ft] = float(curva['Tasa'].iloc[0]) fechas_array_tasa = np.array(fechas_tasa) tasas_array = np.array([tasa_dict[d] for d in fechas_tasa]) # ── VectorizaciΓ³n de Volatilidad ───────────────────────────────────── fechas_vol_validas = np.array([]) vol_base = pd.DataFrame() if es_opcion: vol_base = df_volat[ (df_volat['Underlying'].astype(str).str.strip() == tipo_subyacente_vol) & (df_volat['Date'] <= d_val) ].copy() fechas_vol_validas = np.sort(vol_base['Date'].dropna().unique()) # ── Loop por escenario histΓ³rico ───────────────────────────────────── resultados = [] for hist_date, asset_price in zip(precios_op['Date'], precios_op['AssetPrice']): hist_date_np = np.datetime64(hist_date) idx_tasa = np.searchsorted(fechas_array_tasa, hist_date_np, side='right') - 1 tasa_interp = tasas_array[idx_tasa] if idx_tasa >= 0 else 0.0 vol_skew = 0.0 if es_opcion and len(fechas_vol_validas) > 0: idx_vol = np.searchsorted(fechas_vol_validas, hist_date_np, side='right') - 1 if idx_vol >= 0: fecha_vol_usada = fechas_vol_validas[idx_vol] curva_vol = vol_base[vol_base['Date'] == fecha_vol_usada] vol_skew = get_volatility_by_skew(curva_vol, deriv_type, plazo_fijo, asset_price, strike, tasa_interp) resultados.append({ 'Hist_Date': hist_date.date(), 'AssetPrice': float(asset_price), 'Tasa_Interp': float(tasa_interp), 'Volat_Interp': float(vol_skew) if pd.notna(vol_skew) else 0.0, }) df_res = pd.DataFrame(resultados).sort_values('Hist_Date') with np.errstate(divide='ignore', invalid='ignore'): df_res['AssetPrice_LogDiff'] = np.log(df_res['AssetPrice'] / df_res['AssetPrice'].shift(1)) df_res['Tasa_LogDiff'] = np.log(df_res['Tasa_Interp'] / df_res['Tasa_Interp'].shift(1)) df_res['Volat_LogDiff'] = np.log(df_res['Volat_Interp'] / df_res['Volat_Interp'].shift(1)) df_res[['AssetPrice_LogDiff', 'Tasa_LogDiff', 'Volat_LogDiff']] = \ df_res[['AssetPrice_LogDiff', 'Tasa_LogDiff', 'Volat_LogDiff']].replace([np.inf, -np.inf], np.nan) df_res = df_res.dropna(subset=['AssetPrice_LogDiff']).copy() if df_res.empty: print(f" ⚠️ Ticker '{ticker}' tiene datos insuficientes (menos de 2 precios).") return None df_res[['AssetPrice_LogDiff', 'Tasa_LogDiff', 'Volat_LogDiff']] = \ df_res[['AssetPrice_LogDiff', 'Tasa_LogDiff', 'Volat_LogDiff']].fillna(0.0) base_price = float(df_res['AssetPrice'].iloc[-1]) base_tasa = float(df_res['Tasa_Interp'].iloc[-1]) base_vol = float(df_res['Volat_Interp'].iloc[-1]) df_res['BASE_AssetPrice'] = base_price df_res['BASE_Tasa'] = base_tasa df_res['BASE_Volat'] = base_vol df_res['SIM_AssetPrice'] = base_price * (1 + df_res['AssetPrice_LogDiff']) df_res['SIM_Tasa_Interp'] = base_tasa + df_res['Tasa_LogDiff'] df_res['SIM_Volat_Interp'] = base_vol + df_res['Volat_LogDiff'] mtm_all = calc_mtm_scenario(df_res, row, 'SIM_AssetPrice', 'SIM_Tasa_Interp', 'SIM_Volat_Interp') mtm_price = calc_mtm_scenario(df_res, row, 'SIM_AssetPrice', 'BASE_Tasa', 'BASE_Volat') mtm_tasa = calc_mtm_scenario(df_res, row, 'BASE_AssetPrice', 'SIM_Tasa_Interp', 'BASE_Volat') mtm_volat = calc_mtm_scenario(df_res, row, 'BASE_AssetPrice', 'BASE_Tasa', 'SIM_Volat_Interp') mtm_base = calc_mtm_scenario(df_res, row, 'BASE_AssetPrice', 'BASE_Tasa', 'BASE_Volat') base_mtm = float(mtm_base.iloc[0]) idx = df_res['Hist_Date'].values dict_factores = {'CPO': 25, 'SM': 100, 'S': 5000, 'SBO': 60000, 'W': 5000, 'KW': 5000, 'MW': 5000, 'C': 5000, 'BLW': 5000, 'CL': 1000} volumen_ton = float(row['CAKS'] * dict_factores.get(tipo_subyacente_vol, 1)) return { 'nro_oper': nro_oper, 'commodity': tipo_subyacente_vol, 'instrumento': str(deriv_type).strip().upper(), 'contrato': str(ticker).strip(), 'posicion': "Long" if row['LongShortType'] == 1 else "Short", 'volumen': volumen_ton, 'strike': float(strike), 'base_mtm': base_mtm, 'base_price': base_price, 'pnl_all': pd.Series(mtm_all.values - base_mtm, index=idx, name=nro_oper), 'pnl_price': pd.Series(mtm_price.values - base_mtm, index=idx, name=nro_oper), 'pnl_tasa': pd.Series(mtm_tasa.values - base_mtm, index=idx, name=nro_oper), 'pnl_volat': pd.Series(mtm_volat.values - base_mtm, index=idx, name=nro_oper), } def _calcular_var_desde_series(resultados_ops, N_DIAS_VAR=253): """ Recibe lista de dicts de _procesar_operacion(), construye matrices de P&L, aplica ventana de 253 dΓ­as y calcula VaR + Euler normalizado. MEJORA v3-nueva: Euler normalizado con factor de ajuste (correcciΓ³n de fuga residual no lineal β€” suma de betas = 1 garantizada). Devuelve dict listo para serializar a JSON (compatible con var_api.py y dashboard). """ if not resultados_ops: raise ValueError("No hay operaciones procesadas") def _dedup(s): return s[~s.index.duplicated(keep='last')] def _build_matrix(key): series_list = [_dedup(r[key]).rename(r['nro_oper']) for r in resultados_ops] df = series_list[0].to_frame() for s in series_list[1:]: df = df.join(s.to_frame(), how='outer') return df df_all = _build_matrix('pnl_all') df_price = _build_matrix('pnl_price') df_tasa = _build_matrix('pnl_tasa') df_volat = _build_matrix('pnl_volat') base_mtm_dict = {r['nro_oper']: r['base_mtm'] for r in resultados_ops} oper_to_commodity = {r['nro_oper']: r['commodity'] for r in resultados_ops} ops = list(df_all.columns) for _df in [df_all, df_price, df_tasa, df_volat]: _df.drop_duplicates(keep='last', inplace=True) df_all = df_all.sort_index().fillna(0).tail(N_DIAS_VAR) fechas_v = df_all.index df_price = df_price.reindex(fechas_v).fillna(0) df_tasa = df_tasa.reindex(fechas_v).fillna(0) df_volat = df_volat.reindex(fechas_v).fillna(0) pnl_total = df_all[ops].sum(axis=1) var_95 = float(np.percentile(pnl_total.dropna(), 5)) var_99 = float(np.percentile(pnl_total.dropna(), 1)) var_05 = float(np.percentile(pnl_total.dropna(), 95)) var_pnl = float(pnl_total.var()) base_total = float(sum(base_mtm_dict.values())) # ── VaR individual y detalle operativo ─────────────────────────────── var_95_dict = {} sum_var_95 = 0.0 sum_var_99 = 0.0 operaciones_detalle = [] for r in resultados_ops: op = r['nro_oper'] v95 = float(np.percentile(df_all[op].dropna(), 5)) v99 = float(np.percentile(df_all[op].dropna(), 1)) var_95_dict[op] = v95 sum_var_95 += v95 sum_var_99 += v99 ls_sign = 1 if r['posicion'] == "Long" else -1 operaciones_detalle.append({ 'id': op, 'instr': r['instrumento'], 'sub': r['commodity'], 'cont': r['contrato'], 'pos': r['posicion'], 'vol': float(r['volumen']), 'strike': float(r['strike']), 'asset': float(r['base_price']), 'notional': float(r['volumen'] * r['strike'] * ls_sign), 'mtm': float(base_mtm_dict[op]), 'var95': v95, 'var99': v99, }) # ── Euler normalizado con factor de ajuste ─────────────────────────── # Suma de betas crudos antes de normalizar commodities = list(set(oper_to_commodity.values())) beta_f_crudos = {} suma_betas_crudos = 0.0 for comm in commodities: ops_c = [o for o, c in oper_to_commodity.items() if c == comm] beta_f_crudos[comm] = {} for f_name, df_f in [('precio', df_price), ('tasa', df_tasa), ('volat', df_volat)]: pnl_fc = df_f[ops_c].sum(axis=1) beta_fc = float(np.cov(pnl_fc.values, pnl_total.values)[0, 1] / var_pnl) if var_pnl != 0 else 0.0 beta_f_crudos[comm][f_name] = beta_fc suma_betas_crudos += beta_fc factor_ajuste = 1.0 / suma_betas_crudos if suma_betas_crudos != 0 else 1.0 aportes = [] for comm in commodities: ops_c = [o for o, c in oper_to_commodity.items() if c == comm] beta_c_total = 0.0 euler_f = {} for f_name in ['precio', 'tasa', 'volat']: beta_ajustado = beta_f_crudos[comm][f_name] * factor_ajuste beta_c_total += beta_ajustado euler_f[f'component_var_{f_name}'] = float(round(beta_ajustado * var_95, 2)) aportes.append({ 'commodity': comm, 'pct_aporte': float(round(beta_c_total, 4)), 'total_component_var': float(round(beta_c_total * var_95, 2)), **euler_f, }) # ── Correlaciones para dashboard IA ────────────────────────────────── corr_summary = [] for comm in commodities: ops_c = [o for o, c in oper_to_commodity.items() if c == comm] pnl_c = df_all[ops_c].sum(axis=1) sigma_comm = float(pnl_c.std()) rho = float(pnl_c.corr(pnl_total)) vol_total_comm = sum(r['volumen'] for r in resultados_ops if r['commodity'] == comm) corr_summary.append({ 'commodity': comm, 'sigma_diaria_pnl': round(sigma_comm, 2), 'rho_con_portafolio': round(rho, 4), 'rho_long_vs_portafolio': round(-rho, 4), 'interpretacion': 'Long reduce riesgo' if rho > 0 else 'Long aumenta riesgo', }) diversificacion = float(round(sum_var_95 - var_95, 2)) # ── % VaR / Nominal neto ────────────────────────────────────────────── nominal_neto = sum(d['notional'] for d in operaciones_detalle) pct_var_nominal = round(abs(var_95) / abs(nominal_neto) * 100, 4) if nominal_neto != 0 else 0.0 return { 'var_95': round(var_95, 2), 'var_99': round(var_99, 2), 'var_05_ganancia': round(var_05, 2), 'mtm_total': round(base_total, 2), 'mtm_adverso_95': round(base_total + var_95, 2), 'mtm_adverso_99': round(base_total + var_99, 2), 'suma_var_individuales': round(sum_var_95, 2), 'efecto_diversificacion': diversificacion, 'pct_diversificacion': round(diversificacion / sum_var_95 * 100, 1) if sum_var_95 != 0 else 0.0, 'pct_var_nominal': pct_var_nominal, 'n_escenarios': int(len(pnl_total)), 'n_operaciones': len(operaciones_detalle), 'aportes_commodity': aportes, 'operaciones': operaciones_detalle, 'corr_summary': corr_summary, 'pnl_escenarios': [round(v, 2) for v in pnl_total.dropna().tolist()], } # ========================================================================= # 4. FUNCIONES DE API β€” simulate_portfolio, calc_incremental_var, optimize_hedge # ========================================================================= def simulate_portfolio(df_port_override, input_path=INPUT_PATH_DEFAULT): """ FunciΓ³n principal de simulaciΓ³n. Acepta un DataFrame de portafolio (puede incluir posiciones hipotΓ©ticas) y devuelve el dict completo de KPIs de VaR. Si el df coincide con _PORTAFOLIO_BASE, reutiliza _SERIES_BASE (cachΓ©). """ global _DATOS_MERCADO, _SERIES_BASE, _PORTAFOLIO_BASE if _DATOS_MERCADO is None: _cargar_datos_mercado(input_path) df_precio = _DATOS_MERCADO['df_precio'] df_tasa = _DATOS_MERCADO['df_tasa'] df_volat = _DATOS_MERCADO['df_volat'] es_portafolio_base = ( _PORTAFOLIO_BASE is not None and len(df_port_override) == len(_PORTAFOLIO_BASE) and set(df_port_override['NroOper'].astype(str)) == set(_PORTAFOLIO_BASE['NroOper'].astype(str)) ) if es_portafolio_base and _SERIES_BASE is not None: print(f"⚑ simulate_portfolio: usando cachΓ© ({len(_SERIES_BASE)} ops)") resultado = _calcular_var_desde_series(_SERIES_BASE) resultado['tiempo_segundos'] = 0.0 return resultado print(f"πŸ”„ simulate_portfolio: procesando {len(df_port_override)} operaciones...") t0 = time.time() resultados = [] for _, row in df_port_override.iterrows(): res = _procesar_operacion(row, df_precio, df_tasa, df_volat) if res is not None: resultados.append(res) else: print(f" ⚠️ Sin precios histΓ³ricos para {row['NroOper']} / {row['Ticker']}") if es_portafolio_base or _SERIES_BASE is None: _SERIES_BASE = resultados print(f" πŸ’Ύ Series base guardadas en cachΓ© ({len(resultados)} ops)") resultado = _calcular_var_desde_series(resultados) resultado['tiempo_segundos'] = round(time.time() - t0, 1) print(f" βœ… Completado en {resultado['tiempo_segundos']}s") return resultado def calc_incremental_var(new_position: dict, input_path=INPUT_PATH_DEFAULT): """ Calcula el VaR del portafolio actual + UNA posiciΓ³n hipotΓ©tica. OPTIMIZACIΓ“N: reutiliza _SERIES_BASE (cachΓ© del portafolio base) y solo procesa la operaciΓ³n nueva. Tiempo: ~45s β†’ ~5-10s. ParΓ‘metros requeridos en new_position: NroOper, Ticker, TypeVolUnder, DerivativeType, DerivativeSubSpec, LongShortType, CAKS, StrikePrice, PremiumPaid, DateValuation, DateMaturity """ global _PORTAFOLIO_BASE, _SERIES_BASE, _DATOS_MERCADO if _PORTAFOLIO_BASE is None: _cargar_datos_mercado(input_path) new_position = new_position.copy() new_position['DateValuation'] = pd.to_datetime(new_position['DateValuation']) new_position['DateMaturity'] = pd.to_datetime(new_position['DateMaturity']) df_precio = _DATOS_MERCADO['df_precio'] df_tasa = _DATOS_MERCADO['df_tasa'] df_volat = _DATOS_MERCADO['df_volat'] # ── 1. Asegurar cachΓ© del portafolio base ──────────────────────────── if _SERIES_BASE is None: print("πŸ”„ Cargando series base por primera vez...") t0 = time.time() series_base = [] for _, row in _PORTAFOLIO_BASE.iterrows(): res = _procesar_operacion(row, df_precio, df_tasa, df_volat) if res is not None: series_base.append(res) _SERIES_BASE = series_base print(f" πŸ’Ύ Series base cacheadas en {round(time.time()-t0, 1)}s") else: print(f"⚑ Usando cachΓ© de series base ({len(_SERIES_BASE)} ops)") # VaR actual (desde cachΓ© β€” operaciΓ³n instantΓ‘nea) resultado_actual = _calcular_var_desde_series(_SERIES_BASE) # ── 2. Procesar SOLO la nueva operaciΓ³n ───────────────────────────── print(f"πŸ”„ Procesando nueva op: {new_position.get('NroOper')} / {new_position.get('Ticker')}...") t1 = time.time() nueva_fila = pd.Series(new_position) res_nueva = _procesar_operacion(nueva_fila, df_precio, df_tasa, df_volat) if res_nueva is None: raise ValueError( f"No se encontraron precios histΓ³ricos para '{new_position.get('Ticker')}'. " f"Verifica que el ticker exista en la hoja PRECIO del Excel." ) # ── 3. Combinar series base + nueva operaciΓ³n ──────────────────────── series_extendidas = _SERIES_BASE + [res_nueva] resultado_nuevo = _calcular_var_desde_series(series_extendidas) print(f" βœ… Nueva op procesada en {round(time.time()-t1, 1)}s") return { 'portafolio_actual': resultado_actual, 'portafolio_nuevo': resultado_nuevo, 'delta_var_95': round(resultado_nuevo['var_95'] - resultado_actual['var_95'], 2), 'delta_mtm': round(resultado_nuevo['mtm_total'] - resultado_actual['mtm_total'], 2), 'nueva_operacion': {k: str(v) for k, v in new_position.items()}, } def optimize_hedge(commodity: str, target_var_95: float, deriv_type='Future', deriv_subspec='', long_short=1, ticker_override=None, input_path=INPUT_PATH_DEFAULT, max_iter=20, tolerancia=500.0): """ Encuentra el volumen (CAKS) de una nueva posiciΓ³n que lleva el VaR 95% del portafolio al target indicado, usando bΓΊsqueda binaria sobre el motor real. ParΓ‘metros: commodity : 'S', 'SM', 'C', 'SBO', 'KW', etc. target_var_95 : VaR objetivo en USD (negativo, ej. -100000) deriv_type : tipo de derivado de cobertura long_short : 1=Long, -1=Short ticker_override: ticker del contrato (None = mΓ‘s reciente del portafolio) max_iter : mΓ‘ximo de iteraciones de bΓΊsqueda binaria tolerancia : margen de error aceptable en USD """ global _PORTAFOLIO_BASE, _DATOS_MERCADO if _PORTAFOLIO_BASE is None: _cargar_datos_mercado(input_path) ops_comm = _PORTAFOLIO_BASE[_PORTAFOLIO_BASE['TypeVolUnder'].str.strip() == commodity] if ops_comm.empty: raise ValueError(f"Commodity '{commodity}' no encontrado en el portafolio base") ref_row = ops_comm.iloc[-1] ticker = ticker_override or ref_row['Ticker'] d_val = ref_row['DateValuation'] d_mat = ref_row['DateMaturity'] precios_ticker = _DATOS_MERCADO['df_precio'][ _DATOS_MERCADO['df_precio']['Ticker'].str.strip() == ticker.strip() ].sort_values('Date') if precios_ticker.empty: tickers_comm = _PORTAFOLIO_BASE[ _PORTAFOLIO_BASE['TypeVolUnder'].str.strip() == commodity ]['Ticker'].unique() for t_alt in tickers_comm: alt = _DATOS_MERCADO['df_precio'][ _DATOS_MERCADO['df_precio']['Ticker'].str.strip() == t_alt.strip() ].sort_values('Date') if not alt.empty: ticker = t_alt precios_ticker = alt print(f" ⚠️ Ticker '{ticker_override}' sin datos β€” usando '{t_alt}' como fallback") break if precios_ticker.empty: raise ValueError( f"Ticker '{ticker}' sin datos histΓ³ricos. " f"Tickers disponibles para {commodity}: {list(tickers_comm)}" ) strike_ref = float(precios_ticker['AssetPrice'].iloc[-1]) pos_template = { 'NroOper': '99999', 'Ticker': ticker, 'TypeVolUnder': commodity, 'DerivativeType': deriv_type, 'DerivativeSubSpec': deriv_subspec, 'LongShortType': long_short, 'StrikePrice': strike_ref, 'PremiumPaid': 0.0, 'DateValuation': d_val, 'DateMaturity': d_mat, } t_opt_inicio = time.time() resultado_base = simulate_portfolio(_PORTAFOLIO_BASE, input_path) var_actual = resultado_base['var_95'] print(f"🎯 optimize_hedge: commodity={commodity}, target={target_var_95:.0f}, " f"long_short={'Long' if long_short==1 else 'Short'}, ticker={ticker}") dict_factores = {'CPO': 25, 'SM': 100, 'S': 5000, 'SBO': 60000, 'W': 5000, 'KW': 5000, 'MW': 5000, 'C': 5000, 'BLW': 5000, 'CL': 1000} factor = dict_factores.get(commodity, 1) # EstimaciΓ³n paramΓ©trica de V_optimal para acotar la bΓΊsqueda binaria _SIGMA_PRECIO = {'SM': 3.518, 'S': 0.046059, 'C': 0.034670, 'SBO': 0.001067} _RHO_REDUCE = {'SM': 0.6484, 'S': 0.5524, 'C': 0.3762, 'SBO': 0.1008} sigma_p_est = abs(var_actual) / 1.645 sigma_precio_p = _SIGMA_PRECIO.get(commodity, 0.01) rho_p = _RHO_REDUCE.get(commodity, 0.3) caks_optimal_est = (rho_p * sigma_p_est) / (sigma_precio_p * factor) caks_hi_limit = max(caks_optimal_est * 1.3, 50.0) print(f" πŸ“ V_optimal est β‰ˆ {caks_optimal_est:.0f} contratos | bΓΊsqueda [0, {caks_hi_limit:.0f}]") historial = [] mejor_caks = None mejor_var = None # Verificar si el target es alcanzable en el punto Γ³ptimo pos_hi = {**pos_template, 'CAKS': caks_hi_limit} res_hi = calc_incremental_var(pos_hi, input_path) var_hi = res_hi['portafolio_nuevo']['var_95'] historial.append({'caks': round(caks_hi_limit, 2), 'var_95': round(var_hi, 2), 'iter': 0}) if var_hi <= target_var_95: print(f" ⚠️ Target {target_var_95:.0f} inalcanzable. MΓ­nimo alcanzable β‰ˆ {var_hi:.0f}") mejor_caks = 0.0 mejor_var = var_actual else: caks_lo, caks_hi = 0.0, caks_hi_limit for i in range(1, max_iter + 1): caks_mid = (caks_lo + caks_hi) / 2.0 pos_mid = {**pos_template, 'CAKS': caks_mid} res_mid = calc_incremental_var(pos_mid, input_path) var_mid = res_mid['portafolio_nuevo']['var_95'] historial.append({'caks': round(caks_mid, 2), 'var_95': round(var_mid, 2), 'iter': i}) print(f" optimize iter {i}: CAKS={caks_mid:.1f} β†’ VaR={var_mid:.0f} (target={target_var_95:.0f})") if abs(var_mid - target_var_95) <= tolerancia: mejor_caks = caks_mid mejor_var = var_mid break if var_mid < target_var_95: caks_lo = caks_mid else: caks_hi = caks_mid if mejor_caks is None: caks_mid = (caks_lo + caks_hi) / 2.0 res_final = calc_incremental_var({**pos_template, 'CAKS': caks_mid}, input_path) mejor_caks = caks_mid mejor_var = res_final['portafolio_nuevo']['var_95'] t_opt_total = round(time.time() - t_opt_inicio, 1) volumen_final = round(mejor_caks * factor, 0) notional_final = round(long_short * volumen_final * strike_ref, 0) print(f"βœ… optimize_hedge: {t_opt_total}s | {len(historial)} iter | " f"CAKS={round(mejor_caks,2)} | VaR={round(mejor_var,2)}") return { 'commodity': commodity, 'ticker': ticker, 'long_short': 'Long' if long_short == 1 else 'Short', 'caks_optimo': round(mejor_caks, 2), 'volumen_tm': volumen_final, 'strike_ref': round(strike_ref, 4), 'notional': notional_final, 'var_95_actual': round(var_actual, 2), 'var_95_objetivo': round(target_var_95, 2), 'var_95_alcanzado': round(mejor_var, 2), 'diferencia_vs_target': round(mejor_var - target_var_95, 2), 'historial_iteraciones': historial, 'iteraciones': len(historial), 'tiempo_segundos': t_opt_total, } # ========================================================================= # 5. GENERACIΓ“N DE EXCEL β€” REPORTING Y AUDITORÍA # ========================================================================= def generar_datos_var(output_path=OUTPUT_PATH_DEFAULT): """ Calcula el VaR completo desde el Excel de inputs y genera un archivo de auditorΓ­a con mΓΊltiples hojas: KPIs, PnL por operaciΓ³n, Resumen VaR, Aporte por Commodity/Factor (Euler), Detalle Portafolio, Correlaciones. """ input_path = INPUT_PATH_DEFAULT t_inicio = time.time() print("πŸ“₯ Cargando datos desde Excel...") df_port, df_precio, df_tasa, df_volat = _cargar_datos_mercado(input_path) print(f"πŸš€ Iniciando procesamiento para {len(df_port)} operaciones...") df_resumen_mtm = pd.DataFrame() df_mtm_price = pd.DataFrame() df_mtm_tasa = pd.DataFrame() df_mtm_volat = pd.DataFrame() base_mtm_dict = {} base_price_dict = {} oper_to_commodity = {} with pd.ExcelWriter(output_path, engine='xlsxwriter') as writer: for idx, row in df_port.iterrows(): nro_oper = str(row['NroOper']) ticker = row['Ticker'] tipo_subyacente_vol = row['TypeVolUnder'].strip() d_val = row['DateValuation'] d_mat = row['DateMaturity'] oper_to_commodity[nro_oper] = tipo_subyacente_vol print(f" -> OperaciΓ³n {nro_oper} ({ticker})...") t_op = time.time() res = _procesar_operacion(row, df_precio, df_tasa, df_volat) if res is None: print(f" ⚠️ Sin datos para {nro_oper} / {ticker}") continue base_mtm_dict[nro_oper] = res['base_mtm'] base_price_dict[nro_oper] = res['base_price'] # Reconstituir df_res para hoja individual (a partir de los P&L centrados en base) idx_dates = res['pnl_all'].index # Matrices por factor for df_g, key in [(df_resumen_mtm, 'pnl_all'), (df_mtm_price, 'pnl_price'), (df_mtm_tasa, 'pnl_tasa'), (df_mtm_volat, 'pnl_volat')]: t_ser = res[key].rename(nro_oper).to_frame() if df_g is df_resumen_mtm: df_resumen_mtm = t_ser if df_resumen_mtm.empty else df_resumen_mtm.join(t_ser, how='outer') elif df_g is df_mtm_price: df_mtm_price = t_ser if df_mtm_price.empty else df_mtm_price.join(t_ser, how='outer') elif df_g is df_mtm_tasa: df_mtm_tasa = t_ser if df_mtm_tasa.empty else df_mtm_tasa.join(t_ser, how='outer') elif df_g is df_mtm_volat: df_mtm_volat = t_ser if df_mtm_volat.empty else df_mtm_volat.join(t_ser, how='outer') print(f" ⏱ {nro_oper} completada en {time.time()-t_op:.1f}s") # ── VENTANA 253 DÍAS ───────────────────────────────────────────── t_fase2 = time.time() print("πŸ“Š Generando hojas de resumen...") operaciones_individuales = list(df_resumen_mtm.columns) N_DIAS_VAR = 253 df_resumen_mtm = df_resumen_mtm[~df_resumen_mtm.index.duplicated(keep='last')] df_resumen_mtm = df_resumen_mtm.sort_index().fillna(0).tail(N_DIAS_VAR) fechas_ventana = df_resumen_mtm.index df_mtm_price = df_mtm_price[~df_mtm_price.index.duplicated(keep='last')].reindex(fechas_ventana).fillna(0) df_mtm_tasa = df_mtm_tasa[~df_mtm_tasa.index.duplicated(keep='last')].reindex(fechas_ventana).fillna(0) df_mtm_volat = df_mtm_volat[~df_mtm_volat.index.duplicated(keep='last')].reindex(fechas_ventana).fillna(0) print(f" β†’ Ventana: {len(df_resumen_mtm)} escenarios ({fechas_ventana.min()} a {fechas_ventana.max()})") df_resumen_mtm['TOTAL_P&L'] = df_resumen_mtm[operaciones_individuales].sum(axis=1) pnl_total_series = df_resumen_mtm['TOTAL_P&L'] var_div_95 = np.percentile(pnl_total_series.dropna(), 5) var_div_99 = np.percentile(pnl_total_series.dropna(), 1) var_div_05 = np.percentile(pnl_total_series.dropna(), 95) var_total_pnl = pnl_total_series.var() base_total = sum(base_mtm_dict.values()) # ── Euler normalizado ──────────────────────────────────────────── commodities = list(set(oper_to_commodity.values())) beta_f_crudos = {} suma_betas_crudos = 0.0 for comm in commodities: ops_c = [op for op, c in oper_to_commodity.items() if c == comm and op in operaciones_individuales] beta_f_crudos[comm] = {} for f_name, df_f in [('Price', df_mtm_price), ('Tasa', df_mtm_tasa), ('Volat', df_mtm_volat)]: pnl_f_comm = df_f[ops_c].sum(axis=1).values beta = np.cov(pnl_f_comm, pnl_total_series.values)[0, 1] / var_total_pnl if var_total_pnl != 0 else 0.0 beta_f_crudos[comm][f_name] = beta suma_betas_crudos += beta factor_ajuste = 1.0 / suma_betas_crudos if suma_betas_crudos != 0 else 1.0 euler_results = [] for comm in commodities: ops_c = [op for op, c in oper_to_commodity.items() if c == comm and op in operaciones_individuales] pnl_comm = df_resumen_mtm[ops_c].sum(axis=1) row_euler = {'Commodity': comm} total_beta_c = 0.0 for f_name in ['Price', 'Tasa', 'Volat']: beta_ajustado = beta_f_crudos[comm][f_name] * factor_ajuste total_beta_c += beta_ajustado row_euler[f'Component_VaR_{f_name}'] = round(beta_ajustado * var_div_95, 2) row_euler['Total_Component_VaR'] = round(total_beta_c * var_div_95, 2) euler_results.append(row_euler) df_aporte_commodity = pd.DataFrame(euler_results) df_aporte_commodity.sort_values('Total_Component_VaR', ascending=True, inplace=True) total_row = pd.DataFrame([{ 'Commodity': 'TOTAL PORTAFOLIO', 'Component_VaR_Price': df_aporte_commodity['Component_VaR_Price'].sum(), 'Component_VaR_Tasa': df_aporte_commodity['Component_VaR_Tasa'].sum(), 'Component_VaR_Volat': df_aporte_commodity['Component_VaR_Volat'].sum(), 'Total_Component_VaR': df_aporte_commodity['Total_Component_VaR'].sum(), }]) df_aporte_commodity = pd.concat([df_aporte_commodity, total_row], ignore_index=True) # ── VaR individual ─────────────────────────────────────────────── var_95_dict = {} var_results = [] sum_var_95 = 0.0 sum_var_99 = 0.0 for operacion in operaciones_individuales: pnl_s = df_resumen_mtm[operacion] v95 = np.percentile(pnl_s.dropna(), 5) v99 = np.percentile(pnl_s.dropna(), 1) v05 = np.percentile(pnl_s.dropna(), 95) var_95_dict[operacion] = v95 sum_var_95 += v95 sum_var_99 += v99 var_results.append({'Operacion': operacion, 'MTM_BASE': base_mtm_dict[operacion], 'VaR_95%': v95, 'VaR_99%': v99, 'VaR_05%': v05}) var_results.append({'Operacion': 'TOTAL PORTAFOLIO', 'MTM_BASE': base_total, 'VaR_95%': var_div_95, 'VaR_99%': var_div_99, 'VaR_05%': var_div_05}) var_results.append({'Operacion': 'Suma VaR Individual', 'MTM_BASE': base_total, 'VaR_95%': sum_var_95, 'VaR_99%': sum_var_99, 'VaR_05%': np.nan}) var_results.append({'Operacion': 'Efecto_Diversificacion', 'MTM_BASE': 0.0, 'VaR_95%': sum_var_95 - var_div_95, 'VaR_99%': sum_var_99 - var_div_99, 'VaR_05%': np.nan}) df_var_resumen = pd.DataFrame(var_results) # ── Detalle portafolio (blotter) ───────────────────────────────── dict_factores = {'CPO': 25, 'SM': 100, 'S': 5000, 'SBO': 60000, 'W': 5000, 'KW': 5000, 'MW': 5000, 'C': 5000, 'BLW': 5000, 'CL': 1000} resumen_portafolio = [] for _, row in df_port.iterrows(): nro_oper = str(row['NroOper']) if nro_oper not in var_95_dict: continue sub = str(row['TypeVolUnder']).strip() instr = str(row['DerivativeType']).strip().capitalize() posicion = "Long" if row['LongShortType'] == 1 else "Short" factor = dict_factores.get(sub, 1) volumen = row['CAKS'] * factor strike = row['StrikePrice'] asset = base_price_dict[nro_oper] notional = volumen * strike * row['LongShortType'] mtm_act = base_mtm_dict[nro_oper] v95_op = var_95_dict[nro_oper] dt_lower = instr.lower() if dt_lower == 'call': mny = 'ITM' if asset > strike else ('OTM' if asset < strike else 'ATM') elif dt_lower == 'put': mny = 'ITM' if asset < strike else ('OTM' if asset > strike else 'ATM') else: mny = 'N/A' resumen_portafolio.append({ 'Fecha': pd.to_datetime(row['DateValuation']).date(), 'ID': nro_oper, 'Instrumento': instr, 'Subyacente': sub, 'Contrato': row['Ticker'], 'Posicion': posicion, 'Volumen (tm)': volumen, 'Precio Strike (usd/tm)': strike, 'Precio Actual (usd/tm)': asset, 'Notional (usd)': notional, 'MTM Actual (usd)': mtm_act, 'VaR 95%': v95_op, 'MTM + VaR': mtm_act + v95_op, 'Moneyness': mny, 'Status P&L': 'Ganancia' if mtm_act >= 0 else 'PΓ©rdida', }) df_resumen_portafolio = pd.DataFrame(resumen_portafolio) nominal_neto = df_resumen_portafolio['Notional (usd)'].sum() pct_var_nom = (abs(var_div_95) / abs(nominal_neto) * 100) if nominal_neto != 0 else 0.0 tot_row = { 'Fecha': 'TOTAL', 'ID': '-', 'Instrumento': '-', 'Subyacente': '-', 'Contrato': '-', 'Posicion': '-', 'Volumen (tm)': df_resumen_portafolio['Volumen (tm)'].sum(), 'Precio Strike (usd/tm)': '-', 'Precio Actual (usd/tm)': '-', 'Notional (usd)': nominal_neto, 'MTM Actual (usd)': df_resumen_portafolio['MTM Actual (usd)'].sum(), 'VaR 95%': df_resumen_portafolio['VaR 95%'].sum(), 'MTM + VaR': df_resumen_portafolio['MTM + VaR'].sum(), 'Moneyness': '-', 'Status P&L': '-', } df_resumen_portafolio = pd.concat([df_resumen_portafolio, pd.DataFrame([tot_row])], ignore_index=True) # ── KPIs generales ─────────────────────────────────────────────── df_kpis = pd.DataFrame([{ 'MTM TOTAL (P&L)': base_total, 'VAR 95% PORTAFOLIO': var_div_95, 'VAR 99% PORTAFOLIO': var_div_99, 'VAR 5% PORTAFOLIO': var_div_05, '% VAR 95 / NOMINAL': pct_var_nom, 'SUMA VAR INDIVIDUAL': sum_var_95, 'EFECTO DIVERSIFICACION': sum_var_95 - var_div_95, 'N_ESCENARIOS': N_DIAS_VAR, }]) # ── Correlaciones para dashboard IA ───────────────────────────── df_pnl_comm_temp = pd.DataFrame() for comm in commodities: ops_c = [op for op, c in oper_to_commodity.items() if c == comm and op in operaciones_individuales] if ops_c: df_pnl_comm_temp[comm] = df_resumen_mtm[ops_c].sum(axis=1) df_pnl_comm_temp['TOTAL'] = pnl_total_series corr_matrix = df_pnl_comm_temp.dropna().corr().round(4) sigma_total = pnl_total_series.dropna().std() corr_summary_rows = [] for comm in commodities: if comm not in df_pnl_comm_temp.columns: continue sigma_comm = df_pnl_comm_temp[comm].dropna().std() rho = df_pnl_comm_temp[comm].corr(pnl_total_series) vol_total_comm = df_resumen_portafolio[df_resumen_portafolio['Subyacente'] == comm]['Volumen (tm)'].sum() corr_summary_rows.append({ 'Commodity': comm, 'Οƒ_diaria_P&L (USD)': round(sigma_comm, 2), 'Οƒ_precio_diario (USD/tm)': round(sigma_comm / (vol_total_comm or 1), 6), 'ρ_con_Portafolio': round(rho, 4), 'ρ_long_vs_Portafolio': round(-rho, 4), 'InterpretaciΓ³n': 'Long reduce riesgo' if rho > 0 else 'Long aumenta riesgo', }) df_corr_summary = pd.DataFrame(corr_summary_rows) df_nota = pd.DataFrame([{ 'NOTA': 'ρ calculada sobre series histΓ³ricas de P&L (253 escenarios). ' 'Para posiciΓ³n LARGA adicional usar ρ_long_vs_Portafolio. ' 'Para posiciΓ³n CORTA adicional usar ρ_con_Portafolio.' }]) # ── Escritura a Excel ──────────────────────────────────────────── df_kpis.to_excel(writer, sheet_name='KPIs_Generales', index=False) df_resumen_portafolio.to_excel(writer, sheet_name='Detalle_Portafolio', index=False) df_var_resumen.to_excel(writer, sheet_name='Resumen_VaR', index=False) df_aporte_commodity.to_excel(writer, sheet_name='Aporte_Commodity', index=False) df_resumen_mtm_out = df_resumen_mtm.copy() df_resumen_mtm_out.index.name = 'Fecha' df_resumen_mtm_out.reset_index().sort_values('Fecha', ascending=False).to_excel( writer, sheet_name='Resumen_PnL', index=False) corr_matrix.to_excel(writer, sheet_name='Correlaciones', startrow=0) df_corr_summary.to_excel(writer, sheet_name='Correlaciones', startrow=len(corr_matrix) + 3, index=False) df_nota.to_excel(writer, sheet_name='Correlaciones', startrow=len(corr_matrix) + len(df_corr_summary) + 6, index=False) t_total = time.time() - t_inicio t_f1 = t_fase2 - t_inicio t_f2 = t_total - t_f1 print(f"\nβœ… Archivo guardado en: {output_path}") print(f"\n⏱ TIEMPOS:") print(f" Fase 1 β€” Escenarios por operaciΓ³n: {t_f1:.1f}s ({t_f1/60:.1f} min)") print(f" Fase 2 β€” VaR / Aportes / Resumen: {t_f2:.1f}s") print(f" TOTAL: {t_total:.1f}s ({t_total/60:.1f} min)") return output_path if __name__ == "__main__": generar_datos_var()
import pandas as pd
import numpy as np
from scipy.stats import norm
from scipy.interpolate import interp1d, CubicSpline
from scipy.optimize import brentq, fsolve
import warnings
import time
import os as _os
# =========================================================================
# 1. MOTOR DE VOLATILIDAD SKEW Y PRICING
# =========================================================================
def blsdelta(price_asset, strike, rate, time_in_year, volatility):
if time_in_year <= 0: return 1.0 if price_asset > strike else 0.0
d1 = (np.log(price_asset / strike) + (rate + 0.5 * volatility**2) * time_in_year) / (volatility * np.sqrt(time_in_year))
return norm.cdf(d1)
def optimize_delta(x, D, price_asset, rate, time_in_year, volatility):
delta = blsdelta(price_asset, abs(x), rate, time_in_year, volatility)
return 100000 * (D - delta)
def get_volatility_by_skew(df_filtered, derivative_type, days_to_expirity, price_asset, price_strike, rate):
if df_filtered.empty or str(derivative_type).strip().lower() not in ['call', 'put']: return np.nan
H = df_filtered['Plazo'].values
deltas_cols = ['X10DCall', 'X20DCall', 'X30DCall', 'X40DCall', 'X45DCall', 'ATM',
'X45DPut', 'X40DPut', 'X30DPut', 'X20DPut', 'X10DPut']
A = df_filtered[deltas_cols].values
interpolator_time = interp1d(H, A, axis=0, kind='linear', fill_value='extrapolate')
V = interpolator_time(days_to_expirity)
if V.ndim > 1: V = V[0]
D = np.array([10, 20, 30, 40, 45, 50, 55, 60, 70, 80, 90])
V1, V99 = 2 * V[0] - V[1], 2 * V[-1] - V[-2]
Dmin, Dmax = 0.5 * (1 + D[0]), 0.5 * (D[-1] + 99)
m = 0.5 * ((V[0] - V1) / (D[0] - 1) + (V[1] - V[0]) / (D[1] - D[0]))
n = 0.5 * ((V[-1] - V99) / (D[-1] - 99) + (V[-2] - V[-1]) / (D[-2] - D[-1]))
Vmin, Vmax = V[0] - (m * (D[0] - Dmin)), V[-1] - (n * (D[-1] - Dmax))
D_ext = np.array([1, Dmin] + D.tolist() + [Dmax, 99]) / 100.0
V_ext = np.array([V1, Vmin] + V.tolist() + [Vmax, V99])
time_in_year = days_to_expirity / 365.0
RowK = []
with warnings.catch_warnings():
warnings.simplefilter("ignore")
for nCol in range(len(V_ext)):
func = lambda x: optimize_delta(x, D_ext[nCol], price_asset, rate, time_in_year, V_ext[nCol])
low_k, high_k = price_asset * 0.0001, price_asset * 50.0
try:
x_sol = brentq(func, low_k, high_k, maxiter=1000)
RowK.append(abs(x_sol))
except:
try:
x_sol = fsolve(func, price_strike)[0]
RowK.append(abs(x_sol))
except:
RowK.append(np.nan)
Ki, Vi = np.array(RowK), V_ext.copy()
valid_idx = ~np.isnan(Ki)
Ki_clean, Vi_clean = Ki[valid_idx], Vi[valid_idx]
Ki_unique, unique_indices = np.unique(Ki_clean, return_index=True)
Vi_unique = Vi_clean[unique_indices]
if len(Ki_unique) < 2: return np.nan
if price_strike < Ki_unique[0]:
Ki_to_use, Vi_to_use = Ki_unique[0:2], Vi_unique[0:2]
elif price_strike > Ki_unique[-1]:
Ki_to_use, Vi_to_use = Ki_unique[-2:], Vi_unique[-2:]
else:
Ki_to_use, Vi_to_use = Ki_unique, Vi_unique
if len(Ki_to_use) == 2:
m_slope = (Vi_to_use[1] - Vi_to_use[0]) / (Ki_to_use[1] - Ki_to_use[0])
vol_final = Vi_to_use[0] + m_slope * (price_strike - Ki_to_use[0])
else:
spline_interpolator = CubicSpline(Ki_to_use, Vi_to_use, extrapolate=True)
vol_final = spline_interpolator(price_strike)
return float(vol_final)
# =========================================================================
# 2. MOTORES DE VALORIZACIΓ“N MTM
# =========================================================================
def value_futures(df):
"""ValorizaciΓ³n de futuros: P&L = (precio_actual - strike) * long_short"""
df = df.copy()
df["Price"] = (df["AssetPrice"] - df["StrikePrice"]) * df["LongShortType"]
df["Delta"] = 1 * df["LongShortType"]
for col in ["Gamma", "Vega", "Rho", "Theta", "Lambda"]: df[col] = 0
df["DF"] = 1
return df
def value_swaps(df):
"""ValorizaciΓ³n de swaps con descuento por factor DF."""
df = df.copy()
T = df["T"]
rate = df["Rate"]
df["DF"] = 1 / ((1 + rate) ** T)
df["Price"] = (df["AssetPrice"] - df["StrikePrice"]) * df["DF"] * df["LongShortType"]
for col in ["Gamma", "Vega", "Rho", "Theta", "Lambda"]: df[col] = 0
df["Delta"] = df["DF"] * df["LongShortType"]
return df