Transaction Netting: Multiple Buys/Sells - Looking to minimize number of transactions

[dgarland]

My goal would be to do this systematically through either functions (I doubt this is possible), or through VBA:

I receive instructions for buying or selling in accounts. Once I receive them, I submit official trade tickets for those transactions. When the trades are all one direction, there is no issue - each trade has a separate ticket.

However, when there are buys AND sells I need to incorporate exchanges. Ultimately my goal is to minimize the number of transactions in total.

As an example:
Buy 100 A
Buy 200 B
Sell 300 C

would best be output as two exchanges:
exchange 200 C for B
exchange 100 C for A

I am struggling right now to come up with the logic for this type of process, but have the following so far:

1. determine if trades are one-sided in which case do not generate any exchanges
if they are NOT one sided:
2. determine if any are perfect offsets (if a buy = 100, AND a sell = 100), if so, generate exchange(s) reflecting said match(es) and populate a table
if there are no perfect offsets:
3. start with the largest absolute value, take the smallest opposite value(s) and generate exchanges to the table until the largest value is used up, then proceed to the second largest value until there are no more offsetting trades.

Finally, after all three of the above steps, paste the residual single-direction trades in the table below the exchanges.

I imagine this can be accomplished through VBA, but so far I do not how to approach step #2. Any help in approaching this problem would be much appreciated!

 

[dgarland]

Here is my partial code so far (#1 answered only) - I can likely take care of #2, but #3 is beyond my current level of VBA expertise. Any input even general tips at approaching the problem would be much appreciated!

Sub Populate_Blotter()

Dim Buys
Dim Sells


Buys = Application.SumIf(Range("D4:D20"), ">0")
Sells = Application.SumIf(Range("D4:D20"), "<0")


    If Buys > 0 And Sells < 0 Then
'Code for calculating exchanges:
'2. determine if any are perfect offsets (if a buy = 100, AND a sell = 100), _
 if so, generate exchange(s) reflecting said match(es) and populate a table


'3. start with the largest absolute value, take the smallest opposite value(s) _
 and generate exchanges to the table until the largest value is used up, then _
 proceed to the second largest value until there are no more offsetting trades.




    Else
'Paste Buys & Sells directly into blotter
Range("A4:C20").Copy
Range("A25").PasteSpecial xlPasteValues


    End If
End Sub

 

[dgarland]

Found my solution on my own - putting this here so other people may benefit from it. workbook available here:
https://www.dropbox.com/s/9ox25lw7t2j9h9k/TradeBlotter.xlsm?dl=0