Jay said:
Wht i m looking for is
I would like to find out in a tenure of trading Maximum % Lost say
$1000
$1200
$1350
$1450 - Max 1
$1200
$1450
$1200
$1100
$1000
$900 - Min 1
$1000
$1200
$1350
$1450
$1500 - Max 2
$1200
$1100
$1000
$800 - Min 2
1st Max and Min - $1450-900/$1450 = 37.94% Lost this will be
displayed 1st
2nd Max and Min - $1500-800/$1500= 46% lost this will displayed noth
the first one as we have lost more than first Max Min .
its about checking each trading day Max bank minimum bank - minimum
bank should be taken after the maximum bank attained not the previous
minimum
Max-Min/Max * 100 = Max % Lost after attaining Maximum bank.
Minimum should be taken after the maximum bank attained as explained
above
what ever lost has to be Maximum % lost in all the trading ....
i think its clear now
regards
jay
Jay
I don't think it's possible to make a formula solution without helper cells
and/or named formulae, so I have dropped it and instead made a user
defined function (UDF). It has the added advantage, that you are able
to find the max % lost for an arbitrary (sequentially) period of time.
Here's how to implement it:
1. Copy the code below (see note!)
2. Enter the VBA-editor with <Alt><F11>
3. Doubleclick the project in the project window
at the left side of the screen. (if it isn't visible, use <Ctrl>r)
4. Choose the menu Insert > Module
5. Doubleclick the new module in the project window
6. Paste the code to the right hand window.
7. Return to the sheet with <Alt><F11>
8. Save the workbook.
From the workbook:
Assuming data in A2:A4000 (more than 10 years, so you probably
haven't filled all cells in the range
In any cell outside column A (e.g. G1) enter:
=maxbank(a2:a4000)
G1 displays the result for all entered values (e.g. a2:a167)
=maxbank(a23:a129)
G1 displays the result for the period spanning cells a23 through a129.
As you enter new data in a168 and down, G1 will display the
largest % loss for the various groups (a group being data from
one max value to the next, or from the last max value to the
last entry)
Regards
Leo Heuser
Note:
Because of the spaces in front of the lines, the code may not work
when copied and pasted. If you experience that open the toolbar
"Edit", select all code in the module window and press the button
"Outdent" repeatedly until *all* lines match the left border of the
window.
The code may loose in readability, but it works
Function MaxBank(BankRange As Range) As Double
'Leo Heuser, Sep. 16, 2006
Dim BankRangeValue As Variant
Dim Counter As Long
Dim Counter1 As Long
Dim CountElement As Long
Dim GetMaxiValue As Double
Dim GetMiniValue As Double
Dim MaxiBankRow() As Long
Dim MiniBankValue() As Double
Dim Result() As Double
Set BankRange = BankRange.Columns(1)
If IsEmpty(BankRange.Cells(1, 1). _
Offset(BankRange.Rows.Count)) Then
Set BankRange = Range(BankRange.Cells(1, 1), _
ActiveSheet.Cells(ActiveSheet.Rows.Count, _
BankRange.Column).End(xlUp))
End If
If BankRange.Rows.Count = 1 Then
MaxBank = 0
GoTo Finito
End If
BankRangeValue = BankRange.Value
GetMaxiValue = BankRangeValue(1, 1)
ReDim MaxiBankRow(1 To UBound(BankRangeValue, 1))
CountElement = 1
MaxiBankRow(CountElement) = CountElement
For Counter = 2 To UBound(BankRangeValue, 1) - 1
If BankRangeValue(Counter, 1) > GetMaxiValue Then
If BankRangeValue(Counter, 1) > _
BankRangeValue(Counter + 1, 1) Then
CountElement = CountElement + 1
GetMaxiValue = BankRangeValue(Counter, 1)
MaxiBankRow(CountElement) = Counter
End If
End If
Next Counter
MaxiBankRow(CountElement + 1) = Counter
ReDim Preserve MaxiBankRow(1 To CountElement + 1)
ReDim MiniBankValue(1 To UBound(MaxiBankRow))
ReDim Result(1 To UBound(MaxiBankRow))
For Counter = 1 To UBound(MiniBankValue) - 1
GetMiniValue = BankRangeValue(MaxiBankRow(Counter), 1)
For Counter1 = MaxiBankRow(Counter) To MaxiBankRow(Counter + 1)
If BankRangeValue(Counter1, 1) < GetMiniValue Then
GetMiniValue = BankRangeValue(Counter1, 1)
End If
Next Counter1
MiniBankValue(Counter) = GetMiniValue
Next Counter
MiniBankValue(Counter) = _
BankRangeValue(UBound(BankRangeValue, 1), 1)
For Counter = 1 To UBound(Result)
Result(Counter) = (BankRangeValue(MaxiBankRow(Counter), 1) - _
MiniBankValue(Counter)) / _
BankRangeValue(MaxiBankRow(Counter), 1)
Next Counter
MaxBank = Application.Max(Result)
Finito:
End Function
