(匯入最新收盤行情)在證券買賣交易中，投資者最關切為個股每日收盤行情，當股市交投熱絡時，報紙的證券行情表，自然成為閱讀率最高的專欄。然而，對Excel的使用者而言，在區域網路或數據機的撥號網路環境中，勿須藉助報紙的證券行情表來獲取證券行情，僅須運用Micrsoft Excel的「Web查詢」功能，即可取得查詢及匯入Web網站的證券行情資料。
●HowTo：如何匯入最新收盤行情？ 在Excel 2000中，請您選取「資料」→「取得外部資料」→「新增Web查詢」選項，來進行Web查詢作業，Excel將顯示如圖1之對話方塊：
圖1 Web查詢對話方塊在圖1中，可以在第一個文字方塊內，輸入所要查詢的網頁位置，在本文中，筆者使用台証綜合證券網站中的每日收盤行情網頁：Http://venus.tsc.com.tw/mis_AL.htm，來作為例舉。依序在第二個選項類別中，可以設定運用何種方式來載入該網頁的資料，其中設定值可以是整個網頁的資料、僅載入網頁中所有的表格資料或是網頁中若干個表格資料。至於最後第三個選項類別，則可以決定載入於工作表的網頁資料係以文字方式來顯示、RTF格式或Html格式來匯入。一般來說，如為求最完整的顯示方式，則選用Html格式。在本文中，筆者設定表格方式，並以Html格式載入該網頁資料。
完成了上列設定之後，按下「確定」鈕，顯示如圖2之對話方塊：
圖2 「傳回外部資料」之對話方塊在圖2中，請指定置放資料的儲存格位置，即可將網頁資料載入於工作表中（如圖3）：
圖3 個股每日收盤行情行情表若要更新圖3內「Web查詢」之資料，則請選擇「資料」→「更新資料」指令。
(登錄證券交易紀錄)在本文所設計的證券交易紀錄彙總模式內的『交易內容』工作表中，筆者設計了一張列表來登錄及對應證券買賣交易紀錄，如圖4所示：
圖4 證券買賣交易紀錄在圖4中，於C欄登錄「股票代號」後，D欄立即顯現「股票名稱」。以代表第一筆資料記錄的D4儲存格而言，其公式為：
=IF（C4<>"",VLOOKUP（TEXT（C4,0）,股票代碼區,2,FALSE）,""）
列公式表示，當代表股票代號的C4儲存格為空白時，則D4儲存格亦為空白。然而，當C4儲存格不為空白時，則D4儲存格的值為：
VLOOKUP（TEXT（C4,0）,股票代碼區,2,FALSE）
此一公式使用了TEXT函數將以數字型態所輸入的C4儲存格內容，轉換成文字型態後，再以VLOOKUP函數參照對應到先前『每日個股行情』工作表中被命名為「股票代碼區」的A8:B1006儲存格範圍。
因此，當C4儲存格的內容為「2312」時，則D4儲存格可得出「金寶」。
此外，筆者於第一筆資料記錄之其餘各欄中設定下列公式：
上列公式中，由於交易性質，證券交易金額均取位至整數，因此使用INT函數。而「券商手續費」與「證券交易稅」為「費率」工作表中已命名的A3及B3儲存格。（如圖5）
圖5 證券費率完成第一筆資料記錄的公式後，請將上列各欄公式複製到其餘各筆證券買賣資料記錄中，並將已登錄的各筆資料紀錄命名為TransBase。
(買賣彙總報表)『交易內容』工作表中內含各筆證券交易紀錄，其型式為依時間排列的流水型式資料。在『交易內容』工作表內，要將此些紀錄製作成彙總報表，必須使用Excel的樞紐分析表功能。
●HowTo：如何製作彙總報表？首先將儲存格游標置放於『交易內容』工作表內，在功能表中選擇「資料」→「樞紐分析表及圖報表」指令，以顯示樞紐分析精靈（如圖6）：
圖6 樞紐分析精靈3-1在圖6中請按「下一步」按鈕，將資料來源範圍設定為Transbase（如圖7）：
圖7 設定資料來源範圍接著請按「下一步」按鈕，設定樞紐分析表的存放位置（如圖8）：
圖8 設定樞紐分析表的存放位置在圖8中按下「版面配置」按鈕，將樞紐分析表的欄位結構設定如圖9之畫面：
圖9 設定樞紐分析表的欄位結構設定完後，請按「確定」鈕。接著請按「完成」鈕，即可製作完成如圖10的證券買賣交易彙總日報表：
圖10 證券買賣交易彙總日報表 ●HowTo：將日報表彙集成月報表？圖10內的資料為日報表，倘若要將日報表彙集成月報表，則請先將儲存格游標置放於代表日期欄位的C2儲存格，選擇「資料」→「群組及大綱」→「群組」指令，並於「數列群組」對話方塊內設定「月」群組選項（如圖11）：
圖11 設定『數列群組』對話方塊按「確定」鈕後，顯現如圖12之月報表畫面：
圖12 證券買賣交易彙總月報表●HowTo：修改彙總報表？圖12的報表，尚未顯示『買進平均單位成本』、『賣出平均單位售價』、『結存持股』、及『買(賣)超』等彙總資料。此些彙總資料必須由證券交易紀錄資料庫之各項欄位進行公式運算而求算出。因此，筆者將圖12作進一步的修改，有關修改的程序如下：
1.增添『買進平均單位成本』欄位：
將儲存格游標置放於樞紐分析表如圖12中之B資料欄位內，再按下滑鼠右鍵，並選擇「公式」→「計算欄位」選項（如圖13）：
圖13 選擇「計算欄位」指令 點選「計算欄位」選項指令後，顯示如圖14畫面：
圖14 在樞紐分析表中，加入計算欄位 於圖14畫面的「名稱」欄位中，加入『買進平均單位成本』，並在「公式」欄位中設定下列公式：
=IF（ISERR（買進總價/買進股數）,0,（買進總價/買進股數））
按「新增」鈕後，再依上列程序於名稱欄中加入下列欄位及公式：
2.完成計算欄位的增添作業後，就可以更改樞紐分析表結構：
將儲存格游標置放於樞紐分析表內，選擇「資料」→「樞紐分析表及圖報表」，再點選「版面配置」選項並將樞紐分析表的結構變更成如圖15之畫面。
圖15 變更樞紐分析表的欄位結構 設定完成後，請按下「確定」鈕後，再按下「完成」鈕，則如圖16的報表立即增加了『買進平均單位成本』、『賣出平均單位售價』、『結存持股』、及『買(賣)超』等四個欄位。
圖16 變更欄位結構後之樞紐分析表 在圖16中，所顯示者為各股之彙總資料。倘若要顯示構成彙總項目的明細資料，則只要將滑鼠指向該彙總資料，再連按滑鼠左鍵兩下即可。
例如，在圖16中，對C5儲存格連按滑鼠左鍵兩下，可顯示所有五月份聯電的買賣交易紀錄。（如圖17）
圖17 五月份聯電的買賣交易紀錄(計算交易損益)
登錄了各筆買賣證券交易後，根據買進成本、賣出價格及未賣出持股的最新收盤行情，則可以計算出各項證券買賣交易的損益。於「損益彙算」工作表中，筆者設計了一個損益彙算模式，自動顯示了所有投資股票的交易損益（如圖18）。
圖18 損益彙算模式●HowTo：自動顯示已投資的股票名稱？ 在圖18中，代表已投資股票名稱的A欄，係以公式來自動顯示股票名稱。以A2儲存格為例，其公式為：
=IF（LEN（INDIRECT（"買賣彙總！A"&11*（ROW（）-1）-7））>4,"",
（INDIRECT（"買賣彙總！A"&11*（ROW（）-1）-7）））
在「買賣彙總」工作表的樞紐分析表內，彙總顯示了已投資的股票名稱。因此，上列公式，以參照方式對應「買賣彙總」工作表內A欄的股票名稱，來顯示已投資的股票名稱。
但是，要如何才能正確地自動顯示「買賣彙總」工作表A欄內已存在的股票名稱資料呢？
首先，觀察到「損益彙算」工作表內，代表股票名稱的儲存格分別為A2、A3、A4、A5…等儲存格位，而「買賣彙總」工作表內，代表股票名稱的儲存格，則分別為A4、A15、A26、A37…等儲存格位，其間相差了11個儲存格位。因此，「損益彙算」工作表內，A欄的股票名稱可使用下列字串公式對應到「買賣彙總」工作表內所對應股票的名稱：
"買賣彙總！A"&11*（ROW（）-1）-7”
例如：「損益彙算」工作表內，A2儲存格套用至上列公式，則因該A2儲存格的ROW（）值為2，而A2儲存格內11*（ROW（）-1）-7為11*（2-1）-7亦即等於4。因此上列字串公式之對應值為 “買賣彙總！A4”。同理「損益彙算」工作表內A3、A4、A5...等儲存格位，按上列公式可對應成 ”買賣彙總！A15”、”買賣彙總！A26”、”買賣彙總！A37”…等文字。
然而上列公式的值為文字值，而要真正對應到「買賣彙總」工作表內的儲存格位，則須使用INDIRECT函數。例如：A2儲存格內的值若為INDIRECT（“買賣彙總！A4”），則其值等於 =買賣彙總！A4 ，亦即為「聯電」。因此，先前之公式，需要再修正為：
INDIRECT（"買賣彙總！A"&11*（ROW（）-1）-7）
此外，上列公式，有可能對應到「買賣彙總」工作表內代表加總的統計數值之標題文字（如圖19）：
圖19 損益彙算報表內之加總統計一般而言，此些加總統計數值之標題文字皆甚長，而非為本模式內作為股票名稱的四個文字長度。因此，筆者以下列公式，來判斷是否對應到「買賣彙總」工作表內的加總統計數值之標題文字：
=IF（LEN（INDIRECT（"買賣彙總！A"&11*（ROW（）-1）-7））>4,"",
（INDIRECT（"買賣彙總！A"&11*（ROW（）-1）-7）））
如對應到「買賣彙總」工作表內的加總統計數值之標題文字，則其文字長度依LEN函數判斷，必大於4，因此，其值為空白；否則依前列INDIRECT函數來連結「買賣彙總」工作表內的股票名稱儲存格。
完成「損益彙算」工作表內，代表股票名稱的A2儲存格公式後，可以將其複製到A欄中其餘儲存格內，以便自動傳回相對應於「買賣彙總」工作表內的各個股票名稱。
●HowTo：自動在準則區域內顯示已投資的股票名稱？ 於「損益彙算」工作表內，筆者使用了DSUM函數來計算「交易內容」工作表內，已投資股票的「買進股數」、「賣出股數」、「結存持股」、「買進總額」及「賣出淨額」之累計數額。
DSUM函數的語法為： DSUM（資料庫,欄位,準則）
於「損益彙算」內，各欄位之DSUM函數的引數可設定如下：
資料庫引數可設定為TransBase，TransBase為「交易內容」工作表內各筆紀錄所構成之資料庫範圍。而欄位引數可設定為所要求計算的「買進股數」、「賣出股數」、「結存持股」、「買進總額」及「賣出淨額」等欄位字串。至於準則引數，必須於準則內的「股票名稱」欄位內對應至「損益彙算」工作表內，要求計算的各列股票名稱。因此，於「準則」工作表內以公式自動顯示各個準則（如圖20）：
圖20 「準則」工作表在圖20中，代表DSUM準則的A欄，係以公式來自動顯示「損益彙算」工作表內已投資的各個股票名稱。
以代表第一筆股票名稱的A2儲存格為例，其公式為：
=INDIRECT（"損益彙算！A"&（ROW（）/2）+1）
如同先前股票名稱的對應方式。「準則」工作表內，股票名稱資料儲存格的欄位分別為A2、A4、A6、A8…等儲存格位，而「損益彙算」工作表內，代表股票名稱的儲存格之欄位分別為A2、A3、A4、A5…等儲存格位，其間相差1個儲存格位。因此，「準則」工作表內A欄的股票名稱可使用下列字串公式對應到「損益彙算」工作表內所對應股票的名稱：
"損益彙算！A"&（ROW（）/2）+1）
例如：將上列公式套用至「準則」工作表內A2儲存格，則因該A2儲存格的ROW（）值為2，而A2儲存格內 （ROW（）/2）+1 為 （2/2）+1 ，亦即等於2。因此，上列字串公式對應值為 "損益彙算！A2”。同理「準則」工作表內A4、A6、A8…等儲存格位，按上列公式可對應成 "損益彙算！A3”、"損益彙算！A4”、"損益彙算！A5”…等文字。
然而，上列公式的值為文字值，若要真正對應到「損益彙算」工作表內的儲存格位，則須使用INDIRECT函數。例如，A2儲存格內的值若為INDIRECT（“損益彙算！A2”），則其值等於 =損益彙算！A2 ，亦即為「聯電」。因此，先前之公式，需要再修正為：
=INDIRECT（"損益彙算！A"&（ROW（）/2）+1）
●HowTo：彙算「買進股數」、「賣出股數」、「買進總額」及「賣出淨額」累計數？ 在「損益彙算」工作表內，B欄代表已投資股票的「買進股數」加總數。該欄位各個儲存格位係以公式來自動計算「買進股數」加總數。以代表第一列股票「買進股數」的B2儲存格為例，其公式為：
=IF（A2="","",DSUM（TransBase,"買進股數",
INDIRECT（"準則！A"&2*（ROW（）-1）-1&":A"&2*（ROW（）-1））））
上列公式表示，當被投資股票的名稱為空白時，則代表此筆資料無任何欄位資料，因此「買進股數」為空白。然而，當被投資股票的名稱非為空白時，則B2儲存格的值為下列公式：
DSUM（TransBase,"買進股數",
INDIRECT（"準則！A"&2*（ROW（）-1）-1&":A"&2*（ROW（）-1）））
該公式中的準則引數為：
INDIRECT（"準則！A"&2*（ROW（）-1）-1&":A"&2*（ROW（）-1））
此公式代表「準則」工作表內相對應的準則區域，該準則區域為相鄰的兩列儲存格，其公式已於先前述及。
以上列公式套用到代表第一列股票「買進股數」的B2儲存格，其最終之準則引數為：
INDIRECT（"準則！A1:A2”）
因此，其DSUM公式為：
DSUM（TransBase,"買進股數",INDIRECT（"準則！A1:A2”））
其值顯現為60,000。
完成「損益彙算」工作表內代表「買進股數」的B2儲存格公式後，可以將其複製到B欄中其餘儲存格內，以便自動計算其餘各股票「買進股數」之累計數。
同理，可以求算「損益彙算」工作表內代表「賣出股數」的C欄儲存格，以代表第一列股票「賣出股數」的C2儲存格為例，其公式為：
=IF（A2="","",DSUM（TransBase,"賣出股數"
,INDIRECT（"準則！A"&2*（ROW（）-1）-1&":A"&2*（ROW（）-1））））
而「損益彙算」工作表內代表第一列股票「買進總額」的E2儲存格，其公式為：
=IF（A2="","",DSUM（TransBase,"買進總額"
,INDIRECT（"準則！A"&2*（ROW（）-1）-1&":A"&2*（ROW（）-1））））
至於「損益彙算」工作表內代表第一列股票「賣出淨額」的F2儲存格，其公式為：
=IF（A2="","",DSUM（TransBase,"賣出淨額"
,INDIRECT（"準則！A"&2*（ROW（）-1）-1&":A"&2*（ROW（）-1））））
複製上列各公式到相對應的儲存格欄位內，以便自動計算其餘各種股票之「賣出股數」、「買進總額」及「賣出淨額」累計數。
●HowTo：計算「收盤價格」、「股票投資之損益計算」？ l 收盤價格：
在「損益彙算」工作表內，G欄代表已投資股票的「收盤價格」。該欄位各個儲存格位係以公式來自動參照「每日個股行情」內之收盤價格，以G2為例，其公式為：
=IF（A2="",0,VLOOKUP（A2,Pricebase,9,FALSE））
上列公式，倘若代表股票名稱的A2儲存格非為空白時，則運用VLOOKUP函數，將代表股票名稱的A2儲存格，對應到『每日個股行情』工作表中已命名為Pricebase的$B$9:$N$1007儲存格範圍內，具相同股票名稱的第9欄資料（收盤價格）。因此，當A2儲存格之內容為「聯電」時，則其於87年10月23日的收盤價格為$36.2。
將G2儲存格內之公式複製到G欄內其餘的儲存格內，以便得出同一欄位內各儲存格所對應的收盤價格。
l 股票投資之損益計算：
根據下列算式，可計算股票投資之損益計算：
該股票之持股市值 * （1-證券交易稅-券商手續費）+賣出淨額 - 買進總額
因此，在「損益彙算」工作表內，I欄內代表第一列股票損益計算之I2儲存格，其公式可設定為：
=H2*（1-證券交易稅-券商手續費）+F2-E2
將I2儲存格內之公式複製到I欄內其餘的儲存格內，以便得出同一欄位內各儲存格所對應的損益計算。
l 各欄數值之總體彙算：
在本模式中，於「損益彙算」工作表之報表內最後一列中，以SUM函數，彙算各欄數值，即可彙算各欄之總體數值。
l 報表之更新計算：
「損益彙算」工作表之報表，由於其代表股票名稱的A欄係參照到『買賣彙總』工作表內之樞紐分析報表。因此，唯有更新『買賣彙總』工作表內樞紐分析報表之數值時，「損益彙算」工作表之報表方能得出最正確之損益數值。換言之，若要計算「損益彙算」工作表內正確之損益數值，則必須於『買賣彙總』工作表內選擇「資料」→「更新資料」指令。
(繪製圖表)完成了損益彙算後，可將損益彙算表內的資料轉換成圖表。例如：選擇「插入」→「圖表」指令，以立體長條圖顯示目前持有股票之市值狀況（如圖21）：
圖21 持有股票之市值狀況結語證券投資者平日往往忙於盤勢變化的觀察與分析，而疏於對買賣證券的各項交易記錄作更進一步的登錄與彙算。為提供投資證券者一個簡便的模式來改善證券投資的資料登錄與彙算作業，本文使用「Web查詢」來取得上市與上櫃公司各股每日之收盤行情，並以工作表函數及公式設定來登錄交易記錄，並使用「樞紐分析表」來彙算交易彙總報表。
此外，在計算已投資股票的交易損益報表時，您可運用INDIRECT、DSUM、VLOOKUP、IF及LEN等函數來構成一個自動計算模式，並根據模式內的資料來繪製相關圖表。
[摘自:台灣微軟 - 作者 : 洪士吉]
這篇文章最後由 mazeya 在 2004/10/08 19:31:28 重新編輯。
|
加好友 
悄悄話
YiLan
Post By：2004-7-9 8:04:00
