Group Buy or Sell with corresponding opposite transaction based on like data

Hi All

I am trying to build a workflow where I can pair column E (TRT) BUY and SELL based on the values in column B, C, D, F, and G being exactly the same. For example, I would want to group lines 169 and 133 as they have equal values in all of these conditional columns AND are opposites in column E (one is buy, one is sell). Anything that does not have an opposite transaction (again there should be 1 buy paired/grouped with 1 sell if the criteria columns match) I would just want to kind of separate from the paired groupings. I am not really sure how to start to try to get to this point. If anyone can give me any assistance I would really appreciate it. Thank you so much!

TD	VD	ACCT	SEC	TRT	CP	AMT	MinOfCASH	SumOfPROCEEDS	Price
03-Feb-21	16-Feb-21	505	01F022436	BUY	WF	600,000.00	630,906.25	0	105.05
04-Mar-21	16-Feb-21	505	01F022436	SELL	WF	600,000.00	628,398.44	627773.44	104.63

TBA Sample.xlsx

Developer

Developer Tools

Accepted answers

Luke_C

Hi @jmmart08

Here's a method you could use.

Summarize by the key columns you mentioned and get the count of how many combinations there are for each set of columns.
Join that count back with the original data
Filter on records that have a count of 2 (indicating a pair). Anything in the F anchor does not have a corresponding buy/sell record.
Sort that data by the key columns so they are in the desired presentation

New Workflow8.yxmd

Luke_C

Hi @jmmart08

That's a little trickier depending on the data you want. I modified the attached to bring the price and net money into the output. Since these vary for the records we're collapsing I used the max and min functions within the summarize tool, but you could change to average or some other operator based on your needs. Is this along the lines of what you're looking for? If the solution was helpful please mark it as accepted 🙂

TBA Example.yxmd

Luke_C

Hi @jmmart08

Glad to help! This last part is super straightforward, in an output tool there's an option to leverage a field to name the file or tabs. For excel, choosing the field and selecting 'change file/table name' will result in one tab per value in that field. See attached - just update the file path in the output tool and you should be all set.

TBA Example.yxmd

All comments

Luke_C

Hi @jmmart08

Here's a method you could use.

Summarize by the key columns you mentioned and get the count of how many combinations there are for each set of columns.
Join that count back with the original data
Filter on records that have a count of 2 (indicating a pair). Anything in the F anchor does not have a corresponding buy/sell record.
Sort that data by the key columns so they are in the desired presentation

New Workflow8.yxmd

jmmart08

Hi @Luke_C

This is great. Thank you very much. As I was going through this process, I found other variables that I didn't know existed, and was wondering if I may be able to run these by you as I am having trouble solving for these new variables. There are situations where there are multiple transactions that need to be aggregated together into 1 BUY or SELL based on the criteria discussed in my original post. The piece that needs to be summed is the amount. For example, I have attached a new input file where some of the counts are 3 or 4. I attempted to incorporate these into the summarize tool but I can't seem to get the same result once these pieces are summed together (again using a sum on AMT and grouping by VD, ACCT, SEC, CP, and TRT as Count). I basically have attempted to do an additional summarize tool but I keep losing the same structure where I have buy/sell as 1 to 1 on the exact match portion. So there's a population on my false's that I need to account for that I would like to ultimately figure out how to get on my true side that really do have a 1 to 1 match when say the count is higher than 2 but there are 2 buys that match 1 sell so those come as a 3 count so once the AMT is summed they really are a match. I have attached my workflow that exists now (thanks to your help!!).

Thank you!

TBA Example.yxmd

TBA Sample 2.xlsx

Quick Links

This months top contributors

atcodedog05 19598

Qiu 15867

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703