def load_internal_ticker_ids(self):
for ticker in commons.getHistSp500TickerList(commons.min_date,
commons.min_date, False):
for row in self.t_ticker_ids.read_where('ticker==' + "'" + ticker +
"'"):
self.ti_ticker_ids[row[1]] = row[0]
self.ti_ticker[row[0]] = row[1]
def __init__(self,refresh_data=False,reload_baseline=False,demo_scenario=True,quandlkey=''):
Quandl.ApiConfig.api_key=quandlkey
self.demo_scenario=demo_scenario
self.refresh_data=refresh_data
self.df1st_date=commons.data_sp500_1st_date
self.dfWikiSp500=commons.read_dataframe(commons.data_path+'WIKI_SP500.h5')
self.dfSector=commons.read_dataframe(commons.data_path+'SECTOR_SP500.h5')
self.dfSentiment=commons.read_dataframe(commons.data_path+'SENT_SP500.h5')
self.dfFundamentals=commons.read_dataframe(commons.data_path+'FUND_SP500.h5')
self.dfShortSell=commons.read_dataframe(commons.data_path+'SHORT_SP500.h5')
self.dfMarketcap=commons.read_dataframe(commons.data_path+'MARKETCAP.h5')
self.dfanb=commons.read_dataframe(commons.data_path+'anb.h5')
self.dfLastCalloff=commons.read_dataframe(commons.data_path+'last_calloff.h5')
#select columns with low, high, open and close
if commons.demo_scenario:
column_selection=list()
for ticker in commons.getHistSp500TickerList(1,1,False):
column_selection.append(ticker+'_Open')
column_selection.append(ticker+'_Close')
column_selection.append(ticker+'_Low')
column_selection.append(ticker+'_High')
else:
column_selection=list([])
for c in self.dfWikiSp500.columns:
if c[-4:] in ['Open', 'lose', 'High', '_Low']:
column_selection.append(c)
self.dfPrices=self.dfWikiSp500.ix[:,column_selection]
def getFundamentals(self):
if self.refresh_data==True:
startdate=commons.max_date['FUND_SP500']
enddate=commons.max_date['WIKI_SP500']
fundamentals=list(['_PB_ARQ','_EPSDIL_ARQ'])
items=list([])
for fund in fundamentals:
for ticker in commons.getHistSp500TickerList(commons.date_index_internal[commons.max_date['FUND_SP500']],commons.date_index_internal[commons.max_date['WIKI_SP500']]):
items.append('SF1/'+str(ticker)+str(fund))
for i in range(0,len(items)):
if i%100==5 or i==(len(items)-1):
if i==5:
itemList=items[0:i]
else:
itemList=items[i-100:i]
df=pd.DataFrame([])
df=Quandl.get(itemList,start_date=startdate,end_date=enddate)
columns=list([])
for x in df.columns:
x=str(x).replace(' - ', '_')
x=x.replace('SF1/','')
x=x.replace('_Value','')
columns.append(x)
df.columns=columns
self.dfFundamentals=self.processResults(df,self.dfFundamentals,'FUND_SP500','ffill')
self.dfLastCalloff.ix[dt.datetime.today(),'fundamentals']=1
self.dfLastCalloff.to_hdf(commons.data_path+'last_calloff.h5','table',mode='w')
print 'Fundamentals data refreshed'
def checkDemoData(self,frame):
columns=list()
if not commons.demo_scenario:
return getattr(self,frame)
elif frame=='dfSector':
columns.append('ARCA_VOX'+'_Low')
columns.append('ARCA_VOX'+'_High')
columns.append('ARCA_VOX'+'_Open')
columns.append('ARCA_VOX'+'_Close')
return getattr(self,frame).ix[:,columns]
elif frame=='dfFundamentals':
for ticker in commons.getHistSp500TickerList(1,1,False):
columns.append(ticker+'_EPSDIL_ARQ')
columns.append(ticker+'_PB_ARQ')
return getattr(self,frame).ix[:,columns]
elif frame=='dfShortSell':
for ticker in commons.getHistSp500TickerList(1,1,False):
columns.append('FNSQ_'+ticker+'_ShortVolume')
columns.append('FNSQ_'+ticker+'_ShortExemptVolume')
columns.append('FNSQ_'+ticker+'_TotalVolume')
columns.append('FNYX_'+ticker+'_ShortVolume')
columns.append('FNYX_'+ticker+'_ShortExemptVolume')
columns.append('FNYX_'+ticker+'_TotalVolume')
return getattr(self,frame).ix[:,columns]
elif frame=='dfMarketcap':
for ticker in commons.getHistSp500TickerList(1,1,False):
columns.append(ticker)
return getattr(self,frame).ix[:,columns]
elif frame=='dfanb':
for ticker in commons.getHistSp500TickerList(1,1,False):
columns.append('B_'+ticker+'_Open')
columns.append('B_'+ticker+'_Close')
columns.append('B_'+ticker+'_Low')
columns.append('B_'+ticker+'_High')
columns.append('A_'+ticker+'_Open')
columns.append('A_'+ticker+'_Close')
columns.append('A_'+ticker+'_Low')
columns.append('A_'+ticker+'_High')
return getattr(self,frame).ix[:,columns]
elif frame=='dfSentiment':
return getattr(self,frame)
def load_ticker_ids(self):
all_rows = self.t_ticker_ids.read()
for ticker in commons.getHistSp500TickerList(commons.min_date,
commons.min_date, False):
ticker_id = self.t_ticker_ids.read_where('ticker==' + "'" +
ticker + "'")
if not any(ticker_id):
if any(all_rows):
new_id = self.t_ticker_ids.nrows + 1
else:
new_id = 1
self.t_ticker_ids.row['ticker'] = ticker
self.t_ticker_ids.row['id'] = new_id
self.t_ticker_ids.row.append()
self.t_ticker_ids.flush()
all_rows = self.t_ticker_ids.read()
print 'Ticker IDs table initialized.'
def getShortSell(self):
if self.refresh_data==True:
startdate=commons.max_date['SHORT_SP500']
enddate=commons.max_date['WIKI_SP500']
short_sell=list(['FINRA/FNSQ_','FINRA/FNYX_'])
for s in short_sell:
items=list()
for ticker in commons.getHistSp500TickerList(commons.date_index_internal[startdate],commons.date_index_internal[enddate]):
items.append(s+ticker)
df=pd.DataFrame()
df=Quandl.get(items,start_date=startdate,end_date=enddate)
columns=list([])
for x in df.columns:
x=str(x).replace(' - ', '_')
x=x.replace('FINRA/','')
columns.append(x)
df.columns=columns
self.dfShortSell=self.processResults(df,self.dfShortSell,'SHORT_SP500')
print 'Short sell data refreshed'
import commons
from database import db
import quandl as Quandl
import csv
import pandas as pd
Quandl.ApiConfig.api_key = 'QWQMKc-NYsoYgtfadPZs'
dba = db('', 'r+')
t_log = dba.t_log
subList = list()
for t in commons.getHistSp500TickerList(1, 1, False):
subList.append('WIKI/' + t)
df1 = Quandl.get(subList,
start_date=commons.min_date,
end_date=commons.max_date['WIKI_SP500'])
columns = list([])
for x in df1.columns:
x = str(x).replace('WIKI/', '')
x = str(x).replace(' - ', '_')
columns.append(x)
df1.columns = columns
dfIndex = list()
for dix in range(commons.date_index_internal[commons.min_date],
commons.date_index_internal[commons.max_date['WIKI_SP500']]):
dfIndex.append(commons.date_index_external[dix])
df2 = pd.DataFrame(index=dfIndex)