Auto-categorization

[iffy]

No description provided.

[iffy]

Imported from https://trello.com/c/fi1WLdoc

[iffy]

Some would like automatic categorization and some would like explicit rules (and some would like a combination)

[AAverin]

+1 to the topic
Maybe we could write regex rules to assign categories?
YNAB does this automatically – I think it remembers which description/memo of the transaction went to which category and after it can deduce the rule – it assigns category automatically.

[iffy]

@AAverin Yeah, I could allow regex for advanced users. When I get to this I'm going to use:

• Full text searching (a prior test version had this and it worked very well)
• Simple string matching
• Regex/pattern matching
• Maybe SQL matching (e.g. "name in ('Foo', 'Bar') and amount > 500")

[AAverin]

Any update on the issue?
It takes a lot of time to manually put all transactions into buckets while most of them could be placed automatically.

[iffy]

The best I can do is point to the roadmap: https://github.com/buckets/application/projects/2 After bug fixes, this is pretty high up on the list.

[WMP]

This is my script to automatical add transactions to categories:

#!/usr/bin/python3

import yaml
import sqlite3

file = open('auto_categorize.yml', 'r')
cfg = yaml.load(file, Loader=yaml.FullLoader)

def get_scalar_result(conn, sql):
    cursor=conn.cursor()
    cursor.execute(sql)

    return cursor.fetchone()[0]




conn = sqlite3.connect(cfg['sqlite']['path'])

for keyword in cfg['income']:
  cur = conn.cursor()
  cur.execute("SELECT * FROM account_transaction WHERE id NOT IN (SELECT account_trans_id FROM bucket_transaction WHERE account_trans_id IS NOT NULL) and memo LIKE \"%"+keyword+"%\" and general_cat is ''")
  rows = cur.fetchall()
  
  print ("Found transactions for keyword: {}".format(keyword))
  for row in rows:
    print ("{}\t{}\t{}".format(row[2], float(row[4])/100, row[5]))
    press = input('Press ENTER to add to category "income", or other key to skip: ')
    if press == "":
      sql = 'UPDATE account_transaction set general_cat = "income" where id = ?'
      cur = conn.cursor()
      cur.execute(sql, (row[0],))
      conn.commit()
  print

for category in cfg['category']:
    category_id = get_scalar_result(conn, "SELECT id from bucket WHERE name is \""+category+"\"")
    if not category_id:
      print ("ERROR: Cannot found cattegory '{}' in database!".format(category))
    else:
      for keyword in cfg['category'][category]:
        cur = conn.cursor()
        cur.execute("SELECT * FROM account_transaction WHERE id NOT IN (SELECT account_trans_id FROM bucket_transaction WHERE account_trans_id IS NOT NULL) and memo LIKE \"%"+keyword+"%\"")
        rows = cur.fetchall()
        
        print ("Found transactions for keyword: {}".format(keyword))
        for row in rows:
          print ("{}\t{}\t{}".format(row[2], float(row[4])/100, row[5]))
          press = input('Press ENTER to add to category "{}", or other key to skip: '.format(category))
          if press == "":
            sql = "INSERT INTO bucket_transaction(posted,bucket_id,amount,memo,account_trans_id,linked_trans_id) VALUES( ?,?,?,?,?,?)"
            cur = conn.cursor()
            cur.execute(sql, (row[2],category_id,row[4],row[5],row[0],0))
            conn.commit()
        print

And configuration auto_categorize.yml:

sqlite:
  path: "/home/majanows/Dropbox/My Budget.buckets"

# This is special cattegory, defined in Buckets
income:
  - MY WORK
  - MY SECOND WORK
  
category:
  Jedzenie:
    - JMP S.A. BIEDRONKA 
    - Sklep Male Delikatesy
    - Stokrotka
    - LIDL
    - E LECLERC
  Jedzenie poza domem:
    - KFC
    - pyszne.pl
    - Zascianek Restauracja
    - PIRI PIRI KEBAB
    - Karczma Pod Strzecha
  Kredyt:
    - '98102031470000819600447011'
  Raty szafy:
    - 46 2120 0001 0001 8200 0054 9909  
  

[ctheune]

Coming from nYNAB I have to say that I'm actually quite relaxed not having auto categorization. Why? If the categorization has sufficient usability (like just typing a few letters that appear somewhere in the category than having to scroll and click and maybe having multi-select) then I have to fight less against an incorrect auto categorization.

nYNAB tended to incorrectly assign a number of particular cases that I have a lot of (like 30% of all new transactions that I import via CSV). I move money for my house (separate account and budget) a lot and I get income payments and expenditures from my and my wife's work all the time and stuff like Amazon and Paypal typically get mis-classified.

So, making an auto-categorization feature, I'd recommend considering how much auto-categorization causes one to having to watch very closely whether a given category is correct and also making the rules easy to edit. nYNAB makes this really hard, so it's been a problem all the time that I just had to live with (papercuts and such).

[gitchap]

I agree that while I miss the auto categorization a little, it's not all that different than having to correct half of the entries.

I do think ultimately some kind of auto categorization is helpful -- maybe with options to ignore various payees (ie. Amazon).

I'm not minding the manual exercise, but there are some pain points:

1. Not being able to bulk categorize, and
2. Awkward category search

I see at lest #2 is being addressed, so that's great!

While I'm at it -- it would be wonderful to see the bucket balance next to the bucket name when I'm selecting it for the transaction. Just to serve as a reminder, and so I can see right away if I've overspent and need to make adjustments.

[iffy]

@gitchap Good idea on the balance in the dropdown. I've filed this: #676

Bulk/mass editing is #311
And what improvements would you like to see in search? Are you looking for #153 or something else?

[gitchap]

@iffy Yes, #153 is exactly what I'm looking for.

[iffy]

Another comment describing what people want:

I have see two types of auto categorizations in other apps - (1) where the app learns from my manual categorization and will identify a similar transaction in future, (2) based on the merchant and the memo/notes/description of the transaction, the app assigns a default category which can be manually modified

[mscipio]

I love the concept of this app, but this missing feature is quite big. I would love to have something like auto categorization with a rule based approach similar to what Tiller Money does on Google Sheet: a table of rules that each user can specify to match a transaction with a category.

This however seems very low of a priority in the roadmap here ... it's very unfortunate...

[peachesCLH]

Allow the categorization rules to be detailed, specific. For example "if the merchant contains this phrase" or "if the amount is X" for the exact same amount for recurring transactions.

[mefistofelis]

Any news regarding this issue?