I am converting an old VBA code to a python scribt, and I'm stuck. Does any one know how i can translate this VBA code to a python scipt?
I want to minimize an error term by changing the values in asset_val and asset_vol. The old VBA code that does this is (column 28 is the error value, and 20/21 is "initial Asset Value" / "initial Asset Volitility"):
For k = startobs To (endobs - 1) Step 1
SolverReset
SolverOK SetCell:=Worksheets("Select").Cells(k, 28).address, MaxMinVal:=2, ByChange:=Range(Cells(k, 20), Cells(k, 21)).address
SolverReset
SolverOK SetCell:=Worksheets("Select").Cells(k, 28).address, MaxMinVal:=2, ByChange:=Range(Cells(k, 20), Cells(k, 21)).address
SolverOptions precision:=0.00001, Iterations:=300, Scaling:=True, Convergence:=0.00001
The error term is computed as and given by the formula: enter image description here
def Error(S, K, T, r, sigma, E, log_ret, lamda):
Error = ((BnSCall(S, K, T, r, sigma) - E)**2 + (SigmaByEquity(S, K, T, r, sigma) - EstVolEWMA(log_ret, lamda)*E)**2)
return Error
data['Error'] = Error(S = data['asset_val'], K = data['debt interpolated'], T = 1, r = data['12m rate']/100, sigma = data['asset_vol'], E = data['av_m_cap'], log_ret = data['log_ret'], lamda = 0.94)
BnSCall is here the formula for computing value of equity (Black & Scholes' option pricing formula), and EstVolEWMA is a formula for calculating EWMA (exponentially weighted moving average) volitility.
Given by:
Calculate EWMA (exponentially weighted moving average) volitility
def EstVolEWMA(log_ret, lamda):
est_vol_ewma = abs(log_ret*0)
est_vol_ewma_prev = 0
for date in est_vol_ewma.index[50:]:
est_vol_ewma_t = (lamda*(est_vol_ewma_prev**2)+(1-lamda)*(log_ret[date]**2)*52)**0.5
est_vol_ewma[date] = est_vol_ewma_t;
est_vol_ewma_prev = est_vol_ewma_t
return est_vol_ewma
and:
def BnSCall(S,K,T,r,sigma):
return S*norm.cdf(BnSd1(S,K,T,r,sigma), 0, 1)-K*np.exp(-np.log(1+r)*T)*norm.cdf(BnSd2(S,K,T,r,sigma))
The initial asset value and volatility is calculated as.
Initial Asset Value
def InitAssetVal(debt, avv_mcap):
init_asset_val = debt + avv_mcap
return init_asset_val
data['asset_val'] = InitAssetVal(data['debt interpolated'], data['av_m_cap'])
Initial Asset volatility (equity volatility*0.5)
def InitAssetVol(equity_vol):
init_asset_vol = equity_vol*0.5
return init_asset_vol
data['asset_vol'] = InitAssetVol(data['est_vol_ewma'])