Cryptocurrencies are outstanding virtual or digital coins that are decentralized under the blockchain system and are bought and traded online.
These currencies date back to 2009 when they were created. Some people invested in them already but with the advent of the coronavirus pandemic, cryptocurrencies suddenly became the talk of the town due to their extremely high returns and profit gained when they are sold in 2020.
This increased the crypto market as more buyers invested their money and more sellers gained millions for selling the currencies they bought in peanuts.
Do you know that you can also pull not only stock market prices but also cryptocurrency prices on your Google sheet? Of course, you can. This feature helps stock and crypto traders and buyers keep tabs on the value increase or decrease in crypto prices.
In this tutorial, we are going to discuss and show you how to pull crypto prices and values effectively as data, into our google sheets using a set of functions and formulas.
Formulas for Pulling Crypto Prices.
There are three major functions used in pulling crypto prices. These formulas move from simple to advanced or complex categories but we would disseminate them into easy steps that ease understanding.
These formulas are;
- The GOOGLEFINANCE function.
- The IMPORTXML function.
- The IMPORTDATA function.
Pulling Cryptocurrency Prices into Google Sheets with the GOOGLEFINANCE function.
The GOOGLEFINANCE function is the most basic formula that is used to pull not only stock prices but also crypto prices. It is a limited function as it only pulls the prices of common cryptocurrencies such as Bitcoin(BTC), Ethereum(ETH), Dogecoin, Litecoin(LTC) etcetera.
In the sample worksheet below, we want to get the live and latest crypto prices of Bitcoin, Ethereum, Dogecoin and Litecoin.
This function is used by following the listed steps below.
Step 1: We want to find the live prices of Bitcoin therefore, we input the Google finance function under the price column, where we want the results displayed.
=GOOGLEFINANCE(cell reference)
Or in place of entering the cell reference, you could input the name of the cryptocurrency you want to work on. That is;
=GOOGLEFINANCE(“BTCUSD”)
Step 2: Click Enter and a Loading text appears in the cell.
This means the function is searching for matching data on the Google Finance website. After a few seconds, the live crypto price is pulled out of the Google Finance website automatically.
Step 3: You can highlight the other cells below by dragging your cursor and dropping the pulled prices into the respective cryptocurrencies.
This formula is copied into the other cells. To avoid errors, be sure to use the first formula where the cell reference containing the cryptocurrencies is a parameter in the formula.
Step 4: Click Enter and the crypto prices are pasted into their respective columns. Some of the cryptocurrency’s prices were not inputted, which means the GOOGLEFINANCE function wasn’t able to extract the prices of Litecoin and Dogecoin.
As said earlier, this function is limited and sometimes, won’t produce the prices of some cryptocurrencies. Therefore, we would utilize the IMPORTXML function.
Note that these live prices change accordingly to the rise and dip in prices of the cryptocurrencies in the general crypto market. Check out why #N/A error come and how to fix it.
The prices of cryptocurrencies are ever-changing. They could increase in a minute and decrease in the next. This implies that you won’t get the exact prices displayed in the sample worksheet used in this tutorial.
Pulling Cryptocurrency Prices into Google Sheets with the IMPORTXML Function.
This function is more complex than the GOOGLEFINANCE function, but it performs more and pulls more results than the previous function. The IMPORTXML function takes the format below.
=IMPORTXML(“URL”, “XPath_query”)
The IMPORTXML function requires two parameters which are the
- URL- This is the website address that contains the prices and other details on the cryptocurrency. This is where we would copy the prices from.
- XPath is the element containing the prices you want to pull into the worksheet.
The URL website could be any crypto-based website that deals mostly in the sales, research, development and analytics of cryptocurrencies.
A notable website is the Coinbase website which we will use to dissect the IMPORTXML function properly.
Using Cell references with the IMPORTXML Formula.
We can also input cell references into the IMPORTXML function as parameters. Here, the copied URL and the copied XPath are pasted into random cell references in your worksheet.
Therefore, when we copy the parameters we paste them into cell references. For instance, we copy the URL of the Coinbase website and paste it into cell C9.
Then, we go back to the website and copy the XPath element of the price and paste it into cell C10. The cell references have been established successfully.
Then, when we enter the parameters of the formula =IMPORTXML(” URL”, “XPath_query”)
We can substitute the parameters with the established cell references which take the format as seen below.
=IMPORTXML(“C9”, “C10”)
Or we can also mix the cell references and copied data together like this and vice versa.
=IMPORTXML(“copied URL”, “C10”) or =IMPORTXML(“C9″, ” copied XPath_query”)
Click Enter and the price of Bitcoin is pulled into the three columns in your worksheet.
Pulling Cryptocurrency Prices from a Website.
In this topic, we are going to go deeper and analyze how to pull cryptocurrency prices from a website. This is a continuation of how to use the IMPORTXML function. With the steps below, we would explain the IMPORTXML function and its parameters in detail.
Step 1: Go to the Coinbase website
Step 2: Remember to copy the URL and paste it into the function on your worksheet.
Step 3: Go back to the loaded website. Here, we want to extract the web path containing the element we need in our formula.
Step 4: Hover your cursor over the price of Bitcoin. Right-click on the price. An array of options are produced here. Click on Inspect.
Step 5: A tab with multiple elements is displayed on the right side or below the website depending on the browser you use.
Move your cursor across the elements to find the right one that highlights the price of Bitcoin that you want to pull.
Step 6: Right-click on the element. Click Copy.
Step 7: Click Copy XPath.
Step 8: Go back to your worksheet, and paste the XPath in quotation marks into the formula. /Html/body/div[1]/div/div[1]/main/div/section[2]/div/div[1]/div[1]/div/div/div/div/div[1]/div[1]/div[1]
Step 9: The price of the Bitcoin is input into three columns. The first result column holds the dollar sign.
The second result column holds the price in the dollar value and the third result column holds the price balance in cents. Therefore, to get the overall value, you add the data in the three columns together.
Repeat the steps to input other crypto prices from the Coinbase website into your Google sheet.
Pulling Cryptocurrency Prices into Google Sheets with the IMPORTDATA function.
Lastly, we can use the IMPORTDATA function to pull cryptocurrency prices into the Google Sheet. This function has recently been disseminated and made easier and more available for use by the Software Developer of the crypto prices. cc website.
This website creates a resource list that allows the IMPORTDATA function to pull long lists of cryptocurrency costs into your worksheet. With the steps provided below, we will learn how to use this website with the IMPORTDATA to pull prices.
Step 1: Go to the website below. Crypto Prices | Prices for CryptoCurrency Coins.
Step 2: Scroll down and click on Lists of all Tokens available. A long list of different cryptocurrencies and their prices are listed there.
Step 3: Then, we copy the currencies that we want their prices pulled.
Step 4: Paste the copied tokens on your worksheet.
Step 5: Use the =IMPORTDATA(URL) formula to pull the prices of the data. The cell references contain the copied URLs. Example:
=IMPORTDATA(“https://cryptoprices.cc/BTC/“)
Step 6: Click Enter on the keyboard. The price of Bitcoin when you use the URL as the parameter is shown in the result.
Also, instead of typing the URL in full, you can refer the IMPORTDATA function to the cell containing the URL text. The function takes this format.
=IMPORTDATA(A2).
Click Enter and an accurate live price of Bitcoin is pulled successfully.
Step 7: Select the rows below and copy the formula to auto-fill the prices in their respective cells.
This is another easy function, that is quick in producing results and pulling out live prices of any kind of cryptocurrency.
Final Thoughts:
I hope now you know How To Pulling cryptocurrency prices into Google Sheets. These formulas are essential tools used to extract live prices that keep tabs on unit rises and drops of cryptocurrencies daily. This feature is very important for individuals investing in cryptocurrencies.
I hope you found this tutorial helpful. Be sure to practice the functions regularly and master their full potential. Thank you for reading.
