Build a multi-currency invoice tracker in Excel

The workbook keeps invoice amounts in their original currency and converts them to USD using the unique Snapshot ID you choose. Posted rows stay reproducible because the ID, date, source, data_updated_at value, and rate are stored together instead of refreshing silently.

Download the workbook

Download the multi-currency invoice tracker workbook and open it in Excel. It includes ten illustrative invoices, EUR/GBP/JPY/IDR snapshots, calculated USD columns, and a compact audit trail. The numbers are examples; replace them with your own records and source observations.

Understand the four sheets

Invoice Tracker holds the invoice record and formula outputs. FX Snapshots stores a unique Snapshot ID for each observation and revision. Audit Trail records the freeze decision for posted rows. Read Me explains the workflow, rate direction, source fields, and reporting limitation.

Add an invoice in its original currency

Add the invoice ID, customer, invoice date, due date, currency, net amount, tax, Snapshot ID, and status in the input cells with blue text. Keep Net, Tax, and Gross in the invoice currency; do not convert the source amount before entering it.

Add the dated FX snapshot

For each invoice row, add the exact historical observation used for reporting to FX Snapshots. Give it a unique ID such as EUR-20260602-v1, then store the date, quote currency, source, market_session, data_updated_at, is_forward_filled, and rate. The rate means invoice currency units for 1 USD: for example, 0.85844 means 1 USD = 0.85844 EUR.

{
  "result": "success",
  "base": "USD",
  "date": "2026-06-02",
  "source": "ecb_daily",
  "sources": { "EUR": "ecb_daily" },
  "market_session": "open",
  "timestamp": "2026-09-08T11:52:18Z",
  "data_updated_at": "2026-06-02T00:00:00Z",
  "is_forward_filled": false,
  "rates": { "EUR": 0.85844 },
  "notice": "Indicative rates, not for settlement."
}

Review the formula-driven USD columns

The tracker matches the invoice Snapshot ID to exactly one row in FX Snapshots, then divides each invoice-currency amount by FX Rate / USD. The Gross column is Net + Tax; USD columns stay blank when the ID is missing or duplicated, the snapshot currency differs from the invoice currency, or its rate is invalid. The FX Source / Status column explains the failure. The download has formulas through invoice row 100 and snapshot lookups through row 100; extend both ranges together for a larger workbook.

=IF(OR(F6<0,G6<0,H6<0,K6="",L6="Missing snapshot ID",L6="Duplicate snapshot ID",L6="Currency mismatch",L6="Invalid rate"),"",F6/K6)

Freeze posted historical values

Changing Status to Posted does not lock cells or freeze formulas automatically. When an invoice is posted, keep its Snapshot ID and the referenced snapshot row unchanged. A later API response may revise a daily reference; replacing the old row would silently change a previously reported USD amount. Add a new row with a new ID such as EUR-20260602-v2, point only the intended invoice at it, and record the reason in Audit Trail.

Add a new snapshot with Power Query

The optional Power Query script calls the dated endpoint for one explicit date and currency. On Windows Excel, choose Data → Get Data → From Other Sources → Blank Query, then open Advanced Editor. On supported Microsoft 365 for Mac versions, start with Data → Get Data → Launch Power Query Editor and create a blank query. Paste the script, change SnapshotDate, Currency, and Revision, then load it to a separate staging sheet. The nine output columns match FX Snapshots A:I. Copy the data row without its headers and use Paste Special → Values in the next empty snapshot row, starting at row 7. Do not load the query over the snapshot table: Refresh All replaces query output; it does not append an immutable history. Use a new revision ID for corrections, then point only the intended invoice at that ID. Choose Anonymous unless you deliberately route the request through a server-side key; never put an API key in a shared workbook.

let
    SnapshotDate = "2026-06-02",
    Currency = Text.Upper(Text.Trim("EUR")),
    Revision = "v1",
    SelectedDate = Date.FromText(SnapshotDate, [Format = "yyyy-MM-dd", Culture = "en-US"]),
    RelativePath = "v1/" & Date.ToText(SelectedDate, "yyyy-MM-dd") & "/USD",
    Endpoint = "https://api.exchangerate.dev/" & RelativePath & "?symbols=" & Currency,
    Payload = Json.Document(Web.Contents("https://api.exchangerate.dev", [
        RelativePath = RelativePath,
        Query = [symbols = Currency],
        Timeout = #duration(0, 0, 0, 10)
    ])),
    Source = if Payload[base] <> "USD" or Payload[date] <> SnapshotDate
        then error "Unexpected response base or date" else Payload,
    RawRate = Record.Field(Source[rates], Currency),
    Rate = if not Value.Is(RawRate, type number) or RawRate <= 0
        then error "Expected a positive numeric rate" else RawRate,
    SnapshotId = Currency & "-" & Text.Replace(SnapshotDate, "-", "") & "-" & Revision,
    Notes = "Response timestamp=" & Source[timestamp] & "; is_forward_filled=" &
        (if Source[is_forward_filled] then "true" else "false"),
    Snapshot = #table(
        {"Snapshot ID", "Snapshot Date", "Currency", "Source", "Market Session", "Data Updated At (UTC)", "Rate / USD", "API Endpoint", "Notes"},
        {{SnapshotId, SelectedDate, Currency, Record.Field(Source[sources], Currency), Source[market_session], Source[data_updated_at], Rate, Endpoint, Notes}}
    )
in
    Snapshot

Keep the audit trail with the workbook

For each posted invoice, record when it was posted, which Snapshot ID was used, its source/session and forward-fill flag, and why the row is frozen. Preserve the raw response or a link to the request in your own controlled records if your process requires a stronger audit trail.

Use the result for reporting only

This workbook demonstrates indicative currency conversion for internal reporting and analysis. It does not determine tax treatment, accounting policy, compliance treatment, settlement value, or the amount a payment provider will charge. Review those decisions with your own finance and compliance process.