0

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'])

enter image description here

  • Please edit the question to limit it to a specific problem with enough detail to identify an adequate answer. – Community Oct 25 '21 at 11:00
  • Welcome to SO! Please provide a [minimal **reproducible** example](https://stackoverflow.com/help/minimal-reproducible-example), i.e. provide all data. Otherwise, it's hard to help. – joni Oct 27 '21 at 06:17

0 Answers0