Pulling cryptocurrency prices into Google Sheets (3 Methods)

Are you looking for a way to pull cryptocurrency prices into your Google spreadsheet automatically with a formula? In this lesson I am going to show you three different methods to pull crypto prices into Google Sheets. These methods do NOT require add-ons.

With the first method we will use the GOOGLEFINANCE function, which is very easy to use, and then I will show you a more advanced method so that you can pull any crypto price… and this method uses the IMPORTXML function. I will also show you how to use the IMPORTDATA function to pull crypto prices into your Google spreadsheet.

Below I go over an example of using this method in detail, and then further below I will teach you how to use the IMPORTXML function.

Note that a new method for pulling crypto prices has been added to this page, (the ability to use the IMPORTDATA function for crypto prices). This IMPORTDATA method is described in the video directly below, or you can scroll down to the bottom of the page to read about it. Watch the other video further down the page to get more detail on using the IMPORTXML function. You can also click here to get even more detail on using IMPORTXML.

Click here to get your Google Sheets cheat sheet

Or click here to take the dashboards course

The IMPORTXML function is able to pull in any crypto price, where the GOOGLEFINANCE function is limited on the cryptocurrencies that it can pull. ETHUSD (Ethereum) is another symbol that the GOOGLEFINANCE function will pull.

Formulas for pulling crypto prices into Google Sheets

The formulas below can be used to pull crypto prices into a Google spreadsheet.

*Note that when using the IMPORTXML function, websites often change the XPath of their website elements and so you will need to follow the lesson below to learn how to retrieve the correct XPath for your formula.

Pull cryptocurrency prices into Google Sheets with the GOOGLEFINANCE function

First let's go over the more simple method for pulling crypto prices into Google Sheets, which is by using the GOOGLEFINANCE function. With this method, all that you have to do is specify the cryptocurrency symbol for the criteria in the formula, and the formula will display the current price for that cryptocurrency.

When entering the cryptocurrency symbol that Google Sheets will expect, you have to enter the cryptocurrency “pair”, such as the pair between Bitcoin and US Dollars, which is represented as “BTCUSD”.

When using the GOOGLEFINANCE function with ordinary stock symbols, there is more criteria that you can specify, which would allow you to show many other types of stock data other than just the current price… but we are going to use the most basic criteria for this function by specifying a cryptocurrency symbol only, to display the current price of the specified crypto (When no additional criteria is given to the formula, the default is to show the current price).

The GOOGLEFINANCE function can also be used to pull a wide variety of data for stocks, such as past prices, shares outstanding, etc../ but I will go over that in another article. In this article I will stick to teaching how to pull the current price for cryptocurrencies.

Click here to get the free Google Sheets stock tracker templates.

Example: Pulling crypto prices with GOOGLEFINANCE

In this example we will use the GOOGLEFINANCE function to find the price of Bitcoin, displayed in US Dollars.

To pull the price of a cryptocurrency with the GOOGLEFINANCE function, simply enter the symbol for cryptocurrency pair between quotation marks, as the criteria for the formula, like this: =GOOGLEFINANCE("BTCUSD")

If you wat you can enter the symbol for the cryptocurrency into a cell in your spreadsheet, and then you can simply refer to the cell as the criteria for your formula, like this: =GOOGLEFINANCE("BTCUSD")

As you can see in the image above, by using the formulas above… the price for Bitcoin is pulled directly into a spreadsheet. You can also pull the price for Ethereum (ETHUSD) with the GOOGLEFINANCE function.

This content was originally created by Corey Bustos / SpreadsheetClass.com

Pull cryptocurrency prices into Google Sheets with the IMPORTXML function

The IMPORTXML function can be used to pull data directly from websites into a Google spreadsheet (This process is called "web scraping"). The formula requires two major components: #1 The website URL, and #2 the XPath of the "element" that contains the data that you want to pull into your spreadsheet.

Check out this article to learn how to use cell references with the IMPORTXML formula, as well as to learn more details about using the IMPORTXML formula. In that article I go over the process of finding the correct XPath in much more detail.

The video below goes over using IMPORTXML in more detail than the video at the top.

Below is an example of the IMPORTXML formula. Notice the first criteria is the URL, and the second criteria is the XPath which is a long code. Both of these criteria are entered between quotation marks.

=importxml("https://www.coinbase.com/price/loopring", "/html/body/div[1]/div/div/main/div/section[2]/div/div[1]/div[1]/ div/div/div/div/div[1]/div[1]/div[1]")

The URL can simply be copied from the top of the web page that you want to pull data from.

The XPath can be copied from the element of the webpage that you want to pull data from.

In the image above, you can see multiple examples of using the IMPORTXML function to pull crypto prices, from a variety of websites. This also will show you that you can use cell references to refer to the website URL, and the XPath, which in this case have been entered into cells A7 (URL) and B7 (XPath). You can see that the formula =IMPORTXML(A7,B7) fetches the crypto price because the correct URL and XPath are entered into the cells that the formula refers to.

The same image also shows multiple examples of using the GOOGLEFINANCE function (such as for pulling the price for Ethereum), for comparison.

Using cell references with the IMPORTXML formula

The main lesson on using the IMPORTXML function will go over using cell references in more detail, such as how to combine text / cells to make the URL update by simply entering a stock / crypto symbol in a cell… but note that if you want, instead of entering the URL and XPath directly into the IMPORTXML formula, you can enter the URL and XPath into a spreadsheet cell, and then you can simply refer to those cells with your IMPORTXML formula. For example, if you pasted the website URL into cell A1, and you pasted the XPath into cell B1, your IMPORTXML formula could be built like this when using cell references:

=IMPORTXML(A1, B1)

Or you can use a combination of cell references and text, like this:

=IMPORTXML(A1, "/html/body/div[1]/div/div/main/div/section[2]/div/div[1]/div[1]/ div/div/div/div/div[1]/div[1]/div[1]")

Or like this:

=IMPORTXML("https://www.coinbase.com/price/loopring", B1)

Pulling crypto prices from a website

Each web page is made up of many "elements". Each button, toolbar, table, etc., is an "element". Each element has what is called an "XPath", which is code that tells a computer / Google Sheets where to find the element that you are specifying.

(Note that the XPath for website elements will change when companies update their websites, and so sometimes you will need to update the XPath for your formula.)

The XPath can be found and copied on a web page, by doing the following:

• Go to the website that you want to pull data from / that contains the desired element
• Hover your cursor over the element that you want to pull data from / copy the XPath of
• Right-click, and then click "Inspect"
• After the code pops up on the right, drag your cursor up and down, and look on the left of your screen while doing so, so that you can see which elements are being highlighted as you hover your cursor over different lines of code
• When your cursor is hovered over the code for the correct element, right-click your mouse
• When the menu pops up, hover your cursor over "Copy", and then click "Copy full XPath"

After following the steps above, the XPath will be copied to your computer's clipboard, and you will be able to copy and paste it into your IMPORTXML formula (Between quotation marks, as shown below).

"/html/body/div[1]/div/div/main/div/section[2]/div/div[1]/div[1]/ div/div/div/div/div[1]/div[1]/div[1]"

The process of finding the XPath as well as using the IMPORTXML formula, is demonstrated with images in the example below.

Example: Pulling crypto prices with IMPORTXML

Further below I will teach you how to find the "XPath", but here are the basic instructions for pulling crypto prices with the IMPORTXML function.

To pull crypto prices into a Google spreadsheet with the IMPORTXML function, follow these steps:

• Type =IMPORTXML( to begin the formula
• Paste or type the URL for the web page that you are pulling crypto prices from (between quotation marks) like this: =importxml("https://www.coinbase.com/price/loopring"
• Type a comma, and then paste the XPath for element that you are pulling the price data from (between quotation marks) like this: =importxml("https://www.coinbase.com/price/loopring", "/html/body/div[1]/div/div/main/div/section[2]/div/div[1]/div[1]/ div/div/div/div/div[1]/div[1]/div[1]")
• Press enter on the keyboard

Note that some elements contain multiple columns of data, or multiple rows of data. When this is the case, the formula's result in your spreadsheet will split the data into separate cells, as shown in the image below, where the cryptocurrency price is split into three different columns (The dollar sign, the dollar amount, and the number of cents).

When this happens, simply add the appropriate cells together, or refer to the cell that contains the data that you need with a cell reference, or by using the INDEX function. In this case we add the cell that contains the dollar amount to the cell that contains the number of cents, to get the full cryptocurrency price (See further below).

Notice in the image below, that the formula in cell T6, adds together cells Z6 and AA6, so that cell T6 displays the entire cryptocurrency price (1 + 0.19 = 1.19).

Finding the XPath for the crypto price in the above example:

Here are the instructions for finding the XPath in the example above, with images included.

First, go to the web page that contains the crypto price that you want to pull, in this case I am going to https://www.coinbase.com/price/loopring

Then hover your cursor over the price / hover your cursor over the element that contains the price.

Right-click your mouse, and then click "Inspect".

A menu with website code will pop up on the right. The code that is highlighted represents the element that you right-clicked on. This may or may not be the correct element. Notice in the image below how on the left, the number "21" is highlighted, which is only a portion of the entire price.

Drag your cursor up and down through the lines of website code, and watch the left side of the screen to see when the correct element is highlighted in blue color.

In this example the cursor is dragged upwards until the element containing the full price is highlighted, as shown below.

When your cursor is hovered over the code for the desired element, right-click your mouse.

A menu will pop up after right-clicking. Hover your cursor over "Copy", and then click "Copy full XPath".

Now you can paste the XPath into your IMPORTXML formula in Google Sheets, as described in the example above.

Pull cryptocurrency prices into Google Sheets with the IMPORTDATA function

There is another method for pulling cryptocurrency prices into a Google spreadsheet that I want to teach you. This method, which uses the IMPORTDATA function, is possible with much thanks to the website cryptoprices.cc. The creator of this website uses an API from Coin Gecko to create a resource that allows us to use the IMPORTDATA function to pull crypto prices into a Google spreadsheet.

Visit the website below to learn more about cryptoprices.cc and the great resource that they are providing.

https://cryptoprices.cc/

The link below can be found on the main page that is linked above. This page shows all of the crypto pairs that you can use for pulling crypto prices with IMPORTDATA.

https://cryptoprices.cc/sitemap.txt

To use the IMPORTDATA function to pull crypto prices, simply refer to the proper crypto pair / URL, provided by cryptoprices.cc, like this =IMPORTDATA("https://cryptoprices.cc/BTC/")

https://cryptoprices.cc/BTC/

The pair / URL shows above, will give you the price for Bitcoin when you use the URL as the criteria for the IMPORTDATA function, as described above / shown below.

Below you can see that I have entered the URL into a spreadsheet cell (A4), and I have referred to the cell with the IMPORTDATA function, like this: =IMPORTDATA(A4)

The IMPORTDATA function pulls CSV data into a spreadsheet… and again the reason we can use this function to pull crypto prices into Google Sheets, is because the creator of cryptoprices.cc has created a resource that enables the IMPORTDATA function to pull a long list of crypto prices.

Now you know three different ways to pull cryptocurrency prices into your Google spreadsheet!

Click here to get your Google Sheets cheat sheet

Or click here to take the dashboards course