Consider the following:

You have an Excel spreadsheet that contains the Trade History of a particular trading account from Binance Exchange.

This file has stored the information in a way that you can know how a particular trade got "FILLED", meaning that a trade of a specific SIDE should have another one of the opposite SIDE, so

• If the first trade for a particular symbol had BUY as the initial SIDE (Entry) then the last trade for that particular symbol must have SELL as the final SIDE (Exit).

• Similarly happens if the first trade had SELL as the initial SIDE (Entry) the last trade must then have BUY as the final SIDE (Exit).

However, there are some tricky stuff in this spreadsheet, here's a sample of it:

As can be seen, the history of trades is sorted from the last one to the to the first one made, it can be assumed that:

• All of the trades got completely FILLED (For every Entry made there's an Exit made)
• Any symbol could have been traded again later on.
• The amount of trades of one side aren't necessarily equal to the amount of trades of the opposite side, for instance in the picture above, the first trade for APEBUSD immediately FILLED the order while its exit trade got distributed in 5 SELL orders.

How could the Excel function COUNTIF be adapted to estimate the total amount of trades made?

In the picture above, assuming there were no more data, the total amount of trades would be 3

CodePudding user response：

The following formula gets you the number of unique Symbols that have at least 1 SELL and 1 BUY Side:

=SUMPRODUCT(
            SIGN(
FREQUENCY(
          IFERROR( 
                  MATCH(Table1[Symbol]&"BUY",Table1[Symbol]&Table1[Side],0)
                  *ISNUMBER(MATCH(Table1[Symbol]&"SELL",Table1[Symbol]&Table1[Side],0)),
                  ""), 
                   
          MATCH(Table1[Symbol]&"BUY",Table1[Symbol]&Table1[Side],0)
          *ISNUMBER(MATCH(Table1[Symbol]&"SELL",Table1[Symbol]&Table1[Side],0)))))

I used match to get the row number of the first matching row of the Symbol with Buy Side if it also has both a Sell Side. The IFERROR is making the error values a blank, so the FREQUENCY formula will not error.

The MATCH part is repeated and wrapped in FREQUENCY to get the count of each unique distinct number. Wrapping that in SIGN gives 1 for each unique distinct count FREQUENCY returned. SUMPRODUCT sums the 1's, so returns the unique distinct count.

I think the SUMPRODUCT makes it unneeded to enter this with ctrl shift enter in older Excel versions. Unable to test that.

The screenshot below shows the arrays this function returns for the MATCH, FREQUENCY and SIGN.

In Office 365 the same logic can be narrowed down to the following:

=LET(sell, UNIQUE(FILTER(Table1[Symbol],Table1[Side]="SELL")),  
     buy,UNIQUE(FILTER(Table1[Symbol],Table1[Side]="BUY")),
SUM(--ISNUMBER(XMATCH(sell,buy))))

CodePudding user response：

As per your requirement:

All of the trades got completely FILLED (For every Entry made there's an Exit made)

It would be enough to count unique pair of elements {Symbol, Side} by row and divide ty two. We can use UNIQUE for that

UNIQUE(B2:C22)

It produces the following output:

GALBUSD     SELL
APEBUSD     SELL
MATICBUSD   SELL
MATICBUSD   BUY
APEBUSD     BUY
GALBUSD     BUY

Each symbol appears in pairs {BUY, SELL}, no we just need to count distinct (unique) symbols from previous result. BUT you need to take into account that there are certain Excel functions that don't accept an array as input argument (first argument), it has to be a range. That is the case of

Therefore the following formula produces an error, because the output of UNIQUE/FILTER is an array, but not a range:

LET(set, FILTER(UNIQUE(B2:C22), {1,0}), COUNTIF(set, UNIQUE(set))) -> ERROR

We need to circumvent this by using a function that doesn't have this constraint. For example we can use MATCH/XMATCH to achieve the same. In cell: E2 we have the following formula (see screenshot below):

=LET(set, FILTER(UNIQUE(B2:C22), {1,0}),
 subset, XMATCH(set, UNIQUE(set)), 
 SUM(FREQUENCY(subset, UNIQUE(subset)))/2)

We use LET function to avoid repeating the same element in the formula. We use the name set for selecting the symbols: FILTER(UNIQUE(B2:C22), {1,0}) (only from the unique rows). The partial result is:

GALBUSD
APEBUSD
MATICBUSD
MATICBUSD
APEBUSD
GALBUSD

We use the name subset for the array that matches the positions of unique element, i.e.: XMATCH(set, UNIQUE(set)), here the partial result:

1
2
3
3
2
1

FREQUENCY(subset, UNIQUE(subset)) calculates the frequency of such positions, the partial result is:

2
2
2
0

Because we should have at least one BUY per SELL or vice versa, we just need to SUM the frequency and divide by two.

Here the output: