diff --git a/conservancy_beancount/reconcile/helper.py b/conservancy_beancount/reconcile/helper.py index 2aff9af186562f86421f529e3e29db6a9206564d..4829c05b2a439f3d1399c74f7a7cae1e35c50952 100644 --- a/conservancy_beancount/reconcile/helper.py +++ b/conservancy_beancount/reconcile/helper.py @@ -114,7 +114,7 @@ def reconciliation_report_path(account, end_date): return f'Financial/Controls/Reports-for-Treasurer/{end_date}_{account_name}_bank-reconciliation.csv' -def parse_args(): +def parse_args(argv): parser = argparse.ArgumentParser(description='Reconciliation helper') parser.add_argument('--beancount-file', required=True) parser.add_argument('--account', help='Full account name, e.g. "Liabilities:CreditCard:AMEX"', required=True) @@ -126,7 +126,7 @@ def parse_args(): parser.add_argument('--cost-function', default='COST') parser.add_argument('--grep-output-filename') # parser.add_argument('--report-group-regex') - args = parser.parse_args() + args = parser.parse_args(args=argv[1:]) if args.month or args.period: if not (args.month and args.period): parser.error('--month and --period must be used together') @@ -140,108 +140,118 @@ def beancount_file_exists(path): return os.path.isfile(path) -args = parse_args() -if not beancount_file_exists(args.beancount_file): - sys.exit(f'Beancount file does not exist: {args.beancount_file}') -if args.month or args.period: - parsed_date = datetime.datetime.strptime(args.month, '%Y-%m').date() - preDate = end_of_month(parsed_date - relativedelta(months=args.period)).isoformat() - lastDateInPeriod = end_of_month(parsed_date).isoformat() - month = args.month -else: - preDate = args.prev_end_date - lastDateInPeriod = args.cur_end_date.isoformat() - month = args.cur_end_date.strftime('%Y-%m') -grep_output_file: typing.IO -if args.grep_output_filename: - grep_output_file = open(args.grep_output_filename, 'w') -else: - grep_output_file = tempfile.NamedTemporaryFile(prefix='bc-reconcile-grep-output_', mode='w', delete=False) -beancount_file = args.beancount_file -account = args.account -cost_function = args.cost_function -statement_match = args.statement_match if args.statement_match else month - -QUERIES = { - f"00: CLEARED BAL ENDING DAY BEFORE {preDate}": - # $CONLEDGER -V -C -e "$preDate" bal "/$acct/" - f"""SELECT sum({cost_function}(position)) AS aa WHERE account = "{account}" - AND date < {preDate} AND META('bank-statement') != NULL""", - - f"01: ALL TRANSACTION BAL ENDING DAY BEFORE {preDate}": - # $CONLEDGER -V -e "$preDate" bal "/$acct/" - f"""SELECT sum({cost_function}(position)) AS aa WHERE account = "{account}" - AND date < {preDate}""", - - f"02: ALL TRANSACTION BAL, ending {lastDateInPeriod}": - # $CONLEDGER -V -e "$date" bal "/$acct/" - f"""SELECT sum({cost_function}(position)) AS aa WHERE account = "{account}" - AND date <= {lastDateInPeriod}""", - - f"03: UNCLEARED TRANSACTIONS, ending {lastDateInPeriod}": - f"""SELECT date, {cost_function}(position) as amt, ANY_META('check-id') as chknum, narration, payee, ENTRY_META('code') as code - WHERE account = "{account}" - AND date <= {lastDateInPeriod} AND META('bank-statement') = NULL - ORDER BY date, payee, narration""", - - "04: UNCLEARED TRANSACTION FILE, SUITABLE FOR GREP": - # $CONLEDGER -w -F "%(filename):%(beg_line): %(date) %(code) %(payee) %(amount)\n" --sort d -U -e "$date" reg "/$acct/" > "$TMPDIR/unreconciled-lines" - f"""SELECT ENTRY_META('filename') as file, META('lineno') as line, date, - {cost_function}(position) as amt, ANY_META('check-id') as chknum, narration, payee, ANY_META("entity") as entity, ENTRY_META('code') as c - WHERE account = "{account}" - AND date <= {lastDateInPeriod} AND META('bank-statement') = NULL - ORDER BY date, payee, narration""", - - f"05: CLEARED BALANCE ending {lastDateInPeriod}": - # $CONLEDGER -V -C -e "$date" bal "/$acct/" - f"""SELECT sum({cost_function}(position)) AS aa WHERE account = "{account}" - AND date <= {lastDateInPeriod} AND META('bank-statement') != NULL""", - - f"06: CLEARED SUBTRACTIONS on {month}'s statement": - # $CONLEDGER -V -C --limit "a > 0 and tag(\"Statement\") =~ /$statementSearchString/" bal "/$acct/" - f"""SELECT sum(number({cost_function}(position))) AS aa - WHERE account = "{account}" - and META("bank-statement") ~ "{statement_match}" and number({cost_function}(position)) < 0""", - - f"07: CLEARED ADDITIONS on {month}'s statement": - # $CONLEDGER -V -C --limit "a < 0 and tag(\"Statement\") =~ /$statementSearchString/" bal "/$acct/" - f"""SELECT sum(number({cost_function}(position))) AS aa - WHERE account = "{account}" - and META("bank-statement") ~ "{statement_match}" and number({cost_function}(position)) > 0""", -} - -# Run Beancount queries. -print(f"START RECONCILIATION FOR {account} ENDING {lastDateInPeriod} (previous end date {preDate})") -entries, _, options = loader.load_file(beancount_file) -uncleared_rows = [] # Hack to capture results of query 03. -cleared_balance = decimal.Decimal('0') -all_trans_balance = decimal.Decimal('0') -for desc, query in QUERIES.items(): - rtypes, rrows = run_query(entries, options, query, numberify=True) - if not rrows: - print(f'{desc:<55} {"N/A":>11}') - elif desc.startswith('04'): - homedir = os.getenv('HOME', '') - print(f'{desc}\n See {grep_output_file.name}') - grep_rows = [format_record_for_grep(row, homedir) for row in rrows] - print(tabulate(grep_rows), file=grep_output_file) - elif len(rrows) == 1 and isinstance(rrows[0][0], decimal.Decimal): - result = rrows[0][0] - print(f'{desc:<55} {result:11,.2f}') - if desc.startswith('02'): - all_trans_balance = result - if desc.startswith('05'): - cleared_balance = result +def main(args): + if not beancount_file_exists(args.beancount_file): + sys.exit(f'Beancount file does not exist: {args.beancount_file}') + if args.month or args.period: + parsed_date = datetime.datetime.strptime(args.month, '%Y-%m').date() + preDate = end_of_month(parsed_date - relativedelta(months=args.period)).isoformat() + lastDateInPeriod = end_of_month(parsed_date).isoformat() + month = args.month + else: + preDate = args.prev_end_date + lastDateInPeriod = args.cur_end_date.isoformat() + month = args.cur_end_date.strftime('%Y-%m') + grep_output_file: typing.IO + if args.grep_output_filename: + grep_output_file = open(args.grep_output_filename, 'w') else: - headers = [c[0].capitalize() for c in rtypes] - if desc.startswith('03'): - uncleared_rows = rrows - print(desc) - print(textwrap.indent(tabulate(rrows, headers=headers), ' ')) - -uncleared = [(r[0], r[2], r[4] or r[3], r[1]) for r in uncleared_rows] -report_path = os.path.join(os.getenv('CONSERVANCY_REPOSITORY', ''), reconciliation_report_path(account, lastDateInPeriod)) -# TODO: Make the directory if it doesn't exist. -with open(report_path, 'w') as f: - f.write(reconciliation_report(account, lastDateInPeriod, cleared_balance, uncleared, '1900-01-01', all_trans_balance, [])) -print(f'Wrote reconciliation report: {report_path}.') + grep_output_file = tempfile.NamedTemporaryFile(prefix='bc-reconcile-grep-output_', mode='w', delete=False) + beancount_file = args.beancount_file + account = args.account + cost_function = args.cost_function + statement_match = args.statement_match if args.statement_match else month + + QUERIES = { + f"00: CLEARED BAL ENDING DAY BEFORE {preDate}": + # $CONLEDGER -V -C -e "$preDate" bal "/$acct/" + f"""SELECT sum({cost_function}(position)) AS aa WHERE account = "{account}" + AND date < {preDate} AND META('bank-statement') != NULL""", + + f"01: ALL TRANSACTION BAL ENDING DAY BEFORE {preDate}": + # $CONLEDGER -V -e "$preDate" bal "/$acct/" + f"""SELECT sum({cost_function}(position)) AS aa WHERE account = "{account}" + AND date < {preDate}""", + + f"02: ALL TRANSACTION BAL, ending {lastDateInPeriod}": + # $CONLEDGER -V -e "$date" bal "/$acct/" + f"""SELECT sum({cost_function}(position)) AS aa WHERE account = "{account}" + AND date <= {lastDateInPeriod}""", + + f"03: UNCLEARED TRANSACTIONS, ending {lastDateInPeriod}": + f"""SELECT date, {cost_function}(position) as amt, ANY_META('check-id') as chknum, narration, payee, ENTRY_META('code') as code + WHERE account = "{account}" + AND date <= {lastDateInPeriod} AND META('bank-statement') = NULL + ORDER BY date, payee, narration""", + + "04: UNCLEARED TRANSACTION FILE, SUITABLE FOR GREP": + # $CONLEDGER -w -F "%(filename):%(beg_line): %(date) %(code) %(payee) %(amount)\n" --sort d -U -e "$date" reg "/$acct/" > "$TMPDIR/unreconciled-lines" + f"""SELECT ENTRY_META('filename') as file, META('lineno') as line, date, + {cost_function}(position) as amt, ANY_META('check-id') as chknum, narration, payee, ANY_META("entity") as entity, ENTRY_META('code') as c + WHERE account = "{account}" + AND date <= {lastDateInPeriod} AND META('bank-statement') = NULL + ORDER BY date, payee, narration""", + + f"05: CLEARED BALANCE ending {lastDateInPeriod}": + # $CONLEDGER -V -C -e "$date" bal "/$acct/" + f"""SELECT sum({cost_function}(position)) AS aa WHERE account = "{account}" + AND date <= {lastDateInPeriod} AND META('bank-statement') != NULL""", + + f"06: CLEARED SUBTRACTIONS on {month}'s statement": + # $CONLEDGER -V -C --limit "a > 0 and tag(\"Statement\") =~ /$statementSearchString/" bal "/$acct/" + f"""SELECT sum(number({cost_function}(position))) AS aa + WHERE account = "{account}" + and META("bank-statement") ~ "{statement_match}" and number({cost_function}(position)) < 0""", + + f"07: CLEARED ADDITIONS on {month}'s statement": + # $CONLEDGER -V -C --limit "a < 0 and tag(\"Statement\") =~ /$statementSearchString/" bal "/$acct/" + f"""SELECT sum(number({cost_function}(position))) AS aa + WHERE account = "{account}" + and META("bank-statement") ~ "{statement_match}" and number({cost_function}(position)) > 0""", + } + + # Run Beancount queries. + print(f"START RECONCILIATION FOR {account} ENDING {lastDateInPeriod} (previous end date {preDate})") + entries, _, options = loader.load_file(beancount_file) + uncleared_rows = [] # Hack to capture results of query 03. + cleared_balance = decimal.Decimal('0') + all_trans_balance = decimal.Decimal('0') + for desc, query in QUERIES.items(): + rtypes, rrows = run_query(entries, options, query, numberify=True) + if not rrows: + print(f'{desc:<55} {"N/A":>11}') + elif desc.startswith('04'): + homedir = os.getenv('HOME', '') + print(f'{desc}\n See {grep_output_file.name}') + grep_rows = [format_record_for_grep(row, homedir) for row in rrows] + print(tabulate(grep_rows), file=grep_output_file) + elif len(rrows) == 1 and isinstance(rrows[0][0], decimal.Decimal): + result = rrows[0][0] + print(f'{desc:<55} {result:11,.2f}') + if desc.startswith('02'): + all_trans_balance = result + if desc.startswith('05'): + cleared_balance = result + else: + headers = [c[0].capitalize() for c in rtypes] + if desc.startswith('03'): + uncleared_rows = rrows + print(desc) + print(textwrap.indent(tabulate(rrows, headers=headers), ' ')) + + uncleared = [(r[0], r[2], r[4] or r[3], r[1]) for r in uncleared_rows] + report_path = os.path.join(os.getenv('CONSERVANCY_REPOSITORY', ''), reconciliation_report_path(account, lastDateInPeriod)) + # TODO: Make the directory if it doesn't exist. + with open(report_path, 'w') as f: + f.write(reconciliation_report(account, lastDateInPeriod, cleared_balance, uncleared, '1900-01-01', all_trans_balance, [])) + print(f'Wrote reconciliation report: {report_path}.') + + +if __name__ == '__main__': + args = parse_args(sys.argv) + main(args) + + +def entry_point(): + args = parse_args(sys.argv) + main(args)