Spaces:
Sleeping
Sleeping
Download codigo_v3_api.py from Patience1643/VaR_IA: direct link, hf CLI and curl.
- Browser
- Download file 55.6 kB
-
https://huggingface.co/spaces/Patience1643/VaR_IA/resolve/main/codigo_v3_api.py
- Command line
-
hf download hf://spaces/Patience1643/VaR_IA/codigo_v3_api.py
-
curl -L -o codigo_v3_api.py https://huggingface.co/spaces/Patience1643/VaR_IA/resolve/main/codigo_v3_api.py
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