Help with understanding and adjusting VBA code

[yem8123]

' I need to implement another calculation into this code but fail to 'understand exactly what part needs adjusting and how to write new code 'for the new calculation. Any?


Option Explicit 'Forces the need to declare every variable
Option Base 1 'Forces arrays to start at 1 instead of 0 (vba default)
Sub ValueAtRisk()
'Declarations. Notes dynamic arrays declared by simply adding () after the name
Dim returns() As Double, Covar() As Double, dSubReturns() As Double, vol As Double
Dim iDays As Long, iNames As Long, i As Long, j As Long, n As Long, iWindow As Long
Dim Exp As Double, T As Double, CI As Double
Dim stocks As Range, weights As Range, cel As Range
On Error GoTo ErrHandler 'On error skips to end of subroutine and does something sensible
Application.ScreenUpdating = False 'Massively speeds up big loops as not constantly update the screen
'Read in data from spreadsheet to the variables used here
Exp = Range("exp").Value 'Note a named range is used instead of Range("B7").Value
T = Range("t").Value
CI = Range("ci").Value
Set stocks = Range("stocks") 'Note the Set word is necessary to assign ranges
Set weights = Range("weights")
iWindow = Range("window").Value
'Note: add code here to check that input values are sensible (ie no negative exposures etc)
'Set array sizes using a count of number of days and names in portfolio (in the stocks named range)
iDays = stocks.Rows.Count
iNames = stocks.Columns.Count
ReDim returns(iDays, iNames) 'Re-dimension the dynamic array to the correct size
ReDim Covar(iNames, iNames) 'Redim the Covariance matrix to by NxN matrix
ReDim dSubReturns(iWindow, iNames) 'Also Redim the window array
'Loop through stocks range and calc the daily returns and place answer in returns array
'Note the generic bounds of the i and j loops means that it does not matter how many
'days of data or names in the stocks range, so 10 names over 20 years would also be handled.
'Just remember to change the stocks named range in the spreadsheet to include all the new data.
For i = LBound(returns, 1) + 1 To UBound(returns, 1)
For j = LBound(returns, 2) To UBound(returns, 2)
returns(i, j) = (stocks.Cells(i, j).Value - stocks.Cells(i - 1, j).Value) _
/ stocks.Cells(i - 1, j).Value
Next j
Next i
'The n loop starts at the end of the stocks range (today) and steps back one day at a time
'calculating the rolling iWindow (eg 250) day Value-at-Risk for all days in the data.
For n = iDays To (iWindow + 1) Step -1

'Find the cell to return the VaR to. This will return the var in the next column to the left.
Set cel = Range("stocks").Cells(n, iNames).Offset(0, 1)

'Fill an additional array with data from the part of returns() we are interested in.
'We do this because the user defined functions (UDF) below calculate the VaR over ALL the data they
'are passed, and if we passed them the returns matrix you would get a 10yr VaR instead of 250days
'hence we create a sub-array that contains only the data we are interested in.
For i = LBound(dSubReturns, 1) To UBound(dSubReturns, 1)
For j = LBound(dSubReturns, 2) To UBound(dSubReturns, 2)
dSubReturns(i, j) = returns(n - i + 1, j)
Next j
Next i
'Pass data to UDF
Covar = VCVMatrix(dSubReturns)
Range("vcv").Value = Covar 'write data to spreadsheet so we can see the vcv matrix
'Calc VaR and write the cell called cel
vol = Sqr(PortfolioVariance(weights, Range("vcv"))) 'the UDF takes two ranges
cel.Value = Exp * Application.NormSInv(1 - CI) * Sqr(T) * vol 'the standard calculation
Next n
'Tidy up
Application.ScreenUpdating = True
Exit Sub
ErrHandler:
MsgBox "Error in ValueAtRisk subroutine!"
Application.ScreenUpdating = True
End Sub
Function VCVMatrix(retsmat)
' Returns nxn sample Variance-Covariance Matrix
Dim i As Integer, j As Integer, k As Integer, nc As Integer, nr As Integer
Dim r1vec() As Variant, r2vec() As Variant, Vmat() As Double
'nc = retsmat.Columns.Count
nc = UBound(retsmat, 2) - LBound(retsmat, 2) + 1
ReDim Vmat(nc, nc)
'nr = retsmat.Rows.Count
nr = UBound(retsmat, 1) - LBound(retsmat, 1) + 1
ReDim r1vec(nr)
ReDim r2vec(nr)
For i = 1 To nc
For k = 1 To nr
r1vec(k) = retsmat(k, i)
Next k
Vmat(i, i) = Application.Var(r1vec)
For j = i + 1 To nc
For k = 1 To nr
r2vec(k) = retsmat(k, j)
Next k
Vmat(i, j) = Application.Covar(r1vec, r2vec) * (nr / (nr - 1))
Vmat(j, i) = Vmat(i, j)
Next j
Next i
VCVMatrix = Vmat
End Function

Function PortfolioVariance(wtsvec, vcvmat)
' Returns the portfolio variance
Dim v0 As Variant, v1 As Variant
If Application.Count(wtsvec) = vcvmat.Rows.Count Then
If wtsvec.Columns.Count > wtsvec.Rows.Count Then wtsvec = Application.Transpose(wtsvec)
v0 = Application.Transpose(wtsvec)
v1 = Application.MMult(v0, vcvmat)
PortfolioVariance = Application.SumProduct(v1, v0)
Else
PortfolioVariance = -1
End If
End Function

 

[xld]

Uh? Some explanation as to what it does and what you want to add might just be a tad pertinent here.

 

[yem8123]

The code uses historical share prices of several companies.
This code is used to generate a rolling VaR (value at risk - the potential amount of loss you could face over a certain time).

''A Rolling VaR calculates the characteristics of a portfolio's theoretical Value at Risk measured over an extended period of time (e.g. several years). To calculate a rolling VaR, the practitioner first needs to determine the portfolio returns' sample window size(e.g. 250 days). This is the period over which the portfolio volatility will be determined (if using the standard deviation measure of volatility). These 250 days of return history can, of course, be selected from an historical database of returns and may be chosen from several years in the past. Thus, if 250 days of portfolio returns are selected using data from five years ago - sorted from most distant to most recent - the standard deviation of these 250 portfolio returns represents the portfolio volatility over these 250 days as measured on the last (most recent) day of the sample window. In this particular case, the portfolio volatility measured from these data will be for the first day of the fourth year. The sample period is then 'rolled forward' by a single day and the process is repeated for all return data for the next four years. (by S Ali)''


This code uses a EW volatility techinique. I need to implement an EWMA technique where the lambda is changeable by the user.

This Method used to calculate the VaR is the Variance-covariance approach

 

[baitmaster]

Welcome to the board

however...

You need to pose this question first to a finance specialist, we can only help fix your code / calculations (unless someone here happens to specialise in financial risk management?)

Once you know what part of the formula needs changing, Im sure someone here can do it for you. However, I personally have no idea what you mean by EWMA, Lambda (other than its a Greek letter) etc. Please can you be more specific in your question