[Feature] Merge unmatched transactions

[lanmaster53]

Verified feature request does not already exist?

• I have searched and found no existing issue

💻

• Would you like to implement this feature?

Pitch: what problem are you trying to solve?

When Actual fails to recognize a transaction as a match, it doesn't provide a way to gracefully merge the transactions. This is a workflow that I encounter very often and is a show-stopper for me as someone that wants to transition to Actual on a permanent basis.

Describe your ideal solution to this problem

When 2 transactions are selected, there should be an option in the actions menu to merge the transactions so that the manual entry is updated with the date from the imported version. Manually updating one of the entries and deleting the other is an unnecessary and cumbersome process for something so simple. All other budgeting software I've used have had this feature.

Teaching and learning

It was intuitive for me to look for that functionality in the actions menu when I had 2 transactions selected. I was quite surprised that the feature didn't exists because it is such a common workflow. Other than add a blurb in the importing section about merging, I don't think much else would be needed. It's an intuitive feature.

[j-f1]

The logic for matching an existing transaction with an updated one is here:

actual/packages/loot-core/src/server/accounts/sync.js

Lines 236 to 331 in cb0ae2d

	export async function reconcileTransactions(acctId, transactions) {
	const hasMatched = new Set();
	const updated = [];
	const added = [];
	let { normalized, payeesToCreate } = await normalizeTransactions(
	transactions,
	acctId,
	);
	// The first pass runs the rules, and preps data for fuzzy matching
	let transactionsStep1 = [];
	for (let { payee_name, trans, subtransactions } of normalized) {
	// Run the rules
	trans = runRules(trans);
	let match = null;
	let fuzzyDataset = null;
	// First, match with an existing transaction's imported_id. This
	// is the highest fidelity match and should always be attempted
	// first.
	if (trans.imported_id) {
	match = await db.first(
	'SELECT * FROM v_transactions WHERE imported_id = ? AND account = ?',
	[trans.imported_id, acctId],
	);
	// TODO: Pending transactions
	if (match) {
	hasMatched.add(match.id);
	}
	}
	// If it didn't match, query data needed for fuzzy matching
	if (!match) {
	// Look 1 day ahead and 4 days back when fuzzy matching. This
	// needs to select all fields that need to be read from the
	// matched transaction. See the final pass below for the needed
	// fields.
	fuzzyDataset = await db.all(
	`SELECT id, date, imported_id, payee, category, notes FROM v_transactions
	WHERE date >= ? AND date <= ? AND amount = ? AND account = ? AND is_child = 0`,
	[
	db.toDateRepr(monthUtils.subDays(trans.date, 4)),
	db.toDateRepr(monthUtils.addDays(trans.date, 1)),
	trans.amount || 0,
	acctId,
	],
	);
	}
	transactionsStep1.push({
	payee_name,
	trans,
	subtransactions,
	match,
	fuzzyDataset,
	});
	}
	// Next, do the fuzzy matching. This first pass matches based on the
	// payee id. We do this in multiple passes so that higher fidelity
	// matching always happens first, i.e. a transaction should match
	// match with low fidelity if a later transaction is going to match
	// the same one with high fidelity.
	let transactionsStep2 = transactionsStep1.map(data => {
	if (!data.match && data.fuzzyDataset) {
	// Try to find one where the payees match.
	let match = data.fuzzyDataset.find(
	row => !hasMatched.has(row.id) && data.trans.payee === row.payee,
	);
	if (match) {
	hasMatched.add(match.id);
	return { ...data, match };
	}
	}
	return data;
	});
	// The final fuzzy matching pass. This is the lowest fidelity
	// matching: it just find the first transaction that hasn't been
	// matched yet. Remember the the dataset only contains transactions
	// around the same date with the same amount.
	let transactionsStep3 = transactionsStep2.map(data => {
	if (!data.match && data.fuzzyDataset) {
	let match = data.fuzzyDataset.find(row => !hasMatched.has(row.id));
	if (match) {
	hasMatched.add(match.id);
	return { ...data, match };
	}
	}
	return data;
	});

It currently looks 1 day after the imported transaction date and 4 days before. Then, it merges any matched transactions using this logic. (existing is the existing transaction, trans is the proposed imported transaction)

actual/packages/loot-core/src/server/accounts/sync.js

Lines 344 to 352 in cb0ae2d

	const updates = {
	date: trans.date,
	imported_id: trans.imported_id || null,
	payee: existing.payee || trans.payee || null,
	category: existing.category || trans.category || null,
	imported_payee: trans.imported_payee || null,
	notes: existing.notes || trans.notes || null,
	cleared: trans.cleared != null ? trans.cleared : true,
	};

While it definitely makes sense to allow running this logic directly somehow, I worry about the way the merge action as currently implemented is non-symmetric and there currently isn’t a concept of “selection order” in Actual as far as I know. Maybe we could interpret the newer one as the freshly imported one? Or only enable the action when you select one “new” (bolded) transaction and one non-“new” transaction? I want to make sure that when we implement this feature it doesn’t end up being confusing.

[lanmaster53]

A couple thoughts.

I don't think I would only show the option based on the condition of "new" and "old". There are too many scenarios where both transactions could end up being seen as "old", and tying functionality to an unstable condition like that seems fragile. The option should show up ANY time amounts match. It's not like enabling the option forces people to use it. It's okay if it's in the menu even when the user doesn't intend to use it for the selection. Restricting it too much will likely just result in someone with an edge case asking you to change it later. It's better to put the power in the users' hands.

I do think the current match window is too small, especially when dealing with checks. It's not unreasonable for someone to wait until they "get around to it" to go to the bank. Plus, mail is unpredictable, etc. Expanding that helps a little, but doesn't solve the problem.

Existing transactions should be the source of truth for metadata because they are most likely manual entry. Imports should be the source of truth for the date because it is actual from the financial institution. This is how YNAB and others do it. If that doesn't provide the desired effect, then it is very easy to undo and manually edit, but I believe this will handle the vast majority of use cases.

[j-f1]

It's okay if it's in the menu even when the user doesn't intend to use it for the selection. Restricting it too much will likely just result in someone with an edge case asking you to change it later. It's better to put the power in the users' hands.

Agreed, that makes sense! I do think we should limit to when exactly 2 transactions are selected though.

Existing transactions should be the source of truth for metadata because they are most likely manual entry. Imports should be the source of truth for the date because it is actual from the financial institution.

I agree! and this is the question: how do you know which transaction is the one that was imported? One way would be checking if one transaction has an imported payee name and the other doesn’t but how could handle cases where both or neither have an imported payee name?

I do think the current match window is too small, especially when dealing with checks. It's not unreasonable for someone to wait until they "get around to it" to go to the bank. Plus, mail is unpredictable, etc. Expanding that helps a little, but doesn't solve the problem.

I wonder if we could continentally expand the match window, since the current matching uses 3 different algorithms, with the final one being just “does the amount and account match?” Maybe we could expand the window for amount+account+payee matches but keep the others as-is? I am worried that if someone has e.g. a weekly subscription, we could accidentally merge separate transactions, though.

[lanmaster53]

I agree! and this is the question: how do you know which transaction is the one that was imported? One way would be checking if one transaction has an imported payee name and the other doesn’t but how could handle cases where both or neither have an imported payee name?

Why not do it by date? Won't the oldest transaction always be the manual entry, and the newest always be the import? I can't think of a reason that I would manually enter a transaction after it had already been imported and then merge them. If I wanted to update a transaction that had already been imported, then I would just edit the existing imported record. No need for a merge. Therefore, logically, the imported transaction should always be the more recent, unless I'm missing a use case.

I wonder if we could continentally expand the match window, since the current matching uses 3 different algorithms, with the final one being just “does the amount and account match?” Maybe we could expand the window for amount+account+payee matches but keep the others as-is? I am worried that if someone has e.g. a weekly subscription, we could accidentally merge separate transactions, though.

I think what I've seen elsewhere is that the time window is much larger, and rather than auto-merge, the interface shows a potential match and the user determines whether or not to merge them or leave them separate.

[j-f1]

Therefore, logically, the imported transaction should always be the more recent, unless I'm missing a use case.

One case where this would not be true is if you manually add a transaction on eg Feb 10th, but the bank records it on Feb 9th. If you import on Feb 20th, you would want to merge the newer transaction into the older one, I think.

That said, we could do a 2-step algorithm: if one transaction was just imported and the other one wasn’t, then update the transaction that was just imported. Otherwise, update the newest transaction. People can always undo the change and manually perform the merge if it doesn’t work correctly.

[j-f1]

I think what I've seen elsewhere is that the time window is much larger, and rather than auto-merge, the interface shows a potential match and the user determines whether or not to merge them or leave them separate.

At least in my use case, I generally get a file with the 50 most recent transactions, which tends to have considerable overlap with the transactions already in my account, so manually approving merges would not be a good experience (although maybe we could only prompt for lower-confidence matches?). That would still introduce another interstitial modal which I’m not a huge fan of, though.

[CHAMLEX]

I think what I've seen elsewhere is that the time window is much larger, and rather than auto-merge, the interface shows a potential match and the user determines whether or not to merge them or leave them separate.

At least in my use case, I generally get a file with the 50 most recent transactions, which tends to have considerable overlap with the transactions already in my account, so manually approving merges would not be a good experience (although maybe we could only prompt for lower-confidence matches?). That would still introduce another interstitial modal which I’m not a huge fan of, though.

Maybe having an option to either auto match or manually match? I do agree the last two things keeping me from using this full time is mobile entry (which is being worked on now) and a way to merge transactions when I import my bank statements so they don't get imported again.

[lanmaster53]

I think what I've seen elsewhere is that the time window is much larger, and rather than auto-merge, the interface shows a potential match and the user determines whether or not to merge them or leave them separate.

At least in my use case, I generally get a file with the 50 most recent transactions, which tends to have considerable overlap with the transactions already in my account, so manually approving merges would not be a good experience (although maybe we could only prompt for lower-confidence matches?). That would still introduce another interstitial modal which I’m not a huge fan of, though.

Very true. Any matches that include a transaction that is already reconciled should be merged without prompt. Does that make sense?

[github-actions]

✨ Thanks for sharing your idea! ✨

This repository is now using lodash style issue management for enhancements. This means enhancement issues will now be closed instead of leaving them open. This doesn’t mean we don’t accept feature requests, though! We will consider implementing ones that receive many upvotes, and we welcome contributions for any feature requests marked as needing votes (just post a comment first so we can help you make a successful contribution).

The enhancement backlog can be found here: https://github.com/actualbudget/actual/issues?q=is%3Aissue+label%3A%22needs+votes%22+sort%3Areactions-%2B1-desc

Don’t forget to upvote the top comment with 👍!

[yoyotogblo]

Therefore, logically, the imported transaction should always be the more recent, unless I'm missing a use case.

One case where this would not be true is if you manually add a transaction on eg Feb 10th, but the bank records it on Feb 9th. If you import on Feb 20th, you would want to merge the newer transaction into the older one, I think.

That said, we could do a 2-step algorithm: if one transaction was just imported and the other one wasn’t, then update the transaction that was just imported. Otherwise, update the newest transaction. People can always undo the change and manually perform the merge if it doesn’t work correctly.

@j-f1 Just weighing in on this...

YNAB explains what they do for the most part here:
https://support.ynab.com/en_us/approving-and-matching-transactions-a-guide-ByYNZaQ1i#:~:text=When%20you%20enter%20a%20transaction,10%20days%20of%20each%20other.

YNAB 4 and 5 use a 10 day window before and 10 day window after the imported transaction date to find potential matches. They both use the manually entered transaction as the truth for date, payee and category (same behavior as in YNAB 4). Also, both YNAB 4 and YNAB 5 use the imported transaction as the truth for amount if manually matching.

When 2 transactions are matched, YNAB4 (and YNAB5) requires the user to either approve the match, delete both transactions, or unmatch the 2 transactions. So action is always needed on the user's side. It's worth noting that newly importing previous transactions don't require matching since those have already been confirmed (so if the import ID matches, no need to approve or disapprove the match).

Screenshot of matched transactions:

Here's a screenshot of how the approval of a match looks:

On a sidenote, in YNAB 4, all newly imported transactions (OFX, CSV etc) need to be approved. I much prefer this to Actual just importing and categorizing and losing visibility and control of what was imported.

Transfers and matches:

• If an account A has a manually entered transfer to account B already in YNAB 4, and then transactions are imported into account A, it works the exact same as above. The manually entered transaction is the source of truth and a match dialog is presented to approve, disapprove or delete both transactions.

• If an account A has an imported transfer to account B already in YNAB 4, and then transactions are imported into account B, the earlier imported transfer into account B (from account A) is used as the source of truth.
  

Manual matches:

• When manually matching, similar to auto matching, YNAB always use the manually entered transaction as the source of truth for the date, payees and category. This is still the case even if the manually entered transaction was entered into YNAB after the imported transaction. However, it uses the imported transaction as the source of truth for the amount (if manually matching 2 transactions with different amounts). To do this, YNAB keeps track of which transactions were imported or manually entered. I imagine Actual can do this with import IDs.

• Even manually matched orders still require approval. Same dialog box as before.

• If manually matching 2 manually entered transactions, I believe YNAB uses the most recently entered transaction as the source of truth for the date, payee and category and uses the older transaction as the source of truth for the amount. The transaction still needs to be approved as usual. However, I'd say using the most recently entered transaction as the source of truth for everything (including the amount) probably makes more sense here.

2 transactions entered

Transaction entered 2nd is always the source of truth for date, payees and categories regardless of transaction date

• Also, there's no window to manually match 2 transactions (so they could be a year apart) if you want.

Hope knowing how YNAB behaves helps. I personally think they have the correct logic figured out and if we can replicate it in Actual, that'd be awesome!

[yoyotogblo]

I was getting excited that someone might have completed this only to come here and see the completely different issue this was closed for. Please reopen this. I use the transfers and it's solves none of the issues listed in this feature request

[lanmaster53]

Why was my comment deleted? As the OP, I was seeking clarification for why the issue was closed.

[youngcw]

Seems like it was a misunderstanding of the request. To be fair I also misunderstood the request originally too and thought that it was exactly what that PR did. Upon better reading, you are correct that this is a different feature than that PR adds. The feature is open again

[carkom]

Why was my comment deleted? As the OP, I was seeking clarification for why the issue was closed.

Thanks @youngcw. Yes I misunderstood the PR. I thought by reseting my mistake and re-opening the issue it would be understood that I incorrectly marked the request. @lanmaster53, I hope you now have the clarification you so desire. My apologies for the mistake.

[lanmaster53]

Why was my comment deleted? As the OP, I was seeking clarification for why the issue was closed.

Thanks @youngcw. Yes I misunderstood the PR. I thought by reseting my mistake and re-opening the issue it would be understood that I incorrectly marked the request. @lanmaster53, I hope you now have the clarification you so desire. My apologies for the mistake.

It is still showing as closed for me.

[carkom]

Why was my comment deleted? As the OP, I was seeking clarification for why the issue was closed.

Thanks @youngcw. Yes I misunderstood the PR. I thought by reseting my mistake and re-opening the issue it would be understood that I incorrectly marked the request. @lanmaster53, I hope you now have the clarification you so desire. My apologies for the mistake.

It is still showing as closed for me.

Yes, it's been closed for months. If you look at the history you'll see I didn't change the open status. All feature requests are kept as closed in this repo. We use labels to mark them as complete or not.

[atgrey24]

Just want to add another voice for this request. I just had multiple imported transactions fail to match existing scheduled transactions. In one case, all fields were identical except the amount was off by $0.01! In the other, it was a couple days and $10 difference. However, even after manually adjusting the scheduled transaction to perfectly match, there was no way to trigger a merge or force Actual to recognize them. The only workaround I could find was to delete the imported transactions, then run bank sync again.

While I would love the ability to select the two transactions and force a match, even the ability to just tigger "search for duplicates" across the board would be an improvement.

I also think the fuzzy logic for matching could use improvement. It should not fail due to a 1 penny difference in amount!

[peterjcarroll]

Just want to add another voice for this request. I just had multiple imported transactions fail to match existing scheduled transactions. In one case, all fields were identical except the amount was off by $0.01! In the other, it was a couple days and $10 difference. However, even after manually adjusting the scheduled transaction to perfectly match, there was no way to trigger a merge or force Actual to recognize them. The only workaround I could find was to delete the imported transactions, then run bank sync again.

While I would love the ability to select the two transactions and force a match, even the ability to just tigger "search for duplicates" across the board would be an improvement.

I also think the fuzzy logic for matching could use improvement. It should not fail due to a 1 penny difference in amount!

+1. Being able to manually merge two transactions into a single transactions is a big part of how I reconciled accounts in YNAB4 and is the only thing I feel is missing in ActualBudget. Especially when recurring transactions amounts can vary.

[dasm]

I'd also like to have a way to manually merge transactions.
Oftentimes, I submit my spending on the date I purchased something, but I might be charged few days (or even weeks) after the date.
I can change dates on initial purchase, but it can affect my entire budget.