用Excel的宏编制个人证券账户管理器

钱勤

Excel宏的简介
　　重所周知,如果在 Microsoft Excel 中经常重复某项任务，可以用宏将其变为可自动执行的任务。宏就是贮存在 Visual Basic 模块中的一系列命令和函数，并且在需要执行该项任务时可随时运行的程序。在Excel中记录宏就如同用磁带录音机录制音乐，然后运行宏使其重复执行或"回放"这些命令。也可以利用Microsoft Excel自带的Microsoft Visual Basic 宏编辑器，直接编制宏。
本资金管理器的使用特点
　　下面介绍一个采用Excel宏编制的个人证券账户管理器。投资证券的朋友们都知道，在证券市场的每一次成功的委托买卖，都要打印交割单，上面详细记录了每一次操作的信息，利用它才能清楚的计算出每只股票的成本价，如果在同一只股票上多次操作，计算这只股票的成本价就比较麻烦，同时计算盈亏也是如此。实际上，很少有人能够详细了解自己每只股票的盈亏累计，大多数人掌握一个总体账面盈亏也就足够了。
　　如果能够养成一个比较好的习惯，及时将每一次从证券公司打印的资金对账单输入到自己的电脑，在利用本管理器，就能够详细跟踪自己账面上的盈亏，查询每只股票准确的均价，也许会对你的操盘方式起到一定的辅助作用。

本软件实际上是一个Excel文档，只需要输入两个工作表：
1．流水明细工作表格式如下： 日期 业务名称 资金发生 后资金余额 证券名称 价格 发生数量 后股票额
1998年11月13日 OUT -4956.86 5040.14 江苏工艺  16.38 300  300  
　　表头项目从标准的资金对帐单中选取，实际输入时，业务名称可以用任意代码表示（这里买入/卖出股票out/in，现金存取分别用in/out等），但其他各项一定要准确录入，特别是资金发生项入账用正数，出帐用负数，发生数量项买入时用正数，卖出时用负数。
2．价格工作表格式如下：
证券名称 最新价
亚盛集团 13.9
　　分别输入目前持有股票的最新价格即可，已抛售的股票可以不用保留。
　　只要每次准确更新上述两个工作表，然后执行快捷工具栏? 宏按钮，即可打开主菜单，执行相应操作。下面着重介绍一下"全部交易合计"功能的实现。

编制原理，程序详解
　　打开Excel,建立一个新工作簿，按照上述方法建立两个工作表流水明细表和价格表,先可以任意输入数据。选择菜单中工具→宏→Visual Basic编辑器（或者直接按热键Alt+F11）启动宏编辑器，在编辑器中选择菜单插入→添加模块，输入下列程序清单。
" *******主宏,将此宏定义快捷按钮***********
Sub main()
"由于宏执行时,屏幕会出现执行相应鼠标键盘动作的显示,严重影响运行速度,下面语句,将工作窗口最小化,可忽略显示更新,大大提高效率
ActiveWindow.WindowState = xlMinimized
UserForm1.Show " 打开主控表单
End Sub
" **********统计全部交易的子模块**************
Sub sumdata()
"首先检索成交明细表,查找共有多少条交易纪录,存入MaxRecords
"接着查夜灿卸嗌僦Ч善?存入MaxStocks(排除重复的),并将股票名称存入StkName数组
Dim StkName(1000) As String
Dim PriceArray(100, 2)
"为了加快执行速度,将窗口最小化,无须察看执行过程
ActiveWindow.WindowState = xlMinimized

"删除当前工作簿除流水明细表和价格表外的所有工作表，并且不显示确认对话框
Application.DisplayAlerts = False
For Each w In Worksheets
If w.name $#@60;$#@62; "流水明细表" And w.name $#@60;$#@62; "价格表" Then w.Delete
Next w
Application.DisplayAlerts = True
i = 0
For Each rw In Worksheets("价格表").Rows
If IsEmpty(rw.Cells(1, 1)) Then Exit For
If rw.Row $#@62; 1 Then
If rw.Cells(1, 2).Value $#@62; 0 Then
i = i + 1
PriceArray(i, 1) = rw.Cells(1, 1).Value
PriceArray(i, 2) = rw.Cells(1, 2).Value
End If
End If
Next rw
MaxPrice = i
"增加两张表,"合计表表保存计算结果

Sheets.Add
ActiveSheet.name = "合计表"
Worksheets("合计表").Cells(1, 1).Value = "序号"
Worksheets("合计表").Cells(1, 2).Value = "股票名称"
Worksheets("合计表").Cells(1, 3).Value = "个股盈亏"
Worksheets("合计表").Cells(1, 4).Value = "股票余额"
Worksheets("合计表").Cells(1, 5).Value = "成本价格"
Worksheets("合计表").Cells(1, 6).Value = "股票市值"
Sheets.Add "Temp表存储中间数据
ActiveSheet.name = "Temp"
Sheets("流水明细表").Select "选中流水明细表表,执行自动筛选
Selection.AutoFilter
i = 0
k = 0
While IsEmpty(Worksheets("流水明细表").Cells(i + 2, 1).Value) $#@60;$#@62; True
If IsEmpty(Worksheets("流水明细表").Cells(i + 2, 5).Value) = False Then
For j = 1 To k
If StkName(j) = Worksheets("流水明细表").Cells(i + 2, 5).Value Then Exit For
Next j
If j $#@62; k Then
k = k + 1
StkName(k) = Worksheets("流水明细表").Cells(i + 2, 5).Value
End If
End If
i = i + 1
Wend
MaxRecords = i
MaxStocks = k
AmountPast = 0
AmountLatest = 0
Amount = 0
For i = 1 To MaxStocks
Sheets("流水明细表").Select "对工作表流水明细表按照证券名称=逐个股票名称进行筛选
Selection.AutoFilter Field:=5, Criteria1:=StkName(i)
Sheets("Temp").Select "将临时工作表内容清空
Cells.Select
Application.CutCopyMode = False
Selection.ClearContents
Sheets("流水明细表").Select "将筛选后的数据拷贝到临时工作表temp中
Range(Cells(1, 1), Cells(MaxRecords + 2, 8)).Select
Selection.Copy
Sheets("Temp").Select
Range("A2").Select " 粘贴数据从temp工作表的第二行开始
ActiveSheet.Paste


"第一行数据用于数据合计,这里单支股票最大交易次数假定为100次
"对于散户而言已经足够了,如果是大户,请你自己增加
Range("C1").Select " 资金发生额合计
ActiveCell.FormulaR1C1 = "=SUM(R[2]C:R[102]C)"
Range("G1").Select " 股票发生数量合计
ActiveCell.FormulaR1C1 = "=SUM(R[2]C:R[102]C)"
With Worksheets("合计表") "将序号、证券名称、资金发生额、股票发生额存入合计工作表
.Cells(i + 1, 1).Value = I " 序号
.Cells(i + 1, 2).Value = Worksheets("Temp").Range("E3") " 证券名称
.Cells(i + 1, 3).Value = Worksheets("Temp").Range("C1") " 个股盈亏
.Cells(i + 1, 4).Value = Worksheets("Temp").Range("G1") " 股票余额
"如果某只股票还有余额，则计算该股票的成本价格（含交易手续费）、股票市值
If .Cells(i + 1, 4).Value $#@62; 0 Then
.Cells(i + 1, 5).Value = -.Cells(i + 1, 3).Value / .Cells(i + 1, 4).Value "成本价格
.Cells(i + 1, 6).Value = -Worksheets("Temp").Range("C1") "股票市值
.Cells(i + 1, 3).Value = 0 "如果无法查到该股的最新价格,则个股盈亏无法计算=0
Amount = Amount + .Cells(i + 1, 6).Value
For j = 1 To MaxPrice
If .Cells(i + 1, 2).Value = PriceArray(j, 1) Then
" 如果能查到最新价格，则计算个股盈亏金额
.Cells(i + 1, 3).Value = PriceArray(j, 2) * .Cells(i + 1, 4).Value - .Cells(i+1,6).Value
" 累加已清仓的股票盈亏金额
AmountLatest = AmountLatest + .Cells(i + 1, 3).Value
Exit For
End If
Next j
Else
AmountPast = AmountPast + .Cells(i + 1, 3).Value " 累加已清仓的股票盈亏金额
End If
End With
Next i
Worksheets("合计表").Cells(i + 2, 1).Value="已清仓股票累计盈亏: " + Format(AmountPast, "#.##") + "元"
Worksheets("合计表").Cells(i + 3, 1).Value ="持仓股票累计盈亏: " + Format(AmountLatest, "#.##") + "元"
Worksheets("合计表").Cells(i+4,1).Value="总体账面累计盈亏:"+Format(AmountPast+AmountLatest,"#.##")
Worksheets("合计表").Cells(i + 5, 1).Value = "账面市值: " + Format(Amount, "#.##") + "元"
Sheets("流水明细表").Select
Selection.AutoFilter " 关闭工作表流水明细表筛选状态
Range("A1").Select
Application.DisplayAlerts = False " 删除临时工作表
Sheets("Temp").Delete
Application.DisplayAlerts = True
Sheets("合计表").Select " 设置有关显示格式
Range("E:E").Select
Selection.NumberFormatLocal = "0.00"
Columns("C:C").Select
Selection.NumberFormatLocal = "0.00_);[红色](0.00)"
ActiveWindow.SplitRow = 1 " 设置分割窗口
ActiveWindow.SplitColumn = 0
Range("D1").Select " 按照股票数量余额进行排序
Selection.Sort Key1:=Range("D1"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, SortMethod _
:=xlPinYin
For i = 1 To MaxStocks " 添加序号
Cells(i + 1, 1) = i
Next i
ActiveWindow.Panes(3).Activate
Range("A1").Select
UserForm1.Hide "关闭主控表单
i = MsgBox("转换完毕!请浏览合计工作表", 0)
ActiveWindow.WindowState = xlMaximized "直接切换到合计工作表状态
End Sub



注：以上"(半角单引号)引导的下划线部分均为注释,可以忽略。
　　接下来，点击宏编辑器的菜单项：插入→添加用户窗体，建立如图二所示的表单，添加相应按钮，建立相应动作。这里我们添加两个按钮：合计和退出按钮。分别在两个按钮上双击，进入单击动作编程，在合计按钮单击命令中输入语句：call SumData，在退出按钮单击命令中输入：Hide。
　　自此，宏编辑部分大功告成，下面可以关闭编辑器，在Excel中，添加宏按钮：点击菜单项工具→自定义，选择命令页，在类别中选择宏后，命令中就会出现带有图标的自定义按钮项，将此项直接拖到Excel的快捷工具栏上，再点击自定义窗口中的更改所选内容按钮，选择指定宏，将宏名选择为main后确定即可。
总结及扩充性能介绍
　　以上，读者可以对以上宏加以扩充，并且通过增加一些设置，制作报表打印部分，个股详细查询、结合Excel强大的图表功能，相信读者完全可以制作出更加实用、完善可与商业软件相媲美的文档来。

原文转自：http://www.ltesting.net