Ctrl shift enter for array
I am trying to create a macro that returns the estimated std dev for a portfolio per cell. However, since the number of shares, their insidiousness, and also their weights will change mostly constantly and with different portfolios that I want in VBA (my ultimate goal is to make my reports as light as possible). In the meantime, there is no need to worry about it.
I don't seem to understand ctrl+ shift+ enter, or the sqr part.
Could you take a look at my code help me?
Sub PorteføljeRisiko()
Dim RapportBok As Workbook
Dim RapportArk As Worksheet
Set RapportBok = Workbooks("Rapport kunder")
Set RapportArk = RapportBok.Sheets(1)
Dim Region1 As Long
Dim RegionA As Long
Dim Matrise As Range
(since the number of shares does not change, I find out how many rows actually include the value (i.e.% weighting in each share))
Region1 = Application.WorksheetFunction.CountIf(RapportArk.Range("AC7:AC18"), "<>" & "")
RegionA = Region1 - 1
(to get the matrix for the kovar between stocks, since there are no stocks with a puff and selling stocks. The matrix starts in cells (3.55)
SisteKolonne = RapportArk.Cells(RegionA + 3, RegionA + 55)
Set Matrise = RapportArk.Range(Cells(3, 55), Cells(3 + RegionA, 55 + RegionA))
Set Region = RapportArk.Range("AC7:AC" & Region1 + 6)
(I want the result in Range ("AG21"))
RapportArk.Range("AG21").FormulaArray = MMult(Application.MMult(Region, Matrise), Application.Transpose(Region))
(Everything works fine, except that it returns #VALUE !, as I cannot get neither the ctrl+ shift+ enternor the SQR part in the macro)
End Sub
source to share
First of all, what you are calculating here in VBA is not formula
, but value
. Also, I was not able to verify all of your calculations, but I could see the problem here:
Application.MMult(Region, Matrise)
The dimensions do not follow the rules for matrix multiplication. From code analysis Region
, this is a column vector with size Region1
and Matrise
is a matrix Region1 x Region1
.
Remember that matrix multiplication is not commutative. Most likely, you need to switch the order of these operands:
Application.MMult(Matrise, Region)
This will give you a column vector of size Region1
. But then you want to have a dot product with a vector Region
, and the row vector must appear here again, so you must apply Transpose
to the first operand, not the second. So the correct statement should be:
RapportArk.Range("AG21").value = _
Application.MMult(Application.Transpose(Application.MMult(Matrise, Region)), Region)
Since the last operation is dot-product
two vectors, you can perhaps simplify it with SumProduct
:
RapportArk.Range("AG21").value = _
Application.SumProduct(Application.MMult(Matrise, Region), Region)
source to share