Changeset - 31146b884361
[Not reviewed]
0 1 0
Ben Sturmfels (bsturmfels) - 2 years ago 2022-02-09 01:29:44
ben@sturm.com.au
reconcile: Add initial FR support to statement reconciler.
1 file changed with 47 insertions and 13 deletions:
0 comments (0 inline, 0 general)
conservancy_beancount/reconcile/prototype_amex_reconciler.py
Show inline comments
...
 
@@ -7,3 +7,4 @@ $ python3 conservancy_beancount/reconcile/prototype_amex_reconciler.py \
 
  --beancount-file=$HOME/conservancy/beancount/books/2021.beancount \
 
  --amex-csv=$HOME/conservancy/confidential/2021-09-10_AMEX_activity.csv
 
  --csv-statement=$HOME/conservancy/confidential/2021-09-10_AMEX_activity.csv \
 
  --account=amex
 

	
...
 
@@ -14,2 +15,13 @@ directives, it just checks to see that similar directives are already present.
 

	
 
Problem this attempts to address:
 
 - errors in the books take hours to find during reconciliation ("you're entering a world of pain"
 
 - balance checks are manually updated in svn/Financial/Ledger/sanity-check-balances.yaml
 
 - paper checks are entered in the books when written, but may not be cashed until months later (reconcile errors)
 
 - adding statement/reconciliation metadata to books is manual and prone to mistakes
 
 - creating reconciliation reports
 
 - normally transactions are entered manually, but potentially could create transaction directives (a.k.a. importing)
 
 - jumping to an individual transaction in the books isn't trivial - Emacs grep mode is helpful
 

	
 
Q. How are reconciliation reports created currently? How do you read them?
 

	
 
TODO/ISSUES:
...
 
@@ -20,3 +32,2 @@ import argparse
 
import csv
 
import collections
 
import datetime
...
 
@@ -31,2 +42,3 @@ from thefuzz import fuzz  # type: ignore
 

	
 

	
 
def standardize_amex_record(row: Dict) -> Dict:
...
 
@@ -36,3 +48,3 @@ def standardize_amex_record(row: Dict) -> Dict:
 
        'amount': -1 * decimal.Decimal(row['Amount']),
 
        'payee': row['Description'],
 
        'payee': row['Description'] or '',
 
    }
...
 
@@ -51,5 +63,12 @@ def standardize_beancount_record(row) -> Dict:  # type: ignore[no-untyped-def]
 

	
 
def standardize_fr_record(row: Dict) -> Dict:
 
    return {
 
        'date': datetime.datetime.strptime(row['Date'], '%m/%d/%Y').date(),
 
        'amount': decimal.Decimal(row['Amount']),
 
        'payee': row['Detail'] or '',
 
    }
 

	
 

	
 
def format_record(record: Dict) -> str:
 
    return f"{record['date'].isoformat()}: {record['amount']:>8} {record['payee'][:20]:<20}"
 
    return f"{record['date'].isoformat()}: {record['amount']:>11} {record['payee'][:20]:<20}"
 

	
...
 
@@ -76,3 +95,4 @@ parser = argparse.ArgumentParser(description='Reconciliation helper')
 
parser.add_argument('--beancount-file', required=True)
 
parser.add_argument('--amex-csv', required=True)
 
parser.add_argument('--csv-statement', required=True)
 
parser.add_argument('--account', required=True, help='eg. Liabilities:CreditCard:AMEX')
 
parser.add_argument('--grep-output-filename')
...
 
@@ -82,5 +102,12 @@ args = parser.parse_args()
 

	
 
with open(args.amex_csv) as f:
 
# 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:
 
    standardize_statement_record = standardize_amex_record
 
else:
 
    standardize_statement_record = standardize_fr_record
 

	
 
with open(args.csv_statement) as f:
 
    reader = csv.DictReader(f)
 
    statement_trans = sort_records([standardize_amex_record(row) for row in reader])
 
    statement_trans = sort_records([standardize_statement_record(row) for row in reader])
 

	
...
 
@@ -100,3 +127,3 @@ entries, _, options = loader.load_file(args.beancount_file)
 
# 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 = 'Liabilities:CreditCard:AMEX' and date >= {begin_date} and date <= {end_date}"
 
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}"
 
result_types, result_rows = run_query(
...
 
@@ -106,2 +133,4 @@ result_types, result_rows = run_query(
 
)
 

	
 

	
 
books_trans = sort_records([standardize_beancount_record(row) for row in result_rows])
...
 
@@ -126,3 +155,3 @@ for r1 in statement_trans:
 
            if not r2['statement']:
 
                metadata_to_apply.append((r2['filename'], r2['line'], f'    bank-statement: "{args.amex_csv}"\n'))
 
                metadata_to_apply.append((r2['filename'], r2['line'], f'    bank-statement: "{args.csv_statement}"\n'))
 
            books_trans.remove(r2)
...
 
@@ -130,5 +159,5 @@ for r1 in statement_trans:
 
    else:
 
        matches.append([r1['date'], f'{format_record(r1)}    -->    {" ":^41}  ✗ Not in books'])
 
        matches.append([r1['date'], f'{format_record(r1)}    -->    {" ":^44}  ✗ Not in books'])
 
for r2 in books_trans:
 
    matches.append([r2['date'], f'{" ":^41}    -->    {format_record(r2)}  ✗ Not on statement'])
 
    matches.append([r2['date'], f'{" ":^44}    -->    {format_record(r2)}  ✗ Not on statement'])
 

	
...
 
@@ -141,2 +170,3 @@ print(f'------------------------------------------------------------------------
 

	
 
# Write statement metadata back to books
 
if metadata_to_apply:
...
 
@@ -145,8 +175,12 @@ if metadata_to_apply:
 
        files = {}
 
        for filename, line, metadata in metadata_to_apply:
 
        # Query results aren't necessarily sequential in a file, so need to sort
 
        # so that our line number offsets work.
 
        for filename, line, metadata in sorted(metadata_to_apply):
 
            if filename not in files:
 
                with open(filename, 'r') as f:
 
                    files[filename] = [0, f.readlines()]
 
                    print(f'Opening {filename}.')
 
                    files[filename] = [0, f.readlines()]  # Offset and contents
 
            files[filename][1].insert(line + files[filename][0], metadata)
 
            files[filename][0] += 1
 
            print(f'File {filename} offset {files[filename][0]}')
 
        for filename in files:
0 comments (0 inline, 0 general)