En analysant Ethereum, Bitcoin et plus de 1200 autres crypto-monnaies à l'aide de PostgreSQL

Les crypto-monnaies sont le moteur de la nouvelle ruée vers l'or. L'auteur suggère d'utiliser l'analyse des données pour mieux comprendre ce marché émergent.

Récemment, on a le sentiment que l'argent pousse sur les arbres.



Les volumes des échanges commerciaux atteignent des millions de dollars et la capitalisation boursière - des milliards . Il est juste de parler de la ruée vers l'or, alimentée par l'avènement d'un nombre croissant de nouvelles crypto-monnaies.

Nous vivons à une époque de monnaies numériques. Apparu il y a moins de 10 ans, le concept de crypto-monnaies s'est déjà généralisé aujourd'hui. Malgré un temps aussi court, il existe déjà plus d'un millier de crypto-monnaies différentes sur le marché, et les ICO se produisent presque tous les jours .

À mesure que nous nous habituons au nouveau marché à croissance rapide, il est important d'essayer de comprendre ce qui lui arrive. Il existe de nombreux risques, tant au niveau micro (par exemple, l'investissement personnel) qu'au niveau macro (par exemple, la prévention de l'effondrement du marché ou de grandes pertes de capital). C'est là que nous entrons dans le jeu.

Nous traitons des données, ou plutôt, je représente l'équipe de développement de TimescaleDB , une nouvelle base de données Open Source PostgreSQL pour travailler avec des séries chronologiques. Nous avons pensé qu'il serait utile et passionnant d'analyser le marché des crypto-monnaies en utilisant PostgreSQL et TimescaleDB (et R pour visualiser les données reçues).

Au cours de notre travail, nous avons analysé les données historiques sur les cartes OHLC pour plus de 1200 crypto-monnaies, gracieusement fournies par CryptoCompare (la dernière date d'échantillonnage est le 26 juin de cette année). L'étape dans cet ensemble de données particulier est de 24 heures, cependant, il convient de noter que TimescaleDB est facilement évolutif pour fonctionner avec des périodes de temps beaucoup plus courtes. Notre produit, en particulier, convient parfaitement au rôle d'un outil de base pour travailler avec le flux croissant de données sur les nouvelles pièces et les échanges.

Voici ce que vous pouvez retirer de ce post:

• Quelques conclusions générales précieuses sur la situation sur le marché des crypto-monnaies.
• Une meilleure compréhension de la façon dont TimescaleDB + PostgreSQL peut simplifier l'analyse des données de séries chronologiques.
• Obtenez des instructions pour télécharger vous-même cet ensemble de données, découvrez de nouvelles tendances (et peut-être même utilisez-les pour créer votre propre stratégie d'entrée sur le marché!).

Avertissement: l'analyse ci-dessus ne doit pas être considérée comme une recommandation pour la conduite de certaines transactions financières. Si vous souhaitez effectuer votre propre analyse, faites attention aux instructions qui décrivent comment installer TimescaleDB et télécharger les données CryptoCompare .

Donc, si vous avez investi 100 $ dans Bitcoin il y a 7 ans, cela coûterait maintenant ...

Commençons par le bon vieux syndrome du profit perdu . Si vous êtes même un peu familier avec les crypto-monnaies, vous avez probablement entendu parler de Bitcoin, "l'arrière-grand-père" de toutes les crypto-monnaies. Il s'avère que si en juillet 2010 vous y aviez investi 100 $, ce montant aurait atteint aujourd'hui 5 millions de dollars.

Pendant ce temps, Bitcoin a montré une dynamique plutôt agréable (même en tenant compte de l'effondrement relativement petit qui s'est produit relativement récemment):

-- BTC USD prices by two week intervals SELECT time_bucket('14 days', time) as period, last(closing_price, time) AS last_closing_price FROM btc_prices WHERE currency_code = 'USD' GROUP BY currency_code, period ORDER BY period; 

BTC en dollars américains à la clôture de l'échange au cours des 7 dernières années

En utilisant PostgreSQL, nous avons demandé des lectures de prix BTC à des intervalles de deux semaines, analysant le taux de change par rapport au dollar américain sur les échanges. Remarque: time_bucket et last in this query sont les fonctions natives de TimescaleDB (absentes dans PostgreSQL) utilisées pour l'analyse des séries chronologiques.

Nous espérons que vous n'avez pas acheté de bitcoins en février 2014 ...

On ne peut cependant pas dire que tout s'est toujours bien passé avec BTC. Examinons de plus près la volatilité quotidienne du cours et effectuons des calculs à l'aide des puissantes fonctions de la fenêtre PostgreSQL :

 -- Daily BTC returns by day SELECT time, closing_price / lead(closing_price) over prices AS daily_factor FROM ( SELECT time, closing_price FROM btc_prices WHERE currency_code = 'USD' GROUP BY 1,2 ) sub window prices AS (ORDER BY time DESC); 

Le ratio de la division du taux BTC / USD de la journée en cours au taux de la période précédente (période de 7 ans)

En raison de l'immaturité relative du marché, le taux de change du Bitcoin est soumis à des changements importants. Une augmentation de valeur stable indique généralement le succès de la crypto-monnaie, mais il y a une période de forte croissance qui est clairement hors de propos dans son histoire, qui a été observée début 2014. Après avoir examiné cette période plus en détail, nous remarquons de grands sauts en février-mars 2014. Les investisseurs qui ont investi dans la BTC au sommet du marché ont dû attendre à peu près, car le taux s'est rapidement stabilisé et a rapidement atteint des indicateurs où les ventes de bitcoins achetées à cette époque sont devenues une entreprise rentable.



Le coefficient de division du taux BTC / USD du jour en cours sur le taux précédent (2014)

Au revoir la Chine, bonjour le Japon

Le marché des crypto-monnaies est un phénomène international. En étudiant les volumes de négociation en termes de devises, nous avons remarqué quelque chose d'intéressant:

 -- BTC trading volumes by currency SELECT time_bucket('14 days', time) as period, currency_code, sum(volume_btc) FROM btc_prices GROUP BY currency_code, period ORDER BY period; 

Volumes de négociation de la BTC dans diverses monnaies fiduciaires au cours des 7 dernières années (intervalles de deux semaines, barres composites)

En 2014, la valeur du Bitcoin a légèrement augmenté en Chine, causée par la dévaluation présumée du renminbi et l'affaiblissement du marché boursier national du pays. Cela a été suivi d'un boom en 2016 et au début de 2017: la monnaie chinoise a dominé le commerce de bitcoins.



Part du RMB dans le trading de bitcoins au cours de la dernière année (intervalles de deux semaines)

En quelques mois seulement, ce chiffre a chuté.

Quelle en est la raison? Voici venu le temps d'aller au-delà de l'analyse des données numériques et de faire la bonne vieille recherche. Nos conclusions illustrent bien le fait qu'il est impossible de s'appuyer uniquement sur des données quantitatives pour des études de marché.

Début 2017, la Banque populaire de Chine a promulgué une réglementation restreignant les activités des échanges de crypto-monnaies. En février, les deux plus grandes bourses du pays (OKCoin et Huobi.com) ont suspendu le retrait de la crypto-monnaie en échange de devises étrangères et, au milieu de cette année, le flux des transactions chinoises avait diminué. Dans le même temps, le Japon est devenu le leader en termes de volume d'opérations avec des bitcoins. Il est même arrivé au point que Bitcoin a été reconnu comme monnaie légale en avril 2017.



Le volume de BTC dans différentes devises après une forte baisse de la part des transactions BTC / CNY en 2017. BTC / JPY - le nouveau leader en termes de volume (intervalles de deux semaines)

Et maintenant, réfléchissons à ce qui se passerait si vous investissiez 100 $ en ETH en janvier 2017 ...

Malgré le fait que le «train bitcoin soit parti» en 2010, il n'y a pas lieu de s'inquiéter. De nombreux observateurs s'accordent à dire que même au milieu de sa grande volatilité, les fluctuations des prix d'Ethereum semblent encore plus impressionnantes (et la récente «correction» n'est qu'une autre confirmation). Regardons les prix d'Ethereum en équivalent bitcoin (comme il est généralement habituel de citer):

 -- ETH prices in BTC by two week intervals SELECT time_bucket('14 days', c.time) as period, last(c.closing_price, c.time) AS last_closing_price_in_btc FROM crypto_prices c WHERE c.currency_code = 'ETH' GROUP BY period ORDER BY period; 

ETH à BTC à la clôture du jour de bourse pour les 3 dernières années

Cependant, comme nous le savons, Bitcoin lui-même ne diffère pas d'une stabilité notable, ce qui réduit l'utilité du graphique ci-dessus. Examinons les prix des ETH en monnaies fiduciaires en utilisant les cotations quotidiennes BTC pour les monnaies fiduciaires. (Pour cela, nous utiliserons les propriétés utiles des JOIN Postgres et plusieurs filtres sophistiqués):

 -- ETH prices in BTC, USD, EUR, and CNY by two week intervals SELECT time_bucket('14 days', c.time) as period, last(c.closing_price, c.time) AS last_closing_price_in_btc, last(c.closing_price, c.time) * last(b.closing_price, c.time) filter (WHERE b.currency_code = 'USD') AS last_closing_price_in_usd, last(c.closing_price, c.time) * last(b.closing_price, c.time) filter (WHERE b.currency_code = 'EUR') AS last_closing_price_in_eur, last(c.closing_price, c.time) * last(b.closing_price, c.time) filter (WHERE b.currency_code = 'CNY') AS last_closing_price_in_cny FROM crypto_prices c JOIN btc_prices b ON time_bucket('1 day', c.time) = time_bucket('1 day', b.time) WHERE c.currency_code = 'ETH' GROUP BY period ORDER BY period; 

ETH à trois monnaies fiduciaires à la clôture du jour de bourse pour les 3 dernières années

Au cours de la première année de son existence, l'ETH a dépassé l'un des indicateurs de croissance annuels de la CTB dans l'histoire de ce dernier. Une augmentation impressionnante de 530% du cours de clôture moyen par rapport à l'année précédente est un bon début. En général, en 2017, le taux de croissance agrégé pour toutes ces années a diminué en 2017 par rapport à 2016 pour atteindre 200%. Cependant, même un tel résultat semble toujours impressionnant pour tout autre actif. Comme pour les six derniers mois, les prix des ETH ont maintenant augmenté de 3000%. Par conséquent, si vous avez investi 100 $ dans des ETH en janvier de cette année (il y a près de 7 mois), leur valeur aurait aujourd'hui été de 3 000 $.

Exprimant la valeur de l'ETH en devises stables (USD, EUR, CNY), nous voyons que les trois graphiques ont la même forme. Au cours des six derniers mois, une croissance rapide a été clairement observée dans tous les équivalents monétaires, à l'exception de la BTC. Le graphique des taux ETH / BTC, similaire aux graphiques des devises fiduciaires, est beaucoup plus sujet aux fluctuations de la valeur du BTC. En conséquence, les tentatives d'exprimer le prix de l'ETH en BTC créent une impression invraisemblable de l'instabilité du premier. De toute évidence, la BTC est encore une monnaie trop jeune pour la considérer comme une base.

Qu'en est-il des 1200 autres crypto-monnaies?

Nous espérons que ce bref aperçu des tendances BTC et ETH vous a permis de mieux comprendre le monde chaotique des crypto-monnaies. Alors, que ferons-nous avec les 1200 autres crypto-monnaies?

Eh bien, tout d'abord, utilisons notre jeu de données pour suivre son origine:

Remarque importante: notre ensemble de données contient des informations au moment de leur collecte, qui auraient pu se produire après l'ICO.

 -- Currencies sorted by first time we have data for them SELECT ci.currency_code, min(c.time) FROM currency_info ci JOIN crypto_prices c ON ci.currency_code = c.currency_code AND c.closing_price > 0 GROUP BY ci.currency_code ORDER BY min(c.time) DESC; 

  currency_code | min ---------------+------------------------ CIR | 2017-06-26 20:00:00+00 MDC | 2017-06-26 20:00:00+00 WBTC | 2017-06-26 20:00:00+00 NET | 2017-06-26 20:00:00+00 NAS2 | 2017-06-26 20:00:00+00 TPAY | 2017-06-26 20:00:00+00 MRSA | 2017-06-26 20:00:00+00 XCI | 2017-06-26 20:00:00+00 PAY | 2017-06-19 20:00:00+00 SNM | 2017-06-18 20:00:00+00 LGD | 2017-06-17 20:00:00+00 SNT | 2017-06-16 20:00:00+00 IOT | 2017-06-12 20:00:00+00 QRL | 2017-06-09 20:00:00+00 MGO | 2017-06-09 20:00:00+00 CFI | 2017-06-08 20:00:00+00 VERI | 2017-06-07 20:00:00+00 EQT | 2017-06-07 20:00:00+00 ZEN | 2017-06-05 20:00:00+00 BAT | 2017-05-31 20:00:00+00 

Horodatage du premier enregistrement pour chaque crypto-monnaie (décroissant)

Le marché est en constante évolution et, à en juger par la croissance quotidienne de nouvelles crypto-monnaies, il est également en constante expansion. La liste publiée ci-dessus ne contient que les 20 jetons les plus récents au 26 juin et vous permet d'avoir une idée du nombre de nouvelles crypto-monnaies qui apparaissent chaque semaine.

Calculons le nombre de nouvelles crypto-monnaies apparaissant chaque jour en fonction de la première date d'apparition des informations de prix sur elles:

 -- Number of new currencies with data sorted by day SELECT day, COUNT(code) FROM ( SELECT min(c.time) AS day, ci.currency_code AS code FROM currency_info ci JOIN crypto_prices c ON ci.currency_code = c.currency_code AND c.closing_price > 0 GROUP BY ci.currency_code ORDER BY min(c.time) )a GROUP BY day ORDER BY day DESC; 

Le nombre de nouvelles crypto-monnaies par jour au cours des 4 dernières années



Le nombre de nouvelles crypto-monnaies par jour pour l'année en cours

  day | count ------------------------+------- 2017-06-26 20:00:00+00 | 8 2017-06-19 20:00:00+00 | 1 2017-06-18 20:00:00+00 | 1 2017-06-17 20:00:00+00 | 1 2017-06-16 20:00:00+00 | 1 2017-06-12 20:00:00+00 | 1 2017-06-09 20:00:00+00 | 2 2017-06-08 20:00:00+00 | 1 2017-06-07 20:00:00+00 | 2 2017-06-05 20:00:00+00 | 1 2017-05-31 20:00:00+00 | 5 2017-05-28 20:00:00+00 | 29 2017-05-27 20:00:00+00 | 13 2017-05-26 20:00:00+00 | 32 2017-05-25 20:00:00+00 | 303 2017-05-16 20:00:00+00 | 1 2017-05-15 20:00:00+00 | 7 

Le nombre de nouvelles crypto-monnaies par dates, statistiques quotidiennes (décroissantes)

Lorsque nous demandons des informations sur la première apparition des données de crypto-monnaie (pour suivre leur "âge"), il devient évident que le marché n'est pas seulement un groupe d'investisseurs, il existe une autre catégorie de ses participants - les créateurs d'actifs numériques. Tout récemment, du 25 au 28 mai, selon nos données de recrutement, il y a eu un afflux important de nouvelles pièces - plus de 300 nouveaux jetons en moins d'une semaine. (Étant donné que notre ensemble enregistre uniquement les informations sur les prix des crypto-monnaies, les informations sur leur apparence peuvent ne pas correspondre aux dates de l'ICO.)

Leaders de la crypto-monnaie et mondes de capture

Il y a tellement de cryptomonnaies aujourd'hui qu'il devient difficile de distinguer le sensible du douteux. Comment pouvez-vous déterminer lesquels méritent votre attention? Voici l'une des mesures: le volume total des transactions d'échange au cours de la semaine dernière.

 -- 1200+ crypto currencies by total transaction volume (in btc) over the last month SELECT 'BTC' as currency_code, sum(b.volume_currency) as total_volume_in_usd FROM btc_prices b WHERE b.currency_code = 'USD' AND now() - date(b.time) < INTERVAL '8 day' GROUP BY b.currency_code UNION SELECT c.currency_code as currency_code, sum(c.volume_btc) * avg(b.closing_price) as total_volume_in_usd FROM crypto_prices c JOIN btc_prices b ON date(c.time) = date(b.time) WHERE c.volume_btc > 0 AND b.currency_code = 'USD' AND now() - date(b.time) < INTERVAL '8 day' AND now() - date(c.time) < INTERVAL '8 day' GROUP BY c.currency_code ORDER BY total_volume_in_usd DESC; 

  currency_code | total_volume_in_usd ---------------+--------------------- BTC | 2040879023.54 ETH | 1617388472.94011 LTC | 287613541.293571 XRP | 269417667.514443 ETC | 165712729.612886 ANS | 126377042.5269 SC | 111623857.796786 DASH | 86875922.3588143 ZEC | 78836728.2129428 BTS | 69459051.5958428 

Volume total de transactions de 10 principales crypto-monnaies en dollars américains au cours de la semaine dernière (décroissant)



Volume total de transactions de 10 principales crypto-monnaies en dollars américains au cours de la semaine dernière (décroissant)

Une petite précision à cette demande: les données sur BTC et autres crypto-monnaies vivent dans des tableaux différents. Par conséquent, nous devons combiner ces deux requêtes avec UNION. Plus tôt, nous avons également décidé que nous voulions recevoir des devis en monnaies fiduciaires (par exemple, en dollars), et non en BTC. Par conséquent, la seconde moitié de la demande combine les données avec le tableau BTC pour convertir le BTC en USD.

Curieusement, Bitcoin et Ethereum se sont avérés être des leaders en termes de volume d'opérations. Mais les participants suivants au hit parade - Litecoin (LTC) , Ripple (XRP) et Ethereum Classic (ETC) , sont presque égaux. Présent sur le marché depuis cinq ans, le Litecoin est presque identique au Bitcoin et est souvent considéré comme un acteur clé du marché. Ripple, qui se positionne comme une pièce de monnaie bancaire pour les représentants du marché commercial international et travaille pour un public plus spécifique, est également considérée comme une pièce prometteuse et en croissance. Il est également intéressant de noter que les cinq premiers ont non seulement l'ETH, mais aussi l'ETC, ce qui suggère que le marché est aujourd'hui fortement axé sur l'Ethereum.

Les crypto-monnaies les plus rentables

Une autre façon de «gratter» une longue liste de crypto-monnaies est d'analyser leur rentabilité, par exemple, un indicateur du profit quotidien total. Notre ensemble contient des données de prix pour plus de 1200 crypto-monnaies. Si vous regardez de près la plus forte augmentation du taux par jour, vous pouvez identifier les leaders du trading intraday.

 -- Top crypto by daily return, by day SELECT time, last(currency_code,daily_factor), max(daily_factor) FROM ( SELECT currency_code, time, closing_price, lead(closing_price) over (partition BY currency_code ORDER BY time DESC) AS prev_day_closing_price, closing_price / lead(closing_price) over (partition BY currency_code ORDER BY time DESC) AS daily_factor FROM crypto_prices) q GROUP BY time ORDER BY time DESC; 

  time | last | daily_return ------------------------+--------+------------------ 2017-06-26 20:00:00+00 | CIN | 40.1428571428571 2017-06-25 20:00:00+00 | KC | 38 2017-06-24 20:00:00+00 | VOYA | 14.2747252747253 2017-06-23 20:00:00+00 | PAY | 3.18506315211422 2017-06-22 20:00:00+00 | YOVI | 119.607843137255 2017-06-21 20:00:00+00 | ION | 7.97665369649805 2017-06-20 20:00:00+00 | TES | 5.25157232704403 2017-06-19 20:00:00+00 | KNC | 150000 2017-06-18 20:00:00+00 | ZNY | 22.5217391304348 2017-06-17 20:00:00+00 | YOVI | 22.0590746115759 2017-06-16 20:00:00+00 | LTD | 9.50207468879668 2017-06-15 20:00:00+00 | AMIS | 168758.782201405 2017-06-14 20:00:00+00 | JANE | 6 2017-06-13 20:00:00+00 | YOVI | 690.636254501801 2017-06-12 20:00:00+00 | U | 5.21452145214522 2017-06-11 20:00:00+00 | JANE | 6 2017-06-10 20:00:00+00 | WGO | 3.58744394618834 2017-06-09 20:00:00+00 | BNT | 5000 2017-06-08 20:00:00+00 | XNC | 52.7704485488127 2017-06-07 20:00:00+00 | CBD | 14.3243243243243 2017-06-06 20:00:00+00 | CC | 72 2017-06-05 20:00:00+00 | BLAZR | 7.38461538461538 2017-06-04 20:00:00+00 | GREXIT | 13.0833333333333 2017-06-03 20:00:00+00 | EPY | 4.29880478087649 2017-06-02 20:00:00+00 | YOVI | 1257.67790262172 2017-06-01 20:00:00+00 | FCN | 8.57142857142857 2017-05-31 20:00:00+00 | EPY | 348.611111111111 2017-05-30 20:00:00+00 | BST | 14.1441860465116 2017-05-29 20:00:00+00 | FCN | 45.3086419753086 2017-05-28 20:00:00+00 | NOO | 56536.231884058 

Les taux les plus élevés de différence de valeur de crypto-monnaie intraday (date décroissante)

Définissons la crypto-monnaie avec le plus grand indicateur de rentabilité quotidienne. Pour calculer le profit quotidien, nous utilisons à nouveau la fonction de fenêtre et pour rechercher la crypto-monnaie qui a généré le plus de revenus chaque jour, nous utilisons la dernière fonction de l'ensemble TimescaleDB.

La conclusion des trois derniers mois montre la supériorité quantitative de l'AMIS (augmentation de la valeur de 168 fois le 15 juin). Cette crypto-monnaie a enregistré la plus forte augmentation en 15 jours différents. Cependant, en y regardant de plus près, nous constatons que la forte croissance est due à des fluctuations de prix tout aussi élevées: le prix AMIS revient souvent à zéro après chaque augmentation.



Cours de clôture de l'AMIS pour les cinq derniers mois

Un autre leader de cet échantillon, YOVI, a montré le meilleur résultat 3 fois, mais est également soumis à des tendances peu fiables similaires à celles de l'AMIS:



Cours de clôture du jour pour YOVI au cours des cinq derniers mois

Malgré l'instabilité de cette paire de tendances, elles semblent néanmoins plus prometteuses par rapport à l'ETH, dont la valeur n'a cessé de baisser au cours de la première année de son existence (2015):



Cours de clôture à l'ETH en 2015

(Avertissement répété: TimescaleDB ne prend en charge aucune de ces crypto-monnaies et n'est pas responsable de votre investissement dans celles-ci et des pertes possibles qui leur sont associées.)

Il s'avère donc que l'argent se développe ... sur les arbres Merkle ?

Dans cet article, nous avons tiré plusieurs conclusions à partir d'un ensemble de données de crypto-monnaie ouvert, démontrant les points forts de PostgreSQL et TimescaleDB. Néanmoins, il convient de rappeler que le marché des crypto-monnaies changera inévitablement le mois prochain, la semaine prochaine ou même demain.

Cependant, si vous souhaitez étudier cet ensemble de manière indépendante et effectuer votre analyse, les instructions appropriées pour télécharger les données et installer TimescaleDB sont disponibles à votre service.

Si vous voulez en savoir plus sur TimescaleDB et comment il peut améliorer l'efficacité de PostgreSQL pour travailler avec des séries chronologiques, nous vous recommandons de vous familiariser avec le poste technique .