Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
import re
from com.infinitekind.moneydance.model import ParentTxn
#
# 1. SET YOUR CREDIT CARD ACCOUNT NAME HERE
AMAZON_ACCOUNT_NAME="Chase Amazon Visa"
#
#
# 2. Go to https://www.amazon.com/gp/b2b/reports export an order history report and put the full
# file path here.
AMAZON_REPORT_CSV="/Users/edalquist/Downloads/01-Nov-2020_to_25-May-2021.csv"
#
#
# 3. Optional Settings:
# Regex patterns match that match the descriptions of your Amazon charges
AMAZON_DESC_PATTERNS = [
re.compile(r'Amazon\.com\*.*'),
re.compile(r'AMZN .*'),
]
#
# Max difference in MoneyDance date vs Amazon date
MAX_DATE_DIFF = 3
#
#
#
# 4. Open Moneydance Console, paste in this script and run it.
#
ORDER_ID_P = re.compile(r'\d{3}-\d{7}-\d{7}.*')
CHARGE_P = re.compile(r'\$(\d*)\.(\d*)')
DATE_P = re.compile(r'(\d\d?)/(\d\d?)/(\d\d?)')
oldest = None
newest = None
amazon_orders = {}
f = open(AMAZON_REPORT_CSV, "r")
for line in f.readlines()[1:]:
fields = line.split(',')
ship_date = fields[6]
ship_date_m = DATE_P.match(ship_date)
charge = fields[20]
charge_m = CHARGE_P.match(charge)
if charge_m is None or ship_date_m is None:
print("Skipping: %s" % line)
continue
# Convert date & charge to moneydance tx format
date_md = int('20%s%s%s' % (ship_date_m.group(3), ship_date_m.group(1), ship_date_m.group(2)))
charge_md = int('-%s%s' % (charge_m.group(1), charge_m.group(2)))
if oldest is None or date_md < oldest:
oldest = date_md - 14
if newest is None or date_md > newest:
newest = date_md + 14
# Build dict of amazon orders keyed by transaction cost
order_id = fields[1]
orders = amazon_orders.get(charge_md, [])
orders.append((date_md, order_id))
amazon_orders[charge_md] = orders
print('Merging amazon transactions from %s to %s' % (oldest, newest))
book = moneydance.getCurrentAccountBook()
acct = book.getRootAccount().getAccountByName(AMAZON_ACCOUNT_NAME)
for txn in sorted(book.getTransactionSet().getTxnsForAccount(acct), key=lambda txn: txn.getDateInt()):
# skip transactions with splits
if isinstance(txn, ParentTxn) and txn.getSplitCount() > 1:
continue
# skip transactions outside of the amazon import date range
tx_date = txn.getDateInt()
if tx_date < oldest or tx_date > newest:
continue
# If no matching transaction value in the orders dict
if not txn.getValue() in amazon_orders:
continue
# skip transactions that don't have amazon-like descriptions
if not any(regex.match(txn.getDescription()) for regex in AMAZON_DESC_PATTERNS):
print ("Non-AMZN Skip:\t%s %s" % (tx_date, txn.getDescription()))
continue
# If there are multiple orders with the same cost find the one with the closest date
orders = amazon_orders.get(txn.getValue())
date, order_id = orders[0]
date_diff = date - tx_date
for order in orders[1:]:
if abs(date_diff) > abs(order[0] - tx_date):
date, order_id = order
date_diff = order[0] - tx_date
# Skip if matching tx is too far away
if abs(date_diff) > MAX_DATE_DIFF:
continue
# If the memo doesn't already start with the order_id prepend it.
if not ORDER_ID_P.match(txn.getMemo()):
new_memo = order_id + (' ' + txn.getMemo() if len(txn.getMemo()) else '')
print "Updating Memo:\t%s (%s) %s,\t'%s' -> '%s'" % (txn.getDateInt(), date_diff, txn.getDescription(), txn.getMemo(), new_memo)
txn.setMemo(new_memo)
txn.syncItem()
else:
print " Leaving Memo:\t%s (%s) %s,\t'%s'" % (txn.getDateInt(), date_diff, txn.getDescription(), txn.getMemo())
@yogi1967
Copy link

Hi. It’s possible that the record might auto save, but to be safe, the code should really be..:

txn.setMemo(new_memo)
txn.syncItem()

unless you make multiple edits to the same record. In which case you should call txn.setEditingMode() first.

@edalquist
Copy link
Author

Thanks for the tip! It was working as is but that may be more of a happy accident.

@stumpyd123
Copy link

I have never used MoneyDance, but I hate that my Amazon transactions are so hard to validate. Just to be clear - This script works in MoneyDance and adds the order number to a description of the transaction in MoneyDance assuming the transaction meets all the requirements (within the window of the charge, not split, and is an Amazon transaction). Did I summarize this correctly?

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment