计算平均价格、已实现收益和使用 FIFO 方法通过 UDF 的未实现增益 [英] Calculate Avg Price, Realized gain & Unrealized gain via UDF using FIFO method
问题描述
这篇文章是我
需要通过使用现有代码从队列中删除最早购买的股票来获得每次卖出交易的收益(或损失)，但要添加额外的行来计算:
收益=销售价格*销售数量-∑买入价格*买入数量按时间顺序对满足销售数量的不同购买"交易进行求和.
我现在添加了 OP 建议的额外计算，并添加了一些基本的错误处理(例如，用户不会尝试出售超过可用数量的股票，从而使队列变空).
UDF 仅接受单列参数作为范围或数组.
UDF
像以前一样需要一个 BuySell 类:
公价双倍公共数量双倍选项显式函数 avgRate(qtyRange As Variant, rateRange As Variant, Optional calcNumber As Integer = 1)'创建队列Dim 队列作为对象Set queue = CreateObject(System.Collections.Queue")' 声明一些变量Dim bs 作为对象昏暗数量双倍调光率双倍Dim 数量双倍出售昏暗的数量双倍购买Dim qtyRemaining As DoubleDim rateBought As Double昏暗的我点心价格为双倍，总数量为双倍Dim avRate 双倍Dim saleValue As DoubleDim purchaseValue As DoubleDim gainForThisSale As DoubleDim totalGain As DoubleDim totalCost 双倍Dim total 利润翻倍昏暗的整体成本为两倍Dim tempQty() As Variant, workQty() As Variant, tempRate() As Variant, workRate() As VariantDim nRows as long将 argType 变暗为整数'从范围或数组复制 - 假设在这两种情况下都是单列或单个元素.如果 TypeOf qtyRange 是 Range 那么如果 IsArray(qtyRange) 那么' 列范围参数类型 = 1别的' 单元素范围参数类型 = 2万一别的如果 UBound(qtyRange, 1) >1 那么'列数组参数类型 = 3别的' 单元素数组参数类型 = 4万一万一Debug.Print ("Argtype=" & argType)选择案例 argType情况1tempQty = qtyRange.ValuetempRate = rateRange.Value案例二n行 = 1ReDim workQty(1 To nRows)ReDim workRate(1 To nRows)workQty(1) = qtyRange.ValueworkRate(1) = rateRange.Value案例3tempQty = qtyRangetempRate = rateRange案例四n行 = 1ReDim workQty(1 To nRows)ReDim workRate(1 To nRows)工作数量(1) = 数量范围(1)workRate(1) = rateRange(1)结束选择如果 argType = 1 或 argType = 3 那么nRows = UBound(tempQty, 1)ReDim workQty(1 To nRows)ReDim workRate(1 To nRows)对于 i = 1 到 nRows工作数量(i) = 临时数量(i, 1)workRate(i) = tempRate(i, 1)接下来我万一' 遍历行总利润 = 0总体成本 = 0对于 i = 1 到 nRows数量 = 工作数量(i)' 如果 qty 为零，则什么也不做如果数量 = 0 然后转到继续:费率 = 工作费率(i)总体成本 = 总体成本 + 费率 * 数量如果数量>0 那么'买设置 bs = 新买卖bs.rate = 比率bs.qty = 数量queue.Enqueue bs别的'卖qtyRemaining = -qty'实现增益的代码购买价值 = 0saleValue = rate * qtyRemainingtotalProfit = totalProfit + saleValue'从最早的开始处理队列中的'购买'交易.虽然 qtyRemaining >0如果 queue.Count = 0 那么avgRate = CVErr(xlErrNum)退出函数万一如果 qtyRemaining 我添加了一个新版本，它测试第一个参数是数组还是范围(并假设第二个参数是相同类型的).OP 要求我检查它是单个元素数组还是单个单元格范围的情况.允许数组等的要点是你可以有一个函数调用，如:
=avgRate(FILTER($C2:$C10,C2:C10=10),FILTER($A2:$A10,C2:C10=10),8)或
=avgrate($C$2,$A$2,8)仅选择(在本例中)第一行.这使得 UDF 在您可能拥有多家公司的股票并希望过滤公司的情况下更加通用.
This post is in continuation of my earlier post where I have already got assistance from Tom Sharpe on how to calculate the Average Price of a stock using the FIFO method as per transaction table & UDF given below. In order to add more functionality to it, I was trying hard to calculate my profit/loss by tweaking the UDF but I was unsuccessful therefore I started a new thread for this.
Profit and Loss is divided into two parts. One is the profit/loss I made by sellling few stocks which will be referred to as Realized Gain and the second one is the Gain which is available in the stock exchange for my unsold stocks which will be called as Unrealized Gain. Both can go into negative if there is a loss instead of profit.
Calculating Unrealised Gain is fairly simple because of the solution which is already provided and the answer to it is, Remaining Qty x Avg Price. Referring the table, 150 x 10 100 = 1 515 000 (I think that is the way it should be calculated - correct me if I am wrong). But calculating Realized Gain is the challenge that I am facing. As per the table, the Realized gain works out to -7 500 which is a loss that is calculated as (Sold Price - First Price) x Sold Quantity (hope mathematics behind this logic is also correct). Plus I am facing even more difficulty when the number of transaction increases.
In short, I was looking forward for having 3 things. The Invested Avg Price (which the UDF is already giving), Unrealized profit (which can be calculated based on the UDF). Need to know how to calculate Realised Profit and if all three things can be returned using the same UDF by adding a parameter in the formula.
Here is the table
| 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 | 10100 |
Below is the Explanation
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. 2 250 000.00
2nd trade: Rs. 765 000.00
Total quantity = 300
Total value for first two orders : Rs. 3 015 000.00
Now here is the catch. 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 and hence the Average Price is 10100 (which I am able to get it from the UDF below.
| Date | Side | Qty | Price | Value | Holding | Avg Price |
|---|---|---|---|---|---|---|
| 1-Jul | Buy | 75 | 10000 | 750000 | 75 | 10000 |
| 2-Jul | Buy | 75 | 10200 | 765000 | 150 | 10100 |
In case the sell quantity was more than 225, then it would have moved to the next trade to deduct the remaining quantity
Thanks to Tom Sharpe for this UDF which is called as =avgRate(qtyRange,rateRange)
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
Here is the UDF
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
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.
EDIT: Adding image of a larger sample that I tried with the provided solution
Need to get the gain (or loss) per sell transaction by using the existing code to remove earliest bought stocks from the queue, but add additional lines to work out:
gain = sale price * sale quantity - ∑ buy price * buy quantity
where the summation is over the different 'buy' transactions that satisfy the sale quantity, in chronological order.
I have now added the additional calculations suggested by OP and added some basic error handling (e.g. that the user doesn't try to sell more stocks than are available, making the queue become empty).
The UDF only accepts single-column arguments either as ranges or arrays.
UDF
Need a BuySell class as before:
Public rate As Double
Public qty As Double
Option Explicit
Function avgRate(qtyRange As Variant, rateRange As Variant, Optional calcNumber As Integer = 1)
' Create the queue
Dim queue As Object
Set queue = CreateObject("System.Collections.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, totalQty As Double
Dim avRate As Double
Dim saleValue As Double
Dim purchaseValue As Double
Dim gainForThisSale As Double
Dim totalGain As Double
Dim totalCost As Double
Dim totalProfit As Double
Dim overallCost As Double
Dim tempQty() As Variant, workQty() As Variant, tempRate() As Variant, workRate() As Variant
Dim nRows As Long
Dim argType As Integer
'Copy from range or array - assuming single column or single element in both cases.
If TypeOf qtyRange Is Range Then
If IsArray(qtyRange) Then
' column range
argType = 1
Else
' Single element range
argType = 2
End If
Else
If UBound(qtyRange, 1) > 1 Then
' Column array
argType = 3
Else
' Single element array
argType = 4
End If
End If
Debug.Print ("Argtype=" & argType)
Select Case argType
Case 1
tempQty = qtyRange.Value
tempRate = rateRange.Value
Case 2
nRows = 1
ReDim workQty(1 To nRows)
ReDim workRate(1 To nRows)
workQty(1) = qtyRange.Value
workRate(1) = rateRange.Value
Case 3
tempQty = qtyRange
tempRate = rateRange
Case 4
nRows = 1
ReDim workQty(1 To nRows)
ReDim workRate(1 To nRows)
workQty(1) = qtyRange(1)
workRate(1) = rateRange(1)
End Select
If argType = 1 Or argType = 3 Then
nRows = UBound(tempQty, 1)
ReDim workQty(1 To nRows)
ReDim workRate(1 To nRows)
For i = 1 To nRows
workQty(i) = tempQty(i, 1)
workRate(i) = tempRate(i, 1)
Next i
End If
' Loop over rows
totalProfit = 0
overallCost = 0
For i = 1 To nRows
qty = workQty(i)
' Do nothing if qty is zero
If qty = 0 Then GoTo Continue:
rate = workRate(i)
overallCost = overallCost + rate * qty
If qty > 0 Then
'Buy
Set bs = New BuySell
bs.rate = rate
bs.qty = qty
queue.Enqueue bs
Else
'Sell
qtyRemaining = -qty
'Code for realized Gain
purchaseValue = 0
saleValue = rate * qtyRemaining
totalProfit = totalProfit + saleValue
'Work through the 'buy' transactions in the queue starting at the oldest.
While qtyRemaining > 0
If queue.Count = 0 Then
avgRate = CVErr(xlErrNum)
Exit Function
End If
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
'Code for realized gain
purchaseValue = purchaseValue + qtyRemaining * queue.peek().rate
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
'Code for realized gain
purchaseValue = purchaseValue + bs.qty * bs.rate
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
'Code for realized gain
purchaseValue = purchaseValue + bs.qty * bs.rate
End If
Wend
'Code for realized gain
gainForThisSale = saleValue - purchaseValue
totalGain = totalGain + gainForThisSale
End If
Continue:
Next i
'Calculate average rate
If queue.Count = 0 Then
avRate = 0
Else
totalCost = 0
totalQty = 0
For Each bs In queue
totalCost = totalCost + bs.qty * bs.rate
totalQty = totalQty + bs.qty
Next
avRate = totalCost / totalQty
End If
Select Case calcNumber
Case 1
'Average rate
avgRate = avRate
Case 2
'Realized gain
avgRate = totalGain
Case 3
'Invested
avgRate = totalCost
Case 4
'Bal qty
avgRate = totalQty
Case 5
'Net worth (total quantity times most recent rate)
avgRate = totalQty * rate
Case 6
'Total profit (total sale amounts)
avgRate = totalProfit
Case 7
'Unrealized gain
avgRate = totalProfit - totalGain
Case 8
'Overall cost
avgRate = overallCost
Case Else
avgRate = CVErr(xlErrNum)
End Select
End Function
I have added a new version which tests for the first argument being an array or a range (and assumes the second argument is of the same type). OP has asked me to check for the case where it is a single element array or single-cell range as well. The main point of allowing arrays etc. is that you can have a function call like:
=avgRate(FILTER($C2:$C10,C2:C10=10),FILTER($A2:$A10,C2:C10=10),8)
or
=avgrate($C$2,$A$2,8)
to select (in this case) just the first row. This makes the UDF more versatile in situations where you may have stocks from more than one company and want to filter on the company.
这篇关于计算平均价格、已实现收益和使用 FIFO 方法通过 UDF 的未实现增益的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
