calculate Max & Min for Live market data

[nt82]

So futures market opens at 9 30 am CDT. I've a software through which i can create a DDE link and receive live market data in excel.

So Last Price ( A1 ) changes as market price moves. I would like to calculate High ( max ) and Low ( Min ) for only time period between 9 30 am to 9 45 am.

Basically I want to calculate range in that 15 mins after open.

Please let me know if anyone has a macro or formula which can acheive above task.

thanks in advance

 

[John_w]

You could do this with the Worksheet_Change event, like this:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Time >= TimeValue("09:30") And Time <= TimeValue("09:45") Then
        If Not Intersect(Target, Range("A1")) Is Nothing Then
            Range("B1").Value = Application.WorksheetFunction.Max(Range("A1"), Range("B1"))
            Range("C1").Value = Application.WorksheetFunction.Min(Range("A1"), Range("C1"))
        End If
    End If
End Sub

B1 is High, C1 is Low.

 

[nt82]

Just tried but the code is not doing anything when i paste this code under sheet1

Any thoughts?

 

[John_w]

If A1 is the result of a formula, use Worksheet_Calculate:

Private Sub Worksheet_Calculate()
    Application.EnableEvents = False
    If Time >= TimeValue("09:30") And Time <= TimeValue("09:45") Then
        Range("B1").Value = Application.WorksheetFunction.Max(Range("A1"), Range("B1"))
        Range("C1").Value = Application.WorksheetFunction.Min(Range("A1"), Range("C1"))
    End If
    Application.EnableEvents = True
End Sub

 

[nt82]

PERFECTO..thanks a lot