Repository: bvanelli/actualpy
Pull request #77 (2024-09-24 10:42:51)

fix: Floating-Point Representation and Precision Error in transaction amount calculation
The issue is related to floating-point arithmetic in Python, which affects the accuracy of transaction amount calculations. Specifically, when performing arithmetic operations with floating-point numbers, the results can sometimes be slightly off due to the way floating-point numbers are represented in binary format and when these transactions accumulate, the final account budget result is different from the reality(sometimes by a significant amount). This can lead to incorrect financial tracking and budgeting

#### Example
For instance, the calculation `-9.95 * 100` results in `-994.9999999999999` instead of the expected `-995` and results in amount of `-9.94` added to ActualBudget instead of `-9.95`.

### This might be a breaking change for some users that implemented float-type amount insert using `actualpy`

#### References
- [Python decimal module documentation](https://docs.python.org/3/library/decimal.html)
- [Floating-point arithmetic issues](https://docs.python.org/3/tutorial/floatingpoint.html)

bvanelli: Hello @maxtacu ,

I do not feel like we should drop support the float, but instead handle it better and round the result. It seems like the issue is due to the fact that the int function will truncate the number, rather than rounding it. I was actually unaware of this behaviour. We could get around this by rounding the final floating number, or first converting it to decimal before doing any operations.

For example:

```python
>>> int(-9.95*100)
-994.9999999999999   # does not work
>>> round(-9.95*100)
-995                                 # works

```

Let me know what do you think, here are the proposed changes:

```diff
diff --git a/actual/database.py b/actual/database.py
index 07cd232..eda9750 100644
--- a/actual/database.py
+++ b/actual/database.py
@@ -594,7 +594,7 @@ class Transactions(BaseModel, table=True):
         self.date = int(datetime.date.strftime(date, "%Y%m%d"))
 
     def set_amount(self, amount: Union[decimal.Decimal, int, float]):
-        self.amount = int(amount * 100)
+        self.amount = int(round(amount * 100))
 
     def get_amount(self) -> decimal.Decimal:
         return decimal.Decimal(self.amount) / decimal.Decimal(100)
diff --git a/actual/queries.py b/actual/queries.py
index 7cc1a15..916e7bb 100644
--- a/actual/queries.py
+++ b/actual/queries.py
@@ -175,7 +175,7 @@ def create_transaction_from_ids(
         id=str(uuid.uuid4()),
         acct=account_id,
         date=date_int,
-        amount=int(amount * 100),
+        amount=int(round(amount * 100)),
         category_id=category_id,
         payee_id=payee_id,
         notes=notes,
(END)
```
maxtacu: @bvanelli rounding sounds good! 
as long as Actual doesn't add support for 3 digits decimal numbers 😄 
codecov[bot]: ## [Codecov](https://app.codecov.io/gh/bvanelli/actualpy/pull/77?dropdown=coverage&src=pr&el=h1&utm_medium=referral&utm_source=github&utm_content=comment&utm_campaign=pr+comments&utm_term=Brunno+Vanelli) Report
All modified and coverable lines are covered by tests :white_check_mark:
> Project coverage is 97.27%. Comparing base [(`8b92820`)](https://app.codecov.io/gh/bvanelli/actualpy/commit/8b928207e6849b0960056bc92cd0be8295a901b1?dropdown=coverage&el=desc&utm_medium=referral&utm_source=github&utm_content=comment&utm_campaign=pr+comments&utm_term=Brunno+Vanelli) to head [(`57841bd`)](https://app.codecov.io/gh/bvanelli/actualpy/commit/57841bd0e739d46a2f9d637469f416e596edd909?dropdown=coverage&el=desc&utm_medium=referral&utm_source=github&utm_content=comment&utm_campaign=pr+comments&utm_term=Brunno+Vanelli).

<details><summary>Additional details and impacted files</summary>


```diff
@@           Coverage Diff           @@
##             main      #77   +/-   ##
=======================================
  Coverage   97.27%   97.27%           
=======================================
  Files          14       14           
  Lines        1945     1945           
=======================================
  Hits         1892     1892           
  Misses         53       53           
```

</details>

[:umbrella: View full report in Codecov by Sentry](https://app.codecov.io/gh/bvanelli/actualpy/pull/77?dropdown=coverage&src=pr&el=continue&utm_medium=referral&utm_source=github&utm_content=comment&utm_campaign=pr+comments&utm_term=Brunno+Vanelli).   
:loudspeaker: Have feedback on the report? [Share it here](https://about.codecov.io/codecov-pr-comment-feedback/?utm_medium=referral&utm_source=github&utm_content=comment&utm_campaign=pr+comments&utm_term=Brunno+Vanelli).

bvanelli: > @bvanelli rounding sounds good! I will update the PR as long as Actual doesn't add support for 3 digits decimal numbers 😄

Yeah, I wonder if they will ever add that, I believe is required when they support multi currency because of cryptocurrency.

Anyways, would you mind adding a test for the failing case (i.e. adding one transaction with -9.95) as value?
bvanelli: Looks good to me, there is one issue with the pre-commit and one issue with the typing, but I'll fix that once merged. I created https://github.com/bvanelli/actualpy/issues/80 to also document the pre-commit stuff.



Patch:
diff --git a/actual/database.py b/actual/database.py
index 07cd232..eda9750 100644
--- a/actual/database.py
+++ b/actual/database.py
@@ -594,7 +594,7 @@ class Transactions(BaseModel, table=True):
         self.date = int(datetime.date.strftime(date, "%Y%m%d"))
 
     def set_amount(self, amount: Union[decimal.Decimal, int, float]):
-        self.amount = int(amount * 100)
+        self.amount = int(round(amount * 100))
 
     def get_amount(self) -> decimal.Decimal:
         return decimal.Decimal(self.amount) / decimal.Decimal(100)
diff --git a/actual/queries.py b/actual/queries.py
index 7cc1a15..73c254a 100644
--- a/actual/queries.py
+++ b/actual/queries.py
@@ -129,7 +129,7 @@ def match_transaction(
     # if not matched, look 7 days ahead and 7 days back when fuzzy matching
     query = _transactions_base_query(
         s, date - datetime.timedelta(days=7), date + datetime.timedelta(days=8), account=account
-    ).filter(Transactions.amount == amount * 100)
+    ).filter(Transactions.amount == round(amount * 100))
     results: list[Transactions] = s.exec(query).all()  # noqa
     # filter out the ones that were already matched
     if already_matched:
@@ -175,7 +175,7 @@ def create_transaction_from_ids(
         id=str(uuid.uuid4()),
         acct=account_id,
         date=date_int,
-        amount=int(amount * 100),
+        amount=int(round(amount * 100)),
         category_id=category_id,
         payee_id=payee_id,
         notes=notes,


Test patch:
diff --git a/tests/test_database.py b/tests/test_database.py
index e0b29f1..46d0265 100644
--- a/tests/test_database.py
+++ b/tests/test_database.py
@@ -63,6 +63,18 @@ def test_account_relationships(session):
     assert get_accounts(session, "Bank") == [bank]
 
 
+def test_transaction(session: Actual.session):
+    today = date.today()
+    other = create_account(session, "Other")
+    coffee = create_transaction(
+        session, date=today, account="Other", payee="Starbucks", notes="coffee", amount=float(-9.95)
+    )
+    session.commit()
+    assert coffee.amount == -995
+    assert len(other.transactions) == 1
+    assert other.balance == decimal.Decimal('-9.95')
+
+
 def test_reconcile_transaction(session):
     today = date.today()
     create_account(session, "Bank")