Count Max Open Trades by Date and Pair

M

mohammadamjad48

New Member
Joined
Nov 25, 2015
Messages
2
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 IDCreated OnClosed OnDurationMax Trades Open in on time
1​
1​
22-Aug-17 6:00 AM23-Aug-17 10:30 PM1.69
2​
2​
2-Sep-17 10:45 AM6-Sep-17 12:15 AM3.56
1​
4​
3​
1​
2-Sep-17 11:00 AM3-Sep-17 2:30 PM1.15
TRUE​
1​
4​
1​
4-Sep-17 12:30 PM6-Sep-17 10:00 AM1.90
TRUE​
1​
5​
2​
5-Sep-17 6:30 AM6-Sep-17 12:15 AM0.74
TRUE​
1​
6​
2​
8-Sep-17 6:30 PM15-Sep-17 7:30 PM7.04
1​
5​
7​
1​
8-Sep-17 6:45 PM18-Sep-17 12:00 PM9.72
TRUE​
1​
8​
2​
14-Sep-17 4:45 PM15-Sep-17 7:30 PM1.11
TRUE​
1​
9​
1​
14-Sep-17 10:15 PM18-Sep-17 12:00 PM3.57
TRUE​
1​
10​
2​
15-Sep-17 4:30 PM15-Sep-17 7:30 PM0.13
TRUE​
1​
11​
2​
20-Sep-17 1:15 AM20-Sep-17 7:00 PM0.74
TRUE​
1​
12​
1​
21-Sep-17 8:30 PM24-Sep-17 2:45 AM2.26
TRUE​
1​
2​
13​
2​
21-Sep-17 8:45 PM22-Sep-17 9:45 AM0.54
TRUE​
1​
14​
2​
29-Sep-17 7:45 AM29-Sep-17 10:45 PM0.63
15​
1​
15-Oct-17 1:15 PM16-Oct-17 9:00 AM0.82
16​
2​
15-Oct-17 3:15 PM15-Oct-17 9:45 PM0.27
17​
2​
2-Nov-17 6:30 AM4-Nov-17 6:00 AM1.98
18​
1​
8-Nov-17 11:45 PM9-Nov-17 1:30 PM0.57
19​
1​
10-Nov-17 4:30 PM16-Nov-17 6:15 AM5.57
20​
2​
10-Nov-17 6:00 PM12-Nov-17 5:30 AM1.48
21​
1​
17-Nov-17 12:45 PM17-Nov-17 4:30 PM0.16
22​
1​
21-Nov-17 8:45 AM22-Nov-17 12:45 AM0.67
23​
4​
28-Nov-17 5:30 PM28-Nov-17 7:30 PM0.08
24​
3​
30-Nov-17 12:45 AM30-Nov-17 9:00 AM0.34
25​
1​
30-Nov-17 12:45 AM30-Nov-17 11:15 AM0.44
26​
2​
30-Nov-17 12:45 AM2-Dec-17 9:15 AM2.35
27​
4​
30-Nov-17 12:45 AM3-Dec-17 11:00 PM3.93
28​
1​
4-Dec-17 2:45 AM5-Dec-17 3:15 PM1.52
29​
2​
15-Dec-17 7:45 AM16-Dec-17 3:30 AM0.82
30​
2​
18-Dec-17 6:00 AM18-Dec-17 7:15 PM0.55
31​
1​
18-Dec-17 6:15 AM18-Dec-17 7:00 PM0.53
32​
1​
19-Dec-17 3:15 PM26-Dec-17 8:00 PM7.20
33​
4​
19-Dec-17 3:30 PM21-Dec-17 11:00 AM1.81
34​
3​
21-Dec-17 8:15 PM22-Dec-17 6:45 AM0.44
35​
2​
21-Dec-17 8:15 PM22-Dec-17 6:00 AM0.41
36​
5​
21-Dec-17 8:15 PM22-Dec-17 6:30 AM0.43
37​
4​
21-Dec-17 8:15 PM22-Dec-17 2:30 PM0.76
38​
1​
22-Dec-17 8:45 AM26-Dec-17 8:00 PM4.47
39​
4​
22-Dec-17 12:30 PM22-Dec-17 2:30 PM0.08
40​
2​
24-Dec-17 4:45 AM25-Dec-17 7:00 PM1.59
41​
4​
24-Dec-17 5:00 AM25-Dec-17 6:30 PM1.56
42​
3​
24-Dec-17 5:15 AM24-Dec-17 6:30 PM0.55
43​
1​
28-Dec-17 8:00 AM29-Dec-17 1:15 PM1.22
44​
2​
28-Dec-17 8:00 AM28-Dec-17 10:30 PM0.60
45​
5​
28-Dec-17 8:00 AM29-Dec-17 1:00 PM1.21
46​
4​
28-Dec-17 8:00 AM29-Dec-17 8:45 AM1.03
47​
2​
30-Dec-17 7:15 AM1-Jan-18 5:15 AM1.92
48​
4​
30-Dec-17 7:30 AM31-Dec-17 7:45 AM1.01
49​
2​
8-Jan-18 7:45 PM9-Jan-18 6:00 AM0.43
50​
1​
8-Jan-18 8:00 PM9-Jan-18 3:15 AM0.30
51​
4​
8-Jan-18 8:00 PM9-Jan-18 1:15 AM0.22
52​
1​
10-Jan-18 1:30 PM11-Jan-18 6:45 AM0.72
53​
1​
11-Jan-18 8:45 AM12-Jan-18 12:00 AM0.64
54​
4​
11-Jan-18 9:00 AM12-Jan-18 12:15 PM1.14
55​
2​
11-Jan-18 9:30 AM11-Jan-18 9:45 PM0.51
56​
1​
14-Jan-18 9:45 AM16-Jan-18 12:00 AM1.59
57​
2​
14-Jan-18 5:15 PM15-Jan-18 7:30 AM0.59
58​
3​
16-Jan-18 6:45 AM16-Jan-18 4:45 PM0.42
59​
1​
16-Jan-18 6:45 AM17-Jan-18 4:30 AM0.91
60​
2​
16-Jan-18 6:45 AM18-Jan-18 5:30 PM2.45
61​
5​
16-Jan-18 1:00 PM17-Jan-18 5:15 AM0.68
62​
3​
16-Jan-18 3:15 PM16-Jan-18 4:45 PM0.06
63​
2​
17-Jan-18 2:30 AM18-Jan-18 5:30 PM1.63
64​
5​
17-Jan-18 3:00 AM17-Jan-18 5:15 AM0.09
65​
1​
17-Jan-18 3:30 AM17-Jan-18 4:30 AM0.04
66​
4​
17-Jan-18 3:30 AM17-Jan-18 7:00 AM0.15
67​
1​
22-Jan-18 6:30 PM24-Jan-18 4:30 AM1.42
68​
2​
22-Jan-18 6:30 PM24-Jan-18 7:30 PM2.04
69​
5​
22-Jan-18 6:30 PM24-Jan-18 4:30 AM1.42
70​
4​
22-Jan-18 6:30 PM23-Jan-18 10:00 PM1.15
71​
3​
26-Jan-18 1:15 PM27-Jan-18 3:30 AM0.59
72​
1​
26-Jan-18 1:15 PM27-Jan-18 3:15 AM0.58
73​
2​
26-Jan-18 1:15 PM28-Jan-18 10:15 AM1.88
74​
4​
26-Jan-18 1:15 PM27-Jan-18 3:00 AM0.57
75​
5​
26-Jan-18 1:30 PM27-Jan-18 4:30 PM1.13
76​
1​
29-Jan-18 6:30 AM14-Feb-18 5:15 PM16.45
77​
2​
30-Jan-18 7:00 AM30-Jan-18 6:00 PM0.46
78​
2​
30-Jan-18 11:15 PM1-Feb-18 1:15 PM1.58
79​
3​
1-Feb-18 1:45 PM2-Feb-18 7:00 PM1.22
80​
4​
1-Feb-18 1:45 PM2-Feb-18 7:00 PM1.22
81​
1​
2-Feb-18 12:45 AM14-Feb-18 5:15 PM12.69
82​
4​
2-Feb-18 2:15 PM2-Feb-18 7:00 PM0.20
83​
3​
2-Feb-18 5:15 PM2-Feb-18 7:00 PM0.07
84​
2​
4-Feb-18 5:15 PM7-Feb-18 1:15 AM2.33
85​
1​
5-Feb-18 11:00 PM14-Feb-18 5:15 PM8.76
86​
2​
6-Feb-18 12:30 AM7-Feb-18 1:15 AM1.03
87​
4​
15-Feb-18 3:15 PM16-Feb-18 8:30 PM1.22
88​
4​
18-Feb-18 9:00 AM19-Feb-18 11:00 PM1.58
89​
2​
18-Feb-18 11:00 AM18-Feb-18 9:45 PM0.45
90​
2​
21-Feb-18 3:45 AM24-Apr-18 6:30 PM62.61
91​
4​
21-Feb-18 4:00 AM26-Feb-18 5:30 PM5.56
92​
3​
21-Feb-18 4:15 AM26-Feb-18 7:15 PM5.63
93​
1​
21-Feb-18 4:15 AM21-Apr-18 8:30 AM59.18
94​
4​
6-Mar-18 1:30 AM16-Apr-18 5:30 AM41.17
95​
3​
6-Mar-18 3:45 PM13-Mar-18 6:45 PM7.13
96​
5​
7-Mar-18 10:00 PM8-Mar-18 4:00 AM0.25
97​
2​
9-Mar-18 3:45 AM24-Apr-18 6:30 PM46.61
98​
4​
9-Mar-18 7:15 AM16-Apr-18 5:30 AM37.93
99​
1​
9-Mar-18 9:15 AM21-Apr-18 8:30 AM42.97
100​
3​
9-Mar-18 9:30 AM13-Mar-18 6:45 PM4.39
101​
4​
15-Mar-18 2:30 AM16-Apr-18 5:30 AM32.13
102​
2​
18-Mar-18 12:15 AM24-Apr-18 6:30 PM37.76
103​
5​
26-Mar-18 4:30 PM3-Apr-18 11:45 PM8.30
104​
5​
30-Mar-18 1:45 AM3-Apr-18 11:45 PM4.92
105​
1​
30-Mar-18 5:30 AM21-Apr-18 8:30 AM22.13
106​
5​
4-Apr-18 2:45 PM13-Apr-18 4:00 AM8.55
107​
4​
6-Apr-18 4:00 PM16-Apr-18 5:30 AM9.56
108​
3​
9-Apr-18 3:30 PM12-Apr-18 5:00 AM2.56
109​
4​
18-Apr-18 12:15 AM18-Apr-18 12:30 PM0.51
110​
4​
21-Apr-18 1:30 PM23-Apr-18 1:00 PM1.98
111​
6​
21-Apr-18 3:00 PM21-Apr-18 10:00 PM0.29
112​
5​
21-Apr-18 4:15 PM22-Apr-18 1:45 AM0.40
113​
6​
25-Apr-18 5:45 AM27-Apr-18 5:45 PM2.50
114​
2​
25-Apr-18 9:00 AM27-Apr-18 3:30 PM2.27
115​
4​
25-Apr-18 9:00 AM29-Apr-18 1:45 PM4.20
116​
5​
25-Apr-18 10:00 AM29-Apr-18 1:30 PM4.15
117​
6​
28-Apr-18 1:45 AM28-Apr-18 6:00 PM0.68
118​
2​
28-Apr-18 1:45 AM28-Apr-18 11:00 PM0.89
119​
3​
29-Apr-18 2:00 PM30-Apr-18 8:00 AM0.75
120​
2​
29-Apr-18 2:00 PM30-Apr-18 9:00 AM0.79
121​
6​
29-Apr-18 2:15 PM29-Apr-18 8:30 PM0.26
122​
6​
1-May-18 5:45 AM1-May-18 7:45 PM0.58
123​
3​
1-May-18 5:45 AM3-May-18 11:00 AM2.22
124​
1​
1-May-18 5:45 AM2-May-18 8:15 AM1.10
125​
2​
1-May-18 5:45 AM2-May-18 5:15 PM1.48
126​
4​
1-May-18 5:45 AM2-May-18 8:30 AM1.11
127​
6​
6-May-18 11:15 AM2-Apr-19 12:15 PM331.04
128​
2​
6-May-18 11:15 AM15-May-19 9:00 PM374.41
129​
4​
6-May-18 11:15 AM22-Jun-19 6:15 PM412.29
130​
1​
6-May-18 11:30 AM20-Jul-18 9:45 PM75.43
131​
6​
11-May-18 12:30 PM2-Apr-19 12:15 PM325.99
132​
4​
11-May-18 12:45 PM22-Jun-19 6:15 PM407.23
133​
6​
23-May-18 1:00 PM2-Apr-19 12:15 PM313.97
134​
2​
23-May-18 8:45 PM15-May-19 9:00 PM357.01
135​
1​
24-May-18 1:30 PM20-Jul-18 9:45 PM57.34
136​
4​
24-May-18 1:30 PM22-Jun-19 6:15 PM394.20
137​
5​
28-May-18 11:00 AM29-May-18 4:00 PM1.21
138​
5​
30-May-18 8:00 PM31-May-18 6:15 PM0.93
139​
8​
10-Jun-18 6:00 AM3-Jul-18 8:45 AM23.11
140​
5​
10-Jun-18 6:00 AM6-Mar-19 3:45 PM269.41
141​
3​
10-Jun-18 10:45 PM11-Jun-18 7:15 PM0.85
142​
10​
13-Jun-18 12:15 AM15-Jun-18 6:45 AM2.27
143​
2​
13-Jun-18 4:30 AM15-May-19 9:00 PM336.69
144​
8​
13-Jun-18 2:45 PM3-Jul-18 8:45 AM19.75
145​
9​
20-Jun-18 6:30 AM20-Jun-18 3:30 PM0.38
146​
7​
22-Jun-18 12:15 PM2-Jul-18 6:30 PM10.26
147​
9​
22-Jun-18 12:15 PM24-Jun-18 10:30 PM2.43
148​
5​
22-Jun-18 3:00 PM6-Mar-19 3:45 PM257.03
149​
6​
23-Jun-18 2:15 AM2-Apr-19 12:15 PM283.42
150​
1​
23-Jun-18 2:15 AM20-Jul-18 9:45 PM27.81
151​
8​
23-Jun-18 2:15 AM3-Jul-18 8:45 AM10.27
152​
4​
23-Jun-18 2:15 AM22-Jun-19 6:15 PM364.67
153​
9​
24-Jun-18 10:00 AM24-Jun-18 10:30 PM0.52
154​
9​
9-Jul-18 10:15 PM16-Jul-18 4:30 PM6.76
155​
7​
10-Jul-18 11:15 AM16-Jul-18 4:30 PM6.22
156​
9​
13-Jul-18 3:45 AM16-Jul-18 4:30 PM3.53
157​
7​
20-Jul-18 12:00 PM26-Jul-18 2:00 PM6.08
158​
1​
25-Jul-18 8:45 PM26-Jul-18 8:15 AM0.48
159​
7​
27-Jul-18 2:30 AM30-Jul-18 1:00 PM3.44
160​
1​
27-Jul-18 2:45 AM1-Sep-18 8:15 PM36.73
161​
10​
27-Jul-18 2:45 AM27-Jul-18 4:30 PM0.57
162​
10​
30-Jul-18 10:45 PM21-Sep-18 1:15 PM52.60
163​
7​
1-Aug-18 4:30 AM4-Aug-18 1:30 AM2.88
164​
8​
3-Aug-18 5:30 AM3-Aug-18 9:45 AM0.18
165​
5​
8-Aug-18 1:15 AM6-Mar-19 3:45 PM210.60
166​
10​
8-Aug-18 9:15 AM21-Sep-18 1:15 PM44.17
167​
4​
8-Aug-18 9:45 PM22-Jun-19 6:15 PM317.85
168​
1​
8-Aug-18 11:45 PM1-Sep-18 8:15 PM23.85
169​
3​
11-Aug-18 2:15 AM15-Aug-18 7:30 AM4.22
170​
7​
11-Aug-18 2:15 AM15-Aug-18 9:15 PM4.79
171​
2​
11-Aug-18 5:30 PM15-May-19 9:00 PM277.15
172​
9​
11-Aug-18 7:00 PM11-Aug-18 10:30 PM0.15
173​
6​
14-Aug-18 6:30 AM2-Apr-19 12:15 PM231.24
174​
3​
14-Aug-18 7:00 AM15-Aug-18 7:30 AM1.02
175​
7​
14-Aug-18 7:00 AM15-Aug-18 9:15 PM1.59
176​
9​
14-Aug-18 7:15 AM14-Aug-18 1:15 PM0.25
177​
3​
5-Sep-18 3:00 PM9-Jan-19 12:30 PM125.90
178​
1​
5-Sep-18 3:00 PM15-Oct-18 12:00 PM39.88
179​
7​
5-Sep-18 3:00 PM22-Sep-18 6:15 AM16.64
180​
8​
5-Sep-18 3:00 PM14-Sep-18 8:00 AM8.71
181​
9​
5-Sep-18 3:00 PM7-Sep-18 4:30 AM1.56
 

Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.
Sort by date Sort by votes
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
ABCDEFGHIJKLM
1Max open at onceWhen that was happening
2Sr#Pair IDCreated OnClosed OnDurationMax Trades Open in on time8/22/2017 6:001268/14/2018 7:3026
3118/22/2017 6:008/23/2017 22:301.698/22/2017 6:1518/14/2018 7:4526
4229/2/2017 10:459/6/2017 0:153.56148/22/2017 6:3018/14/2018 8:0026
5319/2/2017 11:009/3/2017 14:301.15TRUE18/22/2017 6:4518/14/2018 8:1526
6419/4/2017 12:309/6/2017 10:001.9TRUE18/22/2017 7:0018/14/2018 8:3026
7529/5/2017 6:309/6/2017 0:150.74TRUE18/22/2017 7:1518/14/2018 8:4526
8629/8/2017 18:309/15/2017 19:307.04158/22/2017 7:3018/14/2018 9:0026
9719/8/2017 18:459/18/2017 12:009.72TRUE18/22/2017 7:4518/14/2018 9:1526
10829/14/2017 16:459/15/2017 19:301.11TRUE18/22/2017 8:0018/14/2018 9:3026
11919/14/2017 22:159/18/2017 12:003.57TRUE18/22/2017 8:1518/14/2018 9:4526
121029/15/2017 16:309/15/2017 19:300.13TRUE18/22/2017 8:3018/14/2018 10:0026
131129/20/2017 1:159/20/2017 19:000.74TRUE18/22/2017 8:4518/14/2018 10:1526
141219/21/2017 20:309/24/2017 2:452.26TRUE128/22/2017 9:0018/14/2018 10:3026
151329/21/2017 20:459/22/2017 9:450.54TRUE18/22/2017 9:1518/14/2018 10:4526
161429/29/2017 7:459/29/2017 22:450.638/22/2017 9:3018/14/2018 11:0026
1715110/15/2017 13:1510/16/2017 9:000.828/22/2017 9:4518/14/2018 11:1526
1816210/15/2017 15:1510/15/2017 21:450.278/22/2017 10:0018/14/2018 11:3026
1917211/2/2017 6:3011/4/2017 6:001.988/22/2017 10:1518/14/2018 11:4526
2018111/8/2017 23:4511/9/2017 13:300.578/22/2017 10:3018/14/2018 12:0026
2119111/10/2017 16:3011/16/2017 6:155.578/22/2017 10:4518/14/2018 12:1526
2220211/10/2017 18:0011/12/2017 5:301.488/22/2017 11:0018/14/2018 12:3026
2321111/17/2017 12:4511/17/2017 16:300.168/22/2017 11:1518/14/2018 12:4526
2422111/21/2017 8:4511/22/2017 0:450.678/22/2017 11:3018/14/2018 13:0026
2523411/28/2017 17:3011/28/2017 19:300.088/22/2017 11:4518/14/2018 13:1526
2624311/30/2017 0:4511/30/2017 9:000.348/22/2017 12:001
Sheet42
Cell Formulas
RangeFormula
I2:I64274I2=SEQUENCE((MAX(D3:D183)-MIN(C3:C183))*24*4,1,MIN(C3:C183),1/24/4)
K2K2=MAX(J2:J64274)
L2:M25L2=FILTER(I2:J64274,J2:J64274=K2,"")
J2:J26J2=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.
 
Upvote 0
You must log in or register to reply here.

Similar threads

C
  • Question Question
TIME LIMITATIONS
Replies
1
Views
68
cooleomter
C
J
  • Question Question
XLOOKUP, Finding a Value based on Time and Date
Replies
1
Views
309
SunnyAlv
SunnyAlv
B
  • Solved
Possible to pull apart 1st section of string for date & time
2 3
Replies
23
Views
450
Peter_SSs
Peter_SSs
A
  • Question Question
Get enddatetime if X no. of working hours is added to startdatetime
Replies
1
Views
438
myall_blues
myall_blues
M
  • Solved
sumifs using date and time values
Replies
9
Views
322
Asbestos_Jen
Asbestos_Jen

Forum statistics

Threads
1,223,893
Messages
6,175,242
Members
452,623
Latest member
russelllowellpercy

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