How to Link/ Associate Bank Payment to CC Transfer transactions?

New to Tiller: I understand Credit Card payments are classified as “transfers”, but how do you LINK or ASSOCIATE the 2 transactions together - ie. bank payment to credit card?
Is there a way to do that?

TIA! :slight_smile:

What I do is setup AutoCat to alter the description of the transactions to make it more apparent. Descriptions like “Payment to American Express” or “Payment from My Bank” help. I also use conditional formatting to turn transactions with category ‘Transfer’ to yellow so they stand out. I then manually add a tag ‘Match’ to each side of the transfer so I know they are both accounted for. The conditional formatting then turns off when the ‘Match’ tag is present. This isn’t really a ‘link’ (not sure what a ‘link’ would actually be) but it is a way to make sure both sides of the transfer are present.

4 Likes

Makes sense. There really is no reason to pair-link them other than reconciliation.
Quickbooks, does allow you to actually pair-match-link them for reference, so was curious how others handle it here.
I know search will always be a good way to find pairs, thought there may be some “trick” I may have missed. thx!

Sharing example - in QBO, 2 dependent transactions are linked together (Bank → Credit Card).
In Tiller, we categorize them as “Transfers”, and I guess we have to “hack” an association/ link together. Anyone come up with something else?

@naeemjuttonline

I am old school and not that smart. I do not use autocat. I like to manually categorize (and therefore touch) each transaction. Here is what I do.

I pay my $1,000 credit card bill from my checking account. I am expecting two transactions. One, the money [-1,000] going out of my checking account. Two, the money [+1,000] coming into my credit card account. When I see the first hit the transactions tab, I categorize it as transfer/credit card. I highlight it yellow as a reminder that I am waiting for the other offsetting transaction to hit. Once the second transaction hits, I categorize it as transfer/credit card and remove the yellow highlighting from the first transaction since both are now accounted for and net to zero. It is simple (albeit manual) and requires two seconds of additional time. It works for me.

Cheers,

1 Like
  1. Categorize the payment out of your account as “Debt Servicing”.
  2. Categorize the card payment coming into the CC account as “Payment Transfer In”.
  3. Set the “Payment Transfer In” category as a utility GROUP excluded from reports.
2 Likes

There is not way to do it similar to how QBO does where stuff is actually linked (that I know of). I think what jpfieber stated is a good way to do it or similar to DanielTanguay, you could come up with very specific transfer category types instead of just “Transfer” or just “Credit Card Payment” - like “AMEX Blue CC Payment” for both the checking withdrawal and CC deposit side. Then have a running Pivot Table Report that shows all of these “Transfer” type categories and they Should Net $0 in Each Category and as a Group. If not, then you know to investigate. This would also make it easier to look up specifics of each transfer.

I also add a tag called “matched” when both sides of a transfer have occurred.

I run a QUERY() of all transactions where the Type is Transfer and the tag column is empty. Something like =IFERROR(QUERY( [range of your transaction sheet] , SELECT [columns you want to view] WHERE [type column letter] = ‘Transfer’ AND [tag column letter] = ‘’ ORDER BY [date column letter]), IFERROR(1/0))

This lists all the unmatched transfers. A handy thing to have.

I also have a conditional format rule applied to the Transaction sheet that turns any unmatched transfer transaction row yellow.
The rule uses the Custom formula:
=AND($O2="Transfer",$H2<>"matched")

O is my Type column and H is my Tags column.

If you want a method to track linked Transfers, you could use a unique tag for each linked Transfer. Or perhaps even better, created a new column in your Transaction sheet called “Link ID” or something like that. When you have a linked transfer, give the first one the number 1 for both transactions. Then continue with 2,3,4 etc on the next sets for linked transfers.

Searching for a number will locate both of the transactions.

You could also get the highest Link ID value using the formula MAX(Z2:Z), if the column used is column Z. Adjust the letter as needed. That way you know what number to use for the next one. You could add that number to the top row using ="Link ID - next "&MAX(Z2:Z)

Jon

2 Likes

I would like to apply your strategy on my sheet but I’m missing some steps. Do you mind explaining in detail how you set up the Conditional Formatting? I would really appreciate it!
Thanks Kindly

My Category column is D, and my Tags column is F.
I use AutoCat to change the Category of my common bank transfers to either “Transfer In” or “Transfer Out”, usually based on words in the “Full Description”, or sometimes on the “Amount Polarity” (ie if it’s positive or negative).
I create a Conditional formating rule with the range set to cover your entire sheet, in my case A2:V7947
In the rule, I choose “Custom formula is” and enter the following (you should be able to copy paste it as is, or you can remove the extra tabs and jam it all together on one line):

=OR(
	AND(
		$D2="Transfer In",
		NOT(REGEXMATCH($F2,"Match"))
	),
	AND(
		$D2="Transfer Out",
		NOT(REGEXMATCH($F2,"Match"))
	)
)

I then choose my font color to be bright yellow, which stands out against the dark grey background that I use.

Now, when a transaction has the category of “Transfer In” or “Transfer Out”, and it doesn’t have a tag named “Match” (you can use multiple tags separated by commas and this will still work), the rule will apply and the entire line will turn yellow. Once you enter the tag “Match”, the formatting will return to normal. I wait for both sides of the transfer to appear in my transactions, and once they’re both there, I mark them both with the “Match” tag.

Hope this helps!

Thank you so much. I appreciate it!