Storing data to be used in conditional calculation

[starkc69]

Hello All - First off, thanks in advance for any help. This is probably very simple, but eludes me. Ideally I wanted to do this with an embeded Excel function but willing to try it in a macro, VBA last resort. Goal is to run some backtesting against various data dumps with many buy/sell columns. For simplicity, I'm using one buy and one sell column and hope I can scale this solution.

Problem - Running chronologically down the timeline find the first non-zero value of 'Buy' identified and store it. Continue running chronologically down the timeline find the first non-zero value of 'Sell' identified and at that timestamp dump the result of the Stored 'Sell' - Stored 'Buy' into the P_L column. Repeat this process down the timeline until data ends, similar to the example below.

Kindest Regards,
Captain 'Can't see the forest through the Trees' starkc69

	Buy	Sell	P_L
Time
9:31 AM	$ 7.50	$ -
9:32 AM	$ -	$ -
9:33 AM	$ -	$ -
9:34 AM	$ 7.65	$ -
9:35 AM	$ 7.75	$ -
9:36 AM	$ -	$ -
9:37 AM	$ -	$ 8.00	$0.50
9:38 AM	$ -	$ 7.60
9:39 AM	$ -	$ 7.45
9:40 AM	$ -	$ 7.68
9:41 AM	$ -	$ 7.67
9:42 AM	$ -	$ 7.58
9:43 AM	$ -	$ 7.33
9:44 AM	$ -	$ -
9:45 AM	$ -	$ -
9:46 AM	$ 7.00	$ -
9:47 AM	$ 7.02	$ -
9:48 AM	$ -	$ -
9:49 AM	$ -	$ -
9:50 AM	$ -	$ 7.17	$0.17
9:51 AM	$ -	$ 7.15
9:52 AM	$ -	$ 7.10
9:53 AM	$ -	$ 7.12
9:54 AM	$ -	$ 7.09
9:55 AM	$ -	$ 7.03
9:56 AM	$ -	$ 6.90
9:57 AM	$ -	$ 6.89
9:58 AM	$ -	$ 6.83
9:59 AM	$ -	$ 6.89
10:00 AM	$ -	$ 6.81
10:01 AM	$ -	$ 6.73
10:02 AM	$ -	$ 6.70
10:03 AM	$ -	$ 6.79
10:04 AM	$ -	$ 6.90
10:05 AM	$ -	$ -
10:06 AM	$ -	$ -
10:07 AM	$ -	$ -
10:08 AM	$ -	$ -
10:09 AM	$ -	$ 6.68
10:10 AM	$ -	$ 6.65
10:11 AM	$ -	$ 6.89
10:12 AM	$ -	$ 7.03
10:13 AM	$ -	$ -
10:14 AM	$ 7.35	$ -
10:15 AM	$ 7.45	$ -
10:16 AM	$ 7.34	$ -
10:17 AM	$ 7.30	$ -
10:18 AM	$ 7.12	$ -
10:19 AM	$ -	$ -
10:20 AM	$ -	$ -
10:21 AM		$ 6.68	-$0.67
10:22 AM		$ 6.65

 

[Fluff]

How about

+Fluff v2.xlsm
	A	B	C	D
1		Buy	Sell	P_L
2	Time
3	9:31 AM	7.5	0
4	9:32 AM	0	0
5	9:33 AM	0	0
6	9:34 AM	7.65	0
7	9:35 AM	7.75	0
8	9:36 AM	0	0
9	9:37 AM	0	8	0.5
10	9:38 AM	0	7.6
11	9:39 AM	0	7.45
12	9:40 AM	0	7.68
13	9:41 AM	0	7.67
14	9:42 AM	0	7.58
15	9:43 AM	0	7.33
16	9:44 AM	0	0
17	9:45 AM	0	0
18	9:46 AM	7	0
19	9:47 AM	7.02	0
20	9:48 AM	0	0
21	9:49 AM	0	0
22	9:50 AM	0	7.17	0.17
23	9:51 AM	0	7.15
24	9:52 AM	0	7.1
25	9:53 AM	0	7.12
26	9:54 AM	0	7.09
27	9:55 AM	0	7.03
28	9:56 AM	0	6.9
29	9:57 AM	0	6.89
30	9:58 AM	0	6.83
31	9:59 AM	0	6.89
32	10:00 AM	0	6.81
33	10:01 AM	0	6.73
34	10:02 AM	0	6.7
35	10:03 AM	0	6.79
36	10:04 AM	0	6.9
37	10:05 AM	0	0
38	10:06 AM	0	0
39	10:07 AM	0	0
40	10:08 AM	0	0
41	10:09 AM	0	6.68
42	10:10 AM	0	6.65
43	10:11 AM	0	6.89
44	10:12 AM	0	7.03
45	10:13 AM	0	0
46	10:14 AM	7.35	0
47	10:15 AM	7.45	0
48	10:16 AM	7.34	0
49	10:17 AM	7.3	0
50	10:18 AM	7.12	0
51	10:19 AM	0	0
52	10:20 AM	0	0
53	10:21 AM	0	6.68	-0.67
54	10:22 AM	0	6.65
Main

Cell Formulas
Range	Formula
D3:D54	D3	=IF(AND(C3<>0,C2=0),IFERROR(C3-INDEX($B$2:$B3,AGGREGATE(15,6,(ROW($B$2:$B3)-ROW($B$2)+1)/($B$2:$B3>0)/(ROW($B$2:$B3)>LOOKUP(2,1/(C$1:C2>0),ROW(B$1:B2))),1)),""),"")

 

[starkc69]

Thank you. I will give that a try and get back to you.

 

[starkc69]

That did the job. Thank you much!

 

[Fluff]

You're welcome & thanks for the feedback.