Combinations to match FX trades

Question

   Buy Currency Sell Currency Buy Amount Sell Amount
1. CHF          USD           50000      61000
2. USD          CHF           20999      20000     
3. USD          CHF           34000      30000
4. CHF          AUD           400        356
5. AUD          CHF           378        400
6. USD          CHF           34000      30000

Hi, I am trying to solve a problem of matching FX trades. As seen above, the first line buy CHF 50000, can be reconciled by combining 2nd and 3rd, or 2nd and 6th trade. Trade 4 can be reconciled with trade 5. Hope you guys get the idea.

What I've worked out so far:

try to take the unique currencies in buy column and check if they are present in sell column. [i for i in df['Buy Currency'].unique() if i in df['Sell Currency'].unique()]
check if Buy only has 1 type of currency. if true, continue to next loop. assert len(set(df['Buy Currency'])) != 1
check for exact match in Buy Amt and Sell Amt. In this case, 400 appears in both columns thus can be easily found. [amt for amt in df['Buy Amount'].astype(int).tolist() if amt in df['Sell Amount'].astype(int).tolist()]

The part where I'm stuck is matching 1 buy trade with more than 1 sell trade. In addition, there can be more than 1 way of doing so, as illustrated in the first trade. Also tried using itertools.combinations as seen in Find all combinations of a list of numbers with a given sum, but I won't be able to verify if the combinations are same FX pairs.

Any help is greatly appreciated!

score 0 · Answer 1 · answered Oct 09 '20 at 13:10

The solution is to populate some list with all the valid combinations of FX pairs. Then you can use an existing method to find all combinations of a list of numbers with a given sum.

For example, if you select trade #1:

Grab the sell currency, USD
Populate a list of all trades where buy currency is USD
Find the combinations of that list that sum to our original sell currency

MaPy · Accepted Answer · 2020-10-09T13:48:40.527

you can groupby and merge the dataframe with itself on Buy ccy with the Sell ccy.

If you want exact match (i.e. and not greater) you can use the following snippet:

from itertools import chain, combinations


def match(row):
    res1 = []
    res2 = []
    def all_subsets(ss):
        return chain(*map(lambda x: combinations(ss, x), range(0, len(ss) + 1)))
    for subset in all_subsets(row[0]):
        res1.append(sum(subset))
    for subset in all_subsets(row[1]):
        res2.append(sum(subset))
    common = set(res1) & set(res2)
    return common
    

df_buy = df.groupby('Buy Currency')['Buy Amount'].unique().reset_index()
df_sell = df.groupby('Sell Currency')['Sell Amount'].unique().reset_index()
results = df_buy.merge(df_sell, left_on='Buy Currency', right_on='Sell Currency', how='outer')
results['match'] = results[[u'Buy Amount', u'Sell Amount']].apply(match, axis=1)

The output will be:

Buy Currency      Buy Amount Sell Currency          Sell Amount  \
0          AUD           [378]           AUD                [356]   
1          CHF    [50000, 400]           CHF  [20000, 30000, 400]   
2          USD  [20999, 34000]           USD              [61000]   
                    match  
0                     {0}  
1  {0, 50000, 400, 50400}  
2                     {0}

Where match column will give you the possible exact matches, 0 means there is no exact match.

Combinations to match FX trades

2 Answers2