How to backtest if you do not know how to code

[userque]

Coding not so hard.

Well, that depends. It depends on the 'for whom?' question. And it depends on the 'what are you coding?' question.

If you were above average at Maths at school then you should be able to learn enough coding to do backtesting.

Right...above average. Most of those folks are already drawn to coding as a general principle.

You do not need advanced coding skills to write a backtesting program.
Just get that bar data into memory and iterate over it.

It depends upon how complex the underlying system is. If you're talking MA's, ok. But what about a machine learning system that randomly accesses historical data? [Or an Excel system that utilizes Excel Solver, etc.] Those are rhetorical questions. [I know the answers already.]

 

[Iwilldoit]

Yes, you can backtest manually.

lol so what I am asking is how do I backtest manually?

please explain.

@userque

 

[userque]

lol so what I am asking is how do I backtest manually?

please explain.

@userque

That depends a lot on your spreadsheet. When I did what you are trying to do:

My data was organized as new bars first--near the top of the sheet. The sheet logic worked on the newest bar.

In order to convert it for manual backtesting, I inserted a row above the latest bar. The data in this new row could 'point' to any other row. I used a spin button to change the row it pointed to. I modified the sheet to generate the signal based upon this 'variable' row instead of always on the 'top' row.

To backtest, I would simply click the spin button to 'go back in time.'

This is a very simplified account. But it presents the crux of what you need, based on I don't know anything about your sheet.

 

[Iwilldoit]

That depends a lot on your spreadsheet. When I did what you are trying to do:

My data was organized as new bars first--near the top of the sheet. The sheet logic worked on the newest bar.

In order to convert it for manual backtesting, I inserted a row above the latest bar. The data in this new row could 'point' to any other row. I used a spin button to change the row it pointed to. I modified the sheet to generate the signal based upon this 'variable' row instead of always on the 'top' row.

To backtest, I would simply click the spin button to 'go back in time.'

This is a very simplified account. But it presents the crux of what you need, based on I don't know anything about your sheet.

@userque

anyway you could PM me an example?

 

[userque]

@userque

anyway you could PM me an example?

I'll PM you.

EDIT:

Actually, I won't be revealing any secrets if that was the concern. I'll post here shortly.

 

[userque]

@userque

anyway you could PM me an example?

Hi, how is your system laid out?

When it generates a signal, what row/data is it working on?

Here is an example of what I was talking about:

Original OHLC data:

01/30/99 1 2 3 4
01/29/99 5 6 7 8
01/28/99 9 0 1 2
01/27/99 1 2 3 4
01/26/99 5 6 7 8
01/25/99 9 0 1 2

Now suppose a system that uses the last three days of data. Here's how I would rearrange the data:

01/30/99 1 2 3 4 5 6 7 8 9 0 1 2
01/29/99 5 6 7 8 9 0 1 2 1 2 3 4
01/28/99 9 0 1 2 1 2 3 4 5 6 7 8
01/27/99 1 2 3 4 5 6 7 8 9 0 1 2
01/26/99 5 6 7 8 9 0 1 2
01/25/99 9 0 1 2

The last (oldest) two rows are ignored as they are incomplete. The sheet/system does its calculations on the top row only in order to generate a signal. So, how do we manually backtest this?

Insert two new rows. In A1, type 2.
In A2, enter:

=offset(a1,$a$1,0,1,1)

Drag copy that so it is above every column that has data (=offset(b1,$a$1,0,1,1)...=offset(c1,$a$1,0,1,1)...etc.). Your data should look like this:

2
01/30/99 1 2 3 4 5 6 7 8 9 0 1 2
01/30/99 1 2 3 4 5 6 7 8 9 0 1 2
01/29/99 5 6 7 8 9 0 1 2 1 2 3 4
01/28/99 9 0 1 2 1 2 3 4 5 6 7 8
01/27/99 1 2 3 4 5 6 7 8 9 0 1 2
01/26/99 5 6 7 8 9 0 1 2
01/25/99 9 0 1 2

Now, insure the sheet still does the calculations on the new top row. (Instead of inserting the rows initially, maybe sliding the values down via a copy-by-values would keep your formulas pointing to the 'new' row without modifications.)

Now, change A1 to 3. You should then see this:

3
01/29/99 5 6 7 8 9 0 1 2 1 2 3 4
01/30/99 1 2 3 4 5 6 7 8 9 0 1 2
01/29/99 5 6 7 8 9 0 1 2 1 2 3 4
01/28/99 9 0 1 2 1 2 3 4 5 6 7 8
01/27/99 1 2 3 4 5 6 7 8 9 0 1 2
01/26/99 5 6 7 8 9 0 1 2
01/25/99 9 0 1 2

Notice that the top row has changed. Your sheet should now be doing calculations on this 'new' data. You can add a spin button to make it easier/quicker to go back in time. You have to manually log the results of each calculation.

But, you can make a macro to log the calculations for you. Then, you can decide to automate the whole process...at that point, you would have completed your simple backtesting system. You would have gone from manual to automated. But manual may be sufficient...depending upon how much testing you plan to do.

 

[marsman]

adventerous...
I just don't understand why the OP still asks others if he can do it w/o any programming as he claims...

 

[learner2007]

lol so what I am asking is how do I backtest manually?

Guys like me from the old generation still do it the old school way. We just go back over old charts and pick out 100, 200, 300 or more examples of the set up in question and count the number of times it worked out, failed, or allowed us to get out even. Based on those results we then know the probability. If it is high enough we will trade the set up and will know what to expect in 100 actual trades. Been doing this for 40 years. And one of the benefits of doing it this way is that you learn that much more about the set up by visual examination which you wouldn't get if a computer was doing it, and if the computer could really recognize the set up along with the context properly.

 

[Iwilldoit]

adventerous...
I just don't understand why the OP still asks others if he can do it w/o any programming as he claims...

I am asking how to manually backtest, since I do not know how to code

@marsman

 

[Iwilldoit]

Guys like me from the old generation still do it the old school way. We just go back over old charts and pick out 100, 200, 300 or more examples of the set up in question and count the number of times it worked out, failed, or allowed us to get out even. Based on those results we then know the probability. If it is high enough we will trade the set up and will know what to expect in 100 actual trades. Been doing this for 40 years. And one of the benefits of doing it this way is that you learn that much more about the set up by visual examination which you wouldn't get if a computer was doing it, and if the computer could really recognize the set up along with the context properly.

That is essentially, what I do in excel:

1. Look at the setup
2. Record entry price in cell, see where price goes next on the chart, and then record the exit price (whether it may be stop or target order)
3. Then there are formulas which calculate, the win ratio and net overall profit over a period of x trades.

@learner2007 @userque @marsman