One of the joys of cryptocurrencies is that all the data you would ever want is publicly available. The problem that we have is trying to 'extract' it from the internet, in a format that can be used for data analysis.
Here I will show you how to get bitcoin data from CoinGecko. CoinGecko has a free open API, so we can get historical price data for any coin or token, as long as it's supported by the website. Also, because it's an open API, we don't need an API key.
You may find the documentation for the CoinGecko API here: https://www.coingecko.com/en/api/documentation
Here is an overview of my Alteryx Flow:
Our first tool is the text input tool, where we have the API URL that we want to 'call'.
Here is the link we use: https://api.coingecko.com/api/v3/coins/bitcoin/market_chart/range?vs_currency=usd&from=1609459200&to=1640995201
The important parts in the URL are:
- bitcoin - tell the API what cryptocurrency data we want. We could change this for another coin
- vs_currency=usd - tell the API which currency to use
- from=1609459200 - the start date of our query, in UNIX form
- to=1640995201 - the end date of our query, in UNIX form
I used https://www.unixtimestamp.com/ to convert UNIX time. The date's i've chosen are from the 1st Jan 2021 to 31st Dec 2021.
The next step is to use the Download tool
Our target URL should be whatever we entered in the text input. We want to output as a string
For our headers, we want to specify application/json. This is because data comes in JSON format.
In connections, we'll want to throttle records to 50 records per minute as that is CoinGecko's limit in the documentation
Our next tool is the JSON Parse tool. This will 'clean' our JSON so that we can restructure the API output later on
Our data will now look something like this:
The API pulls bitcoin prices, market cap and total trading volume. For this example, we will only look at prices, so we use a filter tool to filter JSON_Name by prrice
Next, split JSON_Name using text-to-column tool
Our data looks like this now:
Column "2" has our day of the year, whilst Column 3 tells us if the JSON_ValueString (or JSON Value) is either a price or a UNIX date. I've used a select tool to rename the fields so they make more sense. Also I drop the column with 'prices' in it as it's not needed anymore.
We'll then use a Crosstab tool to rotate our rows into columns
Here's the Configuration
We group by Day of Year, so that we have an individual row for each day of the year.
Our column headers become our date/price, and values, for the values. Our crosstab will result in the data looking like this:
Use a select tool to rename the columns
We're almost there now, we just need to convert our UNIX date into something more legible.
We're going to use a formula tool now.
The UNIX that the coingecko API uses is in milliseconds so first we divide by 1000, so it's in seconds instead
We then convert it into an actual legible date/time.
UNIX is defined as the number of seconds since 1st Jan 1970, so we are just adding our UNIX time to that date.
Finally we round our prices so that it's 2 decimal places
We use a select tool to drop the 'day of year' column and then finally we can output. Choose whatever file type you'd like. In my case, I went with a tableau hyper file, but .csv or .xlsx would work fine as well.
The final output looks like this:
And there you have it, we can now extract historical bitcoin data