Cryptocurrencies are the driving force of the new gold rush. The author suggests using data analysis to better understand this emerging market.
Recently there is a feeling that money grows on trees.
The volume of stock trading reaches millions of
dollars, and market capitalization - billions
. It is time to talk about the gold rush, fueled by the emergence of an increasing number of new cryptocurrencies.
We live in an era of digital currencies. Appearing less than 10 years ago, the concept of cryptocurrency has already become widespread today. Despite such a short time, there are already over a thousand different cryptocurrencies on the market, and ICOs occur almost every day
As we get used to the new, rapidly gaining market, it is important to try to understand what is happening to it. There are many risks, both at the micro level (for example, personal investment) and at the macro level (for example, preventing market crashes or large capital losses). This is where we come into play.
We are dealing with data, or more precisely, I represent the development team of TimescaleDB
, a new open source PostgreSQL database for working with time series. We thought that it would be useful and exciting to analyze the cryptocurrency market using PostgreSQL and TimescaleDB (and R to visualize the received data).
In the course of the work, we analyzed historical data on OHLC-charts
for more than 1200 cryptocurrencies, kindly provided to us by CryptoCompare
(the last sample date is June 26 of this year). The step in this particular dataset is 24 hours, but it should be noted that TimescaleDB scales easily to work with much shorter time intervals. Our product, in particular, is excellently suited as a basic tool for working with the growing flow of data on new coins and exchanges.
Here is what you can learn for yourself from this post:
- A few valuable general conclusions about the state of affairs in the cryptocurrency market.
- Better understanding of how a bunch of TimescaleDB + PostgreSQL can simplify data analysis by time series.
- Get instructions for self-loading this data set, discover new trends (and maybe even make your own strategy for entering the market with their help!).
Disclaimer: this analysis should not be considered as a recommendation for those or other financial transactions. If you want to conduct your own analysis, pay attention to the instructions
, which describe the installation of TimescaleDB and the download of CryptoCompare
So, if 7 years ago you invested $ 100 in Bitcoin, now it would be worth ...
Let's start with the good old syndrome of lost profits
. If you are at least familiar with cryptocurrencies, you have probably heard about Bitcoin, the “great-grandfather” of all cryptocurrencies. It turns out that if in July 2010 you invested $ 100 in it, today this amount would increase to $ 5 million.
During this time, Bitcoin showed a rather pleasant dynamics (even taking into account the relatively recent collapse):
BTC to US dollar exchange rate at the time of the exchange closing in the last 7 years
Using PostgreSQL, we requested BTC price readings at two-week intervals by analyzing the exchange rate to the US dollar on the exchanges. Note: time_bucket
in this query are TimescaleDB's own functions (not available in PostgreSQL) used to analyze time series.
We hope that you did not buy bitcoins in February 2014 ...
However, it cannot be said that everything at BTC was always smooth. Let's take a closer look at the daily volatility of the course and perform calculations using the powerful PostgreSQL window functions
The rate of division of the BTC / USD of the current day by the rate of the previous (7-year period)
Due to the relative immaturity of the market, the course of Bitcoin is subject to significant fluctuations. A stable increase in value as a whole speaks about the success of cryptocurrency, but there is in its history one period of sharp growth, clearly out of the general picture, observed in early 2014. Having considered this period in more detail, we note major jumps in February-March 2014. Investors who invested in BTC at the peak of the market had to wait considerably, because the course soon stabilized, and soon did not reach the indicators at which the sale of bitcoins purchased at that time became a profitable business.The rate of division of the BTC / USD of the current day by the rate of the previous (2014)
Goodbye China, hello Japan
Cryptocurrency market is an international phenomenon. Studying trading volumes in terms of currencies, we noticed something interesting:
BTC trading volumes in various fiat currencies over the past 7 years (two-week intervals, multiple bars)
In 2014, there was a small jump in the value of Bitcoin in China, caused by the alleged devaluation of the yuan and the weakening of the domestic stock market of the country. This was followed by a boom in 2016 and early 2017: the Chinese currency dominated bitcoin trading.Yuan share in Bitcoin trade for the last year (two-week intervals)
In just a few months, this figure dropped sharply.
What is the reason? Here it is time to go beyond the analysis of numerical data and do the good old research. Our conclusions well illustrate the fact that it is impossible to rely only on quantitative data to study the market.
In early 2017, the People’s Bank of China put into effect
decrees restricting the activities of cryptocurrency exchanges. Already in February, the two largest stock exchanges in the country (OKCoin and Huobi.com) suspended the
withdrawal of cryptocurrency in exchange for foreign currency, and by the middle of this year the flow of Chinese transactions had disappeared. At the same time, Japan became the leader in the number of transactions with Bitcoins. It even went so far that Bitcoin was recognized as a
legal currency in April 2017.The amount of BTC in various currencies after a sharp decline in the share of BTC / CNY transactions in 2017. BTC / JPY - new leader in terms of volume (two-week intervals)
And now let's discuss what it would be like if you invested $ 100 in ETH in January 2017 ...
Despite the fact that the “Bitcoin train went away” back in 2010, there is no cause for concern. Many observers agree that even against the background of its high volatility, the Ethereum price drops look even more impressive (and the recent “correction”
is another proof of this). Let's look at the prices of Ethereum in Bitcoin equivalent (as is customary to quote it):
ETH to BTC at the close of the trading day for the last 3 years
However, as we know, Bitcoin itself is not marked by noticeable stability, which reduces the usefulness of the above graph. Let's look at ETH prices in fiat currencies, with the help of daily BTC exchange prices for fiat currencies. (To do this, we use the useful properties of the Postgres JOINs and several clever filters):
ETH rate to three fiat currencies at the close of the trading day for the last 3 years
In the first year of its existence, ETH surpassed any of the annual BTC growth figures for the entire history of the latter. An impressive 530% growth in the average closing price compared to the previous year is a good start. In general, by 2017, the cumulative growth rate for all these years fell in 2017 compared to 2016 to 200%. However, even this result still looks impressive for any other asset. As for the last half year, now prices for ETH have grown by 3000%. Therefore, if you invested $ 100 in ETH in January of this year (almost 7 months ago), today their cost would be $ 3 thousand.
Expressing the value of ETH in stable currencies (USD, EUR, CNY), we see that all three charts have the same shape. In the past six months, there has been a clear increase in all currency equivalents, except for BTC. The ETH / BTC rate chart, being similar to fiat currency charts, is much more prone to BTC cost fluctuations. As a result, attempts to express the price of ETH in BTC create an improbable impression of instability first. Obviously, the BTC is still too young currency to consider it base.
What about the other 1200 cryptocurrencies?
We hope that this brief overview of the BTC and ETH trends has enabled you to better understand the chaotic world of cryptocurrencies. So, what do we do with the other 1200 cryptocurrencies?
Well, for starters, let's use our data set to track their origin:Important note: Our data set contains information at the time of its collection, which could have happened after the ICO.
Timestamp of the first entry for each cryptocurrency (descending)
currency_code | min
The market is constantly evolving, and besides, judging by the daily increase in new cryptocurrencies, it is also constantly expanding. The list published above contains only 20 of the newest tokens as of June 26 and provides an idea of how many new cryptocurrencies appear every week.
Let's calculate the number of new cryptocurrencies that appear every day based on the first date of the pricing information on them:
Number of new cryptocurrencies per day for the last 4 yearsNumber of new cryptocurrencies per day for the current year
Number of new cryptocurrencies by date, daily statistics (descending)
day | count
When we request information about the first appearance of data on cryptocurrencies (to track their "age"), it becomes noticeable that the market is not only a group of investors, there is another category of its participants - creators of digital assets. Just recently, on May 25–28, according to our recruitment, there was a large influx of new coins — over 300 new tokens in less than a week. (Since our set only records pricing information on cryptocurrencies, the information about their appearance may not correspond to the dates of the ICO.)
Leaders and chasing cryptocurrency world
Cryptocurrency today is so much that it becomes difficult to distinguish between sensible and doubtful. How can you determine which ones are worthy of attention? Here you have one of the metrics: the total volume of exchange transactions for the last week.
Total volume of transactions of 10 main cryptocurrencies in US dollars for the last week (descending)Total volume of transactions of 10 main cryptocurrencies in US dollars for the last week (descending)A small explanation of this request: data on BTC and other cryptocurrencies live in different tables. Therefore, we have to combine these two queries with UNION. Earlier, we also decided that we want to receive quotes in fiat currencies (for example, in dollars), and not in BTC. Therefore, the second half of the query combines the data with a BTC table for converting BTC to USD.
currency_code | total_volume_in_usd
Leaders in terms of operations were, oddly enough, Bitcoin and Ethereum. But the following members of the charts - Litecoin (LTC)
, Ripple (XRP)
, and Ethereum Classic (ETC)
, are almost equal. Litecoin has been on the market for five years, is almost identical to Bitcoin and is often seen as a key player in the market. Ripple, which is positioned as a bank coin for representatives of the international commercial market and works for a more specific audience, is also considered a promising and gaining momentum. It is also interesting that in the top five there is not only ETH, but also ETC, which makes it possible to say that the market today is strongly focused on Ethereum.
The most profitable cryptocurrencies
Another way to “go through” a long list of cryptocurrencies is to analyze their profitability, for example, the indicator of total daily profit. In our set there are price data for more than 1200 cryptocurrencies. If you look at the largest rate increase per day, you can identify the leaders of intraday trading.
The highest intraday cryptocurrency cost difference (in descending order)
time | last | daily_return
Let's define a cryptocurrency with the largest daily profit margin. To calculate the daily profit, we will use the window function
again, and to search for the cryptocurrency that brought the most income on each single day we use the last
function from the TimescaleDB set.
A conclusion for the last three months shows the quantitative superiority of AMIS (a 168-fold increase in value on June 15). This cryptocurrency showed the biggest increase in 15 different days. However, after looking more closely at it, we note that high growth is due to equally high price fluctuations: the cost of AMIS is often rolled back to the zero level after each increase.AMIS closing day price in the last five months
Another leader of this sample, YOVI, showed the best result 3 times, but is also subject to similar unreliable trends as AMIS:The closing price of the day for YOVI in the last five months
Despite the instability of this pair of trends, they nevertheless look more promising compared to ETH, the cost of which steadily dropped in the first year of its existence (2015):Day closing price for ETH in 2015(Repeated disclaimer: TimescaleDB does not support any of these cryptocurrencies and is not responsible for your investments in them and any possible losses associated with them.)
So, it turns out that money is growing ... on Merkle trees ?
In this material, we made several conclusions from an open data set on cryptocurrencies, demonstrating the strengths of PostgreSQL and TimescaleDB. Nevertheless, it should be remembered that the cryptocurrency market will inevitably change next month, next week or even tomorrow.
However, if you want to independently study this set and carry out your analysis, the appropriate instruction
for downloading data and installing TimescaleDB is available at your service.
If you want to learn more about TimescaleDB and how it improves PostgreSQL's efficiency in working with time series, we recommend that you familiarize yourself with the technical post