Coder Social home page Coder Social logo

ofxcat's People

Contributors

dependabot[bot] avatar musikpolice avatar

Stargazers

avatar

Watchers

avatar avatar avatar

ofxcat's Issues

Attempt to Detect Database Corruption

In #28 an exception was logged that related to an entry in the CategorizedTransaction table with an invalid category_id. The referenced Category just didn't exist. This shouldn't be possible, and should be detected, with the user prompted to re-categorize those broken transactions.

Add CLI options to fix miscategorized transactions

  • Find description strings that have been associated with two or more categories - these are probably mistakes
  • Re-categorize all transactions that have been associated with some category, optionally between two dates - for instance, take another pass at the UNKNOWN category

Transaction Categorization History Should be Considered in Category Suggestions

Right now, all category suggestions are driven by direct or fuzzy match on the transaction's description string. Consider searching for similar existing transactions (account, description, amount), and suggesting the categories that they were associated with. This should help to catch recurring payments/transfers to the same place

Try to Match up Transactions that are Transfers Between Accounts

If an OFX file contains transactions that represent the transfer from one account and the transfer to another, we can recognize them as being the same and categorize them together. The transfers in question will belong to two separate accounts, but share a date, an amount, and the transfer label. This label might vary depending on the institution however

Track Start of Month and End of Month Balances to Verify Data Integrity

When a new Transaction for some month/year is imported, check the database to see if a start of month balance and end of month balance have been recorded for that month/year combination.

If not found, prompt the user to input the start of month balance and end of month balance for that month. If the month/year is the current month/year, do not prompt for end of month balance.

Once recorded, these can be used in combination with the sum of the amount of all Transactions for the month to check if some Transactions are missing. It is possible for this check to return a false positive, but the check is better than nothing, since ofx files do not include balance changes.

Back up transaction database before importing new transactions

Before new transactions are imported, a copy of the existing transaction database should automatically be made, and the current timestamp should be appended to the name of that copy. This way, if an import goes badly, the user always has a way to recover known good data.

Improve Error Handling

Currently, if an exception is thrown while attempting to talk to the database, the import of a single transaction fails but there is no feedback to the user shown in the CLI.

Most recently, this happened when I had the database file open in intellij, causing the file to be locked and preventing the application from executing any transactions. This made the application behave strangely.

08:30:49.321 [main] ERROR ca.jonathanfritz.ofxcat.service.TransactionImportService - Failed to import Transfer Transfer{id=null, source=CategorizedTransaction{id=null, category=Category{id=1, name='TRANSFER'}}, sink=CategorizedTransaction{id=null, category=Category{id=1, name='TRANSFER'}}}
ca.jonathanfritz.ofxcat.exception.OfxCatException: Failed to insert either the source or sink of a Transfer
	at ca.jonathanfritz.ofxcat.service.TransactionImportService.insertTransferTransaction(TransactionImportService.java:210) ~[classes/:?]
	at ca.jonathanfritz.ofxcat.service.TransactionImportService.lambda$identifyTransfers$9(TransactionImportService.java:173) ~[classes/:?]
	at java.util.stream.ReferencePipeline$7$1.accept(ReferencePipeline.java:273) ~[?:?]
	at java.util.HashMap$KeySpliterator.forEachRemaining(HashMap.java:1707) ~[?:?]
	at java.util.stream.AbstractPipeline.copyInto(AbstractPipeline.java:509) ~[?:?]
	at java.util.stream.AbstractPipeline.wrapAndCopyInto(AbstractPipeline.java:499) ~[?:?]
	at java.util.stream.ReduceOps$ReduceOp.evaluateSequential(ReduceOps.java:921) ~[?:?]
	at java.util.stream.AbstractPipeline.evaluate(AbstractPipeline.java:234) ~[?:?]
	at java.util.stream.ReferencePipeline.collect(ReferencePipeline.java:682) ~[?:?]
	at ca.jonathanfritz.ofxcat.service.TransactionImportService.identifyTransfers(TransactionImportService.java:189) ~[classes/:?]
	at ca.jonathanfritz.ofxcat.service.TransactionImportService.categorizeTransactions(TransactionImportService.java:123) ~[classes/:?]
	at ca.jonathanfritz.ofxcat.service.TransactionImportService.importTransactions(TransactionImportService.java:81) ~[classes/:?]
	at ca.jonathanfritz.ofxcat.OfxCat.importTransactions(OfxCat.java:65) ~[classes/:?]
	at ca.jonathanfritz.ofxcat.OfxCat.main(OfxCat.java:135) ~[classes/:?]
Caused by: org.sqlite.SQLiteException: [SQLITE_BUSY]  The database file is locked (database is locked)
	at org.sqlite.core.DB.newSQLException(DB.java:1030) ~[sqlite-jdbc-3.36.0.3.jar:?]
	at org.sqlite.core.DB.newSQLException(DB.java:1042) ~[sqlite-jdbc-3.36.0.3.jar:?]
	at org.sqlite.core.DB.execute(DB.java:881) ~[sqlite-jdbc-3.36.0.3.jar:?]
	at org.sqlite.core.DB.executeUpdate(DB.java:922) ~[sqlite-jdbc-3.36.0.3.jar:?]
	at org.sqlite.jdbc3.JDBC3PreparedStatement.executeUpdate(JDBC3PreparedStatement.java:98) ~[sqlite-jdbc-3.36.0.3.jar:?]
	at ca.jonathanfritz.ofxcat.datastore.utils.DatabaseTransaction.insert(DatabaseTransaction.java:101) ~[classes/:?]
	at ca.jonathanfritz.ofxcat.datastore.CategorizedTransactionDao.insert(CategorizedTransactionDao.java:218) ~[classes/:?]
	at ca.jonathanfritz.ofxcat.service.TransactionImportService.insertTransferTransaction(TransactionImportService.java:202) ~[classes/:?]
	... 13 more

Another case where some transactions referenced a category that didn't exist (this should not have been possible - dunno what happened to foreign keys in this case):

08:30:49.458 [main] ERROR ca.jonathanfritz.ofxcat.service.TransactionImportService - Failed to import transaction Transaction{type=DEBIT, date=2019-05-23, amount=-429.74, description='OUTGOING INTERAC E-TRANSFER', account=Account{bankId='900000100', accountNumber='025025162961', accountType='SAVINGS', name='Home Renovation', id=3}, balance=9572.9, fitId='90000010020190523S002D328C61F'}
java.sql.SQLException: Category with id 2 does not exist
	at ca.jonathanfritz.ofxcat.datastore.CategorizedTransactionDao.lambda$new$1(CategorizedTransactionDao.java:47) ~[classes/:?]
	at java.util.Optional.orElseThrow(Optional.java:403) ~[?:?]
	at ca.jonathanfritz.ofxcat.datastore.CategorizedTransactionDao.lambda$new$2(CategorizedTransactionDao.java:47) ~[classes/:?]
	at ca.jonathanfritz.ofxcat.datastore.utils.ResultSetDeserializer.apply(ResultSetDeserializer.java:37) ~[classes/:?]
	at ca.jonathanfritz.ofxcat.datastore.utils.ResultSetDeserializer.apply(ResultSetDeserializer.java:15) ~[classes/:?]
	at ca.jonathanfritz.ofxcat.datastore.utils.DatabaseTransaction.query(DatabaseTransaction.java:70) ~[classes/:?]
	at ca.jonathanfritz.ofxcat.datastore.CategorizedTransactionDao.findByDescription(CategorizedTransactionDao.java:143) ~[classes/:?]
	at ca.jonathanfritz.ofxcat.service.TransactionCategoryService.categorizeTransactionExactMatch(TransactionCategoryService.java:93) ~[classes/:?]
	at ca.jonathanfritz.ofxcat.service.TransactionCategoryService.categorizeTransaction(TransactionCategoryService.java:76) ~[classes/:?]
	at ca.jonathanfritz.ofxcat.service.TransactionImportService.lambda$categorizeTransactions$7(TransactionImportService.java:137) ~[classes/:?]
	at java.lang.Iterable.forEach(Iterable.java:75) ~[?:?]
	at ca.jonathanfritz.ofxcat.service.TransactionImportService.categorizeTransactions(TransactionImportService.java:128) ~[classes/:?]
	at ca.jonathanfritz.ofxcat.service.TransactionImportService.importTransactions(TransactionImportService.java:81) ~[classes/:?]
	at ca.jonathanfritz.ofxcat.OfxCat.importTransactions(OfxCat.java:65) ~[classes/:?]
	at ca.jonathanfritz.ofxcat.OfxCat.main(OfxCat.java:135) ~[classes/:?]

Consider trying to detect a locked database file specifically, and surfacing error messages related to failing to import a transaction more generally.

Transaction import hangs when transaction may belong to one of a subset of categories

Ran into an issue while categorizing a transaction that might have belonged to one of two existing categories. Was prompted to choose which of the two, but then something went wrong and I was prompted to choose from the entire list of categories. The application hanged for maybe a minute and then proceeded with the next transaction.

Here's the UI:

Found new transaction:
Date: 2018-12-17
Type: DEBIT
Amount: -$300.00
Description: LOAN PMT

Select an existing category for the transaction:
  1: VISA
  2: UTILITIES
  3: Choose another Category
Enter your choice: 1

Select an existing category for transaction:
   1: ALCOHOL
   2: CASH
   3: CELLPHONES
   4: GROCERIES
   5: HAIRCUTS
   6: HEALTH
   7: HOME IMPROVEMENT
   8: INCOME
   9: MORTGAGE
  10: PETS
  11: RESTAURANTS
  12: SHOPPING
  13: UNKNOWN
  14: UTILITIES
  15: VACATION
  16: VISA
  17: New Category
Enter your choice: 16

Categorized transaction as VISA

Relevant logs:

16:36:02.975 DEBUG c.j.o.d.CategorizedTransactionDao - Attempting to determine if Transaction{type=DEBIT, date=2018-12-17, amount=-300.0, description='LOAN PMT', account=Account{bankId='xxxxx', accountId='yyyyy', accountType='CHECKING', name='Chequing', id=1}, balance=2456.0452} is a duplicate
16:36:02.975 DEBUG c.j.o.d.utils.ResultSetDeserializer - ResultSet contained zero results
16:36:02.975 DEBUG o.b.textio.jline.JLineTextTerminal - ansiColor(3, gray) = �[1;37m
16:36:02.975 DEBUG o.b.textio.jline.JLineTextTerminal - ansiColor(3, white) = �[1;37m
16:36:02.975 DEBUG o.b.textio.jline.JLineTextTerminal - ansiColor(3, gray) = �[1;37m
16:36:02.975 DEBUG o.b.textio.jline.JLineTextTerminal - ansiColor(3, white) = �[1;37m
16:36:02.975 DEBUG o.b.textio.jline.JLineTextTerminal - ansiColor(3, gray) = �[1;37m
16:36:02.975 DEBUG o.b.textio.jline.JLineTextTerminal - ansiColor(3, white) = �[1;37m
16:36:02.975 DEBUG o.b.textio.jline.JLineTextTerminal - ansiColor(3, gray) = �[1;37m
16:36:02.975 DEBUG o.b.textio.jline.JLineTextTerminal - ansiColor(3, white) = �[1;37m
16:36:02.975 DEBUG c.j.o.d.DescriptionCategoryDao - Attempting to select all DescriptionCategory objects
16:36:02.975 DEBUG c.j.ofxcat.datastore.CategoryDao - Attempting to query Category with id 1
16:36:02.975 DEBUG c.j.o.d.utils.ResultSetDeserializer - Found 1 results
16:36:02.975 DEBUG c.j.ofxcat.datastore.CategoryDao - Attempting to query Category with id 2
16:36:02.975 DEBUG c.j.o.d.utils.ResultSetDeserializer - Found 1 results
16:36:02.975 DEBUG c.j.ofxcat.datastore.CategoryDao - Attempting to query Category with id 3
16:36:02.975 DEBUG c.j.o.d.utils.ResultSetDeserializer - Found 1 results
16:36:02.975 DEBUG c.j.ofxcat.datastore.CategoryDao - Attempting to query Category with id 4
16:36:02.975 DEBUG c.j.o.d.utils.ResultSetDeserializer - Found 1 results
16:36:02.975 DEBUG c.j.ofxcat.datastore.CategoryDao - Attempting to query Category with id 5
16:36:02.975 DEBUG c.j.o.d.utils.ResultSetDeserializer - Found 1 results
16:36:02.975 DEBUG c.j.ofxcat.datastore.CategoryDao - Attempting to query Category with id 5
16:36:02.975 DEBUG c.j.o.d.utils.ResultSetDeserializer - Found 1 results
16:36:02.975 DEBUG c.j.ofxcat.datastore.CategoryDao - Attempting to query Category with id 6
16:36:02.975 DEBUG c.j.o.d.utils.ResultSetDeserializer - Found 1 results
16:36:02.975 DEBUG c.j.ofxcat.datastore.CategoryDao - Attempting to query Category with id 2
16:36:02.975 DEBUG c.j.o.d.utils.ResultSetDeserializer - Found 1 results
16:36:02.975 DEBUG c.j.ofxcat.datastore.CategoryDao - Attempting to query Category with id 7
16:36:02.975 DEBUG c.j.o.d.utils.ResultSetDeserializer - Found 1 results
16:36:02.975 DEBUG c.j.ofxcat.datastore.CategoryDao - Attempting to query Category with id 8
16:36:02.975 DEBUG c.j.o.d.utils.ResultSetDeserializer - Found 1 results
16:36:02.975 DEBUG c.j.ofxcat.datastore.CategoryDao - Attempting to query Category with id 5
16:36:02.975 DEBUG c.j.o.d.utils.ResultSetDeserializer - Found 1 results
16:36:02.975 DEBUG c.j.ofxcat.datastore.CategoryDao - Attempting to query Category with id 2
16:36:02.975 DEBUG c.j.o.d.utils.ResultSetDeserializer - Found 1 results
16:36:02.975 DEBUG c.j.ofxcat.datastore.CategoryDao - Attempting to query Category with id 9
16:36:02.975 DEBUG c.j.o.d.utils.ResultSetDeserializer - Found 1 results
16:36:02.975 DEBUG c.j.ofxcat.datastore.CategoryDao - Attempting to query Category with id 10
16:36:02.975 DEBUG c.j.o.d.utils.ResultSetDeserializer - Found 1 results
16:36:02.975 DEBUG c.j.ofxcat.datastore.CategoryDao - Attempting to query Category with id 11
16:36:02.975 DEBUG c.j.o.d.utils.ResultSetDeserializer - Found 1 results
16:36:02.975 DEBUG c.j.ofxcat.datastore.CategoryDao - Attempting to query Category with id 12
16:36:02.975 DEBUG c.j.o.d.utils.ResultSetDeserializer - Found 1 results
16:36:02.975 DEBUG c.j.ofxcat.datastore.CategoryDao - Attempting to query Category with id 13
16:36:02.975 DEBUG c.j.o.d.utils.ResultSetDeserializer - Found 1 results
16:36:02.975 DEBUG c.j.ofxcat.datastore.CategoryDao - Attempting to query Category with id 1
16:36:02.975 DEBUG c.j.o.d.utils.ResultSetDeserializer - Found 1 results
16:36:02.975 DEBUG c.j.ofxcat.datastore.CategoryDao - Attempting to query Category with id 7
16:36:02.975 DEBUG c.j.o.d.utils.ResultSetDeserializer - Found 1 results
16:36:02.975 DEBUG c.j.ofxcat.datastore.CategoryDao - Attempting to query Category with id 1
16:36:02.975 DEBUG c.j.o.d.utils.ResultSetDeserializer - Found 1 results
16:36:02.975 DEBUG c.j.ofxcat.datastore.CategoryDao - Attempting to query Category with id 14
16:36:02.975 DEBUG c.j.o.d.utils.ResultSetDeserializer - Found 1 results
16:36:02.975 DEBUG c.j.ofxcat.datastore.CategoryDao - Attempting to query Category with id 15
16:36:02.975 DEBUG c.j.o.d.utils.ResultSetDeserializer - Found 1 results
16:36:02.975 DEBUG c.j.ofxcat.datastore.CategoryDao - Attempting to query Category with id 1
16:36:02.975 DEBUG c.j.o.d.utils.ResultSetDeserializer - Found 1 results
16:36:02.975 DEBUG c.j.ofxcat.datastore.CategoryDao - Attempting to query Category with id 16
16:36:02.975 DEBUG c.j.o.d.utils.ResultSetDeserializer - Found 1 results
16:36:02.975 DEBUG c.j.ofxcat.datastore.CategoryDao - Attempting to query Category with id 12
16:36:02.975 DEBUG c.j.o.d.utils.ResultSetDeserializer - Found 1 results
16:36:02.975 DEBUG c.j.o.d.utils.ResultSetDeserializer - Found 25 results
16:36:02.975 DEBUG c.j.o.s.TransactionCategoryService - Fuzzy category matches for transaction description "LOAN PMT":
16:36:02.975 DEBUG c.j.o.d.DescriptionCategoryDao - Attempting to select all DescriptionCategory objects
16:36:02.975 DEBUG c.j.ofxcat.datastore.CategoryDao - Attempting to query Category with id 1
16:36:02.975 DEBUG c.j.o.d.utils.ResultSetDeserializer - Found 1 results
16:36:02.975 DEBUG c.j.ofxcat.datastore.CategoryDao - Attempting to query Category with id 2
16:36:02.975 DEBUG c.j.o.d.utils.ResultSetDeserializer - Found 1 results
16:36:02.975 DEBUG c.j.ofxcat.datastore.CategoryDao - Attempting to query Category with id 3
16:36:02.975 DEBUG c.j.o.d.utils.ResultSetDeserializer - Found 1 results
16:36:02.975 DEBUG c.j.ofxcat.datastore.CategoryDao - Attempting to query Category with id 4
16:36:02.975 DEBUG c.j.o.d.utils.ResultSetDeserializer - Found 1 results
16:36:02.975 DEBUG c.j.ofxcat.datastore.CategoryDao - Attempting to query Category with id 5
16:36:02.975 DEBUG c.j.o.d.utils.ResultSetDeserializer - Found 1 results
16:36:02.975 DEBUG c.j.ofxcat.datastore.CategoryDao - Attempting to query Category with id 5
16:36:02.975 DEBUG c.j.o.d.utils.ResultSetDeserializer - Found 1 results
16:36:02.975 DEBUG c.j.ofxcat.datastore.CategoryDao - Attempting to query Category with id 6
16:36:02.975 DEBUG c.j.o.d.utils.ResultSetDeserializer - Found 1 results
16:36:02.975 DEBUG c.j.ofxcat.datastore.CategoryDao - Attempting to query Category with id 2
16:36:02.975 DEBUG c.j.o.d.utils.ResultSetDeserializer - Found 1 results
16:36:02.975 DEBUG c.j.ofxcat.datastore.CategoryDao - Attempting to query Category with id 7
16:36:02.975 DEBUG c.j.o.d.utils.ResultSetDeserializer - Found 1 results
16:36:02.975 DEBUG c.j.ofxcat.datastore.CategoryDao - Attempting to query Category with id 8
16:36:02.975 DEBUG c.j.o.d.utils.ResultSetDeserializer - Found 1 results
16:36:02.975 DEBUG c.j.ofxcat.datastore.CategoryDao - Attempting to query Category with id 5
16:36:02.975 DEBUG c.j.o.d.utils.ResultSetDeserializer - Found 1 results
16:36:02.975 DEBUG c.j.ofxcat.datastore.CategoryDao - Attempting to query Category with id 2
16:36:02.975 DEBUG c.j.o.d.utils.ResultSetDeserializer - Found 1 results
16:36:02.975 DEBUG c.j.ofxcat.datastore.CategoryDao - Attempting to query Category with id 9
16:36:02.975 DEBUG c.j.o.d.utils.ResultSetDeserializer - Found 1 results
16:36:02.975 DEBUG c.j.ofxcat.datastore.CategoryDao - Attempting to query Category with id 10
16:36:02.975 DEBUG c.j.o.d.utils.ResultSetDeserializer - Found 1 results
16:36:02.975 DEBUG c.j.ofxcat.datastore.CategoryDao - Attempting to query Category with id 11
16:36:02.975 DEBUG c.j.o.d.utils.ResultSetDeserializer - Found 1 results
16:36:02.975 DEBUG c.j.ofxcat.datastore.CategoryDao - Attempting to query Category with id 12
16:36:02.975 DEBUG c.j.o.d.utils.ResultSetDeserializer - Found 1 results
16:36:02.975 DEBUG c.j.ofxcat.datastore.CategoryDao - Attempting to query Category with id 13
16:36:02.975 DEBUG c.j.o.d.utils.ResultSetDeserializer - Found 1 results
16:36:02.975 DEBUG c.j.ofxcat.datastore.CategoryDao - Attempting to query Category with id 1
16:36:02.975 DEBUG c.j.o.d.utils.ResultSetDeserializer - Found 1 results
16:36:02.975 DEBUG c.j.ofxcat.datastore.CategoryDao - Attempting to query Category with id 7
16:36:02.975 DEBUG c.j.o.d.utils.ResultSetDeserializer - Found 1 results
16:36:02.975 DEBUG c.j.ofxcat.datastore.CategoryDao - Attempting to query Category with id 1
16:36:02.975 DEBUG c.j.o.d.utils.ResultSetDeserializer - Found 1 results
16:36:02.975 DEBUG c.j.ofxcat.datastore.CategoryDao - Attempting to query Category with id 14
16:36:02.975 DEBUG c.j.o.d.utils.ResultSetDeserializer - Found 1 results
16:36:02.975 DEBUG c.j.ofxcat.datastore.CategoryDao - Attempting to query Category with id 15
16:36:02.975 DEBUG c.j.o.d.utils.ResultSetDeserializer - Found 1 results
16:36:02.975 DEBUG c.j.ofxcat.datastore.CategoryDao - Attempting to query Category with id 1
16:36:02.975 DEBUG c.j.o.d.utils.ResultSetDeserializer - Found 1 results
16:36:02.975 DEBUG c.j.ofxcat.datastore.CategoryDao - Attempting to query Category with id 16
16:36:02.975 DEBUG c.j.o.d.utils.ResultSetDeserializer - Found 1 results
16:36:02.975 DEBUG c.j.ofxcat.datastore.CategoryDao - Attempting to query Category with id 12
16:36:02.975 DEBUG c.j.o.d.utils.ResultSetDeserializer - Found 1 results
16:36:02.975 DEBUG c.j.o.d.utils.ResultSetDeserializer - Found 25 results
16:36:02.975 DEBUG c.j.o.s.TransactionCategoryService - LOAN PMT: 100
16:36:02.975 DEBUG c.j.o.s.TransactionCategoryService - UTILITY BILL PMT UTILITIES: 86
16:36:02.975 DEBUG c.j.o.s.TransactionCategoryService - HYDRO BILL PMT KIT-WIL HYDRO: 86
16:36:17.815 DEBUG c.j.o.d.DescriptionCategoryDao - Attempting to select DescriptionCategory with description LOAN PMT and Category Category{id=4, name='VISA'}
16:36:17.815 DEBUG c.j.ofxcat.datastore.CategoryDao - Attempting to query Category with id 4
16:36:17.815 DEBUG c.j.o.d.utils.ResultSetDeserializer - Found 1 results
16:36:17.815 DEBUG c.j.o.d.utils.ResultSetDeserializer - Found 1 results
16:36:17.815 DEBUG c.j.ofxcat.datastore.CategoryDao - Attempting to select all Category objects
16:36:17.815 DEBUG c.j.o.d.utils.ResultSetDeserializer - Found 16 results
16:36:29.353 DEBUG c.j.o.d.DescriptionCategoryDao - Attempting to select DescriptionCategory with description LOAN PMT and Category Category{id=null, name='VISA'}
16:36:29.353 DEBUG c.j.ofxcat.datastore.CategoryDao - Attempting to query Category with id 4
16:36:29.353 DEBUG c.j.o.d.utils.ResultSetDeserializer - Found 1 results
16:36:29.353 DEBUG c.j.o.d.utils.ResultSetDeserializer - Found 1 results
16:36:29.353 DEBUG o.b.textio.jline.JLineTextTerminal - ansiColor(3, gray) = �[1;37m
16:36:29.353 DEBUG o.b.textio.jline.JLineTextTerminal - ansiColor(3, white) = �[1;37m
16:36:29.353 INFO  c.j.o.s.TransactionImportService - Categorized Transaction Transaction{type=DEBIT, date=2018-12-17, amount=-300.0, description='LOAN PMT', account=Account{bankId='xxxxx', accountId='yyyyy', accountType='CHECKING', name='Chequing', id=1}, balance=2456.0452} as Category{id=4, name='VISA'}
16:36:29.353 DEBUG c.j.o.d.CategorizedTransactionDao - Attempting to insert CategorizedTransaction CategorizedTransaction{id=null, category=Category{id=4, name='VISA'}}

Investigate odd transactions in Home Improvement and Baby Savings accounts

Some transactions that appear to belong to my chequing account have been associated with my savings accounts. Things like debit card purchases at commonly visited retailers. The savings accounts are not available from my debit card, so this should not be possible. Could be a bug in the bank's ofx export? Might also be an issue in the way we parse the files and/or a bad cache somewhere.

Add Graphical Visualizations

Excel is nice and all, but it would be cool to pop up graphs of trends, distributions, or other interesting data.
There's a library called JFreeChart that is licensed under LGPL and should be a good fit for this use case.

Add command line switches for pulling transaction reports

Now that transactions are being recorded in the database, we need a way to generate reports. At minimum, we'd have the following:

  • Get all Transactions between any two dates or for any month
  • Get all Transactions, bucketed into categories between any two dates or for any month
  • Get all Categories
  • Manage DescriptionCategories (i.e. delete a problematic association)

The results should be output into CSV format so that they can be piped out to a file or into some other command line utility

Serialize Transaction Categorization Reports to Disk

When new transactions are loaded into memory for a particular month, the total amount spent on each known category should be re-computed for that month, and that report should be serialized out to disk in a CSV format that can be easily imported into Excel or similar. When the report for a month is updated, the user must be notified so that they can act accordingly

Is Fuzzy Matching Useful?

It certainly seemed like a good idea, but maybe it's better just to match a new transaction based on previous matches for similar transaction descriptions. In essence, this would still be a fuzzy match, but on the transactions table, and would allow us to delete the transaction categories table altogether. Probably want to keep an in-memory map of existing distinct transaction descriptions and their associated cateogries. Fuzzy search on keys and collect values to return suggested categories. Exact matches should be weighted highest.

Add Proper Support for Credit Cards

This one is embarrassing. It turns out that credit cards look different than bank accounts in the OFX format. Here's an example of a bank account:

<BANKMSGSRSV1>
  <STMTTRNRS>
   <TRNUID>D061078135126302
   <STATUS>
    <CODE>0
    <SEVERITY>INFO
    <MESSAGE>OK
   </STATUS>
   <STMTRS>
    <CURDEF>CAD
    <BANKACCTFROM>
     <BANKID>800200200
     <ACCTID>078145256973
     <ACCTTYPE>CHECKING
    </BANKACCTFROM>
    <BANKTRANLIST>
         ...transactions go here...
    </BANKTRANLIST>
   </STMTRS>
  </STMTTRNRS>
</BANKMSGSRSV1>

and here's what a credit card looks like:

<CREDITCARDMSGSRSV1>
  <CCSTMTTRNRS>
   <TRNUID>V0034510156020975822
   <STATUS>
    <CODE>0
    <SEVERITY>INFO
    <MESSAGE>OK
   </STATUS>
   <CCSTMTRS>
    <CURDEF>CAD
    <CCACCTFROM>
     <ACCTID>4510156020975822
    </CCACCTFROM>
    <BANKTRANLIST>
         ...transactions go here...
    </BANKTRANLIST>
   <LEDGERBAL>
    <BALAMT>-766.48
    <DTASOF>20201218
   </LEDGERBAL>
   <AVAILBAL>
    <BALAMT>-766.48
    <DTASOF>20201218
   </AVAILBAL>
  </CCSTMTRS>
 </CCSTMTTRNRS>
</CREDITCARDMSGSRSV1>

Right now, ofxcat determines the account number associated with a set of transactions by looking for the <ACCTID> element nested inside of a <BANKACCTFROM> element. But credit cards nest their <ACCTID> element inside of an <CCACCTFROM> element.

Because my bank always exports my credit card at the end of the file, the transactions that should belong to it are actually associated with the account that appeared right before it in the file. I didn't notice this until recently, when I added the account to the transaction display.

The solution here is simple enough - fix the OFX parser so that it correctly assigns credit card numbers, and so that it clears the stored account whenever it sees a </BANKACCTFROM> or </CCACCTFROM> element.

Allow for Custom Transaction Matching Rules

Currently, transactions are always matched based on their description string (and nominally their account, but that may not last for long).

Consider this transaction that transfers some money to an RESP account:

<STMTTRN>
  <TRNTYPE>DEBIT
  <DTPOSTED>20220328120000[-5:EST]
  <TRNAMT>-53.42
  <FITID>30000020020220328F0020474DBA3
  <NAME>SCHEDULED PAYMENT               
  <MEMO>ONLINE TRANSFER 
 </STMTTRN>

The NAME and MEMO fields on this transaction also appear on similar transactions that transfer money to other RBC Direct Investing accounts like an RRSP, LIRA, or similar.

From the perspective of the TransactionCategorizationService, all of these transactions are identical. They all transfer some amount of money out of one account into an investment account, but the target investment account is not indicated in the transaction data, and is not included in the OFX file, so this transfer cannot be automatically detected.

In practice, the only option for automatically categorizing this transaction is to create a single category called "Investments" and associate all instances of SCHEDULED PAYMENT ONLINE TRANSFER to it.

Ideally, we could differentiate between these based on the amount of the transfer ($53.42 is a scheduled payment to an RESP, while $400 is a scheduled payment to an RRSP, etc) so that they could be properly categorized.

The problem with this idea is that there is currently no way for the user to create complex rules that consider more than just the description of each transaction. This type of evaluator exists in the ca.jonathanfritz.ofxcat.cleaner.rules package, but it is only used by transaction cleaners. This case cannot be solved with transaction cleaners, because it is very user specific.

Should the user be able to create special rules in a JSON file? Or should the UI be enhanced to allow for the creation of special rules? How would the user be prompted to create a special rule each time they categorize a transaction?

Add import filename to CategorizedTransaction table

If a bug is causing transactions to be imported incorrectly, it would be great to be able to go back to the problem file (which is backed up in the data directory) and verify the import data.

OFX files also contain a per-transaction unique identifier string. Wouldn't be a bad idea to record this as well.

Improve transaction categorization report

A few issues:

  1. All current database categories should be included in the csv output, even if they don't have any associated transactions. This makes it easier to copy/paste the data to excel/calc
  2. Categories should always be listed in the same order. Alphabetical probably makes sense. Again, this makes copy/pasting easier
  3. Dollar signs should not be included in CSV export. This confuses excel/calc's data type inference systems
  4. Add an --account flag to limit results to a particular account. This will make it easy to see how much we contributed to savings, because it won't include all the money flowing into those savings accounts
  5. Add a -o flag to set output type. Default should be csv, but we may as well also support table for a pretty table and json for people who have some kind of json processing pipeline set up

Serialize Transactions to Database

Transaction history should be persisted into the database. When new transactions are imported, existing transactions for that month should be loaded from the database and duplicates discarded.

Unmatched XFER Transactions

There are a few cases where transactions were recognized as the XFER type and automatically categorized as Transfer, but the matching transaction was not found in the OFX file.

What should we do with these one-sided transactions? Ideally they would be properly categorized, but we lack context to do so. Should they be categorized as UNKNOWN?

Is it possible for the two halves of a transfer to appear in separate OFX files? If so, does the transfer matching algorithm need to consider transactions that already exist when searching for a source/sink?

Include Default Categories for Large Retailers

Consider including a list of default categories, as well as category description strings for large retail outlets and restaurant chains. This will likely help most uses by automatically suggesting relevant matches for the majority of their transactions

Fuzzy Match of Multiple Categories Still Prompts User to Choose from All Categories

Transactions that fuzzy match multiple categories are appropriately detected and the subset of categories is displayed, but once I pick from that subset, the full set of categories is still displayed and I'm prompted to pick again. Interestingly, the second choice is always ignored and the answer from the first prompt is used.

The second prompt should not be displayed.

Found new transaction:
Date: 2020-12-11
Type: CREDIT
Amount: $8166.89
Description: TRANSFER

Select an existing category for the transaction:
  1: UNKNOWN
  2: VACATION
  3: JOE MORTGAGE
  4: HOME IMPROVEMENT
  5: ALCOHOL
  6: Choose another Category
Enter your choice: 4

Select an existing category for transaction:
   1: ALCOHOL
   2: AUTO INSURANCE
   3: CASH
   4: CELLPHONES
   5: ENTERTAINMENT
   6: GAS
   7: GROCERIES
   8: HAIRCUTS
   9: HEALTH
  10: HOME IMPROVEMENT
  11: INCOME
  12: JOE MORTGAGE
  13: LINE OF CREDIT
  14: MORTGAGE
  15: PETS
  16: RESTAURANTS
  17: SHOPPING
  18: UNKNOWN
  19: UTILITIES
  20: VACATION
  21: VISA
  22: New Category
Enter your choice: 1

Categorized transaction as HOME IMPROVEMENT

Serialize CategoryTransactionStore to JSON

The serialized file should be saved to the user's home folder, and automatically loaded on startup to populate the transaction map with saved data from previous runs. This will allow the application to get smarter as it gets used.

Recommend Projects

  • React photo React

    A declarative, efficient, and flexible JavaScript library for building user interfaces.

  • Vue.js photo Vue.js

    🖖 Vue.js is a progressive, incrementally-adoptable JavaScript framework for building UI on the web.

  • Typescript photo Typescript

    TypeScript is a superset of JavaScript that compiles to clean JavaScript output.

  • TensorFlow photo TensorFlow

    An Open Source Machine Learning Framework for Everyone

  • Django photo Django

    The Web framework for perfectionists with deadlines.

  • D3 photo D3

    Bring data to life with SVG, Canvas and HTML. 📊📈🎉

Recommend Topics

  • javascript

    JavaScript (JS) is a lightweight interpreted programming language with first-class functions.

  • web

    Some thing interesting about web. New door for the world.

  • server

    A server is a program made to process requests and deliver data to clients.

  • Machine learning

    Machine learning is a way of modeling and interpreting data that allows a piece of software to respond intelligently.

  • Game

    Some thing interesting about game, make everyone happy.

Recommend Org

  • Facebook photo Facebook

    We are working to build community through open source technology. NB: members must have two-factor auth.

  • Microsoft photo Microsoft

    Open source projects and samples from Microsoft.

  • Google photo Google

    Google ❤️ Open Source for everyone.

  • D3 photo D3

    Data-Driven Documents codes.