payee. Matches are ranked, so where there is only one decent match for

an amount/date this is accepted, but if there are multiple similar

candidates it will refuse to guess.

The reconciler will also attempt to identify where a single statement

entry has been split out into multiple Beancount postings, such as a

single bank transfer representing health insurance for multiple

employees.

Run it like this:

$ statement_reconciler \

Background:

Beancount users often write importers to create bookkeeping entries

direct from a bank statement or similar. That approach automates data

entry and reconciliation in one step. In some cases though, it's

useful to manually enter transactions and reconcile them later

on. This workflow helpful in cases like writing a paper check when

there's a time lag between committing to making a payment and the

funds being debited. That's the workflow we're using here.

Conservancy currently enter data by hand rather than using Beancount

# TODO: Return list of tuples (instead of list of lists).

def format_matches(matches: List, csv_statement: str, show_reconciled_matches: bool) -> List[List]:

    match_output = []

    for r1s, r2s, note in matches:

        note = ', '.join(note)

        note = ': ' + note if note else note

        if r1s and r2s:

            if show_reconciled_matches or not all(x['bank_statement'] for x in r2s):

                if len(r2s) == 1:

                else:

                    match_output.extend(format_multirecord(r1s, r2s, note))

        elif r1s:

            match_output.append([r1s[0]['date'], Fore.RED + Style.BRIGHT + f'{format_record(r1s[0])}  →  {" ":^59}  ✗ NOT IN BOOKS ({os.path.basename(csv_statement)}:{r1s[0]["line"]})' + Style.RESET_ALL])

        else:

            match_output.append([r2s[0]['date'], Fore.RED + Style.BRIGHT + f'{" ":^59}  →  {format_record(r2s[0])}  ✗ NOT ON STATEMENT ({os.path.basename(r2s[0]["filename"])}:{r2s[0]["line"]})' + Style.RESET_ALL])

    return match_output

def date_proximity(d1: datetime.date, d2: datetime.date) -> float:

    diff = abs(int((d1 - d2).days))

    if diff > 60:

    number_text = number_text.replace(',', '')

    return decimal.Decimal(number_text)

def parse_arguments(argv: List[str]) -> argparse.Namespace:

    parser = argparse.ArgumentParser(prog=PROGNAME, description='Reconciliation helper')

    cliutil.add_version_argument(parser)

    cliutil.add_loglevel_argument(parser)

    parser.add_argument('--beancount-file', required=True, type=parse_path)

    parser.add_argument('--csv-statement', required=True, type=parse_repo_relative_path)

    parser.add_argument('--bank-statement', required=True, type=parse_repo_relative_path)

    parser.add_argument('--account', required=True, help='eg. Liabilities:CreditCard:AMEX')

    # parser.add_argument('--report-group-regex')

    parser.add_argument('--show-reconciled-matches', action='store_true')

    args = parser.parse_args(args=argv)

    return args

def totals(matches: List[Tuple[List, List, List]]) -> Tuple[decimal.Decimal, decimal.Decimal, decimal.Decimal]:

    total_matched = decimal.Decimal(0)

    total_missing_from_books = decimal.Decimal(0)

    total_missing_from_statement = decimal.Decimal(0)

    for statement_entries, books_entries, _ in matches:

        if statement_entries and books_entries:

            total_matched += sum(c['amount'] for c in statement_entries)

        elif statement_entries:

    begin_date = statement_trans[0]['date']

    end_date = statement_trans[-1]['date']

    # Do we traverse and filter the in-memory entries list and filter that, or do we

    # use Beancount Query Language (BQL) to get a list of transactions? Currently

    # using BQL.

    #

    # beancount.query.query_compile.compile() and

    # beancount.query.query_execute.filter_entries() look useful in this respect,

    # but I'm not clear on how to use compile(). An example would help.

    entries, _, options = loader.load_file(args.beancount_file)

    # 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 line, META("bank-statement") AS bank_statement, date, number(cost(position)), payee, ENTRY_META("entity") as entity, ANY_META("check-id") as check_id, narration where account = "{args.account}" and date >= {begin_date} and date <= {end_date}'

    _, result_rows = run_query(entries, options, query)

    books_trans = sort_records([standardize_beancount_record(row) for row in result_rows])

    matches, remaining_statement_trans, remaining_books_trans = match_statement_and_books(statement_trans, books_trans)

    subset_matches, remaining_statement_trans, remaining_books_trans = subset_match(remaining_statement_trans, remaining_books_trans)

    matches.extend(subset_matches)

    unmatched = process_unmatched(remaining_statement_trans, remaining_books_trans)

    matches.extend(unmatched)

    match_output = format_matches(matches, args.csv_statement, args.show_reconciled_matches)

    _, total_missing_from_books, total_missing_from_statement = totals(matches)

    print('-' * 155)

    print('-' * 155)

    for _, output in sorted(match_output, key=lambda x: x[0]):

        print(output)

    print('-' * 155)

    print('-' * 155)

    # Write statement metadata back to books

    metadata_to_apply = []

    for match in matches:

        metadata_to_apply.extend(metadata_for_match(match, args.bank_statement, args.csv_statement))

    if metadata_to_apply and not args.non_interactive:

        print('Mark matched transactions as reconciled in the books? (y/N) ', end='')

        if input().lower() == 'y':

            write_metadata_to_books(metadata_to_apply)