Count Max Open Trades by Date

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



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
see the attachment for more detail
the data is huge about 8000 rows

Thank you for any help

whats the date format DD/MM or MM/DD ?
how do you define a trade as OPEN , why the count of 2
Not clear to me.

Also an sample spreadsheet would help with expected results and WHY
see yellow banner at top of thread

mohammadamjad48

There are newer functions/features that may make this easier. Your profile shows Excel 2007. If it is newer please update your profile.

In the meantime try this in helper column I to get cumulative max counts of trades open at the same time. In cell I3 and filled down to cell I14.

Formula:

Please Login or Register  to view this content.

Then in J3 and filled down to J14 this will return the max counts you indicate.

Formula:

Please Login or Register  to view this content.

See the attached.

This proposal employs ten helper columns, one for each Pair ID.
K1:T1 display the maximum number of trades open and are populated using: =MAX(K3:K200)
K2:T2 are populated with the Pair ID numbers
K3:T183 are populated using: =IF($B3<>K$2,0,SUM(COUNTIFS($B$2:$B2,K$2,$C$2:$C2,"<="&$C3,$D$2:$D2,">="&$C3),COUNTIFS($B4:$B$200,K$2,$C4:$C$200,"<="&$D3,$D4:$D$200,">="&$D3),1))
Let us know if you have any questions.

Originally Posted by mohammadamjad48

...... when i copy the formula for result i see the max Open Count Trades is 55 Nos , but the pairs ID is 1 ~ 10 so the max open in on time is not > then Max Pair ID which is 10 ......

If I understand correctly please try this formula

Formula:

Please Login or Register  to view this content.

I do not understand what you mean here:

when i apply it to 8000 rows it can not copy the formula down and even don't save.

Perhaps if you could manually calculate the result that you would like to see for the first 20 rows of the data in the Count Max Open Trades by Date.xlsx file attached to post #7, then we would have a better idea of the kind of formulas/code that is needed to replicate that result.