Auto Categorize Deposits by Specific Amounts

Google Sheets
, ,
1

Is there a way to Categorize deposits into main bank account from 2 different persons, where we deposit by specific $$ amount? For example: my partner and I freelance and get paid separately but each deposit 20% into our shared bank account. We differentiate who makes a deposit by depositing $xxx.00 and the other person does $xxx.01 this way we can track who is contributing what into the overall account. Currently, in Tiller it only shows up as an ACH deposit but I would like to categorize so we can tell who made the deposit. Can this be done in AutoCat or just manually all the time?

2

Yeah, totally possible. Create a column in AutoCat called ‘Amount’ and put one of the amounts in. Then put in the Description what you’d like transactions with that amount to be called. Create another rule with the other amount and that description. The problem is if a transaction of the same amount shows up for a different thing, it will get the incorrect description. To fix that, you could add another column, like “Account” and then put which account the amount will be in, so the rule is more specific.

1 Like
3

Thank you for the prompt response. I am not sure if this will work, because the amounts added to the account are usually not the same. Some weeks we may deposit $302.00 and $302.01 respectively, and then following week it may be $414.00 and $414.01… and sometimes when we work for different clients it can be very different $278.00 and 635.01. We use the “pennies” to help us sort through because bank just classifies it as ACH deposit but we would like to know so we make sure each is contributing equally (or close enough). I am thinking some code that is "if amount is $xxxx.00 then classify as “‘Person 1 deposit’”. If amount is “$xxxx.01 then classify as ‘Person 2 deposit’.” Then I think add your account column so any future same amount is not misclassified. Does this make sense?

4

I think you’d have to create an “Amount Regex” column and use a regex string to determine between the two. I’m not a regex expert, but I’d thing something like ^(\d+.01)$ and ^(\d+.00)$ could work. You might also add “Amount Min” and “Amount Max” columns to narrow the rules down to only apply to transactions within a certain range of values.

1 Like
5

I don’t even know what “Regex” is :rofl: so that is probably not an option for me, but I will see if there is a help desk or thread for that. Thanks for your time and help.

1 Like
6

Since you specified you want to be able to categorize them, why not just make two categories? I have personally separated out a bunch of things like this, as I am a consultant who charges expenses back to my company after I have already made the purchase, so have made copies of a number of categories to separate the work and personal varieties.

7

Sam: Were you able to make two categories, based on a specific amount, but amount changes? So the issue is would like my deposits and my partners deposits to be auto-categorized. Right now we do it so that when we deposit it we either do $xxx.00 and $xxx.01 to tell who deposited what, but the $xxx is never the same. I am not worry about confusing with other similar amounts because these are the only “inbound” items to our account…so if auto-cat already knows its a deposit I just need to specify which person sent it in based on the “cents”…make sense? Can it do that?

8

This is a really interesting problem, @apacekw. I like what you are trying to do.

I want Autocat to be able to solve for this scenario. I also really wanted @jpfieber’s very clever solution to work. Unfortunately, I don’t think it quite does… though it’s my fault… not his.

When I tried @jpfieber’s solution, I got this error:

Regex failed: “cellValue.match is not a function”

I checked the code and the issue is that regex filters only work on string fields. Essentially, regex-match is “not a function” of a number. At some point, we can consider updating the Autocat processor to try converting numbers to strings whenever a regex filter is applied to them (:wave: @heather) since this would unlock some powerful Autocat solutions & workarounds.

All this said, the problem is more of an issue with the exact way the Autocat regex filters are implemented in Google Sheets than with the idea conceptually… so, I’m curious if this approach works in Excel (which uses a different Autocat engine). Sadly, I don’t have time to check that tonight. :sleeping:

9

I admit, I haven’t made any autocat rules based on amounts. Perhaps you could do it via descriptions? I imagine the transaction description would be different coming from your account vs partner’s account? With my own accounts, when I transfer money between accounts, as I use phrases to help me remember what the transfers were for (when looking at the banking statements). These are then pulled into Tiller, so I can use them for autocat (I use phrases like ‘savings’ or ‘mum xmas’ etc).

10

I am new to Tiller, and well have no clue what all this means…but very much appreciate it your looking into it. We are two freelancers in a partnership, so it just might help others in a similar situation where you have money coming from different “teams” from different clients so we can track inflows and outflows. Thanks again for looking into it. For now, I can just manually go in and track and assign to the correct category.

11

Have you looked at the Autocat tab at all? You can specify by the description as pulled through the Transactions. Example of mine:
image

So all you would have to do would be identify part of the description from yours and your partner’s account that is unique to the situation at hand.

12

To @sam.benito’s point, @apacekw, AutoCat filters are all AND-ed together meaning they must all be met to apply the override rules. So you could use several filters like “Description Contains” and “Account Contains” and “Amount Min” and “Amount Max” to really bracket the transaction metadata that you are trying to categorize.

13

Exciting news, everyone… especially @apacekw!

Our team took your request as a challenge and updated AutoCat to work with the Amount column based on @jpfieber’s crazy idea to use a regex.

The code has been live for 15 minutes… just make sure to refresh the browser tab to fetch it.

Here is what you need to do to get this to work @apacekw:

  1. In your AutoCat sheet, add a new column called “Amount Regex” - this creates a Regex-type filter on the Amount column.
  2. Create a rule that applies the appropriate categorization for the one of you that uses amount $xx.00. In the “Amount Regex” column, add this text “\.00$” (ends with $.00). I highly recommend adding a secondary filter (e.g. “Account Contains” for the account you fund from) or else any transaction ending in $xx.00 will receive this categorization.
  3. Now, redo step 2 for the other partner but use this regex text: “\.01$” (ends with $.01)
  4. Run AutoCat.

Thanks for sharing this challenge.
Let us know if this works!
Randy

3 Likes