我想通过 VBA 在我的工作表中创建一系列协方差矩阵
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/10064687/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
I want to create a series of covariance matrices in my worksheet through VBA
提问by L1meta
I'm new here. :) I tried searching first, but couldn't find exactly what I'm looking for, so here goes.
我是新来的。:) 我先尝试搜索，但找不到我要找的东西，所以这里是。
I have a set consisting of a column of dates and T daily returns for N assets in my excel worksheet. What I want to do is to create a N*N covariance matrix for the beginning of each month based on the returns of all the assets for the past x days.
我的 Excel 工作表中有一组由日期列和 N 资产的 T 日收益组成的集合。我想要做的是根据过去 x 天所有资产的回报为每个月初创建一个 N*N 协方差矩阵。
I have this code here for creating a covariance matrix, but I don't know how to make excel/vba create several of them automatically for the beginning of each month. Awesome if any of you can help me. :)
我在这里有这个用于创建协方差矩阵的代码，但我不知道如何让 excel/vba 在每个月初自动创建其中的几个。如果你们中的任何人可以帮助我，那就太棒了。:)
Function VarCovar(Rng As range) As Variant
Dim i As Integer
Dim j As Integer
Dim numcols As Integer
numcols = Rng.Columns.Count
Dim matrix() As Double
ReDim matrix(numcols - 1, numcols - 1)
For i = 1 To numcols
For j = 1 To numcols
matrix(i - 1, j - 1) = Application.WorksheetFunction.Covar(Rng.Columns(i), Rng.Columns(j))
Next j
Next i
VarCovar = matrix
End Function
采纳答案by ja72
Try this:
尝试这个：
Sub VarCovar(rng as Range, target as Range)
Dim i As Integer
Dim j As Integer
Dim numcols As Integer
numcols = Rng.Columns.Count
Dim matrix() As Variant
ReDim matrix(1 to numcols, 1 to numcols)
For i = 1 To numcols
For j = 1 To numcols
matrix(i, j) = Application.WorksheetFunction.Covar(Rng.Columns(i), Rng.Columns(j))
Next j
Next i
Set target = target.Resize(numcols,numcols)
target.Value = matrix
End Sub
Now when you want this to be done, call the function with a reference range for your data (as before) and a reference range to the target location where the covar is shown.
现在，当您希望完成此操作时，请使用数据的参考范围（和以前一样）和显示 covar 的目标位置的参考范围调用该函数。
