def __init__(self, df):
self.df = df.reset_index()
dict_df = pd.read_sql(
'''SELECT stockid,symbol,sector FROM stockTable''', conn)
self.symbol_dict = dict_builder(dict_df['stockid'].tolist(),
dict_df['symbol'].tolist())
self.sector_dict = dict_builder(dict_df['stockid'].tolist(),
dict_df['sector'].tolist())
self.map_()
def __init__(self, df):
self.df = df.reset_index()
df2 = pd.read_sql('SELECT symbol,stockid,sector from stockTable', conn)
sector_dict = dict_builder(df2['stockid'].tolist(),
df2['sector'].tolist())
self.df['symbol'] = self.df['stockid'].map(
dict_builder(df2['stockid'].tolist(), df2['symbol'].tolist()))
self.df['sector'] = self.df['stockid'].map(sector_dict)
self.df = self.df[self.df['sector'] != 'n/a']
try:
self.date_list = self.df['Date'].unique()
except:
self.date_list = self.df['date'].unique()
self._run()
def __init__(self, df):
self.df = df.reset_index()
df2 = pd.read_sql('''SELECT stockid,symbol from stockTable''', conn)
symbol_dict = dict_builder(key_list=df2['stockid'].tolist(),
value_list=df2['symbol'].tolist())
self.df['symbol'] = self.df['stockid'].map(symbol_dict)
self.date_list = self.df['Date'].unique()
self._run()
def _to_db(self):
self.id_dict = dict_builder(self.df1['symbol'].tolist(),
self.df1['stockid'].tolist())
self.df['stockid'] = self.df['symbol'].map(self.id_dict)
self.df = self.df.set_index('stockid')
self.df = self.df[['earningsdate']]
c.execute(
'''CREATE TABLE IF NOT EXISTS earningsTable (stockid INTEGER,earningsdate TIMESTAMP)'''
)
self.df.to_sql('earningsTable',
conn,
if_exists='replace',
index=True,
index_label='stockid')
def __init__(self, beta=250, batch=3000):
df = pd.read_sql(
'''SELECT DISTINCT symbol,ipoyear FROM sdt JOIN stockTable ON sdt.stockid = stockTable.stockid''',
conn)
self.ticker_list, self.ipoyear = df['symbol'].tolist(
), df['ipoyear'].tolist()
self.dict = dict_builder(self.ticker_list, self.ipoyear)
self.spx = web.DataReader('^GSPC', 'yahoo', datetime(1975, 1, 1),
datetime.today())
self.spx = self.spx.rename(columns={'Close': 'SPXClose'})
self.spx = self.spx[['SPXClose']]
self.beta = beta
self.batch = batch
self._beta()
def _run(self):
final_df = pd.DataFrame()
for z in self.date_list:
try:
df = self.df[self.df['Date'] == z]
except:
df = self.df[self.df['date'] == z]
z = str(z)
z = z.split('T')[0]
z = datetime.strptime(z, '%Y-%m-%d').date()
date = '{dt.month}/{dt.day}/{dt.year}'.format(dt=z)
symbol_list = df['symbol'].tolist()
usid_list = df['USID'].tolist()
usid_symbol_dict = dict_builder(key_list=symbol_list,
value_list=usid_list)
symbol = str(symbol_list).strip('[]').strip(' ').replace(
' ', '').replace("'", "")
Fundamentals = 'MarketCapitalization,Beta,PriceToBook'
url = '''https://factsetfundamentals.xignite.com/xFactSetFundamentals.json/GetFundamentals?IdentifierType=Symbol&Identifiers=%s&FundamentalTypes=%s&AsOfDate=%s&ReportType=Quarterly&ExcludeRestated=false&UpdatedSince=&_token=''' % (
symbol, Fundamentals, date)
r = urllib.request.urlopen(url + token)
pre_data = json.loads(r.read().decode(r.info().get_param('charset')
or 'utf-8'))[0:]
big_df = pd.DataFrame()
for j in range(0, len(pre_data)):
try:
data = pre_data[j]
a = {'symbol': [data['Company']['Symbol']]}
a['Date'] = data['FundamentalsSets'][0]['AsOfDate']
for i in range(
0,
len(data['FundamentalsSets'][0]['Fundamentals'])):
a[data['FundamentalsSets'][0]['Fundamentals'][i]
['Type']] = [
data['FundamentalsSets'][0]['Fundamentals'][i]
['Value']
]
df = pd.DataFrame.from_dict(a)
df['USID'] = df['symbol'].map(usid_symbol_dict)
df = df.set_index('USID')
df = df[['MarketCapitalization', 'PriceToBook', 'Beta']]
df = df.apply(pd.to_numeric)
df = df.replace('', np.nan)
big_df = big_df.append(df)
except:
pass
final_df = final_df.append(big_df)
self.final_df = final_df.dropna(how='any')
def day_of(self):
self.df_trade = pd.DataFrame()
for i in self.df['symbol'].unique():
mkt_cap, pe, beta, inst_own = fetchExtra(i)
try:
if str(mkt_cap).find('B') > 0:
self.df_trade = self.df_trade.append(
pd.DataFrame({
'symbol': [i],
'mktcap': [mkt_cap],
'pe': [pe],
'beta': [beta],
'inst_own': [inst_own]
}))
elif str(mkt_cap).find('B') < 0:
if int(re.findall(r'\d+', str(mkt_cap))[0]) > 500:
self.df_trade = self.df_trade.append(
pd.DataFrame({
'symbol': [i],
'mktcap': [mkt_cap],
'pe': [pe],
'beta': [beta],
'inst_own': [inst_own]
}))
else:
continue
except:
continue
self.symbol_usid_dict = dict_builder(self.df['symbol'].tolist(),
self.df['USID'].tolist())
self.df_trade['USID'] = self.df_trade['symbol'].map(
self.symbol_usid_dict)
self.df_trade = self.df_trade.set_index('USID')
# self.df_trade.to_sql('preTrade',conn,if_exists='append',index=True,index_label='USID')
self.df = self.df[self.df['symbol'].isin(
self.df_trade['symbol'].tolist())]
self.df['Open'] = self.df.apply(fetchOpen, axis=1)
self.df['Gap'] = (self.df['Open'] / self.df['price']) - 1
self.df['DayDelta'], self.df['SectorDelta'], self.df[
'MktDelta'] = None, None, None
self.df = self.df[self.columns]
def _run(self):
final_df = pd.DataFrame()
count = 0
for z in self.date_list:
count += 1
df = self.df[self.df['Date'] == z]
z = str(z)
z = z.split('T')[0]
z = datetime.strptime(z, '%Y-%m-%d').date()
z2 = z + BDay(6)
usid_symbol_dict = dict_builder(key_list=df['symbol'].tolist(),
value_list=df['USID'].tolist())
date_df = pd.DataFrame()
for i in df['symbol'].unique():
try:
pxdf = web.DataReader(i, 'yahoo', z, z2)
pxdf['NDR'] = pxdf['Close'] / pxdf['Close'].shift() - 1
pxdf['N3R'] = pxdf['Close'] / pxdf['Close'].shift(3) - 1
pxdf['N5R'] = pxdf['Close'] / pxdf['Close'].shift(5) - 1
y = pd.DataFrame({
'symbol': [i],
'ndr': [pxdf.ix[1]['NDR']],
'n3r': [pxdf.ix[4]['N3R']],
'n5r': [pxdf.ix[6]['N5R']],
'ndv': [pxdf.ix[1]['Volume']]
})
y['USID'] = y.symbol.map(usid_symbol_dict)
y = y.set_index('USID')
date_df = date_df.append(y)
except:
pass
final_df = final_df.append(date_df)
final_df = final_df[['ndr', 'n3r', 'n5r', 'ndv']]
# final_df = self.df.merge(final_df,how='left',left_index=True,right_index=True)
final_df.to_sql('n3rTable',
conn,
if_exists='append',
index=True,
index_label='USID')
def _symbol_dict(self):
dict_df = pd.read_sql('''SELECT stockid,symbol FROM stockTable''',conn)
self.symbol_dict = dict_builder(dict_df['symbol'].tolist(),dict_df['stockid'].tolist())