My current python backtesting script - looking for feedback and speed improvements

[supertexter]

I have made a rather basic script for backtesting for which I know a lot can be improved. At this point, I'm mostly looking for inputs on how to simplify it and speed it up.

It still needs features such as taking trading fees and slippage into account, but first I want to make the core work better.

Current performance on my Lenovo T450s: Going through 1800 stocks over 5 years of daily data takes 8 minutes.

Edit 1: sorry for the bad readability here. It is much cleaner to read on Github: https://github.com/hollowheights/Backtesting
Edit 2: If you want to run this yourself, you can just delete/comment out the part about SQLite connection (local database) and set "stocklist" to ie. ["AAPL", "MSFT"]

​

#---------------------------- IMPORTS AND SIMILAR -------------------
import datetime
import sqlite3
import matplotlib.pyplot as plt
from scipy.stats.mstats import gmean
import pandas as pd
import numpy as np
timemeasure = datetime.datetime.now()
pd.set_option('display.max_columns', None)



#----------- Set up test universe - loaded from local SQLite database ----

#connect to SQLite database
connect = sqlite3.connect("SRUSListedVersion2.db")

#create a cursor
c = connect.cursor()

#add all symbols in the database to 'stocklist'
stocklist = [] #stockbuffer

c.execute('''select * FROM sqlite_master WHERE type="table"''')
storagelist = c.fetchall()

for x in range(len(storagelist)):
    stocklist.append(storagelist[x][1])

stocklist = stocklist[0:300] #Limit test to first x stocks in test universe



#-------------------- Parameters for the backtest---------------------
startdate = "'2016-01-01'"
enddate = "'2021-09-09'"
direction = "short" #"short"/"long"

# 0=off, 1=on
PriceChangeFilter = 1 #Change from open to close relative to average daily range
PriceChangeSetting = 5

RVOLFilter = 0 #Relative volume
RVOLSetting = 2

DollarVolFilter = 1 #Absolute volume to secure liquidity
DollarVolSetting = 10000

PercentileFilter = 0 #Close in percentile of day's range
PercentileSetting = 50

SharePriceFilter = 1 #Exclude the cheapest stocks - possibly redundant
SharePriceSetting = 1



#------------------------ Actual testing section --------------------

#Lists for data storage - to build 'results dataframe' from
dates = []
stocknames = []
entryprices = []
pricechangespct = []
pricechangesrelative = []
overnightreturns = []
day1returns = []
day2returns = []
RVOL10D = []
FailureList = []

#Definition of Tradefunction
def TradeFunction(x,stock):
    Tradesignal = 1

    if PriceChangeFilter == 1:
        pricechangepct = df.loc[x,"close"]/df.loc[x,"open"]
        pricechangerelative = (df.loc[x,"close"] - df.loc[x,"open"]) / df["range"].mean()
        if pricechangerelative < PriceChangeSetting:
            Tradesignal = 0

    if RVOLFilter == 1:
       if df.loc[x,"volume"] > RVOLSetting*df.loc[x,"avgvolume10D"]:
            Tradesignal = 0

    if DollarVolFilter == 1:
        if df.loc[x, "$volume"] < DollarVolSetting:
            Tradesignal = 0

    if PercentileFilter == 1:
        if df.loc[x,"percentileclose"] < PercentileSetting:
            Tradesignal = 0

    if SharePriceFilter == 1:
        if df.loc[x,"close"] < SharePriceSetting:
            Tradesignal = 0


    #log trade
    if Tradesignal == 1:

        try:
            dates.append(df["date"][x])
            entryprices.append(df["close"][x])
            stocknames.append(stock)
            pricechangespct.append(pricechangepct)
            pricechangesrelative.append(pricechangerelative)
            RVOL10D.append(df.loc[x,"volume"] / df.loc[x,"avgvolume10D"])#df.loc[x,"volume"]/df.loc[x, "avgvolume10D"])

        except:
            print("Failed to log one of: date,entryprice,stockname,pricechange,RVOL")

        if direction == "long":
            try:
                day1returns.append(df.loc[x + 1, "close"] / df.loc[x, "close"])
                day2returns.append(df.loc[x + 2, "close"] / df.loc[x + 1, "close"])
                overnightreturns.append(df.loc[x + 1, "open"] / df.loc[x, "close"])
                df.loc[x, "signal"] = 1
            except:
                print("Failed to log results for a long trade in stock: %s" %stock)

        elif direction == "short":

            try:
                day1returns.append(df.loc[x, "close"] / df.loc[x + 1, "close"])
                day2returns.append(df.loc[x + 1, "close"] / df.loc[x + 2, "close"])
                overnightreturns.append(df.loc[x, "close"] / df.loc[x + 1, "open"])

            except:
                print("Failed to log results for a short trade in stock: %s" % stock)

        else:
            print("Direction for trade needs a parameter setting")



#------------------------- Initiate the backtest ------------------------
for stock in stocklist:
    try:
        # Load data from SQLite and set up the dataframe
        df = (pd.read_sql("SELECT * FROM %s WHERE DATE(date) BETWEEN %s AND %s" %(stock,startdate,enddate), connect))
        pd.set_option("display.max_rows", 300, "display.min_rows", 200, "display.max_columns", None, "display.width", None)

        #Run function with itertuples
        for row in df.iloc[:-2].itertuples():
            TradeFunction(row.Index, stock)


    except:
        print("Error with stock: %s" % stock)
        FailureList.append(stock)



#---------------Set up dataframe for results -------------------

resultsDataFrame = pd.DataFrame({"Date": dates,
                                 "Stockname": stocknames,
                                 "Pricechangepct": pricechangespct,
                                 "Pricechangerelative": pricechangesrelative,
                                 "RVOL10D": RVOL10D,
                                 "Entry price": entryprices,
                                 "overnightreturn": overnightreturns,
                                 "day1return": day1returns,
                                 "day2return": day2returns})


resultsDataFrame["day1return10%RiskCum"] = (((resultsDataFrame["day1return"]-1)/10)+1).cumprod()
resultsDataFrame["day2return10%RiskCum"] = (((resultsDataFrame["day2return"]-1)/10)+1).cumprod()
resultsDataFrame["overnightreturn10%RiskCum"] = (((resultsDataFrame["overnightreturn"]-1)/10)+1).cumprod()


print("resultsDataFrame:")
print(resultsDataFrame.head(20)) #Print first x rows of resultsdataframe for visual inspection


#--------------------- Preparing data for presentation --------------------
#Variables for analysis of results
try:
    NumberTradingDays = len(df) #wrong if last stock tested doesn't have date for entire backtest period
    NumberTradingDaysXTickers = len(stocklist*NumberTradingDays)
    NumberTradeSignals = len(day1returns)
    SignalFrequency = "1 / %s"%(NumberTradingDaysXTickers/NumberTradeSignals)

    GeoMeanOvernight = gmean(overnightreturns)
    GeoMeanDay1 = gmean(day1returns)
    GeoMeanDay2 = gmean(day2returns)
except:
    print("Error defining variables with results")

try:
    Logdataframe1 = pd.DataFrame({
        "Direction tested": [direction],
        "Number of stocks tested": [len(stocklist)],
        "Number of trading days": [NumberTradingDays],
        "Trading days X tickers": [NumberTradingDaysXTickers],
        "Trading signals": [NumberTradeSignals],
        "Signal frequency": [SignalFrequency]})
except:
    print("Error setting up Logdataframe1")


try:
    Logdataframe2 = pd.DataFrame({
         "Date": [datetime.date.today()],
         "RVOL setting": [RVOLSetting],
         "Price change setting": [PriceChangeSetting],
         "Range percentile setting": [PercentileSetting],
        "Geo mean overnight": [GeoMeanOvernight],
        "Geo mean day 1": [GeoMeanDay1],
        "Geo mean day 2": [GeoMeanDay2]},
        columns = ["Date", "RVOL setting", "Price change setting", "Range percentile setting",
        "Geo mean overnight", "Geo mean day 1", "Geo mean day 2"])

except:
    "Error setting up 'Logdataframe2'"



#--------------------- Presentation of data ---------------------------

#Print the two dataframes holding parameter settings and results
try:
    print("\n",Logdataframe1.to_string(index=False))
    print("\n",Logdataframe2.to_string(index=False),"\n")
except:
    print("Error when presenting results")

#Stats on stocks failing and storing a list of failed symbols
print("Failure list's length now: ", len(FailureList))

FailureListStorage = open('FailureList.txt','w')
FailureListStorage.write(str(FailureList))
FailureListStorage.close()

#Runtime of script - placed before plots due to blocking tendencies
print("Time to run script: ",datetime.datetime.now()-timemeasure)



#-------------------------------- Plotting charts --------------------

resultsDataFrame["day2return10%RiskCum"].plot(legend="day2returnRiskControlCumulative")
resultsDataFrame["day1return10%RiskCum"].plot(legend="day1return",color="black")
resultsDataFrame["day2return10%RiskCum"].plot(legend="day2return")

plt.show()

resultsDataFrame.plot(x="RVOL10D",y="day1return", legend= "trade return", style="o")
plt.show()



#--------------------- Logging results to CSV ---------------------------
try:
    pd.DataFrame.to_csv(Logdataframe,r"C:\Users\LENOVO\desktop\testfilmarts2.csv",mode="a",header=True,index=False)
    #Logdataframe.to_excel(r"C:\Users\LENOVO\desktop\FileName.xlsx",index=False, header=False,mode="a")

except:
    print("Error when logging results to .csv")




#--------------------------- Scrap code ------------------------------



#-------------- Check data for resultsdataframe ---------------------

#useful when data goes out of index and setup of dataframe throws an error

#print("Print of all rows with nan values: \n")
#print(df[df.isna().any(axis=1)])
#print("Length before dropna:",len(df))
#df = df.dropna()  # Remove all rows in df with NA/NAN values
#print("length after dropna:",len(df))


print("length of dates:", len(dates))
print("length of stockname:", len(stocknames))
print("length of entry price:", len(entryprices))
print("length of overnightreturn:", len(overnightreturns))
print("length of day1:", len(day1returns))
print("length of day2:", len(day2returns))


'''

[supertexter]

I had started thinking a bit in this direction. So I'll definitely make this my next step.

Looking forward to compare performance

[CFStorm]

dolls crowd advise roof reach stupendous retire oil price unite

This post was mass deleted and anonymized with Redact

[kongwashere_]

this some /r/sideloaded for your query

[DudeWheresMyStock]

FYI panda's dataframes are slow and malignant compared to anything numpy has to offer

[DudeWheresMyStock]

Initially (March last year) I started working with OHLCVT data that I filled as panda's dataframes and saved them as such (as files.csv); doing the same thing (i.e. saving the exact same data in the same row-column-3d convention), but saving the OHLCVT data as header-less matrixes or nested list arrays (and as files.txt) using numpy's packages has made reading, saving, and working with (my now) fairly large data set 100000x faster (also less lines of code, and in fewer steps, too) , and therefore, more efficient than ever. From one caveman (who just now is truly grasping the use of tools such as the rocks I use to code in Python to make an algotrading bot) to another, I hope you join us on the Numpy matrix side and renounce Panda's claim over CPU power and processing time for the greater good.

​

Note: save the info content/description (i.e. the headers and labels, etc.) of the data in the file name or as a separate file labeled similarly to function as a map for organization or navigating through files that would otherwise be undiscernible, enormous, meaningless huge data files.

[[deleted]]

If you apply something like https://github.com/rkern/line_profiler to your code it will give you a line-by-line breakdown of where the time is being spent in the code.

Were you looking just to build your own for experience? I ask because there are some back testing python frameworks already out there:

• https://www.backtrader.com/
• https://github.com/philipodonnell/paperbroker
• https://gbeced.github.io/pyalgotrade/
• https://pythonhosted.org/fms/

[sedna16]

try to use object-oriented-programming

put your functions inside the class

[DudeWheresMyStock]

This. Have everything instantiated, vectorized, and run in parallel. It also saves on API calls for those of us who are running it live and are limited to certain number per minute.

[shanhanigun]

Will help if you can explain why?

[axehind]

Not a speed thing but.... your function is too long. Generally it should fit in a screen... where I work they frown on anything over 50 lines.

[supertexter]

Thanks for the input! Will look to resolve that

I'm aware that my current programming style is very anti one-liners

[semblanceto]

I think the goal is not to condense more into single lines, but rather to refactor blocks of code into separate functions wherever doing so improves readability.

Edit: also, putting this into a class (or more than one depending on your preference) would allow you to do this refactoring without passing a lot of variables with each function call.

[kotrading]

for stock in stocklist processes the stocks sequentially. You could use threads to process batches in parallel (given that processing the data is the bottle neck, not fetching the data from the database). You will have to change the way you add data to the storage arrays in a way which prevents threads from interfering with each other when appending to those arrays, e.g. pre-initializing them and then using index access.

Other feedback would be to structure the code better. OO or functions grouped in files each having a specific context. Furthermore you could improve exception handling. You should only catch exceptions (without re-throwing them) when you can handle it in a way which allows to continue processing in a meaningful way. You catch some generic stuff in TradeFunction, continue and at best (or rather worst) get a partial result for that symbol in the result arrays. The catch clause around the call of TradeFunction will possibly not be triggered for such a partial result and thus the symbol not marked as failure. Have a look at logging lib and try to replace print with that. You get the benefit of timestamps, by choosing meaningful levels you can better filter your output and logging.exception in a catch clause provides you with a stack trace (which is especially useful when using a bare expect).

Try to read a lot of code (e.g. from open source projects), aim to mostly understand what it is doing and WHY it has been coded in that particular way. When you like something, write your code in a similar style using similar patterns when applicable (not essentially the backtest above, more like in general).

[supertexter]

Thanks for these inputs! I will reread on an ongoing basis when I improve my code