Analyzing Ethereum, Bitcoin, and 1200 other Cryptocurrencies using PostgreSQL

Analyzing Ethereum, Bitcoin, and 1200 other Cryptocurrencies using PostgreSQL

Cryptocurrencies are fueling a modern day gold rush. Can gegevens help us better understand this evolving market?

Update: Thank you everyone for making this #1 on Hacker News!

Lately it seems like money has bot growing on trees.

Wij live ter the age of digital currencies, with cryptocurrencies birthed within the decade. Yet already, there are more than a thousand cryptocurrencies te the market and an initial coin suggesting (ICO) almost daily.

Spil wij embrace this fresh, proliferous market, it’s significant that wij attempt to understand what’s going on. There are many risks to observe at both the micro-level (e.g., private investments) and macro-level (e.g., prevention of market crashes and major loss of capital). That’s where wij come ter.

We’re gegevens people. Specifically, we’re the developers of TimescaleDB, a fresh open source time-series database built up from PostgreSQL. And wij thought it would be insightful (and joy) to analyze the cryptocurrency market using PostgreSQL and TimescaleDB (plus R for gegevens visualization).

For this analysis*, wij looked at historical OHLCV price gegevens on overheen 1200 cryptocurrencies (spil of 6/26/2018, courtesy of CryptoCompare). While our current dataset represents only a daily record of rates, TimescaleDB scales lightly to much finer-grained historical gegevens. With the onveranderlijk influx of fresh coins and exchanges, TimescaleDB can provide a reliable foundation for time-series gegevens ter the cryptocurrency market.

Here’s what you should take away from this postbode:

  • Several high-level insights into the cryptocurrency market
  • A better understanding of how TimescaleDB + PostgreSQL make time-series gegevens analysis lighter
  • Instructions on how to stream this dataset yourself and draw your own insights (and perhaps find your own arbitrage opportunities!)

So if you had invested $100 ter Bitcoin 7 years ago, it would be worth…

Let’s commence with some good old-fashioned FOMO. If you know anything about cryptocurrencies, you’ve most likely heard of Bitcoin, the “granddaddy” of all cryptocurrencies. Turns out that if you had invested $100 te Bitcoin te July 2010, it would be worth overheen $Five,000,000 today.

Bitcoin has had a pretty nice run since then (albeit taking a puny dip recently):

Using PostgreSQL, we’ve queried BTC’s prices at 2-week intervals, analyzing the rates for USD exchanges. (Note that “time_bucket” and “last” ter this query are special TimescaleDB time-series gegevens analysis functions not te PostgreSQL.)

But hopefully you didn’t buy te February 2014…

It hasn’t exactly bot a slick rail for BTC. Let’s hone te on the day-by-day volatility of BTC. Here wij calculate daily comebacks using the power of PostgreSQL window functions:

Spil a relatively fresh market, bitcoin prices are notably subject to volatile fluctuations. While a constant increase ter price marks the success of BTC, the highest spike occurred te early 2014. If wij zoom ter on 2014, wij notice that the leap occurred specifically inbetween February and March of 2014. For those who invested at the peak of this market, the price soon stabilized, forcing investors who bought then to hold for a long time to see comebacks.

Goodbye China, hello Japan

The cryptocurrency market is global. When looking at trade volumes by currency, wij noticed something interesting:

The year 2014 eyed a minor leap for Bitcoin rates ter China, presumably caused by the devaluation of the yuan and weakening domestic stock market. This wasgoed followed by a subsequent boom te 2016 and early 2018, spil Chinese volume predominated Bitcoin trades.

Within a few months, the volumes dropped dramatically.

Why? This is where wij had to step out of the numbers and do some old-fashioned research. (And what wij found shows how you can’t just rely on quantitative gegevens when attempting to understand this market.)

Ter early 2018 the People’s Handelsbank of China began reinforcing regulations and legal liabilities for risky cryptocurrency exchanges. By February, two of the largest Chinese exchanges (OKCoin and Huobi.com) had suspended withdrawals and by mid-2018, Chinese transactions had dried up. From there, Japan became the leader te bitcoin transactions by volume, even going so far spil to recognizing bitcoin spil legal currency te April 2018.

Now, if you had invested $100 te ETH ter January 2018….

Don’t worry if you didn’t hop onto the Bitcoin train te 2010. Spil volatile spil Bitcoin has bot, some would argue that Ethereum has bot a crazier rail (and the latest “correction” shows it). Let’s look at the Ethereum price overheen time ter Bitcoin (spil it’s normally quoted):

But spil wij know, Bitcoin itself has bot fairly volatile, which renders the above chart less useful. So let’s look at ETH prices te fiat currencies, using each day’s BTC to fiat exchange rates. (Taking advantage of Postgres JOINs and some fancy filters):

Te its very first year, ETH surpassed any yearly BTC growth rate te all of BTC history — a hefty 530% surge te average closing price from the previous year marked a good begin. Cumulatively, the growth has since fallen to 200% going from 2016 to 2018, however still an impressively high rate for any other asset. And within the last half year, ETH prices have enlargened by 3000%. So, if you had invested $100 ter ETH te January 2018 (less than 7 months ago), it would be worth overheen $Trio,000 today.

Projecting the price of ETH te thesis stable currencies (USD, EUR, CNY), it emerges that the trend lines remain consistent inbetween the three fiat monies. A clear progression is apparent ter the steep uprise within the last six months and trends reflect a massive growth for the coin when quoted te all currencies, except BTC. Relative to the fiat charts, the ETH/BTC chart is fairly unstable due to the fluctuating price of BTC overheen the years. Spil a result, the representation of ETH by BTC price inflates the variability of ETH. Clearly BTC is still too immature to be considered a base currency.

What about the 1200 other cryptocurrencies??

With that geschreven examination of BTC and ETH trends, hopefully you have more setting into the hectic world of cryptocurrencies. So what do wij do with the other 1200 cryptocurrencies?

Well very first, let’s use our dataset to trace the lineage of thesis cryptocurrencies:

(Disclaimer: our dataset represents when wij very first have recorded gegevens, which may not necessarily correspond to the ICO.)

It’s an evolving market. And one with no clear ceiling, spil wij can see when wij query the number of fresh cryptocurrencies by day. Above are just the most latest 20 records, showcasing how many fresh currencies amass each week.

Here’s a look at that same day, but counting the number of fresh currencies with gegevens each day:

When wij query each of the currencies by their very first set of gegevens (to track its “age”), it’s clear that the market is not simply for investors, but also for creators of thesis digital assets. Most recently, a flood of fresh coins entered our dataset during May 25–28, amounting to overheen 300 fresh cryptocurrency records ter less than a week. (Of course, thesis dates reflect when our gegevens source very first had price gegevens for the currencies, which may not correspond to the ICO.)

Who’s at the head of the cryptocurrency long tail?

There are so many cryptocurrencies that it becomes hard to separate the good ones from the noise. How do wij identify which ones worth focusing on? Here’s one metric: total trade volume overheen the past week.

Quick note on what this query is doing: The BTC and crypto-currency gegevens lives ter separate tables. So wij have to UNION the two queries. Also, spil wij established earlier, wij want to quote volumes ter a fiat currency (e.g., USD) and not BTC. So the 2nd half of the query joins with the BTC table to convert BTC to USD.

Top cryptocurrencies (measuring by transaction volume) are (not remarkably) Bitcoin and Ethereum. But after that, seems like Litecoin (LTC), Ripple (XRP), and Ethereum Classic (ETC) are not far off. Spil a five-year old coin, Litecoin is almost identical to Bitcoin and is often considered a key player ter the market. Meantime, Ripple targets a more specific audience spil a banking coin ter the global commerce strijdperk, also displaying promise spil a progressively vooraanstaande coin. Interestingly te the top Five for our query, both ETH and ETC make appearances, suggesting a major shift towards Ethereum te the market.

What are the most profitable cryptocurrencies?

Another way to sift through the long tail of cryptocurrencies is by profitability (e.g., spil measured by total daily comeback). Our gegevens contains a set of prices for overheen 1200 cryptocurrencies. If wij hone ter on the highest increase te rate by day, wij can see which cryptocurrencies lead the daily market.

Here wij identify the cryptocurrency with the highest total daily comeback, by day, going rearwards te time. (To do that, wij again use a window function to calculate daily comebacks, and then use the TimescaleDB last function to find the cryptocurrency with the highest terugwedstrijd for that day.)

Our output for the last three months shows a numeric lead by AMIS (168x on 6/15), which emerges spil the cryptocurrency with the top increase for 15 distinct days. However, if wij look more closely at AMIS’ rates, wij realize that this high increase is also due to high fluctuation rates: AMIS tends to druppel back to a closing price of zero after each increase.

Likewise, the cryptocurrency YOVI shows up Trio times ter our list of daily leads but has a similarly unreliable trend like AMIS:

While both trends are rather unstable, they showcase more promise than ETH’s down-sloping very first year (2015):

(Repeat Disclaimer: TimescaleDB does not endorse any of thesis cryptocurrencies and is not liable for investments that you make / losses you may incur.)

So money grows on… Merkle Trees?

Here wij drew some conclusions from public cryptocurrency datasets, highlighting the power of both PostgreSQL and TimescaleDB. Yet wij should reminisce that the cryptocurrency market will inevitably be different next month, week, even day.

If you’d like to learn more about TimescaleDB, and how it makes PostgreSQL scalable for time-series gegevens, we’d recommend this technical postbode.

Thanks for reading our postbode! If you found it helpful, be sure to recommend or share.

If you have any go after up questions or comments, wij welcome them via email or Twitter.

And if you’d like to learn more about TimescaleDB, please check out our GitHub (starlets appreciated), and let us know how wij can help.

Related movie: Mine Bitcoin / Cryptocurrency On iPhone


Leave a Reply

Your email address will not be published. Required fields are marked *