Live Forex, Gold and Crypto Prices in Google Sheets
The biquote add-on gives Google Sheets two formulas: one for a live price, one for converting between currencies, metals, crypto and Grand Bazaar gold. It's free, and you don't need an API key or an account.
1. Install
In Google Sheets, open Extensions → Add-ons → Get add-ons, search for biquote and install it. Then Extensions → biquote → Open biquote opens the sidebar.
2. A live price
=BIQUOTE("EURUSD")
=BIQUOTE("XAUUSD", "ask")
=BIQUOTE("BTCUSDT", "change")The second argument picks the field: mid (default), bid, ask, high, low, change (daily %), spread, time, stale or name. Pass a range to price a whole column in one request:
=BIQUOTE(A2:A50, "bid")Symbols are the ones on the market pages: forex pairs like EURUSD, metals like XAUUSD, crypto like BTCUSDT, stocks and indices. The sidebar searches them for you.
3. Convert
=BIQUOTE_CONVERT(100, "USD", "TRY")
=BIQUOTE_CONVERT(B2, "EUR", "JPY")
=BIQUOTE_CONVERT(1, "XAU", "EUR")
=BIQUOTE_CONVERT(0.5, "BTC", "GBP")When the feed quotes the pair directly (EURTRY), that rate is used. Otherwise the conversion goes through USD, the way our converter widget does. The amount can also be a range.
4. Grand Bazaar (Kapalıçarşı) gold and rates
Turkish names work in both formulas:
=BIQUOTE("gram altın")
=BIQUOTE("çeyrek", "satış")
=BIQUOTE_CONVERT(2, "çeyrek", "TL")
=BIQUOTE_CONVERT(1, "gram altın", "EUR", "kapalicarsi")
=BIQUOTE_CONVERT(1000, "dolar", "TL", "kapalicarsi")Recognised names include gram altın, has altın, çeyrek, yarım, tam, ata, ata beşli, gremse, 22 ayar, 14 ayar, gümüş, ons, plus dolar, euro, sterlin and TL. The optional fourth argument "kapalicarsi" uses Grand Bazaar dealer rates for every leg; the default "market" uses broker rates. All Grand Bazaar prices are on tr.biquote.io.
5. Refreshing
Google Sheets only recalculates a custom formula when one of its inputs changes. Formulas inserted from the sidebar include a last argument, BIQUOTE_TICK, which points at a hidden cell the add-on updates:
=BIQUOTE("EURUSD", "mid", BIQUOTE_TICK)- Extensions → biquote → Refresh prices now updates everything that references it.
- While the sidebar is open, it can refresh every 1, 5 or 15 minutes.
Prices are cached for one minute, so refreshing faster than that returns the same numbers.
Good to know
- Prices are indicative. They come from third-party sources and can be delayed or wrong. Check the Terms of Use before relying on them.
- When a market is closed you get the last quote;
=BIQUOTE("XAUUSD", "stale")tells you whether it is live. - The add-on sends only symbol names and currency codes to biquote. See the Privacy Policy.