A workaround for Google Finance broken for Bitcoin
1769 sats \ 12 comments \ @SpaceHodler 13 Oct bitcoin
I have a Google sheet that fetches the fiat price from Google Finance using the formula =GOOGLEFINANCE("CURRENCY:BTCUSD") and it hasn't changed in over a week - it's been stuck at 63,126.50. The web UI using the same source is https://www.google.com/finance/quote/BTC-USD?hl=en
It's odd that they still haven't fixed it, considering how resourceful they are. It's as if they didn't want to.
My first workaround was to fetch the price of an ETF and multiply it by a constant, but that only updates during US market hours.
I also tried the =IMPORTXML from this Reddit thread, but that doesn't update and it only works in the browser, not in the mobile Sheets app.
Eventually I created a script to make an API call, extract the price and write it into the cell. This also works in the mobile app (I've tested it on Android). I wanted an unauthenticated API and found Bitvavo, which unfortunately only has the BTC-EUR price, so you need to fetch =GOOGLEFINANCE("CURRENCY:EURUSD") and multiply the BTCEUR price by that.
To add the script, click on Extensions / Apps Script and paste the following (replace B3 with your target cell):
function fetchBitcoinPriceBitvavo() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet1"); // Change "Sheet1" to your actual sheet name
  var url = "https://api.bitvavo.com/v2/ticker/price?market=BTC-EUR"; // Bitvavo API URL for BTC-EUR price

  try {
    // Fetch the API data
    var response = UrlFetchApp.fetch(url);
    var jsonData = JSON.parse(response.getContentText());
    
    // Get the price from the API response
    var price = jsonData.price;
    
    sheet.getRange("B3").setValue(price);
  } catch (error) {
    Logger.log("Error fetching price: " + error);
    sheet.getRange("B3").setValue("Error fetching price");
  }
}
To make it update periodically, click on the clock icon, click on "Add Trigger" and set it to update at your selected interval, e.g. every 5 minutes.
write
preview
related posts
40 sats \ 4 replies \ @bisdak 13 Oct
btw, you can use coindesk, example: https://api.coindesk.com/v1/bpi/currentprice/USD.json or replace USD with your desired currency like https://api.coindesk.com/v1/bpi/currentprice/PHP.json
reply
10 sats \ 1 reply \ @nym 14 Oct
Thanks for the idea! I just created one for myself!
reply
15 sats \ 0 replies \ @bisdak 14 Oct
it’s a pleasure to share. actually there’s more like https://api.yadio.io/exrates or https://api.yadio.io/exrates/CAD as base currency.
reply
0 sats \ 0 replies \ @SpaceHodler OP 14 Oct
Updated.
function fetchBitcoinPriceCoinDesk() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet1"); // Change "Sheet1" to your actual sheet name
  var url = "https://api.coindesk.com/v1/bpi/currentprice/USD.json"; // CoinDesk API URL for Bitcoin price in USD
  
  try {
    // Fetch the API data
    var response = UrlFetchApp.fetch(url);
    var jsonData = JSON.parse(response.getContentText());
    
    // Get the rate_float (Bitcoin price in USD) from the API response
    var price = jsonData.bpi.USD.rate_float;
    
    // Set the price in the target cell (e.g., B3)
    sheet.getRange("B3").setValue(price);
  } catch (error) {
    Logger.log("Error fetching price: " + error);
    sheet.getRange("B3").setValue("Error fetching price");
  }
}
reply
0 sats \ 0 replies \ @SpaceHodler OP 14 Oct
Thanks, I'll update the sheet when I get on my laptop.
reply
10 sats \ 0 replies \ @OriginalSize 13 Oct
As annoying as this is, stacker.news fills in nicely. I'm not doing a workaround that still depends on Google so thank you for this code.
reply
10 sats \ 1 reply \ @486DX2 15 Oct
This seems to work:
=INDEX(googlefinance("Currency:BTCUSD", "price", TODAY()),2,2)
reply
0 sats \ 0 replies \ @SpaceHodler OP 14h
Thanks, but the bug has been fixed now at Google's end. Your formula gives me a different value, perhaps because it's less up-to-date.
reply
0 sats \ 0 replies \ @fernandoporazzi 14 Oct
1 BTC = 1 BTC, that's the only conversion I need.
Either way, cool workaround.
reply
0 sats \ 1 reply \ @Rsync25 13 Oct
Oh, why I’m not surprised? 🫠
reply
0 sats \ 0 replies \ @SpaceHodler OP 14 Oct
They've also removed the Bitcoin price and chart when you search for it in Google Search. But not for DOGE and other shitcoins.
reply
0 sats \ 0 replies \ @DarthCoin 13 Oct
wait ... people still use google ? What the fuck have in their broken mind?
reply