1、江西農(nóng)業(yè)大學(xué)經(jīng)濟(jì)管理學(xué)院實(shí)驗(yàn)報(bào)告股票投資組合實(shí)驗(yàn)報(bào)告第一步：從股票軟件同花順中導(dǎo)出4只股票2016年的相關(guān)指數(shù)數(shù)據(jù)。導(dǎo)出 數(shù)據(jù)為：吉艾科技（JAKJ）、上海家化（SHJH）、國(guó)星光電（GXGD）、通用航空 （TYHK）。導(dǎo)出結(jié)果如圖1所示：AaU時(shí)間AJKJSHJHGXGDTYHK2016-01-04,19. 9136. 6615. 212033 432016-01-05,二1937. 3514. 521989 4了2016-01-06,三20. 2837. 7814. 99沁9. 772016-01-07,四18. 634. 8813. 491842 922016-01-08,五19. 53

2、5. 7313. 571851 872016-01-11,17. 5533. 4212. 211707162016-01-12,二17. 3533. 3812. 191710. 452016-01-13,三15. 6232. 9411. 191618. 822016-01-14,四15. 7533. 4S11. 621705192016-01-15,五14 1832. 3210 851&66 98圖1數(shù)據(jù)導(dǎo)出結(jié)果第二步：計(jì)算各數(shù)據(jù)日收益率公式：f（B3-B2）/B2,以AJKJ為例，其余數(shù)據(jù)處理方法與其相同。取AJKJ 在2016年1月4日的收盤(pán)價(jià)為1月5日收益率基數(shù)，輸入上述公式，計(jì)算出1

3、月5日的收益率為- 4.57%,其他日期的收益率計(jì)算方法與此相同，最終計(jì)算出精選文本各股的日收益率如圖2所示:時(shí)間AJKJS町HGXGDTYHK2016-01-05,二-4. 57%1. 88%-4 54%-2.16%2016-01-06,三6 74%1. 15%3. 24%2. 53%2016-01-07,四-8. 28%-7. 68%-10. O19i-9. 65-71石A 口力館n 轉(zhuǎn)圖2收益率第三步：計(jì)算各股之間的年化收益率步驟一：采用Excel函數(shù)average （number 1, number2,）計(jì)算日平均收益率，計(jì)算過(guò)程如圖3所示：丄w-wz，四2016-12-23,五丄.v

4、o0. 09K-u0. 45%2016-12-26, 一0. 78X-0.11%2016-12-27,二2. 76M0. 41冷2016-12-20, Z-0. 80%0. 82%2016-12-29,四-0.30%2016-12-30,五0. 64X0. T處日平均收註率AVERAGE (B2:B243)-0.函數(shù)譽(yù)數(shù)-1.&inbex】B2:K43屁 -0.0457056馮5393297；(:1.：Hun 應(yīng) 2-0.O.e-0.0312473350.E返回基卷淞的耳朮平均銜；券可以罡澈値或包含前佰的名稱(chēng).敖組或引用Wvnbcr:nvnbtrb wbeQ . 是用于計(jì)苴平均値的1i+HSf

5、fi - 0.(0124鶴35伺工診勺的芯肋（祐丄.丄.0L%圖3日平均收益率計(jì)算過(guò)程步驟二：通過(guò)日平均收益率X242 （2016年共242個(gè)交易日）計(jì)算年化收益率。最終得岀各股年化收益率結(jié)果如圖4所示:2016-12-27,二2. 76%0. 41%1. 38%-0 06%2016-12-28,三-0. 80%0. 82%-0. 23%0.13%2016-12-29,四-1. 01%-0. 30軀0. 68%0. 19%2016-12-30,五0. 64%0. 74%0. 53%-0 28%日平均收益率0. 12-0. 11%-0 01%-0. 04%年化收益率30. 19%-26. 65%

6、-1. 91%-9 09%圖4年化收益率數(shù)據(jù)第四步：計(jì)算各股之間的年化收益率協(xié)方差步驟采用Excel函數(shù)VARP (number 1, number2,)計(jì)算日收益率之間的 協(xié)方差；樣本方差用varp ()函數(shù)，協(xié)方差用covar ()函數(shù)，結(jié)果如圖5所示:1AU日收益率協(xié)方差A(yù)JKJSHJHGXGDTYHKAJKJ0.001451809SHJH-6.2988E-050.000571GXGD0.0003982220.0002610.000908TYHK0.0002977260.0003150.0005070. 000534圖5日收益率方差計(jì)算結(jié)果步驟二：計(jì)算年化收益率協(xié)方差(2016年共242

7、個(gè)交易日)。最終計(jì)算出的 結(jié)果如圖6所示：年化收益率協(xié)方差A(yù)JKJS町HGXGDTYHKAJKJ0. 351337681SHJH-0.0152430870. 13818411GXGD0. 0963696610.063243540. 219820094TYHK0. 0720497730. 07614780. 1226494260.12914圖6年化收益率協(xié)方差第五步：計(jì)算各股權(quán)重。步驟一：采用Excel函數(shù)rand自動(dòng)生成各年化收益率對(duì)應(yīng)隨機(jī)數(shù)，確定各 股收益率權(quán)重系數(shù)，并通過(guò)SUM函數(shù)對(duì)隨機(jī)數(shù)進(jìn)行加總求和步驟二：通過(guò)已知各股年化收益率與系數(shù)總和的比值，確定各股權(quán)重，同步 驟一方式對(duì)權(quán)重W進(jìn)行加

8、總，結(jié)果為1。權(quán)重計(jì)算結(jié)果如圖7所示。年化收註率協(xié)萬(wàn)差A(yù)JKJSHJBGKGDTYHK5J0. 351337681-0. 0152430870. 096396010. 072049773SHJH-0. 0152430870.1381841130. 063243540. 076147802GXGD0.0963696610.063243540. 2198200940.122649426TYHE0.0720497730. 0T&1478020. 1226494260.129140351年優(yōu)收益率30.19%-26. 69X-1.91%-9. 09%隨機(jī)數(shù)0.6397677810. 52455528Q

9、0.4329289140. 63566&2722. 2329180. 28651&4370. 2349191&20.1938848020. 2846了95991圖7各股權(quán)重計(jì)算結(jié)果第六步：計(jì)算該組合下的方差。利用MMULT, TRANSPOSE兩個(gè)語(yǔ)法合并計(jì)算:(MMULT(MMULT(B8:E8,B2：E5),TRANSPOSE(B8: E8)=MMULT(MMULT(B8:E8, E2:E5), TRANSPOSE(E8:E8)輸入定義公式后，按住Ctrl+Shift+Enter輸出結(jié)果如圖8所示08 A l-HflULT (MMULT (E8:E8, B2:E5), TltOISFOSE

10、 (B0:8)ABCDEFG年化收益率協(xié)方差A(yù)JKJSHJHGXGDTYHKAJKJ0351337681-0. 0152430870.096369他0. 0T2049773SHJH-0. 0152430870.1381841130.063243540.0T6147802GXGD0.0963096610. 003243540.2198200940.122649426TYHX0. 0720497730. 0761478020.1226494260.129140351年比收就率30.19M-26.65H-1.91-9 09H0 0062237S20. 9674402840.0256569280. 4

11、65077155 1. 464398萬(wàn)墓權(quán)數(shù)u0. 0042500610.6606401990.0175204590. 8眇圖8方差計(jì)算結(jié)果第七步：計(jì)算證券組合下資產(chǎn)收益率的期望值。步驟一:利用 SlMPRODLCT(array 1, array2, array3,.)函數(shù)計(jì)算各股年 化收益率與其對(duì)應(yīng)權(quán)數(shù)的乘積，得到各股在資產(chǎn)組合下的期望收益率，輸出結(jié)果 為- 9. 04%,如圖9所示匸 二SUMPRODUCT(B8:E8, B6:E6)H8-A =SVKF5l0DVCT(BB:EB. E6:E6)BCDB；H1年化收益率切方址AJKJSHJHCXCDTYKK2AJK

12、J0.35133781-0.0152430 盯0 0963636610 0720497783SEJH-0. 0152430B70.1381841130. 063243540 0761478024CXGD0.0963696610. 063243540.2198200940.1226494265TYHXO O72Q497730. 0761478020.1226494260.129140351630. m-26. 6滋-1.9H-9. 09J6?隨機(jī)數(shù)0.0755605570. 5706960960. 995119340. 3008043171.94218方羞朗望收益率80.0389050160.

13、29384SCI040 5123722730.154879707112.S19J-9. 04%圖9期望收益率計(jì)算結(jié)果第八步：利用Excel的模擬運(yùn)算表功能隨機(jī)產(chǎn)生包含上述證券的1000個(gè)組 合的期望收益率和方差步驟一：選中F8單元格，輸入“1”，在菜單欄中選擇“編輯/填充/序列”， 將序列產(chǎn)生修改為列”，步長(zhǎng)值為1”，終止值為1000”，點(diǎn)擊“確定”， 如圖10. 11所示：0. 1343861270.4088479970. 986770289 1. 897602 方差 期望收益率0.0708189320.2154551140. 520009164111.62%-1 18%L30120.261

14、6739990.0570877141.244481方差期望收益率26430. 2102675150. 04587269619.82%-2.52%2345678910111213圖10模擬運(yùn)算表運(yùn)算過(guò)程3.00195方差期望收益率110. 59H-3. 38%213.345. 03%310.1191-4. &9%412. 201.89%510. 23%-11. 94%613.1091-2 97%711.15$-0. 09%812. 8291-5 53%911.18%0.19%1012.37-0. 83%1110. 39-3. 32%128.97%一& 13%139.39%一4 18%i Am圖1

15、1模擬運(yùn)算表運(yùn)算結(jié)果第九步：繪制數(shù)據(jù)散點(diǎn)圖選中G7至H1008單元格，選擇“插入/圖表，選中散點(diǎn)圖，點(diǎn)擊“下一步”,并將圖表名稱(chēng)修改為“資產(chǎn)組合”，圖表繪制如圖12所示隨機(jī)數(shù)(1.5371254740.784106930.6419285110. 700192 i?0.2016499130.2943727340.2409955120. 2629810415.005?-20咖2500%資產(chǎn)組合25.00%20.00%15.00%10.00%5.0C%0.00%25.00%10.00%-15.00%2. 66S65SI1方差期望收益率f19.95H1 1-4.61K211.2426甜39.48H-10. 29K411.19H0. T2H515.96H4. 67H11. 20H-6. 47K12. 6M-3.7111.08H-2. 9814. 55H6.