Excel/VBA Backtesting

[hyunski]

Hi guys,

Was wondering if anyone would be interested in using this Excel/VBA backtesting spreadsheet I've made...I'd like to see if I should clean this up and release it for people to download, learn and test different strategies they have.

Basically it'll pull online data from yahoo finance and create statistics to measure your strategy's effectivenss on stocks. It also has pre-made functions that do things for you like calculate trailing standard deviation, averages, median, etc. Also, it has a screener functionality that basically screens the market for your patterns that you program. This'll be an upgrade to people who like to use FINVIZ, like myself.

But I think the best thing is that you can customize your code and interface any way you want....so you're not limited to what a backtesting software gives you, but you can create anything! Below is an example of a code for a simple moving average golden-cross/dead-cross strategy:

'[ENTRY SIGNALS]
'Filter #1: Uptrend --> 20-SMA above 60-SMA
If i < UBound(c) Then
If ma20(i) > ma60(i) Then
signal_filters(i) = signal_filters(i) + 1
End If
End If


'[EXIT SIGNALS]
'Exit #1: Downtrend --> 20-SMA below 60-SMA
If i <= UBound(c) Then
If ma20(i) < ma60(i) Then
exit_signals(i) = exit_signals(i) + 1
End If
End If

Here is a YouTube video demonstrating the functionalities:
https://www.youtube.com/watch?v=_QSyPCNg4kM&feature=youtu.be&hd=1

And here is what the result looks like:


And here is the interface:


Here is the result of a screener that screens stocks on your strategy:


And here is a large-scale backtester that will check your strategy against all the stocks you selected with a single click of a button:


Would just like to gauge the demand before I dive into this, debug everything and write a manual for people to use...

The target audience would be people who are familiar with excel/vba but are frustrated with the lack of resources to do a healthy backtest of their strategies without paying for it.

Let me know what you guys think!! PM me/reply here if interested!
Oh, and please let me know if something like this already exists.

 

[Jhet]

Hi

I would be very interested in seeing this developed further.

Thanks for your efforts.

Jhet

 

[hyunski]

Here's the current version: http://adf.ly/hztRa

Feel free to play around with it, but don't flip it and try to sell it
You need to edit "Strategy" module in VBA to write your strategy. Comments should explain most things.

I'll get serious when there are more people but this should be enough to get started!
*Hint: haven't found any good trend following strategies that outperform buy and hold...but mean reversion does substantially better

 

[Jhet]

Thank you very much.

I code a little in vba sometimes for work. I can see a huge amount of work has gone into this! This is really quite powerful.

Thanks again.

 

[au2037]

dear hyunski may i please have a copy of this file? stuck on a project and this looks amazing.cheers

 

[$oro$]

VBA backtesting

Hi,

I do most of my backtesting work in MATLAB and would be interested to see how VBA performs for similar functions.

I'll be moving to a new job that wouldn't grant me the request to a matlab license - would be great if I could have a look at your file. Would be more than happy to provide comments or help you with development!

Here's the current version: http://adf.ly/hztRa

Feel free to play around with it, but don't flip it and try to sell it
You need to edit "Strategy" module in VBA to write your strategy. Comments should explain most things.

I'll get serious when there are more people but this should be enough to get started!
*Hint: haven't found any good trend following strategies that outperform buy and hold...but mean reversion does substantially better

 

[hyunski]

Hey, just PM me with your e-mail I'll send a copy.

Matlab is superior to VBA in terms of dealing with data and in-built functions, but I'm sure you'll get used to it!

Good luck!

 

[rameshb345]

Hi Hyunski,

Request you to share the latest excel to me. rameshb345(at)gmail.com.

Please help me on this to use it for back testing.

Appreciate your help.

Thanks,
Ramesh

 

[mrmoreland]

Hi Hyunski,

I am also interested in the latest excel doc, could it be posted to this thread?

Many thanks,

Ian

 

[shahrachit]

Hi Hyunski,

Request you to share the latest excel to me. shahrachit(at)gmail.com.

Thanking you in advance.

Br/Rachit

 

[MarkVincent]

I would be very interested to see the current status and test with gold strategy.
Many thanks in advance.
MV

 

[MarkVincent]

Hi all,

Can anyone confirm if the following link works still as I get nothing:

Here's the current version: http://adf.ly/hztRa

Br MV

 

[MarkVincent]

ping... i wanted to play a little this weekend if possible..
Thanks for any functional links
Br Mak

 

[BP48072]

Hyunski, could you please provide a working file url to download from? I have tried all the links provided in this thread but they all indicate "file was deleted".

Thanks.

 

[tradethetrade]

This is my first post here so I won't spam with links, but there are much better tools to do basic and advanced backtesting that does not require programming at all. Even if you are really good at VBA, you'll get stuck at VBA's speed and architecture that were not designed for trading at all. Look up sites for algo trading and you find a ton of languages made exactly for trading. I coded a few robots myself using mql5.

 

[hyunski]

Hi guys,

This post was made three years ago and hence I no longer have the code. I would suggest using a more powerful interface such as Quantopian, although you won't be able to be flexible with your assumptions. For those wondering what happened to me since I went quiet I've turned into a discretionary trader and made back my losses over the past few years and as such I found no value in redeveloping this backtester.

Ignore below if disinterested other than backtester.

Looking back I would say a few things for people who are really struggling:
1. Don't use leverage until you know what you're doing, this includes derivatives.
2. Don't short unless you diversify into 20+ stocks (so not at all)
3. Only bet when you have 1:3 risk/reward and 80%+ probability of winning
4. In the beginning, you will be unable to gauge risk/reward and probability properly. It took me 3.5 years to do so, and it took me 3 years to find what I was good at.
5. Add on the way up, cut your losses at 5-7% no excuses.
6. Look beyond prices. If your job is to forecast the rain and the only thing you look at is the humidity in the air, you're missing out on dozens of other variables and you'll be mystified as to why humidity is not working as an indicator.

Here are examples of things I started seeing after I developed my edge and experience:
1. A characteristic of a super theme is that 2-3 books about it will appear in the bookstore at the same time. This is a confirmatory lagging indicator.
2. Super themes are generally uncorrelated to the market.
3. When the Belgian terror attack happened in 2016 and the French index fell more than the Belgian index, it was a signal to buy the dip on the French index.
4. Global correlations across futures, currencies, etc start to make sense after you watch it every day for a year. Ex. Oh, a falling dollar is holding oil up vs the index.

At the core of trading I realized that you can structure the game to win. If I gave you $1,000, sent you to a casino with hundreds of tables and you can jump in and out of each table without paying the blinds, an obvious strategy is to only play when you see almost unbeatable hands like pocket aces. That should give you 1:3 risk/reward and 80% odds of winning. Now let's say you had a rule where you only risked 5% of total equity on each hand. How can you lose???

The only reason people lose is because they're playing marginal hands from boredom and greed, not managing risk or playing hands despite the fact that they can't tell the difference between pocket aces and a 2-3 in their hands. If you play small in the beginning, develop an edge (the ability to estimate r/r and probabilities) and bet small, you literally can't lose.

Long post, hope this helps.