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
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 |