Al analizar Ethereum, Bitcoin y más de 1200 otras criptomonedas usando PostgreSQL

Las criptomonedas son la fuerza impulsora detrás de la nueva fiebre del oro. El autor sugiere utilizar el análisis de datos para comprender mejor este mercado emergente.

Recientemente, existe la sensación de que el dinero está creciendo en los árboles.



Los volúmenes de intercambio alcanzan millones de dólares y la capitalización de mercado: miles de millones . Es justo hablar sobre la fiebre del oro, impulsada por el advenimiento de un número creciente de nuevas criptomonedas.

Vivimos en una era de monedas digitales. Habiendo aparecido hace menos de 10 años, el concepto de criptomonedas ya se ha generalizado hoy. A pesar de tan poco tiempo, ya hay más de mil criptomonedas diferentes en el mercado, y las ICO se producen casi todos los días .

A medida que nos acostumbramos al nuevo mercado de rápido crecimiento, es importante tratar de entender lo que le está sucediendo. Existen muchos riesgos, tanto a nivel micro (por ejemplo, inversión personal) como a nivel macro (por ejemplo, la prevención del colapso del mercado o grandes pérdidas de capital). Aquí es donde entramos en el juego.

Nos ocupamos de los datos, o más bien, represento al equipo de desarrollo de TimescaleDB , una nueva base de datos PostgreSQL de código abierto para trabajar con series de tiempo. Pensamos que sería útil y emocionante analizar el mercado de criptomonedas usando PostgreSQL y TimescaleDB (y R para visualizar los datos recibidos).

En el curso del trabajo, analizamos los datos históricos en los gráficos OHLC para más de 1200 criptomonedas, amablemente proporcionadas por CryptoCompare (la última fecha de muestreo es el 26 de junio de este año). El paso en este conjunto de datos en particular es de 24 horas, sin embargo, debe tenerse en cuenta que TimescaleDB es fácilmente escalable para trabajar con períodos de tiempo mucho más cortos. Nuestro producto, en particular, se adapta perfectamente al papel de una herramienta básica para trabajar con el creciente flujo de datos en nuevas monedas e intercambios.

Esto es lo que puedes tomar de esta publicación:

• Algunas valiosas conclusiones generales sobre la situación en el mercado de criptomonedas.
• Una mejor comprensión de cómo TimescaleDB + PostgreSQL puede simplificar el análisis de datos de series temporales.
• Obtenga instrucciones para descargar este conjunto de datos usted mismo, descubra nuevas tendencias (¡y quizás incluso las use para crear su propia estrategia para ingresar al mercado!).

Descargo de responsabilidad: el análisis anterior no debe considerarse como una recomendación para la realización de ciertas transacciones financieras. Si desea realizar su propio análisis, preste atención a las instrucciones que describen cómo instalar TimescaleDB y descargar datos de CryptoCompare .

Entonces, si invirtió $ 100 en Bitcoin hace 7 años, ahora costaría ...

Comencemos con el viejo Síndrome de Ganancias Perdidas . Si incluso está un poco familiarizado con las criptomonedas, probablemente haya oído hablar de Bitcoin, el "bisabuelo" de todas las criptomonedas. Resulta que si en julio de 2010 invirtió $ 100 en él, hoy esta cantidad habría aumentado a $ 5 millones.

Durante este tiempo, Bitcoin mostró una dinámica bastante agradable (incluso teniendo en cuenta el colapso relativamente pequeño que ocurrió relativamente recientemente):

-- 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 a dólares estadounidenses al cierre del intercambio durante los últimos 7 años

Usando PostgreSQL, solicitamos lecturas de precios de BTC en intervalos de dos semanas, analizando el tipo de cambio frente al dólar estadounidense en los intercambios. Nota: time_bucket y last in this query son las funciones nativas de TimescaleDB (ausentes en PostgreSQL) utilizadas para el análisis de series de tiempo.

Esperamos que no hayas comprado bitcoins en febrero de 2014 ...

Sin embargo, no se puede decir que todo fue siempre fácil con BTC. Echemos un vistazo más de cerca a la volatilidad diaria del curso y realicemos cálculos utilizando las potentes funciones de ventana de 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); 

La razón de dividir la tasa BTC / USD del día actual a la tasa del anterior (período de 7 años)

Debido a la relativa inmadurez del mercado, el tipo de cambio de Bitcoin está sujeto a cambios significativos. Un aumento estable en el valor generalmente indica el éxito de la criptomoneda, pero hay un período de fuerte crecimiento que está claramente fuera de la imagen en su historia, que se observó a principios de 2014. Después de examinar este período con más detalle, notamos grandes saltos en febrero-marzo de 2014. Los inversores que invirtieron en BTC en la cima del mercado tuvieron que esperar bastante, ya que la tasa pronto se estabilizó, y pronto alcanzaron indicadores en los que las ventas de bitcoins compradas en ese momento se convirtieron en un negocio rentable.



El coeficiente de dividir la tasa BTC / USD del día actual en la tasa anterior (2014)

Adiós China, Hola Japón

El mercado de criptomonedas es un fenómeno internacional. Al estudiar los volúmenes de negociación en términos de monedas, notamos algo interesante:

 -- 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; 

Volúmenes comerciales de BTC en varias monedas fiduciarias en los últimos 7 años (intervalos de dos semanas, barras compuestas)

En 2014, hubo un pequeño salto en el valor de Bitcoin en China, causado por la supuesta devaluación del renminbi y el debilitamiento del mercado de valores interno del país. Esto fue seguido por un auge en 2016 y principios de 2017: la moneda china dominó el comercio de bitcoins.



Participación de RMB en el comercio de bitcoins durante el año pasado (intervalos de dos semanas)

En el transcurso de unos pocos meses, esta cifra cayó bruscamente.

Cual es la razon Aquí llega el momento de ir más allá del análisis de datos numéricos y hacer la buena investigación anterior. Nuestras conclusiones ilustran bien el hecho de que es imposible confiar solo en datos cuantitativos para la investigación de mercado.

A principios de 2017, el Banco Popular de China promulgó regulaciones que restringen las actividades de los intercambios de criptomonedas. En febrero, los dos intercambios más grandes del país (OKCoin y Huobi.com) suspendieron el retiro de la criptomoneda a cambio de moneda extranjera, y a mediados de este año el flujo de transacciones chinas había disminuido. Al mismo tiempo, Japón se convirtió en el líder en términos de volumen de operaciones con bitcoins. Incluso llegó al punto de que Bitcoin fue reconocido como moneda legal en abril de 2017.



El volumen de BTC en varias monedas después de una fuerte disminución en la participación de las transacciones de BTC / CNY en 2017. BTC / JPY: el nuevo líder en términos de volumen (intervalos de dos semanas)

Y ahora pensemos en lo que sucedería si invirtiera $ 100 en ETH en enero de 2017 ...

A pesar de que el "tren bitcoin se fue" en 2010, no hay motivo de preocupación. Muchos observadores coinciden en que incluso en medio de su gran volatilidad, los cambios en los precios de Ethereum se ven aún más impresionantes (y la reciente "corrección" es otra confirmación de esto). Echemos un vistazo a los precios de Ethereum en bitcoin equivalente (como suele ser habitual citar):

 -- 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; 

Tasa de ETH a BTC al cierre del día de negociación de los últimos 3 años

Sin embargo, como sabemos, el Bitcoin en sí no difiere notablemente de la estabilidad, lo que reduce la utilidad de la tabla anterior. Veamos los precios ETH en monedas fiduciarias utilizando las cotizaciones diarias de BTC para monedas fiduciarias. (Para esto, utilizaremos las propiedades útiles de Postgres JOIN y varios filtros sofisticados):

 -- 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 a tres monedas fiduciarias al cierre del día de negociación durante los últimos 3 años

En el primer año de su existencia, ETH superó cualquiera de los indicadores de crecimiento anual de BTC en la historia de este último. Un buen comienzo del 530% en el precio de cierre promedio durante el año anterior es un buen comienzo. En general, para 2017, la tasa de crecimiento agregado para todos estos años cayó en 2017 en comparación con 2016 a 200%. Sin embargo, incluso ese resultado todavía parece impresionante para cualquier otro activo. En cuanto a los últimos seis meses, ahora los precios de ETH han aumentado en un 3000%. Por lo tanto, si invirtió $ 100 en ETH en enero de este año (hace casi 7 meses), hoy su valor habría sido de $ 3 mil.

Al expresar el valor de ETH en monedas estables (USD, EUR, CNY), vemos que los tres gráficos tienen la misma forma. En los últimos seis meses, se ha observado claramente un rápido crecimiento en todos los equivalentes de divisas, excepto BTC. El gráfico de tasas ETH / BTC, que es similar a los gráficos de moneda fiduciaria, es mucho más propenso a las fluctuaciones en el valor de BTC. Como resultado, los intentos de expresar el precio de ETH en BTC crean una impresión inverosímil de la inestabilidad del primero. Obviamente, BTC todavía es una moneda demasiado joven para considerarla base.

¿Qué pasa con las otras 1200 criptomonedas?

Esperamos que esta breve descripción general de las tendencias BTC y ETH le haya permitido comprender mejor el mundo caótico de las criptomonedas. Entonces, ¿qué haremos con las otras 1200 criptomonedas?

Bueno, primero, usemos nuestro conjunto de datos para rastrear su origen:

Nota importante: Nuestro conjunto de datos contiene información en el momento de su recopilación, que podría haber ocurrido después de la 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 

Marca de tiempo del primer registro para cada criptomoneda (descendente)

El mercado está en constante evolución y, además, a juzgar por el crecimiento diario de las nuevas criptomonedas, también se está expandiendo constantemente. La lista publicada anteriormente contiene solo los 20 tokens más recientes al 26 de junio y le permite tener una idea de cuántas criptomonedas nuevas aparecen cada semana.

Calculemos la cantidad de nuevas criptomonedas que aparecen todos los días en función de la primera fecha de aparición de la información de precios en ellas:

 -- 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; 

El número de nuevas criptomonedas por día en los últimos 4 años.



La cantidad de nuevas criptomonedas por día para el año actual

  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 

El número de nuevas criptomonedas por fechas, estadísticas diarias (descendentes)

Cuando solicitamos información sobre la primera aparición de datos de criptomonedas (para rastrear su "edad"), se hace evidente que el mercado no es solo un grupo de inversores, hay otra categoría de sus participantes: los creadores de activos digitales. Recientemente, del 25 al 28 de mayo, según nuestros datos de reclutamiento, hubo una gran afluencia de nuevas monedas, más de 300 nuevas fichas en menos de una semana. (Dado que nuestro conjunto solo registra información de precios en criptomonedas, la información sobre su apariencia puede no corresponder a las fechas de la ICO).

Líderes de criptomonedas y mundos atrapantes

Hay tantas criptomonedas hoy en día que se hace difícil distinguir entre lo sensible y lo dudoso. ¿Cómo puede determinar cuáles merecen atención? Esta es una de las métricas: el volumen total de transacciones de cambio durante la semana pasada.

 -- 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 

Volumen total de transacciones de 10 criptomonedas principales en dólares estadounidenses durante la semana pasada (descendente)



Volumen total de transacciones de 10 criptomonedas principales en dólares estadounidenses durante la semana pasada (descendente)

Una pequeña aclaración a esta solicitud: los datos sobre BTC y otras criptomonedas viven en diferentes tablas. Por lo tanto, tenemos que combinar estas dos consultas con UNION. Anteriormente, también decidimos que queríamos recibir cotizaciones en monedas fiduciarias (por ejemplo, en dólares) y no en BTC. Por lo tanto, la segunda mitad de la solicitud combina los datos con la tabla BTC para convertir BTC a USD.

Curiosamente, Bitcoin y Ethereum resultaron ser líderes en términos de volumen de operaciones. Pero los siguientes participantes en el exitoso desfile: Litecoin (LTC) , Ripple (XRP) y Ethereum Classic (ETC) , son casi iguales. Presente en el mercado durante cinco años, Litecoin es casi idéntico a Bitcoin y a menudo se lo considera un jugador clave en el mercado. Ripple, que se posiciona como una moneda bancaria para representantes del mercado comercial internacional y trabaja para un público más específico, también se considera una moneda prometedora y en crecimiento. También es interesante que los cinco principales no solo tengan ETH, sino también ETC, lo que sugiere que el mercado actual está fuertemente centrado en Ethereum.

Las criptomonedas más rentables

Otra forma de "rascar" una larga lista de criptomonedas es analizar su rentabilidad, por ejemplo, un indicador del beneficio diario total. Nuestro conjunto contiene datos de precios de más de 1200 criptomonedas. Si observa de cerca el mayor aumento en la tasa por día, puede identificar a los líderes del comercio intradía.

 -- 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 

Las tasas más altas de diferencia de valor de la criptomoneda intradía (fecha descendente)

Definamos la criptomoneda con el mayor indicador de rentabilidad diaria. Para calcular el beneficio diario, nuevamente usamos la función de ventana , y para buscar la criptomoneda que generó la mayor cantidad de ingresos en cada día, usamos la última función del conjunto TimescaleDB.

La conclusión de los últimos tres meses muestra la superioridad cuantitativa de AMIS (un aumento de valor de 168 veces el 15 de junio). Esta criptomoneda mostró el mayor aumento en 15 días diferentes. Sin embargo, al observarlo más de cerca, observamos que el alto crecimiento se debe a fluctuaciones de precios igualmente altas: el precio de AMIS a menudo vuelve a cero después de cada aumento.



Precio de cierre de AMIS en los últimos cinco meses.

Otro líder de esta muestra, YOVI, mostró el mejor resultado 3 veces, pero también está sujeto a tendencias poco confiables similares a AMIS:



Precio de cierre del día para YOVI en los últimos cinco meses

A pesar de la inestabilidad de este par de tendencias, sin embargo, parecen más prometedoras en comparación con ETH, cuyo valor disminuyó constantemente en el primer año de su existencia (2015):



Precio de cierre del día para ETH en 2015

(Descargo de responsabilidad repetido: TimescaleDB no admite ninguna de estas criptomonedas y no es responsable de su inversión en ellas y de las posibles pérdidas asociadas con ellas).

¿Entonces resulta que el dinero está creciendo ... en los árboles Merkle ?

En este artículo, sacamos varias conclusiones de un conjunto de datos de criptomonedas abierto, demostrando las fortalezas de PostgreSQL y TimescaleDB. Sin embargo, debe recordarse que el mercado de criptomonedas cambiará inevitablemente el próximo mes, la próxima semana o incluso mañana.

Sin embargo, si desea estudiar de forma independiente este conjunto y realizar su análisis, las instrucciones apropiadas para descargar datos e instalar TimescaleDB están disponibles en su servicio.

Si quiere saber más sobre TimescaleDB y cómo puede mejorar la eficiencia de PostgreSQL para trabajar con series de tiempo, le recomendamos que se familiarice con la publicación técnica .