Matching dataframe rows for finding trade match

I have a few comments on the code:

1. Globally changing values is a pretty big no-no in R code, in large part because it makes functions have unexpected side effects.
2. This code makes use of a lot of for loops instead of using R's vectorized operators.
3. The code separately loops through the buys for each sell, which could be wasteful (why do you need to check a buy again after it has already been matched to a sell?)

Based on this, I have a few proposals:

1. I would do away with the concept of find_matching_buy and I would simultaneously match all the buys and sells for a security.
2. When simultaneously matching the buys and sells, I would simplify the logic as follows. Let b be the total volume of buys and s be the total volume of sells. If b == s, then mark everything as fully matching. If b > s, then match all sells as fully matching and the first s volume of the buys as fully matching. If s > b, then match all the buys as fully matching and the first b volume of sells as fully matching.

Let's start with a fully reproducible example dataset (I obtained this using dput):

(dat <- structure(list(AsxCode = c("DMP", "DMP", "RFG", "RFG", "RFG", "RFG"), Order.Type = c("Buy", "Sell", "Buy", "Buy", "Buy", "Sell"), Trade.Date = structure(c(16664, 16734, 16511, 16590, 16707, 16617), class = "Date"), Price = c(42.1179, 47.05, 7.49, 5.98, 4.2, 5.4286), Quantity = c(105L, 105L, 640L, 870L, 700L, 1510L)), .Names = c("AsxCode", "Order.Type", "Trade.Date", "Price",  "Quantity"), row.names = c(NA, -6L), class = "data.frame"))
#   AsxCode Order.Type Trade.Date   Price Quantity
# 1     DMP        Buy 2015-08-17 42.1179      105
# 2     DMP       Sell 2015-10-26 47.0500      105
# 3     RFG        Buy 2015-03-17  7.4900      640
# 4     RFG        Buy 2015-06-04  5.9800      870
# 5     RFG        Buy 2015-09-29  4.2000      700
# 6     RFG       Sell 2015-07-01  5.4286     1510

The central piece of the code involves determining the matching for sells and buys for a given security, doing away with the concept of find_matching_buy using the approach I described above. I'll call this function match.security:

match.security <- function(x) {
  x <- x[order(x$Trade.Date),]
  x$match_status <- "FULL"
  x$match_vol <- x$Quantity
  b <- sum(x$Quantity[x$Order.Type == "Buy"])
  s <- sum(x$Quantity[x$Order.Type == "Sell"])
  if (b > s) {
    # Only some of the buys are matched; update match_status and match_vol for buys
    b.quant <- x$Quantity[x$Order.Type == "Buy"]
    b.vol <- diff(c(0, pmin(cumsum(b.quant), s)))
    x$match_vol[x$Order.Type == "Buy"] <- b.vol    
    x$match_status[x$Order.Type == "Buy"] <-
      ifelse(b.vol == b.quant, "FULL", ifelse(b.vol == 0, "0", "Partial"))
  } else if (s > b) {
    # Only some of the sells are matched; update match_status and match_vol for sells
    s.quant <- x$Quantity[x$Order.Type == "Sell"]
    s.vol <- diff(c(0, pmin(cumsum(s.quant), b)))
    x$match_vol[x$Order.Type == "Sell"] <- s.vol    
    x$match_status[x$Order.Type == "Sell"] <-
      ifelse(s.vol == s.quant, "FULL", ifelse(s.vol == 0, "0", "Partial"))
  }
  return(x)
}

This function first labels everything as fully matched, and then computes the total volume of buys b and total volume sells s. If b > s it goes back and marks the unmatched and partially matched buys (similarly processing sells with s > b). The tricky bit is to determine the matched volume for each buy, which we do with b.vol <- diff(c(0, pmin(cumsum(b.quant), s))), where b.quant is the Quantity values for the buys and s is the total quantity of sells. For security RFG in the example data above, we have:

b.quant <- c(640, 870, 700)
s <- 1510

First, we compute the cumulative sum of b.quant, which for each buy represents the total quantity of buys if we included all earlier buys and that buy:

cumsum(b.quant)
# [1]  640 1510 2210

However, we can't match more volume of buys than the volume of sells, so we will cap these values at s using the pmin function:

pmin(cumsum(b.quant), s)
# [1]  640 1510 1510

We can see that the differences between these values are the amount of the buy that is actually matched: 640 of the first buy is matched (640-0), 870 of the second buy is matched (1510-640), and none of the third buy is matched (1510-1510). We get this by taking the pairwise differences between elements:

diff(c(0, pmin(cumsum(b.quant), s)))
# [1] 640 870   0

This may seem like a very roundabout/complex way to compute the matched volume for the buys, but in fact it has the nice property that cumsum, pmin, and diff are all vectorized functions in R, so this will actually be much more efficient than a for loop when you have a large number of buys and sells for a given security.

We can run our match.security function on each of our securities to make sure it is running correctly:

match.security(dat[dat$AsxCode == "DMP",])
#   AsxCode Order.Type Trade.Date   Price Quantity match_status match_vol
# 1     DMP        Buy 2015-08-17 42.1179      105         FULL       105
# 2     DMP       Sell 2015-10-26 47.0500      105         FULL       105
match.security(dat[dat$AsxCode == "RFG",])
#   AsxCode Order.Type Trade.Date  Price Quantity match_status match_vol
# 3     RFG        Buy 2015-03-17 7.4900      640         FULL       640
# 4     RFG        Buy 2015-06-04 5.9800      870         FULL       870
# 6     RFG       Sell 2015-07-01 5.4286     1510         FULL      1510
# 5     RFG        Buy 2015-09-29 4.2000      700            0         0

The last step is to run match.security for each security and to combine the results into a final data frame. To do so, I would use the following:

(matched.dat <- do.call(rbind, lapply(split(dat, dat$AsxCode), match.security)))
#       AsxCode Order.Type Trade.Date   Price Quantity match_status match_vol
# DMP.1     DMP        Buy 2015-08-17 42.1179      105         FULL       105
# DMP.2     DMP       Sell 2015-10-26 47.0500      105         FULL       105
# RFG.3     RFG        Buy 2015-03-17  7.4900      640         FULL       640
# RFG.4     RFG        Buy 2015-06-04  5.9800      870         FULL       870
# RFG.6     RFG       Sell 2015-07-01  5.4286     1510         FULL      1510
# RFG.5     RFG        Buy 2015-09-29  4.2000      700            0         0

Breaking down this line of code:

• split(dat, dat$AsxCode) splits dat up into a list of data frames, each associated with one security.
• lapply(..., match.security) calls match.security on each list element and stores the results of those calls into a list.
• do.call(rbind, ...) combines all of our returned lists into a single data frame using rbind.