Assigning account categories to bank transactions

[pjfarleyiii]

This is as simple an example as I could manage to set up to show how to match a CSV of bank transactions to a much smaller CSV of Payee_Name and Account_Category. The original goal was to process a CSV of years of bank transactions to add an accounting category to each transaction for eventual input to a checkbook program like KMyMoney or Quicken where reports by account category can help manage a budget.

Note that the example input transaction with $Name == "CON EDISON" does NOT have a matching entry in the name-to-category file. As time goes by, there will be new "Name" values in the input bank transaction file and they need to have a non-blank Category value, so I assign the category "Uncategorized" to any line where the Category field is not populated after the join is completed.

The "Starting / Ending Balance" lines in the input transaction file DO have a Category value, but in order to get the Category from the name-to-category input file into the final result I found that I had to first cut the original "Category" field from the input. I could not figure out how to get a join to keep an original non-empty value of Category but assign a new category value when the input transaction Category is empty.

The resulting Miller command line looks complex but it works, which is what counts.

I hope this helps anyone with a similar requirement.

Input Name to Category CSV (bankcats.csv):

Name,Category
ALLSTATE INSURANCE,Insurance
AT&T MOBILITY (1),Telephone Services
Cash Withdrawal,Cash
NYC DEPT OF FIN SUMMONS 1,Parking Ticket
TIME WARNER CABLE NYC,Utilities

Input sample bank transaction file (banktrans.csv):

Status,Date,Number,Name,Memo,Debit,Credit,Balance,Account,Category,AcctType,SimpleDesc,Seq,Currency
cleared,2013-12-24,,[Starting Balance As Of 2013-12-24],,,,1201.39,xxxx8295,[Balance],Checking,,1,$
cleared,2013-12-24,,NYC DEPT OF FIN SUMMONS 1,ACH Electronic Debit,45.00,,1156.39,xxxx8295,,Checking,,2,$
cleared,2013-12-24,,Cash Withdrawal,MyBank ATM 02:32p #9361,300.00,,856.39,xxxx8295,,Checking,,3,$
cleared,2013-12-26,13259,ALLSTATE INSURANCE,Payment 013259 DA,86.31,,770.08,xxxx8295,,Checking,,4,$
cleared,2013-12-30,13278,AT&T MOBILITY (1),Payment 013278 DA,235.14,,534.94,xxxx8295,,Checking,,5,$
cleared,2013-12-30,13282,TIME WARNER CABLE NYC,Payment 013282 DA,210.57,,324.37,xxxx8295,,Checking,,6,$
cleared,2014-01-21,13289,CON EDISON,Payment 013289 DA,293.00,,31.37,xxxx8295,,Checking,,7,$
cleared,2014-01-24,,[Ending Balance As Of 2014-01-23],Balance 31.37,,,31.37,xxxx8295,[Balance],Checking,,8,$

Miller command to add the category value to the transactions base on the Name field:

mlr --csv cut -f Category -x then join -j Name --ur --lk Category -f bankcats.csv then unsparsify --fill-with "" then put -e '$Name=~"Balance"{$Category="[Balance]"};$Category==""{$Category="Uncategorized"}' banktrans.csv > bankjoin.csv

Output of the Miller command (bankjoin.csv):

Status,Date,Number,Name,Memo,Debit,Credit,Balance,Account,AcctType,SimpleDesc,Seq,Currency,Category
cleared,2013-12-24,,[Starting Balance As Of 2013-12-24],,,,1201.39,xxxx8295,Checking,,1,$,[Balance]
cleared,2013-12-24,,NYC DEPT OF FIN SUMMONS 1,ACH Electronic Debit,45.00,,1156.39,xxxx8295,Checking,,2,$,Parking Ticket
cleared,2013-12-24,,Cash Withdrawal,MyBank ATM 02:32p #9361,300.00,,856.39,xxxx8295,Checking,,3,$,Cash
cleared,2013-12-26,13259,ALLSTATE INSURANCE,Payment 013259 DA,86.31,,770.08,xxxx8295,Checking,,4,$,Insurance
cleared,2013-12-30,13278,AT&T MOBILITY (1),Payment 013278 DA,235.14,,534.94,xxxx8295,Checking,,5,$,Telephone Services
cleared,2013-12-30,13282,TIME WARNER CABLE NYC,Payment 013282 DA,210.57,,324.37,xxxx8295,Checking,,6,$,Utilities
cleared,2014-01-21,13289,CON EDISON,Payment 013289 DA,293.00,,31.37,xxxx8295,Checking,,7,$,Uncategorized
cleared,2014-01-24,,[Ending Balance As Of 2014-01-23],Balance 31.37,,,31.37,xxxx8295,Checking,,8,$,[Balance]

[johnkerl]

Nice, @pjfarleyiii !! :D

[pjfarleyiii]

Thanks John. It still bothers me a little bit that I need one pattern-action statement in the final "put" operation to re-create the "[Balance]" category when it already exists in the transaction input file as well as the final one to convert empty $Category values to "Uncategorized".

Can you think of any shorter way to keep just the original transaction input $Category values but assign new ones from the join left-file when $Category is empty?

Something along the lines of this, where the second argument to function "lookup-in-CAT-file" specifies a default value if nothing is found:

if ($Category == "") { $Category = lookup-in-CAT-file($Name, "Uncategorized") }