# -*- 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 = ['Gasoline', 'Heating_Oil', 'Crude_Oil'] #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 # 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): maxduration = 21 # max number of days for trade 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(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(0).sum(axis=1)-1)/(data.count(axis=1) - 1) data['count'] = data.count(axis=1) - 2 data = data.query("winrate >= 0.80 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 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') 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')