Running gain/loss calculation through simple backtest

[spark.tsang]

Column B is the lowest price which happened at the time as indicated in column C. Similarly column D is the highest price which happened at the time as indicated in column E.

Let's say we have a long term strategy giving buying and selling suggestions at certain predetermined prices each year, as in columns F and G, which may or may not be reached in reality. Column H and I denote whether the suggested prices are reached or not.

Now in order to test the performance of such strategy, we have column J which calculates the winning or losing percentage whenever a selling is done. Since it's actually bought at F2 and sold at G2 (with the valid chronology of E2>C2), J2 is calculated by G2/F2-1.

At F3 it's bought again. G3 isn't reached thus it's not sold then. At F4 it suggests buying again but we already bought so there's no effect. At G4 we reach the suggested price and sell it. Thus the outcome is calculated at J4.

Similarly the next buying happens at F5 and the next selling happens at G6, resulting in J6. At G7 it's suggested to sell but we have no stocks to sell. Note that G7 happens before F7 as deduced from the high/low dates (E7<C7). The price of F7 is reached but we don't have a valid selling until now so there's no complete trade to be calculated for this single buying.

We need a formula for J2:J8 (as well as other sets of data arranged in a similar manner) to automatically calculate this. Any help would be very much appreciated!

[AliGW]

Administrative Note:

We would very much like to help you with your query, however the thread title does not really convey what your request is about.

Please take a moment to amend your thread title. Make sure that the title properly explains your request. Your title should be explicit and not be generic (this includes function names used without an indication of what you are trying to achieve).

Please see Forum Rule #1 about proper thread titles and adjust accordingly. To edit the thread title, open the original post to edit and then click on Go Advanced (bottom right) to access the area where you can edit your title.

(Note: this change is not optional. No help to be offered until this moderation request has been fulfilled.)

[spark.tsang]

Would the new title do? Please inspire me or just manually change it in any way you like if not, thanks a lot.

[AliGW]

It's better. It is YOUR responsibility to ensure that your thread titles meet the forum requirements - please make sure they do in future. Thanks.

[spark.tsang]

OK, I strive to be a law-abiding user. Now hope I'll get an answer soon!

[Glenn Kennedy]

I'm an analytical chemist. So... your explanation makes no sense to me.

Please explain in LOGICAL, mathematical terms

In J4 you need a calculation, because H4 and I4 are TRUE and E4>C4.

The numerator is G4 because it (or something else) is higher/lower than another value... What value? Which cell? Why?


The denominator is F3 because it (or something else) is higher/lower than another value... What value? Which cell? Why?

[spark.tsang]

Thanks for the reply. Let me explain from the beginning.

J2=G2/F2-1, because
1. I2=TRUE, a trade is completed (with an exception I'll explain), we now need to calculate its outcome as J2
2. No selling has happened before I2. There's no such x where I2>Ix and Ix=TRUE. If there's a selling beforehand, the calculation process of J4 would be applied.
3a. H2=TRUE, there is no such x that Hx=TRUE and H2>Hx, indicating the buying happens at H2 if the following criterion is also fulfilled
3b. E2>C2, indicating H2 happens before I2 not vice versa, confirming H2 is the buying point of the trade.
4. As a result, the denominator is (F2-1) since the buying happens at H2.

J3="", because
I2=FALSE, no trade is completed, no outcome can be calculated.
The same goes for J5, J7 and J8

J4=G4/F3-1, because
1. I4=TRUE, a trade is completed (with an exception I'll explain),
2. Latest selling happens at I2. In mathematical terms, there's an x where I4>Ix and Ix=TRUE and MAX(x)=2
3. The buying point of this trade happens at H3. This is deduced by:
a. I2 which happens around E2 is the latest selling point.
b. There's an Hx such that Cx>E2 and Hx=TRUE.
c. MIN(x)=3, so the trade happens at H3
4. As a result, the denominator is (F3-1) since the buying happens at H3.
The same goes for J6.

Finally let's go to the mentioned exception (which has not happened in the worksheet). Let's assume H2 is FALSE.
Then, the desired outcome would be J2="", because the single selling does not have a buying point, no trade has been done.

Also, due to the same reason, if H5=H6=FALSE, J6="" too.
In mathematical terms for this case,
1. There's an x where I6>Ix and Ix=TRUE, MAX(x)=4. We'll take I4 as the latest selling point. It happens at around E4.
2. There's NO Hy such that Cy>E4 and Hy=TRUE. (since here we assumed H5=H6=FALSE)

Is it clearer now? Much appreciated.

[spark.tsang]

Based on the clarification above, I think the solution can be broken down to two crucial parts:

1. At a cell Jx, what's the value of y such that y=the maximum possible value of all z where (a) Iz=TRUE and (b) z<x?
For example, at J6, that should be 4, where I4<I6, I4 is TRUE and while I2 is also TRUE and <I6, I4>I2, thus y=4.

2. At a cell Jx, given y=4, what's the value of a such that a=the minimum possible value of all b where (i) Hb=TRUE and (ii) Cb>Ey?
For example, at J6, that should be 5, since C5>E4, H5 is TRUE and while H6 is also TRUE and fulfills the other criteria, H5<H6, thus a=5

The final formula of Jx=Gx/Fa-1. Simple and easy right?
It'd be much appreciated if anyone could enlighten me on writing the above formula.

[spark.tsang]

Bravo, got my desired outcome already on working alone.

[spark.tsang]

I hope I could add reputation to myself!