2013年4月3日 星期三

利用試算表輕鬆完成再平衡─簡要說明

(源自綠角財經筆記的留言1 留言2)(2013/4/23更新試算表v1.11, 5/16 v1.12, 2015-1-15 v2)

使用ETF作為資產配置的工具時,可利用本試算表整合Firstrade和TD Ameritrade的投資部位,並按照設定的配重自動算出再平衡需買入或賣出的部位。同時也提供彈性的設計,方便選擇某些標的不賣出或不買入

有兩種版本:
  • Google Docs 專用版 :有自動更新報價功能,但不支援Excel
    請用「建立副本」進行編輯,不要請求共用權限
  • Excel版:Excel格式,需下載後使用,沒有自動更新報價功能
簡易使用說明
  1. 載入Firstrade資料
    進入網站 "我的帳戶>持有證券",用滑鼠選取表格(由右下到左上拖曳比較容易選取)貼入空白試算表,確認表格內容完整後,再貼進本試算表的Firstrade工作表。貼入時,標題列必須在第一列,代號必須在第一欄。資料內容至少要有 "代號"、"股數"、"價格" 三個欄位 (專用版不需價格欄位),但除代號外,其他欄位順序無所謂。如果沒有Firstrade帳戶,請把Firstrade工作表的範例資料刪除。
  2. 載入TD Ameritrade資料
    進入網站 My Accounts>Positions,點擊表格右上方的齒輪圖像,選Export即可輸出為Excel檔案,打開確認無誤後,再貼進本試算表的TD Ameritrade工作表。資料內容至少要有Symbol、Qty、Last三個欄位 (專用版不需Last欄位),但除Symbol外,其他欄位順序無所謂 。如果沒有TD Ameritrade帳戶,請把TD Ameritrade工作表的範例資料刪除。
  3. 進入"再平衡"工作表,在淺綠色欄位填入本次投入金額代號和對應的配重,即可完成試算。請不要改變其他表格內容,以免造成錯誤
  4. 如果評估尚不需再平衡,請進入 "無再平衡" 工作表,不必重新輸入資料,即可看到沒有再平衡,將資金直接按配重投入的計算結果。
  5. 如果某些標的本次不加入交易(例如如選擇不賣出的部分再平衡,或是為了節省交易費用本次暫不購入),請在對應的 "自訂交易金額" 欄位填入0,即可調整計算結果。如果不需調整,請確認欄位清空。
  6. 如果即時報價和試算表的價格相差太遠,或是想要自訂目標價格,可將對應的 "自訂價格" 欄位填入所需的價格。如果不需調整,請確認欄位清空。
  7. Excel版因沒有自動更新報價功能,是以帳戶資料上的價格為準,故要交易前再載入資料,可以更貼近實際市場價格。Google Docs 專用版可自動更新市場價格,即使決定過幾天再交易,只要中間沒有股數變動,都不必重新載入資料。
如果對於試算表使用仍有疑問,請參考詳細使用說明

45 則留言 :

  1. 請問該如何下載Google的版本到我的Google Drive裡面?謝謝

    回覆刪除
    回覆
    1. 請見詳細使用說明
      http://ribtw.blogspot.tw/2013/04/blog-post_859.html

      刪除
  2. Rib大你好 感謝你提供豐富的知識跟大家交流 學習到很多
    想問一下這個試算表跟綠角大在資產配置課程給的試算表差異在哪裡?
    兩個是試算表可以搭配使用嗎? 不知道要選擇哪個比較合適

    回覆刪除
    回覆
    1. 抱歉,綠角大的課我沒有去上,所以不知道他是怎樣設計的,無法具體答覆。

      總之,如果各有不同功能當然可搭配著用,如果有同樣功能就選喜歡的用。反正有檔案在手,試用看看就知道了。有興趣深入學習試算表的話,自己搞一個更合用的試算表其實也沒那麼難。

      如果對我的試算表有問題,或學習試算表有疑難雜症,都歡迎在此交流。綠角大的試算表如果有問題,上過課的朋友可以直接寫信問他。

      刪除
  3. Rib大 感謝你的答覆 我會再研究看看^^

    想問一下因為我有買幾支ETF做資產配置 有股票跟債券的
    想問Rib大一下,請問你是怎麼管理手上這些ETF的,透過什麼工具
    去計算他們的報酬率,以及有什麼管理的訣竅?

    回覆刪除
    回覆
    1. 最基本的管理當然就是定期投入和再平衡,這也是這個試算表的主要功能。

      基本的報酬率,券商一般都有提供Realized/Unrealized Gain/Loss功能,如果要精確計算IRR的話,就要會用試算表才行。未來我打算要分享一個可以直接利用下載的交易記錄計算XIRR的試算表,但時程未定。

      如有多個券商戶頭,可以利用Morningstar、Yahoo finance等網站的投資組合功能,TD Ameritrade的X-ray,輸入各家券商的投資組合整合追蹤。

      我因為歷經了幾種不同投資方式變化,多年累積投資狀況還蠻複雜的,一般的工具大概都無法滿足需求,所以是用Microsoft access搭配試算表來整合。學會這兩種工具的話,基本上可以完全按自己希望的方式的管理各種資產,但這需要深入學習,特別是Access這種資料庫軟體,無論軟體再怎麼簡化,學習門檻還是很高。

      刪除
  4. 感謝 Rib大的回覆
    我也覺得 IRR 因為考量時間因素比較貼近真實的報酬率
    我會去你所說的網站試看看
    自重開始學習理財投資後 真的覺得應該要好好學習EXCEL
    因為網路上的管理工具 都不是完全符合需求
    版大EXCEL功力深 都可以客製化自己的需求 真厲害!!

    回覆刪除
    回覆
    1. 加油吧,試算表的話,要按自己的需求設計其實也沒有那麼難。

      如果已經有基礎,透過網路文章學習一些財務公式和函數就行了。從頭學的話,可能還是需要去找一本內容合用、解說能懂得書比較容易上手,自己設計表格時如果有疑難雜症再來交流就行了。

      刪除
  5. 有了Rib大,就不需要Portfolio Planner了!

    回覆刪除
    回覆
    1. 謝謝您的支持,不過這樣說太過獎了。如果只在TD Ameritrade投資,Portfolio Planner還是很方便的。

      刪除
  6. EXCEL如果各位會用<>功能,即可達到自動抓取ETF/股票現值的目的(如果不會寫VBA,必須為每支ETF分別建立工作表),操作方式詳見下列網址
    http://isvincent.pixnet.net/blog/post/33685265-excel-%E5%BE%9E%E7%B6%B2%E9%A0%81%E4%B8%8A%E5%8F%96%E5%BE%97%E5%A4%96%E9%83%A8%E8%B3%87%E6%96%99

    回覆刪除
    回覆
    1. 抱歉,"<>"裡頭的字是"資料/從WEB匯入",不知甚麼因素消失不見了

      刪除
    2. 可能因為blogger的留言支援某些html tag,所以不能用那個符號

      刪除
  7. 我嘗試使用EXCEL VBA的功能改寫了可自動更新的版本,在>工作表的右邊我加了一個VBA按鈕可自動更新報價,如果有新的投資部位,只要在>工作表的ETF代號欄位加上代號即可,VBA會自動的判別抓取
    修改後的EXCEL檔:https://drive.google.com/file/d/0B4JFf9NcZBXxS3F5U1pVVGVVVEE/view?usp=sharing
    PS:EXCEL執行VBA時可能會出現警告,必須去將之設定改為

    回覆刪除
    回覆
    1. 1.抱歉,可能敘述中我加了不該有的符號,PS中的內容變得不完整,重新敘述
      PS:EXCEL執行VBA時可能會出現警告,EXCEL2003可到 EXCEL選項內/信任中心/巨集設定,將之改為"啟用所有巨集"
      2.該檔案如果還有需要修改之處歡迎提出討論,若測試沒有問題版主可放在自己的網頁空間供人分享(怕自己會不小心刪掉)

      刪除
    2. 感謝Lin Albert的分享。最後面沒寫完的部分應該是要容許巨集執行吧。前一個留言<>漏的字我猜應該是「web查詢」。

      有興趣的話可以參考「http://www.vertex42.com/ExcelTemplates/excel-stock-quotes.html」或許用MSN這個功能可能更方便些,速度比較快,又可以直接取得Delayed Quote。

      如果不要求完全自動化,用這個網頁提供的樣版的話,也可以一次解決報價問題,不用為每支ETF分別建立「web查詢」。

      就如Lin大和vertex42展示的,Excel自有其強大功能可用。Excel的web查詢用起來比較麻煩,但有時可以抓到google sheet抓不到的東西,可惜這比較適合自學自用,除非用VBA,不然很難弄出直接可用的通用版出來,不過VBA就超過我能力範圍了。

      刪除
    3. 不是很建議啟用所有巨集說,畢竟這曾經是病毒平台之一。
      我覺得開始檔案時再同意開啟巨集就行了。

      刪除
    4. Rib大分享的這個網頁有提到 Stock Quotes in Excel via Google Sheets
      http://www.vertex42.com/ExcelTemplates/excel-stock-quotes.html
      就是google sheets有個"發布"的功能,可以變成網頁,然後excel就可以匯入這個網頁。這樣變的很有彈性,google sheet上面的東西都可以匯入excel,我現在可以再excel中用google finance的資料了。

      刪除
    5. 也是沒錯啦,但我覺得──這樣我就直接用google sheet了說...

      Google sheet的網頁出版還有個問題是更新頻率,感覺延遲蠻久的...不過我還沒試過用Excel的網頁查詢連google sheet,不知更新頻率是否有差...不過今天好像休市,也沒辦法測吧...

      刪除
    6. googlefinance函式的更新頻率是每2-3分鐘,MSN quoteslookup也差不多,Google sheet的網頁出版更新約7分鐘 (比實際感覺短一些,大概等更新時都比較沒耐性)

      刪除
    7. 分享一個東西,
      Spreadsheet ImportHtml 可以設定更新頻率。
      http://igoogledrive.blogspot.in/2012/09/google-spreadsheet-importhtml-auto-update-or-refresh.html

      刪除
    8. S.J大真是卯起來用Google sheet了啊。

      順帶一提,新版的Google sheet的Now()可以經由試算表設定設為自動更新,舊版Google sheet的Now()不會自動更新,所以還要搭GoogleFinance或 GOOGLECLOCK 這種會自動更新的函數達到自動更新的效果。

      刪除
  8. 感謝前輩分享,我找到了,用這個字串"closeyest",謝謝

    回覆刪除
    回覆
    1. 怎麼覺得漏掉什麼?
      closeyest是用在Googlefinance的參數,用來取得某代碼股票前一天的收盤價。

      刪除
  9. 用在TD試算表中的DAY GAIN裡面的公式,對於再平衡使用沒有影響啦,只是想讓每欄都可以作用

    回覆刪除
  10. Rib大,我要新增標的,是在再平衡那個工作表鍵入代號跟比例嗎?我試好幾次一直沒成功,可以給您看一下?感恩!

    回覆刪除
    回覆
    1. 選擇D10:Q14,然後按Control-D就行了。
      如果還是不行,請再留言,我再請求授權編輯。

      刪除
    2. 版大,可以了!之前是EMAIL留的訊息是選取A到P欄位
      看到整欄出來很有快感,感覺真是佩服阿!非常感謝您~

      刪除
    3. 既然A-C欄已經填數字了當然不能再一起向下填滿了,至於Q藍應該是筆誤吧,不過這不影響其他欄位計算

      其實這算是試算表的基本操作,「詳細使用說明」那篇也有寫。

      刪除
    4. 感謝版大︿︿真的是沒有看到詳細說明,真不好意思,這樣的巧思與仔細竟然眼殘>_<
      終於我要問比較有點腦袋的問題了,但我一直不知道該去哪留言較好,本想找ETF討論最新一則貼文,想想還是在這請教了
      在綠角版大那看到關於R大在VPL有四支加入小型價值股的留言,計算比例這件事,我果然不會算。懇請解惑計算這數字,感恩阿!
      如果VGK占總額的14%,VPL占總額的9%,現在想轉換成VEA(依照R留言比例是歐洲佔64%,遠東36%)比例,在R大的再平衡表裡VEA該寫數字是?再次感謝

      刪除
    5. 再補充請教R大。若改成R大建議的VEA,原先的VGK+VPL是否需要賣出?應該是要對嗎?這樣才能維持資產配置的比例?感恩

      刪除
    6. 1. 應該只有加入小型股,並沒有偏重價值股。
      2. 根據晨星的資料,目前VEA的歐亞比例約62:38,和您的14:9其實不會相去太遠,其實直接只用VEA取代對投資組合影響其實也不會太大。不過,不知您是否清楚,當您只單買VEA時,等於讓歐亞間的比例隨市值自由浮動。雖然可以簡化投資內容、降低交易成本,但必須放棄這部分的再平衡。所以分兩支還是用一隻,還是要看個人的偏好。
      3.如果您要套用這個試算表,那麼賣出後全部買入VEA會比較方便。其比例就是兩者相加的23%。但是,如果有自己設計試算表的能力,那麼也可以不要賣出,留著不動就好,新投入資金再買入VEA即可。這樣可以省下交易費用,但需要自己設計公式,動態計算資金持續投入下VEA逐漸提高的配重,維持VEA+VGK+VPL=23%

      刪除
  11. Rib前輩您好,謝謝花時間心血分享這麼多實用的文章,讓後輩一目瞭然。想請教您有關再平衡的問題,若去年我的股債配置比重是:股70%(僅一支ETF)、債30%(僅一支ETF),今年變化成股50%、債20%,那這樣的狀況該如何執行再平衡 ?

    回覆刪除
    回覆
    1. 不太確定您的 "今年變化成股50%、債20%" 是甚麼意思,是說歷經市場波動後股債比由7:3變成5:2 嗎? 如果您想要的再平衡是恢復7:3,那麼5:2大約是股71.43% 債28.57%,其實變化很有限,不特別再平衡也無所謂,一定要的話,就是投入的資金多買債券ETF以提升其配重,必要時賣出一些股票ETF部位改買債券ETF。 至於資金分配和買賣部位的細節計算,本文試算表就是用來簡化這部分計算過程的。

      刪除
  12. Hello Rib,您好!

    目前已經開好美國券商的帳戶,但對於資金規劃部分,想要聽聽您的意見。

    假設自己2-3年後會有大筆資金的需求(留學),除了國外學校可能提供的獎學金外,學費的缺口,一定還是會需向國內銀行申請貸款,在這種情況下,您會建議仍先把「可投資的資產」去進行ETF配置、並且長期投資嗎?因為如果ETF投資換算下來的年化報酬%>貸款利率%的話,沒有必要把錢放在銀行戶頭等待付學費的那天?
    例如:學費假設NTD300萬,目前手上有NTD100萬可動用資產,剩下NTD200萬打算貸款,您會建議未來直接貸款NTD300萬(NTD100萬拿去資產配置)呢?還是直接貸款NTD200萬呢?謝謝!

    回覆刪除
    回覆
    1. Hello Rib, 我想要補充的是,我上面舉例的100萬是已經扣除"緊急預備金"剩餘自己可投資的閒錢,且100萬不會單次/短期就全部投入~

      刪除
    2. 沒有簡單的最有利答案,只能提醒幾個或許您也已經知道的事實給您參考。

      如果您很介意這2-3年的100W放銀行是浪費賺錢機會,也請您想想如果明年再來個金融海嘯會是如何。

      即使不只看這2-3年而是長期投資,也無法保證 "年化報酬%>貸款利率%"

      如果您了解投資費用的殺傷力,那借款利率應該是當成投資成本考量,而不是把它當成非獲利目標,這樣心理判斷或許會比較接近事實

      刪除
    3. 一般似乎是說,有五年以上不需動用的錢才來投資。像Rib提到的,投資不能只想著賺,也要想到虧了怎麼辦。

      真的按耐不住想要做點什麼的話,我有想到留學學費應該是外幣計價,也許可以分批買匯,處理一點匯率波動的風險。(話說台幣最近對美元還真強阿... )

      刪除
    4. Rib & J 感謝您們的回覆。

      1. 其實我本來的意思就是說希望這個100W是做個>5年以上的投資,而不放
      2-3年後就提領出來,所以我才會想問說,是否這個100W做資產配置,而屆時的資金缺口則採貸款的方式?
      2. 謝謝J的建議,的確也可以趁機分批買匯,感謝!

      3. 如果是針對這種5-6年後需要用到的錢(一種很尷尬的時間),兩位通常會建議怎麼配置?放定存很浪費,但放ETF資產配置可能又有點嫌短了?有好建議嗎?

      4. Rib您這句話「借款利率應該是當成投資成本考量,而不是把它當成非獲利目標」我看了好幾次還是不太懂,可以再勞煩您多解釋一些嗎?

      謝謝~:)

      刪除
    5. 浪費倒底是甚麼? 放定存不會浪費的,不過要接受這一點不是容易的事情。

      一般進場的時候容易覺得自己了解市場風險,覺得願意為了獲利承受風險。但反過來說,為了降低風險而必須減少獲利,這說的其實是同一件事的兩個面向,但有時就很難被接受。當然對於定存族來講,情況就又相反。所以投資不能只看數據,如何拿捏自己的心理狀態,更是大問題。

      有些人可以從客觀歷史數據感受到風險,有些人則要幾次教訓才死心,當然也有人被嚇得放棄投資。有人運氣很好,自信爆棚。如果實際看到這樣"令人羨慕"的例子,自己要死心就又更難。可人家運氣好,自己又未...,我自己花了十幾年時間這樣反反復復,直到被綠角洗禮之後才真正死心。但即使如此,對那些運氣好 (其實大多只是自我感覺良好) 的朋友,我也沒綠角那樣的能力去說服甚麼,何況即使是表達能力強如綠角,也常遇到些莫名的挑釁。牽扯到行為的東西就不是理智討論可以簡單解決的,自己的心理得要自己慢慢去釐清。

      說到投資成本,假設貸款利率2%好了,當成投資目標看,年化報酬要超過2%好像不難,就覺得借錢(負債)投資也不錯。但換個角度,如果您的券商每年收您投資金額2%的資產管理費,你能接受嗎? 假設你知道過幾年這筆費用會取消,那您會等降價還是急著注資? 同樣的費用,觀點不同,對決策的影響卻差很大。

      刪除
    6. 謝謝Rib大精采的提點,Rib大您在綠角的洗禮前,自己過去的投資主要是在哪塊呀?當初是還沒踏入美國券商投資ETF 囉?

      刪除
    7. 是啊,就用本地提供的昂貴的海外基金和國內投信基金。不過,工具雖然很重要,但更重要的是綠角引介的正確投資知識。

      刪除
  13. Dear Rib 兄 , 突然發現好一陣子沒有你繼續的指數化投資的網路分享,覺得會想念另外也希望一切都ok !

    回覆刪除