Highest Lowest price from running price

[dhanu1660]

Hi friends
this is stock market related issue.
L9 cell has a price it shows profit and loss,it changes every second.
L9 Profit
M10 max profit
M12 max loss
example
1. if L9 has 100 profit then M10=100
. if L9 updated 110 then M10=110
. if L9 updated to 90 then M10=110
M10 should be update only for highest values not for lowest values.

[#2 · Accepted solution]

@dhanu1660 

Start by entering -1000000 in M10 and 1000000 in M12. These are initial values that will be overwritten.

Enter the formula =NOW() in another cell (it doesn't matter where).

Right-click the sheet tab.

Select 'View Code' from the context menu.

Copy the following code into the worksheet module:

Private Sub Worksheet_Calculate()
    Application.EnableEvents = False
    Range("M10").Value = Application.Max(Range("L9,M10"))
    Range("M12").Value = Application.Min(Range("L9,M12"))
    Application.EnableEvents = True
End Sub

Switch back to Excel.

Save the workbook as a macro-enabled workbook.

Make sure that you allow macros when you open it.

[#3]

@Hans Vogelaar  Very Very thanks sir, You are super, its working, 

[#4]

Another way to do this without code is to enable iterative calculations.

File | Options | Formulas tab | Calculation Options section | check the Enable iterative calculation checkbox and also change Maximum Iterations to 1.

Then in M10, enter the formula

=MAX(L9, M10)

@dhanu1660 

[#5]

sir ,
How it would be incase of range of item .Data is in L9 :L19 instead of L9 and Max in M9:M19 and MIN O9:O19

[#6]

@samnpti 

What does the worksheet look like now?

[#7]

@Hans Vogelaar 

sir,

Please find attached file.

[#8]

@samnpti 

Thank you. Change the code to

Private Sub Worksheet_Calculate()
    Dim r As Long
    Application.ScreenUpdating = False
    Application.EnableEvents = False
    For r = 9 To 19
        Range("M" & r).Value = Application.Max(Range("L" & r & ",M" & r))
        Range("N" & r).Value = Application.Min(Range("L" & r & ",N" & r))
    Next r
    Application.EnableEvents = True
    Application.ScreenUpdating = True
End Sub

[#9]

Great sir,

It was nice learning from you sir, great work sir.

[#10]

@Hans Vogelaar 

Sir,

Have tried to modify and want to add timestamp whenever Max and Min will updated. The timestamp will also get updated accordingly.

But manual timestamp is working not the  AutoMax and Min.

Please check the attached

Thanks in advance.

[#11]

@samnpti 

Private Sub Worksheet_Calculate()
    Dim r As Long
    Application.ScreenUpdating = False
    Application.EnableEvents = False
    For r = 9 To 19
        If Range("L" & r).Value > Range("M" & r).Value Then
            Range("M" & r).Value = Range("L" & r).Value
            Range("O" & r).Value = Now
        End If
        If Range("L" & r).Value < Range("N" & r).Value Then
            Range("N" & r).Value = Range("L" & r).Value
            Range("P" & r).Value = Now
        End If
    Next r
    Application.EnableEvents = True
    Application.ScreenUpdating = True
End Sub

See the attached version.

[#12]

@Hans Vogelaar 

Sir,

Thanks it is working. But finding  1 small problem.

Problem:-The value range is -1% to 1% in profit part. But the min value is always taking <=0,

1 small changes is requested.

Changes:-

If There is two different profit column

1 for Max profit and 1 for Min profit .

how the changes will be?

Please find attached for your reference

Thanks

[#13]

@samnpti 

Try this version:

Private Sub Worksheet_Calculate()
    Dim r As Long
    Application.ScreenUpdating = False
    Application.EnableEvents = False
    For r = 9 To 19
        If Range("L" & r).Value <> "" Then
            If Range("M" & r).Value = "" Then
                Range("M" & r).Value = Range("L" & r).Value
                Range("O" & r).Value = Now
            ElseIf Range("L" & r).Value > Range("M" & r).Value Then
                Range("M" & r).Value = Range("L" & r).Value
                Range("O" & r).Value = Now
            End If
        End If
        If Range("K" & r).Value <> "" Then
            If Range("N" & r).Value = "" Then
                Range("N" & r).Value = Range("K" & r).Value
                Range("P" & r).Value = Now
            ElseIf Range("K" & r).Value < Range("N" & r).Value Then
                Range("N" & r).Value = Range("K" & r).Value
                Range("P" & r).Value = Now
            End If
        End If
    Next r
    Application.EnableEvents = True
    Application.ScreenUpdating = True
End Sub

[#14]

Sir,

Perfectly fine. Thank you very much. You are a genius.

[#15]

sir
is it anyway possible to make in this way
Min profit 1 will always follow the Max Profit 2

it means MIN profit 1 should always have time stamp after the max profit timestamp

basically what i need. After going MAX profit2 how much min profit 1 goes down.

then i will get Difference between MAx prfit 2-Min profit 1=% of diff

[#16]

@samnpti 

How would that work? Once you have a Min value you would never be able to set the Max value anymore, since that would make the Max timestamp later than the Min timestamp.

[#17]

Sir,

I understand,
Just curiosity question.

Min can act after Max timestamp?
If Max timestamp changed Min will also change accordingly. and it will bring Min value after that maxtimestamp

I mean MinValue timestamp will be always later of Maxtimestamp.

Anyway sir thank you for your support.

[#18]

@samnpti 

I'd leave it as it is now.

[#19]

Ok sir.Thanks

[#20]

@Hans Vogelaar 

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("A:A")) Is Nothing Then
If Target >= 1 / 100 Then
Range("B" & Target.Row).Clear
End If
End If
End Sub

Sir how to change it to formula in "A"

only manual typing is clearing cell B.

want to clear B when A cell change from other reference cell.

something like below

Option Explicit
Private Sub Worksheet_Calculate()
Application.EnableEvents = False
If Not Intersect(Target, Range("A:A")) Is Nothing Then
If Target >= 1 / 100 Then
Range("B" & Target.Row).Clear
Application.EnableEvents = True
End Sub

but its not working