musikpolice / ofxcat Goto Github PK
View Code? Open in Web Editor NEWA command line utility that imports bank transactions in *.ofx format and categorizes them
License: Apache License 2.0
A command line utility that imports bank transactions in *.ofx format and categorizes them
License: Apache License 2.0
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.
Both are expressed in code as floating point values, and are being persisted to the database with much more precision than is necessary. For example, one transaction for $41.13 was saved with the amount -41.1300010681. This is very likely causing odd rounding errors in the math that is done on transactions. Fix it.
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
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
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.
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.
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.
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'}}
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.
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.
Now that transactions are being recorded in the database, we need a way to generate reports. At minimum, we'd have the following:
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
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
Now that we're storing data in sqlite, we need to worry about query execution speed. As the database grows, using indices in our queries will be important.
Review the queries in each of our DAOs and make sure that frequently used columns are indexed.
Here's an article that explains how sqlite indices work and some best practices for using them: https://medium.com/@JasonWyatt/squeezing-performance-from-sqlite-indexes-indexes-c4e175f3c346
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.
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.
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?
Ideally, users shouldn't have to have a Java SDK and Maven installed to compile and use the application. Research ways to automatically build executables for release that contain everything that the user needs to execute the application. Test on different platforms to ensure that they work as expected.
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.
A few issues:
--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-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 upTransaction 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.
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?
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
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
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.
A declarative, efficient, and flexible JavaScript library for building user interfaces.
🖖 Vue.js is a progressive, incrementally-adoptable JavaScript framework for building UI on the web.
TypeScript is a superset of JavaScript that compiles to clean JavaScript output.
An Open Source Machine Learning Framework for Everyone
The Web framework for perfectionists with deadlines.
A PHP framework for web artisans
Bring data to life with SVG, Canvas and HTML. 📊📈🎉
JavaScript (JS) is a lightweight interpreted programming language with first-class functions.
Some thing interesting about web. New door for the world.
A server is a program made to process requests and deliver data to clients.
Machine learning is a way of modeling and interpreting data that allows a piece of software to respond intelligently.
Some thing interesting about visualization, use data art
Some thing interesting about game, make everyone happy.
We are working to build community through open source technology. NB: members must have two-factor auth.
Open source projects and samples from Microsoft.
Google ❤️ Open Source for everyone.
Alibaba Open Source for everyone
Data-Driven Documents codes.
China tencent open source team.