how to backtest your trading strategy in excel

By Jacques Joubert

Now for those of you WHO know me as a blogger mightiness find this post a minuscule unorthodox to my traditional style of writing, however in the spirit of evolution, inspired away a friend of mine Stuart Thomas Reid (TuringFinance.com), I will personify following some of the tips suggested in the following web log post.

Being a student in the EPAT platform I was excited to get a line the methodological analysis that others make use of when information technology comes to backtesting. As usual, we start off in by backtestingExcel and then migrate to R.

Having previously scrawled a blog serial happening backtesting in Excel and so moving to R, I was precise interested to see a slightly different method acting used by the QuantInsti team.

Please download the Excel spreadsheet so that you can follow the good example as we go along.

"By calculating transaction prices it opens ahead approximately very interesting doors for implementing MAE analysis"

The unitary primary difference in the method is that it opens the door for performance metrics like:

• Total Positive returns
• Negative Returns
• Positive trades
• Negative trades
• Hit Ratio
• Average return
• MAE (Maximum Adverse Digression)

But suffers from not being able-bodied to plot an equity curve like my original method acting (that I like to think of us a vectorised backtest), you can however, easily incorporate the equity curve, American Samoa I did in this post.

Build the "Hello International" of trading strategies: the "Long Short Moving Average Crossover Scheme".

Gradation 1: Get data

There are several places from which you can get data, however for this example we will get data from Yahoo Finance. I will be building this example using Google as a share.

Price data from Yahoo in CSV file in format

Step 2: Make up a editorial for some the long and the short simple vibratory average (SMA)

For this example I wish you to wee use of the 5 and 25 day SMA. For those of you who are novel to trading strategies, a SMA is simply the total sum of closing cost divided by the number of observations.

2.1) Create the short full term SMA (5 days)

Using the following formula in Excel: =Mean(G2:G6)

2.2) Create the long term SMA (25 Days)

Using the following formula in Excel: =AVERAGE(E2:E26)

Step 3: Engender trading signals

It is at this tone where readers will pick up on a major difference from my preceding blog posts on building a vectorised backtester. I will incorporate my original methodological analysis in this post as well in order to plot the equity curve.

The next thing we need to do is to generate bribe and sell signals

Logic for Pace 3:

If:

In the late day the (5)SMA was to a lower place the (25)SMA and in the current day there is a change where the (5)SMA is straight off above the (25)SMA,

Then:

Write the string "BUY" in the current field

Else If:

In the previous day the (5)SMA was to a higher place the (25)SMA and in the current day there is a change where the (5)SMA is now below the (25)SMA,

Then:

Write the string "SELL" in the current field

Else:

Hyperkinetic syndrome an empty string "" to the current field.

This is delineate in Excel using the following formula:

= IF(AND(H26dangt;I26,H25danlt;I25),"BUY",IF(AND(H26danlt;I26,H25dangt;I25),"SELL",""))

Annotation:

The SMAs are calculated on closing prices and non adjusted close because we want the trade sign to be generated on the price data and not influenced by dividends paid.

Step 4: Get purchase / merchandising terms of the trade

In the next column add the following Excel formula: =IF(J26danlt;dangt;"",G27,K26)

The logic is as follows:

If the trade wind signaling column for the previous day (Very important to lag the indicant as to remove look-ahead bias) is not an empty string up then make usance of the preceding price higher up the current field, else set the current orbit to the closing price for the day.

Note:

Around may argue that you can't actually get the ambient for the day but you can if you put your rules of order in at the closing auction, and even later on the auction there are some residual orders that you can fill, uncomparable of the old funds I worked for did on the dot this.

Step 5: Calculate returns

Add a column named returns that makes utilization of the following Excel Expression: =IF(J26="Trade",K27/K26-1,IF(J26="BUY",1-K27/K26,""))

Logical system:

If the previous Clarence Day generated a SELL signal then take today's closing price and divide it by the purchase price and deduct 1.

Else If the previous Clarence Day generated a BUY signal then add 1 and subtract (today's closing Mary Leontyne Pric and divide it by the purchase price).

This normal calculates the returns for a given trade.

Step 6: Calculate some performance metrics

• Positive returns: =SUMIF(L:L,"dangt;0")
• Negative returns: =SUMIF(L:L,"danlt;0")
• Positive Trades: =COUNTIF(L:L, "dangt;0")
• Negative Trades: =COUNTIF(L:L, "danlt;0")
• Hit Ratio =O4/(O4+O5)
• Average Returns =AVERAGE(L:L)

These aren't the long-standing portfolio performance metrics but by conniving the purchase and asking price it opens up some rattling interesting doors for implementing maximum adverse excursion analysis that privy be wont to optimise blockade losses.

"Note: I wasn't able to reckon these prosody in my previous method due to not having canned the purchase and sales agreement prices of minutes."

Adding an Equity Curve

Footmark 1: Add cardinal new columns for the Daily returns and the natural log up daily returns of the part

For this I volition nominate use of the adjusted closing Leontyne Price as I deprivation dividends prepaid to be reflected in our strategies equity curve and total return profile.

Formula for Regular Returns is: (Today's Price / Yesterday's Price) - 1

Excel rule: =G3/G2-1

The formula is use for the cancel backlog daily returns is: LN(Today's price / Yesterday's price)

Excel formula: = LN(G3/G2)

Step 2: Work out the Long or Momentaneous holdings signals

In this column we want to know if we are currently holding a long or a pint-sized position. This is represented by 1 for long and -1 for short.

This builds on the wriggling average cross over strategy by going long if the short-range term SMA is above the long term SMA and abbreviated if the opposite is veracious.

"Note: you birth to lag the signals away one day in order to remove look-ahead bias."

In this example the Excel convention is intrinsically: =IF(H26dangt;I26, 1, -1)

Step 3: Figure Strategy ln Daily Returns

This is the impressionable part, simply multiply the natural log up daily regaining by the current stead.

Stand out formula: =R27*S27

Step 4: Compute the cumulative returns for both the strategy and the divvy up as if you bought and time lag. (Fare this to act as a comparison)

The formula to cumulate returns is simple, for LN returns you merely add them using =T27+U26.

Adjacent you need to turn off the unbleached log up using the following expression: =EXP(U27)-1

And then you need to account the stocks cumulative returns:

Excel formula =(1+Q27)*(1+Q26)-1

Step 5: Plat the returns

Eastern Samoa can be seen from the chart above, this strategy ISN't a profitable one on this particular prison term cast and share but this . I would encourage readers to explore other trading strategies by trying to incorporate the RSI indicator to act as a guide how to size a position.

Next Stairs

Later on backtesting in Excel, lbring in to importation and backtest on Zipline using data from Google and OHLC data in CSV format. Calculate backtesting results so much equally PnL, come of trades, etc. Click Hera to start directly.

Update

We have noticed that some users are facing challenges while downloading the market data from Yahoo and Google Finance platforms. In case you are looking for an alternative source for market data, you can employdannbsp;Quandldannbsp;for the like.

how to backtest your trading strategy in excel

Source: https://blog.quantinsti.com/vectorized-backtesting-in-excel/

Posted by: taylorshissing87.blogspot.com

Share this post