def updateIndicator(self):
"""
Calculate and Store the (New) Indicator Data.
"""
start = self._getLatestDate()
Logger.log(
logging.INFO, "Updating Indicator", {
"scope": __name__,
"indicator": self._tableName,
"code": self._tickerCode,
"start": str(start)
})
connection = sqlite3.connect(pyswing.database.pySwingDatabase)
newRecords = self._indicatorDataFrame.query("Date > '%s'" %
(str(start)))
connection.executemany(self._insertQuery,
newRecords.to_records(index=True))
connection.commit()
connection.close()
def importData(self):
"""
Import (New) Data from Yahoo.
"""
start = self._getLatestDate()
end = self._getTodaysDate()
Logger.log(logging.INFO, "Loading Data", {"scope":__name__, "tickerCode":self._tickerCode, "start":str(start), "end":str(end)})
self._data = DataReader(self._tickerCode, "yahoo", start, end)
self._data['Code'] = self._tickerCode
for item in ['Open', 'High', 'Low']:
self._data[item] = self._data[item] * self._data['Adj Close'] / self._data['Close']
self._data.drop('Close', axis=1, inplace=True)
self._data.rename(columns={'Adj Close':'Close'}, inplace=True)
self._data['Volume'] = self._data['Volume'].astype(float)
connection = sqlite3.connect(pyswing.database.pySwingDatabase)
query = "insert or replace into Equities (Date, Open, High, Low, Volume, Close, Code) values (?,?,?,?,?,?,?)"
connection.executemany(query, self._data.to_records(index=True))
connection.commit()
connection.close()
def getActiveStrategies():
connection = sqlite3.connect(pyswing.database.pySwingDatabase)
query = "select rule1, rule2, exit, type, rule3, meanResultPerTrade, medianResultPerTrade, totalProfit, numberOfTrades, sharpeRatio, maximumDrawdown from ActiveStrategy where active = 1"
strategies = None
cursor = connection.cursor()
try:
cursor.execute(query)
strategies = cursor.fetchall()
except sqlite3.OperationalError:
Logger.log(logging.INFO, "Error Getting Strategies",
{"scope": __name__})
connection.close()
strategiesList = []
for strategyRow in strategies:
strategy = Strategy(strategyRow[0], strategyRow[1], strategyRow[2],
strategyRow[3], strategyRow[4])
strategiesList.append(strategy)
strategy.meanResultPerTrade = strategyRow[5]
strategy.medianResultPerTrade = strategyRow[6]
strategy.totalProfit = strategyRow[7]
strategy.numberOfTrades = strategyRow[8]
strategy.sharpeRatio = strategyRow[9]
strategy.maximumDrawdown = strategyRow[10]
return strategiesList
def getStrategies(numberOfTrades, returnPerTrade):
connection = sqlite3.connect(pyswing.database.pySwingDatabase)
query = "select rule1, rule2, rule3, exit, type from ThreeRuleStrategy where numberOfTrades > %s and resultPerTrade > %s" % (
numberOfTrades, returnPerTrade)
strategies = None
cursor = connection.cursor()
try:
cursor.execute(query)
strategies = cursor.fetchall()
except sqlite3.OperationalError:
Logger.log(logging.INFO, "Error Getting Strategies",
{"scope": __name__})
connection.close()
strategiesList = []
for strategyRow in strategies:
strategy = Strategy(strategyRow[0], strategyRow[1], strategyRow[3],
strategyRow[4], strategyRow[2])
strategiesList.append(strategy)
return strategiesList
def evaluateRule(self):
"""
?.
"""
start = self._getLatestDate()
Logger.log(logging.INFO, "Evaluating Rule", {
"scope": __name__,
"Rule": self._ruleTableName,
"start": str(start)
})
self._restrictedSelectQuery = "%s where Date > '%s'" % (
self._selectQuery, start)
connection = sqlite3.connect(pyswing.database.pySwingDatabase)
self._ruleData = read_sql_query(self._restrictedSelectQuery,
connection, 'Date')
self._ruleData['Match'] = self._ruleData['Match'].astype(float)
connection.executemany(self._insertQuery,
self._ruleData.to_records(index=True))
connection.commit()
connection.close()
def analyseRule(self):
"""
?
"""
Logger.log(logging.INFO, "Analysing Rule", {"scope":__name__, "Rule":self._ruleTableName})
equityCount = self._getEquityCount()
potentialRuleMatches = self._getPotentialRuleMatches()
connection = sqlite3.connect(pyswing.database.pySwingDatabase)
cursor = connection.cursor()
try:
query = "select count(1) from '%s' where Match = 1;" % self._ruleTableName
cursor.execute(query)
actualRuleMatches = int(cursor.fetchone()[0])
query = "delete from Rules where Rule = '%s'" % self._ruleTableName
cursor.execute(query)
# Unit Testing is easier if the value is stored...
self._matchesPerDay = actualRuleMatches / potentialRuleMatches * equityCount
query = "insert into Rules values('%s',%s)" % (self._ruleTableName, self._matchesPerDay)
cursor.execute(query)
except sqlite3.OperationalError:
Logger.log(logging.INFO, "Error Analysing Rule", {"scope":__name__, "Rule":self._ruleTableName})
connection.commit()
connection.close()
def sendEmail(tradeDetails):
message = "Hello!\n\n"
for trade in tradeDetails:
message += trade
message += "\n\n"
message += "x"
subject = None
if len(tradeDetails) > 0:
subject = "Horse Says Trade!"
else:
subject = "Horse Says Go Back To Bed!"
header = 'From: %s\n' % "*****@*****.**"
header += 'To: %s\n' % ','.join(["*****@*****.**"])
header += 'Subject: %s\n\n' % subject
message = header + message
server = smtplib.SMTP("smtp.gmail.com:587")
server.starttls()
server.login("*****@*****.**", "abcABC1234567890")
problems = server.sendmail("*****@*****.**", ["*****@*****.**"], message)
server.quit()
if problems:
Logger.log(logging.ERROR, "Error Sending E-mail", {"scope": __name__, "problems": str(problems)})
def _getLatestDate(self):
connection = sqlite3.connect(pyswing.database.pySwingDatabase)
query = "select max(Date) from %s" % (self._tableName)
cursor = connection.cursor()
dateString = None
try:
cursor.execute(query)
dateString = cursor.fetchone()[0]
except sqlite3.OperationalError:
Logger.log(logging.INFO, "Table Does Not Exist", {
"scope": __name__,
"table": self._tableName
})
connection.close()
date = pyswing.constants.pySwingStartDate
if dateString:
date = datetime.datetime.strptime(dateString, "%Y-%m-%d %H:%M:%S")
return date
def askHorse(self, latestDate):
self.tradeDetails = []
connection = sqlite3.connect(pyswing.database.pySwingDatabase)
query = ("select r1.Code, r1.Date "
"from '%s' r1 "
" inner join '%s' r2 on r2.Match = 1 and r1.Date = r2.Date and r1.Code = r2.Code "
" inner join '%s' r3 on r3.Match = 1 and r1.Date = r3.Date and r1.Code = r3.Code "
"where r1.Match = 1 and r1.Date = '%s'") % (self._rule1, self._rule2, self._rule3, latestDate)
trades = None
cursor = connection.cursor()
try:
cursor.execute(query)
# print(query)
trades = cursor.fetchall()
except sqlite3.OperationalError:
Logger.log(logging.INFO, "Error Getting Trades", {"scope":__name__})
connection.close()
for trade in trades:
tradeSummary = ("%s %s using %s") % (self._type, trade[0], self._exit)
strategyDetail = ("Strategy: Mean: %s, Median: %s, Total: %s, Trades: %s, Sharpe Ratio: %s, Drawdown: %s") % (str(self.meanResultPerTrade), str(self.medianResultPerTrade), str(self.totalProfit), str(self.numberOfTrades), str(self.sharpeRatio), str(self.maximumDrawdown))
rulesDetail = ("Rules: '%s', '%s' and '%s'") % (self._rule1, self._rule2, self._rule3)
tradeDetail = "%s (%s)\n%s" % (tradeSummary, strategyDetail, rulesDetail)
self.tradeDetails.append(tradeDetail)
Logger.log(logging.INFO, "Suggested Trade", {"scope":__name__, "tradeDetail":tradeDetail})
return len(self.tradeDetails) > 0
def getActiveStrategies():
connection = sqlite3.connect(pyswing.database.pySwingDatabase)
query = "select rule1, rule2, exit, type, rule3, meanResultPerTrade, medianResultPerTrade, totalProfit, numberOfTrades, sharpeRatio, maximumDrawdown from ActiveStrategy where active = 1"
strategies = None
cursor = connection.cursor()
try:
cursor.execute(query)
strategies = cursor.fetchall()
except sqlite3.OperationalError:
Logger.log(logging.INFO, "Error Getting Strategies", {"scope":__name__})
connection.close()
strategiesList = []
for strategyRow in strategies:
strategy = Strategy(strategyRow[0], strategyRow[1], strategyRow[2], strategyRow[3], strategyRow[4])
strategiesList.append(strategy)
strategy.meanResultPerTrade = strategyRow[5]
strategy.medianResultPerTrade = strategyRow[6]
strategy.totalProfit = strategyRow[7]
strategy.numberOfTrades = strategyRow[8]
strategy.sharpeRatio = strategyRow[9]
strategy.maximumDrawdown = strategyRow[10]
return strategiesList
def __init__(self):
"""
?
"""
Logger.log(logging.DEBUG, "Log Object Creation", {"scope": __name__})
self._rules = self._getRules()
def __init__(self):
"""
?
"""
Logger.log(logging.DEBUG, "Log Object Creation", {"scope": __name__})
self._rules = self._getRules()
def evaluateRule(self, tickerCode):
"""
?
:param tickerCode:
"""
self._tickerCode = tickerCode
start = self._getLatestDate()
Logger.log(
logging.INFO, "Evaluating Rule", {
"scope": __name__,
"Rule": self._ruleTableName,
"code": self._tickerCode,
"start": str(start)
})
self._restrictedSelectQuery = "%s where r.Code = '%s' and r.Date >= '%s'" % (
self._selectQuery, self._tickerCode, start)
connection = sqlite3.connect(pyswing.database.pySwingDatabase)
self._ruleData = read_sql_query(self._restrictedSelectQuery,
connection, 'Date')
self._ruleData['LastCrosser'] = self._ruleData['Crosser'].shift(1)
self._ruleData['LastCrossee'] = self._ruleData['Crossee'].shift(1)
try:
self._ruleData['Match'] = (
self._ruleData['Crosser'] > self._ruleData['Crossee']
) & (self._ruleData['LastCrossee'] > self._ruleData['LastCrosser'])
self._ruleData['Match'] = self._ruleData['Match'].astype(float)
except TypeError as e:
# NOTE: Throws "TypeError: unorderable types: float() > NoneType()" if there is no data (e.g. SMA_200 for datasets smaller than 200)
Logger.log(
logging.ERROR, "Error Evaluating Rule", {
"scope": __name__,
"Rule": self._ruleTableName,
"exception": str(e)
})
self._ruleData['Match'] = 0.0
self._ruleData['Match'] = self._ruleData['Match'].astype(float)
self._ruleData.drop('Crosser', axis=1, inplace=True)
self._ruleData.drop('Crossee', axis=1, inplace=True)
self._ruleData.drop('LastCrosser', axis=1, inplace=True)
self._ruleData.drop('LastCrossee', axis=1, inplace=True)
newRecords = self._ruleData.query("Date > '%s'" % (str(start)))
connection.executemany(self._insertQuery,
newRecords.to_records(index=True))
connection.commit()
connection.close()
def calculateExitValues(self):
Logger.log(logging.INFO, "Calculating Exit Values", {
"scope": __name__,
"Rule": self._tableName,
"code": self._tickerCode
})
connection = sqlite3.connect(pyswing.database.pySwingDatabase)
self._selectBuyQuery = "select e.Date as Date, e.Date as TradeDate, e.Code, e.Open, e.Close, e.High, e.Low, x.Type, x.ExitValue, x.NumberOfDays, x.ExitDetail from Equities e left join '%s' x on e.Date = x.MatchDate and e.Code = x.Code and x.Type = 'Buy' where e.Code = '%s' and x.ExitValue is NULL" % (
self._tableName, self._tickerCode)
self._buyExitValueDataFrame = read_sql_query(self._selectBuyQuery,
connection, "Date")
numberOfRows = self._buyExitValueDataFrame.shape[0]
for i in range(0, numberOfRows):
self.calculateExitValueForBuy(i, numberOfRows - i)
self._buyExitValueDataFrame.drop('Open', axis=1, inplace=True)
self._buyExitValueDataFrame.drop('Close', axis=1, inplace=True)
self._buyExitValueDataFrame.drop('High', axis=1, inplace=True)
self._buyExitValueDataFrame.drop('Low', axis=1, inplace=True)
self._buyExitValueDataFrame['MatchDate'] = self._buyExitValueDataFrame[
'TradeDate'].shift(1)
self._buyExitValueDataFrame.drop('TradeDate', axis=1, inplace=True)
newRecords = self._buyExitValueDataFrame.query("Type=='Buy'")
connection.executemany(self._insertQuery,
newRecords.to_records(index=True))
connection.commit()
self._selectSellQuery = "select e.Date as Date, e.Date as TradeDate, e.Code, e.Open, e.Close, e.High, e.Low, x.Type, x.ExitValue, x.NumberOfDays, x.ExitDetail from Equities e left join '%s' x on e.Date = x.MatchDate and e.Code = x.Code and x.Type = 'Sell' where e.Code = '%s' and x.ExitValue is NULL" % (
self._tableName, self._tickerCode)
self._sellExitValueDataFrame = read_sql_query(self._selectSellQuery,
connection, "Date")
numberOfRows = self._sellExitValueDataFrame.shape[0]
for i in range(0, numberOfRows):
self.calculateExitValueForSell(i, numberOfRows - i)
self._sellExitValueDataFrame.drop('Open', axis=1, inplace=True)
self._sellExitValueDataFrame.drop('Close', axis=1, inplace=True)
self._sellExitValueDataFrame.drop('High', axis=1, inplace=True)
self._sellExitValueDataFrame.drop('Low', axis=1, inplace=True)
self._sellExitValueDataFrame[
'MatchDate'] = self._sellExitValueDataFrame['TradeDate'].shift(1)
self._sellExitValueDataFrame.drop('TradeDate', axis=1, inplace=True)
newRecords = self._sellExitValueDataFrame.query("Type=='Sell'")
connection.executemany(self._insertQuery,
newRecords.to_records(index=True))
connection.commit()
connection.close()
def __init__(self, tickerCode):
"""
Constructor.
There isn't much to see here. It just makes a note of the Ticker Code.
:param tickerCode: Ticker Code.
"""
Logger.log(logging.DEBUG, "Log Object Creation", {"scope":__name__, "arguments":" ".join({tickerCode})})
self._tickerCode = tickerCode
def __init__(self, relativeFilePath):
"""
Read the Ticker Codes in the specified file into a data set (pandas.DataFrame).
:param relativeFilePath: Relative file path (String) for the (file) list of ticker codes.
"""
Logger.log(logging.DEBUG, "Log Object Creation", {"scope":__name__, "arguments":" ".join({relativeFilePath})})
self._relativeFilePath = relativeFilePath
self.tickers = pandas.read_csv(relativeFilePath)
def evaluateRule(self, tickerCode):
"""
?
:param tickerCode:
"""
self._tickerCode = tickerCode
start = self._getLatestDate()
Logger.log(
logging.INFO, "Evaluating Rule", {
"scope": __name__,
"Rule": self._ruleTableName,
"code": self._tickerCode,
"start": str(start)
})
# We can't use self._getLatestDate() because we need data from before that date...
self._restrictedSelectQuery = "%s where Code = '%s'" % (
self._selectQuery, self._tickerCode)
connection = sqlite3.connect(pyswing.database.pySwingDatabase)
self._ruleData = read_sql_query(self._restrictedSelectQuery,
connection, 'Date')
self._ruleData['Relative'] = self._ruleData[
self._indicatorColumn].shift(self._relativeIndex * -1)
if self._comparison == Comparison.GreaterThan:
self._ruleData['Match'] = self._ruleData[
self._indicatorColumn] > self._multiplier * self._ruleData[
'Relative']
else:
self._ruleData['Match'] = self._ruleData[
self._indicatorColumn] < self._multiplier * self._ruleData[
'Relative']
self._ruleData['Match'] = self._ruleData['Match'].astype(float)
self._ruleData.drop('Relative', axis=1, inplace=True)
self._ruleData.drop(self._indicatorColumn, axis=1, inplace=True)
newRecords = self._ruleData.query("Date > '%s'" % (str(start)))
connection.executemany(self._insertQuery,
newRecords.to_records(index=True))
connection.commit()
connection.close()
def deleteDirectory(relativeDirectoryPath):
"""
Delete the specified directory.
:param relativeDirectoryPath: Relative (from the working directory) path to a directory (i.e. that we want to delete).
"""
try:
if os.path.exists(relativeDirectoryPath):
shutil.rmtree(relativeDirectoryPath)
except OSError as osError:
Logger.log(logging.ERROR, "Cannot Delete Directory", {"scope":__name__, "directory":relativeDirectoryPath})
Logger.log(logging.DEBUG, "Caught Exception", {"scope":__name__, "exception":str(osError)})
def copyFile(relativeSourceFilePath, relativeTargetFilePath):
"""
Copy the specified file.
:param relativeSourceFilePath: Relative (from the working directory) path to a file (i.e. source).
:param relativeTargetFilePath: Relative (from the working directory) path to a file (i.e. target).
"""
try:
shutil.copy(relativeSourceFilePath, relativeTargetFilePath)
except shutil.Error as e:
Logger.log(
logging.ERROR, "Cannot Copy File", {
"scope": __name__,
"relativeSourceFilePath": relativeSourceFilePath,
"relativeTargetFilePath": relativeTargetFilePath
})
Logger.log(logging.DEBUG, "Caught Exception", {
"scope": __name__,
"exception": str(e)
})
except IOError as e:
Logger.log(
logging.ERROR, "Cannot Copy File", {
"scope": __name__,
"relativeSourceFilePath": relativeSourceFilePath,
"relativeTargetFilePath": relativeTargetFilePath
})
Logger.log(logging.DEBUG, "Caught Exception", {
"scope": __name__,
"exception": str(e)
})
def emptyHistoricTradesTable():
connection = sqlite3.connect(pyswing.database.pySwingDatabase)
query = "delete from HistoricTrades"
cursor = connection.cursor()
try:
cursor.execute(query)
connection.commit()
except sqlite3.OperationalError:
Logger.log(logging.INFO, "Error Deleting Historic Trades", {"scope":__name__})
connection.close()
def evaluateThreeRuleStrategy(self):
"""
?
"""
Logger.log(logging.INFO, "Evaluating Three-Rule Strategy", {"scope":__name__, "Rule 1":self._rule1, "Rule 2":self._rule2, "Rule 3":self._rule3, "Type":self._type})
connection = sqlite3.connect(pyswing.database.pySwingDatabase)
c = connection.cursor()
sql = self.evaluateThreeRuleSql % (pyswing.globals.pySwingStrategy, self._rule1, self._rule2, self._rule3, self._exit, self._type, self._rule1, self._rule2, self._rule3, self._exit, self._type)
c.executescript(sql)
connection.commit()
c.close()
connection.close()
def deleteEmptyThreeRuleStrategies():
connection = sqlite3.connect(pyswing.database.pySwingDatabase)
query = "delete from ThreeRuleStrategy where resultPerTrade is NULL"
cursor = connection.cursor()
try:
cursor.execute(query)
connection.commit()
except sqlite3.OperationalError:
Logger.log(logging.INFO, "Error Deleting Empty Three Rule Strategies", {"scope":__name__})
connection.close()
def markTwoRuleStrategyAsProcessed(rule1, rule2, type):
connection = sqlite3.connect(pyswing.database.pySwingDatabase)
query = "update TwoRuleStrategy set Searched = 1 where rule1 = '%s' and rule2 = '%s' and type = '%s';" % (rule1, rule2, type)
cursor = connection.cursor()
try:
cursor.execute(query)
connection.commit()
except sqlite3.OperationalError:
Logger.log(logging.INFO, "Error Updating Strategy", {"scope":__name__})
connection.close()
def test_log(self):
with patch.object(Logger._getLogger(), '_log',
return_value=None) as mock_method:
# Assumes that the default log level is DEBUG
Logger.log(logging.DEBUG, "Test Debug Log Entry", {
"key1": "value1",
"key2": "value2"
})
self.assertEqual(1, mock_method.call_count)
Logger.log(logging.INFO, "Test Info Log Entry", {
"key1": "value1",
"key2": "value2"
})
self.assertEqual(2, mock_method.call_count)
Logger.log(logging.WARN, "Test Warning Log Entry", {
"key1": "value1",
"key2": "value2"
})
self.assertEqual(3, mock_method.call_count)
Logger.log(logging.ERROR, "Test Error Log Entry", {
"key1": "value1",
"key2": "value2"
})
self.assertEqual(4, mock_method.call_count)
def deleteFile(relativeFilePath):
"""
Delete the specified file.
:param relativeFilePath: Relative (from the working directory) path to a file (i.e. that we want to delete).
"""
try:
if os.path.exists(relativeFilePath):
if ("pyswing.db" not in relativeFilePath):
Logger.log(logging.INFO, "Delete File", {
"scope": __name__,
"file": relativeFilePath
})
os.remove(relativeFilePath)
else:
Logger.log(logging.WARN,
"Do You Want To Delete This Database?", {
"scope": __name__,
"file": relativeFilePath
})
except OSError as osError:
Logger.log(logging.ERROR, "Cannot Delete File", {
"scope": __name__,
"directory": relativeFilePath
})
Logger.log(logging.DEBUG, "Caught Exception", {
"scope": __name__,
"exception": str(osError)
})
def forceWorkingDirectory():
"""
forceWorkingDirectory() will ensure that the working directory is set to the project root (i.e. /Users/Gary/PycharmProjects/pyswing).
There is a discrepancy between TeamCity (which always forces the working directory to be the project root) and IDEs
(e.g. Eclipse, PyCharm) (which, by default, set the working directory to be the directory containing the script being run).
"""
Logger.log(logging.DEBUG, "Log Method Call", {"scope":__name__, "arguments":""})
workingDirectory = os.path.abspath(os.path.curdir)
if (os.path.exists("pyswing") and os.path.exists("test")):
Logger.log(logging.DEBUG, "Working Directory", {"scope":__name__, "workingDirectory":workingDirectory})
else:
newWorkingDirectory = None
if os.path.exists("../pyswing") and os.path.exists("../test"):
newWorkingDirectory = os.path.abspath("../")
elif os.path.exists("../../pyswing") and os.path.exists("../../test"):
newWorkingDirectory = os.path.abspath("../../")
elif os.path.exists("../../../pyswing") and os.path.exists("../../../test"):
newWorkingDirectory = os.path.abspath("../../../")
elif os.path.exists("../../../../pyswing") and os.path.exists("../../../../test"):
newWorkingDirectory = os.path.abspath("../../../../")
elif os.path.exists("../../../../../pyswing") and os.path.exists("../../../../../test"):
newWorkingDirectory = os.path.abspath("../../../../../")
elif os.path.exists("../../../../../../pyswing") and os.path.exists("../../../../../../test"):
newWorkingDirectory = os.path.abspath("../../../../../../")
if newWorkingDirectory is not None:
os.chdir(newWorkingDirectory)
Logger.log(logging.DEBUG, "Changed Working Directory", {"scope":__name__, "old":workingDirectory,"new":newWorkingDirectory})
else:
Logger.log(logging.WARN, "Cannot Change Working Directory", {"scope":__name__, "workingDirectory":workingDirectory})
def generateHistoricTrades(self):
"""
?
"""
Logger.log(logging.INFO, "Generating Historic Trades", {"scope":__name__, "Rule 1":self._rule1, "Rule 2":self._rule2, "Rule 3":self._rule3, "Type":self._type})
connection = sqlite3.connect(pyswing.database.pySwingDatabase)
c = connection.cursor()
sql = self.insertIntoHistoricTradesSql % (self._type, self._rule1, self._rule2, self._rule3, self._exit, self._type)
# print(sql)
c.executescript(sql)
connection.commit()
c.close()
connection.close()
def __init__(self, relativeFilePath):
"""
Read the Ticker Codes in the specified file into a data set (pandas.DataFrame).
:param relativeFilePath: Relative file path (String) for the (file) list of ticker codes.
"""
Logger.log(logging.DEBUG, "Log Object Creation", {
"scope": __name__,
"arguments": " ".join({relativeFilePath})
})
self._relativeFilePath = relativeFilePath
self.tickers = pandas.read_csv(relativeFilePath)
def deleteEmptyThreeRuleStrategies():
connection = sqlite3.connect(pyswing.database.pySwingDatabase)
query = "delete from ThreeRuleStrategy where resultPerTrade is NULL"
cursor = connection.cursor()
try:
cursor.execute(query)
connection.commit()
except sqlite3.OperationalError:
Logger.log(logging.INFO, "Error Deleting Empty Three Rule Strategies",
{"scope": __name__})
connection.close()
def emptyHistoricTradesTable():
connection = sqlite3.connect(pyswing.database.pySwingDatabase)
query = "delete from HistoricTrades"
cursor = connection.cursor()
try:
cursor.execute(query)
connection.commit()
except sqlite3.OperationalError:
Logger.log(logging.INFO, "Error Deleting Historic Trades",
{"scope": __name__})
connection.close()
def createDatabase(argv):
"""
Create Database.
:param argv: Command Line Parameters.
-n = Name
Example:
python -m pyswing.CreateDatabase -n asx
"""
Logger.log(logging.INFO, "Log Script Call", {"scope":__name__, "arguments":" ".join(argv)})
Logger.pushLogData("script", __name__)
marketName = ""
try:
shortOptions = "n:dh"
longOptions = ["marketName=", "debug", "help"]
opts, __ = getopt.getopt(argv, shortOptions, longOptions)
except getopt.GetoptError as e:
Logger.log(logging.ERROR, "Error Reading Options", {"scope": __name__, "exception": str(e)})
usage()
sys.exit(2)
for opt, arg in opts:
if opt in ("-d", "--debug"):
Logger().setLevel(logging.DEBUG)
elif opt in ("-h", "--help"):
print("?")
usage()
sys.exit()
elif opt in ("-n", "--marketName"):
marketName = arg
if marketName != "":
pyswing.database.initialiseDatabase(marketName)
databaseFilePath = pyswing.database.pySwingDatabase
scriptFilePath = pyswing.constants.pySwingDatabaseScript
Logger.log(logging.INFO, "Creating Database", {"scope":__name__, "databaseFilePath":databaseFilePath, "scriptFilePath":scriptFilePath})
query = open(pyswing.constants.pySwingDatabaseScript, 'r').read()
connection = sqlite3.connect(databaseFilePath)
c = connection.cursor()
c.executescript(query)
connection.commit()
c.close()
connection.close()
TeamCity.setBuildResultText("Created Database")
else:
Logger.log(logging.ERROR, "Missing Options", {"scope": __name__, "options": str(argv)})
usage()
sys.exit(2)
def overrideDatabase(override):
"""
Override "pySwingDatabase" (i.e. file path to the SQL Lite database).
This should only be used for Unit Testing.
:param override: String (e.g. "output/TestEvaluateRules.db")
"""
Logger.log(logging.INFO, "Override Database", {"scope": __name__, "override": override})
global pySwingDatabase
pySwingDatabase = override
global pySwingDatabaseOverridden
pySwingDatabaseOverridden = True
def markTwoRuleStrategyAsProcessed(rule1, rule2, type):
connection = sqlite3.connect(pyswing.database.pySwingDatabase)
query = "update TwoRuleStrategy set Searched = 1 where rule1 = '%s' and rule2 = '%s' and type = '%s';" % (
rule1, rule2, type)
cursor = connection.cursor()
try:
cursor.execute(query)
connection.commit()
except sqlite3.OperationalError:
Logger.log(logging.INFO, "Error Updating Strategy",
{"scope": __name__})
connection.close()
def askHorse(self, latestDate):
self.tradeDetails = []
connection = sqlite3.connect(pyswing.database.pySwingDatabase)
query = (
"select r1.Code, r1.Date "
"from '%s' r1 "
" inner join '%s' r2 on r2.Match = 1 and r1.Date = r2.Date and r1.Code = r2.Code "
" inner join '%s' r3 on r3.Match = 1 and r1.Date = r3.Date and r1.Code = r3.Code "
"where r1.Match = 1 and r1.Date = '%s'") % (
self._rule1, self._rule2, self._rule3, latestDate)
trades = None
cursor = connection.cursor()
try:
cursor.execute(query)
# print(query)
trades = cursor.fetchall()
except sqlite3.OperationalError:
Logger.log(logging.INFO, "Error Getting Trades",
{"scope": __name__})
connection.close()
for trade in trades:
tradeSummary = ("%s %s using %s") % (self._type, trade[0],
self._exit)
strategyDetail = (
"Strategy: Mean: %s, Median: %s, Total: %s, Trades: %s, Sharpe Ratio: %s, Drawdown: %s"
) % (str(self.meanResultPerTrade), str(self.medianResultPerTrade),
str(self.totalProfit), str(self.numberOfTrades),
str(self.sharpeRatio), str(self.maximumDrawdown))
rulesDetail = ("Rules: '%s', '%s' and '%s'") % (
self._rule1, self._rule2, self._rule3)
tradeDetail = "%s (%s)\n%s" % (tradeSummary, strategyDetail,
rulesDetail)
self.tradeDetails.append(tradeDetail)
Logger.log(logging.INFO, "Suggested Trade", {
"scope": __name__,
"tradeDetail": tradeDetail
})
return len(self.tradeDetails) > 0
def importData(argv):
"""
Import Share Data.
:param argv: Command Line Parameters.
-n = Name
Example:
python -m pyswing.ImportData -n asx
"""
Logger.log(logging.INFO, "Log Script Call", {"scope":__name__, "arguments":" ".join(argv)})
Logger.pushLogData("script", __name__)
marketName = ""
try:
shortOptions = "n:dh"
longOptions = ["marketName=", "debug", "help"]
opts, __ = getopt.getopt(argv, shortOptions, longOptions)
except getopt.GetoptError as e:
Logger.log(logging.ERROR, "Error Reading Options", {"scope": __name__, "exception": str(e)})
usage()
sys.exit(2)
for opt, arg in opts:
if opt in ("-d", "--debug"):
Logger().setLevel(logging.DEBUG)
elif opt in ("-h", "--help"):
print("?")
usage()
sys.exit()
elif opt in ("-n", "--marketName"):
marketName = arg
if marketName != "":
pyswing.database.initialiseDatabase(marketName)
Logger.log(logging.INFO, "Import Market Data", {"scope":__name__, "market":marketName})
tickerCodesRelativeFilePath = "resources/%s.txt" % (marketName)
market = Market(tickerCodesRelativeFilePath)
for index, row in market.tickers.iterrows():
equity = Equity(row[0])
equity.importData()
TeamCity.setBuildResultText("Imported Data from Yahoo")
else:
Logger.log(logging.ERROR, "Missing Options", {"scope": __name__, "options": str(argv)})
usage()
sys.exit(2)
def _getRules(self):
connection = sqlite3.connect(pyswing.database.pySwingDatabase)
query = "SELECT name FROM sqlite_master WHERE type='table' and name like 'Rule %' order by name"
rules = None
cursor = connection.cursor()
try:
cursor.execute(query)
rules = cursor.fetchall()
except sqlite3.OperationalError:
Logger.log(logging.INFO, "Error Getting Rules", {"scope": __name__})
connection.close()
return [(rule[0]) for rule in rules]
def generateHistoricTradesForActiveStrategies(argv):
"""
Generate (in the HistoricTrades database table) Historic Trades for the Active Strategies.
Empty the database table and then fill it with the historic trades for all the active strategies.
:param argv: Command Line Parameters.
-n = Name
Example:
python -m pyswing.GenerateHistoricTradesForActiveStrategies -n asx
"""
Logger.log(logging.INFO, "Log Script Call", {"scope":__name__, "arguments":" ".join(argv)})
Logger.pushLogData("script", __name__)
marketName = ""
try:
shortOptions = "n:dh"
longOptions = ["marketName=", "debug", "help"]
opts, __ = getopt.getopt(argv, shortOptions, longOptions)
except getopt.GetoptError as e:
Logger.log(logging.ERROR, "Error Reading Options", {"scope": __name__, "exception": str(e)})
usage()
sys.exit(2)
for opt, arg in opts:
if opt in ("-d", "--debug"):
Logger().setLevel(logging.DEBUG)
elif opt in ("-h", "--help"):
print("?")
usage()
sys.exit()
elif opt in ("-n", "--marketName"):
marketName = arg
if marketName != "":
pyswing.database.initialiseDatabase(marketName)
Logger.log(logging.INFO, "Generate Historic Trades for Active Strategies", {"scope":__name__, "market":marketName})
emptyHistoricTradesTable()
strategies = getActiveStrategies()
for strategy in strategies:
strategy.generateHistoricTrades()
else:
Logger.log(logging.ERROR, "Missing Options", {"scope": __name__, "options": str(argv)})
usage()
sys.exit(2)
def _createTable(self, startIndex, stopIndex):
Logger.log(logging.INFO, "Create Historic Matches Table",
{"scope": __name__, "startIndex": str(startIndex), "stopIndex": str(stopIndex)})
deleteTableStatement, createTableStatement = self._generateSqlStatements(startIndex, stopIndex)
# print(deleteTableStatement)
# print(createTableStatement)
connection = sqlite3.connect(pyswing.database.pySwingDatabase)
c = connection.cursor()
c.executescript(deleteTableStatement)
c.executescript(createTableStatement)
connection.commit()
c.close()
connection.close()
def test_deleteFile(self):
relativeFilePath = "output/test_deleteFile.txt"
try:
file = open(relativeFilePath, 'w')
file.write("Hello!")
file.close()
except OSError as osError:
Logger.log(logging.ERROR, "Cannot Create File", {"scope":__name__, "directory":relativeFilePath})
Logger.log(logging.DEBUG, "Caught Exception", {"scope":__name__, "exception":str(osError)})
self.assertTrue(os.path.exists(relativeFilePath))
deleteFile(relativeFilePath)
self.assertFalse(os.path.exists(relativeFilePath))
def analyse(self):
# Logger.log(logging.INFO, "Analyse Strategy", {"scope":__name__, "Rule 1":self._rule1, "Rule 2":self._rule2, "Rule 3":self._rule3, "Type":self._type})
connection = sqlite3.connect(pyswing.database.pySwingDatabase)
query = self.analyseStrategySql % (self._rule1, self._rule2, self._rule3, self._exit, self._type)
self._strategyData = read_sql_query(query, connection, 'Date')
self._strategyData['ExitValueAfterCosts'] = self._strategyData['ExitValue'] - 0.2
connection.close()
exitValueDataFrame = self._strategyData.ix[:,'ExitValueAfterCosts']
mean = exitValueDataFrame.mean()
median = exitValueDataFrame.median()
sum = exitValueDataFrame.sum()
count = exitValueDataFrame.count()
tradesPerYear = count / 10
sharpeRatio = sqrt(tradesPerYear) * exitValueDataFrame.mean() / exitValueDataFrame.std()
self._strategyData["Sum"] = expanding_sum(exitValueDataFrame)
self._strategyData["Max"] = expanding_max(self._strategyData["Sum"])
self._strategyData["Min"] = expanding_min(self._strategyData["Sum"])
self._strategyData["DD"] = self._strategyData["Max"] - self._strategyData["Min"]
runningSum = expanding_sum(exitValueDataFrame)
max2here = expanding_max(runningSum)
dd2here = runningSum - max2here
drawDown = dd2here.min()
Logger.log(logging.INFO, "Analysing Strategy", {"scope":__name__, "Rule 1":self._rule1, "Rule 2":self._rule2, "Rule 3":self._rule3, "Exit":self._exit, "Type":self._type, "Mean":str(mean), "Median":str(median), "Sum":str(sum), "Count":str(count), "SharpeRatio":str(sharpeRatio), "DrawDown":str(drawDown)})
connection = sqlite3.connect(pyswing.database.pySwingDatabase)
c = connection.cursor()
deleteSql = self.deleteStrategySql % (pyswing.globals.pySwingStrategy, self._rule1, self._rule2, self._rule3, self._exit, self._type)
c.executescript(deleteSql)
connection.commit()
insertSql = self.insertStrategySql % (pyswing.globals.pySwingStrategy, self._rule1, self._rule2, self._rule3, self._exit, self._type, str(mean), str(median), str(sum), str(count), str(sharpeRatio), str(drawDown))
c.executescript(insertSql)
connection.commit()
c.close()
connection.close()
def updateIndicator(self):
"""
Calculate and Store the (New) Indicator Data.
"""
start = self._getLatestDate()
Logger.log(logging.INFO, "Updating Indicator", {"scope":__name__, "indicator":self._tableName, "start":str(start)})
connection = sqlite3.connect(pyswing.database.pySwingDatabase)
newRecords = self._indicatorDataFrame.query("Date > '%s'" % (str(start)))
connection.executemany(self._insertQuery, newRecords.to_records(index=True))
connection.commit()
connection.close()
def getBestUnprocessedTwoRuleStrategy(numberOfTrades):
connection = sqlite3.connect(pyswing.database.pySwingDatabase)
query = "select rule1, rule2, exit, type from TwoRuleStrategy where numberOfTrades > %s and Searched = 0 order by resultPerTrade desc limit 1;" % numberOfTrades
rowData = None
cursor = connection.cursor()
try:
cursor.execute(query)
rowData = cursor.fetchall()
except sqlite3.OperationalError:
Logger.log(logging.INFO, "Error Getting Strategy", {"scope": __name__})
connection.close()
return (rowData[0][0], rowData[0][1], rowData[0][2], rowData[0][3])
def getBestUnprocessedTwoRuleStrategy(numberOfTrades):
connection = sqlite3.connect(pyswing.database.pySwingDatabase)
query = "select rule1, rule2, exit, type from TwoRuleStrategy where numberOfTrades > %s and Searched = 0 order by resultPerTrade desc limit 1;" % numberOfTrades
rowData = None
cursor = connection.cursor()
try:
cursor.execute(query)
rowData = cursor.fetchall()
except sqlite3.OperationalError:
Logger.log(logging.INFO, "Error Getting Strategy", {"scope":__name__})
connection.close()
return (rowData[0][0], rowData[0][1], rowData[0][2], rowData[0][3])
def getRules():
connection = sqlite3.connect(pyswing.database.pySwingDatabase)
query = "SELECT name FROM sqlite_master WHERE type = 'table' and name like 'Rule %'"
rules = None
cursor = connection.cursor()
try:
cursor.execute(query)
rules = cursor.fetchall()
except sqlite3.OperationalError:
Logger.log(logging.INFO, "Error Getting Rules", {"scope":__name__})
connection.close()
return [(rule[0]) for rule in rules]
def getTwoRuleStrategies(minimumMatchesPerDay):
connection = sqlite3.connect(pyswing.database.pySwingDatabase)
query = "select distinct r1.rule, r2.rule from Rules r1 inner join Rules r2 on 1 == 1 where r1.MatchesPerDay * r2.MatchesPerDay > %s and r1.rule != r2.rule" % minimumMatchesPerDay
strategies = None
cursor = connection.cursor()
try:
cursor.execute(query)
strategies = cursor.fetchall()
except sqlite3.OperationalError:
Logger.log(logging.INFO, "Error Getting Strategies", {"scope":__name__})
connection.close()
return [(rules[0], rules[1]) for rules in strategies]
def getExitStrategies():
connection = sqlite3.connect(pyswing.database.pySwingDatabase)
query = "SELECT name FROM sqlite_master WHERE type = 'table' and name like 'Exit %'"
exitStrategies = None
cursor = connection.cursor()
try:
cursor.execute(query)
exitStrategies = cursor.fetchall()
except sqlite3.OperationalError:
Logger.log(logging.INFO, "Error Getting Exit Strategies", {"scope":__name__})
connection.close()
return [(exitStrategy[0]) for exitStrategy in exitStrategies]
def analyseRules(argv):
"""
Analyse Rules.
:param argv: Command Line Parameters.
-n = Name
Example:
python -m pyswing.AnalyseRules -n asx
"""
Logger.log(logging.INFO, "Log Script Call", {"scope":__name__, "arguments":" ".join(argv)})
Logger.pushLogData("script", __name__)
marketName = ""
try:
shortOptions = "n:dh"
longOptions = ["marketName=", "debug", "help"]
opts, __ = getopt.getopt(argv, shortOptions, longOptions)
except getopt.GetoptError as e:
Logger.log(logging.ERROR, "Error Reading Options", {"scope": __name__, "exception": str(e)})
usage()
sys.exit(2)
for opt, arg in opts:
if opt in ("-d", "--debug"):
Logger().setLevel(logging.DEBUG)
elif opt in ("-h", "--help"):
print("?")
usage()
sys.exit()
elif opt in ("-n", "--marketName"):
marketName = arg
if marketName != "":
pyswing.database.initialiseDatabase(marketName)
Logger.log(logging.INFO, "Analyse Rules", {"scope":__name__, "market":marketName})
rules = getRules()
for ruleString in rules:
rule = Rule(ruleString)
rule.analyseRule()
TeamCity.setBuildResultText("Analysed Rules")
else:
Logger.log(logging.ERROR, "Missing Options", {"scope": __name__, "options": str(argv)})
usage()
sys.exit(2)
def deleteDirectory(relativeDirectoryPath):
"""
Delete the specified directory.
:param relativeDirectoryPath: Relative (from the working directory) path to a directory (i.e. that we want to delete).
"""
try:
if os.path.exists(relativeDirectoryPath):
shutil.rmtree(relativeDirectoryPath)
except OSError as osError:
Logger.log(logging.ERROR, "Cannot Delete Directory", {
"scope": __name__,
"directory": relativeDirectoryPath
})
Logger.log(logging.DEBUG, "Caught Exception", {
"scope": __name__,
"exception": str(osError)
})
def getTwoRuleStrategies(minimumMatchesPerDay):
connection = sqlite3.connect(pyswing.database.pySwingDatabase)
query = "select distinct r1.rule, r2.rule from Rules r1 inner join Rules r2 on 1 == 1 where r1.MatchesPerDay * r2.MatchesPerDay > %s and r1.rule != r2.rule" % minimumMatchesPerDay
strategies = None
cursor = connection.cursor()
try:
cursor.execute(query)
strategies = cursor.fetchall()
except sqlite3.OperationalError:
Logger.log(logging.INFO, "Error Getting Strategies",
{"scope": __name__})
connection.close()
return [(rules[0], rules[1]) for rules in strategies]
def overrideDatabase(override):
"""
Override "pySwingDatabase" (i.e. file path to the SQL Lite database).
This should only be used for Unit Testing.
:param override: String (e.g. "output/TestEvaluateRules.db")
"""
Logger.log(logging.INFO, "Override Database", {
"scope": __name__,
"override": override
})
global pySwingDatabase
pySwingDatabase = override
global pySwingDatabaseOverridden
pySwingDatabaseOverridden = True
def _getPotentialRuleMatches(self):
if not pyswing.globals.potentialRuleMatches:
connection = sqlite3.connect(pyswing.database.pySwingDatabase)
query = "select count(1) from '%s'" % self._ruleTableName
cursor = connection.cursor()
try:
cursor.execute(query)
pyswing.globals.potentialRuleMatches = int(cursor.fetchone()[0])
except sqlite3.OperationalError:
Logger.log(logging.INFO, "Error Getting Potential Rule Matches", {"scope":__name__, "rule":self._ruleTableName})
connection.close()
return pyswing.globals.potentialRuleMatches
def _getEquityCount(self):
if not pyswing.globals.equityCount:
connection = sqlite3.connect(pyswing.database.pySwingDatabase)
query = "select count(distinct Code) from Equities"
cursor = connection.cursor()
try:
cursor.execute(query)
pyswing.globals.equityCount = int(cursor.fetchone()[0])
except sqlite3.OperationalError:
Logger.log(logging.INFO, "Error Getting Equity Count", {"scope":__name__})
connection.close()
return pyswing.globals.equityCount
def forceWorkingDirectory():
"""
forceWorkingDirectory() will ensure that the working directory is set to the project root (i.e. /Users/Gary/PycharmProjects/pyswing).
There is a discrepancy between TeamCity (which always forces the working directory to be the project root) and IDEs
(e.g. Eclipse, PyCharm) (which, by default, set the working directory to be the directory containing the script being run).
"""
Logger.log(logging.DEBUG, "Log Method Call", {
"scope": __name__,
"arguments": ""
})
workingDirectory = os.path.abspath(os.path.curdir)
if (os.path.exists("pyswing") and os.path.exists("test")):
Logger.log(logging.DEBUG, "Working Directory", {
"scope": __name__,
"workingDirectory": workingDirectory
})
else:
newWorkingDirectory = None
if os.path.exists("../pyswing") and os.path.exists("../test"):
newWorkingDirectory = os.path.abspath("../")
elif os.path.exists("../../pyswing") and os.path.exists("../../test"):
newWorkingDirectory = os.path.abspath("../../")
elif os.path.exists("../../../pyswing") and os.path.exists(
"../../../test"):
newWorkingDirectory = os.path.abspath("../../../")
elif os.path.exists("../../../../pyswing") and os.path.exists(
"../../../../test"):
newWorkingDirectory = os.path.abspath("../../../../")
elif os.path.exists("../../../../../pyswing") and os.path.exists(
"../../../../../test"):
newWorkingDirectory = os.path.abspath("../../../../../")
elif os.path.exists("../../../../../../pyswing") and os.path.exists(
"../../../../../../test"):
newWorkingDirectory = os.path.abspath("../../../../../../")
if newWorkingDirectory is not None:
os.chdir(newWorkingDirectory)
Logger.log(
logging.DEBUG, "Changed Working Directory", {
"scope": __name__,
"old": workingDirectory,
"new": newWorkingDirectory
})
else:
Logger.log(logging.WARN, "Cannot Change Working Directory", {
"scope": __name__,
"workingDirectory": workingDirectory
})
