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)