Live stock prices in Excel with Power Query and an API

To get live Excel stock prices from an API, use Power Query's From Web connector: point it at a REST endpoint such as https://api.tickerlayer.com/stocks/snapshot/US:KO, send your key in an x-api-key header, load the JSON as a table, and set the query to refresh every few minutes. The same query shape works for currency pairs, crypto and commodities, and it reads exactly the numbers your code gets from the stock API.

Below: the one-symbol version you can do from a dialog in two minutes, then a reusable workbook with a key parameter, a snapshot function, separate stocks, FX and crypto sheets, a daily history sheet, and the refresh arithmetic. The menus are those of Excel for Windows with Microsoft 365; Excel for Mac offers fewer Get Data connectors, so check that From Web is there before you start.

Excel stock data type, STOCKHISTORY or an API

Excel already has two stock features. Type a ticker, choose Data > Stocks, and the cell becomes a data type with fields such as price, change and previous close. STOCKHISTORY returns historical bars into a range. Both are quick, and both are built for glancing at a portfolio: Microsoft's own note says the stock information is delayed and not for trading purposes, and STOCKHISTORY stops at daily, weekly and monthly intervals.

The strongest reason is the last-but-one row. When a spreadsheet sits next to an application, a report or a model, it should show the same numbers from the same source. A finance team checking a dashboard against Excel should never have to explain a difference caused by two different feeds.

The two-minute version: one symbol from the From Web dialog

1. 1Open the Web connectorData > Get Data > From Other Sources > From Web, then choose Advanced.
2. 2Enter the URLURL parts: https://api.tickerlayer.com/stocks/snapshot/US:KO. Stocks always carry a market prefix; a bare KO returns 400.
3. 3Add the headerUnder HTTP request header parameters type x-api-key on the left and your key on the right. Click OK.
4. 4Connect anonymouslyWhen Excel asks how to authenticate, pick Anonymous. The key already travels in the header.
5. 5Turn the record into a tablePower Query shows a record. Choose Into Table, then Close & Load. You now have a two-column table of field names and values.

A reusable workbook: one parameter, two functions, three sheets

1. 1Create the key parameterOpen Power Query (Data > Get Data > Launch Power Query Editor), then Home > Manage Parameters > New. Name TL_API_KEY, type Text, current value your key.
2. 2Create the symbol tablesOn three sheets, type a header Symbol and a few symbols, then Insert > Table and name the tables StockSymbols, FxSymbols and CryptoSymbols in the Table Design tab.
3. 3Add the two functionsIn Power Query choose Home > New Source > Other Sources > Blank Query, open Advanced Editor, paste each listing below, and name the queries fnSnapshot and fnQuotes.
4. 4Add one query per sheetThree more blank queries, each a single line: = fnQuotes("StockSymbols", "stocks"), = fnQuotes("FxSymbols", "forex") and = fnQuotes("CryptoSymbols", "crypto"). Load each to its own sheet.

(asset as text, symbol as text) as record =>
let
    Response = Web.Contents(
        "https://api.tickerlayer.com",
        [
            RelativePath = asset & "/snapshot/" & symbol,
            Headers = [#"x-api-key" = TL_API_KEY],
            // Read these statuses ourselves instead of failing the whole refresh
            ManualStatusHandling = {400, 401, 403, 404, 429}
        ]
    ),
    Status = Value.Metadata(Response)[Response.Status],
    Result =
        if Status = 200 then Json.Document(Response)
        else [symbol = symbol, error = "HTTP " & Text.From(Status)]
in
    Result

(tableName as text, asset as text) as table =>
let
    Source = Excel.CurrentWorkbook(){[Name = tableName]}[Content],
    Typed = Table.TransformColumnTypes(Source, {{"Symbol", type text}}),
    Symbols = Table.SelectRows(Typed, each [Symbol] <> null and Text.Trim([Symbol]) <> ""),
    WithData = Table.AddColumn(Symbols, "Data",
        each fnSnapshot(asset, Text.Upper(Text.Trim([Symbol])))),
    Expanded = Table.ExpandRecordColumn(WithData, "Data",
        {"last_price", "change_percent", "bid", "ask", "prev_close", "last_timestamp", "error"},
        {"Last", "Change %", "Bid", "Ask", "Prev close", "last_timestamp", "Error"}),
    // Unix milliseconds (UTC) to an Excel date-time
    WithTime = Table.AddColumn(Expanded, "Last trade (UTC)",
        each if [last_timestamp] = null then null
             else #datetime(1970, 1, 1, 0, 0, 0) + #duration(0, 0, 0, [last_timestamp] / 1000),
        type datetime),
    // The API sends 0.3883 for +0.39%; Excel percent format wants 0.003883
    AsPercent = Table.TransformColumns(WithTime,
        {{"Change %", each if _ = null then null else _ / 100, Percentage.Type}}),
    Result = Table.RemoveColumns(AsPercent, {"last_timestamp"})
in
    Result

ManualStatusHandling is what keeps one typo from breaking the sheet: a 404 for an unknown symbol becomes a row with HTTP 404 in the Error column while the other rows load normally. RelativePath keeps the base URL constant, which is also what Power BI needs if you ever publish the same query there. The time conversion is the usual Unix-milliseconds arithmetic; the Unix timestamp guide covers the edge cases.

FX pairs are six letters (EURUSD, USDJPY), crypto pairs are concatenated without a separator (BTCUSD, ETHUSD), and commodities use reference codes such as XAUUSD or NGASUSD. A fourth sheet for commodities is one more line: = fnQuotes("CommoditySymbols", "commodities"). For how FX quotes differ from a mid rate, see the exchange rate API guide.

Daily history in its own sheet

The snapshot answers "where is it now". For "how did it get here", call the bars endpoint, GET /stocks/agg/{symbol}/1/day/{from}/{to}, and turn its results array into a table. The same route serves 1, 5 and 15-minute, 1 and 4-hour, and daily bars, so an intraday sheet is the same query with 1/minute in the path.

let
    Response = Web.Contents(
        "https://api.tickerlayer.com",
        [
            RelativePath = "stocks/agg/US:KO/1/day/2026-01-02/2026-09-25",
            Query = [sort = "asc", limit = "5000"],
            Headers = [#"x-api-key" = TL_API_KEY]
        ]
    ),
    Body = Json.Document(Response),
    Bars = Table.FromRecords(Body[results]),
    Renamed = Table.RenameColumns(Bars,
        {{"o", "Open"}, {"h", "High"}, {"l", "Low"}, {"c", "Close"}, {"v", "Volume"}}),
    // Daily bars are stamped at 00:00 UTC of the session date
    WithDate = Table.AddColumn(Renamed, "Date",
        each Date.From(#datetime(1970, 1, 1, 0, 0, 0) + #duration(0, 0, 0, [t] / 1000)), type date),
    Result = Table.SelectColumns(WithDate, {"Date", "Open", "High", "Low", "Close", "Volume"})
in
    Result

Query values must be text, hence limit = "5000". One page holds up to 5,000 bars, which is about twenty years of daily data; for minute bars, pass the response's next_offset back as offset until it is null. The historical stock data guide explains official closes, split adjustments and session boundaries.

Refresh Excel stock prices on a timer without burning the quota

1. 1Open the query propertiesData > Queries & Connections, right-click a query, Properties.
2. 2Pick an intervalTick Refresh every N minutes. Tick Refresh data when opening the file so a morning open starts fresh.
3. 3Keep typing while it runsLeave Enable background refresh on so the grid stays usable during a refresh.

The timer is blunt: it fires whenever the workbook is open, at 3 a.m. and on Sundays too, and every refresh calls the API once per symbol. That makes the interval the biggest lever on your monthly request count.

The last row is the trap: the same 30-symbol workbook that fits an Individual plan during office hours overshoots it when someone leaves it open on a desktop over the month. Plan limits and the per-second ceiling are on the pricing page and in the limits docs.

Errors and what to do about them