How to Import Ethereum Historical Prices to Google Sheets

Google Sheets can be very useful for analyzing Ethereum's performance, but only after transferring the historical price data into Google Sheets. Fear not, for this tutorial will offer three easy solutions

Table of Contents
  1. Import via GOOGLEFINANCE
  2. Import via IMPORTHTML
  3. Import via API
Table of contents
Chapter 1
Chapter 2
Chapter 3
  1. Import via GOOGLEFINANCE
  2. Import via IMPORTHTML
  3. Import via API
5 Minutes

Cryptocurrency, first implemented in 2009, has weathered more than a decade of doubts and market swings. Ethereum (ETH) is the second largest cryptocurrency in circulation. In this tutorial, we will learn three ways of importing Ethereum historical price data to Google Sheets. Are you ready?

Import via GOOGLEFINANCE

Google Finance showcases cryptocurrency data. For example, Ethereum prices in USD can be viewed here in real-time. You can directly import data from Google Finance by using the GOOGLEFINANCE function. To import Ethereum prices in USD for the last 30 days, use the following formula:

=GOOGLEFINANCE("Currency:ETHUSD","price",TODAY()-30,TODAY())

The result will appear like the one below:

Ethereum prices via GOOGLEFINANCE function

This is the easiest method since the function is already built-in to Google Sheets. The main disadvantage is that you can only get the closing prices for each day. In financial markets, the closing price is only one of a few relevant values including the opening price, the highest price, the lowest price, and the volume. These are often abbreviated as OHLCV. The two other methods in this tutorial will allow you to import all these values to Google Sheets. 

Import via IMPORTHTML

You might have found websites that let you browse tables of historical cryptocurrency data, and wonder how to extract them. Well, Google Sheets has its own function for that: the IMPORTHTML function. It allows you to import data from a table in a webpage. 

Here are the steps:

Step 1: Go to Investing.com or any site which displays historical data in a table. The link is shown below:

https://www.investing.com/crypto/ethereum/historical-data

Investing.com page containing historical Ethereum price data

Step 2: Scroll down to the daily Ethereum price table, then click the date range on the upper-right of the table. A calendar will pop up. By default, the prices for the latest 30 days will be listed. You can click the dates on the calendars or type the dates in MM/DD/YYYY format.

Investing.com Ethereum historical data. Calendar selected to change the range of dates displayed. 

The listed dates will change depending on what you set in the date range. Additionally, you can change the time frame to weekly or monthly, which will also adjust the time covered by the table. 

Step 3: Once you set the time range, add the following formula to the first cell in your sheet:

=IMPORTHTML("https://www.investing.com/crypto/ethereum/historical-data", "table", 1)

The data will be loaded quickly to your sheet.

Data loaded from Investing.com via IMPORTHTML

Import via API

Another option is to import data via API. For this case, we will use the API Connector add-on, so make sure you installed it in your Google Sheets. 

For the API, we will use CryptoCompare. CryptoCompare has a free plan with full features but is capped at 250,000 lifetime calls. This is sufficient for personal projects and for those who do not need to regularly access cryptocurrency data several times a day. 

For this tutorial, we will avail of the Personal plan, which is free. 

Step 1: Go to https://min-api.cryptocompare.com/pricing. Click the Get your free key under the Personal plan. 

CryptoCompare pricing options

This will prompt you to create an account. 

CryptoCompare account creation options.

You can connect your account with either of your Facebook or Google accounts, or type the email address and set the password yourself. 

Step 3: Once you created an account, you will be brought to the API Keys page. You need an API key to access data via an API. Click Create an API Key

CryptoCompare API Keys list page. No API Key listed yet; click the Create an API Key to generate a new key. 

You will be asked for the access settings of the API key. Click Read All Price Streaming and Polling Endpoints, and make sure that the two listed options nested below it are checked as well. Click Add

Create new API Key. List of access permissions to be set for the API key. 

Then, you will also be asked for the purpose of generating the API Key. You can choose any option that suits your purpose best. For this example, Building an app is chosen. Click Save afterwards. 

CryptoCompare asks for the reason for using their API before the key is generated. 

The API Key is now listed in the page. 

API Key listed. 

Step 4: Unlike other sites that offer crypto prices data via API, CryptoCompare’s documentation contains a tool to help you build the API link. Click the link below:

Documentation | CryptoCompare Cryptocurrency Data API 

For this tutorial, we want to access the historical Ethereum prices data in USD for the last 30 days. You can use the following API link:

https://min-api.cryptocompare.com/data/v2/histoday?fsym=ETH&tsym=USD&limit=30&api_key=*insert_api_key_here*

Replace *insert_api_key_here* with the API Key you generated in the previous steps. Paste the API Key directly; do not wrap the key in quotation marks or in any other symbol.  

Step 5: Install API Connector add-on in your Google Sheets spreadsheet. Click here to learn more about data-gathering add-ons in Google Sheets. Afterwards, insert the modified API link above to the API URL box in API Connector. 

The output looks like as follows:

API Connector and the data loaded in Google Sheets. 

Step 6: The datetime loaded from CryptoCompare is in UNIX time. To convert it to UTC, use the following formula:

=*cell_containing_unixtime*/86400+DATE(1970,1,1)+time(5,30,0)

Using this formula and a set of other formulas, you can then reformat the data a little. The output can look like the one shown below:

Final output
Thank you! Your submission has been received!
Oops! Something went wrong while submitting the form.

May 10, 2022

How to Import Ethereum Historical Prices to Google Sheets

Google Sheet spreadsheet

Cryptocurrency, first implemented in 2009, has weathered more than a decade of doubts and market swings. Ethereum (ETH) is the second largest cryptocurrency in circulation. In this tutorial, we will learn three ways of importing Ethereum historical price data to Google Sheets. Are you ready?

Import via GOOGLEFINANCE

Google Finance showcases cryptocurrency data. For example, Ethereum prices in USD can be viewed here in real-time. You can directly import data from Google Finance by using the GOOGLEFINANCE function. To import Ethereum prices in USD for the last 30 days, use the following formula:

=GOOGLEFINANCE("Currency:ETHUSD","price",TODAY()-30,TODAY())

The result will appear like the one below:

Ethereum prices via GOOGLEFINANCE function

This is the easiest method since the function is already built-in to Google Sheets. The main disadvantage is that you can only get the closing prices for each day. In financial markets, the closing price is only one of a few relevant values including the opening price, the highest price, the lowest price, and the volume. These are often abbreviated as OHLCV. The two other methods in this tutorial will allow you to import all these values to Google Sheets. 

Import via IMPORTHTML

You might have found websites that let you browse tables of historical cryptocurrency data, and wonder how to extract them. Well, Google Sheets has its own function for that: the IMPORTHTML function. It allows you to import data from a table in a webpage. 

Here are the steps:

Step 1: Go to Investing.com or any site which displays historical data in a table. The link is shown below:

https://www.investing.com/crypto/ethereum/historical-data

Investing.com page containing historical Ethereum price data

Step 2: Scroll down to the daily Ethereum price table, then click the date range on the upper-right of the table. A calendar will pop up. By default, the prices for the latest 30 days will be listed. You can click the dates on the calendars or type the dates in MM/DD/YYYY format.

Investing.com Ethereum historical data. Calendar selected to change the range of dates displayed. 

The listed dates will change depending on what you set in the date range. Additionally, you can change the time frame to weekly or monthly, which will also adjust the time covered by the table. 

Step 3: Once you set the time range, add the following formula to the first cell in your sheet:

=IMPORTHTML("https://www.investing.com/crypto/ethereum/historical-data", "table", 1)

The data will be loaded quickly to your sheet.

Data loaded from Investing.com via IMPORTHTML

Import via API

Another option is to import data via API. For this case, we will use the API Connector add-on, so make sure you installed it in your Google Sheets. 

For the API, we will use CryptoCompare. CryptoCompare has a free plan with full features but is capped at 250,000 lifetime calls. This is sufficient for personal projects and for those who do not need to regularly access cryptocurrency data several times a day. 

For this tutorial, we will avail of the Personal plan, which is free. 

Step 1: Go to https://min-api.cryptocompare.com/pricing. Click the Get your free key under the Personal plan. 

CryptoCompare pricing options

This will prompt you to create an account. 

CryptoCompare account creation options.

You can connect your account with either of your Facebook or Google accounts, or type the email address and set the password yourself. 

Step 3: Once you created an account, you will be brought to the API Keys page. You need an API key to access data via an API. Click Create an API Key

CryptoCompare API Keys list page. No API Key listed yet; click the Create an API Key to generate a new key. 

You will be asked for the access settings of the API key. Click Read All Price Streaming and Polling Endpoints, and make sure that the two listed options nested below it are checked as well. Click Add

Create new API Key. List of access permissions to be set for the API key. 

Then, you will also be asked for the purpose of generating the API Key. You can choose any option that suits your purpose best. For this example, Building an app is chosen. Click Save afterwards. 

CryptoCompare asks for the reason for using their API before the key is generated. 

The API Key is now listed in the page. 

API Key listed. 

Step 4: Unlike other sites that offer crypto prices data via API, CryptoCompare’s documentation contains a tool to help you build the API link. Click the link below:

Documentation | CryptoCompare Cryptocurrency Data API 

For this tutorial, we want to access the historical Ethereum prices data in USD for the last 30 days. You can use the following API link:

https://min-api.cryptocompare.com/data/v2/histoday?fsym=ETH&tsym=USD&limit=30&api_key=*insert_api_key_here*

Replace *insert_api_key_here* with the API Key you generated in the previous steps. Paste the API Key directly; do not wrap the key in quotation marks or in any other symbol.  

Step 5: Install API Connector add-on in your Google Sheets spreadsheet. Click here to learn more about data-gathering add-ons in Google Sheets. Afterwards, insert the modified API link above to the API URL box in API Connector. 

The output looks like as follows:

API Connector and the data loaded in Google Sheets. 

Step 6: The datetime loaded from CryptoCompare is in UNIX time. To convert it to UTC, use the following formula:

=*cell_containing_unixtime*/86400+DATE(1970,1,1)+time(5,30,0)

Using this formula and a set of other formulas, you can then reformat the data a little. The output can look like the one shown below:

Final output

Suscribe to get more data and analytics tips!

Thank you! Your submission has been received!
Oops! Something went wrong while submitting the form.