☰

在 Excel 中计算股票利润/亏损

tag-icon • tag-icon microsoft-excel worksheet-function
在 Excel 中计算股票利润/亏损

我正在尝试创建一个简单的电子表格来计算我在股票市场上完成的每笔交易后的利润/亏损。这样我就可以随时跟踪我的投资组合表现，并保存历史值以供分析。

在下表中，A 至 C 列和单元格 D2 是输入；我想要一个公式来计算从 D3 向下的 D 列（“资产总额”）。当操作（B 列）为“买入”时，该值应保持不变。如果操作为“卖出”，则该值应更新以反映利润/亏损。

有人可以帮忙吗？

       ABCDEFG
                         交易初始
 1   股票行动价值资产总投资
 2 苹果 购买 1000 512 512
 3 苹果 卖 1001 513
 4 谷歌 购买 7000 513
 5 谷歌销售 7004 517
 6 推特 购买 20016 517
 7 netflix 购买 14000 517
 8 推特 卖出 20000 501
 9 索尼 购买 19000 501
10 索尼 销售 19256 757
11 netflix 出售 14064 821

解释/理由：每只股票都有一个名义价值（五） 的A×1000，其中A是股票名称首字母的数值（apple=1000、google=7000、twitter=20000、netflix=14000、sony=19000）。交易（买入和卖出）值均为五和五+4n， 不一定按此顺序， 在哪里n是一个向上的数字（apple=1，google=2，twitter=3，netflix=4，sony=5）。这样，交易就透明且可解读，而不是神秘莫测。例如，从 512 到 513 的增加（第 2 行和第 3 行之间）只能是苹果以 1000 的价格买入后以 1001 的价格卖出的利润。从 517 到 501 的减少（第 7 行和第 8 行之间）只能是推特以 20016 的价格买入后以 20000 的价格卖出的亏损。

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

答案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)。如上所示，如果该行上的股票 ( ) 等于当前行上的股票 ( )，则该值为行号，否则为 0。AnA3
  • 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 数据位于此答案的来源中。

相关内容