ATM Transaction that has Fee included in Total
137 views
Skip to first unread message
CDT
Feb 26, 2024, 10:11:08 AM2/26/24
to Beancount
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.
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
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)...
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...
But I can't seem to get it to append properly to the postings.
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.
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.
Eric Altendorf
Feb 26, 2024, 12:22:51 PM2/26/24
to bean...@googlegroups.com
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).--
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 beancount+...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/beancount/6225321a-b905-4b23-ac5d-6eeb44289122n%40googlegroups.com.
CD
Feb 26, 2024, 1:54:26 PM2/26/24
to Beancount
You wrote...
I almost have a working script. I'll post the completed one up shortly.
"First comment; the code
withdrawal_amount % 2 != 0"
How about... fee_amount = withdrawal_amount % 10...
CD
Feb 26, 2024, 2:24:21 PM2/26/24
to Beancount
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)...
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 erical...@gmail.com wrote:
Eric Altendorf
Mar 20, 2024, 9:55:07 PM3/20/24
to bean...@googlegroups.com
oops, old email....
On Mon, Feb 26, 2024 at 10:54 AM CD <doc...@gmail.com> wrote:
You wrote..."First comment; the codewithdrawal_amount % 2 != 0"How about... fee_amount = withdrawal_amount % 10...
that's probably a good heuristic within the US, i don't think many ATMs dispense denominations less than 10USD, and I don't think I've ever seen one that charges as much as 10USD in fees.
Might not work if you're traveling overseas and withdrawing in a foreign currency....
To view this discussion on the web visit https://groups.google.com/d/msgid/beancount/af0f6e5b-f236-4b40-b1dc-60b91d2e31a1n%40googlegroups.com.
Reply all
Reply to author
Forward
0 new messages