From 0968f7f051b82c482e92706c75f0f4e80d238504 2023-01-13 02:58:36
From: Ben Sturmfels <ben@sturm.com.au>
Date: 2023-01-13 02:58:36
Subject: [PATCH] reconciler: Handle comma separators in some FR statements

---

diff --git a/conservancy_beancount/reconcile/statement_reconciler.py b/conservancy_beancount/reconcile/statement_reconciler.py
index 5ee2115c096553031299a44e5f8aa0e8cd360bae..e68898d70c7bf657f575d06d8f4262431571d59b 100644
--- a/conservancy_beancount/reconcile/statement_reconciler.py
+++ b/conservancy_beancount/reconcile/statement_reconciler.py
@@ -194,6 +194,11 @@ def read_transactions_from_csv(f: TextIO, standardize_statement_record: Callable
     return sort_records([standardize_statement_record(row, i) for i, row in enumerate(reader, 2)])
 
 
+def parse_amount(amount: str) -> decimal.Decimal:
+    """Parse amounts and handle comma separators as seen in some FR statements."""
+    return decimal.Decimal(amount.replace(',', ''))
+
+
 def validate_amex_csv(sample: str, account: str) -> None:
     required_cols = {'Date', 'Amount', 'Description', 'Card Member'}
     reader = csv.DictReader(io.StringIO(sample))
@@ -206,7 +211,7 @@ def standardize_amex_record(row: Dict, line: int) -> Dict:
     # NOTE: Statement doesn't seem to give us a running balance or a final total.
     return {
         'date': datetime.datetime.strptime(row['Date'], '%m/%d/%Y').date(),
-        'amount': -1 * decimal.Decimal(row['Amount']),
+        'amount': -1 * parse_amount(row['Amount']),
         # Descriptions have too much noise, so taking just the start
         # significantly assists the fuzzy matching.
         'payee': remove_payee_junk(row['Description'] or '')[:20],
@@ -225,7 +230,7 @@ def validate_fr_csv(sample: str, account: str) -> None:
 def standardize_fr_record(row: Dict, line: int) -> Dict:
     return {
         'date': datetime.datetime.strptime(row['Date'], '%m/%d/%Y').date(),
-        'amount': decimal.Decimal(row['Amount']),
+        'amount': parse_amount(row['Amount']),
         'payee': remove_payee_junk(row['Detail'] or '')[:20],
         'check_id': row['Serial Num'].lstrip('0'),
         'line': line,
diff --git a/tests/test_reconcile.py b/tests/test_reconcile.py
index 893dff3c0b1851c48e7a81eb8f3633ea7c64c47b..aa368a882426e20bddfeb7784b13faf8650edfd9 100644
--- a/tests/test_reconcile.py
+++ b/tests/test_reconcile.py
@@ -5,15 +5,17 @@ import tempfile
 import textwrap
 
 from conservancy_beancount.reconcile.statement_reconciler import (
-    match_statement_and_books,
-    remove_payee_junk,
     date_proximity,
-    remove_duplicate_words,
-    payee_match,
+    match_statement_and_books,
     metadata_for_match,
-    write_metadata_to_books,
-    totals,
+    payee_match,
+    remove_duplicate_words,
+    remove_payee_junk,
+    standardize_amex_record,
+    standardize_fr_record,
     subset_match,
+    totals,
+    write_metadata_to_books,
 )
 
 # These data structures represent individual transactions as taken from the
@@ -341,3 +343,42 @@ def test_subset_passes_through_all_non_matches():
         [S1],  # No match: preserved intact
         [B2, B3_next_day, B3_next_week]  # No match: preserved intact
     )
+
+
+def test_handles_fr_record_with_comma_separators():
+    # CSV would look something like:
+    #
+    # "Date","ABA Num","Currency","Account Num","Account Name","Description","BAI Code","Amount","Serial Num","Ref Num","Detail"
+    # "02/07/2022",,,,,,,"10,000.00",,,"XXXX"
+    input_row = {
+        'Date': '02/07/2022',
+        'Amount': '10,000.00',
+        'Detail': 'XXXX',
+        'Serial Num': '',
+    }
+    expected = {
+        'date': datetime.date(2022, 2, 7),
+        'amount': decimal.Decimal('10000'),
+        'payee': 'XXXX',
+        'check_id': '',
+        'line': 1,
+    }
+    assert standardize_fr_record(input_row, line=1) == expected
+
+
+def test_handles_amex_record_with_comma_separators():
+    # This insn't typically a problem with AMEX, but adding for completeness.
+    input_row = {
+        'Date': '02/07/2022',
+        'Amount': '-10,000.00',  # Amounts are from Bank's perspective/negated.
+        'Description': 'XXXX',
+        'Serial Num': '',
+    }
+    expected = {
+        'date': datetime.date(2022, 2, 7),
+        'amount': decimal.Decimal('10000'),
+        'payee': 'XXXX',
+        'check_id': '',
+        'line': 1,
+    }
+    assert standardize_amex_record(input_row, line=1) == expected