Расчет прибыли / убытка по акциям в Excel

Question

Я пытаюсь создать простую таблицу для расчета прибыли / убытка после каждой полной транзакции, которую я выполняю на фондовом рынке. Таким образом, я смогу отслеживать эффективность своего портфеля в любой момент и сохраню исторические значения для аналитических целей.

В приведенной ниже таблице столбцы с A по C и ячейка D2 являются входными данными; Я хочу, чтобы формула вычисляла столбец D ("общее количество активов") от D3 и ниже. Значение должно оставаться неизменным, когда действие (столбец B) - "покупка". Если действие "продать", значение должно обновиться, чтобы отразить прибыль / убыток.

Кто-нибудь может помочь?

       A          B           C          D             E              F              G
                         transaction                                             initial
 1   stock      action   value         assets total                              investment
 2   apple      buy      1000          512                                          512
 3   apple      sell     1001          513
 4   google     buy      7000          513
 5   google     sell     7004          517
 6   twitter    buy      20016         517
 7   netflix    buy      14000         517
 8   twitter    sell     20000         501
 9   sony       buy      19000         501
10   sony       sell     19256         757
11   netflix    sell     14064         821

Объяснение / обоснование: каждая акция имеет номинальное значение (V) A × 1000, где A - числовое значение первой буквы в названии акции (apple = 1000, google = 7000, twitter = 20000, netflix = 14000 и сони = 19000).  Все значения транзакции (покупка и продажа) равны V и V+4 ⁿ, необязательно в том порядке, где n - число с одним номером (apple = 1, google = 2, twitter = 3, netflix = 4 и sony = 5).  Таким образом, транзакции являются прозрачными и декодируемыми, а не таинственными.  Например, увеличение с 512 до 513 (между строками 2 и 3) может быть только прибылью, полученной от продажи яблока за 1001 после покупки его за 1000.  Снижение с 517 до 501 (между строками 7 и 8) может быть только убытком от продажи твиттера за 20000 после покупки его за 20016.

821 = 512+1+4-16+64+256

Answer 1

Я предполагаю, что вы уже установили D2 =G2 .  Установите D3 в

=IF(B3="buy", D2, D2+C3-INDEX($A$1:$C$99, MAX(ROW(A$2:A2)*(A$2:A2=A3)), 3))

заменив 99 на верхнюю границу номера последней строки.  Введите Ctrl+Shift+Enter, чтобы сделать его формулой массива.  Перетащите / заполните.

Шиворот навыворот:

• ROW(A2)*(A2=A3) - хитрый способ сказать IF(A2=A3, ROW(A2), 0) , потому что TRUE - 1, а FALSE - 0.
• ROW(A$2:A2)*(A$2:A2=A3) - это виртуальный массив, который проходит от первой строки данных ($2) до строки выше текущей (2).  Как указано выше, значением является номер строки, если запас (An) в этой строке равен запасу в текущей строке (A3), и 0 в противном случае.
• MAX(ROW(A$2:A2)*(A$2:A2=A3)) является наибольшим значением в вышеуказанном виртуальном массиве; т. е. номер последней строки (наибольший номер строки) выше текущей, где акция равна акции в текущей строке.
• INDEX($A$1:$C$99, (the above), 3) получает значение (столбец C ; т. Е. 3-й столбец) для последней строки над текущей, где акция равна акции в текущей строке ,

• ОК, если текущая транзакция (столбец B) является «покупкой», общая сумма активов такая же, как и в предыдущей строке; в противном случае это сумма предыдущих активов плюс цена продажи (C3) за вычетом цены покупки этой акции (формула INDEX(…)).

            

Согласно предупреждению @ fixer1234, это не будет обрабатывать перекрывающиеся транзакции, такие как

apple    buy
apple    buy
apple    sell
apple    sell

и, в то время как он получит правильный результат для частичных транзакций, таких как

apple    buy    16000
apple    sell    8005
apple    buy     1000
apple    sell    9012

промежуточные значения не будут тем, что вы хотите.

Для полноты вот ваши оригинальные номера:

          

В целях тестирования копируемые данные TSV находятся в источнике этого ответа.