Price weighted average for adjusted for new boughts and sells in r

Question

I'm trying to find out the final weighted mean after buys and sells of my stocks.

So I am looking for a weighted average, to be adjusted by the buys and sells.

This is an example of my data. I have more than one stock but I can apply to the others using group_by.

ledger <-data.table(
  ID = 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")


)

ledger<-ledger %>%group_by(ID)%>%
  mutate(Stock = cumsum(Qty))
ledger<-as.data.table(ledger)
View(ledger)

As I looked for my answer I found this 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

That works very well. But I need to group_by my ID. So it wont make the average all toghether.

thanks for your contribution!!

The formula is not clear `R1 =(Buy-Sell)Price` Are you multiplying the Price — akrun, Jan 16 '20 at 20:22
Please provide the full code, e.g. including your calculation for `R1`. — Samuel, Jan 16 '20 at 20:57
Yes Iam multiplying the price. But for the next line I need to considerate the sells to weight values correctly — Marina Verçoza Viana, Jan 16 '20 at 22:53
I am really lost. Just did spread and gather trying to aling better the data. — Marina Verçoza Viana, Jan 16 '20 at 22:55

score 0 · Answer 1 · answered Jan 16 '20 at 21:58

I think it would help to think about storing some useful intermediate values to help with the calculation:

DF$Net_Stocks          <- DF$Buy - DF$Sell
DF$Stocks_Owned        <- cumsum(DF$Net_Stocks)
DF$Cost                <- DF$price * DF$Buy
DF$Sales               <- DF$price * DF$Sell
DF$Net_Cost            <- DF$Cost - DF$Sales
DF$Total_Cost          <- cumsum(DF$Net_Cost)
DF$Cost_Per_Held_Stock <- DF$Total_Cost / DF$Stocks_Owned

Now you can see better what is happening:

DF
#> ID price Buy Sell Net_Stocks Stocks_Owned Cost Sales Net_Cost Total_Cost Cost_Per_Held_Stock
#>  b    10 100   50         50           50 1000   500      500        500            10.00000
#>  b    20 200  100        100          150 4000  2000     2000       2500            16.66667
#>  b    15  50    0         50          200  750     0      750       3250            16.25000

If you only want the formula for the last column, it is:

cumsum((DF$Buy - DF$Sell) * DF$price) / cumsum(DF$Buy - DF$Sell)

Thanks the problem is that the stock cost is influence by previous sells. So I can´t inly multply by lines. — Marina Verçoza Viana, Jan 16 '20 at 22:21

Price weighted average for adjusted for new boughts and sells in r

1 Answers1