如何利用excle计算股票流水买卖盈亏的方案讨论

CLL1998

关于利用excle计算股票流水买卖盈亏的方案讨论
     这个问题是由坛友hufangbao提出的，经过若干天的等待，除了我的方案，没有其他的计算方案回帖。倒不是这个问题有多难，主要是hufangbao的计算提示把很多人“绕进去了”。想到问题解析的逻辑方向是程序设计的首要前提，而遇到实际问题时往往又被表象迷惑，绕来绕去无从入手实在是在所难免，所以，在这里借论坛一隅抛砖引玉，将我的点点体会奉献大家，也算是从务虚回归务实，务会实际，来哈“使用价值”。
     
一、问题的提出

五月二十七日坛友hufangbao帖出“帮忙计算股票交易盈亏”的帖子
他的基础数据结构如图



   对如何计算股票交易盈亏他提示
   
   1，先把交易日期按降序排，从最近的交易开始计算
2，判断交易行为是卖出的（卖出的行为有七八种，比如下面的“对手最优价格卖”也是卖出，这里只有两种，就是“卖出”和“对手最优价格卖”）
3，如果是卖出这两种交易行为，向下查找最近的第一笔相同证券代码交易，
4，找到相同证券代码的交易后，判断交易行为是买入的（同样，买入的行为也有七八种，这张表“红股派息”和“买入”）
5，找到相同证券代码，并且交易行为也是买入的，判断成交数量要大于等于卖出数量（例如：卖出1000股，找到的如果是大于等于1000，就取成交金额，然后用卖出的成交金额减），得到盈亏
6，如果判断成交数量出现小于卖出数，说明这次卖出的股票不是一次买入的，（比如卖出2000股，如果找到的第一笔只有1000，那就是还要向以前日期查找）就继续向下查找，查到后，把第一次找到的成交数量，加起来，再判断成交数量是否大于等于卖出数，如果大于等于卖出数，就计算盈亏（找到的几次买入成交金额加起来，然后和卖出的成交金额相减）还是小于，第三次查找，一直把找到的几笔加起来满足大于等于卖出数，否则循环向下查找，如果最终到最后一行，找不到，就得出盈亏是零（肯定有找不到的情况,）
7，计算过的买入，要从列表中标示或扣除，否则下次可能又会重复使用，例如：
日期  证券代码   交易行为    成交数量    成交价   金额
5月30  600000     卖出       1000       13       13000
5月29  600000     卖出       1000       14       13000
5月20  600000     买入       1000       10       13000
5月10  600000     买入       1000       15       13000
5月30日卖出的找到的最近一次买入是5月20日买入的，应该是正确的。但5月29日向下查找最近一次买入还是5月20日买入就不正确了，应该扣掉计算过的，查找到5月10日买入才正确


二、计算方案选择
      
如果实际按hufangbao提示的方向做交易盈亏计算，对excle公式而言是非常艰难的，难就难在那不知何处是休止的“买入查询”及何条是可引用的“买入数据”。他的提示不是一般的“绕人”，而是非常的“绕人”。简直是无从下手。但又不能说hufangbao的提示是错误的，因为提示的核心是：“卖出的必定是已经买入的，最先买入的必定最先卖出”，而这正是交易盈亏计算立足的依据。
     
     感官“绕人”是一回事，把问题解决了才是硬道理。
     
     其实，如果用拓扑思维就不难理解“股票交易”与“进出库管理”在数据处理原理上是一致的。都是先有进(买)后有出(卖)，有即时存量（进出余量、买卖余量），有进出（买卖）差价核算。
   
     所以，现实可行的计算方案不是“先把交易日期按降序排，从最近的交易开始计算”，而是应按实际的买卖时间及操作顺序进行方案设计。方案的核心是贴紧现实，即时计算。
   
     在作实际数据处理时
     首先，对数据按交易日期作升序排序。
       其次，因为有不同的操作属性（买、卖、分红、送股……）及不同的操作对象（股票），
       作为必要的计算指针必须引入两列辅助列（隐形的或可见的，本方案为可见的）。
       再次，确定计算公式
         操作盈亏：如果买卖属性是卖出 则：操作盈亏=卖出量×(成交价－上笔交易的即期成本）
                       如果买卖属性不是卖出 则： 不计算操作盈亏
        
        即时存量：该股票历史上的（包括本次）累计（买入－卖出）
         
        即期成本：如果是首笔买入  即期成本=成交价
                  
                  如果是买入  即期成本=(成交金额＋上笔交易的即时存量×上笔交易的即期成本)／即时存量

                  如果是卖出   即期成本=0                   （如果 即时存量=0）
                                    即期成本=上笔交易的即期成本  （如果 即时存量<>0）

   
在上述涉及的获得数据中，稍微有点难度的是“上笔交易的即时存量”和“上笔交易的即期成本”
  这两个数据的获得通过index()和match()的组合实现，前面提到的两列辅助列的作用在这里得到体现。

计算效果如图




历史成交查询.rar (12.61 KB, 下载次数: 2261)

2011-6-6 23:15 上传

点击文件名下载附件

后面坛友hufangbao  提出要按时间顺序逐笔对冲买入股票的成本（也就是不按购入均价计算盈利）
这是个现实的问题，我在12楼作了回应。提出了逐笔对冲的计算公式，有兴趣的坛友可到12楼看看

好久没来坛报到了。。。。

[ 本帖最后由 CLL1998 于 2011-7-5 19:39 编辑 ]

CLL1998

郁闷
居然一个回帖都没有  成了冷贴
行或不行都成为一个问号

有木有？

hufangbao

对不起CLL1998，让你的贴子成了冷贴，原因是我没有及时回复。首选还是感谢你的解决问题，但我EXCEL水平太低，很多地方没有看懂，题目可能在是表述上出现问题！！！

原贴地址：http://club.excelhome.net/thread-723963-1-1.html
有一个问题没有解决：我并不想得到平均成本，只想得到他每笔的真实盈亏，例如：
时间         交易行为  证券名称  价格          数量     金额         你计算的盈亏   我需要得到的盈亏   
2011-01-06      买入     方大炭素   13.800         11300         155940.00
2011-01-07      买入           方大炭素    13.750         11400         156750.00
2011-01-07      卖出              方大炭素    13.918         11300         157273.00     1,617.14       157273-155940=1333
2011-01-10      卖出     方大炭素    13.810         11400         157434.00      400.26          157434-156750=684

为什么会出现上面的情况，就是计算了平均成本价，一个股票一般是频繁的做自己了解的几只股，列如这个股民在1月7日就做了一个T+0，为的是降低以后的成本，如果长期这样滚动一只股票，就无法真正知道他每笔的真实盈亏，因为平均成本价将几次的交易融合到一起。

这个客户7日卖出的，应找最近的一次买入，当然不是当天买入的，因为股市不能做T+0

[ 本帖最后由 hufangbao 于 2011-6-23 16:13 编辑 ]

hufangbao

如果把第一次买入改成5日,第二瓷买入改成6日,这样的交易
2011-01-05      买入  方大炭素   13.800      11300         155940.00
2011-01-06      买入  方大炭素   13.750      11400         156750.00
2011-01-07      卖出  方大炭素   13.918      11300         157273.00     1,617.14       157273-155940=1333
2011-01-10      卖出  方大炭素   13.810      11400         157434.00      400.26          157434-156750=684

那么7日卖出的应该是5日那天买入的股票
10号那天卖出的应该是6号那天买入的

[ 本帖最后由 hufangbao 于 2011-6-23 15:58 编辑 ]

hufangbao

我再改一下交易，你就明白我为什么不想要平均成本价了

如果把第一次买入改成5日,第二瓷买入改成6日,这样的交易
2011-01-05      买入  方大炭素   14    11300         158200.00
2011-01-06      买入  方大炭素   9      11400        102600.00
2011-01-07      卖出  方大炭素   12      11300         
2011-01-10      卖出  方大炭素   13.00      11400        

.两次买入后，平均成本价变成了11.49，很显然，7号那天卖出的是赔钱的，但如果按平均成本价算，也是挣钱的,事实上只有10号那天的卖出才是真正的盈利

[ 本帖最后由 hufangbao 于 2011-6-23 16:40 编辑 ]

hufangbao

再次感谢解答

原贴地址：http://club.excelhome.net/thread-723963-1-1.html

hufangbao

还有人想参与吗？

hufangbao

hufangbao

hufangbao