def postgres_conn():
try:
t_str='dbname='+os.getenv("db_dbname")+' user='******' host='+os.getenv("db_host")+' password='******' connect_timeout=1 '
conn = psycopg2.connect(t_str)
lg.echo_msg('DB Connection test successfull')
# conn.close()
return conn
except:
lg.echo_msg('DB Connection test unsuccessfull')
return False
def fun_query(df_4db):
try:
conn=postgres_conn()
cur=conn.cursor()
cur.execute(df_4db)
colnames = [desc[0] for desc in cur.description]
fetch_rec = cur.fetchall()
df= pd.DataFrame(list(fetch_rec),columns=colnames)
return df
except:
lg.echo_msg('Error in function fun_conndb component Q')
return False
def fun_download_url(i_url):
if not i_url:
lg.echo_msg('i_url variable not set')
return 99
try:
r_response = requests.get(i_url, allow_redirects=True)
return r_response
except requests.ConnectionError:
lg.echo_msg(
'Unable to establish connection with url. Check url string or service'
)
return 99
def fun_url_alpha(i_stock):
apikey = os.getenv('AVANTAGEKEY')
if not apikey:
lg.echo_msg(
'API Key not found. Register https://www.alphavantage.co and set env variable AVANTAGEKEY'
)
return 99
else:
url = 'https://www.alphavantage.co/query?function=TIME_SERIES_DAILY&symbol=' + str(
i_stock) + '&apikey=' + str(apikey) + '&datatype=csv'
lg.echo_msg(url)
return url
def fun_truncate(i_tab_nme):
try:
i_str='Truncate ' + str(i_tab_nme)
conn=postgres_conn()
cur=conn.cursor()
lg.echo_msg('Executing stmt:::'+i_str)
cur.execute(i_str)
conn.commit
conn.close()
return 0
except:
lg.echo_msg('Error in function fun_truncate component T')
return False
def fun_mf_extract(i_mf_cnt, i_tp_cnt, start_dt, end_dt, f_filename):
url = 'http://portal.amfiindia.com/DownloadNAVHistoryReport_Po.aspx?mf=' + str(
i_mf_cnt) + '&tp=' + str(
i_tp_cnt) + '&frmdt=' + start_dt + '&todt=' + end_dt
lg.echo_msg('Inside the function' + str(i_mf_cnt) + str(i_tp_cnt))
lg.echo_msg('URL:::' + url)
if os.path.isfile(f_filename):
lg.echo_msg('File already exists: ' + f_filename)
return
r = requests.get(url, allow_redirects=True)
lg.echo_msg(datetime.datetime.now().strftime("%Y%m%d %H%M%S") +
'::: After request call')
if (r.content.find('Scheme Code') == 0):
lg.echo_msg('Inside Scheme Code validation statement')
open(f_filename, 'wb').write(r.content)
def fun_insert(df_4db,i_tab_nme):
try:
conn=postgres_conn()
t_str='postgresql://'+os.getenv("db_user")+':'+os.getenv("logpwd")+'@'+os.getenv("db_host")+':'+os.getenv("db_port")+'/'+os.getenv("db_user")
dbengine = ce(t_str)
print 'dbengine executed'
df_4db.head(0).to_sql(i_tab_nme, con=dbengine,if_exists='append')
print 'head(0) executed'
df_4db.to_sql(i_tab_nme, con=dbengine,if_exists='append', index=False)
print 'data insert executed'
conn.close()
return True
except:
lg.echo_msg('Error in function fun_conndb component IDF')
return False
def fun_getall_stock():
lg.echo_msg("Inside the function fun_all_asx_stock")
t_str_qry="select Exchange,Stock_Name,Stock,Sector from t_all_ASX_stock"
lg.echo_msg("Calling function db.fun_execreq to execute Query:: "+t_str_qry)
df=db.fun_execreq(t_str_qry,'', 'Q')
lg.echo_msg(df)
for t_stock_ex in df['stock']+"."+df['exchange']:
fun_insert_stg(t_stock_ex)
time.sleep(13)
def fun_execreq(df_4db,i_tab_nme, i_action):
### Make the below string as an environment variable
if i_action == 'IDF':
lg.echo_msg('Inside the function fun_conndb component IDF')
fun_insert(df_4db,i_tab_nme)
if i_action == 'Q':
lg.echo_msg('Inside the function fun_conndb component Q')
df=fun_query(df_4db)
return df
if i_action == 'T':
lg.echo_msg('Inside the function fun_truncate component T')
df=fun_truncate(i_tab_nme)
i_mf_cnt = 1
i_tp_cnt = 1 ##Indicate Open Ended fund
l_fin_list = []
while (i_mf_cnt != 3):
i_mf_cnt = i_mf_cnt + 1
# echo_msg('Calling Open Fund Function'+str(i_mf_cnt))
f_filename = str(i_mf_cnt) + '_' + str(i_tp_cnt) + a_filealias
l_fin_list.append(f_filename)
df_mf_fresh_data = pd.DataFrame(l_fin_list, columns=["filename"])
df_mf_fresh_data['download_status'] = ''
df_mf_fresh_data['date_loaded'] = ''
#np.where(df_avail_data['filename']!=df_mf_fresh_data['filename'])
if df_avail_data.empty:
for index, row in df_mf_fresh_data.iterrows():
lg.echo_msg('Calling for file ' + row['filename'])
try:
i_mf_cnt = row['filename'][:1]
fun_mf_extract(i_mf_cnt, i_tp_cnt, start_dt, end_dt,
row['filename'])
time.sleep(20)
except:
lg.echo_msg('Fail to extract data for ' + filename)
# fun_url_process(i_mf_cnt,1,start_dt,end_dt,a_filealias)
lg.echo_msg('Dataset creation completed')
os.chdir(p_curr_path)
lg.echo_msg('-----------------------------------------------------')
def fun_sel_stock(i_stockcode):
lg.echo_msg("Inside the function fun_sel_stock")
t_str_qry="select exchange, stock, timestamp, high, low, daily_range, open, close, prev_close, per_growth, log_return, volume, avg_volume from t_stock_alphavantage where stock='"+i_stockcode+"'"
lg.echo_msg("Calling function db.fun_execreq to execute Query:: "+t_str_qry)
df=db.fun_execreq(t_str_qry,'', 'Q')
lg.echo_msg(df)
def fun_daily_incr_aggr():
lg.echo_msg("Inside the function fun_daily_aggr")
t_str_qry="with t_sql as(SELECT exchange, stock, timestamp, high, low, (high-low) daily_range, open, close, lag(close) OVER(Partition BY stock order by timestamp) as prev_close, volume, AVG(volume) OVER(ORDER BY stock, timestamp ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS avg_volume from (select distinct exchange, stock, timestamp, open, high, low, close, volume from stg_stock_alphavantage) sa) select exchange, stock, timestamp, high, low, daily_range, open, close, prev_close, ((close-prev_close)*100/(case prev_close when 0 then 1 else prev_close END)) as per_growth, (ln(case close when 0 then 1 else close end)-ln(case prev_close when 0 then 1 else prev_close end) )log_return, volume, avg_volume from t_sql"
lg.echo_msg("Calling function db.fun_execreq to execute Query:: "+t_str_qry)
df_new=db.fun_execreq(t_str_qry,'', 'Q')
t_str_qry="select exchange, stock, timestamp, high, low, daily_range, open, close, prev_close, per_growth, log_return, volume, avg_volume from t_stock_alphavantage"
lg.echo_msg("Calling function db.fun_execreq to execute Query:: "+t_str_qry)
df_table=db.fun_execreq(t_str_qry,'', 'Q')
lg.echo_msg("Identify the changes")
df_change=df_new.merge(df_table.assign(flg=1),how='left').loc[lambda x :x['flg'].isnull(),:]
del df_change['flg']
lg.echo_msg("New record count"+str(df_change.count()))
i_tab_nme='t_stock_alphavantage'
i_action='IDF'
lg.echo_msg("Appenidng the change data")
db.fun_execreq(df_change,i_tab_nme, i_action)
import logprint as lg
import pandas as pd
import get_stock_extract as se
import db_postgres_access as db
import time
lg.echo_msg("Inside the function fun_ax_stock")
t_str_qry = "select distinct stock, timestamp, open, high, low, close,volume from stg_stock_alphavantage where stock='ANZ' order by timestamp"
lg.echo_msg("Calling function db.fun_execreq to execute Query:: " + t_str_qry)
df = db.fun_execreq(t_str_qry, '', 'Q')
df8 = df[:17]
df8
df8['prev1'] = df8.groupby(['stock'])['close'].shift(1)
df8['prev2'] = df8.groupby(['stock'])['close'].shift(2)
df8['prev3'] = df8.groupby(['stock'])['close'].shift(3)
df8['prev4'] = df8.groupby(['stock'])['close'].shift(4)
df8['prev5'] = df8.groupby(['stock'])['close'].shift(5)
df8['prev6'] = df8.groupby(['stock'])['close'].shift(6)
df8['prev7'] = df8.groupby(['stock'])['close'].shift(7)
df8