Most people who invest in cryptocurrencies usually have a portfolio of cryptos, not a single cryptocurrency. Although there are websites that allow you to track the cryptos you have at any given moment, sometimes it can be interesting to analyse and study your portfolio in order to make better investment decisions.

Google Sheet

One of the tools that allows the greatest flexibility in calculations and analysis are spreadsheets. In this case I will focus on Google Sheet, because anyone with a google account will be able to access these spreadsheets.

Extracting prices

The most important operation to perform is to extract price information from a website to our spreadsheet. For this example I will use the Coinmarketcap website, but the procedure is the same for any other website.

The first thing we need to do is go to the homepage of a cryptocurrency, look for its price, right click and click on inspect.

Next, we right click on the HTML element and get its full XPath.

Now, we go to Google Sheet and we use the function IMPORTXML.

We need two parameters: the URL and the XPath. For the Bitcoin example we would have:

• URL: https://coinmarketcap.com/currencies/bitcoin/

• XPath: /html/body/div[1]/div[1]/div[1]/div[2]/div/div[1]/div[2]/div/div[2]/div[1]/div/span.

Using our function we would have the following result: $28,972.60.

Now we want to remove the dollar character. To do that I am going to use the function RIGHT with the following parameters:

• String: The price we just got

• Number of characters: Length of price obtained minus one.

At this point we could have our value if we use the dot as a decimal point. In my case, I have the decimals with a comma. So I have to make two substitutions: one to remove the commas from the thousands and the other to change the dots to commas.

Currency exchange

This price we get is in dollars, but you may want to see it in euros, pounds or another currency. We have a very useful function in Google Sheet called GOOGLEFINANCE. With this function we can obtain relationships between various currencies and cryptocurrencies.

If we only have Bitcoin for example, we can use this function to see its price in dollars or euros in an easier way than the one we have explained. The problem with this function is that not all cryptocurrencies are available.

In my particular case I use this function to convert the price obtained from dollars to euros.

Final formula

Here is the final formula I have used to see the price of Bitcoin in euros. As I use the comma for decimals I have to make two substitutions, so I would be showing the more complex case. Feel free to play with the formula.

=SUBSTITUTE(SUBSTITUTE(RIGHT(IMPORTXML(“https://coinmarketcap.com/currencies/bitcoin/";"/html/body/div[1]/div[1]/div[1]/div[2]/div/div[1]/div[2]/div/div[2]/div[1]/div/span");LEN(IMPORTXML("https://coinmarketcap.com/currencies/bitcoin/";"/html/body/div[1]/div[1]/div[1]/div[2]/div/div[1]/div[2]/div/div[2]/div[1]/div/span"))-1);",";"");".";",")*GOOGLEFINANCE("CURRENCY:EURUSD")