Personal Finance from csv and want to automate repetitive transactions into a categories

[J9Boe]

Hi

I am a newbie on airtable and want to start with my personal finance overview.

The csv. export of my bank statement will be used to feed the base. From this I want to put each transaction into categories (to see budgets etc.). Is there a way in airtable that transactions that are repetitive are automatically put into the category that they were put before? For example: If the salary always comes from the same payee airtable should suggest the category 'salary'.

Looking forward to hearing from you.

J

[TheTimeSavingCo]

Try creating a formula field for this; how to set it up depends on what your data looks like though.  As a basic example, if the "Payee" value for salary related transactions is "Google", then you could use a SWITCH like so:

SWITCH(
  Payee,
  "Google", "Salary",
  "Mcdonalds", "Food",
  "Uncategorized"
)

Link to base

[ScottWorld]

@J9Boe 

A formula would not be your best bet for this. You would need to constantly be updating your formula, and it would grow enormously large & unwieldy, possibly even too large for Airtable to handle. It's also not easy to maintain or make changes, since you would have to constantly be scrolling through that formula box.

Your best bet for handling this would be to simply use a linked record field and a lookup field.

Simply create a new table of payees with their categories.

Then, in your original transactions table, create a linked record field that points to the payees table. Then, create a lookup field that looks up the category.

However, regardless of which method you use (the unrecommended formula field, or the recommended lookup field), you're going to have a problem if you want to change the category for one of the transactions, since you can't overwrite lookup fields or formula fields.

So you may need to get automations involved where your lookup field just serves as the "temporary field" for your automation to access, so the automation can then copy & paste that value into a single-select field that you can override on a transaction-by-transaction basis.

Hope this helps! If you’d like to hire an expert Airtable consultant to help you with anything Airtable-related, please feel free to contact me through my website: Airtable consultant — ScottWorld

[TheTimeSavingCo]

Hm, I find the formula pretty easy to manage if it's formatted right, really.  You'll just end up with one line per category, which is the same layout as a new table for it.  Might come down to how comfortable we are with stuff I guess!

One thing to note about using a linked field for this is you might eat into your record count as you're now creating one record per category

A nice thing about using the formulas is that it lets you do general matching instead of direct matches; for example a chain store might have "[STORE NAME] 7151", "[STORE NAME] 2370" for different branches and with a formula you can deal with this with a "FIND()" instead of having a new record for just that store and having to update the record for it

[ScottWorld]

That is true. But outside of that one inconvenience, I feel like there isn’t enough flexibility to be gained by using a formula field.

With categories as a single-select field in their own table, you can easily group by those categories, sort by those categories, create charts & interfaces on those categories, etc.

You can do that to a certain extent with a formula field, but a single-select field is easier & more implemented throughout Airtable (even with colors & certain views). 

But I suppose that there are pros & cons to each method. There’s no 100% perfect way to do this in Airtable.

It would really be best to use an incredible personal finance app like Quicken, which is designed from the ground up to do all of this… and download transactions directly from the banks themselves!

- ScottWorld, Expert Airtable Consultant 

[Alexey_Gusev]

You can't do general matching by FIND() when using SWITCH 😁
But indeed I have such need from time to time, and I using both ways, depending on how much pairs I have and how often I need to add more match pairs. Formula used where table of matches is 'almost static'.

Recently, I had a case with transaction categories and the single-select category type brought additional headache, when trying to add more options by automation script.... well you know how it was solved

[TheTimeSavingCo]

re: You can't do general matching by FIND() when using SWITCH 😁

Ha yeah, I really wish they'd let us though!  For those we'd just put a IF right after

SWITCH(
  Payee,
  "Google", "Salary",
  "KFC", "Food"
) &
IF(FIND("Mcdonalds", Payee), "Food")

 

[ScottWorld]

Here’s my best recommendation for @J9Boe, which is designed specifically for these purposes: Quicken Personal Finances

- ScottWorld, Expert Airtable Consultant