Saturday, 14 September 2013

How to use previous information when I use "by"?

How to use previous information when I use "by"?

I have a data from forex market(mydt) saved by data.table:
datetime|side(0=Bid,1=Ask)| distance(1:best price, 2: 2nd best)| price
2008/01/28,09:11:28.000,0,1,1.6066
2008/01/28,09:11:28.000,0,2,1.6065
2008/01/28,09:11:28.000,1,1,1.6068
2008/01/28,09:11:28.000,1,2,1.6069
2008/01/28,09:11:29.000,0,1,1.6067
2008/01/28,09:11:29.000,0,2,1.6066
2008/01/28,09:11:30.000,0,1,1.6065
2008/01/28,09:11:30.000,0,2,1.6064
2008/01/28,09:11:30.000,1,1,1.6066
2008/01/28,09:11:30.000,1,2,1.6067
I want to calculate spread=minAsk-maxBid in each block of time. The usual
way is to write:
mydt[,min(price[side==1]) - max(price[side==0]),by=datetime]
However, data has been summarized by this rule: If there is no change in
one side, there will be no record in the data. That means comparing block
1 and block 2, there was no change in this part and no record:
2008/01/28,09:11:28.000,1,1,1.6067
2008/01/28,09:11:28.000,1,2,1.6068
I want to go block by block to calculate some variables(spread, or
weighted average price), however sometimes I need information from
previous block(because of this rule). How can I do that?

No comments:

Post a Comment