💰 Trip Expense Splitter¶

Who Owes Who — SLC, Colorado & Montreal Trips¶

This notebook walks through each expense, applies payment rules, and calculates the final settlements.

Payment Status Legend¶

  • 0 — Did not participate → owes $0

  • 1 — Participated → pays equal share

  • 2 — Participated (no drink) → pays equal share minus $12

Step 1 — Load & Preview the Raw Data¶

In [1]:
import pandas as pd
import warnings
warnings.filterwarnings('ignore')

df = pd.read_excel('SLCColoradoMontreal.xlsx')
df = df[['Date','Location','Total','Trip','Paid by','Notes','HG','JK','JW','DB']].copy()
df['Date'] = pd.to_datetime(df['Date']).dt.strftime('%Y-%m-%d')
df = df.reset_index(drop=True)

pd.set_option('display.max_colwidth', 55)
pd.set_option('display.width', 150)
print(f"✅ Loaded {len(df)} expenses")
print(f"   People: HG, JK, JW, DB")
print(f"   Total Spent: ${df['Total'].sum():,.2f}")
df

✅ Loaded 29 expenses
   People: HG, JK, JW, DB
   Total Spent: $5,153.91
Out[1]:
Date Location Total Trip Paid by Notes HG JK JW DB
0 2025-09-09 HARMONS CITY CREEK 135 EAST 100 SOUTH SALT LAKE CIT... 51.35 SLC HG Liquor: DB did not really drink 1 1 1 0
1 2025-09-09 STATE LIQUOR STORE 13 255 WEST NORTH TEMPLE SALT LA... 109.63 SLC HG Liquor: DB did not really drink 1 1 1 0
2 2025-09-11 Smith's 1725 W. Uinta Way, Park City, UT 84060 22.15 SLC HG NaN 1 1 1 1
3 2025-09-13 Smith's, 1725 W. Uinta Way, Park City, UT 84060 37.55 SLC HG NaN 1 1 1 1
4 2025-09-10 Smith's, 1725 W. Uinta Way, Park City UT 84060 51.53 SLC HG NaN 1 1 1 1
5 2025-09-14 Chevron Touch N Go, 375 N Terminal Dr, Salt Lake UT 67.88 SLC HG Gas 1 1 1 1
6 2025-09-09 Red Iguana 1, 736 West North Temple St, Salt Lake C... 74.18 SLC HG Just HG and JK 1 1 0 0
7 2025-09-10 Taste of Thai, 1241 Center Dr Unit L160, Park City,... 145.01 SLC JK DB didn't order drink 1 1 1 2
8 2025-09-10 Costco, 1818 South 300 West, Salt Lake City, UT 84115 164.36 SLC DB NaN 1 1 1 1
9 2025-09-11 Smith's, 1725 W. Uinta Way, Park City UT 84060 18.45 SLC DB NaN 1 1 1 1
10 2025-09-12 Red Iguana 1, 736 West North Temple St, Salt Lake C... 153.81 SLC DB DB didn't order drink 1 1 1 2
11 2025-09-12 Bike Shuttle 40.00 SLC DB NaN 1 1 1 1
12 2025-09-14 National Car Rental, 3920 WEST TERMINAL DR, SALT LA... 298.64 SLC JK Rental car 1 1 1 1
13 2026-01-17 Montreal Airbnb 799.65 Montreal DB NaN 1 1 1 1
14 2026-01-05 Colorado Airbnb 651.23 CO HG NaN 1 1 1 1
15 2026-01-19 Canada Airbnb 732.48 Vermont HG NaN 1 1 1 1
16 2026-01-25 7680 Pena Blvd, Denver. CO 80249 48.86 CO JK Gas 1 1 1 1
17 2026-01-25 National Car Rental, 24530 E 78TH AVE, DENVER, CO 8... 215.03 CO JK Rental car 1 1 1 1
18 2026-02-12 DoubleTree Hilton 289.10 Vermont JW NaN 1 1 1 1
19 2026-02-22 4E Mur 103.43 Montreal JW DB no drank 1 1 1 0
20 2026-02-23 Dehli Kebab 134.92 Montreal JW NaN 1 1 1 1
21 2026-02-25 Hannaford 78.25 Vermont JW NaN 1 1 1 1
22 2026-02-25 Esso 59.05 Montreal JK Gas 1 1 1 1
23 2026-02-28 Henry's Diner 90.00 Vermont JW NaN 1 1 1 1
24 2026-02-28 Ice Cream Tour 26.00 Vermont HG NaN 1 1 1 1
25 2026-02-23 IGA Beer 27.14 Montreal HG DB no drank 1 1 1 0
26 2026-02-28 76 Paul St Garage 6.00 Vermont JK Parking in downtown Burlington 1 1 1 1
27 2026-02-28 ExxonMobil 47.42 Vermont JK Gas 1 1 1 1
28 2026-03-01 National Car Rental 610.81 Vermont JK Rental car 1 1 1 1

Step 2 — Calculation Logic¶

For each expense:

  1. Identify participants — anyone with status 1 or 2
  2. Calculate base share = Total ÷ number of participants
  3. Apply per-person adjustments:
    • Status 0 → owes $0
    • Status 1 → owes base_share
    • Status 2 → owes base_share − $12
  4. Each non-payer creates a debt to the payer for their share
  5. The $12 drink discount is effectively absorbed by the payer

Step 3 — Per-Expense Breakdown¶

In [14]:
people = ['HG', 'JK', 'JW', 'DB']

def calc_shares(row):
    total = float(row['Total'])
    statuses = {p: int(row[p]) for p in people}
    participants = [p for p in people if statuses[p] in (1, 2)]
    n = len(participants)
    if n == 0:
        return {p: 0.0 for p in people}

    base = total / n
    no_drink = [p for p in participants if statuses[p] == 2]
    drinkers  = [p for p in participants if statuses[p] == 1]
    extra = (len(no_drink) * 12.0) / len(drinkers) if drinkers else 0.0

    shares = {p: 0.0 for p in people}

    # Assign all but the last participant first
    for p in participants[:-1]:
        if statuses[p] == 1:
            shares[p] = round(base + extra, 2)
        elif statuses[p] == 2:
            shares[p] = round(base - 12.0, 2)

    # Last participant gets exact remainder so shares sum to total
    last = participants[-1]
    shares[last] = round(total - sum(shares[p] for p in participants[:-1]), 2)

    return shares

# Build detailed breakdown table
rows = []
for _, row in df.iterrows():
    shares = calc_shares(row)
    payer = str(row['Paid by']).strip()
    n_part = sum(1 for p in people if int(row[p]) in (1,2))
    base = float(row['Total']) / n_part if n_part > 0 else 0
    rows.append({
        'Date': row['Date'],
        'Location': row['Location'][:45] + ('…' if len(str(row['Location'])) > 45 else ''),
        'Total': f"${row['Total']:,.2f}",
        'Payer': payer,
        '# People': n_part,
        'Base Share': f"${base:.2f}",
        'HG': f"${shares['HG']:.2f}" if int(row['HG']) > 0 else '—',
        'JK':   f"${shares['JK']:.2f}"   if int(row['JK'])   > 0 else '—',
        'JW':  f"${shares['JW']:.2f}"  if int(row['JW'])  > 0 else '—',
        'DB':  f"${shares['DB']:.2f}"  if int(row['DB'])  > 0 else '—',
    })

detail_df = pd.DataFrame(rows)
print("Per-expense shares (— = not participating):")
detail_df

Per-expense shares (— = not participating):
Out[14]:
Date Location Total Payer # People Base Share HG JK JW DB
0 2025-09-09 HARMONS CITY CREEK 135 EAST 100 SOUTH SALT LA… $51.35 HG 3 $17.12 $17.12 $17.12 $17.11 —
1 2025-09-09 STATE LIQUOR STORE 13 255 WEST NORTH TEMPLE S… $109.63 HG 3 $36.54 $36.54 $36.54 $36.55 —
2 2025-09-11 Smith's 1725 W. Uinta Way, Park City, UT 8406… $22.15 HG 4 $5.54 $5.54 $5.54 $5.54 $5.53
3 2025-09-13 Smith's, 1725 W. Uinta Way, Park City, UT 840… $37.55 HG 4 $9.39 $9.39 $9.39 $9.39 $9.38
4 2025-09-10 Smith's, 1725 W. Uinta Way, Park City UT 8406… $51.53 HG 4 $12.88 $12.88 $12.88 $12.88 $12.89
5 2025-09-14 Chevron Touch N Go, 375 N Terminal Dr, Salt L… $67.88 HG 4 $16.97 $16.97 $16.97 $16.97 $16.97
6 2025-09-09 Red Iguana 1, 736 West North Temple St, Salt … $74.18 HG 2 $37.09 $37.09 $37.09 — —
7 2025-09-10 Taste of Thai, 1241 Center Dr Unit L160, Park… $145.01 JK 4 $36.25 $40.25 $40.25 $40.25 $24.26
8 2025-09-10 Costco, 1818 South 300 West, Salt Lake City, … $164.36 DB 4 $41.09 $41.09 $41.09 $41.09 $41.09
9 2025-09-11 Smith's, 1725 W. Uinta Way, Park City UT 8406… $18.45 DB 4 $4.61 $4.61 $4.61 $4.61 $4.62
10 2025-09-12 Red Iguana 1, 736 West North Temple St, Salt … $153.81 DB 4 $38.45 $42.45 $42.45 $42.45 $26.46
11 2025-09-12 Bike Shuttle $40.00 DB 4 $10.00 $10.00 $10.00 $10.00 $10.00
12 2025-09-14 National Car Rental, 3920 WEST TERMINAL DR, S… $298.64 JK 4 $74.66 $74.66 $74.66 $74.66 $74.66
13 2026-01-17 Montreal Airbnb $799.65 DB 4 $199.91 $199.91 $199.91 $199.91 $199.92
14 2026-01-05 Colorado Airbnb $651.23 HG 4 $162.81 $162.81 $162.81 $162.81 $162.80
15 2026-01-19 Canada Airbnb $732.48 HG 4 $183.12 $183.12 $183.12 $183.12 $183.12
16 2026-01-25 7680 Pena Blvd, Denver. CO 80249 $48.86 JK 4 $12.21 $12.21 $12.21 $12.21 $12.23
17 2026-01-25 National Car Rental, 24530 E 78TH AVE, DENVER… $215.03 JK 4 $53.76 $53.76 $53.76 $53.76 $53.75
18 2026-02-12 DoubleTree Hilton $289.10 JW 4 $72.28 $72.28 $72.28 $72.28 $72.26
19 2026-02-22 4E Mur $103.43 JW 3 $34.48 $34.48 $34.48 $34.47 —
20 2026-02-23 Dehli Kebab $134.92 JW 4 $33.73 $33.73 $33.73 $33.73 $33.73
21 2026-02-25 Hannaford $78.25 JW 4 $19.56 $19.56 $19.56 $19.56 $19.57
22 2026-02-25 Esso $59.05 JK 4 $14.76 $14.76 $14.76 $14.76 $14.77
23 2026-02-28 Henry's Diner $90.00 JW 4 $22.50 $22.50 $22.50 $22.50 $22.50
24 2026-02-28 Ice Cream Tour $26.00 HG 4 $6.50 $6.50 $6.50 $6.50 $6.50
25 2026-02-23 IGA Beer $27.14 HG 3 $9.05 $9.05 $9.05 $9.04 —
26 2026-02-28 76 Paul St Garage $6.00 JK 4 $1.50 $1.50 $1.50 $1.50 $1.50
27 2026-02-28 ExxonMobil $47.42 JK 4 $11.86 $11.86 $11.86 $11.86 $11.84
28 2026-03-01 National Car Rental $610.81 JK 4 $152.70 $152.70 $152.70 $152.70 $152.71
In [15]:
# Total owed and total paid per person
totals = {p: 0.0 for p in people}
paid = {p: 0.0 for p in people}

for _, row in df.iterrows():
    shares = calc_shares(row)
    for p in people:
        totals[p] += shares[p]
    payer = str(row['Paid by']).strip()
    if payer in paid:
        paid[payer] += float(row['Total'])

totals_df = pd.DataFrame([{
    'Person': p,
    'Total Paid': f"${paid[p]:,.2f}",
    'Total Owed': f"${totals[p]:,.2f}",
    'Net (Paid - Owed)': f"${paid[p] - totals[p]:,.2f}"
} for p in people])

print("\nSummary:")
totals_df

Summary:
Out[15]:
Person Total Paid Total Owed Net (Paid - Owed)
0 HG $1,851.12 $1,339.32 $511.80
1 JK $1,430.82 $1,339.32 $91.50
2 JW $695.70 $1,302.21 $-606.51
3 DB $1,176.27 $1,173.06 $3.21

Step 4 — Running Balances (Cumulative)¶

In [16]:
balances = {p: 0.0 for p in people}
balance_history = []

for _, row in df.iterrows():
    shares = calc_shares(row)
    payer = str(row['Paid by']).strip()
    for p in people:
        if p != payer and shares[p] > 0:
            balances[p] -= shares[p]      # p owes payer
            balances[payer] += shares[p]  # payer is owed

balance_history.append({
    'Person': p,
    'Net Balance': round(balances[p], 2),
    'Status': '🟢 Is Owed' if balances[p] > 0 else ('🔴 Owes Others' if balances[p] < 0 else '⚪ Settled')
} for p in people)

bal_df = pd.DataFrame([{
    'Person': p,
    'Net Balance ($)': round(balances[p], 2),
    'Status': '🟢 Is Owed $' + f"{balances[p]:.2f}" if balances[p] > 0.01 
              else ('🔴 Owes $' + f"{abs(balances[p]):.2f}" if balances[p] < -0.01 else '⚪ Settled')
} for p in people])

print("Net balances after all expenses:")
print(bal_df.to_string(index=False))
print()
print(f"Sanity check — balances sum to: ${sum(balances.values()):.2f}  (should be $0.00)")

Net balances after all expenses:
Person  Net Balance ($)            Status
    HG           511.80 🟢 Is Owed $511.80
    JK            91.50  🟢 Is Owed $91.50
    JW          -606.51    🔴 Owes $606.51
    DB             3.21   🟢 Is Owed $3.21

Sanity check — balances sum to: $-0.00  (should be $0.00)

Step 5 — Breakdown by Trip¶

In [17]:
trip_totals = {}

for _, row in df.iterrows():
    trip = str(row['Trip']).strip()
    shares = calc_shares(row)
    payer = str(row['Paid by']).strip()
    if trip not in trip_totals:
        trip_totals[trip] = {'total': 0.0, 'by_person': {p: 0.0 for p in people}}
    trip_totals[trip]['total'] += float(row['Total'])
    for p in people:
        if p != payer and shares[p] > 0:
            trip_totals[trip]['by_person'][p] += shares[p]

print("Trip totals and per-person amounts owed within each trip:\n")
for trip, data in trip_totals.items():
    print(f"📍 {trip}  —  Total Spent: ${data['total']:,.2f}")
    for p, amt in data['by_person'].items():
        if amt > 0:
            print(f"   {p}: owes ${amt:.2f}")
    print()

Trip totals and per-person amounts owed within each trip:

📍 SLC  —  Total Spent: $1,234.54
   HG: owes $213.06
   JK: owes $233.68
   JW: owes $311.50
   DB: owes $143.69

📍 Montreal  —  Total Spent: $1,124.19
   HG: owes $282.88
   JK: owes $277.17
   JW: owes $223.71
   DB: owes $48.50

📍 CO  —  Total Spent: $915.12
   HG: owes $65.97
   JK: owes $162.81
   JW: owes $228.78
   DB: owes $228.78

📍 Vermont  —  Total Spent: $1,880.06
   HG: owes $280.40
   JK: owes $303.96
   JW: owes $355.68
   DB: owes $470.00

Step 6 — Minimum Transactions Settlement¶

Using a greedy algorithm to minimize the number of payments needed to settle all debts.

In [18]:
# Minimize transactions
bal = {p: round(balances[p], 2) for p in people}
debtors  = sorted([(p, -b) for p, b in bal.items() if b < -0.01], key=lambda x: -x[1])
creditors= sorted([(p,  b) for p, b in bal.items() if b >  0.01], key=lambda x: -x[1])

d_list = [[p, amt] for p, amt in debtors]
c_list = [[p, amt] for p, amt in creditors]

settlements = []
i = j = 0
while i < len(d_list) and j < len(c_list):
    debtor, debt   = d_list[i]
    creditor, cred = c_list[j]
    pay = round(min(debt, cred), 2)
    settlements.append({'FROM (pays)': debtor, 'TO (receives)': creditor, 'Amount': f"${pay:.2f}"})
    d_list[i][1] -= pay
    c_list[j][1] -= pay
    if d_list[i][1] < 0.01: i += 1
    if c_list[j][1] < 0.01: j += 1

s_df = pd.DataFrame(settlements)
print("🏦 FINAL SETTLEMENT TRANSACTIONS:")
print("="*42)
print(s_df.to_string(index=False))
print()
for s in settlements:
    print(f"  ➡️  {s['FROM (pays)']} pays {s['TO (receives)']}: {s['Amount']}")

🏦 FINAL SETTLEMENT TRANSACTIONS:
==========================================
FROM (pays) TO (receives)  Amount
         JW            HG $511.80
         JW            JK  $91.50
         JW            DB   $3.21

  ➡️  JW pays HG: $511.80
  ➡️  JW pays JK: $91.50
  ➡️  JW pays DB: $3.21

Step 7 — Visual Summary¶

In [19]:
import matplotlib
# matplotlib.use('Agg')
import matplotlib.pyplot as plt
import matplotlib.patches as mpatches
import numpy as np

fig, axes = plt.subplots(1, 2, figsize=(13, 5))
fig.patch.set_facecolor('#0f172a')

# ─ Left: Net Balance Bar Chart ─
ax1 = axes[0]
ax1.set_facecolor('#1e293b')
colors = ['#22c55e' if balances[p] > 0 else '#ef4444' for p in people]
vals = [balances[p] for p in people]
bars = ax1.bar(people, vals, color=colors, width=0.5, edgecolor='white', linewidth=0.5)
ax1.axhline(0, color='white', linewidth=0.8, linestyle='--', alpha=0.4)
ax1.set_title('Net Balance Per Person', color='white', fontsize=13, pad=12, fontweight='bold')
ax1.set_ylabel('Amount ($)', color='#94a3b8')
ax1.tick_params(colors='white')
ax1.spines[:].set_visible(False)
for bar, val in zip(bars, vals):
    label = f'${val:+,.2f}'
    ax1.text(bar.get_x() + bar.get_width()/2, val + (8 if val >= 0 else -18),
             label, ha='center', va='bottom', color='white', fontsize=10, fontweight='bold')

# ─ Right: Spend by Trip Pie ─
ax2 = axes[1]
ax2.set_facecolor('#1e293b')
trip_spend = df.groupby('Trip')['Total'].sum()
pie_colors = ['#6366f1','#f59e0b','#10b981','#ec4899','#3b82f6']
wedges, texts, autotexts = ax2.pie(
    trip_spend.values, labels=trip_spend.index,
    colors=pie_colors[:len(trip_spend)], autopct='%1.1f%%',
    startangle=140, textprops={'color': 'white'}, pctdistance=0.75
)
for at in autotexts: at.set_fontsize(9)
ax2.set_title('Total Spend by Trip', color='white', fontsize=13, pad=12, fontweight='bold')

plt.tight_layout(pad=2)
# plt.show()
# print("Chart saved.")
No description has been provided for this image
In [ ]:
In [ ]: