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.
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:
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.
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
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.
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.
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.
This will prompt you to create an account.
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.
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.
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.
The API Key is now listed in the page.
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:
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:
-Import Bitcoin Prices to Google Sheets
-Import Multiple Sheets to Google Sheets
-Import Salesforce Data to Google Sheets
