# -*- 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 = 'Sugar_11' #month = 'Jul' curyear = 2019 position = 'short' 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 maxduration = 21 # max number of days for trade out1 = [] def getincrementvalue(commodity): sql = 'select dollarsperincrement from MarketCommodity where dbname = "' + str(commodity) + '";' dbcur.execute(sql) incrementvalue = dbcur.fetchall() return incrementvalue[0][0] 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 # 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: maxloss = -2000 profittarget = 560 startdate = [] enddate = [] out = [] loopcount = 0 bigcount = 0 incrementvalue = getincrementvalue(crop) monthslist = getmonths(crop) firstyear = getfirstyear(crop) for month in monthslist: posentry1 = posentry.copy() posexit1 = posexit.copy() posexitlow = posexit1.copy() posexithigh = posexit1.copy() loopcount = 0 for year in range(firstyear,datetime.datetime.now().year): # for year in [2013]: df = dataseries(crop, month, year).dataseries df['Day'] = pd.to_datetime(df['Date']).dt.strftime('%m-%d') df[str(year) + '-Date'] = pd.to_datetime(df['Date']).dt.strftime('%Y-%m-%d') df = df.set_index(['Day']) dflow = df.copy() dfhigh = df.copy() del dfhigh['Date'], dfhigh['Open'], dfhigh['Low'], dfhigh['Close'] del dflow['Date'], dflow['Open'], dflow['High'], dflow['Close'] del df['Date'], df['Open'], df['High'], df['Low'] df.columns = [year, str(year) + '-Date'] dflow.columns = [year, str(year) + '-Date'] dfhigh.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='bfill').fillna(method='ffill') posexit1[str(year) + '-Date'] = posexit1[str(year) + '-Date'].fillna(method='bfill').fillna(method='ffill') posexitlow = posexitlow.join(dflow, how='outer') posexitlow[year] = posexitlow[year].fillna(method='bfill').fillna(method='ffill') posexitlow[str(year) + '-Date'] = posexitlow[str(year) + '-Date'].fillna(method='bfill').fillna(method='ffill') posexithigh = posexithigh.join(dflow, how='outer') posexithigh[year] = posexithigh[year].fillna(method='bfill').fillna(method='ffill') posexithigh[str(year) + '-Date'] = posexithigh[str(year) + '-Date'].fillna(method='bfill').fillna(method='ffill') posentry1 = posentry1.set_index(['Idx']) posexit1 = posexit1.set_index(['Idx']) posexit1 = posexit1.dropna() posentry1 = posentry1.dropna() posexitlow = posexitlow.set_index(['Idx']) posexitlow = posexitlow.dropna() for column in posentry1: if posentry1[column].dtype == np.float64: for i in range(len(posentry1)): equity = 0 for j in range(i+1,i+maxduration): if j <= 89 - maxduration: startdate.append(str(posentry1['Date'][i].month) + '-' + str(posentry1['Date'][i].day)) enddate.append(str(posexit1['Date'][j].month) + '-' + str(posexit1['Date'][j].day)) for k in range(i,j): if position == 'short': intradayequity = incrementvalue * (posentry1[column][i] - posexithigh[column][k]) tempequity = incrementvalue * (posentry1[column][i] - posexit1[column][k]) else: intradayequity = incrementvalue * (posexitlow[column][k] - posentry1[column][i]) tempequity = incrementvalue * (posexit1[column][k] - posentry1[column][i]) if tempequity <= maxloss or intradayequity <= maxloss: out1.append([column,i,j,tempequity]) #----------- break if tempequity > equity: equity = tempequity out.append(equity) 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 # out1 = out 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.60 and (Start == '4-29' or Start == '4-30' or Start == '5-1' or Start == '5-2' or Start == '5-3')") data = data.query("winrate >= 0.60") 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 print(crop + ' - ' + str(len(finaldata))) return finaldata 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_Crude_Oil = run('Crude_Oil') #aL_Dow_Jones_Mini = run('Dow_Jones_Mini') #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') # 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')