import pandas as pd #資料整理與分析
import numpy as np #基本計算
import scipy.optimize as solver #規劃求解
import statistics as stat #統計數字，回傳投資組合超額報酬率 
import matplotlib.pyplot as plt #畫圖
from functools import reduce #做矩陣中數值的相加
import statsmodels.api as reg #回歸
 
 
# In[2]:
 
# 修改檔名
Monthly_TEJ_return = pd.read_excel(r'C:\\Users\\cpes9\\Desktop\\hw_2.xlsx', sep = ',', sheet_name = '工作表1', index_col = '年月')
# 修改無風險利率Title
Monthly_TEJ_return['MB64_M'] = Monthly_TEJ_return['MB64_Y'] / 12
#print(Monthly_TEJ_return.head(4))
 
 
# In[3]:
 
#Excess return table
# 修改需要的列跟行
Monthly_excess_return = Monthly_TEJ_return.iloc[0:60,0:-2] #只取個股和Y9999[列，行]
for column in Monthly_excess_return:
    Monthly_excess_return[column] = (Monthly_excess_return[column] - Monthly_TEJ_return['MB64_M']) / 100 #本來是顯示百分比數字
#print(Monthly_excess_return)
 
 
# In[4]:
 
#Markowitz approach to construct monthly covariance matrix
# 修改需要的列跟行
Monthly_excess_return = Monthly_excess_return.iloc[:,0:-1]
Monthly_cov_matrix = Monthly_excess_return.cov()
#print(Monthly_cov_matrix)
 
 
# In[5]:
 
#convert monthly matrix to annually matrix
Annual_cov_matrix = Monthly_cov_matrix * 12
print(Annual_cov_matrix)

aW1wb3J0IHBhbmRhcyBhcyBwZCAj6LOH5paZ5pW055CG6IiH5YiG5p6QCmltcG9ydCBudW1weSBhcyBucCAj5Z+65pys6KiI566XCmltcG9ydCBzY2lweS5vcHRpbWl6ZSBhcyBzb2x2ZXIgI+imj+WKg+axguinowppbXBvcnQgc3RhdGlzdGljcyBhcyBzdGF0ICPntbHoqIjmlbjlrZfvvIzlm57lgrPmipXos4fntYTlkIjotoXpoY3loLHphaznjocgCmltcG9ydCBtYXRwbG90bGliLnB5cGxvdCBhcyBwbHQgI+eVq+Wclgpmcm9tIGZ1bmN0b29scyBpbXBvcnQgcmVkdWNlICPlgZrnn6npmaPkuK3mlbjlgLznmoTnm7jliqAKaW1wb3J0IHN0YXRzbW9kZWxzLmFwaSBhcyByZWcgI+WbnuatuAoKCiMgSW5bMl06CgojIOS/ruaUueaqlOWQjQpNb250aGx5X1RFSl9yZXR1cm4gPSBwZC5yZWFkX2V4Y2VsKHInQzpcXFVzZXJzXFxjcGVzOVxcRGVza3RvcFxcaHdfMi54bHN4Jywgc2VwID0gJywnLCBzaGVldF9uYW1lID0gJ+W3peS9nOihqDEnLCBpbmRleF9jb2wgPSAn5bm05pyIJykKIyDkv67mlLnnhKHpoqjpmqrliKnnjodUaXRsZQpNb250aGx5X1RFSl9yZXR1cm5bJ01CNjRfTSddID0gTW9udGhseV9URUpfcmV0dXJuWydNQjY0X1knXSAvIDEyCiNwcmludChNb250aGx5X1RFSl9yZXR1cm4uaGVhZCg0KSkKCgojIEluWzNdOgoKI0V4Y2VzcyByZXR1cm4gdGFibGUKIyDkv67mlLnpnIDopoHnmoTliJfot5/ooYwKTW9udGhseV9leGNlc3NfcmV0dXJuID0gTW9udGhseV9URUpfcmV0dXJuLmlsb2NbMDo2MCwwOi0yXSAj5Y+q5Y+W5YCL6IKh5ZKMWTk5OTlb5YiX77yM6KGMXQpmb3IgY29sdW1uIGluIE1vbnRobHlfZXhjZXNzX3JldHVybjoKICAgIE1vbnRobHlfZXhjZXNzX3JldHVybltjb2x1bW5dID0gKE1vbnRobHlfZXhjZXNzX3JldHVybltjb2x1bW5dIC0gTW9udGhseV9URUpfcmV0dXJuWydNQjY0X00nXSkgLyAxMDAgI+acrOS+huaYr+mhr+ekuueZvuWIhuavlOaVuOWtlwojcHJpbnQoTW9udGhseV9leGNlc3NfcmV0dXJuKQoKCiMgSW5bNF06CgojTWFya293aXR6IGFwcHJvYWNoIHRvIGNvbnN0cnVjdCBtb250aGx5IGNvdmFyaWFuY2UgbWF0cml4CiMg5L+u5pS56ZyA6KaB55qE5YiX6Lef6KGMCk1vbnRobHlfZXhjZXNzX3JldHVybiA9IE1vbnRobHlfZXhjZXNzX3JldHVybi5pbG9jWzosMDotMV0KTW9udGhseV9jb3ZfbWF0cml4ID0gTW9udGhseV9leGNlc3NfcmV0dXJuLmNvdigpCiNwcmludChNb250aGx5X2Nvdl9tYXRyaXgpCgoKIyBJbls1XToKCiNjb252ZXJ0IG1vbnRobHkgbWF0cml4IHRvIGFubnVhbGx5IG1hdHJpeApBbm51YWxfY292X21hdHJpeCA9IE1vbnRobHlfY292X21hdHJpeCAqIDEyCnByaW50KEFubnVhbF9jb3ZfbWF0cml4KQ==