Trip Expense Tracker

Builds an Excel workbook that answers: what did the trip really cost, what will be paid back, and how much did fees on cash cost.

Read the xlsx skill (/mnt/skills/public/xlsx/SKILL.md) before creating the file.

Step 1: Ask (only what is missing)

1. Base currency: the one all totals use (e.g. CNY, USD). Default: ask.
2. Cash currency: the destination currency obtained as cash (e.g. UZS).
3. Other currencies paid in (card currency, home currency, etc.).
4. Cards used (names or last four digits).
5. Reimbursable categories (e.g. visa fee only).
6. Trip dates.

Never hard-code a currency. Everything reads from Settings.

Step 2: Workbook structure

Sheet order: Summary, Expenses, Cash_in, Rates, Lists. Sheet names have no spaces.

Each data range is an Excel Table with the tbl prefix: tblExpenses, tblCashIn, tblRates, and one single-column table per list (tblCategories, tblCurrencies, tblMethods, tblCards, tblSources, tblYesNo).

Settings (top of Rates, rows 1-2, tables start in row 4): named cells Base_currency and Cash_currency, each with a dropdown from tblCurrencies.

tblExpenses (one row per payment)

Date | Category | Description | Amount | Currency | Amount_base | Method | Card | Reimb | Note

• Amount_base: =[@Amount]/XLOOKUP([@[Currency]],tblRates[Currency],tblRates[Official_per_base])
• Method is Card or Cash. Card is filled only when Method = Card.
• Reimb is Yes only for reimbursable items. Never subtract the reimbursement from the row.

tblCashIn (one row per cash received)

Date | Source | Description | Amount | Currency | Cost | Cost_currency | Method | Card | Cost_base | Rate_got

• Source: ATM, Exchange, Person.
• Cost is what it cost the user to get the cash, in Cost_currency. Repayments and gifts: Cost = 0, Cost_currency = base currency.
• Cost_base: =[@Cost]/XLOOKUP([@[Cost_currency]],tblRates[Currency],tblRates[Official_per_base]). Always use Official_per_base here, never an effective rate (circular).
• Rate_got: =IF([@[Cost_base]]=0,"",[@Amount]/[@[Cost_base]])

tblRates (units per 1 base currency)

Currency | Official_rate | Official_per_base

• Official_rate is typed, all in one common reference currency (the row whose rate is 1).
• Official_per_base: =[@[Official_rate]]/INDEX([Official_rate],MATCH(Base_currency,[Currency],0))

Lists

One single-column table per dropdown. Data validation sources: =INDIRECT("tblCategories[Category]") etc.

Default categories: Lodging, Food, Local transport, Intercity transport, Tours, SIM, Visa fee, Fees, Shopping, Tips, Other.

Step 3: Summary (formulas only; columns Item | Value | Note)

Stack blocks vertically with a blank row and a coloured title row between them:

1. Headline (base currency): total cash cost =SUM(tblCashIn[Cost_base]), total card cost =SUMIFS(tblExpenses[Amount_base],tblExpenses[Method],"Card"), total trip cost (sum), reimbursable =SUMIFS(tblExpenses[Amount_base],tblExpenses[Reimb],"Yes"), personal cost (total - reimbursable).
2. Cash exchange: total exchanged cash currency, value at official rate, real cost, effective rate, fee rate. Exclude zero-cost rows with tblCashIn[Cost_base],">0".
3. By card: per card, purchases, ATM/cash cost, total, reimbursable. Totals must match block 1.
4. By category: SUMIFS on Amount_base; add "Unspent cash (value)" so the total equals total trip cost.
5. Cash balance per cash currency: received minus spent with Method = Cash.
6. Checks: IF(ABS(a-b)<0.01,"OK","Check") for each cross-total, and base currency present in tblRates.

Headings that name the currency use formulas such as ="Total trip cost ("&Base_currency&")".

Design rules

• Dates are real dates, displayed yyyy-mm-dd. Date is always column A.
• Same header style on every sheet; freeze row 1 of data sheets.
• Yellow fill = typed input, grey = formula.
• Number formats: #,##0 for large-unit currencies, #,##0.00 for others.
• Column names have no spaces or brackets. In formulas always use [@[Column_name]].
• Headers must contain no trailing spaces.
• Do not count cash twice: spending cash is recorded in Expenses (Method = Cash) but only the cost of obtaining it (Cash_in) enters the trip total.

Daily use (tell the user)

Log each payment and each cash receipt the same day, pick the Card, keep receipt photos for reimbursable items, then open Summary and confirm every check says OK.

Known pitfalls

• Base_currency must be a workbook-scoped name pointing at the value cell. Do not quote it inside formulas.
• Table structured references need Excel; Google Sheets needs plain ranges instead.
• If a reimbursement is paid in another currency, keep the original amount and currency on that row.