Changeset - da5caa3e6cd1
Parent rev.
Child rev.
[Not reviewed]
0 1 0
Ben Sturmfels (bsturmfels) - 2 years ago 2022-03-02 07:26:19
ben@sturm.com.au
reconcile: Remove totals, highlight payee mismatches orange.
1 file changed with 18 insertions and 19 deletions:
conservancy_beancount/reconcile/statement_reconciler.py
18
19
0 comments (0 inline, 0 general)
conservancy_beancount/reconcile/statement_reconciler.py
➞
Show inline comments
...
 
@@ -21,11 +21,10 @@ employees.
 
Run it like this:
 

			




	
	
	
		
 
$ statement_reconciler \


			




	
	
	
		
 
  --beancount-file=2021.beancount \


			




	
	
	
		
 
  --account=Liabilities:CreditCard:AMEX \


			




	
	
	
		
 
  --csv-statement=2021-09-10_AMEX_activity.csv \


			




	
	
	
		
 
  --bank-statement=2021-09-10_AMEX_activity.csv \


			




	
	
	
		
 
  --statement-balance=1000


			




	
	
	
		
 
  --beancount-file 2021.beancount \


			




	
	
	
		
 
  --account Liabilities:CreditCard:AMEX \


			




	
	
	
		
 
  --csv-statement ~/svn/2021-09-10_AMEX_activity.csv \


			




	
	
	
		
 
  --bank-statement ~/svn/2021-09-10_AMEX_activity.pdf


			




	
	
	
		
 

			




	
	
	
		
 
Background:


			




	
	
	
		
 

			




	...
	
		
 
@@ -374,7 +373,10 @@ def format_matches(matches: List, csv_statement: str, show_reconciled_matches: b


			




	
	
	
		
 
        if r1s and r2s:


			




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


			




	
	
	
		
 
                if len(r2s) == 1:


			




	
	
	
		
 
                    match_output.append([r1s[0]['date'], f'{format_record(r1s[0])}  →  {format_record(r2s[0])}  ✓ Matched{note}'])


			




	
	
	
		
 
                    entry = [r1s[0]['date'], f'{format_record(r1s[0])}  →  {format_record(r2s[0])}  ✓ Matched{note}']


			




	
	
	
		
 
                    if 'payee mismatch' in note:


			




	
	
	
		
 
                        entry[1] = Fore.YELLOW + Style.BRIGHT + entry[1] + Style.RESET_ALL


			




	
	
	
		
 
                    match_output.append(entry)


			




	
	
	
		
 
                else:


			




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


			




	
	
	
		
 
        elif r1s:


			




	...
	
		
 
@@ -474,11 +476,9 @@ def parse_arguments(argv: List[str]) -> argparse.Namespace:


			




	
	
	
		
 
    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('--grep-output-filename')


			




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


			




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


			




	
	
	
		
 
    parser.add_argument('--statement-balance', type=parse_decimal_with_separator, required=True, help="A.K.A \"cleared balance\" taken from the end of the period on the PDF statement. Required because CSV statements don't include final or running totals")


			




	
	
	
		
 
    parser.add_argument('--non-interactive', action='store_true', help="Don't prompt to write to the books")


			




	
	
	
		
 
    parser.add_argument('--non-interactive', action='store_true', help="Don't prompt to write to the books")    # parser.add_argument('--statement-balance', type=parse_decimal_with_separator, required=True, help="A.K.A \"cleared balance\" taken from the end of the period on the PDF statement. Required because CSV statements don't include final or running totals")


			




	
	
	
		
 
    args = parser.parse_args(args=argv)


			




	
	
	
		
 
    return args


			




	
	
	
		
 

			




	...
	
		
 
@@ -581,10 +581,10 @@ def main(arglist: Optional[Sequence[str]] = None,


			




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


			




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


			




	
	
	
		
 

			




	
	
	
		
 
    books_balance_query = f"""SELECT sum(COST(position)) AS aa WHERE account = "{args.account}"


			




	
	
	
		
 
        AND date <= {end_date.isoformat()}"""


			




	
	
	
		
 
    _, result_rows = run_query(entries, options, books_balance_query, numberify=True)


			




	
	
	
		
 
    books_balance = result_rows[0][0] if result_rows else 0


			




	
	
	
		
 
    # books_balance_query = f"""SELECT sum(COST(position)) AS aa WHERE account = "{args.account}"


			




	
	
	
		
 
    #     AND date <= {end_date.isoformat()}"""


			




	
	
	
		
 
    # _, result_rows = run_query(entries, options, books_balance_query, numberify=True)


			




	
	
	
		
 
    # books_balance = result_rows[0][0] if result_rows else 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!


			




	...
	
		
 
@@ -604,16 +604,15 @@ def main(arglist: Optional[Sequence[str]] = None,


			




	
	
	
		
 
    _, total_missing_from_books, total_missing_from_statement = totals(matches)


			




	
	
	
		
 

			




	
	
	
		
 
    print('-' * 155)


			




	
	
	
		
 
    print(f'{"Statement transaction":<52}            {"Books transaction":<58}   Notes')


			




	
	
	
		
 
    statement_heading = f'Statement transactions {begin_date} to {end_date}'


			




	
	
	
		
 
    print(f'{statement_heading:<52}            {"Books transactions":<58}   Notes')


			




	
	
	
		
 
    print('-' * 155)


			




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


			




	
	
	
		
 
        print(output)


			




	
	
	
		
 
    print('-' * 155)


			




	
	
	
		
 
    print(f'Statement period {begin_date} to {end_date}')


			




	
	
	
		
 
    print(f'Statement/cleared balance:    {args.statement_balance:12,.2f}    (as provided by you)')


			




	
	
	
		
 
    print(f'Books balance:                {books_balance:12,.2f}    (all transactions, includes unreconciled)')


			




	
	
	
		
 
    print(f'Total not on statement:       {total_missing_from_statement:12,.2f}')


			




	
	
	
		
 
    print(f'Total not on books:           {total_missing_from_books:12,.2f}')


			




	
	
	
		
 
    print(f'Sub-total not on statement: {total_missing_from_statement:12,.2f}')


			




	
	
	
		
 
    print(f'Sub-total not in books:     {total_missing_from_books:12,.2f}')


			




	
	
	
		
 
    print(f'Total:                      {total_missing_from_statement + total_missing_from_books:12,.2f}')


			




	
	
	
		
 
    print('-' * 155)


			




	
	
	
		
 

			




	
	
	
		
 
    # Write statement metadata back to books


			




        

    



    


    



    



      

      





    
    0 comments (0 inline, 0 general)
    





    


  

  







    


    

  


    








    
        Server instance: kapok.sfconservancy.org-2944
    
    
        This site is powered by
            Kallithea 0.6.3,
        which is
        © 2010–2020 by various authors & licensed under GPLv3.
            – Support
        – Privacy Policy