Excel Power Query: forex, gold and stock prices from an API

Excel Power Query can turn a market data API response into a small watchlist without VBA. This guide starts with one EURUSD quote, then shows how to add gold, silver and US stocks. The table keeps bid, ask, a midpoint, the value's timestamp and the refresh time separate, so a saved snapshot is not mistaken for a streaming price.

Get a free SiftingIO API key to try the example, and check that your account includes the market you want to query. The forex API, commodities API and US stocks API use the same quote shape, but access depends on your subscription.

This recipe targets desktop Excel on Windows. Power Query refreshes on demand or on a minute-based schedule while the workbook is open; it does not receive WebSocket updates.

Start with one symbol and a private workbook#

In Power Query Editor, choose Manage Parameters, then New Parameter. Name it SiftingKey, choose Text, and paste your key as the current value.

A parameter keeps the key out of worksheet cells, but it is not a secret vault: someone with access to the workbook's query definitions can read it. Keep this first workbook private. The Web API credential option later in the guide avoids saving the key in the query.

Start with EURUSD alone. Once a manual refresh works, add only the markets your account can access:

Use the symbol catalog to check identifiers before extending the list.

Read a quote, not just a price#

The request is GET /v1/last/quote/{venue}/{symbol}. A quote is one JSON object, not an array of rows. Here is a synthetic example, not a live observation:

{
  "s": "EURUSD",
  "b": "1.17320",
  "B": "100000",
  "a": "1.17340",
  "A": "100000",
  "t": 1789723798800
}

The lowercase fields b and a are bid and ask; uppercase B and A are sizes. Prices arrive as strings. The timestamp t is Unix epoch milliseconds; in this example it represents 2026-09-18 09:29:58.800 UTC.

The REST documentation describes the quote and previous-close endpoints. This recipe uses individual requests rather than a multi-symbol snapshot, keeping the initial setup small and the response mapping visible.

A 503 stale_snapshot means the API could not serve a sufficiently fresh quote. It does not prove that the market is closed: a feed interruption can produce the same result. The example tries GET /v1/last/close/{venue}/{symbol} as a labelled fallback. It puts that value in a separate close column, never in bid, ask or midpoint. The returned close date is shown as supplied; do not assume it always means yesterday or today's session close.

Paste the Power Query M code#

Create a Blank Query from Data > Get Data, open Advanced Editor, and paste the following. It references the SiftingKey parameter and starts with one symbol.

let
    BaseUrl = "https://api.sifting.io",
    Symbols = {[venue = "forex", symbol = "EURUSD"]},
    FetchedAt = DateTimeZone.RemoveZone(DateTimeZone.FixedUtcNow()),

    ToNumber = (v as any) as nullable number =>
        try Number.FromText(Text.From(v, "en-US"), "en-US") otherwise null,

    ToDateTime = (ms as any) as nullable datetime =>
        if not Value.Is(ms, type number) then null
        else try #datetime(1970, 1, 1, 0, 0, 0)
            + #duration(0, 0, 0, ms / 1000) otherwise null,

    ErrorNote = (reply as record) as text =>
        Text.From(reply[status]) & " "
        & Text.From(reply[body][error]? ?? "unavailable"),

    Call = (path as text) as record =>
        let
            raw = Web.Contents(BaseUrl, [
                RelativePath = path,
                Headers = [#"X-API-Key" = Text.Trim(SiftingKey)],
                Timeout = #duration(0, 0, 0, 30),
                ManualStatusHandling = {400, 404, 406, 429, 500, 502, 503, 504}
            ]),
            status = Record.FieldOrDefault(Value.Metadata(raw), "Response.Status", 200),
            parsed = try Json.Document(raw) otherwise null,
            body = if Value.Is(parsed, type record) then parsed
                   else [error = "unreadable_body"]
        in
            [status = status, body = body],

    EmptyRow = (venue as text, symbol as text, note as text) as record =>
        [symbol = symbol, venue = venue, state = "error",
         bid = null, ask = null, mid = null,
         close = null, close_date = null, as_of_utc = null,
         fetched_at_utc = FetchedAt, note = note],

    FetchRow = (venue as text, symbol as text) as record =>
        let
            quote = Call("v1/last/quote/" & venue & "/" & symbol),
            q = quote[body],
            row =
                if quote[status] = 200 then
                    let
                        bid = ToNumber(q[b]?),
                        ask = ToNumber(q[a]?),
                        ts = ToDateTime(q[t]?)
                    in
                        if bid = null or ask = null or ts = null
                            or bid <= 0 or ask <= 0 or bid > ask
                            or (q[s]? ?? "") <> symbol then
                            EmptyRow(venue, symbol, "200 but unusable quote")
                        else
                            [symbol = symbol, venue = venue, state = "snapshot",
                             bid = bid, ask = ask, mid = (bid + ask) / 2,
                             close = null, close_date = null, as_of_utc = ts,
                             fetched_at_utc = FetchedAt, note = null]
                else if quote[status] = 503 and (q[error]? ?? "") = "stale_snapshot" then
                    let
                        prev = Call("v1/last/close/" & venue & "/" & symbol),
                        c = prev[body],
                        price = ToNumber(c[c]?),
                        day = try Date.FromText(c[d]?) otherwise null,
                        ts = ToDateTime(c[t]?)
                    in
                        if prev[status] = 200 and price <> null and price > 0
                            and day <> null and ts <> null then
                            [symbol = symbol, venue = venue, state = "previous close",
                             bid = null, ask = null, mid = null,
                             close = price, close_date = day, as_of_utc = ts,
                             fetched_at_utc = FetchedAt,
                             note = "stale quote; showing stored close, not a live price"]
                        else
                            EmptyRow(venue, symbol,
                                "stale quote; close unavailable: " & ErrorNote(prev))
                else
                    EmptyRow(venue, symbol, ErrorNote(quote))
        in
            row,

    Rows = List.Transform(Symbols, each FetchRow([venue], [symbol])),
    Typed = Table.TransformColumnTypes(Table.FromRecords(Rows), {
        {"symbol", type text}, {"venue", type text}, {"state", type text},
        {"bid", type number}, {"ask", type number}, {"mid", type number},
        {"close", type number}, {"close_date", type date},
        {"as_of_utc", type datetime}, {"fetched_at_utc", type datetime},
        {"note", type text}
    })
in
    Typed

Choose Anonymous when Excel asks how to connect to https://api.sifting.io: the key is supplied separately in the request header. Choose a privacy level appropriate to the data and your organization's policy. Then use Close & Load To > Table.

The explicit en-US number conversion reads decimal-point prices consistently on Turkish and other comma-decimal Excel installations. The midpoint is simply (bid + ask) / 2; it is a display value, not an executable fill price. See the bid and ask guide for spread interpretation.

RelativePath keeps the base address consistent. Microsoft documents this pattern in Web.Contents. It is useful for managing credentials, but it is not a guarantee that every Excel query will refresh in the Power BI service.

What the resulting table means#

These rows are illustrative, not a captured run. Extra symbols require extra entries in Symbols.

Keep both timestamp columns visible. as_of_utc belongs to the value; fetched_at_utc marks this query evaluation. Neither keeps advancing after the refresh. If a later refresh fails, Excel may retain the previously loaded table, so check the timestamps as well as the state. A row marked snapshot is not a promise that it is still fresh when you read it.

Authentication errors are different from row errors#

Microsoft's status-code handling documentation makes an important distinction: ordinary Power Query calls cannot manually handle 401 and 403 in the same way as a custom connector. They can trigger a credentials prompt or fail the refresh. That is why those codes are deliberately absent from ManualStatusHandling.

The code does not add its own retry loop for the manually handled responses. Do not repeatedly press Refresh All after a quota error. The HTTP 429 guide explains the difference between slowing down and waiting for capacity.

Choose a refresh interval that fits your allowance#

In Queries & Connections, right-click the query and open Properties. Depending on your Excel version and connection settings, you can enable refresh on opening the file and refresh every N minutes. The workbook must remain open for the desktop timer. Test manual refresh before enabling it.

There is no streaming connection here. If you need prices pushed as they change, use a different client with REST snapshots and WebSocket streams.

For one query evaluation, each symbol makes one quote request, plus one extra request if the stale-quote fallback runs. Excel previews and repeated evaluations can add traffic. An exact doubling is an assumption, not a maximum.

The following budget assumes six symbols, 32 scheduled refreshes per eight-hour day and 22 working days. It excludes refresh-on-open, previews, manual runs and other uses of the account:

If your allowance is 10,000 calls, the last scenario exceeds it. Keep margin for extra evaluations and shared account use. A paid tier also has limits unless its allowance explicitly says otherwise. Check your dashboard and current plans instead of hard-coding a per-minute entitlement into the workbook.

Share the workbook without embedding your key#

For a workbook you intend to share, replace the Headers option in the Call function with:

ApiKeyName = "api_key",

Keep the other options, and delete the SiftingKey parameter. Change the source credential from Anonymous to Web API in Data Source Settings, then enter the key in that credential dialog.

This uses the API's query-string authentication option. Power Query stores the supplied credential separately from the M source rather than putting the key in the workbook's parameter. Each recipient should configure their own authorized credential. It does not remove already-loaded market data from the shared workbook.

There is a trade-off: the key travels in the request URL and can appear in URL logs. Header-based keys also need redaction if infrastructure logs headers. Never share a workbook with your key embedded, and confirm authentication support separately if moving the query to a cloud refresh service. Microsoft's Web.Contents example shows the credential pattern.

Optional: read symbols from a worksheet#

Once the hard-coded list works, create a table named Symbols with venue and symbol columns and replace the list with:

Symbols = Table.ToRecords(Excel.CurrentWorkbook(){[Name = "Symbols"]}[Content]),

Use uppercase symbols and remove blank rows first. This combines workbook data with the API and can introduce privacy or query-partition errors. Matching privacy levels alone is not a universal fix. Follow Microsoft's Data Privacy Firewall explanation and keep the protection enabled; start with the literal list again if you need to isolate the problem.

Before relying on the sheet#

Run one authorized symbol manually, inspect the timestamps, then add other markets one at a time. Check a closed-market case and an unavailable symbol before enabling a timer. The example has been reviewed against the API implementation and Microsoft documentation; it has not been executed in desktop Excel as part of this review. Credential dialogs and refresh behaviour still need that application-level check.

For a treasury watchlist or a periodic report, this keeps the quote, its age and the fallback visible without VBA. If you use Google Sheets instead, the Apps Script walkthrough covers that separate workflow.

Get your API key