AI disclosure: I used an AI assistant to help draft this post. I ran the code and checked the output myself.
A travel business sells one package but buys it from several suppliers, often in different currencies. The total in the bank rarely tells you which booking made money. In this post, we build a small Python script that reads two CSV files and prints the margin per booking.
All figures are made up.
The data
bookings.csv has one row per sale or cost. Every row carries a booking reference.
csv
booking,type,currency,amount,date
TRV-001,sale,AED,4500,2026-10-01
TRV-001,cost_hotel,EUR,800,2026-10-02
TRV-001,cost_transfer,AED,300,2026-10-03
TRV-001,cost_tour,AED,250,2026-10-03
TRV-002,sale,AED,3200,2026-10-05
TRV-002,cost_hotel,EUR,600,2026-10-06
TRV-003,sale,AED,2000,2026-10-07
rates.csv has one exchange rate per date and currency.
csv
date,currency,rate_to_aed
2026-10-02,EUR,4.30
2026-10-06,EUR,4.30
2026-11-02,EUR,4.35
The script
We use Decimal for money. Floats can give results like 3479.9999999999995, which I hit when I first tried 800 * 4.35.
python
import csv
from decimal import Decimal
from collections import defaultdict
def load_rates(path):
rates = {}
with open(path, newline="") as f:
for row in csv.DictReader(f):
rates[(row["date"], row["currency"])] = Decimal(row["rate_to_aed"])
return rates
def to_aed(amount, currency, date, rates):
if currency == "AED":
return amount
key = (date, currency)
if key not in rates:
raise KeyError(f"No rate for {currency} on {date}")
return amount * rates[key]
def margins(bookings_path, rates):
sales = defaultdict(Decimal)
costs = defaultdict(Decimal)
with open(bookings_path, newline="") as f:
for row in csv.DictReader(f):
value = to_aed(Decimal(row["amount"]), row["currency"], row["date"], rates)
if row["type"] == "sale":
sales[row["booking"]] += value
else:
costs[row["booking"]] += value
return sales, costs
if name == "main":
rates = load_rates("rates.csv")
sales, costs = margins("bookings.csv", rates)
for b in sorted(sales):
profit = sales[b] - costs[b]
pct = profit / sales[b] * 100 if sales[b] else 0
flag = " <-- no costs yet" if costs[b] == 0 else ""
print(f"{b}: sale {sales[b]:.2f} cost {costs[b]:.2f} margin {profit:.2f} ({pct:.1f}%){flag}")
The output
TRV-001: sale 4500.00 cost 3990.00 margin 510.00 (11.3%)
TRV-002: sale 3200.00 cost 2580.00 margin 620.00 (19.4%)
TRV-003: sale 2000.00 cost 0.00 margin 2000.00 (100.0%) <-- no costs yet
Two things stand out. TRV-001 has an 11.3% margin. TRV-003 shows a 100% margin only because no supplier cost has been entered yet. That is exactly the kind of row a monthly check should catch.
What a rate change does
Each row uses the rate for its own date. So the same EUR 800 costs different amounts on different dates:
python
to_aed(Decimal("800"), "EUR", "2026-10-02", rates)
Decimal('3440.00')
to_aed(Decimal("800"), "EUR", "2026-11-02", rates)
Decimal('3480.00')
That AED 40 difference takes TRV-001 from a 510 margin to 470. On thin margins, a small currency move matters.
Failing loudly
If a rate is missing, the script raises an error instead of guessing:
KeyError: 'No rate for USD on 2026-10-02'
For accounting data, a clear error is better than a silently wrong number.
Where to take it next
Add a refund type that subtracts from sales, and check refunds against the same booking.
Read the rates from a file that your bank or accounting tool exports.
Round only at the end, and test the rounding with a few hand-calculated bookings.
Add a unit test for the missing-rate case.
This is a teaching sketch, not a substitute for accounting software or tax advice. Rules about VAT, agent versus principal treatment and exchange gains differ by business, so check those with a qualified adviser.
What other checks would you add? Let me know in the comments.
Top comments (0)