Cryptocurrency, first implemented in 2009, has weathered more than a decade of doubts and market swings. The first cryptocurrency, Bitcoin (BTC), remains the most prominent and has the highest value of all the cryptocurrencies that exist today. In this tutorial, we will learn three ways of importing Bitcoin historical price data to Google Sheets. Are you ready?
Import via GOOGLEFINANCE
Google Finance showcases cryptocurrency data. For example, Bitcoin 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 Bitcoin prices in USD for the last 30 days, use the following formula:
=GOOGLEFINANCE("Currency:BTCUSD","price",TODAY()-30,TODAY())
The result will appear as seen below:
This is the easiest method since the function is already built-in to Google Sheets. The main disadvantage is that you can only import the closing prices for each day. In financial markets, the closing price is only one of many relevant values including opening prices, 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 shows historical cryptocurrency data in a table format. The link is shown below:
https://www.investing.com/crypto/bitcoin/historical-data
Step 2: Scroll down to the daily Bitcoin 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/bitcoin/historical-data", "table", 1)
The data will be loaded quickly to your sheet.
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.
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 BTC 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=BTC&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.
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:
Related Articles
-Import Ethereum Prices to Google Sheets
-Import Multiple Sheets to Google Sheets
-Import Salesforce Data to Google Sheets
-Import Shopify Data to Google Sheets
Recommended articles
More resources
