Factor3
03-01-2007, 09:57 PM
I'm given a set of monthly returns, which are of varying length. I want to take those monthly returns and convert them into Log Normal Returns in a column next to them (it helps with statistical validity).
Sub LN_MONTHLY()
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy Destination:=Selection.Offset(0, 1)
Selection.End(xlUp).Select
Selection.Offset(1, 1).Activate
ActiveCell.FormulaR1C1 = "=EXP(RC[-1])-1"
Selection.Copy
Range(Selection, Selection.End(xlDown)).PasteSpecial (xlPasteFormulas)
Calculate
End Sub
That's all fine and Dandy (the only reason I copied the first row was to paste the formula only to the last row of the first set of returns...I'm new to this, so I am sure there are about a gazillion ways to expedite, I just don't know them).
Now, I need to actually use this array of data to generate some statistical information. The problem is that I don't want to use an exact range (because the ranges will differ depending on the data), so I am trying to assign an "array" variable to it... like so (the first 8 lines of this code are the EXACT same as the one above, the new stuff starts in red)::banghead:
Sub_STORING()
Dim MoR() As Range, ComR() As Range
Dim MoMu as Double
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy Destination:=Selection.Offset(0, 1)
Selection.End(xlUp).Select
Selection.Offset(1, 1).Activate
ActiveCell.FormulaR1C1 = "=EXP(RC[-1])-1"
Selection.Copy
Range(Selection, Selection.End(xlDown)).PasteSpecial (xlPasteFormulas)
Selection.Offset(1,1).Activate
ComR = Range(Selection, Selection.End(xlDown))
Range("B3").FormulaR1C1 = "=SQRT(VAR(ComR)*(253/365*12))"
End Sub
The (253/365*12) at the very end is actually what I want to be the "stored value" of variable "MoMu" (Dimmed Double at the top).
There are several other statistical tests that I want to do on this array (ComR) as well as the other array (MoR). These tests are one cell outputs (not array outputs) of the results, which makes me think it's not a difficult question that I am asking.
Thanks so much,
Sub LN_MONTHLY()
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy Destination:=Selection.Offset(0, 1)
Selection.End(xlUp).Select
Selection.Offset(1, 1).Activate
ActiveCell.FormulaR1C1 = "=EXP(RC[-1])-1"
Selection.Copy
Range(Selection, Selection.End(xlDown)).PasteSpecial (xlPasteFormulas)
Calculate
End Sub
That's all fine and Dandy (the only reason I copied the first row was to paste the formula only to the last row of the first set of returns...I'm new to this, so I am sure there are about a gazillion ways to expedite, I just don't know them).
Now, I need to actually use this array of data to generate some statistical information. The problem is that I don't want to use an exact range (because the ranges will differ depending on the data), so I am trying to assign an "array" variable to it... like so (the first 8 lines of this code are the EXACT same as the one above, the new stuff starts in red)::banghead:
Sub_STORING()
Dim MoR() As Range, ComR() As Range
Dim MoMu as Double
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy Destination:=Selection.Offset(0, 1)
Selection.End(xlUp).Select
Selection.Offset(1, 1).Activate
ActiveCell.FormulaR1C1 = "=EXP(RC[-1])-1"
Selection.Copy
Range(Selection, Selection.End(xlDown)).PasteSpecial (xlPasteFormulas)
Selection.Offset(1,1).Activate
ComR = Range(Selection, Selection.End(xlDown))
Range("B3").FormulaR1C1 = "=SQRT(VAR(ComR)*(253/365*12))"
End Sub
The (253/365*12) at the very end is actually what I want to be the "stored value" of variable "MoMu" (Dimmed Double at the top).
There are several other statistical tests that I want to do on this array (ComR) as well as the other array (MoR). These tests are one cell outputs (not array outputs) of the results, which makes me think it's not a difficult question that I am asking.
Thanks so much,