Betashares Outstanding Unit Volume
This notebook analyzes the volume of Betashares on issue to determine public interest and judge trend in liquidity.
! pip3 install rich[jupyter] tabula-py rich-dataframe >> /dev/null
import tabula
import os
import glob
import pandas as pd
from rich import print
import plotly.express as px
from IPython.core.display import display, HTML
display(HTML("<style>.container { width:100% !important; }</style>"))
from jinja2 import Template
from IPython.display import display, HTML, JSON
from google.colab import data_table
data_table.enable_dataframe_formatter()
#data_table.disable_dataframe_formatter() ### Change display of dataframe in colabs
files_to_process = glob.glob("*.pdf")
complete_df = pd.DataFrame([])
for file_to_process in files_to_process:
tables = tabula.read_pdf(file_to_process, pages="all", silent=True, pandas_options={'header': None})
for index, table in enumerate(tables):
table.columns = ['ASX Code', 'ETF','Outstanding Units' ]
if index == 0:
table = table.iloc[1: , :] ## select all rows after the first row
df = pd.concat(tables)
df['Month'] = file_to_process.split(".")[0]
complete_df = pd.concat([complete_df, df])
complete_df = complete_df[complete_df['ASX Code'] != 'ASX Code']
complete_df['Month'] = pd.to_datetime(complete_df['Month']).dt.date
complete_df = complete_df.sort_values(by=['ASX Code' , 'Month'])
complete_df['Outstanding Units'] = complete_df['Outstanding Units'].str.replace(',', '').astype('int')
pivoted_df = complete_df.pivot(index=["ASX Code", "ETF"], columns="Month", values="Outstanding Units").reset_index()
pivoted_df.columns = pivoted_df.columns.astype(str)
pivoted_df.fillna(0, inplace=True)
#for col in list(pivoted_df.columns):
# if col.startswith('20'):
# pivoted_df[col] = pivoted_df[col].str.replace(',', '').astype('int')
print(len(pivoted_df), "EFT's Loaded", 'Up :thumbsup:')
#data_table.DataTable(pivoted_df, include_index=False, num_rows_per_page=52)
## Generic Approach to render data frame through jinja2 template in notebook
template = Template('''
<table class="formattedTable" width="100%">
<thead>
<tr>
{% for c in columns %}
<th>{{ c }}</th>
{% endfor %}
</tr>
</thead>
<tbody>
{% for row in rows %}
<tr class="{{ loop.cycle('odd', 'even') }}" >
{% for k, v in row.items() %}
{% if v|int != 0 or v == 0 %}
<td><div>{{"%.0f"|format(v|int)}}</div></td>
{% else %}
<td><div>{{v}}</div></td>
{% endif %}
{% endfor %}
</tr>
{% endfor %}
</tbody>
</table>
<style>
table.formattedTable {
border: 1px solid lightgray;
border-bottom: 1px solid lightgray;
border-spacing: 0;
}
table.formattedTable th {
border: thin solid grey;
background : lightgray;
color: black;
min-width: 90px;
text-align: center;
padding: 5px;
}
table.formattedTable td {
border: thin solid grey;
min-width: 90px;
text-align: center;
padding: 5px;
}
table.formattedTable.odd {
background: #0D1424;
}
.even {
background: lightgrey;
}
</style>''')
HTML(template.render(
rows=pivoted_df[['ASX Code', 'ETF', '2021-05-01', '2022-03-01']].to_dict(orient='records'),
columns=pivoted_df[['ASX Code', 'ETF', '2021-05-01', '2022-03-01']].columns.to_list()
))
import numpy as np
pivoted_df['Percentage Change (%)'] = (pivoted_df['2022-03-01'] - pivoted_df['2021-05-01'] ) / pivoted_df['2021-05-01'] * 100
pivoted_df['Absolute Change'] = pivoted_df['2022-03-01'] - pivoted_df['2021-05-01']
change_df = pivoted_df[['ASX Code', 'ETF', 'Percentage Change (%)', 'Absolute Change']].sort_values(by='Percentage Change (%)', ascending=False)
change_df.replace([np.inf, -np.inf], np.nan, inplace=True)
# Drop rows with NaN
change_df.dropna(inplace=True)
HTML(template.render(
rows=change_df.to_dict(orient='records'),
columns=change_df.columns.to_list()
))
fig = px.bar(complete_df, x="Month", y="Outstanding Units", color='ASX Code', facet_col="ASX Code", facet_col_wrap=6, hover_data=['ASX Code', 'ETF'], height=1000).for_each_trace(lambda t: t.update(name=t.name.replace("Code", ""))).for_each_annotation(lambda a: a.update(text=a.text.split("=")[-1]))
## ['ASX Code'] == 'BHYB']
fig.update_yaxes(visible=False ,showticklabels=False, matches=None)
fig.update_xaxes(visible=False ,showticklabels=False)
fig.update_layout(showlegend=False).for_each_trace(lambda t: t.update(name=t.name.replace("ASX Code=", "")))
fig.show()