Suppose that I have the following dataset: Time Volume Price 10:02:04 100 100.25 10:02:07 200 100.35 10:02:14 300 100.55 10:02:18 100 100.60 10:02:18 300 100.70 10:02:23 200 100.80 And, I want to get the following dataset Time Volume Price Diff 10:02:04 100 100.25 -0.0045 = (100.25 - 100.70)/100.25 = (price(t)-price(t+10))/price(t) 10:02:07 200 100.35 -0.0032 = (100.35 - 100.675)/100.35 10:02:14 300 100.55 -0.0012 = (100.55 - 100.675)/100.55 10:02:16 100 100.60 -0.0009 = (100.60 - 100.70)/100.60 10:02:16 300 100.70 So, as you can see, I need to find price change over 10 seconds (p(t) - p(t+10)) and then divide it by p(t). However, there are some issues. First, if I do not have p(t+10), then I need to select the price that is closest to p(t+10). Furthermore, if I have two prices in one second, I need to compute the volume-weighted average price for this second and then use it. For example, in my data, I have an interval of 10:02:07. I do not have a price at 10:02:17 and the closest one is the price at 10:02:16. However, in this interval, I have two prices. Therefore, I am computing the volume-weighted average price for this interval (((100/400)*100.60 + (300/400)*100.70) = 100.675) and then use this one. Could you please help me with this? I hope I could explain my question.
... View more