I got it to work, unfortunately I don't have more than 2 ATM transactions
to test it on.
It was nice to figure out though. Having something like Beancount as an
impetus to write a Python script on something practical is kind fun and a
cool way for me to learn a bit about both (Python and Beancount). Still
don't understand everything but I am getting things to work, so that's a
start.
Here's the revised script (please let me know if this could have been
organized better)...
from beancount.ingest.importer import ImporterProtocol
from beancount.core import data
from beancount.core import amount
from beancount.core.number import D
from dateutil.parser import parse
import datetime
import csv
import re
import logging
import warnings
from beancount.core import flags
class PNCChecking(ImporterProtocol):
def __init__(self, account, currency="USD"):
self.account = account
self.currency = currency
self.other_account = "Expenses" # Default other account
self.second_other_account = None # Second other account
super().__init__()
def identify(self, file):
# Check if the file name contains "pnc checking" and ends with
".csv"
if "pnc checking" in file.name.lower() and file
.name.lower().endswith('.csv'):
return True
else:
return False
def file_account(self, file):
return self.account
def extract(self, file):
entries = []
with open(file.name) as infile:
reader = csv.reader(infile)
next(reader, None) # Skip the header row
for index, row in enumerate(reader):
if len(row) < 5: # Checking if the row has at least 5
elements
logging.warning("Row %d does not have enough elements.
Skipping.", index)
continue
meta = data.new_metadata(file.name, index) # Beancount meta
date = datetime.datetime.strptime(row[0], "%m/%d/%Y").date()
# Parse the date of the transaction
# Extracting relevant information from the description to
determine payee and other_account
description = row[1] # Assuming description is in the
second column
payee = "" # Placeholder, modify this based on the
description
self.other_account = "Expenses" # Reset other account to
default
self.second_other_account = None # Reset second other
account
# Check if the description contains "CARD PMT"
if "CARD PMT" in description and "1234" in description:
payee = "PNC Cash Rewards"
self.other_account = "Liabilities:PNC:CashRewards"
# Check if this is an ATM withdrawal. If fee is included in
withdrawal, separate out.
if "ATM" in description:
payee = "ATM Withdrawal"
self.other_account = "Assets:Cash" # Main account
self.second_other_account = "Expenses:BankFees" #
Second other account
# Adjustments for ATM withdrawal transaction
withdrawal = row[2] # Withdrawals amount
deposit = row[3] # Deposits amount
if withdrawal:
withdrawal_amount = D(withdrawal.replace('$', ''))
fee_amount = withdrawal_amount % 10 # Calculate
the fee as the remainder after division by 10
units = -amount.Amount(withdrawal_amount, self.
currency) # Withdrawal amount
# Subtract the fee from the withdrawal amount to
get the cash amount
cash_amount = withdrawal_amount - fee_amount
account = self.account # Modify this according to
your data
txn = data.Transaction(
meta,
date,
None, # No specific flag
payee,
description,
data.EMPTY_SET,
data.EMPTY_SET,
[
data.Posting(
account, units, None, None, None, None
),
data.Posting(
self.other_account, amount.Amount(
cash_amount, self.currency), None, None, None, None
),
],
)
# Add second_other_account if available
if self.second_other_account:
txn.postings.append(
data.Posting(
self.second_other_account, amount.Amount
(fee_amount, self.currency), None, None, None, None
)
)
entries.append(txn)
else:
logging.warning("Invalid row: %s", row)
continue
# Skip the regular withdrawal and deposit logic if ATM
withdrawal
continue
# Regular withdrawal and deposit logic
withdrawal = row[2] # Withdrawals amount
deposit = row[3] # Deposits amount
if withdrawal:
units = -amount.Amount(D(withdrawal.replace('$', '')),
self.currency) # Using withdrawal amount
elif deposit:
units = amount.Amount(D(deposit.replace('$', '')), self.
currency) # Using deposit amount
else:
logging.warning("Invalid row: %s", row)
continue
account = self.account # Modify this according to your data
txn = data.Transaction(
meta,
date,
None, # No specific flag
payee,
description,
data.EMPTY_SET,
data.EMPTY_SET,
[
data.Posting(
account, units, None, None, None, None
),
data.Posting(
self.other_account, -units, None, None, None,
None
),
],
)
# Add second_other_account if available
if self.second_other_account:
txn.postings.append(
data.Posting(
self.second_other_account, amount.Amount(
fee_amount, self.currency), None, None, None, None
)
)
entries.append(txn)
return entries
On Monday, February 26, 2024 at 12:22:51 PM UTC-5 [email protected] wrote:
> First comment; the code
> withdrawal_amount % 2 != 0
>
> checks for "evenness" in the sense of odd (1, 3, 5) vs even (2, 4, 6)
> numbers. This doesn't seem like what you'd want, since if your fee were
> $5 and you withdraw $100, it would count it one way, but if the fee were $2
> it would count it the other. Only you can come up with the best heuristic
> for yourself (based on your typical withdrawal amounts and typical fees)
> but you might want to revisit this one.
>
>
> as for not appending the posting, it's a bit hard to figure out from your
> two code snippets since I can't tell where the 2nd code snippet lives in
> relation to the 1st. But I suspect that in the 2nd snippet, the "txn"
> you're adding the postings to is not the one you expect. If the 2nd
> snippet actually lives in the context of the
> if "ATM" in description:
> condition in the first snippet, then you're adding to "txn" before you
> create the txn for this input. That means you're probably adding it to the
> txn from the previous iteration, which is still visible and accessible
> (thanks, Python).
>
> On Mon, Feb 26, 2024 at 7:11 AM CDT <[email protected]> wrote:
>
>> I don't like to carry much cash when I travel for business, so from time
>> to time I use my ATM card at a machine when I'm away and it charges a fee
>> that is included in the total.
>>
>> ie - I get $500 cash but the full amount shows as one transaction of
>> $507.99 (the fee isn't separated)
>>
>> I am trying to figure out a way to have this separated out when I use an
>> Importer so that it would show up as $500 USD in cash and 7.99 USD in fees
>>
>> I have a working CSV import for my PNC transactions, but I can't seem to
>> figure out how to include this.
>>
>> Here's the code I use at them moment (without the fees)...
>>
>> from beancount.ingest.importer import ImporterProtocol
>> from beancount.core import data
>> from beancount.core import amount
>> from beancount.core.number import D
>> from dateutil.parser import parse
>> import datetime
>> import csv
>> import re
>> import logging
>> import warnings
>> from beancount.core import flags
>>
>> class PNCChecking(ImporterProtocol):
>> def __init__(self, account, currency="USD"):
>> self.account = account
>> self.currency = currency
>> super().__init__()
>>
>> def identify(self, file):
>> # Check if the file name contains "pnc checking" and ends with
>> ".csv"
>> if "pnc checking" in file.name.lower() and file
>> .name.lower().endswith('.csv'):
>> return True
>> else:
>> return False
>>
>> def file_account(self, file):
>> return self.account
>>
>> def extract(self, file):
>> entries = []
>>
>> with open(file.name) as infile:
>> reader = csv.reader(infile)
>> next(reader, None) # Skip the header row
>>
>> for index, row in enumerate(reader):
>> if len(row) < 5: # Checking if the row has at least 5
>> elements
>> logging.warning("Row %d does not have enough
>> elements. Skipping.", index)
>> continue
>>
>> meta = data.new_metadata(file.name, index) # Beancount
>> meta
>> date = datetime.datetime.strptime(row[0], "%m/%d/%Y").
>> date() # Parse the date of the transaction
>>
>> # Extracting relevant information from the description
>> to determine payee and other_account
>> description = row[1] # Assuming description is in the
>> second column
>> payee = "" # Placeholder, modify this based on the
>> description
>> other_account = "Expenses" # Placeholder, modify this
>> based on the description
>>
>> # Check if the description contains "CARD PMT"
>> if "CARD PMT" in description:
>> payee = "PNC Cash Rewards"
>> other_account = "Liabilities:PNC:CashRewards"
>>
>> # Check if this is an ATM withdrawal
>> # and then subtract their ATM Fee and put that in
>> separate expense
>>
>> if "ATM" in description:
>> other_account = "Assets:Cash"
>>
>>
>> # Determining the credit/debit type based on withdrawals
>> and deposits
>> withdrawal = row[2] # Withdrawals amount
>> deposit = row[3] # Deposits amount
>>
>> if withdrawal:
>> units = -amount.Amount(D(withdrawal.replace('$', '')),
>> self.currency) # Using withdrawal amount
>> elif deposit:
>> units = amount.Amount(D(deposit.replace('$', '')),
>> self.currency) # Using deposit amount
>> else:
>> logging.warning("Invalid row: %s", row)
>> continue
>>
>> account = self.account # Modify this according to your
>> data
>>
>> txn = data.Transaction(
>> meta,
>> date,
>> None, # No specific flag
>> payee,
>> description,
>> data.EMPTY_SET,
>> data.EMPTY_SET,
>> [
>> data.Posting(
>> account, units, None, None, None, None
>> ),
>> data.Posting(
>> other_account, -units, None, None, None, None
>> ),
>> ],
>> )
>>
>> entries.append(txn)
>>
>> return entries
>>
>> This works well and I get this kind of output...
>>
>> 2019-03-01 None "ATM WITHDRAWAL"
>> Assets:PNC:Checking -407.99 USD
>> Assets:Cash 407.99 USD
>>
>> In the section where the ATM transactions are figured out through the
>> descriptor I added this...
>>
>> # Check if this is an ATM withdrawal
>> # and then subtract their ATM Fee and put that in separate expense
>>
>> if "ATM" in description:
>> other_account = "Assets:Cash"
>>
>> # Calculate the ATM fee
>> atm_fee_amount = D("0.00") # Initialize ATM fee
>> amount
>> withdrawal_amount = abs(units.number) # Absolute
>> value of the withdrawal amount
>>
>> # Check if the withdrawal amount is greater than an
>> even number
>> if withdrawal_amount % 2 != 0: # If it's not an
>> even number
>> atm_fee_amount = D(withdrawal_amount - (
>> withdrawal_amount // 2) * 2) # Calculate the fee as the remainder after
>> division by 2
>>
>> # Adjust the ATM fee amount
>> atm_fee_amount = -atm_fee_amount
>>
>> # Convert the ATM fee amount to a beancount.Amount
>> object
>> atm_fee = amount.Amount(atm_fee_amount, self
>> .currency)
>> # Create a posting for the ATM fee
>> atm_fee_posting = data.Posting(
>> "Expenses:BankFees:ATM", atm_fee_amount, None,
>> None, None, None
>> )
>> # Adjust the units for the main account posting
>> main_account_units = units + atm_fee_amount
>> # Add the adjusted main account posting
>> main_account_posting = data.Posting(
>> account, main_account_units, None, None, None,
>> None
>> )
>> txn.postings.append(main_account_posting)
>> # Add the ATM fee posting to the transaction
>> txn.postings.append(atm_fee_posting)
>>
>> But I can't seem to get it to append properly to the postings.
>>
>> I'd like the output to look like this...
>>
>> 2019-03-01 None "ATM WITHDRAWAL"
>> Assets:PNC:Checking -407.99 USD
>> Assets:Cash 400.00 USD
>> Expenses:BankFees:ATM -7.99 USD
>>
>> Any help would be appreciated.
>>
>> I'll probably figure it out eventually, but if anyone has done this
>> already I would love to hear how you did it. So far I have been sort of
>> Frankensteining pieces from Beancount examples I've found so I my
>> understanding of everything is not great yet.
>>
>>
>> --
>> You received this message because you are subscribed to the Google Groups
>> "Beancount" group.
>> To unsubscribe from this group and stop receiving emails from it, send an
>> email to [email protected].
>> To view this discussion on the web visit
>> https://groups.google.com/d/msgid/beancount/6225321a-b905-4b23-ac5d-6eeb44289122n%40googlegroups.com
>>
>> <https://groups.google.com/d/msgid/beancount/6225321a-b905-4b23-ac5d-6eeb44289122n%40googlegroups.com?utm_medium=email&utm_source=footer>
>> .
>>
>
--
You received this message because you are subscribed to the Google Groups
"Beancount" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
To view this discussion on the web visit
https://groups.google.com/d/msgid/beancount/543a5964-1318-4324-b27f-8eaad86d211dn%40googlegroups.com.
