How To Backtest ETF Trading Strategies In Excel (FULL Tutorial w/ Best Practices + Examples)

you don't need to know how to use any popular back testing software like metatrader 4 or think back by think or swim to be able to back test your own quants based trading strategy you definitely don't need to be a python programmer in this video i'm going to show you how to build and back test your very own quants based trading strategy using nothing but excel and historical data that you can download for free from yahoo finance i'm also going to talk about why i like this method the best and most importantly be sure to stick around to the end of the video because i'm going to be sharing some best practices that you absolutely must follow if you're going to be building and testing your own quants based trading strategies what's going on youtube my name is eli i'm the founder of tactile trade and i'm passionate about making quants-based investing available to everyday investors [Music] before we get into this i just want to say that i am going to make a shorter spin-off version of this video for those who just want to back test passive etf indexing index investing strategies that don't require any active buying or selling so if that's kind of the ticket you're looking for then be sure to subscribe down below and hit that little notification bell so that youtube tells you once i've uploaded that video and that's going to be coming really soon i also just want to get a bit of a disclaimer out of the way the strategy that we're building in today's tutorial please don't follow it as a strategy it's far too simplistic and it's just serving as an example so just keep that in mind before we get into it i just want to say that this is not going to be a day trading strategy this is an etf rotation strategy and with that we're going to be using daily closing prices for our historical data now if you have minute by minute data or hourly data you could use this approach for a day trading strategy too but i don't recommend it and here's why first of all etf rotation strategies are easier to follow they happen in one day increments so the most you'll ever need to trade is once a day fewer transactions also result in fewer commissions studies have also shown that the longer your holding period the more likely you are to be profitable which is why i prefer etf rotation over day trading you might be surprised at how hard it is to be profitable as a day trader also when we're working with a longer time frame data analysis can be a lot more meaningful intraday prices truly are a random walk and i really feel like nobody can predict them with any accuracy and probably the most important reason why i'd like you to consider using etf rotation is when you use this method you're going to be building something from scratch and that means you're more likely to end up with something that's proprietary and truly your own everybody and their aunt linda already knows what a 200-day moving average is there's no edge so before we begin you're going to need four things first you're going to need a hypothesis or an idea for a trading strategy that you'd like to build a common one is a rotation between stocks and bonds now my rotation strategy that i use and that i share with my community is called smart beta rotation and it rotates between mid cap stocks utilities stocks and treasury bonds but get creative it could really be anything maybe you want to do an entirely fixed income rotation strategy maybe you want to do an entirely equity rotation strategy sector rotations really popular really you're only limited by your imagination the second thing that you're going to need is you're going to need historical data we can pull that pretty easily for most exchange traded products from yahoo finance which we're going to do in just a second and the third and most challenging ingredient you're going to need is you're going to need your rules and they're going to have to be quantifiable you're going to have to be able to apply logic to them or convert them into logic and then obviously for number 4 you're gonna need excel before we go any further this video took me a long time to make so be sure to hit that like button and also consider subscribing to the channel and if you do be sure to hit that notification bell so that youtube knows you want to see more of my videos and if you are interested in quants-based investing head on over to my website tactiletrade.com and pre-register for my service everybody's going to get a 60-day free trial when it launches so with that out of the way it's time to start our tutorial before we go any further we're going to do a quick review of the if function in excel because it's an important building block for our back testing engine the if function tests if a criteria is true or false it returns a value if it's true and it returns another value if it's false criteria can be an order or an operation greater than less than or equal to or an equal not equal to test and the value that returns is can be either a number or a letter in quotes so in my example here you can see if a given cell is greater than or equal to 100 if true the logical test will return long spy if false then it will return cache if you want to test multiple criteria you can insert an and or an or statement within your if statement and tests if all criteria are true or tests if any one criteria are true you can nest if statements within other if statements to create a chain of logic you can see the chain that i've created to illustrate this and the formula given as an example above so we have an if statement here if criteria is true then the first if statement returns the value given for true if the first if statement is false then instead of returning a value for false it will proceed to the second if statement and then that if statement will test its logic and if it's true return its value and if it's false return the other value so you've nested one if statement inside the other what you're looking at is a comparison between live trading and an excel back test i did on one of the early prototypes for my strategies the yellow is the excel back test and the green is the live or paper trading results in thinkorswim so as you can see this method does track live trading pretty closely there may be some discrepancies but as time goes on in the long run it should track live trading pretty close and if you follow all of the best practices that i'll outline later in the video to minimize things like slippage then you'll be golden okay we have a lot to get through so i'm gonna go pretty quick i'm not going to explain any of the risk adjusted return metrics or the indicators that we're going to be testing i'm assuming you either know them or you want to know more about them in which case subscribe to my channel and they'll be in future videos so here we are in excel i've got historical data uploaded already you just download it from yahoo finance select your date range click apply and then download the csv and copy it in i've done that already to save time but one thing that you just want to make sure you want to spot check the prices so spot check the first and the last closing price for each product that you're testing and make sure the dates line up make sure the dates in the csv you download match the dates in your master spreadsheet absolutely essential even if your dates are off by one row it's going to completely mess up your data so every time you upload a piece of data make sure you spot check it and that your dates line up so i've got the vix 3m which is basically the vix index but instead of a 30-day calculation a 90-day calculation i've got the spy etf tlt which is our treasury bond etf and triple q which is our nasdaq technology etf so let's get building the spreadsheet is going to have three parts blue i like to designate for historical data orange i like to designate for play area building rules and then green i like to designate for the actual back testing engine so first things first we need the daily changes for each of these products and that's just the final price minus initial price divided by initial price format that as a percent drag that oops drag the fill handle across since the columns are next to each other and then double click the fill handle to fill it down we're going to come back to the rule building after we've done our back testing engine so let's get started we need position oh that's annoying there we go we need position we need frictionless capital we need capital net of fees and we need the daily p l for both of them for both of them thick outside border here because this is where we're going to enter our starting capital then we need our trade count then we're also going to need the same for our benchmark so oh sorry that should be capital net of fees let's just copy this over here to save time benchmark and again thick border and then daily p l again for our benchmark okay so let's just put long triple q in here as a placeholder i just want a placeholder while we build the logic for our back testing engine so now frictionless capital the formula for that is if u 3 equals long triple q then yesterday's equity times 1 plus the daily price change for the corresponding or for triple q so this is where we're programming our back testing engine to capture the daily price changes so we're going to be testing triple q and tlt so we're programming triple q and tlt into our back testing engine so we're nesting in if function here if that's false probably missing a bracket there we go so if that's false then test and see if we're long tlt so if u3 equals long tlt comma then yesterday's equity v3 times 1 plus the daily price change in tlt and if neither of those conditions are met sorry if neither of those conditions are met then just return return yesterday's capital so we're going to assume we're in cash now you can nest as many if statements in here as you want for as many products as you want to test you could be testing two or ten it really doesn't matter you're just going to get a long formula if you're doing 10. but that's basically how you program the logic into our back testing engine and the reason that we're getting our signals from yesterday's position is because think of it this way if yesterday you were holding a stock and today your signal changes that indicates it's now time to be holding triple q you held that stock overnight so you still have to capture the daily price change from today to yesterday or yesterday to today so that's why we're basically following yesterday's signals with today's price change i'm sorry i've just made a very important mistake that needs to be row 4 that needs to be row 4. don't mix don't mess that up takes the take the extra time you need to ensure you're doing it right so yes yesterday's signals today's daily price change because if you're holding a position overnight which in this test we are then that's how you account for that looks like i just forgot a couple brackets there now it's zero because we haven't entered anything in our starting capital so let's start with ten thousand you can start with anything you like five ten fifteen twenty twenty five doesn't matter and now we can copy that down so now we're capturing the daily price changes for triple q and our daily p l for that is just the final minus initial divided by initial format as a percent that should now since we're long triple q right across the board that should match the daily price change for triple q which it does that's great so now we need our capital after fees so this is a good time to input our commission or to specify how much we're paying in commission so let's just put up here commission since it's since it's an input let's do a thick bracket and let's just say it's five now this is going to be um round trip commission so if you're paying 250 per trade you're selling one etf and moving into another you're paying it twice so it's five if you're paying five per trade it's actually ten because the way that this is set up this is your round trip commission okay so uh before we do that though we need to put in our logic for our trade count and that's basically going to be if today's position does not equal yesterday's position so if u3 does not equal sorry if u4 does not equal u3 trade if not empty so now when we calculate our capital net of fees we can program the logic if this cell equals trade so if a trade has happened then we've incurred a commission so we have to subtract it so that's going to be yesterday's net of fee capital times 1 plus the daily penal of our frictionless capital minus the commission so we'll change that to an absolute cell reference so we don't lose it and if not then it's just going to be yesterday's capital net of fees times 1 plus the daily pnl frictionless seem to be missing a parenthesis ah it's so annoying there we go so let's program ten thousand dollars to start so we can see that now it's deducting our commission and the nice thing about this is you can just specify whatever commission you want and you can see that it'll just deduct it so it's flexible double click the fill handle format that as a number let's take the daily pnl of this as well final minus initial divided by initial so now we've got our frictionless equity curve we've got our um equity curve net of fees we need our benchmark so again our benchmark obviously we all have to start all of them with 10 000. benchmark is a little bit more simple basically it's just yesterday's equity value times 1 plus the daily pnl of our benchmark which in this case is going to be the s p 500 or the spy so capture the daily price changes of the spy and now we've basically got the growth of 10 000 invested only in the spy again we'll take the daily p l of that leave two columns here these are going to be for drawdown measurements we're going to get more on that get into that in a second when we build our risk adjusted return metrics but just label this drawdown test wrap the text and draw down benchmark we'll get to that in a second so the another thing that we're going to want is we're going to want to see our trade frequency which we could put up here or we could put down below uh might as well put it all in one spot so we'll put it down below okay so now we want to visualize our equity curve so highlight the date column highlight your frictionless capital highlight your capital net of fees and highlight your benchmark go to insert a line chart we'll insert a line chart now it's going to look strange because we haven't extended our rules down all the way yet but that's okay so let's format this chart change it to year so now we can see the year and let's make that a bit bigger that's fine and let's give it a name growth of hypothetical ten thousand dollars seems like a pretty suitable name if you ask me so now let's calculate our results section leave a bit of space here because we're going to put another chart down there but let's now enter the space for our test results so test 2015 2016 2017 2018 2019 2020. this is going to be the yearly and monthly returns extend that out excel's pretty good about using the ai to know what you're doing there and let's make this consistent format it and let's give it some borders so now what you're going to have to do is you're going to have to go month by month year by year and basically see where the month and year months and years end and calculate the p l for them it's going to be very tedious but trust me it's worth it because then you're going to get very granular data an easy way to do it would be to hide these columns except for capital net of fees because you need to see that so just go up to search type hide column hide and that's going to make it a little bit easier so for january if we want to calculate the returns for january 2015 let's just find january 25 the month of january rather here row 23 so capital net of fees in row 23 minus the first day in january 2015 divided by the first you know while we're at it let's format this whole table as a percentage so you're basically going to need to go and do that for every month so the next month would be february and there's the first of february or the second i guess and then for the year again just find the end of 2015. we haven't filled our data in yet but there it is there minus the first day in 2015 divided by the first day in 2015. so i'm gonna pause the video and let you fill all of this in it's tedious you're just going to have to do it it's going to be annoying but trust me it's going to be very good to have this granular data so i'll pause the video and we'll come back once i've filled it in it's probably going to take me about 20 minutes or so so i'll see you then oh man my eyes are tired how about you if your eyes are tired let me know in the comments section down below that was a lot of data entry if you know of a way to record the daily or the weekly or sorry if you know of a way to record the monthly changes that correspond to the dates like some kind of way to automate that so you don't have to enter it manually i'd be very curious to know how to do that so let me know in the comments below if you're an excel whiz and anyways so now is a good time to go ahead and if you haven't already copy down the rest of the formulas in the back testing engine so double click the fill handle oh i should probably unhide these columns first unhide and i just realized i'm not in full screen here i'm not giving you all the real estate there we go okay so there's no adjacent column so we're gonna have to drag this all the way down believe it was cell 1400 and something there it is 14.76 okay so now we've extended our testing engine all the way down so don't worry there's a shortcut to avoid having to do all of that again for the benchmark which i'm going to show you right now so let's just copy this down and change that to benchmark benchmark give it all borders now here's the trick to avoid having to do all of that again this data corresponds to our capital net of fees but what we can do is highlight these cells press ctrl h to bring up the find and replace menu find the equal sign in every formula replace it with the hashtag and click replace all that's going to convert this to plain text now you can copy these cells and paste them here and then press ctrl h again and now replace the x with the column for our benchmark and that's going to be column double a or a a so ctrl h find what find x replace it with a a and click replace all now we can find the hashtag and replace it with the equal sign and that's going to convert everything back into formulas and do the same for the test so ctrl h find the hashtag replace it with the equal sign and that's going to convert that back into a formula so now we've just copied all the formulas over but just changing the column so now we've got the monthly and yearly returns for our test and the monthly and yearly returns for our benchmark so before we start building our rules and actually before i show you how to start testing things the last thing we need to do is we need to build out our dashboard or our risk adjusted return metrics so for our risk adjusted return we'll just create another table down here and we'll do one column for sorry computer seems to be slow one column for test another for benchmark and then we're going to want the compound annual growth rate the sharp ratio correlation to s p 500 maximum drawdown and beta i'm not going to bother explaining what any of these are i have a whole youtube playlist already on risk adjusted return so if you want to learn about the sharp ratio the correlation coefficient maximum drawdown and beta in depth i have a video for each of these four metrics just head to my channel and look at my playlist on risk adjusted return for now we're just going to calculate them to save time so compound annual growth rate that is just the final price final closing price divided by the first closing price so for our test that's our capital net of fees we're going to scroll all the way down to the bottom find our last go all the way back up divide it by our first close the bracket raise it to the power of one divided by the number of periods in this case five years five periods so one divided by five close bracket wrap the whole thing in another bracket minus one format that as a percentage and there's our compound annual growth rate now we can just copy that formula and stick it in the next row or the next cell over rather for our benchmark and now we just change the x to the a a for our benchmark just like we did in the find and replace a moment ago click enter so now we have the cagr for our benchmark so the next metric we'll calculate is the sharp ratio that is just the returns of the portfolio so we'll use the compound annual growth rate minus the risk-free rate that's basically the rate you could get in a risk-free investment like a savings account we'll just call it two percent close bracket divided by the standard deviation of the returns and we have just so happen to have the monthly returns right here so in bracket standard deviation p the monthly returns close bracket times the square root of 12. close bracket that gives us a sharp ratio of 1.22 for our test and drag that over for the benchmark now we need to fix this obviously so the reference to the compound annual growth rate is correct but we just need to change the array inside the standard deviation formula to the monthly returns there we go so now we have our sharp ratios next thing we need is correlation that's easy that's just the correlation of our daily returns so the correlation of the daily returns of our test to the daily returns of our benchmark which is also the spy so we can just punch this in manually here we can see that it's column a b so a b 4 to a b what are we here 1476. done and the correlation for the benchmark obviously it's going to be one 100 because it is the benchmark we'll we'll be uh consistent with our table and put it in anyways so just change the array to match and obviously that's going to give us a correlation of 100 percent format those as percents now maximum drawdown this is a tricky one so just follow me carefully this is why we created these two columns here formula for a real time drawdown from which we derive the maximum drawdown that is i'm just looking at my notes here it's a series of minimums and maximum so minimum bracket bracket the first closing price minus the maximum of again the first closing price x4 colon x4 to create an array now change the first x4 in that array to an absolute cell reference close bracket close bracket divided by max same array again so just highlight that array with the absolute cell reference paste it in there for max close bracket comma 0 close bracket this is going to give us our real time drawdown so we can just drag this or you know what we'll do instead we will come on there we go copy this formula paste it over here and just change the x's to a a to just change the column same formula different column so just change every x in that formula to a a for our benchmark there we go and double click the fill handle to drag both of those formulas down now we have our real time drawdowns and this is pretty cool because we can do a little bit of an extra step here and we can actually add a histogram of our real-time drawdown so select your date column select both your drawdown columns and then go to insert chart area chart and that's going to give us a really cool little histogram for lack of a better term of our real time drawdown so we can actually visualize uh the duration and severity of our drawdowns which is pretty cool now let's just change the x-axis to years format it as years and go to labels and change the label position to low to get it to be on the bottom there and let's make that a bit bigger it's hard to see oh that's too big that should be fine now we just want to make sure the colors of both of these this data set matches the chart above so our benchmark is gray so we just want to make the drawdowns of our benchmark gray as well so just click the orange section and then the paint bucket fill and then just change that color to gray there we go so now we have our equity curve and we have a histogram of our drawdowns and that shows the severity and the duration of drawdowns anyways back to our table here to get the maximum drawdown it's just the minimum value in the respective columns so for the test the minimum value in column ac just click the top of the column there and that will select the entire column click enter and there's our max drawdown for our test format as a percentage and because the columns are next to each other we can just drag the formula over for our benchmark cool now the last metric is beta and that's just the covariance divided by the variance so equals covariance of our arrays so the same arrays as before so the array of our daily returns of our test y4 to y 1476 i believe it was let me just check that yes and then the second array is a b ab4 to a b 1476 covariance divided by the variance of our benchmark so var p is the one you're looking for and then it's just going to be that same red array there which is the daily price changes in our benchmark so you can see now we are given a beta of 0.8 and just to be consistent let's fill beta in for our benchmark even though we know it's going to be one because obviously this is our benchmark so just change that blue array to match the red array so changing the y to the a b that's obviously going to give us a beta of 1 because it is our benchmark but just to be consistent and fill everything in and let's format it the same way too to make it look somewhat nice give it all borders there we go that's going to bug me okay so now we've got our risk adjusted return and we've got our monthly and yearly returns we've got our equity curve and we've got our drawdown histogram that's a pretty good dashboard the only thing we're missing is trades per month so copy that format and let's change this to say trades per month if you'd rather view trades per year you could easily do that too i'll show you how to do it um benchmark is n a because it's a buy and hold obviously but to calculate it for our test we're just going to enter the countif function and then the range just click on the whole of column zed and the criteria is just going to be trade in brackets so now this formula is going to count every occurrence of a trade so every time this column says trade it's going to count it as an occurrence and if we want to get our monthly trade count well we have 5 years of data so 5 times 12 that is 60. so basically just divide this by 60. it's going to give us a very tiny number right now because we haven't actually programmed our rules in so there's only one trade at the start but in a moment when we start testing our strategy then you're gonna see that it's gonna fill in okay so there is our back testing engine complete with dashboard now we're ready to start building and testing some rules so this is a very basic example i'm gonna show you two examples i guess of rules the first is going to be using a relative strength index which is a very common technical analysis indicator and the other one we're going to be using is a proprietary metric of mine that i use in my own trading and i like to call it the three month volatility risk premium that's why i've got the historical data for vix3 i'm here so we'll do rsi first and again i'm not going to explain what either one of these are you should pro most likely if you're watching this video you probably already know what an rsi is and vrp i mean volatility risk premium it's kind of a central concept that drives pricing in financial markets but you'll probably hear me talking about it a lot in future videos but for this video i'm just going to skip over it and just show you how to basically as an example use it in building trading strategies so let's do rsi first so intern calculations for rsi we need positive days negative days just follow along here for sake of time i'm not going to bother explaining it average positive uh what's going on here come on negative rs and rsi okay so for positive days if today's spy price is greater than yesterday's spy price then it went up so we want to return just the difference between the two so today's price minus yesterday's price if not return zero now we're going to do the same but for negative days so if today's spy price is less than yesterday's spy price then yesterday's minus today's to give us an absolute value for what the difference is we don't want any negative numbers here we just want the absolute values um the difference we've basically taken the difference between the days and split them into days when it goes up and days when it goes down double-click your fill handle we're going to do a 14-day rsi so we're going to do a 14 day average so go down to row 18 equals average positive days and since the columns are next to each other copy the formula over double click the fill handle on that rs is just the average positive divided by the average negative that's going to give us our ratio double click the fill handle and last but not least the actual rsi which is basically this normalized on a scale of 1 to 100 also called an oscillating indicator 100 equals 100 minus bracket 100 divided by bracket rs plus one close bracket close bracket so now we have our rsi oscillator on a scale of one to one hundred just uh let's just take a peek at that make sure we did it right uh just highlight that column insert a chart i just want to have a look at it yeah looks like an rsi and then the other metric we're going to use i'll just mark this the actual final products a different shade of orange here so we know not that can barely read it there we go the other indicator we're going to use i said is the three month volatility risk premium so for that let's wow my computer is slow tonight three month vrp so we need to go down to 90 days for three months so go down to row 94 equals today's closing price for vix 3m minus open bracket standard deviation of the last 90 days daily returns of spy close bracket times square root 252 to annualize it times 100 and that's our three month vrp now one thing that i prefer to do when i'm building systems like this is use a instead of an absolute value for an indicator oftentimes using a percentile rank is helpful it's a little more dynamic and it's a little more meaningful because you're basically comparing today's value to all previous values observed it's quite a bit more powerful than just using the absolute value it's not as arbitrary and it also helps a little bit against curve fitting too in my research and in my work anyways that i found so three month vrp percentile rank and that is going to be there's a formula for in excel percent rank inclusive the array this first value here p94 colon p94 change that first p94 to an absolute cell reference comma and then p94 again so obviously now we've created our percentile rank and since this is the first value and the only value it's going to be in the 100th percentile double click the fill handle and now you basically have a percentile ranking for this metric all the way down the line and with each passing day more data gets added and it becomes a little bit more robust and useful so we'll mark that a different color too now we're finally ready to link our rules to our back testing engine and apply them to it so now this is where the testing process begins the last thing we have to do is to avoid curve fitting and to follow the best practices i'm going to talk about momentarily we need to split our data into an in sample and out of sample so going down to the beginning of 2018 that's going to be row 759 you can see i've already marked it yellow here and just break the link in the position column and everything below it just clear it out just get rid of it come on goodness gracious the heck is this about there we go okay okay cool and i've highlighted that whole uh row orange just to mark it your equity curve is going to look a little strange for the time being but you're just going to have to put up with it because it's all part of the process so let's build our rules so in our position cell the first we'll test is the rsi if and the rsi today's rsi is less than 30 typically when you use the rsi a crossover and 30 is a buy signal crossover above 70 is a sell signal so we'll just kind of follow that logic today's rsi value is less than 30 and yesterday so o3 was greater than or equal to 30 that indicates it has crossed over in that case that's a buy signal so that'll uh have us in long triple cube if that's not the case then we'll nest an if statement if that's not the case then we'll test and see if the rsi has crossed above 70. so o4 is greater than 70 and o3 was less than or equal to 70. that indicates cell so we'll move into our tlt close bracket and then if neither of those are true then we'll just assume we're maintaining the same position as yesterday so just return the value of our position yesterday so u3 yes missed a bracket double click the fill handle and there's our first back test so obviously these metrics are going to be incomplete until we've extended it out you want to look at the equity curve very careful and just be cautious because every time you extend your sample out you're compromising your test you want to keep the integrity of it as high as possible so play around with it until you're happy with the rules and only then once you're happy should you be extending it out i'm not going to play around with it too much for the sake of time so let's put this on the back burner here while we test our second rule i'll just put that in the empty cell there clear that out so now our vrp percentile ranking if and and again i've obviously made notes before i prepared this video so i've tested this already if the three month percentile rank is greater than 30 percent and it's less than 80 percent so basically if it's range bound if it's between 30 and 80 we want to be long tlt and if it's outside that range then we can get aggressive and we want to be in triple q so now let's plug that in okay so that's what that one looks like now when you're building rules your rules aren't limited to just what's in this cell you can have as many intermediary calculations as possible as long as you're not curve fitting but at the end of the day all of your logic really needs to funnel into this one decision of which position you're going to be in but you could have dozens and dozens of columns of intermediary calculations right as you can see these are just two indicators and we already have one two three four five six intermediary columns so get creative with it this is the part i cannot show you this is the part you have to develop and the nice thing about using excel as a back testing tool is you literally are only limited by your imagination but this is just a springboard to get you thinking about what metrics you can design your own strategy around so for this tutorial let's just go ahead and extend our data into our out of sample and then we'll compare our two strategies okay so there's our vrp strategy let's compare it quite a bit better than our benchmark quite a bit better sharpe ratio lower correlation better maximum drawdown and it's exposing us to 86 of market risk that's what beta tells us 2.68 trades per month okay copy and let's just paste the values of that there so we can compare it against our rsi that's a percent that's a number that's a percent that's a percent that's a number that's a number and we'll call this rsi now let's try our other strategy i'm sorry this was the vrp and now we're testing the rsi interesting okay so lower compound annual growth rate lower sharpe ratio lower correlation to the s p 500 which is good uh lower maximum drawdown lower beta it's a tough choice obviously i wouldn't pick either of these as a strategy to throw real money at and please don't do that again yet another disclaimer these are just examples these are not by any stretch sophisticated or acceptable strategies these are just showing you kind of a starting off point but so you know rely on many indicators but don't curve fit so that is basically how you back test if you wanted to test a single component of this strategy like if you just wanted to see how much of the returns triple q is responsible for and how much tlt is responsible for you can omit the returns of either one by going into the back testing engine here and just delete this h4 that's capturing the daily returns of tlt so whenever we're holding tlt it'll just be flat now so now you can just see how much of this is contributing to triple q and eye this out see what are my asset class holdings what which etfs am i holding during market environments you know we're long triple q during some pretty scary times in the stock market so you know maybe this actually isn't such a good strategy now we'll put that back and now we'll delete this part for triple q and let's see what tlt is now contributing so this is interesting tlt is actually being held during a lot of the time market times where market is calmer so you probably want to revisit this strategy after seeing this so etf rotation strategies make sure each section is pulling its own weight if you found if you did this and you found out the bond section was actually negative obviously then you shouldn't be holding bonds so this is a good way to kind of filter out and see which part of your rotation strategy is pulling its weight and which one isn't so that is all i really have to show you for this part of the video um i hope you found this valuable and now let's talk about best practices so as you can probably see this is a pretty scientific process and you really just kind of need to be patient and embrace it investing is tough and it takes a really long time to come up with your own strategy i just want to put another disclaimer in here that example that we did it's pretty simplistic and it's definitely not what i would consider acceptable as a strategy to be throwing real money at so just a couple more words of advice here before we talk about our best practices which you absolutely must follow be sure to focus on risk-adjusted return don't just look at the absolute rate of return look at the risk adjusted metrics look at the sharp ratio look at the maximum draw down look at beta ask yourself am i actually going to be able to follow this strategy in real life or am i likely to abandon it if it's super volatile and has massive swings up and down are you really really going to have trust in the system and are you really going to be able to follow it doesn't really matter how good it looks on paper if you're not going to follow it in real life then it's useless also look and make sure that the returns are consistent throughout the years if all of your gains are coming from just one or two years that could be a red flag okay guys it's time for the most important part of the video best practices that you must follow so listen up remember that a back test is backwards looking that's why it's called a back test so when you're looking at historical stock market data in retrospect everything seems really easy oh i should have just bought that and sold that it makes perfect sense now because it's in retrospect you need to be very very aware and cautious of this as you're back testing most back tests will fail in live trading and that is because of a very significant bias that exists when we are working with historical data and that's called curve fitting say it with me curve fitting get this through your head it's important curve fitting is when you start piling on rules to your historical data to try and get the nicest looking equity curve that you can but in reality you're optimizing for past performance so unless the products that you're trading repeat their past behavior the rules that you've developed are not going to work in the future and then you're going to become a victim of the curve fitting bias so what are the best practices that you can use to avoid curve fitting number one the type of rules that you use it's not about the number of rules that you develop per se but how you use them so if you're following indicators that have thresholds don't be too picky with those thresholds don't tweak them just to get the perfect looking equity curve rely on multiple indicators but don't overweight any one indicator too much give them equal weights remember conditional logic that we talked about earlier you could use an or function instead of an and function for example focus less on absolute values and more on percentile rankings like we did in the example because a percentile ranking is going to evolve over time best practice number two remember in our tutorial when we highlighted that cell yellow and split the data what we were doing there was we were dividing our data into two sets one set is going to be called the in sample and one set is going to be called the out of sample so the in-sample data should be about two-thirds or so and the out-sample should be about one-third so take your in-sample data and that's where you play around and optimize your rules muck around with it do everything you have to until you're happy with your in-sample data and then once you're happy with your rules applied to your in-sample data then extend those rules out to the out-of-sample data and see if the performance holds up if your strategy behaves roughly the same in the out-of-sample data as it did with the in-sample data then you can be pretty confident that you've made a good strategy congratulations but if your strategy behaves completely different in your out-of-sample compared to your in-sample then you can be pretty confident that you've curve fitted and you're gonna have to go back to the drawing board and you're maybe gonna have to loosen up your rules it's disappointing i know but trust me it's a lot better than throwing real money at it and finding out the hard way best practice number three pay extra attention to how your strategy performed during tough times for the stock market pay extra attention to how it behaved during the 2008 financial crisis 2015 had two market crashes pay extra attention to how it behaved in 2018 and of course pay extra attention to how it behaved during 2020. the fourth and final best practice that you absolutely must follow is only use etfs that are highly liquid if you use etfs that don't have enough daily volume then you're going to get lots of slippage and you might even have trouble getting your orders filled and obviously this test does not account for slippage basically as a rule of thumb a minimum of about 1 million shares a day in volume and stick with the household names that everybody knows and lots of people trade like y triple q things like that and best practice number five i can't remember if i said there were four or five best practices but anyways best practice number five live trading test drive your strategy in live trading for as long as it takes until you're comfortable with it this is called the walk forward and it's an integral part of building a trading strategy once you're happy with it you're confident that you haven't curve fitted do the walk forward follow your strategy in live trading in a paper trading account compare the live trading results to that of your back test so if you follow those five best practices i can't guarantee success because nothing in life is guaranteed unfortunately but you can be sure that you're well on your way to building your own proprietary trading system and you can be very proud about that and if you want to get kickstarted with quants-based investing head over to tactiletrade.com and jump on my mailing list i'll email you as soon as i'm ready to start sharing signals for my strategies everybody's going to start with 60 days for free if you like this video be sure to give it a thumbs up and if you want to see more quants based investing content then be sure to subscribe to my channel and hit that little notification bell so youtube tells you when i've uploaded a new video i'll leave the links for my videos on beta the sharpe ratio correlation and maximum drawdown in the description as well so be sure to check those out so that you have a full understanding of the risk adjusted metrics and don't forget to check out my website tactiletrade.com thanks for spending the last 10 or so minutes with me i really hope you learned something that's all for now so i'll see you next time

Channel: Tactile Trade

Views: 12,859

Rating: 4.9840956 out of 5

Keywords: Backtest, Excel Tutorial, Excel Finance, ETFs, Trading Strategy

Id: fwO1JwOmHvE

Length: 55min 52sec (3352 seconds)

Published: Sun Nov 29 2020