怪老子ETF線上課程

輕鬆檢視資產配置

日期:2013-11-06

想要投資賺錢,就必須定時的檢視手中所持有的資產配置。然而投資市場的資料都是動態的,檢視前必須即時的更新才具有意義。所以投資者經常面臨一個困擾,就是每當檢視自己的投資組合時,面對分散在不同網站的資料,都得費上一番的功夫,才能蒐集到所需要的資料,然後加以計算整合,才能成為有用的資訊。

其實並不用那麼麻煩,只要善用微軟Excel的強大網頁擷取功能,輕而易舉的就可以自動幫投資者蒐集到不同來源的訊息。再透過Excel的計算功能,這些資料就可以被有效的整合,幫助投資者做好投資決策。

資產管理範例

為了介紹Excel這強大的功能,本篇提供一個Excel試算表,讀者可以這試算表為範本,再修改成適合自己的。範本的主要功能是管理投資者所持有的資產,包括了股票、基金以及定存。這份試算表的特色是動態管理,在檔案開啟時,Excel試算表就會自動更新成最即時的資料,然後算出總報酬率及各類資產的淨值及比例。

當然,製作一份好用的試算表並不簡單,不過一旦完成後,將來要檢視自己所投資的資產時,只要開啟試算表,然後什麼都不用做,資料就會自動更新,使用者只要檢視最後的結果即可。點選下列Excel圖示可以下載檔案:

Excel檔案下載

列出需要管理的資訊

試算表製作之前,首先得知道投資者需要何種資訊。一般投資者通常會有一部份資金投入台灣股市,另外一些資金投入國內外的基金,有部分是股票型基金、也有一些是債券型基金。除此之外,大部分的人也會包括銀行定存。

每當要檢驗投資績效時,就得先知道目前所持有的股票價格。除此之外,還得透過本益比、殖利率以及股價淨值比這些分析,才可以知道目前股價是不是過高或過低。當然,股票操作不會那麼單純只要這些資料就夠,不過這些資訊至少可以做第一層的過濾,讓投資者思考是否有需要進一步的動作。

有了這些資料之後,就可以輕易算出目前股票的投資績效。除了股票之外,許多投資者也持有國內及境外基金,目前的淨值是多少,以及目前各項匯率是多少,才有辦法了解目前基金的績效如何。

試算表架構

了解投資者所需要的資訊之後,試算表的架構就清楚了。整份試算表分為五個工作表,除了主表之外,其中的四個工作表根據所定義的連線,分別到各網路擷取資料,這些工作表名稱為:匯率查詢、基金淨值、股票價格查詢及證交所資料。而主表根據各個工作表所取得的資料,整理出一張投資者所關心的報表。最上方的表格列出了總資產以及個別類別的比例,接著是黑色的股票表格、藍色的基金表格及以橘色的定存表格。

範本上的股票表格目前只列出三檔股票,若使用者的股票在三檔以內,只需改變股票名稱及代號即可。若是三檔以上,則每一檔新的股票都必須新增一列,每一列也都得新增對應的連線。每當有股票買賣時,使用者也必須手動更新所持有股數及平均成本價。當持有股票配息時,也得更新配息金額,這樣所計算出來的期末價值及報酬率才會準確。

目前的基金範例只有兩檔基金,使用者可以參考這兩檔,自行新增或刪除基金的資料,每一列都得有一個對應連線才行,這樣才能動態更新即時資訊。

Excel強大的網路擷取功能

這試算表可以做到資訊即時更新,主要是充分應用Excel的網路擷取功能,讓Excel在檔案開啟時,自行到指定的網站中抓取最新的資料。雖然有些複雜,不過只要按照下列所述步驟,就可做一個具有這樣功能的試算表。

第一步:找出網址

想要應用Excel的網路擷取功能,首先要知道所要擷取網頁的網址。這部分可以先使用瀏覽器找到所要的網頁,然後複製網頁上方的網址即可。例如我們想要知道台積電的股價,通常會先到奇摩首頁,然後點選[股市]之後,再於[股票代號/名稱]欄位中輸入台積電代號2330,就會顯示出台積電最新之交易情形(如下圖)。

這網頁的網址就顯示於上方:http://tw.stock.yahoo.com/q/q?s=2330。只要用這網址就可以做成一個Excel連線,Excel的工作表可以根據這連線,就可以直接擷取到這網頁所顯示的資料。

網址的最右方是台積電代號2330,只要將2330改成其他的股票代號,就可以查詢到其他代號的股價訊息。例如大立光的代號為3008,那麼輸入http://tw.stock.yahoo.com/q/q?s=3008這網址,Excel就會抓取到大立光的網頁。

第二步:新增一個具有參數的連線

雖然利用上述網址,Excel就可以擷取到指定網頁的資料,可是這樣還不夠方便,因為每一檔股票都需要一份不同的網址。然而更方便的方式就是只用一個網址,而將股票代號當成一個參數,而此參數就置於儲存格上。那麼,只要改變儲存格的值,指定的網址就會變更,那就實在是太方便了。只要將上述的網址修改如下:

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就會自動抓取到該代號的股價了。

要做一個具有參數的連線需要費點功夫才行,首先要做一個副檔名為iqy的查詢檔,然後在以這查詢檔新增一個連線。製作查詢檔的步驟如下,首先開啟一張新的工作表,然後:

  1. 點選[資料]索引標籤
  2. 點選[從Web],就會出現一個子視窗
  3. 輸入奇摩股市股價之網址(台積電)
  4. 按[到(G)]按鈕,下方就會出現台積電股價的網頁
  5. 將凱基客戶專區及股票價格區之黃色右箭頭勾選成綠色打勾符號
  6. 點選儲存圖示,將此連線儲存成.iqy的檔案。檔案名稱預設值為q?s=2330.iqy,請用手動修改成較容易識別的名稱(如stockQuery.iqy),儲存於任一目錄。

接著用微軟的「記事本」開啟stockQuery.iqy的檔案,這是一個單純的文字檔,切勿使用微軟的Word編輯。 開啟檔案後,將第三列的網址s=2330修改成s=[“code”],如圖下紅框內所示:

這是一個Excel的查詢檔,敘述到哪一個網址抓資料,當中的網址使用了一個code的參數。查詢檔做好之後,接著就是使用這查詢檔新增一個連線。步驟如下:

  1. 於[資料]索引標籤,點選[連線],就會出現[活頁簿連線小視窗]
  2. 點選[新增]按鈕,會出現[現有連線]小視窗。
  3. 然後點選[瀏覽更多]按鈕,會出現[選取資料來源]的小視窗
  4. 找到放置stockQuery.iqy的目錄之後,點選取該檔案
  5. 點選[開啟]按鈕,這時候[活頁簿連線]上就會新增一個名稱為stockQuery的連線

新增完新的連線之後,接著要對此連線的內容做些修正,例如更改連線名稱、什麼時候要重新抓取此連線資料,以及連線的參數到哪裡取得等。步驟如下:

  1. 選取新增的連線(stockQuery)
  2. 點選內容按鈕,就會出現[連線內容]的子視窗
  3. 更改連線名稱(如stockQuery1),及對此連線做些描述,這樣未來維護時較為容易辨認
  4. 勾選[檔案開啟時自動更新]及[在全部重新整理時重新整理此連線]

除了使用方式需要修改之外,還得在定義中增加參數的敘述。

  1. 點選[定義]
  2. 點選[參數]按鈕,就會出現[參數]子視窗
  3. 點選[以下儲存格做為參數值來源],且輸入參數值所在的儲存格。圖中的“=主表!$B$8”代表此連線的參數以主表的B8儲存格中的值,也就是股票第一列位置,也就是中華電(2412)代號的位置。
  4. 之後按[確定],這新的具有參數的連線就全部完成了

第三步:以連線擷取資料

每一個連線均代表一檔股票的資訊擷取,在範例中的Excel試算表共有三檔股票,所以需要新增三個具有參數的連線(stockQuery1, stockQuery2, stockQuery3),每一個連線的參數分別對應到一個股票代號。當這三個連線都做好以後,就可以使用這三條連線將資料擷取到「股票價格查詢」的工作表中。

做法是新增一張新的工作表,重新命名為[股票價格查詢],然後選定好所要置放的儲存格(如A1),然後依序完成下列步驟:

  1. 點選[現有連線],就會出現[現有連線]之子視窗
  2. 選擇一個連線sotckQuery1
  3. 按[開啟]按鈕,就會出現[匯入資料]子視窗
  4. 輸入資料放置之位置,然後按[確定]鈕,就會開始擷取資料

stockQuery1所擷取到的資料置於以A1為起始點上,如下圖所示。股票價格查詢工作表上,分別放置了stockQuery1, stockQuery2, stockQuery3三檔股票的資料,這些資料每當檔案開啟時,就會重新擷取更新,若要及時更新也可以點選全部重新整理。下圖是stockQuery1的連線所擷取到的資訊,雖然當中有很多資訊,不過投資者只關心成交金額,所以主表只用到成交的欄位。

本益比、殖利率及股價淨值比

股票的表格中,股價之外也會顯示每檔股票最新的本益比、殖利率及股價淨值比。這些資料是從證交所網頁上抓取的。證交所會提供當日盤後交易的資訊,當中包括本益比、殖利率及股價淨值比,其網址為:

http://www.twse.com.tw/ch/trading/exchange/BWIBBU/BWIBBU_d.php

[本益比查詢]的連線主要是抓取上述網址的資料,每當試算表開啟時,「證交所資料」的工作表就會以這一個連線擷取最新的資料。不過所擷取到資料是包含所有上市的股票,並非個別股票。所以若要查詢個股,就必須以股票代號為參數,用VLOOKUP函數查表得知。若要檢視[本益比查詢]連線,請參閱檢視連線內容

基金的淨值及匯率

基金的淨值及匯率每日都會變動,所以也需要上網去查詢才能得到即時資訊,範例中用了富坦全球債及安本環球世界基金兩檔基金。基金淨值可以到funddj的網站中找到,所以因應這兩檔基金,也新增了安本及富坦兩個連線。[基金淨值]的工作表上的資料就是以這兩個連線所抓取的。除了基金淨值之外,匯率也是每日變動,所以每次都需要更新。[匯率查詢]工作表上就是以匯率查詢連線所抓取到的最新匯率。

若要檢視[匯率查詢]、[安本]、[富坦]連線,請參閱檢視連線內容

檢視連線內容

因為篇幅關係,本篇不一一介紹每一連線是如何製作出來的,因為原理都一樣,讀者可以自行檢視每一個連線的內容,方法如下:

  1. 點選[連線]
  2. 點選所要檢視的連線
  3. 點選[內容]按鈕,會出現連線內容子視窗
  4. 點選[定義]的資料夾,連線字串就會顯示該連線的網址

檢視連線內容

限制及延伸應用

雖然Excel的網頁擷取功能非常強大,不過也並非全能。某些網頁的資訊是用程式產生的,對於這樣的網頁Excel也是沒有辦法抓取,只好另外尋找其他網頁來代替。

學會了Excel的網頁擷取功能之後,舉凡任何網頁上可以找得到的資訊,都可以直接用Excel自動抓取,然後運用Excel的計算功能,將這些資料加以整合,應用可以說是非常廣泛。能夠將這項功能發揮到什麼程度,就等著讀者去開發了。




■ 本網站內容儘可能精確完整,但不保證無誤。若做為投資依據,風險請自行斟酌 ,本網站不負賠償之責任。
■ 網站所有資料均為版權所有,非經書面允許請勿轉載或使用。