下載活頁簿
下載發票追蹤活頁簿並用 Excel 開啟。內有示例發票、EUR/GBP/JPY/IDR 快照、USD 欄同審計記錄。
了解四張工作表
Invoice Tracker保存發票同公式，FX Snapshots保存日期觀測，Audit Trail記低固定決定，Read Me解釋流程同限制。
用原幣加入發票
喺藍色輸入格填寫編號、客戶、日期、貨幣、淨額、稅額、匯率日期同狀態。Net、Tax、Gross 保持發票原幣。
加入按日期嘅匯率快照
加入實際使用嘅歷史觀測，保存日期、貨幣、source、market_session、data_updated_at 同匯率。匯率代表 1 USD 對應嘅發票貨幣數量。 使用唯一 Snapshot ID，例如 EUR-20260602-v1，並驗證該 ID 只對應一行，而且快照貨幣同發票貨幣一致。修正時新增 v2，唔好覆寫 v1。data_updated_at 代表歷史資料寫入時間，唔等同於 effective_at。
Representative historical responsecopy
{
"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."
}檢查公式計算嘅 USD 欄
活頁簿按日期同貨幣配對 FX Snapshots，再用 FX Rate / USD 除金額。冇快照時 USD 欄會留空。 示例公式覆蓋發票第100行同快照第100行；擴展時要同時擴展兩處。Posted 只係標籤，唔會鎖定儲存格，亦唔會自動固定公式。
Excel formula · Net USDcopy
=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)
固定已入賬數值
發票入賬後唔好改快照列。如需更正或重述，加入新列並喺 Audit Trail寫低原因。
用 Power Query 加入新快照
Power Query 程式會取得指定日期同貨幣。共享活頁簿唔好保存 API 密鑰。 Power Query 輸出固定係 A:I 九欄。先載入 staging 工作表，再唔連表頭複製資料列，從第7行開始以值貼上。Refresh All 會取代查詢輸出，唔會自動追加不可變歷史。Windows 使用 Data → Get Data → From Other Sources → Blank Query；支援嘅 Microsoft 365 for Mac 使用 Data → Get Data → Launch Power Query Editor。選擇 Anonymous；需要密鑰時使用伺服器代理。
Power Query M · explicit snapshotcopy
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保留審計記錄
記錄入賬時間、使用嘅快照、來源/交易時段同固定原因。需要更完整證據時亦應保存原始回應。 喺 Notes 保存回應 timestamp 同 is_forward_filled，並記低 Snapshot ID、來源、交易時段同修正原因。
只將結果用於報告
呢個係報告同分析示例，唔會決定稅務、會計、合規或者結算金額。 示例容量到第100行；增加容量時要一齊擴展公式同查找範圍。