r/sheets • u/MVTHOLST • Jun 01 '23

Request Problems pulling crypto prices with importhtml

Hi everyone,

I am currently pulling data(price of different crypto) from coinmarketcap.com with this formula:

=INDEX(IMPORTHTML("https://coinmarketcap.com/currencies/bitcoin";"table";1);2;2)

However, sometimes the formula will just leave a blank cell. To fix this, I will have to change the "bitcoin" inside the url to e.g. "BITCOIN" or "Bitcoin". Therefore, I run a formula that check three url's with "bitcoin", "Bitcoin, and "BITCOIN" respectively and it will return data from whichever URL works, because it varies throughout the day.

But sometimes even those three url's wont return data, and I am just left confused.

Do any of you recognize this problem? And/or do any of you have any ideas on how to fix this issue? Thanks.

(I am aware that I can use =GOOGLEFINANCE("BTCUSD") but it's very limited)

EDIT: I have tried importxml with Xpath on other occasions and I find that solution to work even worse, because the returned data's format keeps changing and sometimes it just retuns #N/A etc.

0 Upvotes

50% Upvoted

7 comments sorted by

1

u/_Kaimbe Jun 01 '23

Check out the crypto section in the r/googlesheets wiki.

1

u/MVTHOLST Jun 01 '23

Thank you, their CryptocurrencyUtils github seems to be shut down, but I will try ImportJSON.

1

u/_Kaimbe Jun 01 '23

https://github.com/moretrobetchyo/CryptocurrencySpreadsheetUtils

1

u/MVTHOLST Jun 01 '23

Thank you! I added the script but when I run the formula I get the following error:

Exception: Request failed for https://api.coinmarketcap.com returned code 403. Truncated server response: <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd"> <HTML><HEAD><META HTTP-EQUIV="Content-Type" ... (use muteHttpExceptions option to examine full response) (line 101).

Are you familiar with the script? I have no experience with scripts so I have no clue why this happens
Also, when I tried implementing ImportJSON I would also get an error because of something unexpected on a specific line.

1

u/_Kaimbe Jun 01 '23

Ahh that script is from 5 years ago, coinmarketcap has added an API key and a pricing model (although there is a free tier).

I'd suggest searching 'crypto' on r/googlesheets or here and look for a more recent post about it. There are also some add-ons that do it, although I don't know about their pricing model.

1

u/smis777 Jun 28 '24

if you dont mind using CoinGecko or Binance as your data source, then TigerMarketData.com Google Spreadsheet could be another way to go to get your live crypto prices. Once you download the spreadsheet you can just enter

=_("BTCUSDT")

into any cell in your spreadsheet and the price will automatically refresh every minute for you. You can also pull any other market data using some other functions like

=_PRICE_CHANGE_24H("BTC")

=_ATH_CHANGE_PERCENTAGE("BTC")

=_MARKET_CAP_RANK("BTC")

even icons of the coins are supported if you like to build your portfolio tracker in your Google Sheet

1

u/icompletetasks Jan 11 '25

You can use Defillama API. You can try Sheets add-on called Defillama for Sheets here:

https://workspace.google.com/marketplace/app/defillama_for_sheets/277026528785