Question

Open up a clean Module and complete the coding of Module in VBA. Do the following tasks

1. Create a “main” sub, and assign the sub to the “Start” button on the worksheet.

2. Write the “clearOutput” sub. Also, assign this sub to the “Reset” button. In this sub, you will clear the content of range (B3:D5), (B9:B11), and (D14:D15) on the worksheet “RP”.

3. Write the “loadData” sub. The data is stored in the worksheet “HR”. Name the data range on the worksheet for T-bills, bond, and stock as “tbillHistory”, “bondHistory” and “stockHistory”. Then, you should use the historical data to calculate the average annual returns of each investment option (hint: use excel function Average). You also need to calculate the betas of each investment option. Note that Beta is a relative risk, so here we assume beta of stock is 1, therefore you will calculate the betas of T-bills and bond in relative to stock. The formula to calculate Beta (shown in the photo) Where r_a is return of T-bill or Bond, and r_b is return of stock. Covar() stands for covariance function, and Var() stands for variance function. These two functions are readily available in Excel. (Hint: using the range names you created in step 2 will simplify the code). After you calculate the betas and returns, write them to range (B3:C5) on worksheet “RP”.

Cov(ra,Tb) -Var(r.) β

Answer 1

coding of Module in VBA

Answer

Step 1 :Create a “main” sub, and assign the sub to the “Start” button on the worksheet as follows :

Sub a()
  Dim btn As Button
  Application.ScreenUpdating = False
  ActiveSheet.Buttons.Delete
  Dim t As Range
  For i = 2 To 6 Step 2
    Set t = ActiveSheet.Range(Cells(i, 3), Cells(i, 3))
    Set btn = ActiveSheet.Buttons.Add(t.Left, t.Top, t.Width, t.Height)
    With btn
      .OnAction = "btnS"
      .Caption = "Btn " & i
      .Name = "Btn" & i
    End With
  Next i
  Application.ScreenUpdating = True
End Sub

Sub btnS()
 MsgBox Application.Caller
End Sub

Step 2: Write the “clearOutput” sub. Also, assign this sub to the “Reset” button. In this sub, you will clear the content of range (B3:D5), (B9:B11), and (D14:D15) on the worksheet “RP” as a folows:

Sub Clearcells_Output()

Range("B3", "B5").Clear

Range("B9", "B11").Clear

Range("D14", "D15").Clear

End Sub

Step 3 :  Write the “loadData” sub. The data is stored in the worksheet “HR”. Name the data range on the worksheet for T-bills, bond, and stock as “tbillHistory”, “bondHistory” and “stockHistory”. Then, you should use the historical data to calculate the average annual returns of each investment option (hint: use excel function Average). You also need to calculate the betas of each investment option. Note that Beta is a relative risk, so here we assume beta of stock is 1, therefore you will calculate the betas of T-bills and bond in relative to stock. The formula to calculate Beta (shown in the photo) Where r_a is return of T-bill or Bond, and r_b is return of stock. Covar() stands for covariance function, and Var() stands for variance function. These two functions are readily available in Excel. (Hint: using the range names you created in step 2 will simplify the code). After you calculate the betas and returns, write them to range (B3:C5) on worksheet “RP”.

sub btnCalculateVarCovar_Click()

Function VarCovar(Rng As range) As Variant
Dim r_a As Integer
Dim r_b As Integer
Dim numcols As Integer

numcols = Rng.Columns.Count
Dim matrix() As Double
ReDim matrix(numcols - 1, numcols - 1)
For r_a = 1 To numcols
    For r_b = 1 To numcols
        matrix(r_a - 1, r_b - 1) = Application.WorksheetFunction.Covar(Rng.Columns(r_a), Rng.Columns(r_b))
    Next r_b
Next r_a

VarCovar = matrix

End Function