PDA

View Full Version : Trouble creating a macro for multiplying matrices



L1meta
04-10-2012, 08:48 AM
Hi,

In this sheet I have a matrix of values indexed from 1 to 30, and its transpose to the right.

Below the first matrix I have created the product of the two matrices. I want to create 98 of those. I tried recording a macro that I could edit to automate the process, but I'm not able to change the parameters for the MMULT function in an appropriate fashion.

The procedure was supposed to be like this:
Productmatrix 1 (in the sheet) : First column of transpose * First row of matrix

Productmatrix 2: Second column of transpose * second row of matrix
etc..

Can anyone help me?

7833

Bob Phillips
04-10-2012, 09:48 AM
I have already answered this, so what was wrong with that?

L1meta
04-10-2012, 01:49 PM
Thank you for your reply :)

This one is slightly different, and I'm not yet skilled enough to be able to edit the previous code to make it do what I want.

Bob Phillips
04-10-2012, 01:58 PM
Well, I think it would be helpful to spell out what is different between the two, and give the code from the previous solution.

L1meta
04-11-2012, 04:50 AM
I realized now that the topic title of my thread might not have been apropriate. This was the previous code, which worked well for the previous task.


Public Sub CreateMatrices()
Const FORMULA_COUNT As String = _
"SUMPRODUCT(--(TEXT(C3:C<lastrow>,""yyyymmm"")=""<testdate>""))"
Dim LastRow As Long
Dim LastCol As Long
Dim NextDate As Date
Dim NextRow As Long
Dim vecResult As Variant
Dim sh As Worksheet

Application.ScreenUpdating = False

With ActiveSheet

LastRow = .Cells(.Rows.Count, "C").End(xlUp).Row
LastCol = .Range("D2").End(xlToRight).Column
NextRow = 3
Do

NextDate = .Cells(NextRow, "C").Value
numrows = .Evaluate(Replace(Replace(FORMULA_COUNT, _
"<lastrow>", LastRow), _
"<testdate>", Format(.Cells(NextRow, "C").Value, "yyyymmm")))
vecResult = VarCovar(.Cells(NextRow, "D").Resize(LastRow - NextRow + 1, LastCol - 3))
Set sh = Worksheets.Add(after:=Worksheets(Worksheets.Count))
sh.Name = Format(.Cells(NextRow, "C").Value, "yyyymmm")
sh.Range("A1").Resize(numrows, LastCol - 3) = vecResult

NextRow = NextRow + numrows
Loop Until NextRow > LastRow
End With

Application.ScreenUpdating = True
End Sub

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

The set that this code was designed for was a 30*2296 array of returns, for which it would provide covariance matrixes. And it creates new sheets for each matrix.

My current problem contains a 98*30 array of beta coefficients for the assets(30) during all the months(98) of the previous dataset, plus its transpose. I want to be able to create 98 matrices by doing MMULT(Column i of transpose;Row i of array). And preferably all in one sheet with some space between them. Does that make sense? :p

Bob Phillips
04-11-2012, 05:22 AM
In your example workbook, you have 2 @ 98x30 arrays, one above the other, and 1 @ 30x98 array. Which should we be working with in this case?

L1meta
04-11-2012, 05:38 AM
Array1: B3:AE100 is the array of Betas

Array2: AH2:EA32 is the transpose of Array1

Array3: B102:AE131 is an example of what I want 98 of, which is the columns of Array2 multiplied by rows of Array1

Thank you for taking time to help me with this :)