This notebook explores the number of listings by dollar volume for each country.
from quantrocket import get_prices
from quantrocket.account import download_exchange_rates
from quantrocket.master import get_securities_reindexed_like
import pandas as pd
import io
Get exchange rates (for converting non-USD stocks to USD dollar volume):
f = io.StringIO()
download_exchange_rates(f, latest=True, base_currencies="USD")
rates = pd.read_csv(f)
rates = rates.set_index("QuoteCurrency").Rate.to_dict()
def get_mean_dollar_volumes(
db,
start_date,
universes=None):
"""
Query database and return a Series of average USD dollar volume by sid.
"""
prices = get_prices(
db,
start_date=start_date,
universes=universes,
fields=["Close","Volume"])
closes = prices.loc["Close"]
volumes = prices.loc["Volume"]
securities = get_securities_reindexed_like(closes, fields=["Currency","Multiplier","PriceMagnifier"])
currencies = securities.loc["Currency"]
multipliers = securities.loc["Multiplier"]
magnifiers = securities.loc["PriceMagnifier"]
exchange_rates = currencies.applymap(lambda x: rates[x])
exchange_rates = exchange_rates.reindex(closes.index, method="ffill")
closes = closes * multipliers.fillna(1) / magnifiers.fillna(1)
dollar_volumes = closes.div(exchange_rates) * volumes
avg_dollar_volumes = dollar_volumes.mean()
return avg_dollar_volumes
Countries for which numbers should be run:
countries = (
# North America
("usa-stk-1d", "United States"),
("canada-stk-1d", "Canada"),
("mexico-stk-1d", "Mexico"),
# Asia
("australia-stk-1d", "Australia"),
("japan-stk-1d", "Japan"),
("hongkong-stk-1d", "Hong Kong"),
("singapore-stk-1d", "Singapore"),
# Europe
("spain-stk-1d", "Spain"),
("netherlands-stk-1d", "Netherlands"),
("uk-stk-1d", "United Kingdom"),
("germany-stk-1d", "Germany"),
("france-stk-1d", "France"),
("belgium-stk-1d", "Belgium"),
("swiss-stk-1d", "Switzerland"),
("austria-stk-1d", "Austria"),
("sweden-stk-1d", "Sweden"),
("italy-stk-1d", "Italy"),
)
Run numbers for each country:
all_countries = {}
start_date = "2018-01-01"
for db, country in countries:
print(country)
dollar_volumes = get_mean_dollar_volumes(db, start_date)
all_countries[country] = dollar_volumes
United States Canada Mexico Australia Japan Hong Kong Singapore Spain Netherlands United Kingdom Germany France Belgium Switzerland Austria Sweden Italy
import matplotlib.pyplot as plt
import seaborn as sns
import math
sns.set(palette=sns.color_palette("Blues_d", n_colors=1))
def get_listings_above(threshold):
"""
Returns counts of listings per country which are above the dollar volume threshold.
"""
listings = {}
for country, dollar_volumes in all_countries.items():
listings[country] = dollar_volumes[dollar_volumes>=threshold].count()
listings = pd.Series(listings)
return listings
def plot_counts(listings, title, tick_spacing=100):
"""
Plot listing counts by country.
"""
sns.set(palette=sns.color_palette("Blues_d", n_colors=1))
xmax = math.ceil(listings.max()/100) * 100 + 100
ax = listings.sort_values().plot(kind="barh", title=title, figsize=(16,6), xticks=list(range(0, xmax, tick_spacing)))
plt.xticks(rotation=90)
ax.set_xlabel("Number of listings")
def plot_us_vs_world(listings, title):
"""
Plot US listings vs the rest of the world.
"""
sns.set(palette=sns.color_palette("Blues_d", n_colors=2))
us_v_world = pd.Series(
[listings.loc["United States"],
listings.loc[listings.index != "United States"].sum()],
index=["United States", "Rest of World"], name="Percentage of total")
us_v_world.plot(kind="barh", title=title, figsize=(16,2))
Plot listing counts above various thresholds:
listings = get_listings_above(0)
plot_counts(listings[listings.index != "United States"], "Global stocks listings by country, excluding the US")
plot_us_vs_world(listings, title="Global stock listings available through Interactive Brokers")
listings = get_listings_above(1000000)
plot_counts(listings[listings.index != "United States"], "Global stock listings with average daily dollar volume above $1M USD")
plot_us_vs_world(listings, "Global stock listings with average daily dollar volume above $1M USD")
