4.2 Coins Traded
The plot on the bottom of this page provides visualization of the total number of coins that have been traded on a daily basis for each of the three cryptocurrencies.
The whole process of acquiring the data is shown bellow. Clicking the toggle buttons expands each part of the code.
from google.cloud import bigquery
from google.oauth2 import service_account
import plotly.graph_objects as go
import plotly.tools as tls
from plotly.offline import plot, iplot, init_notebook_mode
from IPython.core.display import display, HTML
init_notebook_mode(connected = True)
config={'showLink': False, 'displayModeBar': False}
credentials = service_account.Credentials.from_service_account_file(
'cred.json')
project_id = 'intricate-yew-283611'
client = bigquery.Client(credentials= credentials,project=project_id)
Bitcoin
The following query (click the toggle button to expand the code) outputs the number of traded Bitcoins for each day
query_job = client.query("""
SELECT EXTRACT(DAY FROM block_timestamp) as Day,
EXTRACT(MONTH FROM block_timestamp) as Month ,
EXTRACT(YEAR FROM block_timestamp) as Year,
concat(Cast(EXTRACT(YEAR FROM block_timestamp) as String),'-',Cast(EXTRACT(MONTH FROM block_timestamp) as String),'-',Cast(EXTRACT(DAY FROM block_timestamp) as String)) as Datum,
SUM(output_value+ fee)/100000000 as TotalBitcoins
FROM `bigquery-public-data.crypto_bitcoin.transactions`
GROUP BY Year,Month, Day, Datum
Order By Year, Month, Day
""")
results = query_job.result()
bitcoin_df = results.to_dataframe()
print(bitcoin_df)
Bitcoin Cash
The following query (click the toggle button to expand the code) outputs the number of traded Bitcoin Cash coins for each day
query_job2 = client.query("""
SELECT EXTRACT(DAY FROM block_timestamp) as Day,
EXTRACT(MONTH FROM block_timestamp) as Month ,
EXTRACT(YEAR FROM block_timestamp) as Year,
concat(Cast(EXTRACT(YEAR FROM block_timestamp) as String),'-',Cast(EXTRACT(MONTH FROM block_timestamp) as String),'-',Cast(EXTRACT(DAY FROM block_timestamp) as String)) as Datum,
SUM(output_value+ fee)/100000000 as TotalBitcoins
FROM `bigquery-public-data.crypto_bitcoin_cash.transactions`
WHERE Date(block_timestamp)> '2017-07-31'
GROUP BY Year,Month, Day, Datum
Order By Year, Month, Day
""")
results2 = query_job2.result()
cash_df = results2.to_dataframe()
print(cash_df)
Litecoin
The following query (click the toggle button to expand the code) outputs the number of traded Litecoins for each day
query_job3 = client.query("""
SELECT EXTRACT(DAY FROM block_timestamp) as Day,
EXTRACT(MONTH FROM block_timestamp) as Month ,
EXTRACT(YEAR FROM block_timestamp) as Year,
concat(Cast(EXTRACT(YEAR FROM block_timestamp) as String),'-',Cast(EXTRACT(MONTH FROM block_timestamp) as String),'-',Cast(EXTRACT(DAY FROM block_timestamp) as String)) as Datum,
SUM(output_value+ fee)/100000000 as TotalBitcoins
FROM `bigquery-public-data.crypto_litecoin.transactions`
GROUP BY Year,Month, Day, Datum
Order By Year, Month, Day
""")
results3 = query_job3.result()
lite_df = results3.to_dataframe()
print(lite_df)
fig2 = go.Figure()
fig2.add_trace(go.Scatter(x = bitcoin_df['Datum'], y = bitcoin_df['TotalBitcoins'],name = 'Bitcoin'))
fig2.add_trace(go.Scatter(x = cash_df['Datum'], y = cash_df['TotalBitcoins'], name = 'Bitcoin Cash'))
fig2.add_trace(go.Scatter(x = lite_df['Datum'], y = lite_df['TotalLiteCoins'], name = 'Litecoin', line = dict(color = 'Light Green')))
def zoom(layout, xrange):
in_view = df.loc[fig2.layout.xaxis.range[0]:fig2.layout.xaxis.range[1]]
fig2.layout.yaxis.range = [in_view.High.min() - 10, in_view.High.max() + 10]
fig2.layout.on_change(zoom, 'xaxis.range')
fig2.update_layout(paper_bgcolor = 'White',xaxis_title="Date",
yaxis_title='Number of coins traded',plot_bgcolor="#FFFFFF", hovermode="x",
hoverdistance=100,
spikedistance=1000,
)
fig2.update_xaxes(showspikes=True,spikethickness=2,
spikedash="dot",
spikecolor="#999999",
spikemode="across",showgrid=False,ticks = 'outside', showline=True, linecolor='black',rangeslider_visible=True,
rangeselector=dict(
buttons=list([
dict(count=1, label="1m", step="month", stepmode="backward"),
dict(count=6, label="6m", step="month", stepmode="backward"),
dict(count=1, label="YTD", step="year", stepmode="todate"),
dict(count=1, label="1y", step="year", stepmode="backward"),
dict(step="all")
])
))
fig2.update_yaxes(autorange = True, fixedrange= False,showgrid=False, ticks = 'outside', showline=True, linecolor='black')
plot(fig2, filename = 'fig2.html', config = config)
#ThebeLab
display(HTML('fig2.html'))