打造投資工具-樂活五線譜

樂活五線譜是以三年六個月及十年兩個特定期間的迴歸直線來觀察股價的中、長期變動趨勢，首先必須先取得投資標的過去十年的每日指數或股價資料，接著利用Excel進行統計分析計算迴歸直線(趨勢線)的相關數據。假設大致符合常態分布，並依據常態分布的平均數(趨勢線)加/減一個、二個標準差得到四組數據，也就是四條線，迴歸直線(趨勢線)加上四條平行線，共五條平行的趨勢線，就是樂活五線譜。

以常態分布來看，在加/減一個標準差的這兩條平行線中，大約有68%的歷史收盤指數/股價落在這個區間，也就是包含近七成收盤價的概念；在加/減二個標準差的這兩條平行線中，大約有95%的歷史收盤指數/股價落在這個區間，也就是將近包含九成五的收盤價的概念，符合統計要求的概念。當然沒有哪組數據是可以用常態分布實際描述，但符合大致描述應該就有不錯的效果，如果指數或股價偏離到三個標準差之外，通常意味發生較大的轉折變動，會改以大趨勢來輔助使用。

 
圖案來源    http://zh./wiki/標準差

 

最後利用Excel圖表功能將這五條平行線以視覺化來呈現，視覺化的圖表方便我們對這個投資標的過去三年六個月及十年的收盤指數進行觀察、分析、歸納與決策。

 註：樂活五線譜是參考香港曾淵滄博士的「曾氏通道」所發展出來的工具，曾氏通道的是分析恆生指數二十年長期資料，將指數取對數(Logarithm)轉換，然後應用線性迴歸找到一個直線趨勢，找到分別以包含75%及95%歷史數據的四條平行線，分別代表95%樂觀線，75%樂觀線，趨勢線，75%悲觀線，95%悲觀線，即所謂曾氏通道。

Yahoo Finance提供我們免費下載服務，包括全世界主要股市的指數及個股的歷史股價的交易資料；以下載台灣加權指數為例，台灣加權指數在Yahoo Finance的代碼為^TWII。

一、由Yahoo Finance下載過去十年的每日指數或股價資料

(以Google Chrome瀏覽器為例)

STEP 1 首先打開電腦的瀏覽器，然後於網址列，輸入網址 http://finance.yahoo.com/

  

STEP 2 接著在網頁頁面左邊的文字輸入框 Quote Lookup 的位置輸入【^TWII】，然後按 【Go】。
註：Yahoo Finance 的大部分指數代碼是以【^】為起始字元，當你輸入【^】，就會看到網頁自動提示的部分代碼清單。

  

 

STEP 3 確認代碼及名稱 TSEC weighted Index (^TWII)，接著以滑鼠左鍵點選頁面左邊側欄選單中的【Historical Prices】，檢視Yahoo Finance有無提供歷史資料下載，部分常用的指數可能未提供檔案下載服務，例如Yahoo Finance就沒有提供【^DJI】歷史資料下載服務，也沒有提供【USDTWD=x】匯率的歷史資料下載服務。

  

STEP 4 我們需要十年的指數資料，我們指定期間為2005/1/1到2014/12/31，一般來說會先決定觀察日期也就是2014/12/31，由資料期間十年計算得到開始日期為2005/1/1，樂活五線譜是統計每日成交收盤價，所以資料頻率選擇【Daily】，接著以滑鼠左鍵點選【Get Prices】。等Yahoo Finance頁面更新傳回指定期間資料後，將頁面右側捲軸往下拉動，以便檢視頁面最底部的下載連結，檢視是否有提供該投資標的的歷史股價或指數。

  

STEP 5 在頁面的最後，我們看到【Download to Spreadsheet】的超連接，以滑鼠左鍵單擊，直接下載歷史指數或股價。本章第二節VBA程式自動化，也是由這個連結取得歷史指數或股價的資料。

註：我們須注意的是，像匯率【USDTWD=x】 或是道瓊工業指數【^DJI】等，Yahoo Finance就不提供歷史資料的下載服務，除非另外找到有提供資料下載的其他網站，因此就無法進行樂活五線譜的圖表繪製。

  

STEP 6 由Chrome瀏覽器的左下角，我們可以看到TSEC weighted Index (^TWII) 指定期間的歷史指數的資料已經下載完成，預設檔案名稱為 table.csv。

 

 二、有歷史資料後，利用Excel計算並繪製樂活五線譜

(以Excel 2010為示範的版本)

STEP 1 執行 Microsoft Excel，打開台灣加權指數(^TWII)歷史資料檔案table.csv，先另存新檔，檔案命名為【TWII-樂活五線譜.xlsx】；以滑鼠左鍵單擊【檔案】 →【另存新檔】→選擇檔案儲存位置，輸入【TWII-樂活五線譜.xlsx】。

  

STEP 2 先將資料依據日期由舊到新排序，方法很簡單，以滑鼠左鍵點一下A1儲存格，接著點選【資料】功能索引標籤，然後點選【從A到Z排序】，立刻會看到資料已經依據Date欄，由舊到新排序完成。

  

STEP 3 五線譜的迴歸直線，我們可以採用y = a + bx 的直線方程式來計算，其中x 為資料數量，由1起算，y為收盤指數或收盤股價。在H1儲存格輸入【Item(x)】，H2儲存格輸入【1】，H3儲存格輸入【2】，接著以滑鼠選取H2及H3兩儲存格，然後將滑鼠移到作用儲存格右下角的填滿控點，待滑鼠游標變為細十字線後，雙擊滑鼠左鍵，完成資料數量填滿。

  

STEP 4 在I1儲存格輸入【Price(y)】，在I2儲存格輸入公式【=E2】，如果要取對數則可以輸入【=LN(E2)】或是【=LOG(E2,2)】，然後將滑鼠移到作用儲存格I2右下角的填滿控點，待滑鼠游標變為細十字線後，雙擊滑鼠左鍵，完成公式向下填滿。

  

註：若資料期間超過十年且高低差距較大時，可以考慮取對數，在Excel上可以使用自然對數(LN)或是以2為底的對數(LOG)。

STEP 5 使用Excel內建函數INTERCEPT及SLOPE，來計算y = a + bx 的截距(a)與斜率(b)。輸入公式前，先拉動工作表右側捲軸到最底，確認最後一筆資料是第2471列，在J1儲存格輸入【a】，K1儲存格輸入【b】，在J2儲存格輸入公式【=INTERCEPT(I2:I2471, H2:H2471)】，在J3儲存格輸入公式【=J$2】；在K2儲存格輸入公式【=SLOPE(I2:I2471, H2:H2471)】，在K3儲存格輸入公式【=K$2】，接著以滑鼠選取J3及K3兩儲存格，然後將滑鼠移到作用儲存格右下角的填滿控點，待滑鼠游標變為細十字線後，雙擊滑鼠左鍵，完成截距及斜率的公式向下填滿。

  

 

STEP 6 緊接著計算迴歸直線的y值；在L1儲存格輸入【Price(TL)】，在L2儲存格輸入公式【=J2+K2*H2】，接著以滑鼠選取L2儲存格，然後將滑鼠移到作用儲存格右下角的填滿控點，待滑鼠游標變為細十字線後，雙擊滑鼠左鍵，完成迴歸直線的公式向下填滿。

  

STEP 7 計算收盤價與迴歸直線的差距，然後計算所有差距的標準差；在M1儲存格輸入【y-TL】，在M2儲存格輸入公式【=I2-L2】，接著以滑鼠選取M1儲存格，然後將滑鼠移到作用儲存格右下角的填滿控點，待滑鼠游標變為細十字線後，雙擊滑鼠左鍵，完成公式向下填滿。

  

STEP 8 計算收盤價與迴歸直線的差距的標準差；在N1儲存格輸入【SD】，在N2儲存格輸入公式【=STDEVP(M2:M2471)】，接著在N3儲存格輸入公式【=N$2】，以滑鼠選取N3儲存格，然後將滑鼠移到作用儲存格右下角的填滿控點，待滑鼠游標變為細十字線後，雙擊滑鼠左鍵，完成標準差的公式向下填滿。

  

STEP 9 我們採用統計上常態分佈概念，取加減一個標準差(兩平行線內累積機率約68.3%)及加減兩個標準差(兩平行線內累積機率約95.5%)，所以就有TL-2SD, TL-SD, TL+SD, TL+2SD 四條線，在加上迴歸直線共五條線，完成五線譜的計算。
在O1儲存格輸入【TL-2SD】，在O2儲存格輸入公式【=$L2-2*$N2】
在P1儲存格輸入【TL-SD】，在P2儲存格輸入公式【=$L2-$N2】
在Q1儲存格輸入【TL+SD】，在Q2儲存格輸入公式【=$L2+$N2】
在R1儲存格輸入【TL+2SD】，在R2儲存格輸入公式【=$L2+2*$N2】
以滑鼠選取O2:R2儲存格，然後將滑鼠移到作用儲存格右下角的填滿控點，待滑鼠游標變為細十字線後，雙擊滑鼠左鍵，完成四條平行線的公式向下填滿。

  
註：由於我們採用加一個及兩個減標準差，所以就不需要使用NORMINV函數來計算四條趨勢線的相對位置。如果你打算使用包含95%及75%機率的四條線，就必須改用NORMINV函數來計算，公式值須改為如下
在O1儲存格輸入【95%悲觀線】，在O2儲存格輸入【=NORMINV(0.025,L2,N2)】
在P1儲存格輸入【75%悲觀線】，在P2儲存格輸入【=NORMINV(0.125,L2,N2)】
在Q1儲存格輸入【75%樂觀線】，在Q2儲存格輸入【=NORMINV(0.875,L2,N2)】
在R1儲存格輸入【95%樂觀線】，在R2儲存格輸入【=NORMINV(0.975,L2,N2)】
，其餘步驟相同。

 

STEP 10 完成所有計算後，開始繪製樂活五線譜的圖表；首先以滑鼠選取A1:A2471，接著左手按住鍵盤CTRL鍵，右手再以滑鼠選取I1:I2471；依序完成不連續區域的L1:L2471、O1:O2471、P1:P2471、Q1:Q2471、R1:R2471的資料選取。

  

 

STEP 11 接著滑鼠左鍵點選【插入】功能索引標籤後，選擇【折線圖】，就完成未經圖表格式設定的樂活五線譜，至於圖表的格式美化設定就請自行完成吧。

  

 

STEP 12 由於Excel圖表的格式美化設定，步驟簡單但瑣碎，為了不浪費篇幅就不做詳細說明，僅提供美化完成的圖表供參考。

  

 

 利用Excel VBA，打造自動化的樂活五線譜

一、撰寫VBA程式由自動下載YahooFinance的歷史資料

STEP 01 執行 Microsoft Excel，開啟我們試做的【TWII-樂活五線譜.xlsx】，開啟完成後另存新檔，先將【存檔類型】改為【Excel啟用巨集的活頁簿】，然後再按【儲存】。

  

STEP 02 必須先打開功能區的【開發人員】的索引標籤，才能方便的完成後續工作。在【檔案】 索引標籤上，選擇【選項】 按鈕。

  

STEP 03 選擇【自訂功能區】，在主索引標籤清單中，選取【開發人員】核取方塊，案【確定】，完成設定。

  

在Excel功能區，已經顯示【開發人員】索引標籤。

   

 

STEP 04 新增【樂活五線譜】、【PRICE_DATA】、【PRICE】三張工作表(worksheet)，並在【樂活五線譜】工作表的B2儲存格輸入【Yahoo Finance代碼】，在C2儲存格輸入【^TWII】，並依據下圖做簡單的格式設定。接著佈置【Get Prices】的ActiveX按鈕；選擇【開發人員】功能後，選擇【插入】後，選用ActiveX 命令按鈕。

  

 

STEP 05目前滑鼠游標呈現細十字線，拖拉佈置一個命令按鈕，預設名稱是CommandButton1。對命令按鈕CommandButton1按滑鼠右鍵，選【內容】，修改Caption屬性，將CommandButton1改為Get Prices。

  

 

STEP 06 緊接著以滑鼠左鍵雙擊【Get Prices】按鈕，Excel會由工作表區進入VBA編輯器，在程式碼視窗直接輸入【Call Get_YahooFinance_HistoricalPrice([C2].Value)】 (參考下圖)。

    

 

STEP 07 接著對專案清單中的ThisWorkbook按滑鼠右鍵，選【插入】，再選【模組】。

  

STEP 08 以滑鼠左鍵雙擊模組中的【Module1】模組，接著可以在右邊的程式碼視窗撰寫下載Yahoo Finance 歷史資料的程式碼。

  

STEP 09 因為無法從頭說明，所以只能請讀者照著輸入下圖中的所有程式碼，對於初學者，程式碼可能不易了解，但並不影響到最後的成果。輸入過程請務必仔細小心不能有任何錯誤。輸入完成後請重複仔細檢查，確認無誤請按VBA編輯器右上角的X，以關閉VBA編輯器回到Excel工作表。

  

 

STEP 10 請注意，此時仍處於【設計模式】狀態，以滑鼠左鍵單擊【設計模式】的圖示來關閉設計模式。

  

 

STEP 11 我們已經完成，按一下【Get Prices】按鈕，VBA程式就依據輸入在C2儲存格的Yahoo Finance代碼，自動下載歷史資料的功能。快來按一下【Get Prices】按鈕試看看！下載後的資料，將暫時存放在PRICE_DATA工作表。

  

 二、撰寫VBA程式，讓Excel將五線譜五條趨勢線的計算自動化

本章節主要的目的是將前一節的人工計算部分改用VBA程式來幫我們自動完成。

STEP 01 依據前面的詳細說明，現在讀者們一定有辦法依照下圖完成基本欄位名稱及命令按鈕的設計，在C6儲存格輸入數值【3.5】，在E6儲存格輸入公式【=TODAY()】，在G6儲存格輸入公式【=IFERROR(E6-ROUND(C6*365, 0), "")】。
  

STEP 02 在設計模式下，分別依序以滑鼠左鍵單擊【3.5】、【10】、【Today】及【樂活五線譜】四個按鈕，依據下圖的說明輸入VBA程式碼。
  

STEP 03 接下來，對專案清單中的ThisWorkbook或是工作表，按滑鼠右鍵，選【插入】，再選【模組】，會看到一個新增的【Module2】模組，對此模組雙擊滑鼠左鍵，讓右手邊的視窗切換到【Module2】模組專屬的程式碼視窗。在程式碼視窗輸入Sub LOHA_Calculation，按下【Enter】鍵，VBA編輯器會自動補完如下圖所示的VBA程式碼。
  

STEP 04 首先要依據【樂活五線譜】工作表，將在開始日期與觀察日期間的資料由【PRICE_DATA】工作表複製到【PRICE】工作表，必須注意的複製之前需先清除前一次查詢所留下的舊資料。 
  

STEP 05 接下來要完成，第一節 [二、有歷史資料後，利用Excel計算並繪製樂活五線譜] STEP 3到STEP 9人工計算五線譜改為自動計算。先完成資料數量計數的程式碼如圖所示。 (註：請參考table工作表的上的欄位及公式)
  

STEP 06 接著將收盤指數或收盤股價close欄引用到Price(y)欄，這裡要注意的是公式中採用相對參照位置，C[-4]表示參照到由I欄往左算第4欄的位置。
註1：相對參照的中括號的負(-)代表往左及往上，正(+)代表往右及往下。
註2：如果你想要將收盤指數或收盤股價取對數，這裡也列出2個做法，可以LOG或LN
  

《欄位參考》


  

STEP 07 開始計算迴歸直線的截距(a)與斜率(b)，如果你仔細比對程式碼會發現這裡只是利用VBA將前一節的公式填入到儲存格而已，一點都不困難。
  

STEP 08 完成截距(a)與斜率(b)的計算後，利用y=a+bx直線方程式，我們可以計算出趨勢線(迴歸直線)的y值，接著計算收盤指數或收盤股價與趨勢線(迴歸直線)的差值。
  

 

STEP 09 計算收盤指數或收盤股價與趨勢線(迴歸直線)之差的標準差。
  

STEP 10 計算趨勢線(迴歸直線)+/-1個標準差及+/-2個標準差，共四條平行線，再加上趨勢線(迴歸直線)就完成樂活五線譜。
註：如果你想要計算曾氏通道的75%悲/樂觀線及95%悲/樂觀線，也用註解的方式將程式碼列出供讀者參考。
  

STEP 11 大功告成，趕快按一下命令按鈕【樂活五線譜】，確認一下VBA程式是否幫我們完成相關計算。

 三、撰寫VBA程式，讓Excel自動繪製樂活五線譜

STEP 01 執行Excel 2010，滑鼠點選【檔案】，然後選擇【開啟舊檔】，接著選擇【TWII-樂活五線譜.xlsm】，開啟前一節完成的檔案。接著，按【ALT】+【F11】組合鍵，進入Visual Basic for Application 編輯器。或是點選【開發人員】再選擇【Visual Basic編輯器】。

   

STEP 02 以滑鼠左鍵雙擊【Module2】，接著拉動右側的捲軸到最後，End Sub程式碼的下一行，輸入【Sub LOHA_Chart】，按【Enter】鍵，讓VBA編輯器自動完成Sub - End Sub 對應。

  

STEP 03 先選定【樂活五線譜】工作表的【A12:I28】 儲存格範圍作為圖表放置位置，可以先設定一底色來識別

   

STEP 04 回到VBA編輯器，先輸入下圖的VBA程式碼，此部分程式碼的主要功能是在指定位置，依據指定大小先產生一個內容空白的圖表區。

   

STEP 05 接著指定圖表類型為折線圖(xlLine)，並指定樂活五線譜圖表的資料來源為【PRICE!$A:$A,PRICE!$I:$I,PRICE!$L:$L,PRICE!$O:$R】，其餘部分則為圖表顯示的相關設定。

   

STEP 06 為了讓圖表內的五線譜不擠在一起，所以運用簡單的計算來調整數值座標軸的最大值與最小值。

    

STEP 07 接著設定收盤指數或股價之數列名稱，並設定到儲存格C6為作用儲存格。

   

STEP 08 接著在 End Sub 之後，再輸入Function SetDecimal 來處理進位的問題，請依照下圖完整輸入即可。

  

STEP 09 最後以滑鼠左鍵雙擊專案清單中【工作表1(樂活五線譜)】，並將 Call LOHA_Chart 前的﹝'﹞單引號刪除，將註解改為可執行之指令行，關閉VBA編輯器，記得按一下【儲存檔案】。

   

STEP 10 現在進行測試，輸入代碼【0050.TW】，按下【Get Prices】按鈕等數字下載完成後，接著按一下【樂活五線譜】，進行計算與繪圖，最後成果如下圖。