How to have a result by group when group_by doesn't apply. Weighted averages for stocks

Question

I want to update the weighted averages of my stocks price as buy and sell them.
I found a partial solution (looking though the stack overflow: Using shift and data table to update the value of an inventory), but it doesn't group by share. My data looks like this:

library(data.table)

ledger <-data.table(
  Share = c(rep("b",3), rep("x",2)),
  Prc = c(10,20,15, 35,40),
  Qty= c(300,-50,100, 50,-10),
  Op =c("Purchase", "Sale", "Purchase", "Purchase", "Sale")


)

What I came up so far:

ledger<-ledger %>%group_by(Share)%>%
  mutate(Stock = cumsum(Qty))%>%
  group_by(Share)%>%
  mutate(id =row_number())%>%
  mutate(AvgPrice =ifelse( id ==1, Prc, NA_real_))

ledger<-as.data.table(ledger)

ledger[, Stock := cumsum(Qty)]  # compute Stock value
ledger[, `:=` ( id = .I, AvgPrice = NA_real_ ) ] # add id and AvgPrice columns
ledger[ 1, AvgPrice := Prc] # compute AvgPrice for first row

# work with remaining rows and find the AvgPrice
ledger[ ledger[, .I[-1]], AvgPrice := {
  if( Op == "Sale" ){   
    ledger[ .I-1, AvgPrice ]
  } else {
    round( ( ( Qty * Prc ) + ledger[ .I-1, AvgPrice * Stock ] ) /
             ( Qty + ledger[ .I-1, Stock]) ,
           digits = 2 )
  }
}, by = id ]

ledger[, id := NULL ]  # remove id column

The problem is I would like it restart when "Share"changes from b to x. As it would do with a group_by function.

I appreciate any help.

Answer 1

Here is a recursive option in data.table:

ledger[, AvgPrice := {
    latestInventory <- 0L
    lastAvgPrice <- 0L
    .SD[, {
        Inventory <- cumsum(c(latestInventory, Qty))
        QtyBought <- cummax(Inventory)
        ValueBought <- cumsum(c(latestInventory * lastAvgPrice,
            replace(Qty, Op=="Sale", 0) * Prc))
        AvgCostPrice <- ValueBought / QtyBought
        latestInventory <- Inventory[.N+1L]
        lastAvgPrice <- AvgCostPrice[.N+1L]

        .(AvgCostPrice[-1L])
    }, .(g=cumsum(shift(Op , fill="Sale")=="Sale" & Op=="Purchase"))]$V1

}, .(Share)]

output:

   Share Prc Qty       Op AvgPrice
1:     b  10 300 Purchase 10.00000
2:     b  20 -50     Sale 10.00000
3:     b  15 100 Purchase 11.42857
4:     x  35  50 Purchase 35.00000
5:     x  40 -10     Sale 35.00000