I have a list of the stock's market transactions in chronological order. Based on the size of each transaction, I mark some of them as "big" transactions, and I'd like to analyze how the market reacts in the next 5m, 15m, 1h, 6h, 24h, 48h intervals, in relation to those "big" transactions. So next to each one of those big transactions I need to calculate a minimum and maximum price point, which are the points to which the price fluctuated during each of the time intervals mentioned above, after a big transaction occurs.
I'm guessing that for this I would need an iterative macro (i could be wrong), but I'm not very experienced with them yet. Can someone provide an example below?
Example of my input data:
| DateTime | BigTransactionID | Size | Price | RecordID |
2021-01-25 13:41:44 | | 0.5 | 132.34 | 1 |
| 2021-01-25 13:46:50 | | 1.3 | 132.38 | 2 |
| 2021-01-25 13:55:53 | 1 | 52.3 | 133.53 | 3 |
| 2021-01-25 13:59:55 | | 0.8 | 133.55 | 4 |
| 2021-01-25 14:11:56 | | 2.3 | 133.32 | 5 |
| 2021-01-25 15:31:44 | 2 | 87.2 | 129.64 | 6 |
| 2021-01-25 18:41:44 | | 4 | 135.34 | 7 |
| 2021-01-25 21:41:44 | 3 | 117.3 | 131.2 | 8 |
| 2021-01-25 23:21:44 | | 0.6 | 137.3 | 9 |
| 2021-01-26 11:41:44 | | 0.2 | 136.32 | 10 |
| 2021-01-26 13:23:53 | | 0.5 | 136.1 | 11 |
So from the macro's point of view, the first iteration would begin from RecordID 3 where the first "big" transaction is encountered and iterate all the way down to 11 where the data ends, then it would append next to recordID 3 the min and max prices encountered at the 5m, 15m, 1h, 6h, 24h intervals starting from 2021-01-25 13:55:53 (10 columns).
The second iteration would begin from RecordID 6 where the second big transaction is encountered and iterate again till the dataset's end (recordID 11), and it would append the min max prices of this timeframe (from 2021-01-25 15:31:44) next to recordID 6, etc.
