help with finding local max and local min (advanced question)

L

locksmith55

New Member
Joined
Mar 6, 2022
Messages
22
Office Version
  1. 365
Platform
  1. Windows
Hi,

I am trying to find the local max and min values and their corresponding time in the included spreadsheet. The "trade high" needs to be used to find the local max and the "trade low" needs to be used to find the local min. I have earlier tried to do this by comparing the current price with the previous price and the next price, however, this gives too many values in my case.

The solution can use multiple formulas or multiple helper columns, as long as it is dynamical.

I have attached a picture of the stock chart that corresponds to this data. As you can see I am trying to capture the data that is circled in the picture. A few things to know. Almost all local min to local max have at least 10 minutes pasted between them. Also the local min to local max should have atleast 5% price increase between them.

I dont need to be able to capture every local max and local min as long as I can capture the general move like in shown in the picture.

Note that not all the values in the picture are 100% perfect, but they are very close.

lfosdpkf,sdfsdssss.PNG


UPDATED EXCEL STOCK PROBLEM.xlsx
RST
1timeTrade HighTrade Low
210:20 AM3.463.32
310:21 AM3.363.29
410:22 AM3.343.3
510:23 AM3.343.27
610:24 AM3.383.31
710:25 AM3.393.31
810:26 AM3.383.33
910:27 AM3.343.23
1010:28 AM3.323.27
1110:29 AM3.353.3033
1210:30 AM3.343.26
1310:31 AM3.353.27
1410:32 AM3.333.21
1510:33 AM3.233.13
1610:34 AM3.223.165
1710:35 AM3.213.16
1810:36 AM3.203.13
1910:37 AM3.243.13
2010:38 AM3.273.18
2110:39 AM3.243.19
2210:40 AM3.253.15
2310:41 AM3.233.11
2410:42 AM3.283.21
2510:43 AM3.263.2
2610:44 AM3.243.2
2710:45 AM3.273.18
2810:46 AM3.253.17
2910:47 AM3.233.14
3010:48 AM3.223.145
3110:49 AM3.162.96
3210:50 AM3.042.98
3310:51 AM3.022.94
3410:52 AM3.002.96
3510:53 AM3.032.98
3610:54 AM3.043.01
3710:55 AM3.032.98
3810:56 AM3.012.98
3910:57 AM3.012.95
4010:58 AM3.002.98
4110:59 AM2.992.94
4211:00 AM3.002.94
4311:01 AM2.972.9
4411:02 AM2.962.9
4511:03 AM2.962.93
4611:04 AM2.992.93
4711:05 AM2.992.96
4811:06 AM3.002.96
4911:07 AM3.002.97
5011:08 AM2.992.94
5111:09 AM2.982.95
5211:10 AM2.982.91
5311:11 AM3.022.97
5411:12 AM3.082.98
5511:13 AM3.073.02
5611:14 AM3.083.03
5711:15 AM3.063.02
5811:16 AM3.063.01
5911:17 AM3.043
6011:18 AM3.032.97
6111:19 AM2.982.95
6211:20 AM3.002.96
6311:21 AM3.022.97
6411:22 AM3.043
6511:23 AM3.052.98
6611:24 AM3.022.98
6711:25 AM3.012.99
6811:26 AM3.032.9917
6911:27 AM3.023
7011:28 AM3.022.94
7111:29 AM2.992.95
7211:30 AM2.992.95
7311:31 AM2.972.92
7411:32 AM2.952.81
7511:33 AM2.842.78
7611:34 AM2.842.8
7711:35 AM2.842.79
7811:36 AM2.862.83
7911:37 AM2.862.82
8011:38 AM2.832.8
8111:39 AM2.832.79
8211:40 AM2.822.77
8311:41 AM2.812.77
8411:42 AM2.842.7911
8511:43 AM2.862.81
8611:44 AM2.862.83
8711:45 AM2.852.83
8811:46 AM2.842.8
8911:47 AM2.822.78
9011:48 AM2.822.79
9111:49 AM2.832.81
9211:50 AM2.852.81
9311:51 AM2.822.79
9411:52 AM2.812.7701
9511:53 AM2.822.8
9611:54 AM2.822.77
9711:55 AM2.822.77
9811:56 AM2.782.6601
9911:57 AM2.702.64
10011:58 AM2.672.64
10111:59 AM2.672.64
10212:00 PM2.682.66
10312:01 PM2.702.67
10412:02 PM2.722.69
10512:03 PM2.722.68
10612:04 PM2.732.68
10712:05 PM2.732.7
10812:06 PM2.712.685
10912:07 PM2.762.69
11012:08 PM2.782.73
11112:09 PM2.772.715
11212:10 PM2.762.73
11312:11 PM2.782.73
11412:12 PM2.752.72
11512:13 PM2.742.71
11612:14 PM2.742.72
11712:15 PM2.732.71
11812:16 PM2.762.71
11912:17 PM2.802.75
12012:18 PM2.792.76
12112:19 PM2.782.75
12212:20 PM2.782.72
12312:21 PM2.762.73
12412:22 PM2.792.75
12512:23 PM2.782.75
12612:24 PM2.832.76
12712:25 PM2.842.81
12812:26 PM2.832.81
12912:27 PM2.872.81
13012:28 PM2.882.85
13112:29 PM2.882.84
13212:30 PM2.902.87
13312:31 PM2.902.88
13412:32 PM2.902.865
13512:33 PM2.882.85
13612:34 PM2.892.85
13712:35 PM2.892.865
13812:36 PM2.882.86
13912:37 PM2.882.86
14012:38 PM2.942.87
14112:39 PM2.952.92
14212:40 PM2.932.9
14312:41 PM2.922.9
14412:42 PM2.912.87
14512:43 PM2.892.86
14612:44 PM2.872.841
14712:45 PM2.892.86
14812:46 PM2.892.85
14912:47 PM2.882.85
15012:48 PM2.882.86
15112:49 PM2.882.8
15212:50 PM2.832.79
15312:51 PM2.822.78
15412:52 PM2.822.79
15512:53 PM2.822.8
15612:54 PM2.812.75
15712:55 PM2.782.75
15812:56 PM2.792.74
15912:57 PM2.802.77
16012:58 PM2.792.74
16112:59 PM2.752.72
1621:00 PM2.762.73
1631:01 PM2.762.7422
1641:02 PM2.762.72
1651:03 PM2.742.71
1661:04 PM2.752.71
1671:05 PM2.762.73
1681:06 PM2.742.72
1691:07 PM2.742.7
1701:08 PM2.732.69
1711:09 PM2.742.7
1721:10 PM2.762.73
1731:11 PM2.762.725
1741:12 PM2.752.73
1751:13 PM2.742.72
1761:14 PM2.722.62
1771:15 PM2.632.6
1781:16 PM2.622.58
1791:17 PM2.612.58
1801:18 PM2.612.58
1811:19 PM2.602.57
1821:20 PM2.592.57
1831:21 PM2.622.58
1841:22 PM2.662.61
1851:23 PM2.662.64
1861:24 PM2.662.64
1871:25 PM2.652.61
1881:26 PM2.652.61
1891:27 PM2.652.63
1901:28 PM2.652.63
1911:29 PM2.642.62
1921:30 PM2.632.59
1931:31 PM2.622.59
1941:32 PM2.612.58
1951:33 PM2.592.54
1961:34 PM2.562.54
1971:35 PM2.562.53
1981:36 PM2.572.53
1991:37 PM2.592.5571
2001:38 PM2.592.57
2011:39 PM2.612.58
2021:40 PM2.612.58
2031:41 PM2.612.59
2041:42 PM2.662.59
2051:43 PM2.692.62
2061:44 PM2.682.65
2071:45 PM2.682.66
2081:46 PM2.702.67
2091:47 PM2.752.69
sample 1
Cell Formulas
RangeFormula
R2R2=INDEX($G:$G,$K$13)
S2S2=INDEX($B:$B,$K$13)
T2T2=INDEX($C:$C,$K$13)
R3:R209R3=IF($K$13-ROW(1:1)<$K$16,"",INDEX($G:$G,$K$13-ROW(1:1)))
S3:S209S3=IF($K$13-ROW(1:1)<$K$16,"",INDEX($B:$B,$K$13-ROW(1:1)))
T3:T209T3=IF($K$13-ROW(1:1)<$K$16,"",INDEX($C:$C,$K$13-ROW(1:1)))
 

Excel Facts

Excel Can Read to You
Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.
Sort by date Sort by votes
LockSmith
locksmith.xlsx
ABCDEFGHIJ
1timeTrade HighTrade Lowminmax2time27time interval
210:203,463,32 3,4610:20
310:213,363,29  10:21
410:223,343,3  10:22
510:233,343,27  10:23
610:243,383,31  10:24
710:253,393,31  10:25
810:263,383,33  10:26
910:273,343,23  10:27
1010:283,323,27  10:28
1110:293,353  10:29
1210:303,343,26  10:30
Blad3
Cell Formulas
RangeFormula
D2:D12D2=IF(AND(COUNTIF(OFFSET($B2,MAX(2-ROW(),-$H$1),,MIN(1+$H$1,ROW()-1),),$B2)=1,$B2=MIN(OFFSET($B2,MAX(2-ROW(),-$H$1),,MIN(ROW()+$H$1,1+2*$H$1),))),$B2,"")
E2:E12E2=IF(AND(COUNTIF(OFFSET($B2,MAX(2-ROW(),-$H$1),,MIN(1+$H$1,ROW()-1),),$B2)=1,$B2=MAX(OFFSET($B2,MAX(2-ROW(),-$H$1),,MIN(ROW()+$H$1,1+2*$H$1),))),$B2,"")
F2:F12F2=TEXT(A2,"uu:mm")
 

Attachments

  • Schermafbeelding 2022-03-08 235738.png
    Schermafbeelding 2022-03-08 235738.png
    29.6 KB · Views: 25
Upvote 0
Solution
BSALV said:
LockSmith
locksmith.xlsx
ABCDEFGHIJ
1timeTrade HighTrade Lowminmax2time27time interval
210:203,463,32 3,4610:20
310:213,363,29  10:21
410:223,343,3  10:22
510:233,343,27  10:23
610:243,383,31  10:24
710:253,393,31  10:25
810:263,383,33  10:26
910:273,343,23  10:27
1010:283,323,27  10:28
1110:293,353  10:29
1210:303,343,26  10:30
Blad3
Cell Formulas
RangeFormula
D2:D12D2=IF(AND(COUNTIF(OFFSET($B2,MAX(2-ROW(),-$H$1),,MIN(1+$H$1,ROW()-1),),$B2)=1,$B2=MIN(OFFSET($B2,MAX(2-ROW(),-$H$1),,MIN(ROW()+$H$1,1+2*$H$1),))),$B2,"")
E2:E12E2=IF(AND(COUNTIF(OFFSET($B2,MAX(2-ROW(),-$H$1),,MIN(1+$H$1,ROW()-1),),$B2)=1,$B2=MAX(OFFSET($B2,MAX(2-ROW(),-$H$1),,MIN(ROW()+$H$1,1+2*$H$1),))),$B2,"")
F2:F12F2=TEXT(A2,"uu:mm")
Click to expand...
Thank you for responding. Nice formula it works. Just need to change the B2 to C2 in the formula in D2. But sometimes when you use a low timeframe like 7 there will be multiple mins or maxes in a row before the next min or max. For instance, it can be 'max min min max' or 'min max max min'. How could I make it so that if there is multiple min or max in a row it would take the lowest for a min and highest for max? Example 3.08 (max), 2.95 (min), 2.77 (min), 2.64 (min), 2.78 (max), would now be just 3.08 (max), 2.64 (min) and 2.78 (max).

I would like that in a separate column to the right.
 
Upvote 0
too difficult with formulas, perhaps with vba and even then the result 'll not always be what you want.
 
Upvote 0
LockSmith
just a trial
VBA Code: 
Sub MyMinMax()
     t = Timer
     Dim arr(1 To 3, 1 To 4), sDir, MinTime As Double
     x = Range("J2").Value
     MinTime = CDbl(TimeSerial(0, Range("J1").Value, 0))
     naam = Array("", "start", "min", "max")
     Application.EnableEvents = False
     sDir = "?"
     Set dict = CreateObject("scripting.dictionary")
     With Sheets("blad3").Range("A1").ListObject

          a = .DataBodyRange.Value2
          k = 5
          b = True
          For i = 1 To UBound(a)
               If b Then
                    b = False                                   'start a new period
                    For j = LBound(arr) To UBound(arr)
                         r = i - 1 - (i = 1)                    'actual row
                         arr(j, 1) = a(r, 1)                    'actual time
                         arr(j, 2) = a(r, 2)                    'actual value
                         arr(j, 3) = r                          'actual row
                         arr(j, 4) = naam(j)                    'row0 is startrow, row1=min, row2=max
                    Next
               End If
               If arr(2, 2) > a(i, 2) Then arr(2, 1) = a(i, 1): arr(2, 2) = a(i, 2): arr(2, 3) = i     'arr(1,1) stores the lowest value until now, arr(i,2) the index
               If arr(3, 2) < a(i, 2) Then arr(3, 1) = a(i, 1): arr(3, 2) = a(i, 2): arr(3, 3) = i     'arr(1,1) stores the lowest value until now, arr(i,2) the index
     '.DataBodyRange.Cells(i, 6) = sDir
     'If i = 202 Then MsgBox "1"

               If i = UBound(a) And 1 Then                      'last record = write both min and max
                    If sDir <> "up" Then dict.Add dict.Count, Application.Index(arr, 2, 0)
                    If sDir <> "down" Then dict.Add dict.Count, Application.Index(arr, 3, 0)
               ElseIf a(i, 2) > arr(2, 2) * (1 + x) And sDir <> "up" And (a(i, 1) - arr(1, 1)) > MinTime Then     'actual value > 105% min = start period "up"
Debug.Print i & "  " & Format(a(i, 1), "hh:mm") & "  " & Format(arr(1, 1), "hh:mm") & " " & Format(MinTime, "hh:mm") & " " & sDir
                    dict.Add dict.Count, Application.Index(arr, 2, 0): sDir = "up": b = True     'add min part
               ElseIf a(i, 2) < arr(3, 2) * (1 - x) And sDir <> "down" And ((a(i, 1) - arr(1, 1)) > MinTime) Then     'actual value < 95% max = start period "down"
Debug.Print i & "  " & Format(a(i, 1), "hh:mm") & "  " & Format(arr(1, 1), "hh:mm") & " " & Format(MinTime, "hh:mm") & " " & sDir
                    dict.Add dict.Count, Application.Index(arr, 3, 0): sDir = "down": b = True     'add max part
               End If
          Next
Debug.Print "done"

          If dict.Count = 1 Then arr1 = Application.Index(dict.items, 0, 0): dict.Add dict.Count, arr1
          If dict.Count Then
               .DataBodyRange.Offset(, k - 1).Resize(, 1).ClearContents
               arr1 = Application.Index(dict.items, 0, 0)
With Range("q1")
.Resize(50, 5).ClearContents
.Resize(UBound(arr1), UBound(arr1, 2)).Value = arr1
End With
              
               For i = 1 To UBound(arr1)
                    .DataBodyRange.Cells(arr1(i, 3), k).Value = arr1(i, 4) & " " & arr1(i, 2)
               Next
          End If
     End With
     Application.EnableEvents = True
     'MsgBox Timer - t
End Sub
 

Attachments

  • Schermafbeelding 2022-03-13 124649.png
    Schermafbeelding 2022-03-13 124649.png
    15.5 KB · Views: 16
Upvote 0
You must log in or register to reply here.

Similar threads

K
  • Question
conditional formatting question
Replies
2
Views
156
kerm007
K
K
  • Question
Excel Pivot table Time Values is not populating same as Source Sheet
Replies
1
Views
310
awoohaw
awoohaw
B
  • Question
Pushing Data from Newly Created Sheets
Replies
1
Views
85
bobaol
B
B
  • Question
Data population after adding sheets
Replies
0
Views
112
BijanBorazjani
B
imback2nite
  • Solved
VBA help.
2
Replies
10
Views
362
Peter_SSs
Peter_SSs

Forum statistics

Threads
1,214,911
Messages
6,122,192
Members
449,072
Latest member
DW Draft

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top