Using shift and data table to update the value of an inventory

Question

I want to use data tables to compute the running value of an inventory.

For example, this following ledger:

ledger <- data.table(Date = c('2017-04-05','2017-06-12','2017-08-12','2017-10-27','2017-11-01'),
                 Op = c('Purchase','Sale','Purchase','Purchase','Sale'),
                 Prod = c('ProdA','ProdA','ProdA','ProdA','ProdA'),
                 Qty = c(27,-20,15,10,-22),
                 Prc = c(36.47,41.64,40.03,40.95,40.82))

I want to compute the running stock of that product and the running average value. Stock is easy:

ledger[,Stock := cumsum(Qty)]

But for the average value, I'm having some difficulty. The logic is the following: if the line is a purchase, average value should be the weighted average of the previous average value and the new price. If it's a sale, average price doesn't change.

The way I thought it would work:

#This Line doesnt work
ledger[,AvgPrice := ifelse(Op == "Purchase",
                     (Prc * Qty + shift(AvgPrice,1,fill = 0)*shift(Stock,1,fill = 0))/ (Qty + shift(Stock,1,fill = 0)),
                     shift(AvgPrice,1,fill = 0))]

Doesn't work because it's self referencing.

For the reference, this is what the result should look like:

    Date        Op          Prod    Qty  Prc    Stock   AvgPrice
1   05-04-17    Purchase    ProdA   27   36.47  27      36.47
2   12-06-17    Sale        ProdA   -20  41.64  7       36.47
3   12-08-17    Purchase    ProdA   15   40.03  22      38.90
4   27-10-17    Purchase    ProdA   10   40.95  32      39.54
5   01-11-17    Sale        ProdA   -22  40.82  10      39.54

Appreciate any help! FYI, if you buy stocks in Brazil this is how you calculate capital gains!

score 2 · Answer 1 · answered Mar 16 '18 at 04:07

Data:

library('data.table')
ledger <- data.table(Date = c('2017-04-05','2017-06-12','2017-08-12','2017-10-27','2017-11-01'),
                     Op = c('Purchase','Sale','Purchase','Purchase','Sale'),
                     Prod = c('ProdA','ProdA','ProdA','ProdA','ProdA'),
                     Qty = c(27,-20,15,10,-22),
                     Prc = c(36.47,41.64,40.03,40.95,40.82))

Code:

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

Output:

ledger
#          Date       Op  Prod Qty   Prc Stock AvgPrice
# 1: 2017-04-05 Purchase ProdA  27 36.47    27    36.47
# 2: 2017-06-12     Sale ProdA -20 41.64     7    36.47
# 3: 2017-08-12 Purchase ProdA  15 40.03    22    38.90
# 4: 2017-10-27 Purchase ProdA  10 40.95    32    39.54
# 5: 2017-11-01     Sale ProdA -22 40.82    10    39.54

Using shift and data table to update the value of an inventory

1 Answers1

Linked