def get_holdings(symbol):
url = 'http://finance.yahoo.com/q/hl?s=' + symbol + '+Holdings'
print url
page = urllib2.urlopen(url, timeout=10)
soup = BeautifulSoup(page)
all_tables = soup.findAll('table', attrs={"class": "yfnc_tableout1"})
list_data = []
if (len(all_tables) > 1):
holdings = all_tables[0].findAll('tr')
for holding in holdings[1:]:
#print holding
items = holding.findAll('td', attrs={"class": "yfnc_tabledata1"})
if (len(items) >= 3):
data = {
"symbol": symbol,
"holding_company": items[0].text,
"holding_symbol": items[1].text,
"holding_pct": items[2].text
}
#print data
list_data.append(data)
#
df = pd.DataFrame(list_data)
df.set_index("symbol", inplace=True)
print df
dbdao.save_dataframe(df, "symbol_holdings")
def saveQuote(symbols):
if not isinstance(symbols, list):
symbols = [symbols]
request = str.join('', keys)
#data = dict(zip(header,[[] for i in range(len(header))]))
list_data = []
urlStr = 'http://finance.yahoo.com/d/quotes.csv?s=%s&f=%s' % (str.join(
'+', symbols), request)
print urlStr
#logger.info(urlStr)
# try:
lines = urllib2.urlopen(urlStr, timeout=10).readlines()
print lines
for line in lines:
#print line
fields = line.strip().split(',')
data = {}
#print fields
if (len(fields) > 27):
fields[26] = fields[26] + fields[27]
for i, header_name in enumerate(header):
data[header_name] = parseStr(fields[i])
list_data.append(data)
df = pandas.DataFrame(list_data)
df = df.fillna(0)
dbdao.save_dataframe(df, 'yahoo_live_symbol')
def create_dataframe(path_dir,index,type,table_name):
for root, dirs, files in os.walk(path_dir):
for file in files:
if file.endswith(".csv"):
try:
print(os.path.join(root, file))
df =pd.read_csv(os.path.join(root, file),sep="^")
df['type']=type
df.set_index(index,inplace=True)
dbdao.save_dataframe(df, table_name)
except Exception,ex:
print ex
def create_dataframe(path_dir, index, type, table_name):
for root, dirs, files in os.walk(path_dir):
for file in files:
if file.endswith(".csv"):
try:
print(os.path.join(root, file))
df = pd.read_csv(os.path.join(root, file), sep="^")
df['type'] = type
df.set_index(index, inplace=True)
dbdao.save_dataframe(df, table_name)
except Exception, ex:
print ex
def get_holdings(symbol):
url='http://finance.yahoo.com/q/hl?s='+symbol+'+Holdings'
print url
page=urllib2.urlopen(url,timeout=10)
soup=BeautifulSoup(page)
all_tables=soup.findAll('table',attrs={"class":"yfnc_tableout1"})
list_data=[]
if(len(all_tables)>1):
holdings= all_tables[0].findAll('tr')
for holding in holdings[1:]:
#print holding
items= holding.findAll('td',attrs={"class":"yfnc_tabledata1"})
if(len(items)>=3):
data = {"symbol":symbol,"holding_company":items[0].text,"holding_symbol":items[1].text,"holding_pct":items[2].text}
#print data
list_data.append(data)
#
df=pd.DataFrame(list_data)
df.set_index("symbol",inplace=True)
print df
dbdao.save_dataframe(df, "symbol_holdings")
print url
page = urllib2.urlopen(url,timeout = 10)
html = page.read().replace("//","").strip()
data = json.loads(html)
df=pd.DataFrame(data)
df.set_index('t',inplace=True)
return df
dbdao.execute_query(['delete from google_live_indices_symbol','delete from google_live_symbol'])
list_symbol=dbdao.get_indices_symbols_list()
print list_symbol
df=getgoogledata(list_symbol)
dbdao.save_dataframe(df, "google_live_indices_symbol")
list_symbol=dbdao.get_symbols_list()
for i in range(0, len(list_symbol), 40):
try:
chunk = list_symbol[i:i + 40]
df=getgoogledata(chunk)
dbdao.save_dataframe(df, "google_live_symbol")
logger.info( chunk)
except Exception,ex:
logger.error(ex)
sql_update=""" update live_symbol t1 , google_live_symbol t2
set price_change=t2.c_fix,
change_pct=cp_fix,
#url="http://stockcharts.com/freecharts/adjusthist.php?search=*&day=-120"
headers = { 'User-Agent' : 'Mozilla/5.0' }
req = urllib2.Request(url, None, headers)
page = urllib2.urlopen(req,timeout=10)
# print page.read()
# exit()
soup=BeautifulSoup(page)
list_symbol=[]
all_tables=soup.findAll('table')
for table in all_tables:
for tr in table.findAll('tr'):
count=0
tds=tr.findAll('td')
dict_symbol={}
if(len(tds)>=2):
dict_symbol.update({"symbol":tds[2].text})
dict_symbol.update({"date":tds[1].text})
list_symbol.append(dict_symbol)
df= pd.DataFrame(list_symbol)
dbdao.save_dataframe(df, "df_splits")
#dataimport.specificimport(list_symbol)
import datetime
from dateutil.relativedelta import relativedelta
import pandas as pd
from util import loglib, alert_constants
from util import constants
from bl import crossover_manager, rsi_manager, alert_manager, trend_manager
from dao import mongodao, dbdao
import math
df = pd.read_csv("data/remove_etf.txt")
print df
dbdao.save_dataframe(df, "df_removeetf")
exit()
end_date_time = datetime.datetime.now() # mysqldb.get_maxdate()[0]
start_date_time = end_date_time - relativedelta(
days=constants.DAYS_FOR_TECHNICALS)
df_mkt = mongodao.getSymbolDataWithSymbol("SPY", start_date_time,
end_date_time)
symbol = "MSFT"
df_symbol = mongodao.getSymbolDataWithSymbol(symbol, start_date_time,
end_date_time)
df_symbol_close = df_symbol[['close']]
def calculate_technical(df_symbol, symbol, df_mkt, start_date_time,
end_date_time, hist_dates, days_back):
#list_drop_cloumns = [ 'open', 'high','low','volume']
df_symbol_close = df_symbol[['close']]
df_mkt_close = df_mkt[['close']]
mom = abstract.MOM(df_symbol_close, timeperiod=5)
df_merged = abstract.MACD(df_symbol_close,
fastperiod=12,
slowperiod=26,
signalperiod=9)
#df_merged=macd.apply(np.round)
df_std = abstract.STDDEV(df_symbol_close.pct_change(), timeperiod=100)
df_merged['stddev'] = df_std
df_merged['volatility'] = df_std * 100 * math.sqrt(252)
rsi = abstract.RSI(df_symbol_close).round(2)
sma20 = abstract.SMA(df_symbol_close, timeperiod=20).round(2)
sma100 = abstract.SMA(df_symbol_close, timeperiod=100).round(2)
sma200 = abstract.SMA(df_symbol_close, timeperiod=200).round(2)
sma3 = abstract.SMA(df_symbol_close, timeperiod=3).round(2)
sma5 = abstract.SMA(df_symbol_close, timeperiod=5).round(2)
sma9 = abstract.SMA(df_symbol_close, timeperiod=9).round(2)
sma13 = abstract.SMA(df_symbol_close, timeperiod=13).round(2)
sma25 = abstract.SMA(df_symbol_close, timeperiod=25).round(2)
sma50 = abstract.SMA(df_symbol_close, timeperiod=50).round(2)
sma90 = abstract.SMA(df_symbol_close, timeperiod=90).round(2)
sma36 = abstract.SMA(df_symbol_close, timeperiod=36).round(2)
sma150 = abstract.SMA(df_symbol_close, timeperiod=150).round(2)
df_merged['mom'] = mom
df_merged['sma20'] = sma20
df_merged['sma50'] = sma50
df_merged['sma100'] = sma100
df_merged['sma200'] = sma200
df_merged['rsi'] = rsi
df_merged['close'] = df_symbol['close']
df_merged['open'] = df_symbol['open']
df_merged['low'] = df_symbol['low']
df_merged['high'] = df_symbol['high']
df_merged['volume'] = df_symbol['volume']
df_merged['sma_volume_6month'] = pd.rolling_mean(df_merged['volume'],
window=120).round(2)
df_merged = df_merged.dropna()
df_merged['symbol'] = symbol
df_merged['rsi_value'] = df_merged['rsi'].apply(
rsi_manager.calculate_rsi_values)
df_merged['sma3'] = sma3
df_merged['sma5'] = sma5
df_merged['sma9'] = sma9
df_merged['sma13'] = sma13
df_merged['sma20'] = sma20
df_merged['sma25'] = sma25
df_merged['sma50'] = sma50
df_merged['sma90'] = sma90
df_merged['sma36'] = sma36
df_merged['sma150'] = sma150
df_merged['Relative_strength'] = relative_strength(
df_symbol_close, df_mkt_close, symbol)['Relative_strength']
df_merged['stdabove'] = df_merged.apply(calculate_stdabove, axis=1)
df_merged['date'] = df_merged.index
df_res = df_symbol.apply(calc_res, axis=1)
df_merged = pd.concat([df_merged, df_res], axis=1)
df_rating = rating_manager.calc_rating_history(df_merged, days_back,
symbol)
df_trends = df_merged.apply(
lambda row: trend_manager.trend_calculation(row), axis=1)
df_merged = pd.concat([df_merged, df_trends], axis=1)
crossover_manager.TrendChangePositive(df_merged, "short_trend",
alert_constants.TREND_SHORT)
crossover_manager.TrendChangePositive(df_merged, "inter_trend",
alert_constants.TREND_INTERMEDIATE)
crossover_manager.TrendChangePositive(df_merged, "long_trend",
alert_constants.TREND_LONG)
crossover_manager.TrendChangeNegative(df_merged, "short_trend",
alert_constants.TREND_SHORT)
crossover_manager.TrendChangeNegative(df_merged, "inter_trend",
alert_constants.TREND_INTERMEDIATE)
crossover_manager.TrendChangeNegative(df_merged, "long_trend",
alert_constants.TREND_LONG)
df_merged['rating'] = df_rating['rating']
df_merged = df_merged.replace([np.inf, -np.inf], np.nan)
df_merged = df_merged.dropna()
print "********************************************************************************"
print "********************************************************************************"
print "********************************************************************************"
#print df_merged
if (df_merged is None or df_merged.symbol.count() == 0):
return
logger.info("Saving history for Symbol " + symbol + " length = " +
str(len(df_merged)))
df_merged.set_index('date', inplace=True)
alert_manager.relative_strength(df_merged)
alert_manager.fullGapPositive(df_merged)
alert_manager.fullGapNegative(df_merged)
alert_manager.partialGapPositive(df_merged)
alert_manager.partialGapNegative(df_merged)
alert_manager.keyReversalPositive(df_merged)
alert_manager.keyReversalNegative(df_merged)
alert_manager.volumePositive(df_merged)
alert_manager.volumeNegative(df_merged)
crossover_manager.smacrossovers(df_merged)
crossover_manager.macd_crossovers(df_merged)
crossover_manager.obos_alerts(df_merged)
dbdao.save_dataframe(df_merged, "df_history")
#alert_manager.relative_strength(df_merged, df_spy, symbol)
df_merged['stdabove_prev'] = df_merged['stdabove'].shift(1)
#latest data calculations
return_data = {}
return_data.update(calculate_beta(df_symbol_close, df_mkt_close, symbol))
return_data.update(calculate_prices_at_dates(df_symbol_close, hist_dates))
monthly_date = hist_dates['Monthly']
weekly_date = hist_dates['Weekly']
df_latest = df_merged.tail(1)
df_latest_sign = df_latest.apply(calc_signs, axis=1)
df_latest = pd.concat([df_latest, df_latest_sign], axis=1)
#df_latest[['sma_20day_sign']]=df_latest.apply(calc_signs,axis=1)
df_latest['volatility_monthly'] = price_manager.get_specific_date_value(
df_merged, monthly_date, 'volatility')
df_latest['volatility_weekly'] = price_manager.get_specific_date_value(
df_merged, weekly_date, 'volatility')
df_latest['std50days'] = df_latest['stdabove']
df_latest['date'] = df_latest.index
df_latest.set_index('date', inplace=True)
for key, value in return_data.iteritems():
df_latest[key] = value
return df_latest
def getstats():
df_all = pd.DataFrame()
#list_symbol=dbdao.get_symbols_list_limit(start,end)
list_symbol = dbdao.get_missing_stats_symbol(start, end)
if (start == '0'):
dbdao.execute_query(["delete from df_stats"])
print list_symbol
logger.info(list_symbol)
for symbol in list_symbol:
try:
systats_data = stats_manager.yf_get_key_stat(symbol)
logger.info('calculating for symbol ' + symbol)
systats_data.append(['symbol', symbol])
df = pd.DataFrame(systats_data)
if (df.shape[0] > 10):
df = df.rename(columns={0: 'name'})
df['name'] = df['name'].apply(correct_name)
df = df[df['name'] != 'drop']
df = df.set_index('name').transpose()
print df
df_all = df_all.append(df)
except Exception, ex:
logger.error(ex)
print df_all
df_all.to_csv('data/df.csv')
dbdao.save_dataframe(df_all, 'df_stats')
if(start=='0'):
dbdao.execute_query(["delete from df_stats"])
print list_symbol
logger.info(list_symbol)
for symbol in list_symbol:
try:
systats_data = stats_manager.yf_get_key_stat(symbol)
logger.info('calculating for symbol '+symbol)
systats_data.append(['symbol',symbol])
df= pd.DataFrame(systats_data)
if(df.shape[0] >10):
df = df.rename(columns={0: 'name'})
df['name']= df['name'].apply(correct_name)
df= df[df['name']!='drop']
df= df.set_index('name').transpose()
print df
df_all=df_all.append(df)
except Exception,ex:
logger.error(ex)
print df_all
df_all.to_csv('data/df.csv');
dbdao.save_dataframe(df_all,'df_stats')
import pandas as pd
from dao import dbdao
df = pd.read_csv('data/etf/international.txt', header=None, names=['symbol'])
print df
dbdao.save_dataframe(df, "df_etf")
def calculate_technical(df_symbol,symbol,df_mkt,start_date_time,end_date_time,hist_dates,days_back):
#list_drop_cloumns = [ 'open', 'high','low','volume']
df_symbol_close = df_symbol[['close']]
df_mkt_close = df_mkt[['close']]
mom=abstract.MOM(df_symbol_close, timeperiod=5)
df_merged=abstract.MACD(df_symbol_close,fastperiod=12,slowperiod=26,signalperiod=9)
#df_merged=macd.apply(np.round)
df_std= abstract.STDDEV(df_symbol_close.pct_change(),timeperiod=100)
df_merged['stddev']=df_std
df_merged['volatility']=df_std*100*math.sqrt(252)
rsi=abstract.RSI(df_symbol_close).round(2)
sma20 = abstract.SMA(df_symbol_close, timeperiod=20).round(2)
sma100 = abstract.SMA(df_symbol_close, timeperiod=100).round(2)
sma200 = abstract.SMA(df_symbol_close, timeperiod=200).round(2)
sma3 = abstract.SMA(df_symbol_close, timeperiod=3).round(2)
sma5 = abstract.SMA(df_symbol_close, timeperiod=5).round(2)
sma9 = abstract.SMA(df_symbol_close, timeperiod=9).round(2)
sma13 = abstract.SMA(df_symbol_close, timeperiod=13).round(2)
sma25 = abstract.SMA(df_symbol_close, timeperiod=25).round(2)
sma50 = abstract.SMA(df_symbol_close, timeperiod=50).round(2)
sma90 = abstract.SMA(df_symbol_close, timeperiod=90).round(2)
sma36 = abstract.SMA(df_symbol_close, timeperiod=36).round(2)
sma150 = abstract.SMA(df_symbol_close, timeperiod=150).round(2)
df_merged['mom']=mom
df_merged['sma20']=sma20
df_merged['sma50']=sma50
df_merged['sma100']=sma100
df_merged['sma200']=sma200
df_merged['rsi']=rsi
df_merged['close']=df_symbol['close']
df_merged['open']=df_symbol['open']
df_merged['low']=df_symbol['low']
df_merged['high']=df_symbol['high']
df_merged['volume']=df_symbol['volume']
df_merged['sma_volume_6month']= pd.rolling_mean(df_merged['volume'], window=120).round(2)
df_merged=df_merged.dropna()
df_merged['symbol']=symbol
df_merged['rsi_value'] = df_merged['rsi'].apply(rsi_manager.calculate_rsi_values )
df_merged['sma3']=sma3
df_merged['sma5']=sma5
df_merged['sma9']=sma9
df_merged['sma13']=sma13
df_merged['sma20']=sma20
df_merged['sma25']=sma25
df_merged['sma50']=sma50
df_merged['sma90']=sma90
df_merged['sma36']=sma36
df_merged['sma150']=sma150
df_merged['Relative_strength']=relative_strength(df_symbol_close, df_mkt_close, symbol)['Relative_strength']
df_merged['stdabove']=df_merged.apply(calculate_stdabove,axis=1)
df_merged['date']=df_merged.index
df_res=df_symbol.apply(calc_res,axis=1)
df_merged=pd.concat([df_merged,df_res],axis=1)
df_rating=rating_manager.calc_rating_history(df_merged, days_back, symbol)
df_trends=df_merged.apply(lambda row : trend_manager.trend_calculation(row),axis=1)
df_merged=pd.concat([df_merged,df_trends],axis=1)
crossover_manager.TrendChangePositive(df_merged,"short_trend",alert_constants.TREND_SHORT)
crossover_manager.TrendChangePositive(df_merged,"inter_trend",alert_constants.TREND_INTERMEDIATE)
crossover_manager.TrendChangePositive(df_merged,"long_trend",alert_constants.TREND_LONG)
crossover_manager.TrendChangeNegative(df_merged,"short_trend",alert_constants.TREND_SHORT)
crossover_manager.TrendChangeNegative(df_merged,"inter_trend",alert_constants.TREND_INTERMEDIATE)
crossover_manager.TrendChangeNegative(df_merged,"long_trend",alert_constants.TREND_LONG)
df_merged['rating']=df_rating['rating']
df_merged=df_merged.replace([np.inf, -np.inf], np.nan)
df_merged=df_merged.dropna()
print "********************************************************************************"
print "********************************************************************************"
print "********************************************************************************"
#print df_merged
if(df_merged is None or df_merged.symbol.count()==0):
return
logger.info("Saving history for Symbol "+symbol + " length = "+ str(len(df_merged)))
df_merged.set_index('date',inplace=True)
alert_manager.relative_strength(df_merged)
alert_manager.fullGapPositive(df_merged)
alert_manager.fullGapNegative(df_merged)
alert_manager.partialGapPositive(df_merged)
alert_manager.partialGapNegative(df_merged)
alert_manager.keyReversalPositive(df_merged)
alert_manager.keyReversalNegative(df_merged)
alert_manager.volumePositive(df_merged)
alert_manager.volumeNegative(df_merged)
crossover_manager.smacrossovers(df_merged)
crossover_manager.macd_crossovers(df_merged)
crossover_manager.obos_alerts(df_merged)
dbdao.save_dataframe(df_merged, "df_history")
#alert_manager.relative_strength(df_merged, df_spy, symbol)
df_merged['stdabove_prev']=df_merged['stdabove'].shift(1)
#latest data calculations
return_data={}
return_data.update(calculate_beta(df_symbol_close,df_mkt_close,symbol))
return_data.update( calculate_prices_at_dates(df_symbol_close,hist_dates))
monthly_date = hist_dates['Monthly']
weekly_date = hist_dates['Weekly']
df_latest=df_merged.tail(1)
df_latest_sign=df_latest.apply(calc_signs,axis=1)
df_latest=pd.concat([df_latest,df_latest_sign],axis=1)
#df_latest[['sma_20day_sign']]=df_latest.apply(calc_signs,axis=1)
df_latest['volatility_monthly']= price_manager.get_specific_date_value(df_merged,monthly_date,'volatility')
df_latest['volatility_weekly']= price_manager.get_specific_date_value(df_merged,weekly_date,'volatility')
df_latest['std50days']=df_latest['stdabove']
df_latest['date']=df_latest.index
df_latest.set_index('date',inplace=True)
for key, value in return_data.iteritems():
df_latest[key]=value
return df_latest
list_drop_cloumns = [ 'open','volume','low','high','sma_volume_6month']
df_technical = result.drop(list_drop_cloumns, 1)
# add extra columns for low high median
df_technical['low_5day_max']=0
df_technical['low_5day_min']=0
df_technical['low_5day_median']=0
df_technical['high_5day_max']=0
df_technical['high_5day_min']=0
df_technical['high_5day_median']=0
df_technical['rsi_text']=''
dbdao.save_dataframe(df_technical,"df_technical");
sql_max_min_median_5days = open('queries/high_low_median.sql', 'r').read()
sql_update_technical_history = open('queries/update_technical_history.txt', 'r').read()
logger.info("Executing Queries for updating technicals")
delete_technicals_latest="delete from technicals_symbol"
update_technical_latest = open('queries/update_technical_latest.txt', 'r').read()
dbdao.execute_query([sql_max_min_median_5days,sql_update_technical_history,delete_technicals_latest,update_technical_latest])
logger.info("Queries Completed !")
import sys
print sys.argv
start,end=sys.argv[1],sys.argv[2]
calculate_technicals(start,end)
