Count Max Open Trades by Date and Pair

[mohammadamjad48]

i am creating a spreadsheet of stock trades.
i have a field for the date the trade was opened and the date the trade was closed.
Is there a way I can calculate the maximum number of trades that were open at one time.
example

SNo D/M/Y D/M/Y
1. open date 1/1/2019 close date 3/1/2019
2. open date 2/1/2019 close date 3/1/2019
3. open date 3/2/2019 close date 4/1/2019



Max Pairs = 10 so the max Count is not Greater then 10
The max number of trades open would be 2 because the Trade 1 is open on 1/1/2019 & close on 3/1/2019 and trade 2 also open on Close with in the time of trade No1 so the Max open in this case = 2 Trades

i want the Count Max Open Orders in one time and its not grater then Max Pair ID.



see the attachment for more detail
the data is huge about 8000 rows

Thank you for any help

Sr#	Pair ID	Created On	Closed On	Duration			Max Trades Open in on time
1​	1​	22-Aug-17 6:00 AM	23-Aug-17 10:30 PM	1.69
2​	2​	2-Sep-17 10:45 AM	6-Sep-17 12:15 AM	3.56		1​	4​
3​	1​	2-Sep-17 11:00 AM	3-Sep-17 2:30 PM	1.15	TRUE​	1​
4​	1​	4-Sep-17 12:30 PM	6-Sep-17 10:00 AM	1.90	TRUE​	1​
5​	2​	5-Sep-17 6:30 AM	6-Sep-17 12:15 AM	0.74	TRUE​	1​
6​	2​	8-Sep-17 6:30 PM	15-Sep-17 7:30 PM	7.04		1​	5​
7​	1​	8-Sep-17 6:45 PM	18-Sep-17 12:00 PM	9.72	TRUE​	1​
8​	2​	14-Sep-17 4:45 PM	15-Sep-17 7:30 PM	1.11	TRUE​	1​
9​	1​	14-Sep-17 10:15 PM	18-Sep-17 12:00 PM	3.57	TRUE​	1​
10​	2​	15-Sep-17 4:30 PM	15-Sep-17 7:30 PM	0.13	TRUE​	1​
11​	2​	20-Sep-17 1:15 AM	20-Sep-17 7:00 PM	0.74	TRUE​	1​
12​	1​	21-Sep-17 8:30 PM	24-Sep-17 2:45 AM	2.26	TRUE​	1​	2​
13​	2​	21-Sep-17 8:45 PM	22-Sep-17 9:45 AM	0.54	TRUE​	1​
14​	2​	29-Sep-17 7:45 AM	29-Sep-17 10:45 PM	0.63
15​	1​	15-Oct-17 1:15 PM	16-Oct-17 9:00 AM	0.82
16​	2​	15-Oct-17 3:15 PM	15-Oct-17 9:45 PM	0.27
17​	2​	2-Nov-17 6:30 AM	4-Nov-17 6:00 AM	1.98
18​	1​	8-Nov-17 11:45 PM	9-Nov-17 1:30 PM	0.57
19​	1​	10-Nov-17 4:30 PM	16-Nov-17 6:15 AM	5.57
20​	2​	10-Nov-17 6:00 PM	12-Nov-17 5:30 AM	1.48
21​	1​	17-Nov-17 12:45 PM	17-Nov-17 4:30 PM	0.16
22​	1​	21-Nov-17 8:45 AM	22-Nov-17 12:45 AM	0.67
23​	4​	28-Nov-17 5:30 PM	28-Nov-17 7:30 PM	0.08
24​	3​	30-Nov-17 12:45 AM	30-Nov-17 9:00 AM	0.34
25​	1​	30-Nov-17 12:45 AM	30-Nov-17 11:15 AM	0.44
26​	2​	30-Nov-17 12:45 AM	2-Dec-17 9:15 AM	2.35
27​	4​	30-Nov-17 12:45 AM	3-Dec-17 11:00 PM	3.93
28​	1​	4-Dec-17 2:45 AM	5-Dec-17 3:15 PM	1.52
29​	2​	15-Dec-17 7:45 AM	16-Dec-17 3:30 AM	0.82
30​	2​	18-Dec-17 6:00 AM	18-Dec-17 7:15 PM	0.55
31​	1​	18-Dec-17 6:15 AM	18-Dec-17 7:00 PM	0.53
32​	1​	19-Dec-17 3:15 PM	26-Dec-17 8:00 PM	7.20
33​	4​	19-Dec-17 3:30 PM	21-Dec-17 11:00 AM	1.81
34​	3​	21-Dec-17 8:15 PM	22-Dec-17 6:45 AM	0.44
35​	2​	21-Dec-17 8:15 PM	22-Dec-17 6:00 AM	0.41
36​	5​	21-Dec-17 8:15 PM	22-Dec-17 6:30 AM	0.43
37​	4​	21-Dec-17 8:15 PM	22-Dec-17 2:30 PM	0.76
38​	1​	22-Dec-17 8:45 AM	26-Dec-17 8:00 PM	4.47
39​	4​	22-Dec-17 12:30 PM	22-Dec-17 2:30 PM	0.08
40​	2​	24-Dec-17 4:45 AM	25-Dec-17 7:00 PM	1.59
41​	4​	24-Dec-17 5:00 AM	25-Dec-17 6:30 PM	1.56
42​	3​	24-Dec-17 5:15 AM	24-Dec-17 6:30 PM	0.55
43​	1​	28-Dec-17 8:00 AM	29-Dec-17 1:15 PM	1.22
44​	2​	28-Dec-17 8:00 AM	28-Dec-17 10:30 PM	0.60
45​	5​	28-Dec-17 8:00 AM	29-Dec-17 1:00 PM	1.21
46​	4​	28-Dec-17 8:00 AM	29-Dec-17 8:45 AM	1.03
47​	2​	30-Dec-17 7:15 AM	1-Jan-18 5:15 AM	1.92
48​	4​	30-Dec-17 7:30 AM	31-Dec-17 7:45 AM	1.01
49​	2​	8-Jan-18 7:45 PM	9-Jan-18 6:00 AM	0.43
50​	1​	8-Jan-18 8:00 PM	9-Jan-18 3:15 AM	0.30
51​	4​	8-Jan-18 8:00 PM	9-Jan-18 1:15 AM	0.22
52​	1​	10-Jan-18 1:30 PM	11-Jan-18 6:45 AM	0.72
53​	1​	11-Jan-18 8:45 AM	12-Jan-18 12:00 AM	0.64
54​	4​	11-Jan-18 9:00 AM	12-Jan-18 12:15 PM	1.14
55​	2​	11-Jan-18 9:30 AM	11-Jan-18 9:45 PM	0.51
56​	1​	14-Jan-18 9:45 AM	16-Jan-18 12:00 AM	1.59
57​	2​	14-Jan-18 5:15 PM	15-Jan-18 7:30 AM	0.59
58​	3​	16-Jan-18 6:45 AM	16-Jan-18 4:45 PM	0.42
59​	1​	16-Jan-18 6:45 AM	17-Jan-18 4:30 AM	0.91
60​	2​	16-Jan-18 6:45 AM	18-Jan-18 5:30 PM	2.45
61​	5​	16-Jan-18 1:00 PM	17-Jan-18 5:15 AM	0.68
62​	3​	16-Jan-18 3:15 PM	16-Jan-18 4:45 PM	0.06
63​	2​	17-Jan-18 2:30 AM	18-Jan-18 5:30 PM	1.63
64​	5​	17-Jan-18 3:00 AM	17-Jan-18 5:15 AM	0.09
65​	1​	17-Jan-18 3:30 AM	17-Jan-18 4:30 AM	0.04
66​	4​	17-Jan-18 3:30 AM	17-Jan-18 7:00 AM	0.15
67​	1​	22-Jan-18 6:30 PM	24-Jan-18 4:30 AM	1.42
68​	2​	22-Jan-18 6:30 PM	24-Jan-18 7:30 PM	2.04
69​	5​	22-Jan-18 6:30 PM	24-Jan-18 4:30 AM	1.42
70​	4​	22-Jan-18 6:30 PM	23-Jan-18 10:00 PM	1.15
71​	3​	26-Jan-18 1:15 PM	27-Jan-18 3:30 AM	0.59
72​	1​	26-Jan-18 1:15 PM	27-Jan-18 3:15 AM	0.58
73​	2​	26-Jan-18 1:15 PM	28-Jan-18 10:15 AM	1.88
74​	4​	26-Jan-18 1:15 PM	27-Jan-18 3:00 AM	0.57
75​	5​	26-Jan-18 1:30 PM	27-Jan-18 4:30 PM	1.13
76​	1​	29-Jan-18 6:30 AM	14-Feb-18 5:15 PM	16.45
77​	2​	30-Jan-18 7:00 AM	30-Jan-18 6:00 PM	0.46
78​	2​	30-Jan-18 11:15 PM	1-Feb-18 1:15 PM	1.58
79​	3​	1-Feb-18 1:45 PM	2-Feb-18 7:00 PM	1.22
80​	4​	1-Feb-18 1:45 PM	2-Feb-18 7:00 PM	1.22
81​	1​	2-Feb-18 12:45 AM	14-Feb-18 5:15 PM	12.69
82​	4​	2-Feb-18 2:15 PM	2-Feb-18 7:00 PM	0.20
83​	3​	2-Feb-18 5:15 PM	2-Feb-18 7:00 PM	0.07
84​	2​	4-Feb-18 5:15 PM	7-Feb-18 1:15 AM	2.33
85​	1​	5-Feb-18 11:00 PM	14-Feb-18 5:15 PM	8.76
86​	2​	6-Feb-18 12:30 AM	7-Feb-18 1:15 AM	1.03
87​	4​	15-Feb-18 3:15 PM	16-Feb-18 8:30 PM	1.22
88​	4​	18-Feb-18 9:00 AM	19-Feb-18 11:00 PM	1.58
89​	2​	18-Feb-18 11:00 AM	18-Feb-18 9:45 PM	0.45
90​	2​	21-Feb-18 3:45 AM	24-Apr-18 6:30 PM	62.61
91​	4​	21-Feb-18 4:00 AM	26-Feb-18 5:30 PM	5.56
92​	3​	21-Feb-18 4:15 AM	26-Feb-18 7:15 PM	5.63
93​	1​	21-Feb-18 4:15 AM	21-Apr-18 8:30 AM	59.18
94​	4​	6-Mar-18 1:30 AM	16-Apr-18 5:30 AM	41.17
95​	3​	6-Mar-18 3:45 PM	13-Mar-18 6:45 PM	7.13
96​	5​	7-Mar-18 10:00 PM	8-Mar-18 4:00 AM	0.25
97​	2​	9-Mar-18 3:45 AM	24-Apr-18 6:30 PM	46.61
98​	4​	9-Mar-18 7:15 AM	16-Apr-18 5:30 AM	37.93
99​	1​	9-Mar-18 9:15 AM	21-Apr-18 8:30 AM	42.97
100​	3​	9-Mar-18 9:30 AM	13-Mar-18 6:45 PM	4.39
101​	4​	15-Mar-18 2:30 AM	16-Apr-18 5:30 AM	32.13
102​	2​	18-Mar-18 12:15 AM	24-Apr-18 6:30 PM	37.76
103​	5​	26-Mar-18 4:30 PM	3-Apr-18 11:45 PM	8.30
104​	5​	30-Mar-18 1:45 AM	3-Apr-18 11:45 PM	4.92
105​	1​	30-Mar-18 5:30 AM	21-Apr-18 8:30 AM	22.13
106​	5​	4-Apr-18 2:45 PM	13-Apr-18 4:00 AM	8.55
107​	4​	6-Apr-18 4:00 PM	16-Apr-18 5:30 AM	9.56
108​	3​	9-Apr-18 3:30 PM	12-Apr-18 5:00 AM	2.56
109​	4​	18-Apr-18 12:15 AM	18-Apr-18 12:30 PM	0.51
110​	4​	21-Apr-18 1:30 PM	23-Apr-18 1:00 PM	1.98
111​	6​	21-Apr-18 3:00 PM	21-Apr-18 10:00 PM	0.29
112​	5​	21-Apr-18 4:15 PM	22-Apr-18 1:45 AM	0.40
113​	6​	25-Apr-18 5:45 AM	27-Apr-18 5:45 PM	2.50
114​	2​	25-Apr-18 9:00 AM	27-Apr-18 3:30 PM	2.27
115​	4​	25-Apr-18 9:00 AM	29-Apr-18 1:45 PM	4.20
116​	5​	25-Apr-18 10:00 AM	29-Apr-18 1:30 PM	4.15
117​	6​	28-Apr-18 1:45 AM	28-Apr-18 6:00 PM	0.68
118​	2​	28-Apr-18 1:45 AM	28-Apr-18 11:00 PM	0.89
119​	3​	29-Apr-18 2:00 PM	30-Apr-18 8:00 AM	0.75
120​	2​	29-Apr-18 2:00 PM	30-Apr-18 9:00 AM	0.79
121​	6​	29-Apr-18 2:15 PM	29-Apr-18 8:30 PM	0.26
122​	6​	1-May-18 5:45 AM	1-May-18 7:45 PM	0.58
123​	3​	1-May-18 5:45 AM	3-May-18 11:00 AM	2.22
124​	1​	1-May-18 5:45 AM	2-May-18 8:15 AM	1.10
125​	2​	1-May-18 5:45 AM	2-May-18 5:15 PM	1.48
126​	4​	1-May-18 5:45 AM	2-May-18 8:30 AM	1.11
127​	6​	6-May-18 11:15 AM	2-Apr-19 12:15 PM	331.04
128​	2​	6-May-18 11:15 AM	15-May-19 9:00 PM	374.41
129​	4​	6-May-18 11:15 AM	22-Jun-19 6:15 PM	412.29
130​	1​	6-May-18 11:30 AM	20-Jul-18 9:45 PM	75.43
131​	6​	11-May-18 12:30 PM	2-Apr-19 12:15 PM	325.99
132​	4​	11-May-18 12:45 PM	22-Jun-19 6:15 PM	407.23
133​	6​	23-May-18 1:00 PM	2-Apr-19 12:15 PM	313.97
134​	2​	23-May-18 8:45 PM	15-May-19 9:00 PM	357.01
135​	1​	24-May-18 1:30 PM	20-Jul-18 9:45 PM	57.34
136​	4​	24-May-18 1:30 PM	22-Jun-19 6:15 PM	394.20
137​	5​	28-May-18 11:00 AM	29-May-18 4:00 PM	1.21
138​	5​	30-May-18 8:00 PM	31-May-18 6:15 PM	0.93
139​	8​	10-Jun-18 6:00 AM	3-Jul-18 8:45 AM	23.11
140​	5​	10-Jun-18 6:00 AM	6-Mar-19 3:45 PM	269.41
141​	3​	10-Jun-18 10:45 PM	11-Jun-18 7:15 PM	0.85
142​	10​	13-Jun-18 12:15 AM	15-Jun-18 6:45 AM	2.27
143​	2​	13-Jun-18 4:30 AM	15-May-19 9:00 PM	336.69
144​	8​	13-Jun-18 2:45 PM	3-Jul-18 8:45 AM	19.75
145​	9​	20-Jun-18 6:30 AM	20-Jun-18 3:30 PM	0.38
146​	7​	22-Jun-18 12:15 PM	2-Jul-18 6:30 PM	10.26
147​	9​	22-Jun-18 12:15 PM	24-Jun-18 10:30 PM	2.43
148​	5​	22-Jun-18 3:00 PM	6-Mar-19 3:45 PM	257.03
149​	6​	23-Jun-18 2:15 AM	2-Apr-19 12:15 PM	283.42
150​	1​	23-Jun-18 2:15 AM	20-Jul-18 9:45 PM	27.81
151​	8​	23-Jun-18 2:15 AM	3-Jul-18 8:45 AM	10.27
152​	4​	23-Jun-18 2:15 AM	22-Jun-19 6:15 PM	364.67
153​	9​	24-Jun-18 10:00 AM	24-Jun-18 10:30 PM	0.52
154​	9​	9-Jul-18 10:15 PM	16-Jul-18 4:30 PM	6.76
155​	7​	10-Jul-18 11:15 AM	16-Jul-18 4:30 PM	6.22
156​	9​	13-Jul-18 3:45 AM	16-Jul-18 4:30 PM	3.53
157​	7​	20-Jul-18 12:00 PM	26-Jul-18 2:00 PM	6.08
158​	1​	25-Jul-18 8:45 PM	26-Jul-18 8:15 AM	0.48
159​	7​	27-Jul-18 2:30 AM	30-Jul-18 1:00 PM	3.44
160​	1​	27-Jul-18 2:45 AM	1-Sep-18 8:15 PM	36.73
161​	10​	27-Jul-18 2:45 AM	27-Jul-18 4:30 PM	0.57
162​	10​	30-Jul-18 10:45 PM	21-Sep-18 1:15 PM	52.60
163​	7​	1-Aug-18 4:30 AM	4-Aug-18 1:30 AM	2.88
164​	8​	3-Aug-18 5:30 AM	3-Aug-18 9:45 AM	0.18
165​	5​	8-Aug-18 1:15 AM	6-Mar-19 3:45 PM	210.60
166​	10​	8-Aug-18 9:15 AM	21-Sep-18 1:15 PM	44.17
167​	4​	8-Aug-18 9:45 PM	22-Jun-19 6:15 PM	317.85
168​	1​	8-Aug-18 11:45 PM	1-Sep-18 8:15 PM	23.85
169​	3​	11-Aug-18 2:15 AM	15-Aug-18 7:30 AM	4.22
170​	7​	11-Aug-18 2:15 AM	15-Aug-18 9:15 PM	4.79
171​	2​	11-Aug-18 5:30 PM	15-May-19 9:00 PM	277.15
172​	9​	11-Aug-18 7:00 PM	11-Aug-18 10:30 PM	0.15
173​	6​	14-Aug-18 6:30 AM	2-Apr-19 12:15 PM	231.24
174​	3​	14-Aug-18 7:00 AM	15-Aug-18 7:30 AM	1.02
175​	7​	14-Aug-18 7:00 AM	15-Aug-18 9:15 PM	1.59
176​	9​	14-Aug-18 7:15 AM	14-Aug-18 1:15 PM	0.25
177​	3​	5-Sep-18 3:00 PM	9-Jan-19 12:30 PM	125.90
178​	1​	5-Sep-18 3:00 PM	15-Oct-18 12:00 PM	39.88
179​	7​	5-Sep-18 3:00 PM	22-Sep-18 6:15 AM	16.64
180​	8​	5-Sep-18 3:00 PM	14-Sep-18 8:00 AM	8.71
181​	9​	5-Sep-18 3:00 PM	7-Sep-18 4:30 AM	1.56

 

[JamesCanale]

I couldn't understand what you meant by maxpairs and maxcount.... And I needed excel365 to do this easily (I hope you have it), but here is a segment of my sheet that calculates the stuff from your 181 records here.

MrExcelPlayground2.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M
1											Max open at once	When that was happening
2	Sr#	Pair ID	Created On	Closed On	Duration			Max Trades Open in on time	8/22/2017 6:00	1	26	8/14/2018 7:30	26
3	1	1	8/22/2017 6:00	8/23/2017 22:30	1.69				8/22/2017 6:15	1		8/14/2018 7:45	26
4	2	2	9/2/2017 10:45	9/6/2017 0:15	3.56		1	4	8/22/2017 6:30	1		8/14/2018 8:00	26
5	3	1	9/2/2017 11:00	9/3/2017 14:30	1.15	TRUE	1		8/22/2017 6:45	1		8/14/2018 8:15	26
6	4	1	9/4/2017 12:30	9/6/2017 10:00	1.9	TRUE	1		8/22/2017 7:00	1		8/14/2018 8:30	26
7	5	2	9/5/2017 6:30	9/6/2017 0:15	0.74	TRUE	1		8/22/2017 7:15	1		8/14/2018 8:45	26
8	6	2	9/8/2017 18:30	9/15/2017 19:30	7.04		1	5	8/22/2017 7:30	1		8/14/2018 9:00	26
9	7	1	9/8/2017 18:45	9/18/2017 12:00	9.72	TRUE	1		8/22/2017 7:45	1		8/14/2018 9:15	26
10	8	2	9/14/2017 16:45	9/15/2017 19:30	1.11	TRUE	1		8/22/2017 8:00	1		8/14/2018 9:30	26
11	9	1	9/14/2017 22:15	9/18/2017 12:00	3.57	TRUE	1		8/22/2017 8:15	1		8/14/2018 9:45	26
12	10	2	9/15/2017 16:30	9/15/2017 19:30	0.13	TRUE	1		8/22/2017 8:30	1		8/14/2018 10:00	26
13	11	2	9/20/2017 1:15	9/20/2017 19:00	0.74	TRUE	1		8/22/2017 8:45	1		8/14/2018 10:15	26
14	12	1	9/21/2017 20:30	9/24/2017 2:45	2.26	TRUE	1	2	8/22/2017 9:00	1		8/14/2018 10:30	26
15	13	2	9/21/2017 20:45	9/22/2017 9:45	0.54	TRUE	1		8/22/2017 9:15	1		8/14/2018 10:45	26
16	14	2	9/29/2017 7:45	9/29/2017 22:45	0.63				8/22/2017 9:30	1		8/14/2018 11:00	26
17	15	1	10/15/2017 13:15	10/16/2017 9:00	0.82				8/22/2017 9:45	1		8/14/2018 11:15	26
18	16	2	10/15/2017 15:15	10/15/2017 21:45	0.27				8/22/2017 10:00	1		8/14/2018 11:30	26
19	17	2	11/2/2017 6:30	11/4/2017 6:00	1.98				8/22/2017 10:15	1		8/14/2018 11:45	26
20	18	1	11/8/2017 23:45	11/9/2017 13:30	0.57				8/22/2017 10:30	1		8/14/2018 12:00	26
21	19	1	11/10/2017 16:30	11/16/2017 6:15	5.57				8/22/2017 10:45	1		8/14/2018 12:15	26
22	20	2	11/10/2017 18:00	11/12/2017 5:30	1.48				8/22/2017 11:00	1		8/14/2018 12:30	26
23	21	1	11/17/2017 12:45	11/17/2017 16:30	0.16				8/22/2017 11:15	1		8/14/2018 12:45	26
24	22	1	11/21/2017 8:45	11/22/2017 0:45	0.67				8/22/2017 11:30	1		8/14/2018 13:00	26
25	23	4	11/28/2017 17:30	11/28/2017 19:30	0.08				8/22/2017 11:45	1		8/14/2018 13:15	26
26	24	3	11/30/2017 0:45	11/30/2017 9:00	0.34				8/22/2017 12:00	1
Sheet42

Cell Formulas
Range	Formula
I2:I64274	I2	=SEQUENCE((MAX(D3:D183)-MIN(C3:C183))*24*4,1,MIN(C3:C183),1/24/4)
K2	K2	=MAX(J2:J64274)
L2:M25	L2	=FILTER(I2:J64274,J2:J64274=K2,"")
J2:J26	J2	=SUMPRODUCT(--(I2>=$C$3:$C$183),--(I2<=$D$3:$D$183))
Dynamic array formulas.

It's set up by going through 15 minute intervals and counting how many trades are open for each 15 minutes from the earliest date to the latest date of your whole set. If everything could be assumed to be just whole days (no time), then this could be done with older versions without too much trouble. I good macro would make quick enough work of it too.