diff --git a/conservancy_beancount/reconcile/prototype_amex_reconciler.py b/conservancy_beancount/reconcile/prototype_amex_reconciler.py index 280951759df48a822e7340680a813a5ce7983ba1..4fb37af797c537881edf4442977ae7c4fb815563 100644 --- a/conservancy_beancount/reconcile/prototype_amex_reconciler.py +++ b/conservancy_beancount/reconcile/prototype_amex_reconciler.py @@ -27,6 +27,7 @@ Problems in scope: - transactions are entered manually and reconciled after the fact, but importing from statements may be useful in some cases Q. How are reconciliation reports created currently? How do you read them? + - by hand from copying and pasting from the helper tool output TODO/ISSUES: - AMEX statement doesn't provide bank balance or running total @@ -110,8 +111,11 @@ parser.add_argument('--account', required=True, help='eg. Liabilities:CreditCard parser.add_argument('--grep-output-filename') # parser.add_argument('--report-group-regex') parser.add_argument('--show-reconciled-matches', action='store_true') +parser.add_argument('--statement-total', required=True) args = parser.parse_args() +statement_total = decimal.Decimal(args.statement_total) + # TODO: Should put in a sanity check to make sure the statement you're feeding # in matches the account you've provided. if 'AMEX' in args.account: @@ -135,6 +139,16 @@ end_date = statement_trans[-1]['date'] # but I'm not clear on how to use compile(). An example would help. entries, _, options = loader.load_file(args.beancount_file) +total_query = f"""SELECT sum(COST(position)) AS aa WHERE account = "{args.account}" + AND date <= {end_date.isoformat()} AND META('bank-statement') != NULL""" +result_types, result_rows = run_query( + entries, + options, + total_query, + numberify=True, +) +cleared_total = result_rows[0][0] + # String concatenation looks bad, but there's no SQL injection possible here # because BQL can't write back to the Beancount files. I hope! query = f"SELECT filename, META('lineno') AS posting_line, META('bank-statement') AS posting_statement, date, number(cost(position)), payee, narration where account = '{args.account}' and date >= {begin_date} and date <= {end_date}" @@ -183,6 +197,9 @@ print('-' * 155) for _, output in sorted(matches): print(output) print('-' * 155) +print(f'STATEMENT TOTAL: {statement_total}') +print(f'CLEARED TOTAL: {cleared_total:12,.2f}') +print('-' * 155) # Write statement metadata back to books if metadata_to_apply: