How to backtest excel with trading view data (1)

-Cleaning the data and finding the trading return/annual average return/cumulative return

 

Export data from trading view.

 

Paste it into Excel.

I only need the date and trading record, so I will delete the rest.

 

Add the rate of return/cumulative rate of return/highest point/highest drop as shown below.

The rate of return is calculated according to the formula below.

Yield = ((Selling Price - Buying Price) / Buying Price) - (Fees & Slippage)

*Binance-based fee is 0.1% and it is calculated as 0.2% for round-trip. Slippage not included.

 

convert to %

 

If you double-click the small square dot in the lower right corner, it will be dragged to the bottom.

 

Can you see it?

 

It then calculates the cumulative return.

Cumulative return = initial investment *( 1 + return)

* The initial investment is set to 1 for easy viewing.

Similarly, double-click the square point and drag it all the way to the end.

 

Calculation of return and cumulative return is complete.

 

Now let's draw a cumulative return chart. Let's see how it works.

 

First drag them all

 

Click Filter on the right.

 

Click Filter under Cumulative Return, click No field value, and click OK.

 

Then all blanks will be removed as shown below.

 

Click on Cumulative Return

CTRL + SHIFT + Down Arrow will drag the entire area.

In this state, click "Insert" at the top.

 

And click on Recommendation Chart.

 

Just select the chart you want and click "OK".

 

Chart complete!

 

Find the average annual return on the investment

Duration = (last date invested - first date invested) / 365 days

 

Cumulative return = most recent balance

Average annual return = cumulative return ^ (1 / investment period) - 1

 

About 120% annual compound interest for the past 3 years! It's crazy haha

Next time I will show you how to calculate the highs and lows!