Buat pelacak faktur multi-mata uang di Excel

Workbook menyimpan nilai faktur dalam mata uang asli lalu mengubahnya ke USD memakai snapshot bertanggal yang Anda pilih. Baris yang sudah diposting tetap dapat direproduksi karena tanggal, sumber, data_updated_at, dan kurs disimpan bersama.

Unduh workbook

Unduh workbook pelacak faktur dan buka di Excel. Isinya contoh faktur, snapshot EUR/GBP/JPY/IDR, kolom USD, dan audit trail. Ganti contoh dengan catatan Anda.

Pahami empat sheet

Invoice Tracker berisi faktur dan rumus. FX Snapshots menyimpan observasi bertanggal. Audit Trail mencatat keputusan pembekuan. Read Me menjelaskan alurnya dan batasannya.

Tambahkan faktur dalam mata uang asli

Masukkan ID, pelanggan, tanggal, mata uang, neto, pajak, tanggal kurs, dan status di sel biru. Simpan Net, Tax, dan Gross dalam mata uang faktur.

Tambahkan snapshot kurs bertanggal

Tambahkan observasi historis yang benar-benar dipakai. Simpan tanggal, mata uang, source, market_session, data_updated_at, dan kurs. Kurs berarti unit mata uang faktur untuk 1 USD. Gunakan Snapshot ID unik seperti EUR-20260602-v1. Pastikan ID tersebut muncul tepat satu kali dan mata uang snapshot sama dengan mata uang faktur. Untuk koreksi, tambahkan v2 dan jangan menimpa v1. data_updated_at adalah waktu pembaruan data historis, bukan effective_at.

{
  "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."
}

Periksa kolom USD berbasis rumus

Workbook mencocokkan tanggal dan mata uang dengan FX Snapshots, lalu membagi jumlah dengan FX Rate / USD. Gross adalah Net + Tax; kolom USD kosong bila snapshot tidak ada. Rumus tersedia sampai baris faktur100 dan snapshot100; perluas keduanya bersama. Posted hanya label, bukan pengunci sel atau pembekuan otomatis.

=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)

Bekukan nilai yang sudah diposting

Setelah faktur diposting, jangan ubah baris snapshot. Untuk koreksi atau restatement, tambahkan baris baru dan jelaskan alasannya di Audit Trail.

Tambahkan snapshot dengan Power Query

Skrip Power Query mengambil tanggal dan mata uang tertentu. Muat sebagai baris baru dan jangan menaruh API key di workbook bersama. Power Query menghasilkan tepat sembilan kolom A:I. Muat dulu ke sheet staging, salin baris data tanpa header, lalu Paste Special sebagai values mulai baris7. Refresh All mengganti output query dan tidak otomatis menambahkan histori. Windows: Data → Get Data → From Other Sources → Blank Query; Microsoft 365 Mac yang mendukung: Data → Get Data → Launch Power Query Editor. Pilih Anonymous; gunakan proxy server jika perlu kunci.

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

Simpan jejak audit

Catat waktu posting, snapshot, sumber/sesi, dan alasan pembekuan. Simpan respons asli bila proses Anda memerlukan bukti lebih kuat. Simpan timestamp respons dan is_forward_filled di Notes, serta catat Snapshot ID, sumber, sesi, dan alasan koreksi.

Gunakan hasil hanya untuk pelaporan

Ini contoh untuk pelaporan dan analisis. Bukan penentuan pajak, akuntansi, kepatuhan, settlement, atau jumlah pembayaran. Contoh mendukung sampai baris100; perluas rumus dan lookup bersama-sama.