sabrinasamuelsen-9409 avatar image
0 Votes"
sabrinasamuelsen-9409 asked sabrinasamuelsen-9409 edited

calculate the average correlation among stocks whose correlations are between 25% (LB) and 75% (UB).

I need help for ending my code i VBA, I am very new to coding and dont know how to code this.. I got an array with the returns of 5 different stocks over the past 5 years. I need to calculate the average correlation among these stocks whose correlations are between 25% (LB) and 75% (UB). So far my coding looks like this, I've managed to calculate the "return average correlation".

Option Explicit

Function AvgRhoBounded(RET, LB, UB)

 ' get number of assets

n = data.Columns.Count

 ' add up all returns

total_rho = 0
n_rho = 0
For i = 1 To n
For j = i + 1 To n

         rho_ij = Application.WorksheetFunction.Correl(data.Columns(i), data.Columns(j))
         total_rho = total_rho + rho_ij
         n_rho = n_rho + 1

     Next j
 Next i

 ' return average correlations

AvgRho = total_rho / n_rho

'calculating the average correlation between stocks whose correlation are between 25% and 75%

Dim LB As String
Dim Lowerbound As String

Dim RET As String

Dim UB As String
Dim Upperbound As String

If LB = 25 Then
Lowerbound = 25

ElseIf UB = 75 Then
Upperbound = 75

End If

End Function

... I've read that I can use the dim function, the loop function, the if-statment function and the LB/UB function but I dont understand how.

I appreciate any help that i get!!

· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

As your issue is about VBA, but the tag "office-excel-itpro" focus on general issues of Excel, I would remove it.
Thanks for your understanding.

1 Vote 1 ·

0 Answers