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.kjueaiud.com/
