投資人面對分散在不同網站的資訊,經 常都得花費一番工夫搜尋整合,才能 成為有用的資訊。但其實只要善用Excel強大的網頁擷取功能,就能輕而易舉的解決這樣的難題。
為了介紹Excel強大的資訊整合功能,本 篇提供一個Excel試算表為範本,可修改成適合自己的。範本的主要功能是管理投資者所持有的資產,包括了股票、基金以及定存。這份試算表的特色是動態管理,在檔案開啟時,Excel試算表就會自動更新成最即時的資料,然後算出總報酬率及各類資產的淨值
及比例。注意:Excel試算表採用Microsoft Office 2013版本。
請輸入以下網址 http://www.masterhsiao.com.tw/bt/ article001/portfolio.xlsx
怎麼使用試算表? 整份試算表分為5個工作表,除了主表之 外,其中的4個工作表根據所定義的連線,分別到各網路擷取資料,這些工作表名稱為:匯率查詢、基金淨值、股票價格查詢及證交所資料。而主表根據各個工作表所取得的資料,整理出一張投資者所關心的報表。最上方的表格列出了總資產以及個別類別的比例,接著是黑色的股票表格、藍色的基金表格以及橘色的定存表格。
範本上的股票表格目前只列出3檔股 票,若使用者的股票在3檔以內,只需 改變股票名稱及代號即可。若是3檔以 上,則每1檔新的股票都必須新增1列, 每1列也都得新增對應的連線。每當有股票買賣時,使用者也必須手動更新所持有股數及平均成本價。當持有股票配息時,也得更新配息金額,這樣所計算出來的期末價值及報酬率才會準確。
目前的基金範例只有兩檔基金,使用者可以參考這兩檔,自行新增或刪除基金的資料,每1列都得有1個對應連線才行,這樣才能動態更新即時資訊。
Excel強大的網路擷取功能
這試算表可以做到資訊即時更新,主要是充分應用Excel的網路擷取功能, 讓Excel在檔案開啟時,自行到指定的網站中抓取最新的資料。雖然有些複雜,不過只要按照下列所述步驟,就可做一個具有這樣功能的試算表。
第一步:新增一個具有參數的連線
雖然Excel中「從Web」功能鍵可擷取到指定網頁的資 料,可是這樣還不夠方便,因為每一檔股票都需要1份不 同的網址。更方便的方式就是只用1個網址,而將股票代 號當成1個參數,而此參數就置於儲存格上。那麼,只要改變儲存格的值,指定的網址就會變更。
只要將上述的網址修改如下:http://tw.stock.yahoo. com/q/q?s= ["code"],這樣就會成為一個含有參數的 網址,參數名稱為code。這code參數可以置於任何儲 存格內,例如將code參數定義為B1儲存格,那麼當B1儲 存格的值為2412時,那麼Excel會將網址解釋為:http:// tw.stock.yahoo.com/q/q?s=2412,也就是中華電的股 價網址。只要更改儲存格上的股票代號,Excel就會自動抓取到該代號的股價了。
2接著用微軟的記事本開啟stockQuery.iqy的檔案,這 是一個單純的文字檔,切勿用微軟的Word來編輯。 開啟後將檔案第三列的網址s=2330修改成s=["code"],如圖中紅框內所示。
1要做一個具有參數的連線,首先要做一個 副檔名為iqy的查詢檔,然後再以這查詢檔 新增一個連線。
製作查詢檔的步驟如下,首先開啟一張新的工作表,然後:
① 點選「資料」索引標籤
② 點選「從Web」,就會出現一個子 視窗
③ 輸入奇摩股市股價之網址(以台積 電為例)
④ 按「到(G)」按鈕,下方就會出 現台積電股價的網頁
⑤ 將凱基客戶專區及股票價格區之黃 色右箭頭勾選成綠色打勾符號
⑥ 點選儲存圖示,將此連線儲存成.iqy的檔案。檔案名稱預設值為q?s=2330.iqy, 請用手動修改成較容易識別的名稱(如stockQuery. iqy),儲存於任一目錄
3Excel的查詢檔敘述到哪一個網址抓資料,當中的網址使用了一個code的參數。查詢檔做好之 後,接著就是使用這查詢檔新增一個連線。
步驟如下:
① 於「資料」索引標籤,點選 「連線」,就會出現「活頁 簿連線小視窗」
② 點選「新增」按鈕,會出現 「現有連線」小視窗
③ 然後點選「瀏覽更多」按 鈕,會出現「選取資料來 源」的小視窗
④ 找到放置stockQuery.iqy的 目錄之後,點選取該檔案
⑤ 點選「開啟」按鈕,這時候 「活頁簿連線」上就會新增 一個名稱為stockQuery的連線
4新增完新的連線之後,接著要對此連線 的內容做些修正,例如更改連線名稱、 什麼時候要重新抓取此連線資料,以及連線的參數到哪裡取得等。
步驟如下:
① 選取新增的連線(stockQuery)
② 點選「內容」按鈕,就會出現「連線內容」的子 視窗
③ 更改連線名稱(如stockQuery1),及對此連線 做些描述,這樣未來維護時較為容易辨認
④ 勾選「檔案開啟時自動更新」及「在全部重新整 理時重新整理此連線」
5除了使用方式需要修改之外,還得在 定義中增加參數的敘述。
① 點選「定義」
② 點選「參數」 按鈕,就會出現「參數」子視窗
③ 點選「以下儲存格作為參數值來源」,且輸入參 數值所在的儲存格。圖中的「=主表!$B$8」代表 此連線的參數以主表的B8儲存格中的值,也就是 股票第一列位置,也就是中華電(2412)代號的 位置
④ 之後按「確定」,新的具有參數的連線就全部完 成了
第二步:以連線擷取資料
6每1個連線均代表1檔股票的資訊擷取,在範例中的Excel試算表共有3檔股票,所以需要新增 3個具有參數的連線(stockQuery1, stockQuery2, stockQuery3),每1個連線的參數,分 別對應到「本表」的1個股票代號。當這3個連線都做好以後,就可以使用這3條連線將資料擷取到「股票價格查詢」的工作表中。
做法是新增一張新的工作表,重新命名為「股票價格查詢」,然後選定好所要置放的儲存格(如A1),然後依序完成下列步驟:
① 點選「現有連線」,就會出現「現有連線」之子視窗
② 選擇一個連線sotckQuery1
③ 按「開啟」按鈕, 就會出現「匯入資 料」子視窗
④ 輸入資料放置之位 置,然後按「確 定」鈕,就會開始 擷取資料
7stockQuery1所擷取到的資料置於以A1為起 始點上。股票價格查詢工作表上,分別放置 了stockQuery1、stockQuery2、stockQuery3的 3檔股票資料,這些資料每當檔案開啟時,就會重新擷取更新,若要即時更新也可以點選全部重新整理。圖為stockQuery1的連線所擷取到的資訊,雖然當中有很多資訊,不過投資者只關心成交金額,所以「主表」只用到成交的欄位。
第三步:更新本益比、殖利率及股價淨值比
股票的表格中除股價之外,也會顯示每檔股票最新的本益比、殖利率及股價淨值比,這些資料是證交所所提供的當日盤後交易資訊。不過擷取到資料是包含所有上市的股票,並非個別股票。所以若要查詢個股,就必須以股票代號為參數,用VLOOKUP函數查表得知。
檢視連線內容 拆解Excel做法
8因為篇幅關係,本篇不一一介紹每一連線是如何 製作出來的,因為原理都一樣,讀者可以自行檢 視每一個連線的內容,方法如下:
① 點選「連線」
② 點選所要檢視的連線
③ 點選「內容」按鈕,會出現連線內容子視窗
④ 點選「定義」的資料夾,連線字串就會顯示該連線的網址