Recursive looping through xts

Question

I'm modelling the development of the orderbook over time. I have an initial orderbook shape in xts and then the subsequent depth updates also in xts:

The initial orderbook shape looks as follows (all entries have the same time):

                            BID.price       size
2014-02-11 23:59:42.494426  508.1000        10.0000000
2014-02-11 23:59:42.494426  509.1200         8.0000000
2014-02-11 23:59:42.494426  509.1000        10.0000000

and the subsequent depth udpates look as follows:

                           BID. price       size
2014-02-12 04:57:51.191514 508.1000        -10.00000000
2014-02-12 04:57:51.640302 514.0000         10.00000000

What I need to to is:

1) for each row in updates, compare the price with the orderbook:

1a) If the updates price level is in orderbook already, adjust the size accordingly, so the example above would look as follows:

                            BID.price       size
2014-02-12 04:57:51.191514  509.1200         8.0000000
2014-02-12 04:57:51.291514  509.1000        10.0000000

(the price level 508.10000 was deleted, and time was updated)

1b) If the depth updates is not in orderbook yet, add new prise level with given size, so the example would looks like:

                            BID.price       size
2014-02-12 04:57:51.640302  509.1200         8.0000000
2014-02-12 04:57:51.640302  509.1000         10.0000000
2014-02-12 04:57:51.640302  514.0000         10.00000000

(new price level of 514 was added and time was adjusted).

Is there any convenient and fast way how to do such thing avoiding the for loop over depth updates xts?

Thanks!

1- I don't think you can avoid the for loop here. Anyway , try to add what you have tried and also put your data in a readable format( hard to use at is now). — agstudy
@agstudy: ok, and jsut to clarify, what do you mean by readable format? Pasted data comes directly from R. — Steef Gregor
You should use dput(head(your_data), and also do you really have an xts object? I mean do you need a time series here , look that you just need the last day, Maybe a simple data.frame(bid,size) is sufficient. — agstudy
What you're trying to do is not clear. Are you trying to build a time series of the orderbook state, or are you just trying to update a snapshot of the orderbook? — Joshua Ulrich
I'm sorry for a vague description. What I need to have is a time serie reflecting a state of an orderbook at time when any orderbook update occured. — Steef Gregor

agstudy agstudy · Accepted Answer · 2014-02-19T12:22:01

I think no need to use an xts object here since the index is the same for all observations and the right id here is the bid variable. So I explain my solution using 1 simple data.frame as shown above:

DT                  ## the day before
  day    bid size
1   1 508.10   10
2   1 509.12    8
3   1 509.10   10
DT1                ## the current or last day
  day   bid size
1   2 508.1  -10
2   2 514.0   10

Now using merge we have nearly the solution:

dtm
     bid day.x size.x day.y size.y
1 508.10     1     10     2    -10
2 509.10     1     10    NA     NA
3 509.12     1      8    NA     NA
4 514.00    NA     NA     2     10

Now we should just adjust size and remove bids with a null positions. I cerate here an intermediate %+% function to deal with missing values.

## compute size
"%+%" <- function(x,y) 
     ifelse(is.na(x),
            ifelse(is.na(y),NA,y),
            ifelse(is.na(y),x,NA))
## remove  numm poistion(size==0)
subset(transform(dtm,size=size.x%+%size.y,day=max(day.y,na.rm=T)),
       size !=0,select=c(day,bid,size))

 day    bid size
2   2 509.10   10
3   2 509.12    8
4   2 514.00   10

I think you can get more succicent and syntax sugar solution here using data.table.

Recursive looping through xts

1 Answers