Count Max Open Trades by Date and Pair

mohammadamjad48 · Jul 8, 2021

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 · Jul 8, 2021

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

26

3

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

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

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))244,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.

Count Max Open Trades by Date and Pair

mohammadamjad48

New Member

Excel Facts

JamesCanale

Well-known Member

Similar threads

Forum statistics

Share this page

Count Max Open Trades by Date and Pair

mohammadamjad48

New Member

Excel Facts

JamesCanale

Well-known Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock