MoneyDanceAmazon.py

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())

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.

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

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?