Hey Chris. I realize you've already accepted an answer, but thought I'd post a solution I use. Hopefully it's of some use to someone. If not at least it provides a place for me to find it sometime in the future. :-)
This is VBA code fired off directly from an Excel 2007 code module. It could be easily converted to .Net.
The key for the data manipulation is the pivot table object. I've found it to be massively efficient at getting the data into the layout you've specified.
Sub GetIndexData ()
Dim cn as ADODB.Connection, cmd As ADODB.Command, rs As ADODB.Recordset
Dim rPivotTopLeft As Range, rPivotBottomRight As Range
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
'Get the data.'
Set cn = New ADODB.Connection
With cn
.Provider = "SQLOLEDB"
.ConnectionString = "Database=" & mDBName & ";" & _
"Server=" & mDBServerName & ";" & _
"UID=" & mDBUserID & ";" & _
"Password=" & mDBPassword & ";" & _
"Persist Security Info=True;"
.CursorLocation = adUseClient
.Open
End With
Set cmd = New ADODB.Command
Set rs = New ADODB.Recordset
With cmd
.ActiveConnection = adoTools.DBConnection
.CommandType = adCmdText
.CommandText = "SELECT YourData From YourSource WHERE YourCritera"
Set rs = .Execute
End With
If Not (rs.BOF And rs.EOF) Then 'Check that we have some data.'
'Put the data into a worksheet.'
With wsRawData
.Cells.CurrentRegion.Clear
Set rPivotTopLeft = .Range("A1")
With ThisWorkbook.PivotCaches.Add(SourceType:=xlExternal)
Set .Recordset = rs
.CreatePivotTable _
TableDestination:=rPivotTopLeft, _
TableName:="MyPivotTable"
End With
'Massage the data into the desired layout.'
With .PivotTables("MyPivotTable")
.ManualUpdate = True
.PivotFields("Date").Orientation = xlRowField
.PivotFields("Index").Orientation = xlColumnField
.AddDataField .PivotFields("Return"), "Returns", xlSum
.DisplayFieldCaptions = False
.ColumnGrand = False
.RowGrand = False
.ManualUpdate = False
End With
mMonthCount = Range(.Range("A3"), .Cells(Rows.Count, "A").End(xlUp)).Count
mIndexCount = Range(.Range("B2"), .Cells(2, Columns.Count).End(xlToLeft)).Count
'Convert pivot table to values.'
Set rPivotBottomRight = .Cells(mMonthCount + 2, mIndexCount + 1)
With .Range(rPivotTopLeft, rPivotBottomRight)
.Copy
.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
End With
'Format the worksheet.'
.Range("A3").Resize(mMonthCount, 1).NumberFormat = "mmm-yy"
.Range("B3").Resize(mMonthCount, mIndexCount).NumberFormat = "0.00%"
Union(.Rows(2), .Columns(1)).Font.Bold = True
.Cells.ColumnWidth = 7.14
.Rows(1).Delete
End With
rs.close
Set rs = Nothing
cmd.ActiveConnection = Nothing
Set cmd = Nothing
cn.close
Set cn = Nothing
End Sub
From there its relatively easy to leverage the built in excel regression statistics to output a correlation matrix. With this technique I'm producing a worksheet with a 600x600 matrix of correlations in about 45 seconds.
Note that the .PivotFields parameters should be changed to fit the column names of your data from your data source.