tivo168_的投資理財_Excel_應用教學

以勞保基金為例，先到勞保局的網站下載「勞保基金用運」的公告資料，網址如下所示。首先依據下載的「勞保基金運用(101年10月以後資料)」及「勞保基金運用(101年10月以前資料)」，整理如《表一》，其中最後一列的平均收益率為表列各年度的收益率的平均數(此處的平均數指的是算數平均數)。 
《平均收益率》
數學公式 
=(7.78%+6.80%+6.46%+5.69%+7.07%+0.92%3.37%+1.15%+5.86%+3.12%+3.79%+7.91%+5.78%-16.53%+18.21%+3.96%-2.97%+6.25%) / 18 = 4.15% 
Excel 計算公式
在儲存格 [C21] 輸入公式 = AVERAGE(C3:C20) 即可求得算數平均數 
《表一》勞保基金自84年起年度收益率及平均收益率
《簡單五步驟，計算勞保基金近20年的年化報酬率》
STEP1 在D欄補上【期末淨值】及在E欄補上【期間(年)】 
STEP2 在第3列處新增一列，在儲存格[B3]補上【84年度(84.6.30)】，同時在儲存格[D3]補上數字【1】，表示期初淨值為1。 
STEP3 因記帳年度的問題，在89年度的起訖為1年6個月，所以就是1.5年，儲存格[E4:E21] 請填入1，並將儲存格[E9]改為1.5，在儲存格[E22] 輸入公式 =SUM(E4:E21) 
STEP4 在儲存格 [D4] 輸入公式 =D3*(1+C4)^E4 ，利用填滿控點的方式，將公式由儲存格[D4]複製到儲存格[D21]。 
STEP5 最後在儲存格[D22]處輸入公式 = (D21/D3) ^ (1/E22) –1 完成年化報酬率的計算。 
《表二》勞保基金年化報酬率計算 
勞保基金近20年的年化報酬率為3.85%，真的是相當不理想，相信大家看見二件比較值得注意的事，一是平均報報酬率大於年化報酬率。二是如果每年的投資報酬率是3.85%，那麼你的資產要18.5年才會翻一倍。
《七二法則》
從七二法則來試算一下，72/3.85(報酬率)=18.7(年)。 
網路上某投資高手，公布的歷年報酬率數字如《表三》，來幫他算算年化報酬率，計算公式與勞保基金相同，這裡就省略不再說明，計算結果年化報酬率22.2%，真是投資高手，10年來資產翻了7倍。 
《表三》某投資高手歷年來年度投資報酬率
 最後想一想年化報酬率有何用途？假設有A, B, C, D 四檔基金，分別有3到5年的年投資報酬率如下表，如果是長期投資你應該要選擇哪一支基金呢？ 依平均報酬率來看A基金7.4%、B基金3.5%、C基金6.2%及D基金8.3%，你應該會說當然是D基金，他的平均報酬率高達8.3%。我們先計算一下四週基金的年化報酬率，結果是A基金7.1%、B基金3.1%、C基金5.8%及D基金5.2%，結果居然不一樣，選A基金應該是比較正確的決策。算數平均數(平均報酬率)易受極端值得影響，幾何平均數(年化報酬率)就比較不會。
《表四》不同期間不同報酬率之比較

如何取得香港恆生指數的 PER(本益比、市盈率、PE Ratio)及DIV(Dividend Yield、殖利率)？
 
STEP 1  前往 恆生指數服務公司 (網址 http://www.hsi.com.hk/HSI-Net/HSI-Net)

    你知道如何做出可自訂起迄日期的個股歷史股價的EXCEL檔案嗎？ 如果你上過我的EXCEL課程，對下載個股歷史股價的問題可能有幾種解決方法。
最簡單的是使用「tivo168增益集」的股價功能，從證交所及櫃買中心一個月一個月抓，需要花一些時間，對沒耐心的人可能就不是個好辦法。
會用Excel VBA的人，或許選擇從 Yahoo Finance 下載 CSV檔，包還開盤、最高、最低、收盤四價及成交量的資料，只是成交量(金額數字有點怪)，且部分公司，資料會有問題，似乎也不是個好辦法。

網友FAQ整理 
[FAQ001] 請問一下，要算k值裡的年度平均股價，可以到哪裡擷取資料？
[FAQ002] 近四季的ROE我該如何得到呢? 是將每一季的ROE相加嗎?
[FAQ003] 請問一下，要算k值裡的每股淨值，可以到哪裡擷取資料？
[FAQ004] 請問要如何同時Show出Excel 裡的每一個儲存格之公式？
[FAQ005] 請問什麼時候才需要真正的分析合併報表？
[FAQ006] 請問權益法投資收益的股息回收率如何計算？
[FAQ007] 請問簽證會計師查核覆蓋率如何計算？
[FAQ008] 如果要算季的應收帳款週轉率，要怎麼算？收現日數也是用365天當分子嗎？
[FAQ009] 計算用應收帳款週轉率時分母使用應收帳款+應收票據，為何不是只用應收帳款？
[FAQ010] 能舉個例子說明如何從財報找到計算應收帳款週轉率所需之「應收款項」？
[FAQ011] 請問定存股台灣50(代號0050)到底有沒有零股買賣？
[FAQ012] 在模擬試算台灣50投資報酬率，沒考慮到配息，請問有什麼好辦法嗎？
[FAQ013] 請問台灣50指數之報酬指數的歷史資料那裡有？
[FAQ014] 台灣50的財務報告書可以在那個網站找到？
[FAQ015] 台灣50持股明細那裡找的到？
[FAQ016] 請問如果想知道董監酬勞、現金紅利、股票紅利的話又該從那邊找會比較快？
[FAQ017] 如何在櫃台買賣中心查到各上櫃股票的各年度股價均值？
[FAQ018] 請問股價跌破淨值就是便宜嗎？
[FAQ019] 請問哪個網站有常用經濟統計用語解釋呢？
[FAQ020] 請問一下，在公開資訊觀測站的那裡有完整的財務報告書？
[FAQ021] 請問Excel有內建統計分析函數或工具嗎？其實想問的是有計算相關係數的功能嗎？
[FAQ022] 請問Excel增益集中的分析工具，提供那些統計分析功能？
[FAQ023] 請問美國ECRI每週領先指標(WLI, Weekly Leading Index)準不準？
[FAQ024] S&P500與ECRI領先指標之相關係數計算實例
[FAQ025] 請問那裡可以免費一次下載台灣加權指數歷史資料？
[FAQ026] 那可以順便問一下美國三大指數歷史資料那裡可以下載嗎？
[FAQ027] 那再請問一下有台灣上市個股的歷史資料嗎？
[FAQ028] 請問一下台灣全體上市公司稅前淨利年增率的資料，那個網站有提供？
[FAQ029] 請問台灣50成份股足以代表全體上市公司嗎？
[FAQ030] 請問 Kn-Band 或Price-Band如何繪製？
[FAQ031] 請問投資藏寶圖一書中EXCEL實作第1招STEP03為何要刪除紅框處的公式？
[FAQ032] 請問投資藏寶圖一書中EXCEL實作第10招中財報ISQT(累計損益季表)是否為多餘的？
[FAQ033] 請問在Excel2003同一個圖表如何同時繪製 P-Band 及 PB-Band？
[FAQ034] 請問圖表中座標軸使用對數刻度的時機？
[FAQ035] 請問一下有免費的台灣上櫃個股的歷史成交資料嗎？
[FAQ036] 請問一下EXCEL 2007如何匯入IQY檔案？
[FAQ037] 請問一下如何繪製台灣加權指數報酬率區間圖？
[FAQ038] 請問一下如何繪製S&P500指數與殖利率對照圖？
[FAQ039] 請問什麼是年複合成長率(CAGR, Compound Annual Growth Rate) ？
[FAQ040] 請問EXCEL雙圖合一的圖表怎麼做出來的？
[FAQ041] 請問公開資訊觀測站改版了，彙總報表的IQY檔不能用了怎麼辦？
[FAQ042] 如何由櫃買中心網站抓取上櫃公司年度股價？
[FAQ043] 如何比較兩種(以上)貨幣對美元的升貶值幅度？
[FAQ044] 請問台灣全體上市公司平均股價淨值比資料哪裡有？
[FAQ045] 如何自行計算與繪製美元指數（US Dollar Index, USDX）？
[FAQ046] 請問一下FAQ038中的S&P500指數如何自動更新？
[FAQ047] 請問一下以將營收及合併營收的查詢整合在相同的Excel中嗎？
[FAQ048] 請問如何查詢上市上櫃公司稅額扣抵率(可扣抵稅額比率)？
[FAQ049] 請問哪裡可以下載 CRB 指數歷史收盤資料？
[FAQ050] 當懷疑投資藏寶圖12招中的數字有問題時，如何確認正確數字？
[FAQ051] 永豐金證券提供之上市櫃收盤價網頁無法使用了，該怎麼辦呢？
[FAQ052] 請問一下恐慌指數就是那個VIX指數的歷史資料哪裡找的到？
[FAQ053] 請問期貨市場三大法人交易情形統計資料在哪裡有提供？
[FAQ054] 請問上市櫃公司財報公布得期限是？
[FAQ055] 請問一下上櫃公司券商買賣日報表查詢入口在哪裡呢？
[FAQ056] 當資料來源網址變更或網頁內容變動之Web查詢的解決技巧。
[FAQ057] 0050 台灣50 ETF 過去所有的配息資料怎麼找？
[FAQ058] 如何計算年化報酬率(年複合報酬率)？
[FAQ059] 如何查詢公司的「自結損益公告-月申報」？
[FAQ060] 如何使用WEB查詢的進階技巧，取得個股的歷史股價？ 
[FAQ061] 如何取得香港恆生指數的 PER(本益比、市盈率、PE Ratio)及DIV(Dividend Yield、殖利率)歷史資料？

[FAQ059] 如何查詢公司的「自結損益公告-月申報」？
STEP 1 首先前往公開資訊觀測站  http://mops.twse.com.tw/mops/web/index
STEP 2 營運概況 → 自結損益公告 → 自結損益公告-月申報

Q: 請問圖表中座標軸使用對數刻度的時機？
A: 在座標軸使用對數刻度，會使相同的座標軸距離變化代表相同比例的變化。以台灣加權指數日收盤折線圖為例，指數由100點漲到1,000點是10倍(也就是1000％)；指數由1,000點漲到10,000點也是10倍。在等距離座標軸上並無法看出來兩者漲幅一樣是10倍，為了解決圖表失真的問題，我們可以在縱座標軸(Y軸)採用對數刻度，讓一樣的座標軸(Y軸)距離，代表相同程度的百分比變化。
《Y軸使用一般刻度(等距刻度)》

STEP 1  前往元大寶來基金理財網  http://www.yuantafunds.com 

自2013年起上市櫃公司因IFRSs會計準則規範，每月公告的營收必須是合併營收，不再公告非合併營收。2012年(含)前，合併營收是採自願公告。MoneyDJ 營收網頁採非合併營收與營並營收網址調換，照成投資藏寶圖12招Excel 檔案查到錯誤營收的情況，解決辦法如下：

STEP1 對正確(新)的網址製作正確(新)的IQY檔
                  合併營收網址   http://www.emega.com.tw/z/zc/zch/zch_2330.djhtm
             (步驟省略)
             (提示：記得關閉日期識別，避免民國年月被解析為錯誤的西元年月)
                 
 
STEP2  將靜態IQY檔修改為動態參數IQY。
       
STEP3 開啟計畫修改的EXCEL檔，對WEB查詢範圍(例如Revenue工作表)按滑鼠右鍵，選「資料範圍屬性」(Excel2003)或「資料範圍內容」(Excel2007)，解除「儲存查詢定義」勾選。
STEP4 匯入新作的動態參數IQY，並設定參數與工作表連動。

【FAQ-055】請問一下上櫃公司券商買賣日報表查詢入口在哪裡呢？
【Answer 】不就是在【盤後資訊】嗎？就在【首頁】 > 【上櫃股票交易資訊】 > 【盤後資訊】 > 【券商買賣證券日報表查詢系統】。可是我看不到啊？仔細瞧一瞧下圖，還真的沒看到，怎麼會這樣呢？

可是貨真價實，東西就在這裡啊！這種問題 tivo168 也答不出來，麻煩打電話問一下櫃買中心吧。
上圖網址 http://www.gretai.org.tw/ch/stock/aftertrading/broker_trading/brokerBS.php

  



以上圖片來源【Excel入門及投資藏寶圖12招】課程投影片。

---