Merge pull request #74 from szabootibor/master

Generate balance assertions (solution for #69)

2 files changed, 285 insertions, 9 deletions

diff --git a/beancount_ing_diba/ec.py b/beancount_ing_diba/ec.py
index f9afb2a..611a236 100644
--- a/beancount_ing_diba/ec.py
+++ b/beancount_ing_diba/ec.py
@@ -1,7 +1,8 @@
 import csv
-from datetime import datetime
+from datetime import datetime, timedelta
 from itertools import count
 import re
+import warnings
 
 from beancount.core.amount import Amount
 from beancount.core import data
@@ -50,7 +51,6 @@ class ECImporter(importer.ImporterProtocol):
 
         self._date_from = None
         self._date_to = None
-        self._balance = None
         self._line_index = -1
 
     def file_account(self, _):
@@ -173,9 +173,11 @@ class ECImporter(importer.ImporterProtocol):
                         splits[1], '%d.%m.%Y'
                     ).date()
                 elif key == 'Saldo':
-                    amount, currency = values
-
-                    self._balance = Amount(_format_number_de(amount), currency)
+                    # actually this is not a useful balance, because it is
+                    # valid on the date of generating the CSV (see first header
+                    # line) and not on the closing date of the transactions
+                    # (see metadata field 'Zeitraum')
+                    pass
 
             # Empty line
             _read_empty_line()
@@ -183,7 +185,19 @@ class ECImporter(importer.ImporterProtocol):
             # Pre-header line (or optional sorting line)
             line = _read_line()
 
+            descending_by_date = ascending_by_date = None
+
             if line.startswith('Sortierung'):
+                if re.match('.*Datum absteigend', line):
+                    descending_by_date = True
+                elif re.match('.*Datum aufsteigend', line):
+                    ascending_by_date = True
+                else:
+                    warnings.warn(
+                        f'{file_.name}:{self._line_index}: '
+                        'balance assertions can only be generated '
+                        'if transactions are sorted by date'
+                    )
                 _read_empty_line()
 
                 line = _read_line()
@@ -212,9 +226,16 @@ class ECImporter(importer.ImporterProtocol):
 
             field_names = remap(next(reader))
 
+            # memoize first and last transactions for balance assertion
+            first_transaction = last_transaction = None
+
             for row in reader:
                 line = dict(zip(field_names, row))
 
+                # Mark first and last transaction together with line numbers
+                last_transaction = (self._line_index, line)
+                if first_transaction is None:
+                    first_transaction = last_transaction
                 date = line['Buchung']
                 payee = line['Auftraggeber/Empfänger']
                 booking_text = line['Buchungstext']
@@ -248,4 +269,55 @@ class ECImporter(importer.ImporterProtocol):
 
                 self._line_index += 1
 
+            def balance_assertion(transaction, opening=False, closing=False):
+                lineno = transaction[0]
+                line = transaction[1]
+                balance = _format_number_de(line['Saldo'])
+                if opening:
+                    # calculate balance before the first transaction
+                    # Currencies must match for subtraction
+                    if line['Währung_1'] != line['Währung_2']:
+                        warnings.warn(
+                            f"{file_.name}:{lineno} "
+                            "opening balance can not be generated "
+                            "due to currency mismatch: "
+                            f"{line['Währung_1']} <> {line['Währung_2']}"
+                        )
+                        return []
+                    balance -= _format_number_de(line['Betrag'])
+                    balancedate = self._date_from
+                if closing:
+                    # balance after the last transaction:
+                    # next day's opening balance
+                    balancedate = self._date_to + timedelta(days=1)
+                return [
+                    data.Balance(
+                        data.new_metadata(file_.name, lineno),
+                        balancedate,
+                        self.account,
+                        Amount(balance, line['Währung_1']),
+                        None,
+                        None,
+                    )
+                ]
+
+            opening_transaction = closing_transaction = None
+
+            # Determine first and last (by date) transactions
+            if ascending_by_date:
+                opening_transaction = first_transaction
+                closing_transaction = last_transaction
+            if descending_by_date:
+                closing_transaction = first_transaction
+                opening_transaction = last_transaction
+
+            if opening_transaction:
+                entries.extend(
+                    balance_assertion(opening_transaction, opening=True)
+                )
+            if closing_transaction:
+                entries.extend(
+                    balance_assertion(closing_transaction, closing=True)
+                )
+
         return entries
diff --git a/tests/test_ec_importer.py b/tests/test_ec_importer.py
index 797c88d..b20deaf 100644
--- a/tests/test_ec_importer.py
+++ b/tests/test_ec_importer.py
@@ -4,6 +4,7 @@ from tempfile import gettempdir
 from textwrap import dedent
 from unittest import TestCase
 import os
+from datetime import date
 
 from beancount_ing_diba.ec import BANKS, ECImporter, PRE_HEADER
 
@@ -274,7 +275,8 @@ class ECImporterTestCase(TestCase):
         with open(self.filename) as fd:
             transactions = importer.extract(fd)
 
-        self.assertEqual(len(transactions), 1)
+        # 1 transaction + 2 balance assertions
+        self.assertEqual(len(transactions), 1 + 2)
 
     def test_category_included(self):
         with open(self.filename, 'wb') as fd:
@@ -306,7 +308,8 @@ class ECImporterTestCase(TestCase):
         with open(self.filename) as fd:
             transactions = importer.extract(fd)
 
-        self.assertEqual(len(transactions), 1)
+        # 1 transaction + 2 balance assertions
+        self.assertEqual(len(transactions), 1 + 2)
 
     def test_no_second_header(self):
         with open(self.filename, 'wb') as fd:
@@ -337,7 +340,8 @@ class ECImporterTestCase(TestCase):
         with open(self.filename) as fd:
             transactions = importer.extract(fd)
 
-        self.assertEqual(len(transactions), 1)
+        # 1 transaction + 2 balance assertions
+        self.assertEqual(len(transactions), 1 + 2)
 
     def test_duplicate_waehrung_field_handled_correctly(self):
         with open(self.filename, 'wb') as fd:
@@ -368,5 +372,205 @@ class ECImporterTestCase(TestCase):
         with open(self.filename) as fd:
             transactions = importer.extract(fd)
 
-        self.assertEqual(len(transactions), 1)
+        # 1 transaction + 1 balance assertion
+        # (opening balance cannot be calculated due to currency mismatch)
+        self.assertEqual(len(transactions), 1 + 1)
         self.assertEqual(transactions[0].postings[0].units.currency, 'EUR')
+
+    def test_bad_sorting_no_balances(self):
+        with open(self.filename, 'wb') as fd:
+            fd.write(
+                self._format_data(
+                    '''
+                    Umsatzanzeige;Datei erstellt am: 25.07.2018 12:00
+
+                    IBAN;{formatted_iban}
+                    Kontoname;Extra-Konto
+                    Bank;ING-DiBa
+                    Kunde;{user}
+                    Zeitraum;01.06.2018 - 30.06.2018
+                    Saldo;5.000,00;EUR
+
+                    Sortierung;Betrag absteigend
+
+                    {pre_header}
+
+                    "Buchung";"Valuta";"Auftraggeber/Empfänger";"Buchungstext";"Kategorie";"Verwendungszweck";"Saldo";"Währung";"Betrag";"Währung"
+                    08.06.2018;08.06.2018;REWE Filialen Voll;Gutschrift;Kategorie;REWE SAGT DANKE;1.234,00;USD;-500,00;EUR
+                    '''  # NOQA
+                )
+            )
+
+        importer = ECImporter(self.iban, 'Assets:ING-DiBa:Extra', self.user)
+
+        with open(self.filename) as fd:
+            transactions = importer.extract(fd)
+
+        # 1 transaction + no balance assertion (not sorted by date)
+        self.assertEqual(len(transactions), 1)
+
+    def test_ascending_by_date_single(self):
+        with open(self.filename, 'wb') as fd:
+            fd.write(
+                self._format_data(
+                    '''
+                    Umsatzanzeige;Datei erstellt am: 25.07.2018 12:00
+
+                    IBAN;{formatted_iban}
+                    Kontoname;Extra-Konto
+                    Bank;ING-DiBa
+                    Kunde;{user}
+                    Zeitraum;01.06.2018 - 30.06.2018
+                    Saldo;5.000,00;EUR
+
+                    Sortierung;Datum aufsteigend
+
+                    {pre_header}
+
+                    "Buchung";"Valuta";"Auftraggeber/Empfänger";"Buchungstext";"Kategorie";"Verwendungszweck";"Saldo";"Währung";"Betrag";"Währung"
+                    08.06.2018;08.06.2018;REWE Filialen Voll;Gutschrift;Kategorie;REWE SAGT DANKE;1.234,00;EUR;-500,00;EUR
+                    '''  # NOQA
+                )
+            )
+
+        importer = ECImporter(self.iban, 'Assets:ING-DiBa:Extra', self.user)
+
+        with open(self.filename) as fd:
+            transactions = importer.extract(fd)
+
+        # 1 transaction + 2 balance assertions
+        self.assertEqual(len(transactions), 1 + 2)
+        # Test opening balance
+        self.assertEqual(transactions[1].date, date(2018, 6, 1))
+        self.assertEqual(transactions[1].amount.number, 1734.0)
+        self.assertEqual(transactions[1].amount.currency, 'EUR')
+        # Test closing balance
+        self.assertEqual(transactions[2].date, date(2018, 7, 1))
+        self.assertEqual(transactions[2].amount.number, 1234.0)
+        self.assertEqual(transactions[2].amount.currency, 'EUR')
+
+    def test_ascending_by_date_multiple(self):
+        with open(self.filename, 'wb') as fd:
+            fd.write(
+                self._format_data(
+                    '''
+                    Umsatzanzeige;Datei erstellt am: 25.07.2018 12:00
+
+                    IBAN;{formatted_iban}
+                    Kontoname;Extra-Konto
+                    Bank;ING-DiBa
+                    Kunde;{user}
+                    Zeitraum;01.06.2018 - 30.06.2018
+                    Saldo;5.000,00;EUR
+
+                    Sortierung;Datum aufsteigend
+
+                    {pre_header}
+
+                    "Buchung";"Valuta";"Auftraggeber/Empfänger";"Buchungstext";"Kategorie";"Verwendungszweck";"Saldo";"Währung";"Betrag";"Währung"
+                    08.06.2018;08.06.2018;REWE Filialen Voll;Gutschrift;Kategorie;REWE SAGT DANKE;1.234,00;EUR;-500,00;EUR
+                    08.06.2018;08.06.2018;LIDL;Lastschrift;Kategorie;LIDL SAGT DANKE;1.200,00;EUR;-34,00;EUR
+                    15.06.2018;08.06.2018;LIDL;Lastschrift;Kategorie;LIDL SAGT DANKE;1.100,00;EUR;-100,00;EUR
+                    15.06.2018;08.06.2018;LIDL;Lastschrift;Kategorie;LIDL SAGT DANKE;1.000,00;EUR;-100,00;EUR
+                    '''  # NOQA
+                )
+            )
+
+        importer = ECImporter(self.iban, 'Assets:ING-DiBa:Extra', self.user)
+
+        with open(self.filename) as fd:
+            transactions = importer.extract(fd)
+
+        # 4 transactions + 2 balance assertions
+        self.assertEqual(len(transactions), 4 + 2)
+        # Test opening balance
+        self.assertEqual(transactions[4].date, date(2018, 6, 1))
+        self.assertEqual(transactions[4].amount.number, 1734.0)
+        self.assertEqual(transactions[4].amount.currency, 'EUR')
+        # Test closing balance
+        self.assertEqual(transactions[5].date, date(2018, 7, 1))
+        self.assertEqual(transactions[5].amount.number, 1000.0)
+        self.assertEqual(transactions[5].amount.currency, 'EUR')
+
+    def test_descending_by_date_single(self):
+        with open(self.filename, 'wb') as fd:
+            fd.write(
+                self._format_data(
+                    '''
+                    Umsatzanzeige;Datei erstellt am: 25.07.2018 12:00
+
+                    IBAN;{formatted_iban}
+                    Kontoname;Extra-Konto
+                    Bank;ING-DiBa
+                    Kunde;{user}
+                    Zeitraum;01.06.2018 - 30.06.2018
+                    Saldo;5.000,00;EUR
+
+                    Sortierung;Datum absteigend
+
+                    {pre_header}
+
+                    "Buchung";"Valuta";"Auftraggeber/Empfänger";"Buchungstext";"Kategorie";"Verwendungszweck";"Saldo";"Währung";"Betrag";"Währung"
+                    08.06.2018;08.06.2018;REWE Filialen Voll;Gutschrift;Kategorie;REWE SAGT DANKE;1.234,00;EUR;-500,00;EUR
+                    '''  # NOQA
+                )
+            )
+
+        importer = ECImporter(self.iban, 'Assets:ING-DiBa:Extra', self.user)
+
+        with open(self.filename) as fd:
+            transactions = importer.extract(fd)
+
+        # 1 transaction + 2 balance assertions
+        self.assertEqual(len(transactions), 1 + 2)
+        # Test opening balance
+        self.assertEqual(transactions[1].date, date(2018, 6, 1))
+        self.assertEqual(transactions[1].amount.number, 1734.0)
+        self.assertEqual(transactions[1].amount.currency, 'EUR')
+        # Test closing balance
+        self.assertEqual(transactions[2].date, date(2018, 7, 1))
+        self.assertEqual(transactions[2].amount.number, 1234.0)
+        self.assertEqual(transactions[2].amount.currency, 'EUR')
+
+    def test_descending_by_date_multiple(self):
+        with open(self.filename, 'wb') as fd:
+            fd.write(
+                self._format_data(
+                    '''
+                    Umsatzanzeige;Datei erstellt am: 25.07.2018 12:00
+
+                    IBAN;{formatted_iban}
+                    Kontoname;Extra-Konto
+                    Bank;ING-DiBa
+                    Kunde;{user}
+                    Zeitraum;01.06.2018 - 30.06.2018
+                    Saldo;5.000,00;EUR
+
+                    Sortierung;Datum absteigend
+
+                    {pre_header}
+
+                    "Buchung";"Valuta";"Auftraggeber/Empfänger";"Buchungstext";"Kategorie";"Verwendungszweck";"Saldo";"Währung";"Betrag";"Währung"
+                    15.06.2018;08.06.2018;LIDL;Lastschrift;Kategorie;LIDL SAGT DANKE;1.000,00;EUR;-100,00;EUR
+                    15.06.2018;08.06.2018;LIDL;Lastschrift;Kategorie;LIDL SAGT DANKE;1.100,00;EUR;-100,00;EUR
+                    08.06.2018;08.06.2018;LIDL;Lastschrift;Kategorie;LIDL SAGT DANKE;1.200,00;EUR;-34,00;EUR
+                    08.06.2018;08.06.2018;REWE Filialen Voll;Gutschrift;Kategorie;REWE SAGT DANKE;1.234,00;EUR;-500,00;EUR
+                    '''  # NOQA
+                )
+            )
+
+        importer = ECImporter(self.iban, 'Assets:ING-DiBa:Extra', self.user)
+
+        with open(self.filename) as fd:
+            transactions = importer.extract(fd)
+
+        # 4 transactions + 2 balance assertions
+        self.assertEqual(len(transactions), 4 + 2)
+        # Test opening balance
+        self.assertEqual(transactions[4].date, date(2018, 6, 1))
+        self.assertEqual(transactions[4].amount.number, 1734.0)
+        self.assertEqual(transactions[4].amount.currency, 'EUR')
+        # Test closing balance
+        self.assertEqual(transactions[5].date, date(2018, 7, 1))
+        self.assertEqual(transactions[5].amount.number, 1000.0)
+        self.assertEqual(transactions[5].amount.currency, 'EUR')