Build an Excel FX tracker with Gemini and Power Query

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.

• 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 10 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.

Write a macro-free Excel Power Query M query that callshttps://exchangerate.dev/docs/api-referenceand the anonymous endpoint /v1/latest/USD?symbols=JPY,KRW,EUR.Use Web.Contents with RelativePath = "v1/latest/USD" andQuery = [symbols = "JPY,KRW,EUR"]. Parse the JSON response,validate that rates, sources, data_updated_at, and market_sessionexist, then return columns Currency, Rate, Source,DataUpdatedAt, and MarketSession. Do not add an API key orinvent 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).

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.

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

Know the boundaries

This macro-free workbook is a useful private tracker and model input. Anonymous access is capped at 10 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.