intra portfolio correlation formula in vba

Hi all, this is my first post and i think it is not an easy one as I cant even get started with it. I have a financial formula that i would like to run in vba. I have a correlation matrix, which is completed, then a code that sorts the relevant data for me to calculate this last step. I included the sheet with some dummy variables and limited data to be easy to understand. I know it is going to be a loop function but what i dont know is how to store a result in vba and recall it later...if someone could help me guide me thru this i would greatly appreciate it. tia

Welcome to the forum!

It is unclear what you are asking. Maybe you could make an even shorter example. I can't see the logic in what you did to solve the equation. You seem to have left some out.

Once we understand the logic, a vba solution can be designed.

Hi Kenneth, thank you for taking some time to take a look at my problem.
the numerator of the formula if I understand correctly is the sum of all the products of each weigths multiplied by their corresponding correlation coefficeint from the matrix. the denominator is simply the sum of the product of the weights.

in cells i10 through i19 i have the product of weights and correlation coefficients.. namely the loop would start out at cell i3 multiply it by j3 multiply it by c3 then step to next step, which is i3 * k3 * c4, next i3 * L3 * c5...i3*m3*c5. after i 3 has been multiplied across, the loop would step to j3 and continue along the same logic: j3*k3*d4 etc...
these products are in cells i10 through i19. cell i21 is the sum of them, which completes the numerator. the denominator is the sum of the weights only (meaning the correlation coefficint from the matrix is not used) in cells k10 through k19...cell k21 is the sum of the product. finally the numerator is divided by denominator.

hope i explained it well.

I found this to explain the formula. http://en.wikipedia.org/wiki/Diversification_(finance)

As I suspected, you are doing i=1 to 5 for j=1 to 5. Since 5*5=25, you will have 25 computations for the numerator and 25 for the denominator. You can simplify the denominator computation by dividing the same denomator sum part by the Pij.

The denominator parts goes like this:
x1*x1*P11
x1*x2*P12
x1*x3*P13
x1*x4*P14
x1*x5*P15

x2*x1*P21
x2*x2*P22
x2*x3*P23
x2*x4*P24
x2*x5*P25
and so on. Make sense now?

So, this is easily done by worksheet formulas.

However, you said that you wanted a VBA method. The next step would be to design a function to accept a Range for the X's (weights) and a Range for the P's (probabilities). The assumptions that I will make is that your X's will be a row, not a column and that your probabilities will be a matrix of the count of X by the count of X.

Now that the problem is defined, the solution is the easy part.

Your data shows 0.393 for the ipc using this udf.

[VBA]Sub Test_IPC()
Debug.Print IPC(Range("I3:M3"), Range("C2:G6"))
End Sub

'=IPC(I3:M3,C2:G6)
Function IPC(rWeights As Range, rProbabilities As Range) As Double
Dim rW As Range, rP As Range
Dim denom As Double, denoms As Double, num As Double, nums As Double
Dim i As Integer, j As Integer

'Make parameters easy for coding.
Set rW = rWeights
Set rP = rProbabilities

'Exit if either parameter count is wrong.
If rW.Count ^ 2 <> rP.Count Then
MsgBox "Probablilities or Weight count is wrong.", vbCritical, "Exit Macro"
Exit Function
End If

For i = 1 To rW.Count
For j = 1 To rW.Count
num = rW.Cells(i) * rW.Cells(i) * rP.Cells(i, j)
nums = nums + num
denom = num / rP.Cells(i, j)
denoms = denoms + denom
Next j
Next i
IPC = nums / denoms
End Function
[/VBA]

actually, the way i understand the formula is each term is multiplied by the other only once...x1*x2 is same as x2*x1 so in this example, if there are 5 weigths, there is only 10 multiplications...that is what i have in the sheet...where i have a problem is writing the macro that spits out the end result/putting the loop together in vba.

didnt refresh page, just noticed u wrote the code...thank you very much...i will take alook at it. but i think the result should be (in this example) 0,2688...if I understand the formula correctly. due to the fact I mentioned above.

Going by Ken's Wikipedia ref, the formula in there for Q's double Sigma shows both i and j go from 1 to N. This index range part is missing from the GIF pasted into your example.

So if N = 5, there would be 5x5 terms. Maybe you were thinking that the formula was <SIGMA> i=1 to 5 <SIGMA> j=i to 5?

I was also wondering about the weights, since I didn't see that in the Wikipedia article:

Quote:

---

Where Q is the intra-portfolio correlation, Xi is the fraction invested in asset i, Xj is the fraction invested in asset j, Pij is the correlation between assets i and j (another number between 1 and -1 that measures how similarly assets i and j perform compared to each other), and n is the number of different assets.

---

Paul

Paul, weight=fraction=%. If X1=10% then 10% was invested in stock 1. If X2=20% then 20% was invested in stock 2 and so on.

On sites like this one, the weight is divided equally. For N (number of stock) = 13 then each would weight as 1/13.
http://www.assetcorrelation.com/

This site has some correlation coefficients for up to 15 stocks for 15-30 days. Not sure how they compute those.
http://www.macroaxis.com/invest/mark...-Fund-Total-Bo

I am working on a macro to make filling the correlation coefficient matrix using just the data shown in the first link. I will check it against what my udf shows. (Based on the quote below, this will not be needed.)

Based on some other web site's definition, the formula is misleading to my way of thinking. I will modify my code to conform with the following exception in the last sentence to the formula. The probability matrix should look more like that on the assetcorrelation site. So, instead of 25 sums for the 5 stocks, we would just need 5*2=10 as you said ilyenabox.

Quote:

---

Where Q is the intra-portfolio correlation, Xi is the fraction invested in asset i, Xj is the fraction invested in asset j, Pij is the correlation between assets i and j, The expression may be computed at least when i does not equal j.

---

After reading the exception more closely and some other sites that made it a bit more clear, the exception to the formula says that when the probablility is 1, then that part is not computed. So, these would not be computed:
X1*X1*P11
X2*X2*P22 and so on.

However, since this is a ratio formula, it does not matter whether we compute the lower half of the probability matrix or the full matrix less the Pij's=1 diagonal parts. In other words, 1/2 is the same as 2/4. I found this to be the case for the 13 stocks in one example. For ilyenabox's data for 5 stocks, the 2 methods varied some.

In my udf, I had to account for the cases of Pij=1 and the case where Pij=0. Pij=0 causes a divide by 0 error so I set that sum to 0 as I did for the Pij's=1.

I have attached the example xls as well.

The udf is then:
[VBA]'=IPC(I3:M3,C2:G6) 'Sheet VBAExpress
'=IPC(C3:O3,C6:O18) 'Sheets AssetCorrelation and AssetCorrelation (2)
Function IPC(rWeights As Range, rProbabilities As Range) As Double
Dim rW As Range, rP As Range
Dim denom As Double, denoms As Double, num As Double, nums As Double
Dim i As Integer, j As Integer

'Make parameters easy for coding.
Set rW = rWeights
Set rP = rProbabilities

'Exit if either parameter count is wrong.
If rW.Count ^ 2 <> rP.Count Then
MsgBox "Probablilities or Weight count is wrong.", vbCritical, "Exit Macro"
Exit Function
End If

For i = 1 To rW.Count
For j = 1 To rW.Count
num = rW.Cells(i) * rW.Cells(i) * rP.Cells(i, j)
'Make the numerator=0 if the probability=1 (comparing same i and j)
If rP.Cells(i, j) = 1 Then num = 0
If rP.Cells(i, j) = 0 Then 'Make denomiator 0 for sum to avoid divide by 0 error.
denom = 0
Else
denom = num / rP.Cells(i, j)
End If
nums = nums + num
denoms = denoms + denom
'Debug.Print "i,j", i & "," & j, num, nums, denom, denoms
Next j
Next i
IPC = nums / denoms
End Function[/VBA]

Though the full probability matrix need not be filled, this code below will fill the top part for you.

Either of the first two subs can be used to fill the diagonal 1's and the top part of a completed bottom part of the matrix. Sites like assetcorrelation give the bottom part. You can use one of the first 2 subs to play. The 1st sub requires that you first select the lower left cell of the probability matrix filled as in the assetcorrelation web site. My udf shows the ratio for the example in the ipc.xls from assetcorrelation example as 0.55 but shows it as 0.57 on their site.
[VBA]Sub FillBySelectLLeftCell()
FillTopDiagonal Selection
End Sub

Sub FillByC18()
FillTopDiagonal Range("C18")
End Sub

'Adds a 1 to top blank cell from the initial cell and transposes
'the column's data to the right. It repeats and the next cell right of the initial
'is empty.
'Fills the top half of a matrix based on a mirror image of the bottom half.
Sub FillTopDiagonal(rLeftCell As Range)
Dim r As Range, t As Range

'Exit if more than one cell was sent.
If rLeftCell.Count > 1 Then
MsgBox "Only select the lower left cell in the matrix.", vbCritical, "Macro Quitting"
Exit Sub
End If

Do
If IsEmpty(rLeftCell.Offset(-1, 0)) Then
Set r = rLeftCell
Set t = rLeftCell.Offset(-1, 0)
Else
Set r = Range(rLeftCell, rLeftCell.End(xlUp))
Set t = rLeftCell.End(xlUp).Offset(-1, 0)
End If
t.Value = 1
t.Offset(0, 1).Resize(1, r.Count).Value = WorksheetFunction.Transpose(r)
Set rLeftCell = rLeftCell.Offset(0, 1)
Loop Until IsEmpty(rLeftCell)
rLeftCell.Value = 1
End Sub
[/VBA]