Анализируя Ethereum, Биткоин и более 1200 других криптовалют с помощью PostgreSQL

Автор оригинала: Sarah Pan
  • Перевод
Криптовалюты — движущая сила новой золотой лихорадки. Автор предлагает использовать анализ данных для лучшего понимания этого развивающегося рынка.

В последнее время возникает ощущение, будто деньги растут на деревьях.

image

Объемы биржевой торговли достигают миллионов долларов, а рыночная капитализация — миллиардов. Впору говорить о золотой лихорадке, подогреваемой появлением все большего количества новых криптовалют.

Мы живем в эпоху цифровых валют. Появившись менее 10 лет тому назад, концепция криптовалют уже сегодня получила широкое распространение. Несмотря на столь малый срок, на рынке уже существует более тысячи разных криптовалют, а ICO происходят чуть ли не каждый день.

По мере того как мы привыкаем к новому, быстро набирающему обороты рынку, важно попытаться понять, что с ним происходит. Существует много рисков, как на микроуровне (например, личное инвестирование), так и на макроуровне (например, предотвращение рыночных крахов или крупные потери капитала). Вот где в игру вступаем мы.

Мы занимаемся данными, а если точнее, то я представляю команду разработчиков TimescaleDB — новой базы данных на базе PostgreSQL с открытым исходным кодом для работы с временными рядами. Мы подумали, что будет полезно и увлекательно проанализировать криптовалютный рынок с помощью PostgreSQL и TimescaleDB (и R для визуализации полученных данных).

В ходе работы мы проанализировали исторические данные по OHLC-графикам для более чем 1200 криптовалют, любезно предоставленные нам CryptoCompare (последняя дата выборки — 26 июня этого года). Шаг в этом конкретном наборе данных составляет сутки, однако следует отметить, что TimescaleDB легко масштабируется для работы с гораздо меньшими временными отрезками. Наш продукт, в частности, прекрасно подходит на роль базового инструмента для работы с растущим потоком данных о новых коинах и биржах.

Вот что вы можете вынести для себя из этого поста:

  • Несколько ценных выводов общего характера о положении дел на криптовалютномм рынке.
  • Лучшее понимание того, как связка TimescaleDB + PostgreSQL может упростить анализ данных по временным рядам.
  • Получите инструкции для самостоятельной загрузки этого набора данных, обнаружите новые тренды (и может быть, даже составите с их помощью собственную стратегию по входу на рынок!).

Отказ от ответственности: приведенный анализ не следует рассматривать как рекомендацию для проведения тех или иных финансовых операций. Если вы захотите провести собственный анализ, обратите внимание на инструкцию, в которой описана установка TimescaleDB и загрузка данных CryptoCompare.

Итак, если бы 7 лет назад вы вложили в Биткоин 100$, сейчас он стоил бы...


Давайте начнем со старого доброго синдрома упущенной выгоды. Если вы хоть немного знакомы с криптовалютами, вы, вероятно, слышали о Биткоине, «прадеде» всех криптовалют. Оказывается, что если бы в июле 2010 годы вы вложили в него $100, сегодня эта сумма выросла бы до $5 млн.

За это время Биткоин показал довольно приятную динамику (даже с учетом произошедшего сравнительно недавно небольшого обвала):

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


image

Курс BTC к доллару США на момент закрытия биржи за последние 7 лет

С помощью PostgreSQL, мы запросили ценовые показания BTC с двухнедельными интервалами, проанализировав курс к доллару США на биржах. Примечание: time_bucket и last в этом запросе — собственные функции TimescaleDB (в PostgreSQL отсутствуют), применяемые для анализа временных рядов.

Надеемся, что вы не покупали биткоины в феврале 2014...


Нельзя однако сказать, что у BTC все всегда было гладко. Давайте присмотримся повнимательнее к ежедневной волатильности курса и проведем вычисления, воспользовавшись мощными оконными функциями 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);


image

Коэффициент деления курса BTC/USD текущего дня на курс предыдущего (7-летний период)

В силу относительной незрелости рынка, курс Биткоин подвержен существенным перепадам. Стабильный прирост стоимости в целом говорит об успешности криптовалюты, но есть в ее истории один явно выбивающийся из общей картины период резкого роста, наблюдавшийся в начале 2014 года. Рассмотрев этот период более подробно, заметим крупные скачки в феврале-марте 2014 года. Инвесторам, которые вложились в BTC на пике рынка пришлось изрядно подождать, поскольку курс вскоре стабилизировался, и еще нескоро достиг показателей, при которых продажа купленных в то время биткоинов стала выгодным делом.

image

Коэффициент деления курса BTC/USD текущего дня на курс предыдущего (2014 год)

Прощай Китай, здравствуй Япония


Рынок криптовалюты — явление международное. Изучая объемы торговли с точки зрения валют, мы заметили кое-что интересное:

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


image

Объемы торговли BTC в различных фиатных валютах за последние 7 лет (двухнедельные интервалы, составные столбики)

В 2014 произошел небольшой скачок стоимости Биткоин в Китае, вызванный предположительно девальвацией юаня и ослаблением внутреннего фондового рынка страны. За этим последовал бум 2016 и раннего 2017 годов: китайская валюта доминировала в биткоин-торговле.

image

Доля юаней в торговле биткоинами за последний год (двухнедельные интервалы)

В течение всего нескольких месяцев этот показатель резко снизился.

В чем же причина? Здесь наступает время выйти за рамки анализа числовых данных и заняться старым добрым исследованием. Сделанные нами выводы хорошо иллюстрируют тот факт, что полагаться только на количественные данные для изучения рынка нельзя.

В начале 2017 года Народный банк Китая привел в действие постановления, ограничивающие деятельность криптовалютных бирж. Уже в феврале две крупнейшие в стране биржи (OKCoin и Huobi.com) приостановили вывод криптовалюты в обмен на иностранную валюту, и к середине этого года поток китайских транзакций сошел на нет. В это же время Япония стала лидером по количеству объема операций с биткоинами. Дело дошло даже до того, что Биткоин был признан легальной валютой в апреле 2017 года.

image

Объем BTC в различных валютах после резкого снижения доли BTC/CNY транзакций в 2017 года. BTC/JPY — новый лидер по объему (двухнедельные интервалы)

А теперь давайте порассуждаем, что было бы, если бы вы вложили 100$ в ETH в январе 2017...


Несмотря на то, что «биткоин-поезд ушел» еще в далеком 2010, поводов для беспокойства нет. Многие наблюдатели согласны, что даже на фоне его большой волатильности, ценовые перепады Ethereum выглядят еще впечатляюще (и недавняя «коррекция» — лишнее тому подтверждение). Давайте посмотрим на цены Ethereum в биткоин эквиваленте (как его обычно принято котировать):

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


image

Курс ETH к BTC на момент закрытия торгового дня за последние 3 года

Однако как мы знаем, Биткоин и сам не отличается заметной стабильностью, что снижает полезность приведенного выше графика. Давайте посмотрим на цены ETH в фиатных валютах, с помощью ежедневных биржевых котировок BTC к фиатным валютам. (Для этого воспользуемся полезными свойствами JOINов Postgres и несколькими навороченными фильтрами):

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


image

Курс ETH к трем фиатным валютам на момент закрытия торгового дня за последние 3 года

В первый год своего существования ETH превзошел любой из годовых показателей роста BTC за все историю последнего. Внушительные 530% роста в средней цене закрытия по сравнению с предыдущим годом — хорошее начало. В целом к 2017 году совокупный показатель роста за все эти годы упал в 2017 по сравнению с 2016 до 200%. Однако даже такой результат все равно выглядит впечатляюще для любого другого актива. Что же касается последнего полугодия, то сейчас цены на ETH выросли на 3000%. Поэтому если бы вы вложили 100 долларов в ETH в январе этого года (почти 7 месяцев тому назад), сегодня их стоимость составляла бы уже $3 тыс.

Выражая стоимость ETH в стабильных валютах (USD, EUR, CNY) видим, что все три графика имеют одинаковую форму. В последние полгода четко прослеживается стремительный рост во всех валютных эквивалентах, кроме BTC. График курса ETH/BTC, будучи схожим с графиками фиатных валют, гораздо больше подвержен флуктуациям стоимости BTC. В результате попытки выражения цены ETH в BTC создают неправдоподобное впечатление неустойчивости первой. Очевидно, BTC еще слишком молодая валюта чтобы считать ее базовой.

А что насчет других 1200 криптовалют?


Надеемся, что этот краткий обзор BTC и ETH трендов позволил вам лучше понять хаотичный мир криптовалют. Итак, что же мы сделаем с другими 1200 криптовалютами?

Что ж, для начала давайте воспользуемся нашим набором данных чтобы отследить их происхождение:

Важное примечание: Наш набор данных содержит информацию на момент ее сбора, который мог произойти уже после проведения 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

Временная метка первой записи для каждой криптовалюты (по убыванию)

Рынок постоянно развивается, а кроме того, судя по ежедневному приросту новых криптовалют, к тому же постоянно расширяется. Опубликованный выше список содержит лишь 20 самых новых по состоянию на 26 июня токенов, и позволяет получить представление о том, сколько новых криптовалют появляются каждую неделю.

Давайте посчитаем количество появляющихся каждый день новых криптовалют на основе первой даты появления ценовой информации по ним:

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


image

Количество новых криптовалют в день за последние 4 года

image

Количество новых криптовалют в день за текущий год

          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

Количество новых криптовалют по датам, ежедневная статистика (по убыванию)

Когда мы запрашиваем информацию о первом появлении данных о криптовалютах (для отслеживания их «возраста»), становится заметно что рынок — это не только группа инвесторов, есть и другая категория его участников — создатели цифровых активов. Буквально недавно, 25–28 мая, по данным нашего набора, был отмечен большой приток новых коинов — свыше 300 новых токенов менее чем за неделю. (Поскольку наш набор фиксирует только ценовую информацию по криптовалютам, информация об их появлении может не соответствовать датам проведения ICO.)

Лидеры и догоняющие криптовалютного мира


Криптовалют сегодня так много, что становится сложно отличить толковые от сомнительных. Как можно определить какие из них достойны внимания? Вот вам одна из метрик: суммарный объем биржевых операций за последнюю неделю.

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

Общий объем транзакций 10 основных криптовалют в долларах США за последнюю неделю (по убыванию)

image

Общий объем транзакций 10 основных криптовалют в долларах США за последнюю неделю (по убыванию)

Небольшое пояснение к этому запросу: данные по BTC и другим криптовалютам живут в разных таблицах. Поэтому нам приходится объединить эти два запроса с помощью UNION. Ранее мы также определились, что хотим получать котировки в фиатных валютах (например, в долларах), а не в BTC. Поэтому вторая половина запроса комбинирует данные с таблицей BTC для конвертации BTC в USD.

Лидерами по объему операций оказались, как это ни странно, Биткоин и Ethereum. А вот следующие участники хит-парада — Litecoin (LTC), Ripple (XRP), и Ethereum Classic (ETC), идут почти на равных. Присутствующий на рынке уже пять лет Litecoin практически идентичен Биткоину и часто рассматривается в качестве ключевого игрока на рынке. Ripple, который позиционируется как банковский коин для представителей международного коммерческого рынка и работает на более специфичную аудиторию, также считается многообещающим и набирающим обороты коином. Интересно и то, что в пятерке лидеров есть не только ETH, но и ETC, что позволяет говорить о том, что рынок сегодня сильно ориентирован на Ethereum.

Самые прибыльные криптовалюты


Еще один способ «прошерстить» длинный список криптовалют — проанализировать их прибыльность, например, показатель суммарной ежедневной прибыли. В нашем наборе имеются ценовые данные для более чем 1200 криптовалют. Если присмотреться к самому крупному увеличению курса в день, можно выявить лидеров внутридневной торговли.

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

Самые высокие показатели внутридневной разницы стоимости криптовалюты (по убыванию даты)

Давайте определим криптовалюту с самым крупным ежедневным показателем прибыльности. Для вычислений ежедневной прибыли снова воспользуемся оконной функцией, а для поиска криптовалюты, принесшей больше всего дохода в каждый отдельно взятый день применим функцию last из набора TimescaleDB.

Вывод за последние три месяца показывает количественное превосходство AMIS (168-кратный прирост стоимости 15 июня). Эта криптовалюта показывала самое большое увеличение в 15 разных дней. Однако присмотревшись к ней повнимательнее, заметим что высокий рост обусловлен столь же высокими флуктуациями цены: стоимость AMIS часто откатывается назад к нулевому уровню после каждого увеличения.

image

Цена закрытия дня для AMIS в последние пять месяцев

Другой лидер этой выборки, YOVI, показывал лучший результат 3 раза, но также подвержен схожим ненадежным трендам, что и AMIS:

image

Цена закрытия дня для YOVI в последние пять месяцев

Несмотря на нестабильность этой пары трендов, они тем не менее выглядят более многообещающе по сравнению с ETH, стоимость которого стабильно падала в первый год своего существования (2015):

image

Цена закрытия дня для ETH в 2015

(Повторный отказ от ответственности: TimescaleDB не поддерживает какую-либо из этих криптовалют и не несет ответственности за ваши инвестиции в них и любые возможные связанные с ними потери.)

Итак, выходит, что деньги растут… на деревьях Меркла?


В этом материале мы сделали несколько выводов из открытого набора данных по криптовалютам, продемонстрировав сильные стороны PostgreSQL и TimescaleDB. И все же следует помнить, что криптовалютный рынок неизбежно изменится в следующем месяце, на следующей неделе или даже завтра.

Однако если вы хотите самостоятельно изучить этот набор и провести свой анализ, к Вашим услугам доступна соответствующая инструкция по скачиванию данных и установке TimescaleDB.

Если вы захотите узнать больше о TimescaleDB и о том, как она позволяет повысить эффективность PostgreSQL для работы с временными рядами, рекомендуем ознакомиться с техническим постом.

image
Wirex
Мобильный банкинг нового поколения
AdBlock похитил этот баннер, но баннеры не зубы — отрастут

Подробнее
Реклама

Комментарии 8

    0
    Вы представляете новый инструмент финансового анализа, и постоянно упоминаете некое «закрытие». Предлагаю вам не губить репутацию нерелевантными словами из эпохи динозавров. Крипто торгуется круглые сутки, выберите конкретный час, используйте VWAP за день или предложите что-либо еще, главное чтобы это было логически и математичски релевантно.
      +1

      А не приходило в голову, что термин "закрытие (некоторого) периода" — вполне нормальный? Так что, термин "цена закрытия" — всегда можно наделить адекватным смыслом. А вы тут уже репутацию прикрутили )

        0

        На любом графике со свечами видна цена закрытия, на каждой свечке. Вне зависимости от выбранного периода свечки.

        0
        1200 криптовалют в анализе? Да сколько же их тогда всего? Такими темпами, скоро у каждого жителя Земли по собственой криптовалюте будет!
          0
          Я конечно ни разу не гриписовец, но чисто из любопытства, очень хотелось бы увидеть статистику киловатт-часов электроэнергии, сожженной на майнинг всего этого добра.
            +1

            Не каждая указанная тут криптовалюта требует майнинга.
            Многие используют чужой блокчейн, даже не добавляя сложности майнингу.


            Такими темпами, скоро у каждого жителя Земли по собственой криптовалюте будет!

            Да, вы сами можете создать свою "валюту", в этом нет ничего сложного, сложнее сделать так, чтобы она была нужна не только вам.
            Всего их уже несчётное множество, но большинство не имеет ценности. Многие вообще перестали интересовать даже своих держателей или создавались изначально для теста.


            Т.е. образно говоря не от количества валют зависит количество сожжённых киловатт-часов.


            Статистику по хешрейту отдельных блокчейнов можно собрать, ну и с какой-то погрешностью привести к киловатт-часам. Сомневаюсь, что кто-то это делал уже. Вряд ли удастся найти готовую инфу.

            0

            Точно. Список официально представленных криптовалют можете найти тут.

            0
            Было бы хорошо в табличках типа «Общий объем транзакций 10 основных криптовалют в долларах США за последнюю неделю (по убыванию)», подровнять количество цифр после запятой, а то я не сразу понял, почему биткоин там на первом месте.

            Только полноправные пользователи могут оставлять комментарии. Войдите, пожалуйста.

            Самое читаемое