MODEL BUILDER 7.1: VaR with Splits and Dividend Payments

1. Create a new workbook and name it MB_7.1_User. While we have provided versions of each Model Builder on the website, we believe that to understand the material it is best for readers to use these as guides off of which they will build their own models. For the purposes of this and other Model Builders, we have used proxy data that can be copied from the files on the website.
2. In B4, put the most recent date for which there is a stock price available. In C4, enter the price for that date—in the complete version of the model we use 12/8/2010. Then enter the historical prices and dates below this for the entire period from which data is being used.
3. In F4, enter the date of the most recent stock split, reverse split, or dividend. Enter dates of previous splits or dividends that occurred during the period in question. In G4 (and below this for subsequent splits or dividends), enter the amount of the dividend for each share. We have to remember that dividend data usually does not account for splits, so we will build this into our adjustments.
4. In H4 and I4, we will enter any information about stock splits. In column H we will list the shares before the split, and in column I we have the number of shares after the split. For a 3:1 split, in which the number of shares triple, we would enter 1 in column H and 3 in column I. For simple dividends without a split, enter 1 in both columns.
5. Once the required data is entered, we will start on the VBA code that will correct our data. First, we will define variables and collect data from the worksheet.

   Sub correctPrices()
       Dim stockPrices(), dividendsSplits(), cumDividendsSplits()
       As Double
       Dim i, j, numDays, numDiv As Integer
   
    'collect raw stock info
       Range(“a9780470931226b4”).Select
       Range(Selection, Selection.End(xlDown)).Select
       numDays = Selection.Rows.Count
       ReDim stockPrices(1 To numDays, 2)
       For i = 1 To numDays
          stockPrices(i, 1) = Range(“a9780470931226b3”).Offset(i, 0)
          stockPrices(i, 2) = Range(“a9780470931226b3”).Offset(i, 1)
       Next i
   
       'collect raw dividend and split info
       Range(“F4”).Select
       Range(Selection, Selection.End(xlDown)).Select
       numDiv = Selection.Rows.Count
       ReDim dividendsSplits(1 To numDiv, 3)
   
       For i = 1 To numDiv
          dividendsSplits(i, 1) = Range(“F3”).Offset(i, 0)
          dividendsSplits(i, 2) = Range(“F3”).Offset(i, 1)
          dividendsSplits(i, 3) = Range(“F3”).Offset(i, 2) / Range(“F3”)
          .Offset(i, 3)
       Next i

   We are creating two arrays, stockPrices() and dividendsSplits(), which will contain all of the information from the sheet. We use the “Range(Selection, Selection.End(xlDown)).Select” method to select the full set of data, no matter how many days of price data or dividend payments/splits there are.

6. Next we will begin the calculations. The key calculation for us is to track the cumulative impact of dividends and splits, which we will calculate in the cumDividendsSplits() array. Remember that in a stock split, we do not need to correct the stock price just on the day before the split, but we also need to adjust the previous stock prices all the way back to the beginning of the period for which we are using data.

   ReDim cumDividendsSplits(0 To numDays, 2)
   j = 1
   cumDividendsSplits(0, 1) = 0
   cumDividendsSplits(0, 2) = 1
   For i = 1 To numDays
    If stockPrices(i, 1) = dividendsSplits(j, 1) Then
           cumDividendsSplits(i, 2) = cumDividendsSplits(i-1, 2)

   * dividendsSplits(j, 3)
   cumDividendsSplits(i, 1) = cumDividendsSplits(i-1, 1)
   + dividendsSplits(j, 2) * cumDividendsSplits(i, 2)
   j = j + 1
    Else
           cumDividendsSplits(i, 2) = cumDividendsSplits(i-1, 2)
           cumDividendsSplits(i, 1) = cumDividendsSplits(i-1, 1)
    End If
   Next i

   In this segment of code, we are tracking dividends in column 1 of the array, and splits in column 2. We expand the array to “0 to numDays” (most of our arrays start at 1, current day, and track back as we move down the array) because we want to be able to account for splits or dividends that may have happened today. We then go backwards through the days, and when a date matches the date of a split or dividend (which is tracked in dividendsSplits() column 1, we enter an adjustment. For a split, we take the previous factor and multiply by the inverse of the split. A 3:1 split reduces the factor of the stock before the split to one-third. This is because if we hold one stock today, it is the equivalent to holding one-third of a share before the split.

   For the dividends, we sum together the paid amounts on the ex-dividends dates, but we again must adjust for the splits. We multiply the amount of the dividend by the cumulative split factor.

7. Finally, we use the cumDividendsSplits() array to adjust the stockPrices() array and give us corrected prices for a VaR implementation. For each date, we will multiply the raw price of the stock by the cumulative split factor and then subtract dividends that are paid between that date and now. See Figure 7.1.

   'adjust prices for splits and dividends
       For i = 1 To numDays
     stockPrices(i, 2) = (stockPrices(i, 2) * cumDividendsSplits
    (i, 2))-cumDividendsSplits(i, 1)
       Next i
   
       'print
    Range(“K4”).Select Selection.Resize(numDays, 3).Select
       Selection = stockPrices
   
   End Sub

MODEL BUILDER 7.2: Implementing Pairwise Deletion for Illiquid Securities

1. Create a new workbook and name it MB_7.2_User.xls. We will be using this sheet for the next two Model Builders, so we recommend that you go through this Model Builder patiently and refer to the complete version on the website if there is any confusion.
2. We will be doing analysis on one liquid security (a security for which we have pricing for every day in the period in question) and one illiquid security (a security for which there are gaps in the pricing). Ideally, we will want to use securities that are as similar as possible (with a high correlation). The proxy data we are using is included in the complete version on the website.

   In B4 enter the most recent date for the liquid security, and in C4 enter the price for that day. In our proxy data, the most recent date is 12/8/2010, and the most recent price for the liquid security is 5.18. Enter the dates and prices in descending order below this. In F4 enter the most recent valuation date for the illiquid security, and in G4 enter the price. In our example, the most recent date is 12/8/2010 and the most recent price is 22.90. Again, enter dates and prices for this security in descending order. Since the illiquid security does not trade every day, there are going to be gaps between dates.

3. We will generate some summary statistics so we can see how complete or deficient our illiquid data set is. In J4, to see how many prices we have for the liquid security, enter “=COUNT(C4:C3000)” (this will work for securities with fewer than 3,000 price entries, of course). In J5, do the same for the illiquid security, by entering “=COUNT(G4:G3000)”. And in J6, we will calculate the percentage of dates where the illiquid security has data by entering “=J5/J4”. If this value in J6 is high enough for comfort (we will use 75 percent as an arbitrary threshold), then we proceed to creating a set of pairwise deleted prices.
4. To begin, we will write code to gather the data on this worksheet and define a number of arrays where we will complete calculations. In illiquidPrices() we will put all pricing data on the dates where we have prices for the illiquid security. In illiquidReturns() and fullReturns() we will put return information for the two securities so we can calculate the correlation between them.

   Sub estCorrelation()
   
       Dim fullPrices(), illiquidPrices(), fullReturns(),
       illiquidReturns()
       As Double
       Dim i, j, numfullpricedays, numIlliquidDays As Integer
   
       'collect full price info
       Range(“a9780470931226b4”).Select
       Range(Selection, Selection.End(xlDown)).Select
       numfullpricedays = Selection.Rows.Count
       ReDim fullPrices(1 To numfullpricedays, 2)
   
       For i = 1 To numfullpricedays
           fullPrices(i, 1) = Range(“a9780470931226b3”).Offset(i, 0)
           fullPrices(i, 2) = Range(“a9780470931226b3”).Offset(i, 1)
       Next i
   
       'collect illiquid price info
       Range(“F4”).Select
       Range(Selection, Selection.End(xlDown)).Select
       numIlliquidDays = Selection.Rows.Count
       ReDim illiquidPrices(1 To numIlliquidDays, 5)
       ReDim fullReturns(1 To numIlliquidDays-1, 0)
       ReDim illiquidReturns(1 To numIlliquidDays-1, 0)
   
       For i = 1 To numIlliquidDays
           illiquidPrices(i, 1) = Range(“F3”).Offset(i, 0)
           illiquidPrices(i, 2) = Range(“F3”).Offset(i, 1)
       Next i

5. Now we will go through the pricing information for the liquid security and pull out the dates that have both prices for it and the illiquid security. We will do this using a For loop that goes through all liquid security trade dates (I = 1 to numfullpricedays) and compares the dates to the illiquid price days. We use the counter “j” for the illiquid price days; once we find a date that both securities were priced, we move to the next date that the illiquid security was priced (j = j + 1).

   'Only take days where there are prices for both securities
       j = 1
       For i = 1 To numfullpricedays
           If fullPrices(i, 1) = illiquidPrices(j, 1) Then
               illiquidPrices(j, 3) = fullPrices(i, 2)
               If j <> nullliquidDays then
                        j = j + 1
               End If
           End If

6. In order to get the correlation in price movements, we will enter the returns between periods by calculating returns in two arrays. This is a little bit unusual because there are not consistent time intervals between the data points. Fortunately, correlation does not require a time component (as opposed to volatility, which we will get to shortly)

   'Calculate changes in price
       For i = 1 To numIlliquidDays-1
           illiquidReturns(i, 0) = (illiquidPrices(i, 2)-illiquidPrices
           (i + 1, 2)) / illiquidPrices(i + 1, 2)
           fullReturns(i, 0) = (illiquidPrices(i, 3)-illiquidPrices(i +
           1, 3)) / illiquidPrices(i + 1, 3)
       Next i

7. In order to finish this macro, we will print out the prices that remain after the pairwise deletion, as well as the correlation and estimated volatilities (standard deviations of return) that we have found in the data. Since the intervals are not constant and the different interval lengths are not accounted for, these estimates are not true volatility numbers. However, they do offer us some information, and we will use this information to help us if we are going to try more aggressive strategies to work with our deficient data, so we will produce these numbers anyways, being mindful of their limitations.

   'print
       Range(“J11”).Value =
   Application.WorksheetFunction.Correl(illiquidReturns, fullReturns)
       Range(“J12”).Value =
       Application.WorksheetFunction.StDev(fullReturns)
       Range(“J13”).Value =
       Application.WorksheetFunction.StDev(illiquidReturns)
       Range(“L4”).Select
       Selection.Resize(numIlliquidDays, 4).Select
       Selection = illiquidPrices

While the previously mentioned process is useful, we are eliminating a lot of very useful data, which is why many modelers use more aggressive data techniques. Imputation is the practice of inserting additional data to complete a data set. It is used in the social sciences via a number of different methods, and not without controversy! When there is relative confidence that the data available is complete (a data completion standard might be that there is price data for 75 percent of the dates in question, or a minimum of 500 holders of the security, or some other measure of liquidity or information), then we will impute data to allow us to use our models on securities with incomplete datasets.

In finance, there are two general methods by which data is imputed: using an expectation-maximization algorithm, and using a partial proxy. We will cover a partial proxy methodology here. This is a practitioner's attempt to make do with an incomplete dataset without running through a processing intensive procedure. We will build off of the estCorrelation() subroutine that we were working with previously.

1. Continuing with the sheet that we used for the estCorrelation() subroutine, we will enter the Cholesky() function that we had developed in Chapter 3. We will use this to attempt to keep the correlation in asset movements similar to what we have experienced previously (or we can stress a portfolio by increasing the correlation between the assets). Our correlation will not be precisely the same as what we are projecting since we will respect the actual data points that we have for the illiquid security, but the correlation should be close.

   FIGURE 7.3 Model Builder 7.2: Boxes on the left indicate dates when both securities are priced. These dates are included in the final dataset on the right.

   

2. We will need to add a few values and change some names, but in general the section of the code where we take values from the worksheet will look similar.

   Sub imputeValues()
   
       Dim allPrices(), allReturns(), trueVol(), estLiqVol, trueLiqVol,
       estIlliqVol, registry(), cholesky1, cholesky2 As Double
       Dim i, j, numfullpricedays, numIlliquidDays As Integer
   
       'collect full price info
       Range(“a9780470931226b4”).Select
       Range(Selection, Selection.End(xlDown)).Select
       numfullpricedays = Selection.Rows.Count
       ReDim allPrices(1 To numfullpricedays, 3)
       ReDim registry(1 To numfullpricedays, 3)
       ReDim allReturns(1 To numfullpricedays-1, 3)
       ReDim trueVol(1 To numfullpricedays-1, 1)
   
       For i = 1 To numfullpricedays
           allPrices(i, 1) = Range(“a9780470931226b3”).Offset(i, 0)
           allPrices(i, 2) = Range(“a9780470931226b3”).Offset(i, 1)
       Next i
   
       'collect illiquid price info
       Range(“F4”).Select
       Range(Selection, Selection.End(xlDown)).Select
       numIlliquidDays = Selection.Rows.Count
       j = 1
       For i = 1 To numfullpricedays
           If Range(“F3”).Offset(j, 0) = allPrices(i, 1) Then
               allPrices(i, 3) = Range(“F3”).Offset(j, 1)
               registry(i, 1) = i
               j = j + 1
           Else

   allPrices(i, 3) = 0
               registry(i, 1) = registry(i-1, 1)
           End If
       Next i

   One important array that is new that we need to track is the registry() array. In this macro, when we are collecting price information in the allPrices() array, we are not deleting liquid security pricing. Instead, we are taking all of the liquid security prices and matching them up with illiquid security prices. Where we do not have illiquid security prices, we are leaving a blank space (specifically, the lines “Else allPrices(I,3) = 0”). When we do not have a price for the illiquid security, we want to track what period the next price will be in. We are tracking this in the registry() array in column 1 (remember, since we are going back in time as we go down the array, period i – 1 is the period after period i.

3. Next, we are going to go “backward” and look at the allPrices() array from bottom to top to find the previous price for each period when the illiquid security does not have price information. When the security does have info, we will enter the current period into column 2 of the registry() array.

   'Create Returns for Illiquid Security
       'Create registry of 'previous price points'
       For i = 2 To numfullpricedays
           If registry(numfullpricedays + 1-i, 1) =
           numfullpricedays + 1-i Then
                registry(numfullpricedays + 1-i, 2) =
                registry(numfullpricedays + 1-i, 1)
           Else
                registry(numfullpricedays + 1-i, 2) =
                registry(numfullpricedays + 2-i, 2)
           End If
       Next i

4. Next, we are going to use the information in the registry() array and the price points we already know to create average values for the dates when we don't have prices for the illiquid security.

   'create mean prices for missing dates
       For i = 1 To numfullpricedays
           If registry(i, 1) = i Then
               registry(i, 3) = allPrices(i, 3)
           Else
               If registry(i, 2) = 0 Then
                   registry(i, 3) = registry(i-1, 3)
               Else
                   registry(i, 3) = allPrices(registry(i, 1), 3) +
                   ((i-registry(i, 1)) / (registry(i, 2)-registry
                   (i, 1)) * (allPrices(registry(i, 2), 3)-allPrices
                   (registry(i, 1), 3)))
               End If
           End If
       Next i

   The key line in this section of code is repeated here for further analysis:

   registry(i, 3) = allPrices(registry(i, 1), 3) + ((i-registry(i, 1))
   / (registry(i, 2)-registry(i, 1)) * (allPrices(registry(i, 2), 3)-
   allPrices(registry(i, 1), 3)))

   What we are doing, for dates without a price, is creating a price based on the previous and next dates for which we have illiquid security prices. “Registry(i, 1)” is the next date when a price is available, and the value of that price is obtained by searching for that period in the allPrices() array via the command “allPrices(registry(i, 1), 3)”. Then we will fractionally increase this price so it is linearly moving toward the price at the previous value we have in our dataset, a process known as linear interpolation. So if we have a price in period 6, and the next price is in period 2, we determine the prices for periods 3, 4, and 5 through equations 7.1, 7.2, and 7.3, respectively, which are represented in this code.

   

5. Now we have created a price for each period, but the values between each true price point are not realistic. They are just linear interpolations of the nearest prices. This means that the price movements will not necessarily have the appropriate correlation with our liquid security, and the price movements will certainly not have a realistic volatility! Remember that when we project prices, we generally do so by using a drift term, μ (mu) and a volatility term σ:

   

   In order to use this formula, we must find the volatility of the illiquid security. As discussed previously, analysts usually do this in two ways: by taking the historical volatility of the stock, or by using option prices to estimate future volatility. Unfortunately, we do not have daily historical volatility (if the stock traded daily, we would have no need for this exercise in the first place), and it is likely that there will not be a liquid options market for an illiquid stock. Instead, we will use the limited historical volatility that we determined using the dataset that incorporated pairwise deletion, and then we will take the relationship between the liquid stock's estimated volatility and its true volatility, and use that to calculate a daily volatility for the illiquid stock.

   As we do this, we will also create and decompose the correlation matrix for the two securities. Since there are only two securities, we will only have to create a 2 × 2 matrix in I22:J23. In I22 and J23, we enter the number 1, in the cells not on the diagonal (J22 and I23) we enter “=J11” to take the previously estimated correlation. Below this we will create the Cholesky decomposition of this matrix by selecting I26:J27, entering “=Cholesky(I22:J23)” and holding Control and Shift and pressing Enter.

   The resulting decomposed matrix in I26:J27 is what we would use to forecast the movements of correlated securities, as we did in Chapter 6. However, here we have a slightly different goal—we want to take the already observed movement of the liquid stock and use that to generate the missing prices for the illiquid security. To do this, we will take the second row of this matrix and make I27 the liquid security's factor. We will assign the variable name “cholesky1”. J27 will be the factor assigned to the illiquid security's uncorrelated draw, which we will call “cholesky2”.

   'Collect Vol and correlation info and Estimate Volatility
       For i = 1 To numfullpricedays-1
           allReturns(i, 1) = Range(“a9780470931226b3”).Offset(i, 0)
           allReturns(i, 2) = (allPrices(i, 2)-allPrices(i + 1, 2)) /
           allPrices(i + 1, 2)
           trueVol(i, 1) = (allPrices(i, 2)-allPrices(i + 1, 2)) /
           allPrices(i + 1, 2)
       Next i
   
       cholesky1 = Range(“I27”)
       cholesky2 = Range(“J27”)
       trueLiqVol = Application.WorksheetFunction.StDev(trueVol)
       estLiqVol = Range(“J12”).Value
       estIlliqVol = (Range(“J13”).Value * trueLiqVol) / estLiqVol

6. Now we are ready to create the missing prices for the illiquid security and impute them. Where we have an actual price from the dataset (we track this as registry(i, 1) = i, which means the next true price we have is the current one) we will use it. When we do not have a price, we will create one.

   'add in error
       For i = 1 To numfullpricedays-1
           If registry(i, 1) = i Then
               allPrices(i, 3) = registry(i, 3)
           Else
               allPrices(i, 3) = registry(i, 3) * (1 + estIlliqVol *
               (cholesky1 * Application.WorksheetFunction.NormDist
               (allReturns(i, 2), 0, trueLiqVol, True) + cholesky2 *
               Application.WorksheetFunction.NormSInv(Rnd())))
           End If
       Next i

   When we do not have a price, we will start with the mean price and then add on a correlated draw. However, we will not actually create a draw for the liquid security, because we already have a real return for each period. Instead, we use the volatility and the actual return, and we use that to figure out what draw would have resulted in that return. To do this we use the NormDist worksheet function, which is the inverse of the NormSInv function we used heavily in Chapter 6 for our Monte Carlo simulations.

7. Once the calculations are done, we print the outputs to complete the Model Builder.

   'print
       Range(“J18”).Value = trueLiqVol
       Range(“J19”).Value = estIlliqVol
       Range(“P4”).Select
       Selection.Resize(numfullpricedays, 4).Select
       Selection = allPrices
       End Sub