Quantile Trading

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14

import os
import numpy as np
import pandas as pd
import pickle
import quandl
import warnings
from functools import reduce
from tqdm.auto import tqdm
import matplotlib.pyplot as plt
import statsmodels.formula.api as smf

pd.set_option('display.max_rows', 20)
warnings.filterwarnings("ignore")
plt.rcParams['figure.figsize'] = (10, 5)

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41

keys = ['ticker', 'per_end_date']
z1 = pd.read_csv('ZACKS_1.csv', usecols=keys + ['mkt_val'])
cols = ['nbr_shares_out', 'filing_date', 'net_lterm_debt', 'tot_lterm_debt', 'per_type',
        'eps_diluted_net', 'basic_net_eps', 'zacks_sector_code', 'zacks_x_ind_code']
z2 = pd.read_csv('ZACKS_5.csv', usecols=keys + cols)
z3 = pd.read_csv('ZACKS_3.csv', usecols=keys + ['tot_debt_tot_equity', 'per_type', 'ret_invst'])

def f(x):
    assert len(x) <= 2
    if len(x) == 2:
        x = x[x.per_type=='Q'].fillna(x[x.per_type=='A'])
    return x.reset_index().drop(['index', 'ticker', 'per_type', 'per_end_date'], axis=1)

zacks = {}

for tic in tqdm(set(z1['ticker'])):
    temp1 = z1[z1.ticker==tic].drop('ticker', axis=1).set_index('per_end_date')
    
    temp2 = z2[z2.ticker==tic].groupby('per_end_date').apply(f)
    if len(temp2) == 0:
        continue
    temp2 = temp2.droplevel(1)
    
    temp3 = z3[z3.ticker==tic].groupby('per_end_date').apply(f)
    if len(temp3) == 0:
        continue
    temp3 = temp3.droplevel(1)
    
    temp = pd.concat([temp1, temp2, temp3], axis=1).sort_index()
    temp = temp['2012-01':'2020-01']
    temp['Debt_To_Mkt_Cap'] = temp['tot_debt_tot_equity'] / temp['mkt_val']
    temp['Debt_To_Mkt_Cap']['2012-09-30':'2012-09-30']
    zacks[tic] = temp

temp = pd.concat(zacks, keys=zacks).reset_index()
temp.drop([c for c in temp.columns if ':' in c], axis=1, inplace=True)
temp.columns = ['ticker'] + list(temp.columns[1:])
temp.to_csv('../HW4/zacks.csv')

ZFB = pd.read_csv('../HW4/zacks.csv', index_col=0)
ZFB

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11

ZFB['eps_diluted_net'].fillna(ZFB['basic_net_eps'], inplace=True)

con1 = ZFB[(ZFB['zacks_sector_code'] == 5)] 
con2 = ZFB[(ZFB['zacks_sector_code'] == 13)]
con3 = ZFB[(ZFB['zacks_x_ind_code'].isin([85, 86, 87, 88, 89, 61, 62, 63, 64, 65, 66, 67 ,68 ,69, 282, 7, 8, 9, 10, 11, 82, 124, 210]))]  # filter industry
con4 = ZFB[((ZFB['basic_net_eps'].isna()) & (ZFB['eps_diluted_net'].isna()))]

ticker_out = set(pd.concat([con1, con2, con3, con4]).set_index('ticker').index)
ZFB_update = ZFB[~(ZFB['ticker'].isin(ticker_out))].set_index('ticker')

ticker_update = set(ZFB_update.index)

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20

start_date = '2012-01-01'
end_date = '2019-01-31'
n = len(ticker_update)

def get_eod(tic):
    return quandl.get(f'EOD/{tic}', start_date=start_date, end_date=end_date)


if os.path.isfile('EOD.pkl'):
    with open('EOD.pkl', 'rb') as f:
        EOD = pickle.load(f)
else:
    EOD = {}
    for tic in tqdm(ticker_update):
        try:
            EOD[tic] = get_eod(tic)
        except Exception:
            print(f'skip {tic} as it\'s missing in EOD dataset')
    with open('EOD.pkl', 'wb') as f:
        pickle.dump(EOD, f)

1
2
3
4
5
6
7

ticker_update2 = set(EOD) & ticker_update
ZFB_update2 = ZFB[(ZFB['ticker'].isin(ticker_update2))]
ZFB_update3 = ZFB_update2[ZFB_update2['tot_debt_tot_equity'] > 0.1]
ZFB_update3['final_debt'] = ZFB_update3['net_lterm_debt'].fillna(ZFB_update3['tot_lterm_debt'])
ZFB_update3['final_debt'] = ZFB_update3['final_debt'].fillna(0)
ZFB_update3['per_end_date'] = pd.to_datetime(ZFB_update3['per_end_date'])
ZFB_update3['filing_date'] = pd.to_datetime(ZFB_update3['filing_date'])

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55

cols = ['ticker', 'per_end_date', 'mkt_val', 'filing_date', 'eps_diluted_net',
        'tot_debt_tot_equity', 'ret_invst', 'final_debt']
ZFB_update4 = ZFB_update3[cols].set_index('ticker')

available_tickers = set()
for ticker in set(ZFB_update4.index):
    if 'Adj_Close' in EOD[ticker]:
        available_tickers.add(ticker)

from functools import lru_cache


@lru_cache(maxsize=None)
def prepare_data(ticker):
    ret = ZFB_update4.loc[ticker]
    if isinstance(ret, pd.Series): return
    ret = ret.set_index('per_end_date').join(EOD[ticker]['Adj_Close'], how='outer')
    ret['Adj_Close'] = ret['Adj_Close'].ffill()
    ret = ret.dropna(subset=['filing_date'], axis=0)
    ret.columns = list(ret.columns[:-1]) + ['Adj_Close_bench']
    ret = ret.reset_index(drop=True)
    ret = ret.set_index('filing_date')
    ret = EOD[ticker][['Adj_Close']].join(ret.shift(), how='left').ffill().dropna()
    return ret


def calculate_ratios(ticker):
    data = prepare_data(ticker)
    if data is None: return
    if not len(data): return
    d2m = data.eval('tot_debt_tot_equity * Adj_Close_bench / Adj_Close')
    roi = data.eval('ret_invst * (final_debt + mkt_val) / (final_debt + mkt_val * Adj_Close / Adj_Close_bench)')
    pe = data.eval('Adj_Close / eps_diluted_net')
    px = data['Adj_Close']
    res = pd.concat([d2m, roi, pe, px], axis=1)
    res.columns = ['d2m', 'roi', 'pe', 'px']
    return res

# calculate_ratios('LLY').loc['2017-10-27']

if os.path.isfile('RATIOS.pkl'):
    with open('RATIOS.pkl', 'rb') as f:
        RATIOS = pickle.load(f)
else:
    RATIOS = {}
    for ticker in tqdm(available_tickers):
        ratios = calculate_ratios(ticker)
        if ratios is None:
            continue
        else:
            RATIOS[ticker] = ratios
    with open('RATIOS.pkl', 'wb') as f:
        pickle.dump(RATIOS, f)

print(f'{len(RATIOS)} tickers in total')

1

STRATEGY_DATA = {k: v.resample('M').last().pct_change() for k, v in RATIOS.items()}

1
2
3
4
5
6
7
8

def sharpe(x):
    return x.mean() / (x.std() * 12**0.5 if x.std() else np.nan)

def win_rate(x):
    return sum(x > 0) / (sum(x != 0) if any(x != 0) else np.nan)

def maximum_drawdown(x):  # use absolute drawdown instead of percentage here -> cum pnl could be negative
    return (x.cummax() - x).max()

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25

debt_to_market_table = pd.concat([STRATEGY_DATA[tic]['d2m'] for tic in STRATEGY_DATA], axis=1)
debt_to_market_table.columns = STRATEGY_DATA.keys()
debt_to_market_table_temp = debt_to_market_table.T

n = round(1723 * 0.1)  # number of tickers we're gonna use in the strategy

returns_table = pd.concat([STRATEGY_DATA[tic]['px'] for tic in STRATEGY_DATA], axis=1)
returns_table.columns = STRATEGY_DATA.keys()

debt_to_market_strategy = pd.DataFrame(
    [debt_to_market_table_temp.nlargest(n, t).index.tolist() +
     debt_to_market_table_temp.nsmallest(n, t).index.tolist() for t in debt_to_market_table.index],
    index=debt_to_market_table.index).shift().dropna()

coef = np.array([1] * n + [-1] * n) / (2 * n)

debt_to_market_return = pd.Series([
    returns_table.loc[t, debt_to_market_strategy.loc[t].values] @ coef
    for t in debt_to_market_strategy.index
], index=debt_to_market_strategy.index)

plt.figure()
plt.plot(debt_to_market_return.fillna(0).cumsum())
plt.ylabel('Monthly PNL')
plt.tight_layout()

1

print(f'sharpe={sharpe(debt_to_market_return):.2f}, win%={win_rate(debt_to_market_return):.2%}, mdd={maximum_drawdown(debt_to_market_return):.2%}')

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25

return_on_investment_table = pd.concat([STRATEGY_DATA[tic]['roi'] for tic in STRATEGY_DATA], axis=1)
return_on_investment_table.columns = STRATEGY_DATA.keys()
return_on_investment_table_temp = return_on_investment_table.T

n = round(1723 * 0.1)  # number of tickers we're gonna use in the strategy

returns_table = pd.concat([STRATEGY_DATA[tic]['px'] for tic in STRATEGY_DATA], axis=1)
returns_table.columns = STRATEGY_DATA.keys()

return_on_investment_strategy = pd.DataFrame(
    [return_on_investment_table_temp.nlargest(n, t).index.tolist() +
     return_on_investment_table_temp.nsmallest(n, t).index.tolist() for t in return_on_investment_table.index],
    index=return_on_investment_table.index).shift().dropna()

coef = np.array([1] * n + [-1] * n) / (2 * n)

return_on_investment_return = pd.Series([
    returns_table.loc[t, return_on_investment_strategy.loc[t].values] @ coef
    for t in return_on_investment_strategy.index
], index=return_on_investment_strategy.index)

plt.figure()
plt.plot(return_on_investment_return.fillna(0).cumsum())
plt.ylabel('Monthly PNL')
plt.tight_layout()

1

print(f'sharpe={sharpe(return_on_investment_return):.2f}, win%={win_rate(return_on_investment_return):.2%}, mdd={maximum_drawdown(return_on_investment_return):.2%}')

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25

price_to_earnings_table = pd.concat([STRATEGY_DATA[tic]['pe'] for tic in STRATEGY_DATA], axis=1)
price_to_earnings_table.columns = STRATEGY_DATA.keys()
price_to_earnings_table_temp = price_to_earnings_table.T

n = round(1723 * 0.1)  # number of tickers we're gonna use in the strategy

returns_table = pd.concat([STRATEGY_DATA[tic]['px'] for tic in STRATEGY_DATA], axis=1)
returns_table.columns = STRATEGY_DATA.keys()

price_to_earnings_strategy = pd.DataFrame(
    [price_to_earnings_table_temp.nlargest(n, t).index.tolist() +
     price_to_earnings_table_temp.nsmallest(n, t).index.tolist() for t in price_to_earnings_table.index],
    index=price_to_earnings_table.index).shift().dropna()

coef = np.array([1] * n + [-1] * n) / (2 * n)

price_to_earnings_return = pd.Series([
    returns_table.loc[t, price_to_earnings_strategy.loc[t].values] @ coef
    for t in price_to_earnings_strategy.index
], index=price_to_earnings_strategy.index)

plt.figure()
plt.plot(price_to_earnings_return.fillna(0).cumsum())
plt.ylabel('Monthly PNL')
plt.tight_layout()

 1
 2
 3
 4
 5
 6
 7
 8
 9
10

def add_return(x):
    x['return'] = x['px'].shift()
    return x

STRATEGY_DATA_update = {k: add_return(v) for k, v in STRATEGY_DATA.items()}

regression_table = pd.concat([df[['return', 'd2m', 'roi', 'pe']].dropna() for df in STRATEGY_DATA_update.values()])
regression_table = regression_table[(abs(regression_table) <= 1e6).all(axis=1)]
regression_table.columns = ['ret', 'd2m', 'roi', 'pe']
regression_table

1
2

result_regression = smf.ols('ret ~ pe + 0', data=regression_table).fit()
print(result_regression.summary())

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25

price_to_earnings_table = pd.concat([STRATEGY_DATA[tic]['pe'] for tic in STRATEGY_DATA], axis=1)
price_to_earnings_table.columns = STRATEGY_DATA.keys()
price_to_earnings_table_temp = price_to_earnings_table.T

n = round(1723 * 0.1)  # number of tickers we're gonna use in the strategy

returns_table = pd.concat([STRATEGY_DATA[tic]['px'] for tic in STRATEGY_DATA], axis=1)
returns_table.columns = STRATEGY_DATA.keys()

price_to_earnings_strategy = pd.DataFrame(
    [price_to_earnings_table_temp.nlargest(n, t).index.tolist() +
     price_to_earnings_table_temp.nsmallest(n, t).index.tolist() for t in price_to_earnings_table.index],
    index=price_to_earnings_table.index).shift().dropna()

coef = np.array([-1] * n + [1] * n) / (2 * n)

price_to_earnings_return = pd.Series([
    returns_table.loc[t, price_to_earnings_strategy.loc[t].values] @ coef
    for t in price_to_earnings_strategy.index
], index=price_to_earnings_strategy.index)

plt.figure()
plt.plot(price_to_earnings_return.fillna(0).cumsum())
plt.ylabel('Monthly PNL')
plt.tight_layout()

1

print(f'sharpe={sharpe(price_to_earnings_return):.2f}, win%={win_rate(price_to_earnings_return):.2%}, mdd={maximum_drawdown(price_to_earnings_return):.2%}')

1
2
3

debt_to_mkt_roi_table = pd.concat([df[['return', 'd2m', 'roi']].dropna() for df in STRATEGY_DATA_update.values()])
debt_to_mkt_roi_table = debt_to_mkt_roi_table[(abs(debt_to_mkt_roi_table) <= 1e6).all(axis=1)]
debt_to_mkt_roi_table

1
2

result = smf.ols('ret ~ d2m + roi + 0', data=debt_to_mkt_roi_table).fit()
print(result.summary())

1
2
3
4

coeffient = result.params
ret_hat = debt_to_mkt_roi_table[['d2m', 'roi']] @ coeffient
prediction = pd.concat([ret_hat, debt_to_mkt_roi_table['ret']], axis=1)
prediction.columns = ['ret_hat', 'ret']

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13

# ret_hat.plot.scatter(x='ret_hat', y='ret', data=prediction);

fig = plt.figure()
ax = fig.add_subplot(111)
ax.scatter(prediction.ret_hat, prediction.ret, s=1, c='k')
# Fmin_ETH_USD = train_ETH_USD.F.min()
# Fmax_ETH_USD = train_ETH_USD.F.max()
# ax.plot((Fmin_ETH_USD, Fmax_ETH_USD), (Fmin_ETH_USD * beta_ETH_USD, Fmax_ETH_USD * beta_ETH_USD), c='r')
plt.xlabel('ret_hat')
plt.ylabel('ret')
# plt.xlim(Fmin_ETH_USD, Fmax_ETH_USD)
plt.tight_layout()
plt.show()

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25

multi_ratio_table = coeffient['d2m'] * debt_to_market_table + coeffient['roi'] * return_on_investment_table
multi_ratio_table_temp = multi_ratio_table.T


n = round(1723 * 0.1)  # number of tickers we're gonna use in the strategy

returns_table = pd.concat([STRATEGY_DATA[tic]['px'] for tic in STRATEGY_DATA], axis=1)
returns_table.columns = STRATEGY_DATA.keys()

multi_ratio_strategy = pd.DataFrame(
    [multi_ratio_table_temp.nlargest(n, t).index.tolist() +
     multi_ratio_table_temp.nsmallest(n, t).index.tolist() for t in multi_ratio_table.index],
    index=multi_ratio_table.index).shift().dropna()

coef = np.array([1] * n + [-1] * n) / (2 * n)

multi_ratio_return = pd.Series([
    returns_table.loc[t, multi_ratio_strategy.loc[t].values] @ coef
    for t in multi_ratio_strategy.index
], index=multi_ratio_strategy.index)

plt.figure()
plt.plot(multi_ratio_return.fillna(0).cumsum())
plt.ylabel('Monthly PNL')
plt.tight_layout()

1

print(f'sharpe={sharpe(multi_ratio_return):.2f}, win%={win_rate(multi_ratio_return):.2%}, mdd={maximum_drawdown(multi_ratio_return):.2%}')

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29

multi_ratio_table = coeffient['d2m'] * debt_to_market_table + coeffient['roi'] * return_on_investment_table
multi_ratio_table_temp = multi_ratio_table.T


n = round(1723 * 0.1)  # number of tickers we're gonna use in the strategy
n -= n % 2

returns_table = pd.concat([STRATEGY_DATA[tic]['px'] for tic in STRATEGY_DATA], axis=1)
returns_table.columns = STRATEGY_DATA.keys()

multi_ratio_strategy = pd.DataFrame(
    [multi_ratio_table_temp.nlargest(n, t).index.tolist() +
     multi_ratio_table_temp.nsmallest(n, t).index.tolist() for t in multi_ratio_table.index],
    index=multi_ratio_table.index).shift().dropna()


coef = np.array(list(np.arange(n, 0, -1)) + list(np.arange(-n, 0, 1)))
coef = coef / abs(coef).sum()


multi_ratio_return = pd.Series([
    returns_table.loc[t, multi_ratio_strategy.loc[t].values] @ coef
    for t in multi_ratio_strategy.index
], index=multi_ratio_strategy.index)

plt.figure()
plt.plot(multi_ratio_return.fillna(0).cumsum())
plt.ylabel('Monthly PNL')
plt.tight_layout()

1

print(f'sharpe={sharpe(multi_ratio_return):.2f}, win%={win_rate(multi_ratio_return):.2%}, mdd={maximum_drawdown(multi_ratio_return):.2%}')