Changeset - 54d11f24377e
Parent rev.
Child rev.
[Not reviewed]
0 2 0
Ben Sturmfels (bsturmfels) - 3 years ago 2022-02-24 11:43:37
ben@sturm.com.au
reconcile: Add further typing info; update tests.
2 files changed with 75 insertions and 41 deletions:
conservancy_beancount/reconcile/statement_reconciler.py
72
38
tests/test_reconcile.py
3
3
0 comments (0 inline, 0 general)
conservancy_beancount/reconcile/statement_reconciler.py
➞
Show inline comments
...
 
@@ -60,3 +60,3 @@ from beancount import loader
 
from beancount.query.query import run_query
 
from colorama import Fore, Style
 
from colorama import Fore, Style  # type: ignore
 

			




	...
	
		
 
@@ -98,3 +98,3 @@ ZERO_RE = re.compile('^0+')


			




	
	
	
		
 

			




	
	
	
		
 
def remove_duplicate_words(text):


			




	
	
	
		
 
def remove_duplicate_words(text: str) -> str:


			




	
	
	
		
 
    unique_words = []


			




	...
	
		
 
@@ -125,3 +125,2 @@ def remove_payee_junk(payee: str) -> str:


			




	
	
	
		
 

			




	
	
	
		
 
# NOTE: Statement doesn't seem to give us a running balance or a final total.


			




	
	
	
		
 

			




	...
	
		
 
@@ -129,10 +128,12 @@ def read_transactions_from_csv(f: TextIO, standardize_statement_record: Callable


			




	
	
	
		
 
    reader = csv.DictReader(f)


			




	
	
	
		
 
    return sort_records([standardize_statement_record(row, reader.line_num) for row in reader])


			




	
	
	
		
 
    # The reader.line_num is the source line number, not the spreadsheet row


			




	
	
	
		
 
    # number due to multi-line records.


			




	
	
	
		
 
    return sort_records([standardize_statement_record(row, i) for i, row in enumerate(reader, 2)])


			




	
	
	
		
 

			




	
	
	
		
 

			




	
	
	
		
 
# Does the account you entered match the CSV?


			




	
	
	
		
 
# Is the CSV in the format we expect? (ie. did they download through the right interface?)


			




	
	
	
		
 
# Logical CSV line numbers


			




	
	
	
		
 
# CSV reconciliation report


			




	
	
	
		
 
# NOTE: Statement doesn't seem to give us a running balance or a final total.


			




	
	
	
		
 
# CSV reconciliation report.


			




	
	
	
		
 
# Merge helper script.


			




	
	
	
		
 

			




	
	
	
		
 

			




	
	
	
		
 
def standardize_amex_record(row: Dict, line: int) -> Dict:


			




	...
	
		
 
@@ -150,2 +151,16 @@ def standardize_amex_record(row: Dict, line: int) -> Dict:


			




	
	
	
		
 

			




	
	
	
		
 
def validate_amex_csv(sample: str, account: str) -> None:


			




	
	
	
		
 
    required_cols = {'Date', 'Amount', 'Description', 'Card Member'}


			




	
	
	
		
 
    reader = csv.DictReader(io.StringIO(sample))


			




	
	
	
		
 
    if reader.fieldnames and not required_cols.issubset(reader.fieldnames):


			




	
	
	
		
 
        sys.exit(f"This CSV doesn't seem to have the columns we're expecting, including: {', '.join(required_cols)}")


			




	
	
	
		
 

			




	
	
	
		
 

			




	
	
	
		
 
def validate_fr_csv(sample: str, account: str) -> None:


			




	
	
	
		
 
    required_cols = {'Date', 'Amount', 'Detail', 'Serial Num'}


			




	
	
	
		
 
    reader = csv.DictReader(io.StringIO(sample))


			




	
	
	
		
 
    if reader.fieldnames and not required_cols.issubset(reader.fieldnames):


			




	
	
	
		
 
        sys.exit(f"This CSV doesn't seem to have the columns we're expecting, including: {', '.join(required_cols)}")


			




	
	
	
		
 

			




	
	
	
		
 

			




	
	
	
		
 
def standardize_fr_record(row: Dict, line: int) -> Dict:


			




	...
	
		
 
@@ -183,3 +198,3 @@ def format_record(record: dict) -> str:


			




	
	
	
		
 

			




	
	
	
		
 
def format_multirecord(r1s, r2s, note):


			




	
	
	
		
 
def format_multirecord(r1s: list[dict], r2s: list[dict], note: str) -> list[list]:


			




	
	
	
		
 
    total = sum(x['amount'] for x in r2s)


			




	...
	
		
 
@@ -193,2 +208,3 @@ def format_multirecord(r1s, r2s, note):


			




	
	
	
		
 

			




	
	
	
		
 

			




	
	
	
		
 
def sort_records(records: List) -> List:


			




	...
	
		
 
@@ -197,5 +213,5 @@ def sort_records(records: List) -> List:


			




	
	
	
		
 

			




	
	
	
		
 
def first_word_exact_match(a, b):


			




	
	
	
		
 
def first_word_exact_match(a: str, b: str) -> float:


			




	
	
	
		
 
    if len(a) == 0 or len(b) == 0:


			




	
	
	
		
 
        return 0


			




	
	
	
		
 
        return 0.0


			




	
	
	
		
 
    first_a = a.split()[0].strip()


			




	...
	
		
 
@@ -205,6 +221,7 @@ def first_word_exact_match(a, b):


			




	
	
	
		
 
    else:


			




	
	
	
		
 
        return 0;


			




	
	
	
		
 
        return 0.0;


			




	
	
	
		
 

			




	
	
	
		
 
def payee_match(a, b):


			




	
	
	
		
 
    fuzzy_match = fuzz.token_set_ratio(a, b) / 100.00


			




	
	
	
		
 

			




	
	
	
		
 
def payee_match(a: str, b: str) -> float:


			




	
	
	
		
 
    fuzzy_match = float(fuzz.token_set_ratio(a, b) / 100.00)


			




	
	
	
		
 
    first_word_match = first_word_exact_match(a, b)


			




	...
	
		
 
@@ -212,3 +229,4 @@ def payee_match(a, b):


			




	
	
	
		
 

			




	
	
	
		
 
def records_match(r1: Dict, r2: Dict) -> Tuple[bool, str]:


			




	
	
	
		
 

			




	
	
	
		
 
def records_match(r1: Dict, r2: Dict) -> Tuple[float, List[str]]:


			




	
	
	
		
 
    """Do these records represent the same transaction?"""


			




	...
	
		
 
@@ -256,3 +274,3 @@ def records_match(r1: Dict, r2: Dict) -> Tuple[bool, str]:


			




	
	
	
		
 

			




	
	
	
		
 
def match_statement_and_books(statement_trans: list, books_trans: list):


			




	
	
	
		
 
def match_statement_and_books(statement_trans: List[Dict], books_trans: List[Dict]) -> Tuple[List[Tuple[List, List, List]], List[Dict], List[Dict]]:


			




	
	
	
		
 
    """


			




	...
	
		
 
@@ -268,5 +286,5 @@ def match_statement_and_books(statement_trans: list, books_trans: list):


			




	
	
	
		
 
    for r1 in statement_trans:


			




	
	
	
		
 
        best_match_score = 0


			




	
	
	
		
 
        best_match_score = 0.0


			




	
	
	
		
 
        best_match_index = None


			




	
	
	
		
 
        best_match_note = ''


			




	
	
	
		
 
        best_match_note = []


			




	
	
	
		
 
        matches_found = 0


			




	...
	
		
 
@@ -282,3 +300,4 @@ def match_statement_and_books(statement_trans: list, books_trans: list):


			




	
	
	
		
 
            # Don't try to make a second match against this books entry.


			




	
	
	
		
 
            del books_trans[best_match_index]


			




	
	
	
		
 
            if best_match_index is not None:


			




	
	
	
		
 
                del books_trans[best_match_index]


			




	
	
	
		
 
        else:


			




	...
	
		
 
@@ -290,3 +309,5 @@ def match_statement_and_books(statement_trans: list, books_trans: list):


			




	
	
	
		
 

			




	
	
	
		
 
def format_matches(matches, csv_statement: str, show_reconciled_matches):


			




	
	
	
		
 
# 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 = []


			




	...
	
		
 
@@ -308,6 +329,6 @@ def format_matches(matches, csv_statement: str, show_reconciled_matches):


			




	
	
	
		
 

			




	
	
	
		
 
def date_proximity(d1, d2):


			




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


			




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


			




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


			




	
	
	
		
 
    if diff > 60:


			




	
	
	
		
 
        return 0


			




	
	
	
		
 
        return 0.0


			




	
	
	
		
 
    else:


			




	...
	
		
 
@@ -315,3 +336,4 @@ def date_proximity(d1, d2):


			




	
	
	
		
 

			




	
	
	
		
 
def metadata_for_match(match, statement_filename, csv_filename):


			




	
	
	
		
 

			




	
	
	
		
 
def metadata_for_match(match: Tuple[List, List, List], statement_filename: str, csv_filename: str) -> List[Tuple[str, int, str]]:


			




	
	
	
		
 
    # Can we really ever have multiple statement entries? Probably not.


			




	...
	
		
 
@@ -363,6 +385,8 @@ def write_metadata_to_books(metadata_to_apply: List[Tuple[str, int, str]]) -> No


			




	
	
	
		
 

			




	
	
	
		
 
def get_repo_relative_path(path):


			




	
	
	
		
 

			




	
	
	
		
 
def get_repo_relative_path(path: str) -> str:


			




	
	
	
		
 
    return os.path.relpath(path, start=os.getenv('CONSERVANCY_REPOSITORY'))


			




	
	
	
		
 

			




	
	
	
		
 
def parse_path(path):


			




	
	
	
		
 

			




	
	
	
		
 
def parse_path(path: str) -> str:


			




	
	
	
		
 
    if not os.path.exists(path):


			




	...
	
		
 
@@ -371,3 +395,4 @@ def parse_path(path):


			




	
	
	
		
 

			




	
	
	
		
 
def parse_repo_relative_path(path):


			




	
	
	
		
 

			




	
	
	
		
 
def parse_repo_relative_path(path: str) -> str:


			




	
	
	
		
 
    if not os.path.exists(path):


			




	...
	
		
 
@@ -381,3 +406,4 @@ def parse_repo_relative_path(path):


			




	
	
	
		
 

			




	
	
	
		
 
def parse_args(argv):


			




	
	
	
		
 

			




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


			




	
	
	
		
 
    parser = argparse.ArgumentParser(description='Reconciliation helper')


			




	...
	
		
 
@@ -394,3 +420,4 @@ def parse_args(argv):


			




	
	
	
		
 

			




	
	
	
		
 
def totals(matches):


			




	
	
	
		
 

			




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


			




	
	
	
		
 
    total_matched = decimal.Decimal(0)


			




	...
	
		
 
@@ -408,3 +435,3 @@ def totals(matches):


			




	
	
	
		
 

			




	
	
	
		
 
def subset_match(statement_trans, books_trans):


			




	
	
	
		
 
def subset_match(statement_trans: List[dict], books_trans: List[dict]) ->  Tuple[List[Tuple[List, List, List]], List[Dict], List[Dict]]:


			




	
	
	
		
 
    matches = []


			




	...
	
		
 
@@ -415,5 +442,5 @@ def subset_match(statement_trans, books_trans):


			




	
	
	
		
 
    for k, group in groups:


			




	
	
	
		
 
        best_match_score = 0


			




	
	
	
		
 
        best_match_score = 0.0


			




	
	
	
		
 
        best_match_index = None


			




	
	
	
		
 
        best_match_note = ''


			




	
	
	
		
 
        best_match_note = []


			




	
	
	
		
 
        matches_found = 0


			




	...
	
		
 
@@ -432,7 +459,7 @@ def subset_match(statement_trans, books_trans):


			




	
	
	
		
 
        if best_match_score > 0.5 and matches_found == 1 and 'check-id mismatch' not in best_match_note or best_match_score > 0.8:


			




	
	
	
		
 
            if best_match_score <= 0.8:


			




	
	
	
		
 
                best_match_note.append('only one decent match')


			




	
	
	
		
 
            matches.append(([statement_trans[best_match_index]], group_items, best_match_note))


			




	
	
	
		
 
            del statement_trans[best_match_index]


			




	
	
	
		
 
            if best_match_index is not None:


			




	
	
	
		
 
                del statement_trans[best_match_index]


			




	
	
	
		
 
            for item in group_items:


			




	
	
	
		
 
                # TODO: Why?


			




	
	
	
		
 
                books_trans.remove(item)


			




	...
	
		
 
@@ -444,4 +471,5 @@ def subset_match(statement_trans, books_trans):


			




	
	
	
		
 

			




	
	
	
		
 
def process_unmatched(statement_trans, books_trans):


			




	
	
	
		
 
    matches = []


			




	
	
	
		
 

			




	
	
	
		
 
def process_unmatched(statement_trans: List[dict], books_trans: List[dict]) -> List[Tuple[List, List, List]]:


			




	
	
	
		
 
    matches: List[Tuple[List, List, List]] = []


			




	
	
	
		
 
    for r1 in statement_trans:


			




	...
	
		
 
@@ -452,3 +480,4 @@ def process_unmatched(statement_trans, books_trans):


			




	
	
	
		
 

			




	
	
	
		
 
def main(args):


			




	
	
	
		
 

			




	
	
	
		
 
def main(args: argparse.Namespace) -> None:


			




	
	
	
		
 
    # TODO: Should put in a sanity check to make sure the statement you're feeding


			




	...
	
		
 
@@ -459,4 +488,6 @@ def main(args):


			




	
	
	
		
 
    if 'AMEX' in args.account:


			




	
	
	
		
 
        validate_csv = validate_amex_csv


			




	
	
	
		
 
        standardize_statement_record = standardize_amex_record


			




	
	
	
		
 
    else:


			




	
	
	
		
 
        validate_csv = validate_fr_csv


			




	
	
	
		
 
        standardize_statement_record = standardize_fr_record


			




	...
	
		
 
@@ -464,2 +495,5 @@ def main(args):


			




	
	
	
		
 
    with open(args.csv_statement) as f:


			




	
	
	
		
 
        sample = f.read(200)


			




	
	
	
		
 
        validate_csv(sample, args.account)


			




	
	
	
		
 
        f.seek(0)


			




	
	
	
		
 
        statement_trans = read_transactions_from_csv(f, standardize_statement_record)


			




        

    


    
    

    

        

                

                    tests/test_reconcile.py
                

                

                  
                      
                        

                      ➞

                      
                        
                  

                  
                      
                  
                      
                  
                      
                  
                      
                  
                  
                

                

                    Show inline comments
                    
                

        

        

            



	...
	
		
 
@@ -220,3 +220,3 @@ def test_payee_mismatch_ok_when_only_one_that_amount_and_date():


			




	
	
	
		
 
    assert match_statement_and_books(statement, books) == (


			




	
	
	
		
 
        [([S3], [B3_payee_mismatch_1], ['payee mismatch', 'only one decent match'])],


			




	
	
	
		
 
        [([S3], [B3_payee_mismatch_1], ['payee mismatch'])],


			




	
	
	
		
 
        [],


			




	...
	
		
 
@@ -257,4 +257,4 @@ def test_metadata_for_match(monkeypatch):


			




	
	
	
		
 
    assert metadata_for_match(([S1], [B1], []), 'statement.pdf', 'statement.csv') == [


			




	
	
	
		
 
        ('2022/imports.beancount', 777, '    bank-statement: statement.pdf'),


			




	
	
	
		
 
        ('2022/imports.beancount', 777, '    bank-statement-csv: statement.csv:222'),


			




	
	
	
		
 
        ('2022/imports.beancount', 777, '    bank-statement: "statement.pdf"'),


			




	
	
	
		
 
        ('2022/imports.beancount', 777, '    bank-statement-csv: "statement.csv:222"'),


			




	
	
	
		
 
    ]


			




        

    



    


    



    



      

      





    
    0 comments (0 inline, 0 general)
    





    


  

  







    


    

  


    








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