Executing an executable from VBA

For people in the software industries such as consultants, developers and even managers, they will very often need to handle different versions of software. Many a times, these versions are stored in different places. This means we will need to memorize these directories and every time we need to access them, we will need to navigate to these directories. The best way to overcome these is to tabulate the versions in Excel. But it is insufficient as it we are not able to start the software from the tables in excel natively.

By using VBA you can do this with the "Shell" command. The problem is with this is that you can only execute files that has no dependency on the folder you are executing it from. This means the environment paths and parameters needs to be setup correctly in Windows.

To overcome this limitation, I combined what I posted previously to build a table having the ability to execute a software from Excel. Instead of building your own DLL, what you need is to use the existing DLL (Refer to here for more details on how this is done in VBA):

Public Declare Function ShellExecute Lib "Shell32.dll" Alias "ShellExecuteA" _
  (ByVal hwnd As Long, _
   ByVal lpOperation As String, _
   ByVal lpFile As String, _
   ByVal lpParameters As String, _
   ByVal lpDirectory As String, _
   ByVal nShowCmd As Long) As Long

By declaring this you are effectively using the "ShellExecute" library from Shell32.dll to execute lpFile from lpDirectory.This will allow user to have more flexibility in building UI that will execute files upon clicking in VBA. An example of using the above is as follows:


RetVal = ShellExecute(0, "open", "C:\folder\client.cmd", "<arguments>", _
                        "C:\folder\", SW_SHOWMAXIMIZED)

SABR Model - Introduction

The famous option pricing Black-Scholes framework assumes that the asset price follows the stochastic process:

$dS_{t} = rd_t+{\sigma}dw_t$
where $w_t$ is a Brownian motion and $r$ is the risk free rate.

This simply means the dynamics of asset price is a random variable following a lognormal distribution with constant volatility of ${\sigma}$. This evaluates to the option pricing formula:

$C({\sigma}, r, S_t, K) = S_t{N(d_1)} - KN(d_2)$
where $d_1 = \frac{(ln(\frac{S_t}{K})+\frac{1}{2}(r+\sigma^2)t}{\sigma\sqrt{t}}$ and $d_2 = d_1-\sigma\sqrt{t}$

However, in practice, the option price implied by the market indicates that there is different volatility for different strikes. Furthermore, stock prices has distribution with fat tail and has higher peak relative to Normal distribution. This implies that there is a mixture in distributions with different variances. This is the motivation for developing volatility models such as Local Volatility, Heston Model, SABR Model and etc. In this post, the SABR model will be introduced briefly. The SABR model is a stochastic volatility model developed by Patrick Hagan et al. It has the following dynamics:

$dF_t = F_t^{\beta} {\sigma}_t dW_t$ , $0≤{\beta}≤1$
$d{\sigma}_t = v{\sigma}_t dB_t$ , $v>0$
$d{<}W,B{>}_t = {\rho}dt$ , ${\rho}ϵ[-1,1]$
${\sigma}_0 = {\alpha} >0$

$where$
${\beta}$ : Describes the forward rate model.
$v$ : Lognormal volatility of volatility
${\rho}$ : Correlation between Forward rate and Volatility
${\alpha}$ : ATM volatility

This dynamic is clearly different from the Black Scholes framework and the market is quoted in Black's volatility for instruments such as cap/floor, swaptions and etc. What Hagan et al. did was to use this dynamics to price a European option. In the process of this evaluation, it is found that with some estimation techniques, the pricing using SABR dynamics is close to the normal model. From the normal model, it is then further derived that the pricing using SABR will approximate to Black's lognormal model.

The formula for the implied Black's volatility is as follows:

${\sigma}_{implied} (K,f)=\frac{\alpha}{((fK)^(\frac{(1-{\beta})}{2}) (1+\frac{(1-{\beta})^2}{24} log^2(\frac{f}{K})+\frac{(1-{\beta})^4}{1920} log^4((f/K)+⋯ ) ) )} \frac{z}{X(z)})$
$(1+\frac{(1-{\beta})^2}{24} \frac{{\alpha}^2}{(fK)^(1-{\beta})} + \frac{1}{4} \frac{{\alpha}{\beta}v{\rho}}{(fK)^\frac{1-{\beta}}{2}} +\frac{2-3{\rho}^2 }{24} v^2 ) t_ex+⋯)$
$where$
$z=\frac{v}{{\alpha}} (fK)^(\frac{1-{\beta}}{2})log(\frac{f}{K})$
$X(z)=log(\frac{\sqrt{1-2{\rho}z+z^2 }+z-{\rho})}{(1-{\rho})})$
This series of proving shows that Black's volatility quoted in the market can be modeled with SABR model. All that needs to be done is to calibrate the SABR parameters such that the volatility matches market quotes.