def get_all_symbols():
sp500 = finsymbols.get_sp500_symbols()
df_sp500=pd.DataFrame(sp500)
df_sp500['exchange']='SPY500'
amex_symbols=finsymbols.get_amex_symbols()
df_amex=pd.DataFrame(amex_symbols)
df_amex['exchange']='AMEX'
nyse_symbols=finsymbols.get_nyse_symbols()
df_nyse=pd.DataFrame(nyse_symbols)
df_nyse['exchange']='NYSE'
nasdaq_symbols=finsymbols.get_nasdaq_symbols()
df_nasdaq=pd.DataFrame(nasdaq_symbols)
df_nasdaq['exchange']='NASDAQ'
result=pd.concat([df_sp500,df_amex,df_nyse,df_nasdaq])
dbdao.save_dataframe(result, "df_symbol_new")
def get_csv(url):
try:
#url=url.replace('html','csv')
base_url="http://biz.yahoo.com/p/"
url=base_url+url
print url
page = urllib2.urlopen(url,timeout = 10)
html = page.read()
soup = BeautifulSoup(html)
sector=""
industry=""
list_symbol=[]
for y in soup.findAll('table' ,attrs={"bgcolor" : "dcdcdc"}):
for x in y.findAll('td',attrs={"bgcolor" : "ffffee"}):
if("Sector:" in x.text ):
sector=x.text.replace("Sector:","")
elif("Industry:" in x.text ):
industry=x.text.replace("Industry:","").replace("(More Info)","")
else:
name= x.text.replace("\n","").replace("\r","")
items_symbol= re.findall('([\w\s.]+)',name)
if(items_symbol is not None and len(items_symbol) >=2):
symbol=items_symbol[1]
name=items_symbol[0]
print name
print symbol
list_symbol.append({"name":name,"symbol":symbol})
df=pd.DataFrame(list_symbol)
df.set_index('symbol',inplace=True)
df['sector']=sector
df['industry']=industry
print df
dbdao.save_dataframe(df, "df_symbol_latest")
except Exception ,ex:
logger.error(ex)
import pandas as pd
from dao2 import dbdao
df = pd.read_csv('data/synopsis/synopsis_rules.csv')
print df
df.set_index("rule_id", inplace=True)
dbdao.save_dataframe(df, "synopsis_rule")
from os.path import isfile, join
from dao2 import dbdao
from pprint import pprint
df= pd.read_csv('data/431conameu.csv',skiprows=2,header=None,names=["name","price_chg","market_Cap","pe","roe","yield","DebttoEquity","PricetoBook","NetProfitMargin","PriceToFreeCashFlow"])
print df[0:2]
dbdao.save_dataframe(df,'df_test')
exit()
path='data/etf/'
onlyfiles = [ f for f in listdir(path) if isfile(join(path,f)) ]
print onlyfiles
def load_data(path,type):
df= pd.read_csv(path,header=None,names=['symbol'])
df['type']=type
df.set_index('symbol',inplace=True)
return df
import pandas as pd
from dao2 import dbdao
df = pd.read_csv("data/synopsis/synopsis_rules.csv")
print df
df.set_index("rule_id", inplace=True)
dbdao.save_dataframe(df, "synopsis_rule")
from bl import Trend_types
df = dbdao.get_data_db_frame(
"select symbol,short_trend,inter_trend,long_trend,rsi_value from df_technical where symbol='MSFT';"
)
def sypnosis(x):
print x
rsi_value = x['rsi_value']
symbol = x['symbol']
short_trend = Trend_types.getString(x['short_trend'])
inter_trend = Trend_types.getString(x['inter_trend'])
long_trend = Trend_types.getString(x['long_trend'])
current_situation = ""
current_situation = "%s short term is %s , intermediate term is %s , and long term is %s , %s is %s" % (
symbol, short_trend, inter_trend, long_trend, symbol, rsi_value)
#return short_trend,inter_trend,long_trend,current_situation
return pd.Series({
'short_trend_text': short_trend,
'inter_trend_text': inter_trend,
'long_trend_text': long_trend,
'current_situation': current_situation
})
df = df.merge(df.apply(sypnosis, axis=1), left_index=True, right_index=True)
print df
dbdao.save_dataframe(df, "df_synopsis")
from os.path import isfile, join
from dao2 import dbdao
from pprint import pprint
df = pd.read_csv('data/431conameu.csv',
skiprows=2,
header=None,
names=[
"name", "price_chg", "market_Cap", "pe", "roe", "yield",
"DebttoEquity", "PricetoBook", "NetProfitMargin",
"PriceToFreeCashFlow"
])
print df[0:2]
dbdao.save_dataframe(df, 'df_test')
exit()
path = 'data/etf/'
onlyfiles = [f for f in listdir(path) if isfile(join(path, f))]
print onlyfiles
def load_data(path, type):
df = pd.read_csv(path, header=None, names=['symbol'])
df['type'] = type
df.set_index('symbol', inplace=True)
return df
import pandas as pd
from dao2 import dbdao
df = pd.read_csv('data/etf/assets.csv')
list_assets = df.columns.values
df_net = pd.DataFrame()
frames = []
for asset in list_assets:
df_temp = pd.DataFrame()
df_temp['symbol'] = df[asset]
df_temp['asset'] = asset
df_temp = df_temp.dropna()
df_temp = df_temp.set_index('symbol')
frames.append(df_temp)
df_net = pd.concat(frames)
print df_net
dbdao.save_dataframe(df_net, "df_etf_load")
from dao2 import dbdao
import pandas as pd
from bl import Trend_types
df= dbdao.get_data_db_frame("select symbol,short_trend,inter_trend,long_trend,rsi_value from df_technical where symbol='MSFT';")
def sypnosis(x):
print x
rsi_value=x['rsi_value']
symbol=x['symbol']
short_trend= Trend_types.getString(x['short_trend'])
inter_trend=Trend_types.getString(x['inter_trend'])
long_trend=Trend_types.getString(x['long_trend'])
current_situation=""
current_situation= "%s short term is %s , intermediate term is %s , and long term is %s , %s is %s" %(symbol,short_trend,inter_trend,long_trend,symbol,rsi_value)
#return short_trend,inter_trend,long_trend,current_situation
return pd.Series({'short_trend_text': short_trend, 'inter_trend_text': inter_trend,'long_trend_text':long_trend,'current_situation': current_situation})
df=df.merge(df.apply(sypnosis,axis=1),left_index=True, right_index=True)
print df
dbdao.save_dataframe(df, "df_synopsis")
import pandas as pd
from dao2 import dbdao
df=pd.read_csv('data/etf/assets.csv')
list_assets=df.columns.values
df_net=pd.DataFrame()
frames=[]
for asset in list_assets:
df_temp=pd.DataFrame()
df_temp['symbol']=df[asset]
df_temp['asset']=asset
df_temp=df_temp.dropna()
df_temp=df_temp.set_index('symbol')
frames.append(df_temp)
df_net=pd.concat(frames)
print df_net
dbdao.save_dataframe(df_net, "df_etf_load")
def create_sector_industry_data(list_symbol):
#list_symbol = dbdao.get_symbols_list_limit(start,end)
list_symbol_data=[]
for symbol in list_symbol:
try:
list_symbol_data.append(getsymbol_sector_industry(symbol))
except Exception,ex:
logger.error(ex)
df=pd.DataFrame(list_symbol_data)
df.set_index("symbol",inplace=True)
dbdao.save_dataframe(df, "df_symbol_sector_industry")
print df
dbdao.execute_query(["delete from df_symbol_sector_industry"])
list_symbol = dbdao.get_symbols_list_missing()
create_sector_industry_data(list_symbol)
sql_insert=""" insert into list_symbol(symbol,companyname,sectorid,industryid)
select t1.symbol,t1.name,s1.id,i1.industryid from df_symbol_sector_industry t1
left join sectors s1 on t1.sector=s1.name
left join industries i1 on t1.industry=i1.industryname"""
dbdao.execute_query([sql_insert])
# import sys
# print sys.argv
# start,end=sys.argv[1],sys.argv[2]
# print start,end
