Calculate average invested price without helper columns/tables
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
- Constrain a Specman list so it doesn't have identical values in consecutive elements
- How to type cast a list of uint to a list of vr_ahb_data in Specman?
- Static fields/methods in e
- What is the difference between deep_copy and gen keeping in Specman?
- Specman UVM: What is the difference between write_reg { .field == 2;}; and write_reg_fields?
- Does Specman support optional parameters to a method?
- Specman e: When colon equal sign ":=" should be used?
- Specman e: Is there a way to know how many values there is in an enumerated type?
- Specman e error: No match for file when using "for each line in file"
- How to run e file one by one? Not in parallel test
- Specman/e constraint (for each in) iteration
- Specman e: How driver's items queue can be locked from a sequence?
- Specman e: How to print variable's address?
- Specman e: "keep type .. is a" fails to refine the type of a field
- Specman soft select on variable, decimal vs. hexadecimal values
- Specman e: How to print a pointer to a struct?
- Specman e: Is there a way to print unit instance name?
- Specman e: simultaneous events error
- Specman e coverage: ignored values appear in the coverage statistics
- Specman e: How a sequence should be started when gen_and_start_main constrained to FALSE?
- Specman/e list of lists (multidimensional array)
- Does Specman e have struct constructor?
- Specman e: How the predefined sequence.item should be used?
- Specman e: define-as-computed macro error
- Specman e UVM: Why to inherit from uvm_* units?
- Specman e: A sequence drives its BFM also its MAIN was not defined in a test
- e HVL (IEEE 1647): expect expression fails unexpectedly
- Specman e subtyping: How to refer to FALSE value of conditional field in when/extend subtyping?
- e HVL (IEEE 1647): How to set 'X' value?
- Difference between declaring an event that is sensitive to a simple_port value and event_port