forked from xinhe97/EquityCharsDaily
-
Notifications
You must be signed in to change notification settings - Fork 0
/
beta_daily.py
161 lines (138 loc) · 6.23 KB
/
beta_daily.py
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
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
import pandas as pd
import numpy as np
import datetime as dt
import wrds
from dateutil.relativedelta import *
from pandas.tseries.offsets import *
import datetime
import pyarrow.feather as feather
import multiprocessing as mp
###################
# Connect to WRDS #
###################
conn = wrds.Connection()
# CRSP Block
crsp = conn.raw_sql("""
select a.permno, a.date, a.ret, (a.ret - b.rf) as exret, b.mktrf, b.smb, b.hml
from crsp.dsf as a
left join ff.factors_daily as b
on a.date=b.date
where a.date > '01/01/2014'
""")
crsp = crsp.dropna()
# sort variables by permno and date
crsp = crsp.sort_values(by=['permno', 'date'])
# change variable format to int
crsp['permno'] = crsp['permno'].astype(int)
# Line up date to be end of month
crsp['date'] = pd.to_datetime(crsp['date'])
# find the closest trading day to the end of the month
crsp['monthend'] = crsp['date'] + MonthEnd(0)
crsp['date_diff'] = crsp['monthend'] - crsp['date']
date_temp = crsp.groupby(['permno', 'monthend'])['date_diff'].min()
date_temp = pd.DataFrame(date_temp) # convert Series to DataFrame
date_temp.reset_index(inplace=True)
date_temp.rename(columns={'date_diff': 'min_diff'}, inplace=True)
crsp = pd.merge(crsp, date_temp, how='left', on=['permno', 'monthend'])
crsp['sig'] = np.where(crsp['date_diff'] == crsp['min_diff'], 1, np.nan)
# label every date of month end
crsp['month_count'] = crsp[crsp['sig'] == 1].groupby(['permno']).cumcount()
# label numbers of months for a firm
month_num = crsp[crsp['sig'] == 1].groupby(['permno'])['month_count'].tail(1)
month_num = month_num.astype(int)
month_num = month_num.reset_index(drop=True)
# mark the number of each month to each day of this month
crsp['month_count'] = crsp.groupby(['permno'])['month_count'].fillna(method='bfill')
# crate a firm list
df_firm = crsp.drop_duplicates(['permno'])
df_firm = df_firm[['permno']]
df_firm['permno'] = df_firm['permno'].astype(int)
df_firm = df_firm.reset_index(drop=True)
df_firm = df_firm.reset_index()
df_firm = df_firm.rename(columns={'index': 'count'})
df_firm['month_num'] = month_num
# daily number
crsp['day_count'] = crsp.groupby(['permno']).cumcount()
day_num = crsp.groupby(['permno'])['day_count'].tail(1)
day_num = day_num.astype(int)
day_num = day_num.reset_index(drop=True)
df_firm['day_num'] = day_num
######################
# Calculate the beta #
######################
def get_beta_daily(df, firm_list):
"""
:param df: stock dataframe
:param firm_list: list of firms matching stock dataframe
:return: dataframe with variance of residual
"""
for firm, count, prog in zip(firm_list['permno'], firm_list['day_num'], range(firm_list['permno'].count()+1)):
prog = prog + 1
print('processing permno %s' % firm, '/', 'finished', '%.2f%%' % ((prog/firm_list['permno'].count())*100))
for i in range(count + 1):
# if you want to change the rolling window, please change here: i - 59 means 60 days is a window.
temp = df[(df['permno'] == firm) & (i - 59 <= df['day_count']) & (df['day_count'] <= i)]
# if observations in less than 60 days, we drop the characteristic of this month
if temp['permno'].count() < 60:
pass
else:
rolling_window = temp['permno'].count()
index = temp.tail(1).index
X = np.mat(temp[['mktrf']])
Y = np.mat(temp[['exret']])
ones = np.mat(np.ones(rolling_window)).T
M = np.identity(rolling_window) - ones.dot((ones.T.dot(ones)).I).dot(ones.T)
beta = (X.T.dot(M).dot(X)).I.dot((X.T.dot(M).dot(Y)))
df.loc[index, 'beta'] = beta
return df
def sub_df(start, end, step):
"""
:param start: the quantile to start cutting, usually it should be 0
:param end: the quantile to end cutting, usually it should be 1
:param step: quantile step
:return: a dictionary including all the 'firm_list' dataframe and 'stock data' dataframe
"""
# we use dict to store different sub dataframe
temp = {}
for i, h in zip(np.arange(start, end, step), range(int((end-start)/step))):
print('processing splitting dataframe:', round(i, 2), 'to', round(i + step, 2))
if i == 0: # to get the left point
temp['firm' + str(h)] = df_firm[df_firm['count'] <= df_firm['count'].quantile(i + step)]
temp['crsp' + str(h)] = pd.merge(crsp, temp['firm' + str(h)], how='left',
on='permno').dropna(subset=['count'])
else:
temp['firm' + str(h)] = df_firm[(df_firm['count'].quantile(i) < df_firm['count']) & (
df_firm['count'] <= df_firm['count'].quantile(i + step))]
temp['crsp' + str(h)] = pd.merge(crsp, temp['firm' + str(h)], how='left',
on='permno').dropna(subset=['count'])
return temp
def main(start, end, step):
"""
:param start: the quantile to start cutting, usually it should be 0
:param end: the quantile to end cutting, usually it should be 1
:param step: quantile step
:return: a dataframe with calculated variance of residual
"""
df = sub_df(start, end, step)
pool = mp.Pool()
p_dict = {}
for i in range(int((end-start)/step)):
p_dict['p' + str(i)] = pool.apply_async(get_beta_daily, (df['crsp%s' % i], df['firm%s' % i],))
pool.close()
pool.join()
result = pd.DataFrame()
print('processing pd.concat')
for h in range(int((end-start)/step)):
result = pd.concat([result, p_dict['p%s' % h].get()])
return result
# calculate variance of residual through rolling window
# Note: please split dataframe according to your CPU situation. For example, we split dataframe to (1-0)/0.05 = 20 sub
# dataframes here, so the function will use 20 cores to calculate variance of residual.
if __name__ == '__main__':
crsp_out = main(0, 1, 0.05)
# process dataframe
crsp_out = crsp_out.dropna(subset=['beta']) # drop NA due to rolling
crsp_out = crsp_out.reset_index(drop=True)
crsp_out = crsp_out[['permno', 'date', 'beta']]
with open('beta_daily.feather', 'wb') as f:
feather.write_feather(crsp_out, f)