Home / Questions / 560613
Accepted
NoahVerner
Asked: 2022-10-06 05:56:13 +0800 CST 2022-10-06 05:56:13 +0800 CST

How to count the number of operations (trades) made in an Excel spreadsheet using a custom conditional formula?

  • 772

Consider the following:

You have an Excel spreadsheet containing the "Trading History" of a particular "Binance Exchange" trading account.

This file has stored the information so that you can know how a particular operation is done "LLENÓ", which means that a SIDEspecific operation must have another SIDE opposite operation , so

  • If the first trade for a particular symbol had BUYa SIDEstart (In), then the last trade for that particular symbol must have SELLa SIDEfinish (Out).

  • Similarly, if the first operation had SELLas SIDEstart (In), the last operation must have BUYas SIDEend (Out).

However, there are some tricky things in this spreadsheet, here is a sample:

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 operations is ordered from the last to the first carried out, it can be assumed that:

  • All trades were completely 'FILLED' (for each input made, one output is made)
  • Any symbol could have been traded again later.
  • The number of trades from one side is not necessarily equal to the number of trades from the opposite side, for example, in the image above, the first trade for 'APEBUSD' immediately 'FILLED' the order while its exit trade was distributed in 5 orders SELL.

How could the Excel function be adapted COUNTIFto estimate the total number of operations performed?

In the image above, assuming there was no more data, the total amount of exchanges would be 3 , this 3 I calculate as follows:

The first record of a symbol with a specific side and the last record with an opposite side count as 1, so since there are 3 pairs, under those conditions, there would be 3 trades made.

excel

1 Answers

  1. Best Answer

    Honestly, I can't think of any way to do it in a single formula, but with a summary table:

    enter image description here

    SymbolYou can only automate the column if you have Excel365 with the formula =UNICOS(B2:B22). If your data frequently changes size and you don't have Excel 365 you will have to create the list by hand (copy and paste and then remove duplicates, it takes seconds) or automate it with VBA.

    Well, the following formula works in any version of Excel. Cell I2 has the following formula:

    =CONTAR.SI.CONJUNTO($B$2:$B$22;$H2;$C$2:$C$22;I$1)

    This formula is manually dragged to cell J2 and then dragged to the bottom. Count how many SELL/BUY trades there are for each SYMBOL. For example, there are 3 SELL rows for GALBUSD.

    The logic here is that the full number of pairs will be the lower of the two Side types. That is, for example GALBUSD has 3 SELL and 4 BUY. Complete pairs (that is, you can pair a SELL with a BUY) there are only 3, one of the BUYs is left without a pair.

    The PAIRS column is the one that simply indicates with a 1 if there is a pair or not. The formula is:

    =MIN(1;I2:J2)

    And drag down.

    You extract the lowest value between the number 1 and the count of SELL/BUY operations of each symbol. The logic is simple. Remember that, as explained above, the smallest number in the previous 2 columns meant the number of complete pairs that exist. Well, we extract the smallest number between 1 and the number of complete pairs. Why 1? Because you, once there are full pairs, you count it as 1, regardless of whether you have 1, 3 or 25000. You want it to count as 1, hence the minimum. If there are no complete pairs, at least one of the previous two columns will be 0, so this formula will return 0 instead of 1, because there is no complete pair for that Symbol.

    Then you just add at the bottom and voila.

    Broken down sucks but I haven't found a way to a single formula. Honestly, I think it's better this way because with a single glance you have what you want and you also have located which symbols do not have a complete pair, or how many complete pairs there are of each symbol.

    All the best.

    • 1