How to get prices of crypto tokens from Google Sheets
Learn how to track your crypto portfolio directly from Google Sheets. Manage tokens like Solana, Ethereum, and more with ease!
4 min read
•
04 Apr, 2024
If you want to get a Google Sheets document with all these methods implemented, keep reading until the end!
Coinmarketcap and Google Sheets
If you're like me, you have the necessity of constantly watching your investment portfolio to see how much is in green or red.
There are a couple of tools out there to track your crypto portfolio.
The most used is the CoinMarketCap's portfolio.
It's very basic but it does the job.
However, it has a couple of cons:
-
You need to enter the balance manually. You enter buys and sells of your assets. But it can not track automatically the current balance of a token in a crypto wallet.
-
CoinMarketCap does not have information on new tokens, low market cap tokens, which usually are the most profitable (and riskier, of course).
-
You can not add your own metrics, or create a custom dashboard.
For all of those reasons, most people end up creating their own tracking tool with some kind of spreadsheet using something like Google Sheets.
To have that spreadsheet updated with real data from the blockchain can be tricky, but it's doable, and I'll show you here a couple of ways and tricks so you can build your own custom crypto portfolio tracker.
Bitcoin Price in Google Sheets
Having the price of popular crypto assets like Bitcoin is very easy.
You can take advantage of Google Sheets native functions like GOOGLEFINANCE.
For instance, this is the formula to get the current price of Bitcoin: =GOOGLEFINANCE("CURRENCY:BTCUSD")
Scraping Coinmarketcap from Your Spreadsheet
To get the price of other crypto coins are trickier.
A solution is to retrieve CoinMarketCap's page of the crypto, and get the price from the HTML.
It's easier than it sounds as, fortunately, Google Sheets provide a function to do that.
For example, this is the formula to get $PEPE price: =IMPORTXML("https://coinmarketcap.com/currencies/pepe", "/html/body/div[1]/div[2]/div/div[2]/div/div/div[1]/div/section/div/div[2]/span",)
As you can see, this function has two arguments.
The first one is the the URL of the page of the token on CoinMarketCap. You can substitute “pepe” with the name of other token, replacing spaces with hyphens, like shiba-inu.
The second argument is more difficult to guess. It's the XPath of the element that contains the price in the HTML. To get this string, you can use a browser like Chrome or Firefox, go to the CoinMarketCap's page of the token, right-click on the price, and then click on “Inspect”. This will show you the current HTML of the page. In the HTML view, right-click again on the price, then “Copy”, and then “Copy XPath”. Now you can paste it into your formula.
Keep in mind that this code will change each time the developers of the website make some changes to the page. So, every time you see the formulas failing, most of the time the problem is that you need to copy the XPath again and update it in your spreadsheet.
Is There No Better Way to Do That?
Yes!
I was struggling with all those ways to keep track of my crypto portfolio for years.
That's why I'm building TokenTracker.Sh.
TokenTracker.Sh allows you to easily get, not only prices for the most popular crypto, but also the prices of recently launched tokens, which you cannot find on websites like CoinMarketCap or CoinGecko.
Moreover, you can also have an updated balance from crypto wallet, so you don't need to manually add buys and sells.
TokenTracker.Sh retrieves the information directly from the blockchain. We're already supporting Ethereum, Solana and Binance, and other networks are in our roadmap.
Get the Demo Spreadsheet
To see all this working (with some advanced touches) go to this document, duplicate it, and start creating your own portfolio tracker:
👉 Google Sheet Portfolio Tracker Demo 🔗
Cristóbal