An old problem

[Dave]

Still no solution to the problem so let me have another go
at explaining it with another example.

I have data on a stock (IBM for eg) and I have a simple
rule if the 5-day average is above the 10-day average then
I buy if it is below then I sell. So I simply have a
colomn that says whether I should buy or sell without
getting into what the price of IBM is.

How do I work out the profit/loss of this simple rule
given that the rule can say buy or sell at any point in
time?

If there is anyone that can help that would be great...

Dave

 

[Peter Atherton]

Dave
Try something like this

if(average(B1:b10)>average(b6:B10),"Sell","Buy")

regards
Peter

 

[Guest]

Hey Peter,

I wanted something for the next stage which is once you
have a colomn of sell and buy how do you get excel to work
out your profit by finding when yo0u went from buy to sell
and then back to buy over time.

 

[Peter Atherton]

Dave

Try something like a cumulative sum. Test layout

Price Buy Date Sell Date Qty Value Cu Sum
23.15
23.45
32.12
29.12
33.12
36.45
33.23
34.45
27.89
28.56 Buy 23/02/04 2300 -65688 -65688
27.21 Sell 24/02/04 1500 40815 -24873
31.23 Sell 25/02/04 500 15615 -9258
33.23 Sell 26/02/04 300 9969 711

Value Formula =IF(C13="Buy",B13*F13*-1,B13*F13)
First Cusum =G13
Folling CuSums =G14+H13

Hope you can work out something suitable from this

Regards
Peter