计算没有辅助列/表的平均投资价格 [英] Calculate average invested price without helper columns/tables
问题描述
这篇文章真的值得一读.这个主题的另一个更好的版本是可用的
所以平均比率是 10100.
对于第二个表，输出为
所以平均比率是 3.
编辑
这里是 UDF 版本，称为
=avRate(qtyRange,rateRange)函数avgRate(qtyRange As Range, rateRange As Range)'创建队列Dim 队列作为对象Set queue = CreateObject("System.Collections.Queue") '创建队列' 声明一些变量Dim bs 作为对象昏暗数量双倍调光率双倍Dim 数量双倍出售昏暗的数量双倍购买Dim qtyRemaining As DoubleDim rateBought As Double昏暗的我点心价格为双倍，总数量为双倍对于 i = 1 到 qtyRange.Cells().Countqty = qtyRange.Cells(i).Value()rate = rateRange.Cells(i).Value()如果数量>0 那么'买设置 bs = 新买卖bs.rate = 比率bs.qty = 数量queue.Enqueue bs别的'卖qtyRemaining = -qty'从最早的开始处理队列中的'购买'交易.虽然 qtyRemaining >0如果 qtyRemaining This post is really worth a read. Another better verion of this topic is available here.
If you look at the table below, I am trying to find Avg price at every transaction without adding helper columns. The Average price is correct when the side is Buy but shows incorrect Avg price on the Sell side for which I am looking for a formula, array formula or UDF for the Avg Price column.
| Date | Side | Qty | Price | Value | Holding | Avg Price |
|---|---|---|---|---|---|---|
| 1-Jul | Buy | 225 | 10000 | 2250000 | 225 | 10000 |
| 2-Jul | Buy | 75 | 10200 | 765000 | 300 | 10050 |
| 3-Jul | Sell | -150 | 9950 | -1492500 | 150 | 10150 |
The formula I have for Value is =E3*D3, for Holding is =SUM($D$3:D3) and for Avg price is =SUMPRODUCT($D$3:D3,$E$3:E3)/SUM($D$3:$D3) which I dragged downwards. Everything seems to be correct except the last value 10150. Ideally it should have 10,100 as per FIFO logic given below.
1st order: Quantity = 225 | Price = Rs. 10,000.00
2nd order: Quantity = 75 | Price = Rs. 10,200.00
To calculate the average price, first calculate the value (Quantity x Price). Hence:
1st trade: Rs. 22,50,000.00
2nd trade: Rs. 7,65,000.00
Total quantity = 300
Total value for first two orders : Rs. 30,15,000.00
Divide total value by total quantity:
Rs. 30,15,000.00 ÷ 300 = Rs.10,050.00 (Did it using the =sumproduct formula)
On 3-Jul, we placed a sell order 150 (out of 300). Price: Rs. 9,950.00
Now the FIFO (first in first out) method will be applied here. The method will check the first trade (on the buy-side). In this case, it is 225. 150 sold stocks will be deducted from 225 (first holding). The balance left of first holding which was 225 earlier will now be 225 - 150 = 75
After FIFO, the table gets converted like this after deducting the sell quantity. See the first Qty is changed from 225 to 75 because 150 stocks were sold.
| Date | Side | Qty | Price | Value | Holding | Avg Price |
|---|---|---|---|---|---|---|
| 1-Jul | Buy | 75 | 10000 | 750000 | 75 | 10000 |
| 2-Jul | Buy | 75 | 10200 | 765000 | 150 | 10100 |
Kindly note: In case the sell quantity was more than 225, then it would have moved to the next trade to deduct the remaining quantity.
Now to get a solution to this, additional helper columns or helper tables are needed which I am looking to eradicate and find for a formula or an array formula or an UDF to calculate the Avg Price. I request excel experts to help me with this problem.
One more example what I am trying is given below where the invested price is showing incorrect:
| Date | Side | Qty | Price | Value | Holding | Avg Price |
|---|---|---|---|---|---|---|
| 1-Jul | Buy | 5 | 10 | 50 | 5 | 10 |
| 2-Jul | Sell | -3 | 17 | -51 | 2 | -0.5 |
| 3-Jul | Buy | 17 | 3 | 51 | 19 | 2.63 |
| 4-Jul | Sell | -15 | 7.8 | -117 | 4 | -16.75 |
Edit
Done after getting a solution from @Tom Sharpe
In order to get the average price, I declared two variables avgRate and sumRate as double and modified the For Each code a bit. Please advice if there is an efficient way to do it. Appreciate if this can be converted into an UDF so that I don't have to run the code again and again. Thank you so much for the wonderful solution.
For Each bs In queue
Debug.Print ("qty=" & bs.qty)
Debug.Print ("rate=" & bs.rate)
avgRate = avgRate + (bs.qty * bs.rate)
sumRate = sumRate + bs.qty
Debug.Print avgRate / sumRate
Next
OK well here is a test version of a VBA implementation.
Algorithm:
If 'buy' transaction, just add to the queue.
If 'sell' transaction (negative quantity)
Repeat
Take as much as possible from earliest transaction
If more is required, look at next transaction
until sell amount reduced to zero.
The program uses a class BuySell so you need to create a class module, rename it to BuySell and include the lines
Public rate As Double
Public qty As Double
The following goes in a normal module.
Option Explicit
Sub FifoTrading()
' Create the queue
Dim queue As Object
Set queue = CreateObject("System.Collections.Queue") 'Create the Queue
' Declare some variables
Dim bs As Object
Dim qty As Double
Dim rate As Double
Dim qtySold As Double
Dim qtyBought As Double
Dim qtyRemaining As Double
Dim rateBought As Double
Dim i As Long
For i = 2 To 5
Debug.Print (Cells(i, 3).Value())
Debug.Print (Cells(i, 4).Value())
rate = Cells(i, 4).Value()
qty = Cells(i, 3).Value()
If qty > 0 Then
'Buy
Set bs = New BuySell
bs.rate = rate
bs.qty = qty
queue.Enqueue bs
Else
'Sell
qtyRemaining = -qty
'Work through the 'buy' transactions in the queue starting at the oldest.
While qtyRemaining > 0
If qtyRemaining < queue.peek().qty Then
'More than enough stocks in this 'buy' to cover the sale so just work out what's left
queue.peek().qty = queue.peek().qty - qtyRemaining
qtyRemaining = 0
ElseIf qtyRemaining = queue.peek().qty Then
'Exactly enough stocks in this 'buy' to cover the sale so remove from queue
Set bs = queue.dequeue()
qtyRemaining = 0
Else
'Not enough stocks in this 'buy' to cover the sale so remove from queue and reduce amount of sale remaining
Set bs = queue.dequeue()
qtyRemaining = qtyRemaining - bs.qty
End If
Wend
End If
Next i
For Each bs In queue
Debug.Print ("qty=" & bs.qty)
Debug.Print ("rate=" & bs.rate)
Next
avRate = 0
totQty = 0
For Each bs In queue
avRate = avRate + bs.qty * bs.rate
totQty = totQty + bs.qty
Next
avRate = avRate / totQty
Debug.Print ("average=" & avRate)
End Sub
For the first table, the output is
so the average rate is 10100.
For the second table, the output is
so the average rate is 3.
EDIT
Here is the UDF version which is called as
=avRate(qtyRange,rateRange)
Function avgRate(qtyRange As Range, rateRange As Range)
' Create the queue
Dim queue As Object
Set queue = CreateObject("System.Collections.Queue") 'Create the Queue
' Declare some variables
Dim bs As Object
Dim qty As Double
Dim rate As Double
Dim qtySold As Double
Dim qtyBought As Double
Dim qtyRemaining As Double
Dim rateBought As Double
Dim i As Long
Dim sumRate As Double, totQty As Double
For i = 1 To qtyRange.Cells().Count
qty = qtyRange.Cells(i).Value()
rate = rateRange.Cells(i).Value()
If qty > 0 Then
'Buy
Set bs = New BuySell
bs.rate = rate
bs.qty = qty
queue.Enqueue bs
Else
'Sell
qtyRemaining = -qty
'Work through the 'buy' transactions in the queue starting at the oldest.
While qtyRemaining > 0
If qtyRemaining < queue.peek().qty Then
'More than enough stocks in this 'buy' to cover the sale so just work out what's left
queue.peek().qty = queue.peek().qty - qtyRemaining
qtyRemaining = 0
ElseIf qtyRemaining = queue.peek().qty Then
'Exactly enough stocks in this 'buy' to cover the sale so remove from queue
Set bs = queue.dequeue()
qtyRemaining = 0
Else
'Not enough stocks in this 'buy' to cover the sale so remove from queue and reduce amount of sale remaining
Set bs = queue.dequeue()
qtyRemaining = qtyRemaining - bs.qty
End If
Wend
End If
Next i
'Calculate average rate over remaining stocks
sumRate = 0
totQty = 0
For Each bs In queue
sumRate = sumRate + bs.qty * bs.rate
totQty = totQty + bs.qty
Next
avgRate = sumRate / totQty
End Function
这篇关于计算没有辅助列/表的平均投资价格的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
