Tutorial/Excel FX tracker

Build an Excel FX tracker with Gemini and Power Query

Use Gemini to draft and explain a Power Query step, then let Excel fetch indicative exchange rates from exchangerate.dev. The workbook stays macro-free and keeps the observed timestamp and market session beside each value.

ERexchangerate.dev·Updated Sep 5, 2026·8 min read

Gemini can help write and troubleshoot Power Query M, but it does not fetch the rate for Excel. Excel does that through Web.Contents. This guide uses the anonymous latest endpoint, validates the response, and exposes source, data_updated_at, and market_session so a tracker never presents an unexplained number as live.

Key points
Gemini drafts and explains M; Excel Power Query makes the HTTP request and refreshes the table.
The query uses Web.Contents with RelativePath and Query, avoiding fragile URL concatenation.
The example requests USD against JPY, KRW, and EUR and shows each source and observation timestamp.
Anonymous access is capped at 12 requests a minute and 100 an hour per IP; no API key belongs in a shared workbook.
Rates are indicative and intended for models, analytics, and display, not for settling a trade.

Use Gemini for the query, not as the data source

Gemini can turn a plain-English requirement into Power Query M, explain a step, and suggest a fix when a refresh fails. It cannot make the workbook’s rate current by itself. The workbook still needs a query that calls the API, parses the JSON, checks that the requested currencies are present, and keeps the observation metadata beside the numbers.

When prompting Gemini, include the public documentation URL and the exact response fields you expect. Never paste an API key, private workbook data, or customer information into the prompt.

Prompt for Gemini
Write a macro-free Excel Power Query M query that calls
https://exchangerate.dev/docs/api-reference
and the anonymous endpoint /v1/latest/USD?symbols=JPY,KRW,EUR.
Use Web.Contents with RelativePath = "v1/latest/USD" and
Query = [symbols = "JPY,KRW,EUR"]. Parse the JSON response,
validate that rates, sources, data_updated_at, and market_session
exist, then return columns Currency, Rate, Source,
DataUpdatedAt, and MarketSession. Do not add an API key or
invent fallback values. Keep HTTP errors visible.

Create the Power Query

In Excel, open Data → Get Data → From Other Sources → Blank Query, then choose Advanced Editor. Replace the starter code with the query below. It uses a fixed base URL and passes the path and parameters separately. Microsoft documents RelativePath and Query as options for Web.Contents; the base URL also makes Power Query’s source and privacy handling easier to inspect (Microsoft documentation).

Power Query M · FXTracker
let
    Response = Web.Contents(
        "https://api.exchangerate.dev",
        [
            RelativePath = "v1/latest/USD",
            Query = [symbols = "JPY,KRW,EUR"],
            ManualStatusHandling = {429}
        ]
    ),
    Status = try Value.Metadata(Response)[Response.Status] otherwise 200,
    Checked = if Status <> 200 then
        error "The FX endpoint returned HTTP " & Text.From(Status)
      else Response,
    Json = Json.Document(Checked),
    Rates = try Json[rates] otherwise error "The response has no rates field",
    Sources = try Json[sources] otherwise error "The response has no sources field",
    UpdatedAt = try Json[data_updated_at] otherwise error "The response has no data_updated_at field",
    Session = try Json[market_session] otherwise error "The response has no market_session field",
    RequireRate = (rates as record, code as text) as number =>
        let value = try Record.Field(rates, code) otherwise null
        in if value is number and value > 0 then value else error (code & " is missing or invalid"),
    RequireSource = (sources as record, code as text) as text =>
        let value = try Record.Field(sources, code) otherwise null
        in if value is text and Text.Length(value) > 0 then value else error (code & " source is missing or invalid"),
    RequiredMetadata = if UpdatedAt is text and Text.Length(UpdatedAt) > 0 and Session is text and Text.Length(Session) > 0 then true else error "Required metadata is missing or invalid",
    Rows = if RequiredMetadata then Table.FromRecords({
        [Currency = "JPY", Rate = RequireRate(Rates, "JPY"), Source = RequireSource(Sources, "JPY"), DataUpdatedAt = UpdatedAt, MarketSession = Session],
        [Currency = "KRW", Rate = RequireRate(Rates, "KRW"), Source = RequireSource(Sources, "KRW"), DataUpdatedAt = UpdatedAt, MarketSession = Session],
        [Currency = "EUR", Rate = RequireRate(Rates, "EUR"), Source = RequireSource(Sources, "EUR"), DataUpdatedAt = UpdatedAt, MarketSession = Session]
    }) else error "Required metadata is missing or invalid",
    Typed = Table.TransformColumnTypes(Rows, {{"Currency", type text}, {"Rate", type number}, {"Source", type text}, {"DataUpdatedAt", type datetimezone}, {"MarketSession", type text}})
in
    Typed

Load a table that explains each value

Choose Done → Close & Load To… → Table. A successful refresh should produce one row per requested currency. The rate means units of the quote currency for one USD. Source, DataUpdatedAt, and MarketSession describe the observation; they do not turn it into an executable quote.

CurrencyRate and metadataInterpretation
JPYRate, Source, DataUpdatedAt, MarketSessionOne row with the observed value and its context
KRWRate, Source, DataUpdatedAt, MarketSessionOne row with the observed value and its context
EURRate, Source, DataUpdatedAt, MarketSessionOne row with the observed value and its context
Do not replace missing data with zero
A missing currency, missing metadata field, or failed request should stop the query with an error. Zero would look like a real rate and could silently corrupt a budget or model.

Refresh the tracker

Use Data → Refresh All when you want Excel to run the query again. Refresh behavior, scheduled refresh, and available Power Query features vary by Excel edition, operating system, privacy settings, and organizational policy. Do not assume that a workbook refreshes automatically or that the same trigger options exist on Mac and Windows. Test the exact Excel runtime where the workbook will be used.

Troubleshoot visible failures

FailureWhat it meansWhat to do
HTTP 429The anonymous per-IP limit was reached.Refresh less often, request fewer distinct pairs, or move the request behind a server-side proxy with a key.
HTTP 403The request was refused, often through Excel’s built-in credential or access handling.Check the endpoint and Excel privacy/source settings, then inspect the credential prompt or query error. Do not paste a key into a shared workbook.
Missing currency or metadataThe response is not the shape the tracker requires.Keep the query error visible and inspect the response before changing the M code.
No new value after Refresh AllExcel did not rerun the query or the response was cached.Inspect Query & Connections and the refresh details; do not label the old observation as current.

Know the boundaries

This macro-free workbook is a useful private tracker and model input. Anonymous access is capped at 12 requests a minute and 100 an hour per IP, and shared egress can make that capacity variable. If a shared workbook needs predictable account-level capacity, put the API key in a server-side service. Keep the key out of Power Query, workbook parameters, prompts, and version control. The returned values remain indicative and are not for settlement.

ER
exchangerate.dev
Integration guides for developers.

Keep reading

TutorialLive exchange rates in Google SheetsRead TutorialLive exchange rates in Excel with Power QueryRead ReferenceReading source and market sessionRead
More ComparisonsFixer vs exchangerate.devOpen Exchange Rates vs exchangerate.devCurrencylayer vs exchangerate.devCurrencylayer vs ExchangeRate-API
LearnReading source and market_session in your pipelineIndicative vs executable FX rates: what a rates API actually gives youECB reference rates, explained
Live RatesEUR/USDGBP/USDUSD/JPY