How to backtest with trading view indicator backtest data excel (3)
2022.01.19 01:19
How to backtest with trading view indicator backtest data excel (3)
- Get bitcoin price data, compare buy-and-hold and backtest
Now, I will show you how to compare the Bitcoin buy-and-hold and backtest results of trading bots. It is important to note that if you look at your trading history, you can see that trading bots do not trade every day.
As some of you may already know when implementing a chart, the X-axis is the date (time) and the Y-axis is the yield. First of all, the performance of the same period can be compared when the X-axis is the same. If you classify the performance of unspecified trading bots by month, and compare and visualize the yield of buying and holding Bitcoin on a monthly basis, you can see it at a glance.
In this process, as it is not as detailed as unit data, the resulting rate of return is the same, but you can see that the data is buried as MDD classifies data by month. In fact, when calculating the annual compounded rate of return and MDD of a strategy, it is recommended to analyze it with single-unit data, and when comparing the excess return (Bitcoin) compared to the index, it is recommended to analyze it with monthly data. This allows you to recognize the best MDD and at the same time know how much alpha you've created.
So shall we start?
Go to investing.com and get the Binance Bitcoin price.
Please click the Binance Bitcoin Price Data link.
***You must log in with Gmail or Facebook to get data.
Select Time Frame as Monthly.
Click Download data.
Open the downloaded file.
Date/Closing Price/Open/High/Low/Volume/Return is displayed as shown below.
Blows out data other than date and price.
As we learned before, we're going to drag the date and price data to modify the dates in ascending order.
Click Filter.
Sort the date in ascending order.
huh! However, it is not in the correct format, so you can see that it is sorted by year followed by month as shown below.
In this case, return the date to the original position, drag the date, and click "Detailed date".
Then, list the bottom three cells in the order 2017/09/01 , 2017/10/01 , and 2017/11/01.
And drag it upwards.
Then sort the date again in ascending order.
completion! The reason why it is set to 1st here is that it is cumbersome to edit each month because the last day of each month is different. Although it is 1 day, in fact, all prices are based on the end of the month.
Open the Excel that you backtested before.
To preserve previously backtested data
Copy the data in sheet1 and paste it into sheet2.
Paste done!!
In the time frame, it deletes all data except for the data closest to the end of each month.
(what a labor...)
Then copy and paste the Bitcoin price next to it.
It calculates the rate of return/accumulated rate of return/highest point/maximum fall in the same way as the backtest when operating as a trading bot.
Drag each cumulative return and peak.
Implement it as a chart.
Wala!! Buy and Hold VS Trading Bot Performance Comparison Completed!
Period: September 2017 - July 2020
Asset Class: Bitcoin
Strategy: Buy and hold
Cumulative return: 233%
Highest drop: -75%
Period: September 2017 - July 2020
Asset Class: Bitcoin
Strategy: Volatility Breakthrough Trading, k value 0.65 (This is the value I use after backtesting)
Cumulative return: 994%
Highest drop: -16.58%
What a difference!