def associate_items_with_orders(orders, items):
items_by_oid = defaultdict(list)
for i in items:
items_by_oid[i.order_id].append(i)
orders_by_oid = defaultdict(list)
for o in orders:
orders_by_oid[o.order_id].append(o)
for oid, orders in orders_by_oid.items():
oid_items = items_by_oid[oid]
if not micro_usd_nearly_equal(Order.sum_subtotals(orders),
Item.sum_subtotals(oid_items)):
# This is likely due to reports being pulled before all outstanding
# orders have shipped. Just skip this order for now.
continue
if len(orders) == 1:
orders[0].set_items(oid_items, assert_unmatched=True)
continue
# First try to divy up the items by tracking.
items_by_tracking = defaultdict(list)
for i in oid_items:
items_by_tracking[i.tracking].append(i)
# It is never the case that multiple orders with the same order id will
# have the same tracking number.
for order in orders:
items = items_by_tracking[order.tracking]
if micro_usd_nearly_equal(Item.sum_subtotals(items),
order.subtotal):
# A perfect fit.
order.set_items(items, assert_unmatched=True)
# Remove the selected items.
oid_items = [i for i in oid_items if i not in items]
# Remove orders that have items.
orders = [o for o in orders if not o.items]
if not orders and not oid_items:
continue
orders = sorted(orders, key=lambda o: o.subtotal)
# Partition the remaining items into every possible arrangement and
# validate against the remaining orders.
for item_groupings in algorithm_u(oid_items, len(orders)):
subtotals_with_groupings = sorted(
[(Item.sum_subtotals(items), items)
for items in item_groupings],
key=lambda g: g[0])
if all([
micro_usd_nearly_equal(subtotals_with_groupings[i][0],
orders[i].subtotal)
for i in range(len(orders))
]):
for idx, order in enumerate(orders):
order.set_items(subtotals_with_groupings[idx][1],
assert_unmatched=True)
break
def test_micro_usd_nearly_equal(self):
self.assertTrue(currency.micro_usd_nearly_equal(0, 10))
self.assertTrue(currency.micro_usd_nearly_equal(0, -10))
self.assertTrue(currency.micro_usd_nearly_equal(-10, 0))
self.assertTrue(currency.micro_usd_nearly_equal(10, 0))
self.assertTrue(currency.micro_usd_nearly_equal(42143241, 42143239))
self.assertTrue(currency.micro_usd_nearly_equal(42143241, 42143243))
self.assertFalse(currency.micro_usd_nearly_equal(0, 400))
self.assertFalse(currency.micro_usd_nearly_equal(0, -200))
self.assertFalse(currency.micro_usd_nearly_equal(-500, 0))
self.assertFalse(currency.micro_usd_nearly_equal(200, 0))
def to_mint_transactions(self,
t,
skip_category=False,
skip_free_shipping=False):
new_transactions = []
# More expensive items are always more interesting when it comes to
# budgeting, so show those first (for both itemized and concatted).
items = sorted(self.items,
key=lambda item: item.item_total,
reverse=True)
# Itemize line-items:
for i in items:
new_cat = (t.category if skip_category else
category.AMAZON_TO_MINT_CATEGORY.get(
i.category, category.DEFAULT_MINT_CATEGORY))
item = t.split(amount=i.item_total,
category=new_cat,
desc=i.get_title(88),
note=self.get_note())
new_transactions.append(item)
# Itemize the shipping cost, if any.
is_free_shipping = (self.shipping_charge and self.total_promotions
and micro_usd_nearly_equal(self.total_promotions,
self.shipping_charge))
if is_free_shipping and skip_free_shipping:
return new_transactions
if self.shipping_charge:
ship = t.split(amount=self.shipping_charge,
category='Shipping',
desc='Shipping',
note=self.get_note())
new_transactions.append(ship)
# All promotion(s) as one line-item.
if self.total_promotions:
# If there was a promo that matches the shipping cost, it's nearly
# certainly a Free One-day/same-day/etc promo. In this case,
# categorize the promo instead as 'Shipping', which will cancel out
# in Mint trends.
cat = ('Shipping'
if is_free_shipping else category.DEFAULT_MINT_CATEGORY)
promo = t.split(amount=-self.total_promotions,
category=cat,
desc='Promotion(s)',
note=self.get_note(),
isDebit=False)
new_transactions.append(promo)
return new_transactions
def set_quantity(self, new_quantity):
"""Sets the quantity of this item and updates all prices."""
original_quantity = self.quantity
assert new_quantity > 0
subtotal_equal = micro_usd_nearly_equal(
self.purchase_price_per_unit * original_quantity,
self.item_subtotal)
assert subtotal_equal < MICRO_USD_EPS
self.item_subtotal = self.purchase_price_per_unit * new_quantity
self.item_subtotal_tax = (self.item_subtotal_tax /
original_quantity) * new_quantity
self.item_total = self.item_subtotal + self.item_subtotal_tax
self.quantity = new_quantity
def associate_items_with_orders(all_orders, all_items, itemProgress=None):
items_by_oid = defaultdict(list)
for i in all_items:
items_by_oid[i.order_id].append(i)
orders_by_oid = defaultdict(list)
for o in all_orders:
orders_by_oid[o.order_id].append(o)
for oid, orders in orders_by_oid.items():
oid_items = items_by_oid[oid]
if not micro_usd_nearly_equal(Order.sum_subtotals(orders),
Item.sum_subtotals(oid_items)):
# This is likely due to reports being pulled before all outstanding
# orders have shipped. Just skip this order for now.
continue
if len(orders) == 1:
orders[0].set_items(oid_items, assert_unmatched=True)
if itemProgress:
itemProgress.next(len(oid_items))
continue
# First try to divy up the items by tracking.
items_by_tracking = defaultdict(list)
for i in oid_items:
items_by_tracking[i.tracking].append(i)
# It is never the case that multiple orders with the same order id will
# have the same tracking number. Try using tracking number to split up
# the items between the orders.
for order in orders:
items = items_by_tracking[order.tracking]
if micro_usd_nearly_equal(Item.sum_subtotals(items),
order.subtotal):
# A perfect fit.
order.set_items(items, assert_unmatched=True)
if itemProgress:
itemProgress.next(len(items))
# Remove the selected items.
oid_items = [i for i in oid_items if i not in items]
# Remove orders that have items.
orders = [o for o in orders if not o.items]
if not orders and not oid_items:
continue
orders = sorted(orders, key=lambda o: o.subtotal)
# Partition the remaining items into every possible arrangement and
# validate against the remaining orders.
# TODO: Make a custom algorithm with backtracking.
# The number of combinations are factorial, so limit the number of
# attempts (by a 1 sec timeout) before giving up.
try:
with timeout(1, exception=RuntimeError):
for item_groupings in algorithm_u(oid_items, len(orders)):
subtotals_with_groupings = sorted(
[(Item.sum_subtotals(itms), itms)
for itms in item_groupings],
key=lambda g: g[0])
if all([
micro_usd_nearly_equal(
subtotals_with_groupings[i][0],
orders[i].subtotal) for i in range(len(orders))
]):
for idx, order in enumerate(orders):
items = subtotals_with_groupings[idx][1]
order.set_items(items, assert_unmatched=True)
if itemProgress:
itemProgress.next(len(items))
break
except RuntimeError:
pass
def main():
parser = argparse.ArgumentParser(
description='Tag Mint transactions based on itemized Amazon history.')
define_args(parser)
args = parser.parse_args()
if args.dry_run:
logger.info('Dry Run; no modifications being sent to Mint.')
# Initialize the stats. Explicitly initialize stats that might not be
# accumulated (conditionals).
stats = Counter(
adjust_itemized_tax=0,
already_up_to_date=0,
misc_charge=0,
new_tag=0,
no_retag=0,
retag=0,
user_skipped_retag=0,
)
orders = amazon.Order.parse_from_csv(args.orders_csv)
items = amazon.Item.parse_from_csv(args.items_csv)
# Remove items from cancelled orders.
items = [i for i in items if not i.is_cancelled()]
# Remove items that haven't shipped yet (also aren't charged).
items = [i for i in items if i.order_status == 'Shipped']
# Remove items with zero quantity (it happens!)
items = [i for i in items if i.quantity > 0]
# Make more Items such that every item is quantity 1.
items = [si for i in items for si in i.split_by_quantity()]
logger.info('Matching Amazon Items with Orders')
amazon.associate_items_with_orders(orders, items)
refunds = [] if not args.refunds_csv else amazon.Refund.parse_from_csv(args.refunds_csv)
log_amazon_stats(items, orders, refunds)
# Only match orders that have items.
orders = [o for o in orders if o.items]
mint_client = None
def close_mint_client():
if mint_client:
mint_client.close()
atexit.register(close_mint_client)
if args.pickled_epoch:
mint_trans, mint_category_name_to_id = (
get_trans_and_categories_from_pickle(args.pickled_epoch))
else:
mint_client = get_mint_client(args)
# Only get transactions as new as the oldest Amazon order.
oldest_trans_date = min([o.order_date for o in orders])
if refunds:
oldest_trans_date = min(
oldest_trans_date,
min([o.order_date for o in refunds]))
mint_transactions_json, mint_category_name_to_id = (
get_trans_and_categories_from_mint(mint_client, oldest_trans_date))
epoch = int(time.time())
mint_trans = mint.Transaction.parse_from_json(mint_transactions_json)
dump_trans_and_categories(mint_trans, mint_category_name_to_id, epoch)
def get_prefix(is_debit):
return (args.description_prefix if is_debit
else args.description_return_prefix)
trans = mint.Transaction.unsplit(mint_trans)
stats['trans'] = len(trans)
# Skip t if the original description doesn't contain 'amazon'
trans = [t for t in trans if 'amazon' in t.omerchant.lower()]
stats['amazon_in_desc'] = len(trans)
# Skip t if it's pending.
trans = [t for t in trans if not t.is_pending]
stats['pending'] = stats['amazon_in_desc'] - len(trans)
# Skip t if a category filter is given and t does not match.
if args.mint_input_categories_filter:
whitelist = set(args.mint_input_categories_filter.lower().split(','))
trans = [t for t in trans if t.category.lower() in whitelist ]
# Match orders.
match_transactions(trans, orders)
unmatched_trans = [t for t in trans if not t.orders]
# Match refunds.
match_transactions(unmatched_trans, refunds)
unmatched_orders = [o for o in orders if not o.matched]
unmatched_trans = [t for t in trans if not t.orders]
unmatched_refunds = [r for r in refunds if not r.matched]
num_gift_card = len([o for o in unmatched_orders
if 'Gift Certificate' in o.payment_instrument_type])
num_unshipped = len([o for o in unmatched_orders if not o.shipment_date])
matched_orders = [o for o in orders if o.matched]
matched_trans = [t for t in trans if t.orders]
matched_refunds = [r for r in refunds if r.matched]
stats['trans_unmatch'] = len(unmatched_trans)
stats['order_unmatch'] = len(unmatched_orders)
stats['refund_unmatch'] = len(unmatched_refunds)
stats['trans_match'] = len(matched_trans)
stats['order_match'] = len(matched_orders)
stats['refund_match'] = len(matched_refunds)
stats['skipped_orders_gift_card'] = num_gift_card
stats['skipped_orders_unshipped'] = num_unshipped
merged_orders = []
merged_refunds = []
updates = []
for t in matched_trans:
if t.is_debit:
order = amazon.Order.merge(t.orders)
merged_orders.extend(orders)
if order.attribute_subtotal_diff_to_misc_charge():
stats['misc_charge'] += 1
# It's nice when "free" shipping cancels out with the shipping
# promo, even though there is tax on said free shipping. Spread
# that out across the items instead.
# if order.attribute_itemized_diff_to_shipping_tax():
# stats['add_shipping_tax'] += 1
if order.attribute_itemized_diff_to_per_item_tax():
stats['adjust_itemized_tax'] += 1
assert micro_usd_nearly_equal(t.amount, order.total_charged)
assert micro_usd_nearly_equal(t.amount, order.total_by_subtotals())
assert micro_usd_nearly_equal(t.amount, order.total_by_items())
new_transactions = order.to_mint_transactions(
t,
skip_free_shipping=not args.verbose_itemize)
else:
refunds = amazon.Refund.merge(t.orders)
merged_refunds.extend(refunds)
new_transactions = [
r.to_mint_transaction(t)
for r in refunds]
assert micro_usd_nearly_equal(t.amount, mint.Transaction.sum_amounts(new_transactions))
for nt in new_transactions:
nt.update_category_id(mint_category_name_to_id)
prefix = get_prefix(t.is_debit)
summarize_single_item_order = (
t.is_debit and len(order.items) == 1 and not args.verbose_itemize)
if args.no_itemize or summarize_single_item_order:
new_transactions = mint.summarize_new_trans(t, new_transactions, prefix)
else:
new_transactions = mint.itemize_new_trans(new_transactions, prefix)
if mint.Transaction.old_and_new_are_identical(
t, new_transactions, ignore_category=args.no_tag_categories):
stats['already_up_to_date'] += 1
continue
if t.merchant.startswith(prefix):
if args.prompt_retag:
if args.num_updates > 0 and len(updates) >= args.num_updates:
break
logger.info('\nTransaction already tagged:')
print_dry_run(
[(t, new_transactions)],
ignore_category=args.no_tag_categories)
logger.info('\nUpdate tag to proposed? [Yn] ')
action = readchar.readchar()
if action == '':
exit(1)
if action not in ('Y', 'y', '\r', '\n'):
stats['user_skipped_retag'] += 1
continue
stats['retag'] += 1
elif not args.retag_changed:
stats['no_retag'] += 1
continue
else:
stats['retag'] += 1
else:
stats['new_tag'] += 1
updates.append((t, new_transactions))
log_processing_stats(stats)
if not updates:
logger.info(
'All done; no new tags to be updated at this point in time!.')
exit(0)
if args.num_updates > 0:
updates = updates[:num_updates]
if args.dry_run:
logger.info('Dry run. Following are proposed changes:')
print_dry_run(updates, ignore_category=args.no_tag_categories)
else:
# Ensure we have a Mint client.
if not mint_client:
mint_client = get_mint_client(args)
send_updates_to_mint(
updates, mint_client, ignore_category=args.no_tag_categories)
def get_mint_updates(
orders,
items,
refunds,
trans,
args,
stats,
mint_category_name_to_id=category.DEFAULT_MINT_CATEGORIES_TO_IDS):
def get_prefix(is_debit):
return (args.description_prefix
if is_debit else args.description_return_prefix)
# Remove items from cancelled orders.
items = [i for i in items if not i.is_cancelled()]
# Remove items that haven't shipped yet (also aren't charged).
items = [i for i in items if i.order_status == 'Shipped']
# Remove items with zero quantity (it happens!)
items = [i for i in items if i.quantity > 0]
# Make more Items such that every item is quantity 1. This is critical
# prior to associate_items_with_orders such that items with non-1
# quantities split into different packages can be associated with the
# appropriate order.
items = [si for i in items for si in i.split_by_quantity()]
itemProgress = IncrementalBar('Matching Amazon Items with Orders',
max=len(items))
amazon.associate_items_with_orders(orders, items, itemProgress)
itemProgress.finish()
# Only match orders that have items.
orders = [o for o in orders if o.items]
trans = mint.Transaction.unsplit(trans)
stats['trans'] = len(trans)
# Skip t if the original description doesn't contain 'amazon'
trans = [t for t in trans if 'amazon' in t.omerchant.lower()]
stats['amazon_in_desc'] = len(trans)
# Skip t if it's pending.
trans = [t for t in trans if not t.is_pending]
stats['pending'] = stats['amazon_in_desc'] - len(trans)
# Skip t if a category filter is given and t does not match.
if args.mint_input_categories_filter:
whitelist = set(args.mint_input_categories_filter.lower().split(','))
trans = [t for t in trans if t.category.lower() in whitelist]
# Match orders.
orderMatchProgress = IncrementalBar('Matching Amazon Orders w/ Mint Trans',
max=len(orders))
match_transactions(trans, orders, orderMatchProgress)
orderMatchProgress.finish()
unmatched_trans = [t for t in trans if not t.orders]
# Match refunds.
refundMatchProgress = IncrementalBar(
'Matching Amazon Refunds w/ Mint Trans', max=len(refunds))
match_transactions(unmatched_trans, refunds, refundMatchProgress)
refundMatchProgress.finish()
unmatched_orders = [o for o in orders if not o.matched]
unmatched_trans = [t for t in trans if not t.orders]
unmatched_refunds = [r for r in refunds if not r.matched]
num_gift_card = len([
o for o in unmatched_orders
if 'Gift Certificate' in o.payment_instrument_type
])
num_unshipped = len([o for o in unmatched_orders if not o.shipment_date])
matched_orders = [o for o in orders if o.matched]
matched_trans = [t for t in trans if t.orders]
matched_refunds = [r for r in refunds if r.matched]
stats['trans_unmatch'] = len(unmatched_trans)
stats['order_unmatch'] = len(unmatched_orders)
stats['refund_unmatch'] = len(unmatched_refunds)
stats['trans_match'] = len(matched_trans)
stats['order_match'] = len(matched_orders)
stats['refund_match'] = len(matched_refunds)
stats['skipped_orders_gift_card'] = num_gift_card
stats['skipped_orders_unshipped'] = num_unshipped
merged_orders = []
merged_refunds = []
updateCounter = IncrementalBar('Determining Mint Updates')
updates = []
for t in updateCounter.iter(matched_trans):
if t.is_debit:
order = amazon.Order.merge(t.orders)
merged_orders.extend(orders)
if order.attribute_subtotal_diff_to_misc_charge():
stats['misc_charge'] += 1
# It's nice when "free" shipping cancels out with the shipping
# promo, even though there is tax on said free shipping. Spread
# that out across the items instead.
# if order.attribute_itemized_diff_to_shipping_tax():
# stats['add_shipping_tax'] += 1
if order.attribute_itemized_diff_to_per_item_tax():
stats['adjust_itemized_tax'] += 1
assert micro_usd_nearly_equal(t.amount, order.total_charged)
assert micro_usd_nearly_equal(t.amount, order.total_by_subtotals())
assert micro_usd_nearly_equal(t.amount, order.total_by_items())
new_transactions = order.to_mint_transactions(
t, skip_free_shipping=not args.verbose_itemize)
else:
refunds = amazon.Refund.merge(t.orders)
merged_refunds.extend(refunds)
new_transactions = [r.to_mint_transaction(t) for r in refunds]
assert micro_usd_nearly_equal(
t.amount, mint.Transaction.sum_amounts(new_transactions))
for nt in new_transactions:
nt.update_category_id(mint_category_name_to_id)
prefix = get_prefix(t.is_debit)
summarize_single_item_order = (t.is_debit and len(order.items) == 1
and not args.verbose_itemize)
if args.no_itemize or summarize_single_item_order:
new_transactions = mint.summarize_new_trans(
t, new_transactions, prefix)
else:
new_transactions = mint.itemize_new_trans(new_transactions, prefix)
if mint.Transaction.old_and_new_are_identical(
t, new_transactions, ignore_category=args.no_tag_categories):
stats['already_up_to_date'] += 1
continue
if t.merchant.startswith(prefix):
if args.prompt_retag:
if args.num_updates > 0 and len(updates) >= args.num_updates:
break
logger.info('\nTransaction already tagged:')
print_dry_run([(t, new_transactions)],
ignore_category=args.no_tag_categories)
logger.info('\nUpdate tag to proposed? [Yn] ')
action = readchar.readchar()
if action == '':
exit(1)
if action not in ('Y', 'y', '\r', '\n'):
stats['user_skipped_retag'] += 1
continue
stats['retag'] += 1
elif not args.retag_changed:
stats['no_retag'] += 1
continue
else:
stats['retag'] += 1
else:
stats['new_tag'] += 1
updates.append((t, new_transactions))
if args.num_updates > 0:
updates = updates[:args.num_updates]
return updates