Thread by @hackertrader

No Code Backtesting in Excel. Long guide.

Following recent discussion started by @TheShortBear regarding backtesting OHLC daily data -

Here are Exact formula’s, locates + fees & optimization to backtest your strategy (google sheets link at the end) 👇

Contrary to what many traders believe, OHLC data is enough to find an edge shorting small caps.

And today with Spikeet and excel - you can actually do it in under 10 minutes without coding skills.

First, some caveats for this method:

1. Intraday halts above your stop loss aren't reflected in OHLC data.

2. Unrealistic locates fees.

3. Serious overfitting risks - keep it simple
Missing intraday alpha.

But even with this disadvantages, it’s probably better than guessing based on your biased memory.

So here is a step by step guide:

1. Take a template you are interested in using @_spikeet. In this example I chose the small cap gappers.

2. Download the data to excel.

3. Choose a universe based on behavior. This will help you a lot in the optimization process.

Different universe = different behavior!

4. For this example, I will take all listed stocks under 500m market cap and more than $1 price that gapped more than 20% and PM volume more than 50k. That last one will save you from most of the un-tradable ones.

5. Add stop loss column. For this example I will use a 20% stop loss from open

6. In a new column - calculate the amount of shares to take.

For this example I will risk $100 per trade [100 / (SL - Open)]

7. Calculate your gross PNL. If high of day is above stop loss cover at stop loss, else - cover at the close.

8. Reduce fees.

I use 0.002 per share but you can use anything that is realistic for you.

9. Reduce locates.

There’s no exact science for this. I use a random function between 0 and 10% of my PNL.

Use whatever you see fit based on experience.

10. Now, insert a PNL chart:

11. Now the fun part that makes the strategy profitable.

Sort by different columns and see where the edge is by watching the PNL curve. I will optimize based on gap% and price:

You can clearly see that anything below $2.5 doesn’t really work.

Optimize based on gap%:

You can see that anything above 80% gap is getting parabolic!

So we got a strategy that:

1. Shorts the open if gap above 80% and price above $2.5

2. Cover at stop loss (20% from entry) or end of day.

Here is a google sheet with all the formulas:

docs.google.com/spreadsheets/d…