Search code examples
excelexcel-formulabinance

How to count the number of trades made on a Excel spreadsheet using a custom conditional formula?

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:

Date(UTC) Symbol Side
2022-09-27 10:39:45 GALBUSD SELL
2022-09-27 10:39:44 GALBUSD SELL
2022-09-27 10:39:44 GALBUSD SELL
2022-09-27 10:22:58 APEBUSD SELL
2022-09-27 10:22:24 APEBUSD SELL
2022-09-27 10:22:22 APEBUSD SELL
2022-09-27 10:22:21 APEBUSD SELL
2022-09-27 10:22:21 APEBUSD SELL
2022-09-27 10:15:00 MATICBUSD SELL
2022-09-27 10:14:50 MATICBUSD SELL
2022-09-27 10:14:48 MATICBUSD SELL
2022-09-27 09:55:06 MATICBUSD BUY
2022-09-27 09:55:06 MATICBUSD BUY
2022-09-27 09:55:06 MATICBUSD BUY
2022-09-27 09:55:06 MATICBUSD BUY
2022-09-27 09:55:06 MATICBUSD BUY
2022-09-27 09:51:38 APEBUSD BUY
2022-09-27 09:43:42 GALBUSD BUY
2022-09-27 09:43:34 GALBUSD BUY
2022-09-27 09:43:33 GALBUSD BUY
2022-09-27 09:43:32 GALBUSD BUY

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

Solution

  • 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 COUNTIF:

    documentation of COUNTIF

    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

    Note: The last row of previous result corresponds to the last open bins frequency generated by FREQUENCY function. Since we are creating the bings based on the unique values of subset. The last open bins (values greater than the max unique values of subset) will be always 0. Check FREQUENCY documentation for more information.

    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:

    sample excel file