首页 | 社区 | 博客 | 招聘 | 文章 | 新闻 | 下载 | 读书 | 代码
亲,您未登录哦! 登录 | 注册

用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强大的图表功能,相信读者完全可以制作出更加实用、完善可与商业软件相媲美的文档来。

本栏文章均来自于互联网,版权归原作者和各发布网站所有,本站收集这些文章仅供学习参考之用。任何人都不能将这些文章用于商业或者其他目的。( Pfan.cn )

编程爱好者论坛

本栏最新文章