Feature: Auto match multi-currency transfers

[pbendus]

Discussed in #1850

^{Originally posted by pbendus February 12, 2025}

Describe the feature

Currently, when transferring money between accounts with different currencies (e.g., USD to local currency in Revolut), the app does not recognize these transactions as transfers. Instead, it prompts the user to create a separate transaction manually.

This feature request proposes an enhancement that would allow the app to automatically match multi-currency transfers across accounts. The system should be able to detect linked transactions based on similar timestamps and amounts (after currency conversion) and categorize them as a transfer.

Why is this feature important?

• Helps users properly track money movements between their own accounts without manual intervention.
• Eliminates the need to manually categorize transfer transactions.
• Many users frequently deal with currency conversions in modern banking apps like Revolut, Wise, and others. Auto-matching transactions would make tracking finances much more seamless.

Additional context, screenshots, and relevant links

A potential implementation could match transactions based on exchange rates, timestamps, and amounts within a reasonable variance range.

[kirill-andr]

I have used a workaround for this issue, but I must warn anyone willing to use it to make sure they clearly understand what they are doing and are willing to take the risks. This fix involves working directly with Postgres Database
It is very likely that Maybe developers would not encourage anyone editing the data directly in the backend.

With that in mind, attempt this only if:

• You understand SQL well and worked with RDBMS databases
• Know how to undo what you have done if this will have unintended results
• Willing to take the risks associated with editing the data directly in the DB.

I also do not guarantee this fix as I have not exhaustively examined the code, but it seemed to work for me.

The Logic:
I have deduced the following from looking at the data.

• The transfers table holds the UUIDs of the inbound and outbound transfers when transactions are matched.
• UUIDs relate to the UUIDs in the table account_transactions
• Statuses in the transfers table are set to pending when there is a match and confirmed when users click the tickmark in the UI, confirming the 2 transactions are indeed transfers.

So, what I decided to do with SQL directly in the Postgres database:

1. Find transactions from first account (outflow) in USD. Filtering for transactions that are likely to be transfers.
2. Find transactions from second account (inflow) in EUR. Filtering for transactions that are likely to be transfers.
3. Connect the transactions on date and amount. Because it is USD->EUR, I set the exchange rate manually at 1.088 and I allow the difference between amounts to be +/- 5%. This could cause big problems if you have same amount converted twice in one day, but you can try to match on timestamp too, adding hours and minutes of transactions.
4. Then I insert everything that matched into table transfers
5. I update the statuses using SQL to be confirmed.

Bellow is my example for Revolut. Note that my filter Exchange to EUR is specific to how Revolut identifies FX conversions.

with outflow as (
    SELECT a.name, e.date, e.name, e.amount, t.id, c.name
    FROM account_transactions t
             JOIN account_entries e ON t.id = entryable_id
             JOIN accounts a ON e.account_id=a.id
             LEFT JOIN categories c ON t.category_id = c.id
    WHERE a.name = 'Revolut USD'
    AND e.name='Exchange to EUR'
      AND t.id NOT IN (SELECT DISTINCT inflow_transaction_id FROM transfers) -- exclude transfers already matched
      AND t.id NOT IN (SELECT DISTINCT outflow_transaction_id FROM transfers) -- exclude transfers already matched
)
,inflow as (
    SELECT a.name, e.date, e.name, e.amount, t.id, c.name
         , ROUND(amount * 1.088, 2) amount_converted
                 , ROUND(amount * 1.088, 2) * 0.95 amount_converted_min_limit -- tolerate +/- 5% error on the amount because of FX conversion
                 , ROUND(amount * 1.088, 2) * 1.05 amount_converted_max_limit -- tolerate +/- 5% error on the amount because of FX conversion
    FROM account_transactions t
             JOIN account_entries e ON t.id = entryable_id
             JOIN accounts a ON e.account_id=a.id
             LEFT JOIN categories c ON t.category_id = c.id
    WHERE a.name = 'Revolut EUR'
    AND e.name='Exchange to EUR'
    AND t.id NOT IN (SELECT DISTINCT inflow_transaction_id FROM transfers) -- exclude transfers already matched
    AND t.id NOT IN (SELECT DISTINCT outflow_transaction_id FROM transfers) -- exclude transfers already matched
)
--INSERT INTO transfers(inflow_transaction_id, outflow_transaction_id, status, created_at, updated_at)
SELECT i.id, o.id, 'pending',CURRENT_TIMESTAMP, CURRENT_TIMESTAMP
FROM outflow o
JOIN inflow i ON o.date = i.date
AND o.amount > amount_converted_min_limit * -1
    AND o.amount < amount_converted_max_limit * -1
;