# -*- coding: utf-8 -*- """ Spyder Editor This is a temporary script file. """ from Modules.dataseries import dataseries import numpy as np import pandas as pd import calendar import datetime import MySQLdb db = MySQLdb.connect(host="a2nlmysql13plsk.secureserver.net", # your host user="coreypace", # username passwd="Aud12rey", # password db="FarmBusiness") # name of the database # Create a Cursor object to execute queries. dbcur = db.cursor() #crop = 'Coffee_ICE' #position = 'long' #month = 'Jul' #curyear = 2019 abbr_to_num = {name: num for num, name in enumerate(calendar.month_abbr) if num} #monthnum = abbr_to_num[month] daysofyear = [] idx = [] curyeardate = [] curmonth = datetime.datetime.now().month curday = datetime.datetime.now().timetuple().tm_yday def getmonths(commodity): monthlist = [] sql = 'SELECT DISTINCT MarketCommodityMonths.ShortMonth \ FROM (MarketCommodity INNER JOIN MarketCommodityMonths ON MarketCommodity.Symbol = MarketCommodityMonths.Symbol) INNER JOIN MarketMonths ON MarketCommodityMonths.ShortMonth = MarketMonths.ShortMonth \ WHERE MarketMonths.MonthNumber Between ' + str(curmonth + 2) + ' And ' + str(curmonth + 4) + ' and DBName = "' + str(commodity) + '" order by monthnumber asc' dbcur.execute(sql) months = dbcur.fetchall() for i in range(len(months)): monthlist.append(months[i][0]) return monthlist def getfirstyear(commodity): sql = 'select FirstYear from MarketCommodity where DBName = "' + str(commodity) + '"' dbcur.execute(sql) firstyear = dbcur.fetchall()[0][0] if datetime.datetime.now().year - 15 < firstyear: return firstyear else: return datetime.datetime.now().year - 15 def getincrementvalue(commodity): sql = 'select dollarsperincrement from MarketCommodity where dbname = "' + str(commodity) + '";' dbcur.execute(sql) incrementvalue = dbcur.fetchall() return incrementvalue[0][0] # build array of all days for next 90 days for i in range(0, 90): day = datetime.date.today() + datetime.timedelta(i) daysofyear.append(day.strftime('%m-%d')) idx.append(int(i)) curyeardate.append(day) posentry = pd.DataFrame({'Day':daysofyear}).join(pd.DataFrame({'Idx':idx})).join(pd.DataFrame({'Date':curyeardate})) posexit = pd.DataFrame({'Day':daysofyear}).join(pd.DataFrame({'Idx':idx})).join(pd.DataFrame({'Date':curyeardate})) posentry = posentry.set_index(['Day']) posexit = posexit.set_index(['Day']) def run(crop): #if True: incrementvalue = getincrementvalue(crop) maxduration = 21 # max number of days for trade profittarget = 260 startdate = [] enddate = [] out = [] loopcount = 0 bigcount = 0 # for crop in ['Gasoline']: monthslist = getmonths(crop) firstyear = getfirstyear(crop) for month in monthslist: posentry1 = posentry posexit1 = posexit loopcount = 0 for year in range(firstyear,datetime.datetime.now().year): df = dataseries(crop, month, year).dataseries df['Day'] = pd.to_datetime(df['Date']).dt.strftime('%m-%d') # df['Month'] = month df[str(year) + '-Date'] = pd.to_datetime(df['Date']).dt.strftime('%Y-%m-%d') df = df.set_index(['Day']) del df['Date'], df['Open'], df['High'], df['Low'] df.columns = [year, str(year) + '-Date'] posentry1 = posentry1.join(df, how='outer') posentry1[year] = posentry1[year].fillna(method='bfill').fillna(method='ffill') posentry1[str(year) + '-Date'] = posentry1[str(year) + '-Date'].fillna(method='bfill').fillna(method='ffill') posexit1 = posexit1.join(df, how='outer') posexit1[year] = posexit1[year].fillna(method='ffill').fillna(method='bfill') posexit1[str(year) + '-Date'] = posexit1[str(year) + '-Date'].fillna(method='ffill').fillna(method='bfill') posentry1 = posentry1.set_index(['Idx']) posexit1 = posexit1.set_index(['Idx']) posexit1 = posexit1.dropna() posentry1 = posentry1.dropna() for column in posentry1: if posentry1[column].dtype == np.float64: for i in range(len(posentry1)): for j in range(i+1,i+maxduration): if j <= 89 - maxduration: # if posentry['Date'][i].weekday() <= 4 and posentry['Date'][j].weekday() <= 4: startdate.append(str(posentry1['Date'][i].month) + '-' + str(posentry1['Date'][i].day)) enddate.append(str(posexit1['Date'][j].month) + '-' + str(posexit1['Date'][j].day)) if position == 'short': diff = posentry1[column][i] - posexit1[column][j] # Short else: diff = posexit1[column][j] - posentry1[column][i] # Long # if diff <= 0: # win = 0 # else: # win = 1 out.append(incrementvalue*diff) df_yr = pd.DataFrame({'Start':startdate}).join(pd.DataFrame({'End':enddate})).join(pd.DataFrame({column:out})) df_yr = df_yr.set_index(['Start', 'End']) if loopcount == 0: data = df_yr else: data = data.join(df_yr, how='outer') loopcount+=1 out = [] del df_yr startdate = [] enddate = [] data['mean'] = data.mean(axis=1) data['winrate'] = (data.select_dtypes(include='float64').gt(profittarget).sum(axis=1)-1)/(data.count(axis=1) - 1) data['count'] = data.count(axis=1) - 2 data = data.query("winrate >= 0.70 and mean > 0") data = data.reset_index() data['Commodity'] = crop data['Month'] = month data['Position'] = position data['Monthnum'] = abbr_to_num[month] data = data.query("Monthnum > datetime.datetime.now().month") if bigcount == 0: finaldata = data else: finaldata = finaldata.append(data) bigcount += 1 finaldata = finaldata.reset_index() outsql = [] if len(finaldata) > 0: print(len(finaldata)) for i in range(len(finaldata)): outsql.append((finaldata.Start[i],finaldata.End[i],finaldata.Commodity[i], finaldata.Position[i], finaldata.Month[i], finaldata.winrate[i], finaldata['mean'][i])) dbcur.executemany("""insert into MarketTradeWindows (Start, End, Commodity, Position, Month, winrate, mean) values (%s,%s,%s,%s,%s,%s,%s)""", outsql) db.commit() return finaldata #monthslist = getmonths('Coffee_ICE') position = 'long' #aL_Australian_Dollar = run('Australian_Dollar') #aL_British_Pound = run('British_Pound') #aL_Coffee_ICE = run('Coffee_ICE') #aL_Copper = run('Copper') #aL_Corn = run('Corn') #aL_Cotton = run('Cotton') #aL_Feeder_Cattle = run('Feeder_Cattle') #aL_Gasoline = run('Gasoline') #aL_Gold = run('Gold') #aL_Heating_Oil = run('Heating_Oil') #aL_Lean_Hogs = run('Lean_Hogs') #aL_Live_Cattle = run('Live_Cattle') #aL_Nasdaq_100_Mini = run('Nasdaq_100_Mini') #aL_Natural_Gas = run('Natural_Gas') #aL_Orange_Juice = run('Orange_Juice') #aL_Platinum = run('Platinum') #aL_SP_500_Mini = run('S&P_500_Mini') #aL_Silver = run('Silver') #aL_Soybeans = run('Soybeans') #aL_Sugar_11 = run('Sugar_11') #aL_Wheat = run('Wheat') #aL_Canadian_Dollar = run('Canadian_Dollar') #aL_Japanese_Yen = run('Japanese_Yen') #aL_KC_Wheat = run('KC_Wheat') #aL_Oats = run('Oats') #aL_Palladium = run('Palladium') #aL_Swiss_Franc = run('Swiss_Franc') #aL_Soybean_Meal = run('Soybean_Meal') #aL_T_Bond_30 = run('T_Bond_30') position = 'short' #aS_Australian_Dollar = run('Australian_Dollar') #aS_British_Pound = run('British_Pound') #aS_Coffee_ICE = run('Coffee_ICE') #aS_Copper = run('Copper') #aS_Corn = run('Corn') #aS_Cotton = run('Cotton') #aS_Crude_Oil = run('Crude_Oil') #aS_Dow_Jones_Mini = run('Dow_Jones_Mini') #aS_Feeder_Cattle = run('Feeder_Cattle') #aS_Gasoline = run('Gasoline') #aS_Gold = run('Gold') #aS_Heating_Oil = run('Heating_Oil') ##aS_Lean_Hogs = run('Lean_Hogs') #aS_Live_Cattle = run('Live_Cattle') #aS_Nasdaq_100_Mini = run('Nasdaq_100_Mini') #aS_Natural_Gas = run('Natural_Gas') #aS_Orange_Juice = run('Orange_Juice') #aS_Platinum = run('Platinum') #aS_SP_500_Mini = run('S&P_500_Mini') #aS_Silver = run('Silver') #aS_Soybeans = run('Soybeans') #aS_Sugar_11 = run('Sugar_11') #aS_Wheat = run('Wheat') #aS_Canadian_Dollar = run('Canadian_Dollar') #aS_Japanese_Yen = run('Japanese_Yen') #aS_KC_Wheat = run('KC_Wheat') #aS_Oats = run('Oats') #aS_Palladium = run('Palladium') #aS_Swiss_Franc = run('Swiss_Franc') #aS_Soybean_Meal = run('Soybean_Meal') #aS_T_Bond_30 = run('T_Bond_30')