Factor3

03-02-2007, 01:39 AM

Upload of file keeps failing, so I can't attach anything. Basically, outputs are supposed to be in B2:D3; 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: pray2:

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

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 Sub

EDIT: Tommy -> Added Line Breaks for readability

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: pray2:

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

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 Sub

EDIT: Tommy -> Added Line Breaks for readability