Get First, High, Low, Last value within a given period

[Av8tordude]

I would like to request assistance with a VBA solution. I would like to get the following information below for each month/year and append the information in cell AA2. The dates begin in column (D18 - until) and the values Column (P18 - until). My intention will be to graph the results.

Month/Year

First value
Highest Value
Lowest Value
last Value

I would like the output to be displayed below...

Date	Open	High	Low	Close
09/01/18	-60.1	808.21	-112.9	808.21
10/01/18	-506.4	2784.1	-779.4	1504.1
11/01/18	1981.63	1981.63	466.1	700.1

<tbody>
</tbody>

Date	Price
09/28/2018	-60.2
09/28/2018	-112.9
09/28/2018	348.39
09/28/2018	199.95
09/28/2018	808.21
10/01/2018	-506.4
10/01/2018	-251.2
10/01/2018	-779.4
10/01/2018	758.36
10/30/2018	1504.1
11/01/2018	1981.63
11/02/2018	466.1
11/02/2018	700.1
12/07/2018	486.69
12/08/2018	332.1
12/10/2018	-2377.9
12/10/2018	-3023.46
12/10/2018	-2422.9
12/10/2018	-2867.9
12/10/2018	-1898.31
12/10/2018	1927.1
12/11/2018	-616.23
12/11/2018	456.8
12/12/2018	-1118.3
12/12/2018	-1013.49
12/12/2018	-819.9
12/12/2018	-548.48
12/12/2018	-387.9
12/12/2018	-328.3
12/14/2018	-518.32
12/14/2018	-174.09
12/14/2018	-84
12/20/2018	-2523.12

<tbody>
</tbody>

Thank you kindly

 

[DanteAmor]

Try this

Sub Get_Values()
    Dim dt As Date, c As Range, f As Range
    Dim lr1 As Long, lr2 As Long, y As String, m As String
    
    Range("AA2:AE" & Rows.Count).ClearContents
    lr1 = Range("D" & Rows.Count).End(xlUp).Row
    For Each c In Range("D18", Range("D" & Rows.Count).End(xlUp))
        y = Year(c)
        m = Month(c)
        dt = DateSerial(y, m, 1)
        Set f = Range("AA:AA").Find(dt, LookIn:=xlValues, lookat:=xlWhole)
        If f Is Nothing Then
            lr2 = Range("AA" & Rows.Count).End(xlUp).Row + 1
            Range("AA" & lr2).Value = dt
            Range("AB" & lr2).Value = Cells(c.Row, "P")
            Range("AC" & lr2).Value = Evaluate("=MAX(IF(YEAR(D18:D" & lr1 & ")=" & y & _
                ",IF(MONTH(D18:D" & lr1 & ")=" & m & ",P18:P" & lr1 & ")))")
            Range("AD" & lr2).Value = Evaluate("=MIN(IF(YEAR(D18:D" & lr1 & ")=" & y & _
                ",IF(MONTH(D18:D" & lr1 & ")=" & m & ",P18:P" & lr1 & ")))")
            Range("AE" & lr2).Value = Cells(Evaluate("=MAX(IF(YEAR(D18:D" & lr1 & ")=" & y & _
                ",IF(MONTH(D18:D" & lr1 & ")=" & m & ",ROW(P18:P" & lr1 & "))))"), "P")
        End If
    Next
End Sub

 

[Av8tordude]

DanteAmor...I sincerely thank you for your generous help. The code achieves the results, however, on first run, it does not achieve the results. on the second run, it works. Any reason?

Try this

Sub Get_Values()
    Dim dt As Date, c As Range, f As Range
    Dim lr1 As Long, lr2 As Long, y As String, m As String
    
    Range("AA2:AE" & Rows.Count).ClearContents
    lr1 = Range("D" & Rows.Count).End(xlUp).Row
    For Each c In Range("D18", Range("D" & Rows.Count).End(xlUp))
        y = Year(c)
        m = Month(c)
        dt = DateSerial(y, m, 1)
        Set f = Range("AA:AA").Find(dt, LookIn:=xlValues, lookat:=xlWhole)
        If f Is Nothing Then
            lr2 = Range("AA" & Rows.Count).End(xlUp).Row + 1
            Range("AA" & lr2).Value = dt
            Range("AB" & lr2).Value = Cells(c.Row, "P")
            Range("AC" & lr2).Value = Evaluate("=MAX(IF(YEAR(D18:D" & lr1 & ")=" & y & _
                ",IF(MONTH(D18:D" & lr1 & ")=" & m & ",P18:P" & lr1 & ")))")
            Range("AD" & lr2).Value = Evaluate("=MIN(IF(YEAR(D18:D" & lr1 & ")=" & y & _
                ",IF(MONTH(D18:D" & lr1 & ")=" & m & ",P18:P" & lr1 & ")))")
            Range("AE" & lr2).Value = Cells(Evaluate("=MAX(IF(YEAR(D18:D" & lr1 & ")=" & y & _
                ",IF(MONTH(D18:D" & lr1 & ")=" & m & ",ROW(P18:P" & lr1 & "))))"), "P")
        End If
    Next
End Sub

 

[Av8tordude]

Is it possible to do this weeks? (i.e. find the same thing, but instead of months, use weeks)

 

[DanteAmor]

DanteAmor...I sincerely thank you for your generous help. The code achieves the results, however, on first run, it does not achieve the results. on the second run, it works. Any reason?

It can be the date format.
The date format of column D must be the same format in column AA (mm/dd/yyyy or dd/mm/yyyy)