Analyze cryptocurrency market data

Analyze cryptocurrency market data

This tutorial is a step-by-step guide on how to analyze a time-series cryptocurrency dataset using TimescaleDB. The instructions in this tutorial were used to create this analysis of 4100+ cryptocurrencies.

The tutorial covers these steps:

Design our database schema
Create a dataset using publicly available cryptocurrency pricing data
Load the dataset into TimescaleDB
Query the data in TimescaleDB

You can skip ahead to the TimescaleDB portion if you would prefer not to run through the scripts to create your database schema or your dataset.

You can also download the resources for this tutorial:

Schema creation script:

schema.sql - Dataset creation script:

crypto_data_extraction.py - Dataset:

Crypto Currency Dataset September 2019

(Note that this data is from September 2019. Follow the steps in Section 2 of this tutorial if you require fresh data)

Prerequisites

To complete this tutorial, you need a cursory knowledge of the Structured Query Language (SQL). The tutorial walks you through each SQL command, but it is helpful if you’ve seen SQL before.

To start, install TimescaleDB. When your installation is complete, you can start ingesting or creating sample data.

This tutorial leads directly into a second tutorial that covers how Timescale can be used with Tableau to visualize time-series data.

Design the database schema

When you have a new database up and running, you need some data to insert into it. Before you get data for analysis, you need to define what kind of data you want to perform queries on.

In this analysis, we have two main goals:

Explore the price of Bitcoin and Ethereum, expressed in different fiat currencies, over time.
Explore the price of different cryptocurrencies, expressed in Bitcoin, over time.

Some questions you might want to ask:

How has Bitcoin’s price in USD varied over time?
How has Ethereum’s price in ZAR varied over time?
How has Bitcoin’s trading volume in KRW increased or decreased over time?
Which crypto-currency has the greatest trading volume in the last two weeks?
Which day was Bitcoin most profitable?
Which are the most profitable new coins from the past three months?

Understanding the questions you want to ask of the data helps to inform your schema definition.

These requirements lead us to four tables. We need three TimescaleDB hypertables, called btc_prices, crypto_prices, and eth_prices, and one relational table, called currency_info.

The btc_prices and eth_prices hypertables contain data about Bitcoin prices in 17 different fiat currencies since 2010. This is the Bitcoin table, but the Ethereum table is very similar:

Field	Description
`time`	The day-specific timestamp of the price records, with time given as the default 00:00:00+00
`opening_price`	The first price at which the coin was exchanged that day
`highest_price`	The highest price at which the coin was exchanged that day
`lowest_price`	The lowest price at which the coin was exchanged that day
`closing_price`	The last price at which the coin was exchanged that day
`volume_btc`	The volume exchanged in the cryptocurrency value that day, in BTC
`volume_currency`	The volume exchanged in its converted value for that day, quoted in the corresponding fiat currency
`currency_code`	Corresponds to the fiat currency used for non-BTC prices/volumes

Finally, the currency_info table maps the currency’s code to its English-language name:

Field	Description
`currency_code`	2-7 character abbreviation for currency. Used in other hypertables
`currency`	English name of currency

When you have established the schema for the tables in the database, you can formulate create_table SQL statements to actually create the tables you need:

2. --Schema for cryptocurrency analysis

4. DROP TABLE IF EXISTS "currency_info";

6. CREATE TABLE "currency_info"(

8. currency_code   VARCHAR (10),

10. currency        TEXT

12. );

14. --Schema for btc_prices table

16. DROP TABLE IF EXISTS "btc_prices";

18. CREATE TABLE "btc_prices"(

20. time            TIMESTAMP WITH TIME ZONE NOT NULL,

22. opening_price   DOUBLE PRECISION,

24. highest_price   DOUBLE PRECISION,

26. lowest_price    DOUBLE PRECISION,

28. closing_price   DOUBLE PRECISION,

30. volume_btc      DOUBLE PRECISION,

32. volume_currency DOUBLE PRECISION,

34. currency_code   VARCHAR (10)

36. );

38. --Schema for crypto_prices table

40. DROP TABLE IF EXISTS "crypto_prices";

42. CREATE TABLE "crypto_prices"(

44. time            TIMESTAMP WITH TIME ZONE NOT NULL,

46. opening_price   DOUBLE PRECISION,

48. highest_price   DOUBLE PRECISION,

50. lowest_price    DOUBLE PRECISION,

52. closing_price   DOUBLE PRECISION,

54. volume_crypto   DOUBLE PRECISION,

56. volume_btc      DOUBLE PRECISION,

58. currency_code   VARCHAR (10)

60. );

62. --Schema for eth_prices table

64. DROP TABLE IF EXISTS "eth_prices";

66. CREATE TABLE "eth_prices"(

68. time            TIMESTAMP WITH TIME ZONE NOT NULL,

70. opening_price   DOUBLE PRECISION,

72. highest_price   DOUBLE PRECISION,

74. lowest_price    DOUBLE PRECISION,

76. closing_price   DOUBLE PRECISION,

78. volume_eth      DOUBLE PRECISION,

80. volume_currency DOUBLE PRECISION,

82. currency_code   VARCHAR (10)

84. );

86. --Timescale specific statements to create hypertables for better performance

88. SELECT create_hypertable('btc_prices', 'time');

90. SELECT create_hypertable('eth_prices', 'time');

92. SELECT create_hypertable('crypto_prices', 'time');

Note that there are three create_hypertable statements which are TimescaleDB-specific statements. A hypertable is an abstraction of a single continuous table across time intervals, so that you can query it using standard SQL. For more on hypertables, see the Timescale docs and this blog post.

Create a dataset to analyze

Now that you’ve defined the data you want, you can construct a dataset containing that data. You can write a small Python script for extracting data from CryptoCompare into four CSV files, called coin_names.csv, crypto_prices.csv, btc_prices.csv, and eth_prices.csv.

To get data from CryptoCompare, you’ll need to obtain an API key. For this analysis, the free key is sufficient.

The script consists of five parts:

Import the necessary Python libraries in order to complete the data extraction
Populate the currency_info table with a list of coin names
Get the historical Bitcoin (BTC) prices in 4198 other cryptocurrencies and populate the crypto_prices table
Get historical Bitcoin prices in different fiat currencies to populate btc_prices
Get historical Ethereum prices in different fiat currencies to populate eth_prices

Here’s the full Python script, which you can also

download

2. #####################################################################

4. #1. Import library and setup API key

6. #####################################################################

8. import requests

10. import json

12. import csv

14. from datetime import datetime

16. apikey = 'YOUR_CRYPTO_COMPARE_API_KEY'

18. #attach to end of URLstring

20. url_api_part = '&api_key=' + apikey

22. #####################################################################

24. #2. Populate list of all coin names

26. #####################################################################

28. #URL to get a list of coins from cryptocompare API

30. URLcoinslist = 'https://min-api.cryptocompare.com/data/all/coinlist'

32. #Get list of cryptos with their symbols

34. res1 = requests.get(URLcoinslist)

36. res1_json = res1.json()

38. data1 = res1_json['Data']

40. symbol_array = []

42. cryptoDict = dict(data1)

44. #write to CSV

46. with open('coin_names.csv', mode = 'w') as test_file:

48. test_file_writer = csv.writer(test_file,

50. delimiter = ',',

52. quotechar = '"',

54. quoting=csv.QUOTE_MINIMAL)

56. for coin in cryptoDict.values():

58. if day.get('time') == None:

60. continue # skip this item

62. name = coin['Name']

64. symbol = coin['Symbol']

66. symbol_array.append(symbol)

68. coin_name = coin['CoinName']

70. full_name = coin['FullName']

72. entry = [symbol, coin_name]

74. test_file_writer.writerow(entry)

76. print('Done getting crypto names and symbols. See coin_names.csv for result')

78. #####################################################################

80. #3. Populate historical price for each crypto in BTC

82. #####################################################################

84. #Note: this part might take a while to run since we're populating data for 4k+ coins

86. #counter variable for progress made

88. progress = 0

90. num_cryptos = str(len(symbol_array))

92. for symbol in symbol_array:

94. # get data for that currency

96. URL = 'https://min-api.cryptocompare.com/data/histoday?fsym=' +

98. symbol +

100. '&tsym=BTC&allData=true' +

102. url_api_part

104. res = requests.get(URL)

106. res_json = res.json()

108. data = res_json['Data']

110. # write required fields into csv

112. with open('crypto_prices.csv', mode = 'a') as test_file:

114. test_file_writer = csv.writer(test_file,

116. delimiter = ',',

118. quotechar = '"',

120. quoting=csv.QUOTE_MINIMAL)

122. for day in data:

124. rawts = day['time']

126. ts = datetime.utcfromtimestamp(rawts).strftime('%Y-%m-%d %H:%M:%S')

128. o = day['open']

130. h = day['high']

132. l = day['low']

134. c = day['close']

136. vfrom = day['volumefrom']

138. vto = day['volumeto']

140. entry = [ts, o, h, l, c, vfrom, vto, symbol]

142. test_file_writer.writerow(entry)

144. progress = progress + 1

146. print('Processed ' + str(symbol))

148. print(str(progress) + ' currencies out of ' +  num_cryptos + ' written to csv')

150. print('Done getting price data for all coins. See crypto_prices.csv for result')

152. #####################################################################

154. #4. Populate BTC prices in different fiat currencies

156. #####################################################################

158. # List of fiat currencies we want to query

160. # You can expand this list, but CryptoCompare does not have

162. # a comprehensive fiat list on their site

164. fiatList = ['AUD', 'CAD', 'CNY', 'EUR', 'GBP', 'GOLD', 'HKD',

166. 'ILS', 'INR', 'JPY', 'KRW', 'PLN', 'RUB', 'SGD', 'UAH', 'USD', 'ZAR']

168. #counter variable for progress made

170. progress2 = 0

172. for fiat in fiatList:

174. # get data for bitcoin price in that fiat

176. URL = 'https://min-api.cryptocompare.com/data/histoday?fsym=BTC&tsym=' +

178. fiat +

180. '&allData=true' +

182. url_api_part

184. res = requests.get(URL)

186. res_json = res.json()

188. data = res_json['Data']

190. # write required fields into csv

192. with open('btc_prices.csv', mode = 'a') as test_file:

194. test_file_writer = csv.writer(test_file,

196. delimiter = ',',

198. quotechar = '"',

200. quoting=csv.QUOTE_MINIMAL)

202. for day in data:

204. rawts = day['time']

206. ts = datetime.utcfromtimestamp(rawts).strftime('%Y-%m-%d %H:%M:%S')

208. o = day['open']

210. h = day['high']

212. l = day['low']

214. c = day['close']

216. vfrom = day['volumefrom']

218. vto = day['volumeto']

220. entry = [ts, o, h, l, c, vfrom, vto, fiat]

222. test_file_writer.writerow(entry)

224. progress2 = progress2 + 1

226. print('processed ' + str(fiat))

228. print(str(progress2) + ' currencies out of  17 written')

230. print('Done getting price data for btc. See btc_prices.csv for result')

232. #####################################################################

234. #5. Populate ETH prices in different fiat currencies

236. #####################################################################

238. #counter variable for progress made

240. progress3 = 0

242. for fiat in fiatList:

244. # get data for bitcoin price in that fiat

246. URL = 'https://min-api.cryptocompare.com/data/histoday?fsym=ETH&tsym=' +

248. fiat +

250. '&allData=true' +

252. url_api_part

254. res = requests.get(URL)

256. res_json = res.json()

258. data = res_json['Data']

260. # write required fields into csv

262. with open('eth_prices.csv', mode = 'a') as test_file:

264. test_file_writer = csv.writer(test_file,

266. delimiter = ',',

268. quotechar = '"',

270. quoting=csv.QUOTE_MINIMAL)

272. for day in data:

274. rawts = day['time']

276. ts = datetime.utcfromtimestamp(rawts).strftime('%Y-%m-%d %H:%M:%S')

278. o = day['open']

280. h = day['high']

282. l = day['low']

284. c = day['close']

286. vfrom = day['volumefrom']

288. vto = day['volumeto']

290. entry = [ts, o, h, l, c, vfrom, vto, fiat]

292. test_file_writer.writerow(entry)

294. progress3 = progress3 + 1

296. print('processed ' + str(fiat))

298. print(str(progress3) + ' currencies out of  17 written')

300. print('Done getting price data for eth. See eth_prices.csv for result')

After running the script, you should have four .csv files:

2. python crypto_data_extraction.py

Load the dataset into TimescaleDB

Before you start, you need a working installation of TimescaleDB.

Set up the schema

Now all your hard work at the beginning comes in handy, and you can use the SQL script you created to set up the TimescaleDB unstance. If you don’t want to enter the SQL script by yourself, you can download

schema.sql

instead.

2. psql -x "postgres://tsdbadmin:{YOUR_PASSWORD_HERE}@{YOUR_HOSTNAME_HERE}:{YOUR_PORT_HERE}/defaultdb?sslmode=require"

From the psql command line, create a database. Let’s call it crypto_data:

2. CREATE DATABASE crypto_data;

4. \c crypto_data

6. CREATE EXTENSION IF NOT EXISTS timescaledb CASCADE;

From the command prompt, you can apply the schema creation script to the database like this:

2. psql -x "postgres://tsdbadmin:{YOUR_PASSWORD_HERE}@{|YOUR_HOSTNAME_HERE}:{YOUR_PORT_HERE}/crypto_data?sslmode=require" < schema.sql

The output should look something like this:

2. NOTICE:  00000: table "currency_info" does not exist, skipping

4. LOCATION:  DropErrorMsgNonExistent, tablecmds.c:1057

6. DROP TABLE

8. Time: 78.384 ms

10. CREATE TABLE

12. Time: 87.011 ms

14. NOTICE:  00000: table "btc_prices" does not exist, skipping

16. LOCATION:  DropErrorMsgNonExistent, tablecmds.c:1057

18. DROP TABLE

20. Time: 77.094 ms

22. CREATE TABLE

24. Time: 79.815 ms

26. NOTICE:  00000: table "crypto_prices" does not exist, skipping

28. LOCATION:  DropErrorMsgNonExistent, tablecmds.c:1057

30. DROP TABLE

32. Time: 78.430 ms

34. CREATE TABLE

36. Time: 78.430 ms

38. NOTICE:  00000: table "eth_prices" does not exist, skipping

40. LOCATION:  DropErrorMsgNonExistent, tablecmds.c:1057

42. DROP TABLE

44. Time: 77.410 ms

46. CREATE TABLE

48. Time: 80.883 ms

50. create_hypertable

52. -------------------------

54. (1,public,btc_prices,t)

56. (1 row)

58. Time: 83.154 ms

60. create_hypertable

62. -------------------------

64. (2,public,eth_prices,t)

66. (1 row)

68. Time: 84.650 ms

70. create_hypertable

72. ----------------------------

74. (3,public,crypto_prices,t)

76. (1 row)

78. Time: 81.864 ms

Now when you log back in to the TimescaleDB instance using psql, you can run the \dt command and see that the tables have been created properly:

2. List of relations

4. Schema |     Name      | Type  |   Owner

6. --------+---------------+-------+-----------

8. public | btc_prices    | table | tsdbadmin

10. public | crypto_prices | table | tsdbadmin

12. public | currency_info | table | tsdbadmin

14. public | eth_prices    | table | tsdbadmin

16. (4 rows)

Ingest data

Now that you’ve created the tables with the desired schema, all that’s left is to insert the data from the .csv files you created into the tables.

Make sure you are logged into TimescaleDB using psql so that you can run each of these commands in turn:

2. \COPY btc_prices FROM btc_prices.csv CSV;

4. \COPY eth_prices FROM eth_prices.csv CSV;

6. \COPY crypto_prices FROM crypto_prices.csv CSV;

8. \COPY currency_info FROM coin_names.csv CSV;

important

Data ingestion could take a while, depending on the speed of your Internet connection.

You can verify that the ingestion worked by running a simple SQL command, such as:

2. SELECT * FROM btc_prices LIMIT 5;

You should get something like this output:

2. -[ RECORD 1 ]---+-----------------------

4. time            | 2013-03-11 00:00:00+00

6. opening_price   | 60.56

8. highest_price   | 60.56

10. lowest_price    | 60.56

12. closing_price   | 60.56

14. volume_btc      | 0.1981

16. volume_currency | 12

18. currency_code   | AUD

20. -[ RECORD 2 ]---+-----------------------

22. time            | 2013-03-12 00:00:00+00

24. opening_price   | 60.56

26. highest_price   | 60.56

28. lowest_price    | 41.38

30. closing_price   | 47.78

32. volume_btc      | 47.11

34. volume_currency | 2297.5

36. currency_code   | AUD

38. -[ RECORD 3 ]---+-----------------------

40. time            | 2013-03-07 00:00:00+00

42. opening_price   | 181.15

44. highest_price   | 273.5

46. lowest_price    | 237.4

48. closing_price   | 262.87

50. volume_btc      | 33.04

52. volume_currency | 8974.45

54. currency_code   | CNY

56. -[ RECORD 4 ]---+-----------------------

58. time            | 2013-03-07 00:00:00+00

60. opening_price   | 32.31

62. highest_price   | 35.03

64. lowest_price    | 26

66. closing_price   | 31.57

68. volume_btc      | 13321.61

70. volume_currency | 425824.38

72. currency_code   | EUR

74. -[ RECORD 5 ]---+-----------------------

76. time            | 2013-03-11 00:00:00+00

78. opening_price   | 35.7

80. highest_price   | 37.35

82. lowest_price    | 35.4

84. closing_price   | 37.15

86. volume_btc      | 3316.09

88. volume_currency | 121750.98

90. currency_code   | EUR

92. Time: 224.741 ms

Query and analyze the data

At the beginning of the tutorial, we defined some questions to answer. Naturally, each of those questions has an answer in the form of a SQL query. Now that you database is set up properly, and the data is captured and ingested, you can find some answers:

For example, How did Bitcoin price in USD vary over time?

2. SELECT time_bucket('7 days', time) AS period,

4. last(closing_price, time) AS last_closing_price

6. FROM btc_prices

8. WHERE currency_code = 'USD'

10. GROUP BY period

12. ORDER BY period

How did BTC daily returns vary over time? Which days had the worst and best returns?

2. SELECT time,

4. closing_price / lead(closing_price) over prices AS daily_factor

6. FROM (

8. SELECT time,

10. closing_price

12. FROM btc_prices

14. WHERE currency_code = 'USD'

16. GROUP BY 1,2

18. ) sub window prices AS (ORDER BY time DESC)

How did the trading volume of Bitcoin vary over time in different fiat currencies?

2. SELECT time_bucket('7 days', time) AS period,

4. currency_code,

6. sum(volume_btc)

8. FROM btc_prices

10. GROUP BY currency_code, period

12. ORDER BY period

How did Ethereum (ETH) price in BTC vary over time?

2. SELECT

4. time_bucket('7 days', time) AS time_period,

6. last(closing_price, time) AS closing_price_btc

8. FROM crypto_prices

10. WHERE currency_code='ETH'

12. GROUP BY time_period

14. ORDER BY time_period

How did ETH prices, in different fiat currencies, vary over time?

2. SELECT time_bucket('7 days', c.time) AS time_period,

4. last(c.closing_price, c.time) AS last_closing_price_in_btc,

6. last(c.closing_price, c.time) * last(b.closing_price, c.time) FILTER (WHERE b.currency_code = 'USD') AS last_closing_price_in_usd,

8. last(c.closing_price, c.time) * last(b.closing_price, c.time) FILTER (WHERE b.currency_code = 'EUR') AS last_closing_price_in_eur,

10. last(c.closing_price, c.time) * last(b.closing_price, c.time) FILTER (WHERE b.currency_code = 'CNY') AS last_closing_price_in_cny,

12. last(c.closing_price, c.time) * last(b.closing_price, c.time) FILTER (WHERE b.currency_code = 'JPY') AS last_closing_price_in_jpy,

14. last(c.closing_price, c.time) * last(b.closing_price, c.time) FILTER (WHERE b.currency_code = 'KRW') AS last_closing_price_in_krw

16. FROM crypto_prices c

18. JOIN btc_prices b

20. ON time_bucket('1 day', c.time) = time_bucket('1 day', b.time)

22. WHERE c.currency_code = 'ETH'

24. GROUP BY time_period

26. ORDER BY time_period

Which cryptocurrencies had the most transaction volume in the past 14 days?

2. SELECT 'BTC' AS currency_code,

4. sum(b.volume_currency) AS total_volume_in_usd

6. FROM btc_prices b

8. WHERE b.currency_code = 'USD'

10. AND now() - date(b.time) < INTERVAL '14 day'

12. GROUP BY b.currency_code

14. UNION

16. SELECT c.currency_code AS currency_code,

18. sum(c.volume_btc) * avg(b.closing_price) AS total_volume_in_usd

20. FROM crypto_prices c JOIN btc_prices b ON date(c.time) = date(b.time)

22. WHERE c.volume_btc > 0

24. AND b.currency_code = 'USD'

26. AND now() - date(b.time) < INTERVAL '14 day'

28. AND now() - date(c.time) < INTERVAL '14 day'

30. GROUP BY c.currency_code

32. ORDER BY total_volume_in_usd DESC

Which cryptocurrencies had the top daily return?

2. WITH

4. prev_day_closing AS (

6. SELECT

8. currency_code,

10. time,

12. closing_price,

14. LEAD(closing_price) OVER (PARTITION BY currency_code ORDER BY TIME DESC) AS prev_day_closing_price

16. FROM

18. crypto_prices

20. )

22. ,    daily_factor AS (

24. SELECT

26. currency_code,

28. time,

30. CASE WHEN prev_day_closing_price = 0 THEN 0 ELSE closing_price/prev_day_closing_price END AS daily_factor

32. FROM

34. prev_day_closing

36. )

38. SELECT

40. time,

42. LAST(currency_code, daily_factor) AS currency_code,

44. MAX(daily_factor) AS max_daily_factor

46. FROM

48. daily_factor

50. GROUP BY

52. time

Next steps

While it’s fun to run SQL queries in the command line, the real magic is when you’re able to visualize it. Follow the companion tutorial to this piece and learn how to use TimescaleDB and Tableau together to visualize your time-series data.

Ready for even more learning? Here’s a few suggestions:

Analyze cryptocurrency data