使用班次和数据表更新库存价值 [英] Using shift and data table to update the value of an inventory
问题描述
我想使用数据表来计算库存的运行价值。
例如，以下分类帐：
分类帐<-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'），
数量= c（27，-20,15,10，-22），
Prc = c（36.47,41.64,40.03,40.95,40.82））
我想计算跑步次数该产品的库存和移动平均值。股票很容易。
但是对于平均值，我有些困难。逻辑如下：如果该行是采购，则平均值应为先前平均值和新价格的加权平均值。如果是销售，平均价格不会改变。
我认为它会起作用的方式：
＃此行无效
ledger [，AvgPrice：= ifelse（Op == Purchase，
（Prc * Qty + shift（AvgPrice，1，fill = 0）* shift（Stock，1，fill = 0））/（数量+ shift（Stock，1，fill = 0）），
shift（AvgPrice，1，fill = 0））]
不起作用，因为它是自引用。
参考，结果应该是这样的：
日期操作产品数量数量库存价格平均价格
1 05- 04-17购买ProdA 27 36.47 27 36.47
2 12-06-17销售ProdA -20 41.64 7 36.47
3 12-08-17购买ProdA 15 40.03 22 38.90
4 27-10 -17购买产品10 40.95 32 39.54
5 01-11-17销售产品-22 40.82 10 39.54
确认要求任何帮助！仅供参考，如果您在巴西购买股票，这就是您计算资本收益的方法！
数据：
library（'data.table'）
分类帐<-data.table（Date = c（'2017-04-05'，' 2017-06-12'，'2017-08-12'，'2017-10-27'，'2017-11-01'），
Op = c（'Purchase'，'Sale'，'Purchase '，'购买'，'销售'），
Prod = c（'ProdA'，'ProdA'，'ProdA'，'ProdA'，'ProdA'），
数量= c（27， -20,15,10，-22），
Prc = c（36.47,41.64,40.03,40.95,40.82））
代码：
分类帐[，股票：= cumsum（Qty）]＃计算股票价值
ledger [，`：=`（id = .I，AvgPrice = NA_real_）]＃添加id和AvgPrice列
ledger [1，AvgPrice：= Prc]＃计算第一行$ b $的AvgPrice b
＃处理剩余的行并找到AvgPrice
ledger [ledger [，.I [-1]]，AvgPrice：= {
if（Op == Sale）{
分类帐[.I-1，平均价格]
} else {
round（（（Qty * Prc）+ ledger [.I-1，AvgPrice * Stock]）/
（Qty + ledger [.I-1，Stock]），
digits = 2）
}
}，by = id]
ledger [，id：= NULL]＃删除id列
输出：
分类帐
＃日期操作产品数量库存价格平均
＃1：2017-04-05购买产品27 27.47 27 36.47
＃2：2017-06-12销售产品-20 41.64 7 36.47
＃3：2017-08-12购买ProdA 15 40.03 22 38.90
＃4：2017-10-27购买ProdA 10 40.95 32 39.54
＃5：2017-11-01销售ProdA -22 40.82 10 39.54
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!
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
这篇关于使用班次和数据表更新库存价值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
