Function Recalc(SumStage2, Limit, Stage2, Gain, AvgIfGain, Trigger, Base)
Dim Alt
If SumStage2 > Limit Then Alt = 1 Else Alt = 0
If (AvgIfGain > 0 And Gain / (AvgIfGain + 0.001) > Trigger) Or Base = 0 Or Stage2 = 0 Then Recalc = 0 Else Recalc = Alt * Application.WorksheetFunction.Min(Base, Stage2) + (1 - Alt) * Stage2
End Function
Public Function AVGIF(ByVal checkRange As Range, ByVal criteria As Variant, ByVal avgRange As Range) As Variant
Dim sumIfResult As Double
Dim countIfResult As Double
sumIfResult = Application.WorksheetFunction.SumIf(checkRange, criteria, avgRange)
countIfResult = Application.WorksheetFunction.CountIf(checkRange, criteria)
If countIfResult = 0 Then
AVGIF = 0
Else
AVGIF = sumIfResult / countIfResult
End If
End Function
Function shares(Balance, price, pct_active, last_pct_active, Last)
If Last = 0 Then
If pct_active > 0 Then shares = Int(Balance * pct_active / price) Else shares = 0
Exit Function
End If
If pct_active <> last_pct_active Then shares = Int(Balance * pct_active / price) Else shares = Last
End Function
Public Function checkmember(a As Date, dates As Range, Flag) As String
Dim B As String, C
B = ""
For Each C In dates
If DateValue(C.VALUE) = a Then B = Flag
Next C
checkmember = B
End Function
Function SharpeRatio(InvestReturn, RiskFree) As Double
Dim AverageReturn As Double
Dim StandardDev As Double
Dim ExcessReturn() As Double
Dim nValues As Integer
nValues = InvestReturn.Rows.Count
ReDim ExcessReturn(1 To nValues)
For I = 1 To nValues
ExcessReturn(I) = InvestReturn(I) - RiskFree(I)
Next I
AverageReturn = Application.WorksheetFunction.Average(ExcessReturn)
StandardDev = Application.WorksheetFunction.StDev(ExcessReturn)
SharpeRatio = AverageReturn / StandardDev
End Function
Function XavCorePct6(core, XAVCore, COREpcnt, coreext, Limit, Sum, Scal)
Dim use
If core < XAVCore Then
XavCorePct6 = 0
Exit Function
End If
use = COREpcnt + Sum * Scal
If Sum > 0 And use <= Limit Then
XavCorePct6 = Application.WorksheetFunction.Min(COREpcnt, Limit - use)
Exit Function
End If
If use <= Limit Then XavCorePct6 = Application.WorksheetFunction.Min(coreext, Limit - use) Else XavCorePct6 = 0
End Function
Function INITTRADE(THISDATE As Range, STARTDATE As Date, val As Range)
Dim C, B
Dim ICOUNT As Integer, MAX As Integer
Dim ARR1()
Dim ARR2()
MAX = THISDATE.Rows.Count
'MsgBox (MAX)
ARR1 = THISDATE
ARR2 = val
For ICOUNT = 1 To MAX
If DateValue(ARR1(ICOUNT)) = STARTDATE Then
INITTRADE = ARR2(ICOUNT, 1)
Exit Function
End If
Next ICOUNT
End Function
Function minf(a, B)
minf = Application.WorksheetFunction.Min(a, B)
End Function
Function maxf(a, B)
maxf = Application.WorksheetFunction.MAX(a, B)
End Function
Bookmarks