Upload of file keeps failing, so I can't attach anything. Basically, outputs are supposed to be in B2
3; monthly returns (which are the ONLY provided information from these reports) are from B10:Unknown (because the return periods might differ).
The error message that I am getting (that makes it so I can't even BEGIN to debug is:
Compile Error:
"Block If without End If".... and I swear if I hear that friggin' beep again this box is goin' out the window....
Anywho, here it is
[vba]Sub MoRet2Stats()
Dim outSheet, inSheet
Dim MoRet(1 To 1000), ComRet(1 To 1000), CumRet(1 To 1000)
Dim RowCnt, FindLast, LastRow
Dim firstDataRow, MoDateCol, MoRetCol, ComRetCol
Dim SDAn, SDCo, MeanAn, MeanCo, GeoMn, ProbL0, ProbG0, ProbGInfl
outSheet = "Report Page"
inSheet = "Return Page"
firstDataRow = 10
MoDateCol = 1
MoRetCol = 2
CumRetCol = 4
ComRetCol = 3
'The original sheet with monthly returns is activated
Sheets(inSheet).Activate
'Because the number of periods of returns is variable,
'this calcuates the overall number of periods of returns, and stores
'that value inside the variable 'FindLast'
For FindLast = 1 To 1000
LastRow(FindLast) = Cells(firstDataRow + FindLast - 1, MoDateCol).Value
If LastRow <> "" Then
FindLast = FindLast + 1
??? What do you want to do ? Cycle through all the not empty rows. Better determine the last
row that is used --- lrow = range("A" & rows.count).end(xlup).row --- will give a number
so FindLast = 10 To lrow -- You also need to have a Next Findlast --
and then the code that needs to be done
Else: GoTo BeginCompute
'Now with the # of returns counted, the Cumulative Return Column (CumRetCol)
'and the Continuously Compounded Return Column (ComRetCol) can be calculated
'and utilized for later variables
BeginCompute:
For RowCnt = 1 To FindLast
CumRetCol(RowCnt) = Cells(firstDataRow + RowCnt - 1, MoRetCol) + 1
ComRetCol(RowCnt) = Exp(Cells(firstDataRow + RowCnt - 1, MoRetCol)) - 1
Next RowCnt
'Now the actual statistical variables can be calculated, and there locations are found below this paragraph
SDAn = SQRT * (Application.Var([firstDataRow:MoRetCol], [FindLast:MoRetCol]) * (253 / 365 * 12))
SDCo = SQRT * (Application.Var([firstDataRow:ComRetCol], [FindLast:ComRetCol]) * (253 / 265 * 12))
MeanAn = Application.Average([firstDataRow:MoRetCol], [FindLast:MoRetCol]) * (253 / 365 * 12)
MeanCo = Application.Average([firstDataRow:ComRetCol], [FindLast:ComRetCol]) * (253 / 265 * 12)
GeoMn = Application.SumProduct([firstDataRow:CumRetCol], [FindLast:CumRetCol]) ^ ((253 / 265 * 12) / FindLast)
'Here is where the location of the statistical variables is placed
Cells(2, 2).Value = SDAn
Cells(2, 3).Value = SDCo
Cells(3, 2).Value = MeanAn
Cells(3, 3).Value = MeanCo
Cells(4, 3).Value = GeoMn
End If
End Sub[/vba]