How to get live gold prices in Excel

=LET(j,WEBSERVICE("https://api.goldprice.dev/v1/spot/XAU-USD-SPOT"),s,SEARCH("""price"":""",j)+9,VALUE(MID(j,s,SEARCH("""",j,s)-s))) returns XAU/USD per troy ounce in Excel 365 for Windows. WEBSERVICE is unavailable on Mac and the web. No key needed. Each workbook recalculation uses the 5,000 calls/month anonymous allowance. Free prices lag 15 minutes. For scheduled refresh, download the companion workbook, add your API key and symbol to GoldpriceSettings, then paste the included Power Query M code in Windows. Mac can refresh supported connections authored in Windows.

Download the Excel companion workbook

**Download the goldprice.dev Excel template**. It includes a Setup table, the full query as copyable text, a quota calculator, and an expected-output example. It deliberately ships with a blank API key and no live connection.

Create a free key at goldprice.dev/onboarding. The free tier needs no credit card.

Enter the key and symbol on Setup

Open the workbook and enter your key in Setup!B5. Change Setup!B6 only if you want a symbol other than XAU-USD-SPOT. These cells form the GoldpriceSettings table read by the query.

The key remains visible in the workbook. Remove it before sharing the file, or use an organization-approved secret-management pattern.

Create the query in Excel for Windows

In Excel for Windows, choose Data → Get Data → From Other Sources → Blank Query, open Advanced Editor, and replace its contents with the query below. The same code is in the workbook and available as a Power Query file. Rename the query GoldPrice.

let    Settings = Excel.CurrentWorkbook(){[Name="GoldpriceSettings"]}[Content],    ApiKey = Text.Trim(Text.From(Table.SelectRows(Settings, each [Setting] = "ApiKey"){0}[Value])),    Symbol = Text.Upper(Text.Trim(Text.From(Table.SelectRows(Settings, each [Setting] = "Symbol"){0}[Value]))),    CheckedKey = if ApiKey = "" then error "Enter your goldprice.dev API key in Setup!B5" else ApiKey,    Raw = Web.Contents(        "https://api.goldprice.dev",        [            RelativePath = "v1/spot/" & Symbol,            Headers = [#"X-API-Key" = CheckedKey]        ]    ),    Source = Json.Document(Raw),    Result = #table(        {"field", "value"},        {            {"symbol", Source[symbol] & "-" & Source[quote_currency]},            {"price", Number.FromText(Source[price], "en-US")},            {"bid", if Record.HasFields(Source, "bid") and Source[bid] <> null then Number.FromText(Source[bid], "en-US") else null},            {"ask", if Record.HasFields(Source, "ask") and Source[ask] <> null then Number.FromText(Source[ask], "en-US") else null},            {"computed_at", Source[computed_at]},            {"is_stale", Source[is_stale]}        }    )in    Result

Load and refresh the result

Click Done → Close & Load. If Excel asks how to connect to https://api.goldprice.dev, choose Anonymous; the query sends your key in the header. Refresh with Data → Refresh All.

Excel for Mac can refresh supported Power Query connections created in Windows, but it does not offer the full Power Query Editor needed to author this connection. The download is a companion template, not a preconnected workbook.

Switch currency or metal

Change Setup!B6, then refresh. Symbols use METAL-CURRENCY-SPOT. The query uppercases and trims the value automatically.

XAU-USD-SPOTXAU-INR-SPOTXAU-EUR-SPOTXAG-USD-SPOT

Expected output

{  "symbol": "XAU",  "quote_currency": "USD",  "unit": "troy_ounce",  "contract_type": "spot",  "price": "4012.75",  "is_stale": false,  "computed_at": "2026-08-18T08:00:00Z"}

The table shows symbol, price, computed_at, and is_stale. Bid/ask are paid-only and may be null. Format numeric cells as Currency.

Common errors

Going further

• Build a price-alert column: =IF([@price] > 4800, "ALERT", "OK")
• Track gold and silver in separate queries, then combine them in one dashboard
• Pull recent daily bars from /v1/bars for a chart or backtest
• Connect the refreshed price to a valuation model instead of pasting values manually