Рассчитать среднюю цену инвестирования без вспомогательных столбцов/таблиц

Этот пост действительно стоит прочитать. Доступна другая лучшая версия этой темы здесь.

Если вы посмотрите на таблицу ниже, я пытаюсь найти среднюю цену для каждой транзакции без добавления вспомогательных столбцов. Средняя цена верна, когда на стороне Купить, но показывает неверную среднюю цену на стороне Продажа, для которой я ищу формулу, формулу массива или UDF для средней цены. столбец.

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

Формула для Value у меня есть =E3*D3, для Holding — =SUM($D$3:D3), а для Средней цены — =SUMPRODUCT($D$3:D3,$E$3:E3)/SUM($D$3:$D3), которую я перетащил вниз. Вроде все правильно, кроме последнего значения 10150. В идеале должно быть 10 100 в соответствии с приведенной ниже логикой FIFO.

1-й заказ: Количество = 225 | Цена = рупий. 10 000,00

2-й заказ: Количество = 75 | Цена = рупий. 10 200,00

Чтобы рассчитать среднюю цену, сначала рассчитайте значение (Количество x Цена). Следовательно:

1-я сделка: руб. 22 50 000,00

2-я сделка: рупий. 7 65 000,00

Общее количество = 300

Общая стоимость первых двух заказов: 100 000 руб. 30,15,000.00

Разделите общую стоимость на общее количество:

рупий 30 15 000,00 ÷ 300 = 10 050,00 рупий (используя формулу =sumproduct)

3 июля мы разместили ордер на продажу 150 (из 300). Цена: рупий. 9 950,00

Теперь здесь будет применяться метод FIFO (first in first out). Метод проверит первую сделку (на стороне покупки). В данном случае это 225. 150 проданных акций будут вычтены из 225 (первое владение). Баланс, оставшийся от первого владения, который ранее составлял 225, теперь будет 225 - 150 = 75.

После FIFO таблица преобразуется таким образом после вычета количества продажи. См. первое количество, измененное с 225 до 75, потому что было продано 150 акций.

Date Side Qty Price Value Holding Avg Price
1-Jul Buy 75 10000 750000 75 10000
2-Jul Buy 75 10200 765000 150 10100

Обратите внимание: если бы количество проданных товаров превышало 225, то было бы принято решение перейти к следующей сделке, чтобы вычесть оставшееся количество.

Теперь, чтобы получить решение этой проблемы, необходимы дополнительные вспомогательные столбцы или вспомогательные таблицы, которые я хочу искоренить и найти формулу, формулу массива или UDF для расчета средней цены. Прошу специалистов Excel помочь мне с этой проблемой.

Еще один пример того, что я пытаюсь сделать, приведен ниже, где вложенная цена показывает неверную:

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

Выполнено после получения решения от @Tom Sharpe

Чтобы получить среднюю цену, я объявил две переменные avgRate и sumRate двойными и немного изменил код For Each. Посоветуйте, есть ли действенный способ. Спасибо, если это можно преобразовать в UDF, чтобы мне не приходилось запускать код снова и снова. Большое спасибо за прекрасное решение.

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

excel user-defined-functions stock vba fifo
person Kawalpreet Kaur    schedule 04.07.2021    source источник
comment
Какой ответ будет правильным и как?   -  person Harun24HR    schedule 04.07.2021
comment
Я думаю, что вложенная цена равна 3, потому что во 2-й сделке было продано 3 акции с первой покупки, а затем в 4-й сделке было продано 15 акций, из которых 2 были взяты по цене 10, а остальные были взяты по цене 10. 3. Следовательно, все, что осталось, покупается по цене 3. Возможно, я плохо объяснил это, объяснение метода FIFO (первым пришел — первым вышел) дано в ссылке, которую я разместил. Спасибо за ваш ответ   -  person Kawalpreet Kaur    schedule 04.07.2021
comment
Пожалуйста, обратитесь к небольшому EDIT, который я написал в своем исходном сообщении. Спасибо   -  person Kawalpreet Kaur    schedule 04.07.2021
comment
Я пробовал несколько формул, таких как sumif, sumproduct, комбинация sumif и sumproduct, но не получил правильного ответа. Поделитесь своими попытками и объясните, где вы застряли.   -  person P.b    schedule 04.07.2021
comment
Будет ли это ожидаемым результатом? =SUMPRODUCT(A2:A5,C2:C5)/SUM(C2:C5) или =SUM(D2:D5)/SUM(C2:C5)   -  person P.b    schedule 04.07.2021
comment
Спасибо за ваш ответ. Мне будет трудно объяснить. Для простоты, можем ли мы сослаться на ссылку, которую я разместил? Если я узнаю, как они рассчитали 10 100 как цену инвестирования после сделки продажи, мне будет легче применить это решение к моему примеру.   -  person Kawalpreet Kaur    schedule 04.07.2021
comment
Вы связывались с Upstox по этому поводу? upstox.freshdesk.com/support/tickets/new   -  person JMP    schedule 04.07.2021
comment
Да... upstox сказал, что они показали свои расчеты и говорят, что это правильно, что использует метод FIFO. Далее они упомянули, что не поддерживают стороннее программное обеспечение. Я понял их логику и пытался воспроизвести эту логику в Excel, для которой я не могу написать формулу, и именно поэтому я пришел сюда за помощью.   -  person Kawalpreet Kaur    schedule 04.07.2021
comment
Не понял — вы просите нас помочь вам интерпретировать расчет на основе присланной вами ссылки? Предпочтительно, чтобы вы описали метод расчета, который вы ищете, чтобы мы помогли в этом посте, так как ссылки часто ломаются/становятся недействительными - и это не будет иметь большого значения для новых пользователей.   -  person JB-007    schedule 04.07.2021
comment
Вы можете работать в обратном порядке: у вас есть 22 акции, вы продали 18, поэтому у вас осталось 4 и вы используете LIFO.   -  person JMP    schedule 04.07.2021
comment
хорошо, давайте не будем смотреть на ссылку, я перефразировал свой вопрос в исходном посте и попытался объяснить, как работает логика. Пожалуйста, не минусуйте мой вопрос, так как это мой первый искренний пост, на который я получил ответ, на который я очень старался.   -  person Kawalpreet Kaur    schedule 04.07.2021
comment
@JMP, я понял логику FIFO и LIFO. Просто я не могу вывести цену инвестирования с помощью одной единственной формулы. Я ищу формулу, которая даст мне 3 в качестве цены инвестирования, потому что непроданные акции в последний раз были куплены по этой цене. Надеюсь, я правильно объясняю.   -  person Kawalpreet Kaur    schedule 04.07.2021
comment
Итак, вы хотите найти, сколько вы заплатили за любые акции, которые у вас остались после FIFO, и разделить это на количество оставшихся акций? За сколько вы продали акции, здесь не имеет значения?   -  person JMP    schedule 04.07.2021
comment
Да, пожалуйста. Может быть, я не правильно объяснил, поэтому было много путаницы.   -  person Kawalpreet Kaur    schedule 04.07.2021
comment
Не хочу омрачать это, но мне кажется, дело в том, что вы смотрите на пошаговый алгоритм, а не на формулу, и трудно понять, как вы могли бы реализовать это без нагрузки помощника столбцы. Если бы я пытался это сделать, я бы посмотрел на использование VBA с очередью для хранения количества акций и их цены в том порядке, в котором вы их купили, чтобы самые старые выходили первыми.   -  person Tom Sharpe    schedule 05.07.2021
comment
Будущие пользователи (а не вы) должны учитывать отрицательное голосование относительно полезности вопроса. Вы должны признать, что это сложно и не хватает многих деталей, чтобы остальные из нас могли восполнить их, пытаясь помочь вам.   -  person JB-007    schedule 05.07.2021
comment
@ JB-007 Да. Соглашусь со сложностью и признаю, что полной информации не предоставили. Извините за то, что я чувствую, что ответ на этот вопрос поможет не только мне, но и будущим пользователям, и поэтому я был бы признателен за некоторую помощь в решении этой суммы.   -  person Kawalpreet Kaur    schedule 05.07.2021
comment
@TomSharpe Да, я согласен с тем, что для решения этой проблемы необходим пошаговый алгоритм со вспомогательными столбцами. Я попросил одну формулу, потому что в моем Excel слишком много данных, а дополнительные столбцы были бы похожи на слишком много информации и визуально не выглядели бы хорошо для просмотра. Могу ли я получить решение VBA или UDF здесь, или мне следует начать новый вопрос с другими тегами excel vba или udf? Пожалуйста, совет. Спасибо   -  person Kawalpreet Kaur    schedule 05.07.2021
comment
Я думаю, что, вероятно, можно оставить этот вопрос (он привлек 60 просмотров, что довольно много) и просто добавить тег VBA. Вы также можете поместить данные по ссылке в свой вопрос в виде таблицы, чтобы людям не приходилось переходить по ссылке. Надеюсь, у меня будет шанс попробовать UDF, поскольку я выздоравливаю от вируса гриппа, и это даст мне что-то интересное.   -  person Tom Sharpe    schedule 05.07.2021
comment
@TomSharpe Спасибо, что проявили интерес к моему запросу. Я скоро отредактирую полный вопрос и поставлю данные в ссылку вместе с моей исходной таблицей и попытаюсь объяснить это хорошо. Берегите себя и выздоравливайте скорее.   -  person Kawalpreet Kaur    schedule 05.07.2021

Ответы (1)


arrow_upward
3
arrow_downward

Хорошо, вот тестовая версия реализации VBA.

Алгоритм:

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.

Программа использует класс BuySell, поэтому вам нужно создать модуль класса, переименовать его в BuySell и включить строки

Public rate As Double
Public qty As Double

Следующее идет в обычном модуле.

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

Для первой таблицы вывод

введите здесь описание изображения

так что средний курс 10100.

Для второй таблицы вывод

введите здесь описание изображения

поэтому средний балл равен 3.

ИЗМЕНИТЬ

Вот версия UDF, которая называется

=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
person Tom Sharpe    schedule 05.07.2021
comment
Спасибо за вб. надеюсь, сейчас ты чувствуешь себя лучше. Я очень рад понять алгоритм. когда я попытался запустить его, он выдал мне ошибку в строке Set bs = New BuySell. Я подумал, что мне нужно установить ссылку, поэтому я попытался найти в Google очередь сбора систем и попытался установить ссылку на mscorlib (C:\windows\microsoft.net\ framework\v4.0.30319\mscorlib.tlb), ms wmi scripting v1.2 lib и ms scripting runtime libraries, но ничего не работает. Посоветуйте пожалуйста в чем может быть проблема? - person Kawalpreet Kaur; 05.07.2021
comment
Да, моя ошибка, я забыл сказать, что вам нужно добавить модуль класса для класса BuySell - добавит его в мой ответ. - person Tom Sharpe; 05.07.2021
comment
Спасибо .. Мне нужно время, чтобы понять, как это работает. Я изучу алгоритм, а также протестирую его еще на нескольких примерах и вскоре свяжусь с вами. Спасибо, что сэкономили ваше драгоценное время. - person Kawalpreet Kaur; 05.07.2021
comment
С удовольствием. Я могу легко изменить его на UDF, который со временем будет более удобным. - person Tom Sharpe; 05.07.2021
comment
Если это удастся сделать, это поможет и обществу. Прежде чем сделать это, у меня могут быть некоторые вопросы, особенно обработка ошибок. Я должен сказать, что это кажется довольно хорошей логикой с подходом очереди сбора, которого я никогда раньше не видел. Я все еще погружаюсь в код, чтобы понять его лучше. Возможно, мне скоро придется вернуться к вам. - person Kawalpreet Kaur; 05.07.2021
comment
Голосование за первую попытку @Sharpe предложить решение на основе предоставленного LoD. - person JB-007; 05.07.2021
comment
Благодарю вас! Нужно немного больше работы, но, надеюсь, на правильных линиях. - person Tom Sharpe; 05.07.2021
comment
Спасибо, @TomSharpe Это был хороший урок из представленной вами логики. Я отредактировал свой исходный пост и опубликовал небольшую модификацию вашего кода, чтобы получить среднюю цену (которая работает как функция рабочего листа =sumproduct). Я был бы признателен, если бы вы могли изменить его на UDF. Спасибо еще раз. - person Kawalpreet Kaur; 05.07.2021
comment
Добавлен UDF, но он нуждается в проверке ошибок - например. если вы попытаетесь продать больше акций, чем купили. Должно быть, я написал код средней цены одновременно с вами, но в основном он такой же, как у вас. - person Tom Sharpe; 05.07.2021
comment
Выглядит отлично! Спасибо Вам за Вашу помощь. Я искал решение этой проблемы с 2 дней и не мог получить надлежащей помощи от поиска Google. Это решение поможет многим другим людям, таким как я, которые занимаются торговлей акциями, форекс, сырьевыми товарами или криптовалютой. Надеюсь, теперь вы полностью выздоровели. Будьте активными, живыми, здоровыми и в розовом цвете здоровья. Ваше здоровье ! - person Kawalpreet Kaur; 06.07.2021
comment
Спасибо за добрые пожелания (теперь я в порядке). Я, вероятно, добавлю немного проверки, например. для того, что происходит, когда очередь становится пустой, или она пытается вычислить среднее значение без остатка. Предложите, чтобы FiFo отображалось в заголовке (или, возможно, в виде тега), чтобы обеспечить поиск. Кроме того, это не совсем моя область, но я понимаю, что где-то там есть связанный с этим вопрос о трейдерах любого рода, которые продают определенное количество единиц акций и хотят знать, сколько эти единицы стоят им для покупки на основе FiFo. В любом случае, мне понравилось работать с вами над этим, и я желаю вам всего наилучшего. - person Tom Sharpe; 06.07.2021
comment
Привет! вернулся, чтобы проверить, отработали ли вы дополнительные чеки. Это будет выгодно для всех трейдеров. Я наткнулся на аналогичную старую тему, которая осталась без ответа. Я не мог дать ссылку на ваш ответ на это, так как у меня недостаточно репутации, чтобы комментировать там. Я также сделал копию вашей UDF, потому что хотел внести в нее несколько изменений, чтобы рассчитать Реализованную прибыль (зарегистрированную прибыль) и Нереализованную прибыль (прибыль, доступную на бирже). ), который снова ищет множество трейдеров. Но я не мог понять, как это сделать. Интересно, а не начать ли мне новую тему. - person Kawalpreet Kaur; 12.07.2021
comment
Нереализованную прибыль легко подсчитать. Оставшееся количество x Средняя цена. Ссылаясь на первую таблицу, 150 x 10100 = 1515000. Реализованная прибыль в этой таблице составляет 7500, которая рассчитывается как (Первое количество - Проданное количество) x Первая цена. Вот где у меня возникают трудности, особенно когда количество сделок больше. - person Kawalpreet Kaur; 12.07.2021
comment
Короче говоря, я с нетерпением ждал трех вещей. Инвестированная средняя цена (которую уже дает UDF), нереализованная прибыль (которую легко рассчитать - 1515000). Нужно знать, как рассчитать реализованную прибыль и можно ли вернуть все три вещи, используя одну и ту же пользовательскую функцию, добавив параметр в формулу. Пожалуйста, дайте мне знать, если у вас есть время изучить его и стоит ли мне начинать новую тему. Спасибо. - person Kawalpreet Kaur; 12.07.2021
comment
Привет @Kawalpreet Мне было бы интересно посмотреть на эти вещи, чтобы сделать UDF более полезным, хотя я сейчас довольно занят, поэтому не смогу тратить на это много времени. Да, я думаю, что вам следует создать новую тему, тем более, что в этой сейчас много комментариев. - person Tom Sharpe; 12.07.2021
comment
Спасибо @TomSharpe за ваш ответ. Я разместил новую тему. Я совсем не тороплюсь. Если есть возможность, посмотрите его. Спасибо и приятно провести время. - person Kawalpreet Kaur; 12.07.2021