PDA

View Full Version : how to use correlation in vba



amateur1902
02-25-2008, 06:35 AM
Hello everyone,

I am trying to create a dynamic correlation calculation in vba. De date that i am using is also in vba.

I absolutely have no idea wich way i should go or what formula's are possible to use for correlation in vba.

I hope you all understand my question.

If not, please ask and i will try to make it more clear.

thanks.

gwkenny
02-25-2008, 06:47 AM
Sorry, I do not understand what dynamic correlation calculation means :(

I know what correlation with regards to data means, and correlation coefficients, but dynamic correlation calculation is beyond me :(

amateur1902
02-25-2008, 07:04 AM
I am sorry,

You are right it is not that wel formulated. What i mean is that i want to calculate the correlation over variabels that i have got in Vba. I dont know what i have to do to come to an corralation.

This is what i have so far

Range("g7").Select
Range(Selection, Selection.End(xlDown)).Select
totalrows = ActiveSheet.UsedRange.Rows.Count - 1
Dim Tict() As String
ReDim Tict(totalrows)
Range("g7").Select
i = 0
Do While i <= totalrows
Tict(i) = ActiveCell.Value
ActiveCell.Offset(1, 0).Select
i = i + 1
Loop
arraySecurities = Tict
End Sub

And i don't know how to come to a correlation when i look to vtresult.

Thanks

gwkenny
02-25-2008, 10:38 PM
I'm going to try again, but honestly I do not think we are going to get very far cause we surely are NOT communicating.

I'll reiterate: I know what correlation with regards to data means, and correlation coefficients, but dynamic correlation calculation is beyond me!

"What i mean is that i want to calculate the correlation over variabels that i have got in Vba" Ummmm, yeah. Well what does that mean to you?

To me it means you have TWO or more sets of data. The code you show demonstrates ONE set of data being read into a one-dimensional array.

Then you have some miscellaneous blurb at the end, "And i don't know how to come to a correlation when i look to vtresult."

Can you please try defining the variables you want to discern the correlation between. That would be a great place to start!

The fact that you say "vtresult" leads me to believe Bloomberg is involved with this, but as there's nothing else beyond just the mention, there is absolutely no clue as to what you are doing.

Additionally you read data into a STRING array. How do you perform correlation coefficient calculations, variance, adjusted R-squared on text? Or are we talking a different type of correlation?

Are you calculating market Beta's?