search

katefike / sage

A personal finance app that's like Mint, but better. It uses a dockerized postfix/dovecot email server. Parses transaction data from alert emails.
MIT License
5 stars 0 forks source link

Validate 202404 Chase prime txns #149

Closed katefike closed 3 months ago

katefike commented 4 months ago

Steps

Local Testing

  1. Run export ISDEV=True && python3 -m sage and verify the emails can be parsed
  2. Download a CSV of all of an account's transaction history validator/real_data/Chase_visa-prime_220618-240618.csv
  3. Validate the DB data against the CSV data python3 -m validator Chase_visa-prime_220618-240618.csv 4-2024

Detour

Create a retry mechanism for unparsed emails. ✔

DONE WHEN

2024-06-25 23:05:21.060 | INFO | main:main:72 - 3 - 04/26/2024, GASWAY FOOD BEDFORD, -62.53

2024-06-25 23:05:21.061 | INFO | main:main:72 - 7 - 04/21/2024, MCDONALD'S F36212, -0.53 2024-06-25 23:05:21.063 | INFO | main:main:72 - 14 - 04/02/2024, DLOCAL*RAPPI, -17.23

katefike commented 3 months ago

Moving mbox

kfike@cutie:~/Downloads$ unzip 202404.zip -d 202404
Archive:  202404.zip
  inflating: 202404/Takeout/Mail/backfill-202404.mbox  
replace 202404/Takeout/archive_browser.html? [y]es, [n]o, [A]ll, [N]one, [r]ename: y
  inflating: 202404/Takeout/archive_browser.html  
kfike@cutie:~/Downloads$ ls 202404/Takeout/Mail/
backfill-202404.mbox
kfike@cutie:~/Downloads$ cp 202404/Takeout/Mail/backfill-202404.mbox ../Projects/sage/docker/mailserver/test_data/real_data/
katefike commented 3 months ago

Identical txns

sage=# SELECT * FROM transactions t WHERE t.identical_txn_id IS NOT NULL;
sage=# SELECT t_dup.email_id AS "possible duplicate", t_orig.email_id AS "original" FROM transactions t_dup JOIN transactions t_orig ON t_orig.id = t_dup.identical_txn_id;
 possible duplicate | original 
--------------------+----------
               1949 |     1946
               1980 |     1976
               2023 |     2018
(3 rows)

Two different Huntington Uber withdrawals - one at 02:43:02, one at 03:17:1

 1949 |  46 | 2024-06-22 16:05:28+00 | 2024-05-25     | outgoing@gmail.com | placeholder | Fwd: Withdrawal or Purchase | t    | ---------- Forwarded message --------- From: Huntington Alerts < HuntingtonAlerts@em
ail.huntington.com > Date: Mon, 15 Apr 2024 02:43:02 -0600 Subject: Withdrawal or Purchase To: outgoing@gmail.com Date Sent: Mon, 15 Apr 2024 02:43:02 -0600 Date Received: Mon, 15 Apr 2024 01
:43:03 -0700 (PDT) We've processed a withdrawal or purchase above the amount you set for an alert. View as a web page Hello, Just a heads up for you. We've processed an ACH withdrawal for $0.
64 at UBER BV IAT PAYPAL from your account nicknamed CHECK.

1946 |  43 | 2024-06-22 16:05:28+00 | 2024-05-25     | outgoing@gmail.com | placeholder | Fwd: Withdrawal or Purchase | t    | ---------- Forwarded message --------- From: Huntington Alerts < HuntingtonAlerts@email.huntington.com > Date: Mon, 15 Apr 2024 03:17:11 -0600 Subject: Withdrawal or Purchase To: outgoing@gmail.com Date 
Sent: Mon, 15 Apr 2024 03:17:11 -0600 Date Received: Mon, 15 Apr 2024 02:19:30 -0700 (PDT) We've processed a withdrawal or purchase above the amount you set for an alert. View as a web page H
ello, Just a heads up for you. We've processed an ACH withdrawal for $0.64 at UBER BV IAT PAYPAL from your account nicknamed CHECK.

Identical Chase SAVE A LOT withdrawals - both for 7:06 PM Removed second forward from docker/mailserver/test_data/real_data/backfill-202404.mbox

1980 |  77 | 2024-06-22 16:05:28+00 | 2024-05-25     | outgoing@gmail.com | placeholder | Fwd: Your $12.97 transaction with SAVE A LOT #24664 | t    | ---------- Forwarded message --------- 
From: Chase < no.reply.alerts@chase.com > Date: Mon, Apr 29, 2024 at 7:06 PM Subject: Your $12.97 transaction with SAVE A LOT #24664 To: < outgoing@gmail.com > This transaction is above the level you set, see more here. ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ Transaction alert You made a $12.97 transaction Account Prime Visa (...6104) Date Apr 29, 2024 at 7:06 PM ET Merchant SAVE A LOT #24664 Amount $12.97

1976 |  73 | 2024-06-22 16:05:28+00 | 2024-05-25     | outgoing@gmail.com | placeholder | Fwd: Your $12.97 transaction with SAVE A LOT #24664 | t    | ---------- Forwarded message --------- From: Chase < no.reply.alerts@chase.com > Date: Mon, 29 Apr 2024 19:06:14 -0400 (EDT) Subject: Your $12.97 transaction with SAVE A LOT #24664 To: outgoing@gmail.com Date Sent: Mon, 29 Apr 2024 19:06:14 -0400 (EDT) Date Received: Mon, 29 Apr 2024 16:06:16 -0700 (PDT) This transaction is above the level you set, see more here. This transaction is above the level you set, see more here. ‌ ‌ ‌ ‌ ‌ ‌Transaction alert You made a $12.97 transaction Account Prime Visa (...6104) Date Apr 29, 2024 at 7:06 PM ET Merchant SAVE A LOT #24664 Amount $12.97

email IDs 2023 and 2018 are also from Uber, so they're probably distinct txns...