Notebook
In [89]:
from pytz import timezone
import matplotlib.pyplot as plt
import pandas as pd
In [107]:
# get minute bar price & volume data for SPY & SHY & compute dollar volume
# assumption is that empty bars can be filled with zeros, corresponding to no trading volume
price = get_pricing(['SPY','SHY'],start_date='2019-01-01',end_date='2020-04-10',fields='price',frequency='minute')
volume = get_pricing(['SPY','SHY'],start_date='2019-01-01',end_date='2020-04-10',fields='volume',frequency='minute').fillna(0)
data = price*volume
data.tail(3)
Out[107]:
Equity(8554 [SPY]) Equity(23911 [SHY])
2020-04-09 19:58:00+00:00 2.121705e+08 4220970.600
2020-04-09 19:59:00+00:00 2.585413e+08 2332986.633
2020-04-09 20:00:00+00:00 0.000000e+00 0.000
In [108]:
# add time & date columns to the data, for pivot table
data['time'] = data.index.tz_convert(timezone('US/Eastern')).time
data['date'] = data.index.date
data.tail(3)
Out[108]:
Equity(8554 [SPY]) Equity(23911 [SHY]) time date
2020-04-09 19:58:00+00:00 2.121705e+08 4220970.600 15:58:00 2020-04-09
2020-04-09 19:59:00+00:00 2.585413e+08 2332986.633 15:59:00 2020-04-09
2020-04-09 20:00:00+00:00 0.000000e+00 0.000 16:00:00 2020-04-09
In [109]:
# on a rolling basis, compute the difference in dollar volume z-scores using a minutely trailing window
# credit to Quantopian user Michael Van Kleeck for this efficient code
# ref.: https://www.quantopian.com/posts/modified-heatmap-based-on-grant-kiehnes-example
WINDOW = 260*390
dollar_volumes = data.iloc[:, 0:2]
means = dollar_volumes.apply(lambda x: pd.rolling_mean(x, window=WINDOW))
sds = dollar_volumes.apply(lambda x: pd.rolling_std(x, window=WINDOW))
zs = ((dollar_volumes - means) / sds)
data['z_diff'] = zs.iloc[:, 1] - zs.iloc[:, 0] # DV_SHY - DV_SPY (where DV is the z-score normalized dollar volume)
data.tail(3)

/venvs/py35/lib/python3.5/site-packages/ipykernel_launcher.py:6: FutureWarning: pd.rolling_mean is deprecated for Series and will be removed in a future version, replace with 
	Series.rolling(center=False,window=101400).mean()
  
/venvs/py35/lib/python3.5/site-packages/ipykernel_launcher.py:7: FutureWarning: pd.rolling_std is deprecated for Series and will be removed in a future version, replace with 
	Series.rolling(center=False,window=101400).std()
  import sys
Out[109]:
Equity(8554 [SPY]) Equity(23911 [SHY]) time date z_diff
2020-04-09 19:58:00+00:00 2.121705e+08 4220970.600 15:58:00 2020-04-09 -1.969160
2020-04-09 19:59:00+00:00 2.585413e+08 2332986.633 15:59:00 2020-04-09 -3.128789
2020-04-09 20:00:00+00:00 0.000000e+00 0.000 16:00:00 2020-04-09 0.588060
In [110]:
# apply a pivot table, with trading day along the vertical & trading time along the horizontal
# fill empty minutes with zeros
ht_map = pd.pivot_table(data.dropna(),'z_diff',index=data['date'], columns=data['time'],fill_value=0)
ht_map.head(3)
Out[110]:
time 09:31:00 09:32:00 09:33:00 09:34:00 09:35:00 09:36:00 09:37:00 09:38:00 09:39:00 09:40:00 ... 15:51:00 15:52:00 15:53:00 15:54:00 15:55:00 15:56:00 15:57:00 15:58:00 15:59:00 16:00:00
date
2020-01-15 0.000000 0.000000 0.000000 0.000000 0.000000 0.000000 0.000000 0.000000 0.000000 0.000000 ... -2.701478 -0.286623 -1.465760 -0.898332 -2.753959 -2.069552 -1.186314 -0.622311 -2.005012 -7.031941
2020-01-16 -3.409384 -0.472741 -0.668204 -0.663369 -0.830018 -1.314846 -0.704477 -0.006026 -0.152241 -0.066090 ... -3.444693 -1.710691 -3.589817 -2.055908 -2.126780 -2.345811 -1.672172 -1.773317 -5.067654 0.665374
2020-01-17 -4.499332 -1.462196 -1.022870 -0.344770 -0.433000 -0.595594 -1.225796 -0.396233 -1.136439 -2.006456 ... -2.249840 -1.893283 -2.856998 -2.145024 -3.545572 -3.786489 -4.711909 -1.648487 -3.844358 -12.605417

3 rows × 390 columns

In [117]:
# plot heat map of z-score difference
# lighter color means z_diff > 0 (DV_SHY > DV_SPY) & darker color mean z_diff < 0 (DV_SHY < DV_SPY)
# see http://matplotlib.org/examples/color/colormaps_reference.html to switch colormap
plt.imshow(ht_map,cmap='jet',aspect=5)
plt.grid(False)
plt.colorbar()
plt.clim(ht_map.min().min(),ht_map.max().max())
plt.title('SPY & SHY z-score-normalized minutely dollar volume difference, ~2020-present')
plt.xlabel('trading minute')
plt.ylabel('trading day')
Out[117]:
<matplotlib.text.Text at 0x7f8c3d6efb70>
In [ ]: