MỤC LỤC
CÂU 1: THU THẬP DATA......................................................................................................................................2
CÂU 2: TÍNH TÝ SUẤT SINH LỢI THEO THÁNG..........................................................................................3
CÂU 3: MA TRẬN HIỆP PHƯƠNG SAI..............................................................................................................5
CÂU 4: TÌM DANH MỤC PHƯƠNG SAI NHỎ NHẤT.....................................................................................6
CÂU 5: VẼ ĐỒ THỊ ĐƯỜNG TẬP HỢP CÁC CƠ HỘI ĐẦU TƯ VÀ ĐƯỜNG BIÊN HIỆU QUẢ............8
CÂU 6: VIẾT HÀM VBA ĐỂ TÍNH ĐỘ LỆCH CHUẨN CỔ PHIẾU...........................................................11
CÂU 7: VIẾT HÀM VBA ĐỂ TÍNH ĐỘ LỆCH CHUẨN DANH MỤC.........................................................12
NGUỒN.................................................................................................................................................................13


PHẠM THỊ KHÁNH LY_TCDL _CH K28_CHIỀU CN

CÂU 1: THU THẬP DATA
 Yêu cầu:
-

Giá đóng cửa hàng ngày.

-

Số lượng cổ phiếu: 15 cổ phiếu.

-

Số ngành: 5 ngành, mỗi ngành 3 cổ phiếu.

-

Khoảng thời gian: 60 tháng, tính đến cuối tháng 7/2019.

 Bài làm:
-

Lựa chọn 5 ngành chính đóng góp lớn trong nền kinh tế Việt Nam bao gồm: Bất động sản, Dệt
may, Dược, Ngân hàng, Xây dựng.

-

Cổ phiếu: Lựa chọn các công ty có vốn hóa lớn, đi đầu trong các ngành trên, cổ phiếu được giao
dịch sôi động và dẫn dắt thị trường. Vì thời gian yêu cầu là 60 tháng (5 năm) nên ngoài các điều
kiện trên còn phải cân nhắc chọn các cổ phiếu có thời gian niêm yết ít nhất là 5 năm, được niêm
yết trên sàn HOSE hoặc HNX.

-

Danh sách 15 công ty được chọn như sau:

STT
1
2
3
4
5
6
7
8
9
10
11
12

13
14
15
-

Ngành
Bất động sản
Dệt may
Dược
Ngân hàng
Xây dựng

Mã CP
DXG
NLG
SCR
GMC
TCM
TNG
DHG
DMC
TRA
BID
CTG
VCB
CTD
FCN
HBC

Tên công ty

Cty CP Tập đoàn Đất Xanh
Cty CP Đầu tư Nam Long
Cty CP Địa ốc SG Thương Tín- TTC land
Cty CP Sản xuất Thương mại May Sài Gòn
Cty CP Dệt may - Đầu tư - Thương mại Thành Công
Cty CP đầu tư và thương mại
Cty CP Dược Hậu Giang
Cty CP Xuất nhập khẩu Y tế Domesco
Công ty Cổ phần TRAPHACO
NH TMCP Đầu tư và Phát triển Việt Nam
NH TMCP Công Thương Việt Nam
NH TMCP Ngoại thương Việt Nam
CTCP Xây dựng Cotec (Coteccons)
Công ty cổ phần FECON
Cty CP Tập đoàn xây dựng Hòa Bình

Thời gian: Gía đóng cửa hàng ngày có điều chỉnh (cổ tức, phát hành cổ phiếu) từ ngày
31/07/2014-31/07/2019. Giá của ngày cuối tháng lấy bằng giá của ngày cuối cùng của tháng có
phát sinh giao dịch.
1


PHẠM THỊ KHÁNH LY_TCDL _CH K28_CHIỀU CN

-

Nguồn: Trang thông tin được nhiều người truy cập và sử dụng: />
-

Thứ tự dữ liệu: Số liệu sau khi tải được sắp xếp theo thứ tự thời gian giảm dần từ trên xuống

dưới như sau. Đơn vị tính là 1.000 VND.

-

Số liệu được trình bày tại sheet: 1.CÂU 1-DATA:

CÂU 2: TÍNH TÝ SUẤT SINH LỢI THEO THÁNG
 Yêu cầu:
-

Trình bày cách lọc ra giá cổ phiếu vào ngày giao dịch cuối cùng của mỗi tháng.

-

Tính tỷ suất sinh lợi theo tháng của từng cổ phiếu.

 Bài làm:
-

Bước 1: Tại sheet 1.Câu 1-DATA . Vì số liệu được thu thập theo ngày từ ô A5 đến ô P1251 và
sắp xếp theo thứ tự từ trên xuống dưới theo thời gian giảm dần, do đó ta có thể áp dụng công
thức sau để tìm ra ngày nào là ngày cuối cùng của mỗi tháng: = IF (MONTH (A5) = MONTH
(A4);"no";"Ngay cuoi thang"). Diễn giải: Nếu tháng tại A5 bằng với tháng của ô liền trước A4,
thì ghi nhận vào ô Q5 đó không phải là ngày cuối tháng:”no”, nếu tháng tại A5 không bằng với
tháng của ô liền trước A4 thì đó là ngày cuối tháng và ghi nhận “Ngay cuoi thang” tại ô Q5.
Tương tự coppy công thức xuống hết dữ liệu tại cột Q ta có:

2



PHẠM THỊ KHÁNH LY_TCDL _CH K28_CHIỀU CN

-

Bước 2: Tại sheet 2.CÂU 2-END MONTH, dùng filter advanced để lọc ra ngày cuối tháng.
 Vào Data -> Filter advanced
 List range: vùng lọc là vùng A4:Q1251 tại sheet 1.
 Criteria range: vùng điều kiện lọc dữ liệu theo yêu cầu là vùng A3:A4 tại sheet 2.
 Coppy to: kết quả dữ liệu trình bày từ ô A6 tại sheet 2.
 Bấm ok ta thu được 61 dữ liệu tháng như sau:

-

Bước 3: Tính TSSL theo tháng tại sheet 3.CÂU 2-RETURN

3


PHẠM THỊ KHÁNH LY_TCDL _CH K28_CHIỀU CN

 Sau khi có được ngày cuối tháng của mỗi cổ phiếu, tính tỷ suất sinh lợi theo tháng của cổ
phiếu có tính đến lãi kép, sử dụng hàm logarit (bằng cách lấy Ln giá đóng cửa của tháng này
chia cho giá đóng cửa của tháng liền trước).
 Ta thu được tỷ suất sinh lơi theo tháng của mỗi cổ phiếu trong vòng 60 tháng như sau:

CÂU 3: MA TRẬN HIỆP PHƯƠNG SAI
 Yêu cầu: Ước lượng ma trận hiệp phương sai tỷ suất sinh lợi của 15 cổ phiếu.
 Bài làm: có 2 cách.
-


Cách 1: Tính tỷ suất sinh lợi trung bình của từng cổ phiếu, sau đó tính tỷ suất sinh lợi vượt trội
và tính toán được ma trận phương sai- hiệp phương sai bằng công thức trình bày tại sheet
3.CÂU 2-RETURN

4


PHẠM THỊ KHÁNH LY_TCDL _CH K28_CHIỀU CN

-

Cách 2: Sử dụng VBA Function, ứng dụng hàm Covariance_S trong excel để tính toán ma trận
phương sai- hiệp phương sai tại sheet 4.CÂU 3-CÂU 4 như sau:
 Developer -> visual basic -> module 1:

 Sau đó, quét vùng variance-covariance matrix: nhập hàm =varcovar () với vùng là TSSL theo
tháng của từng cổ phiếu đã tính ở sheet 3.CÂU 2-RETURN B3:P62. Kết quả thu được như
sau:

CÂU 4: TÌM DANH MỤC PHƯƠNG SAI NHỎ NHẤT.
 Yêu cầu: Tìm danh mục của 15 cổ phiếu trên có phương sai nhỏ nhất, tính tỷ suất sinh lợi và độ
lệch chuẩn của danh mục này.

5


PHẠM THỊ KHÁNH LY_TCDL _CH K28_CHIỀU CN

-


Danh mục cổ phiếu có phương sai nhỏ nhất Global Minimum Variance Portfolio (GMVP).

-

Ma trận S là ma trận phương sai – hiệp phương sai đã tính.

-

Danh mục cần tìm là vector x = {x 1 , x2, x3,…… xn } n=15 cổ phiếu, có phương sai nhỏ nhất
trong tất cả các danh mục khả thi, danh mục này được xác định:

-

Công thức tính trung bình (mean) của danh mục và phương sai danh mục như sau:

 Bài làm: Tại sheet 4.CÂU 3-CÂU 4.
-

Bước 1: Sử dụng hàm VBA function để tính ma trận unit theo cột và theo dòng như sau:
module 3

6


PHẠM THỊ KHÁNH LY_TCDL _CH K28_CHIỀU CN

-

Bước 2: Tìm danh mục GMVP, tính phương sai, độ lệch chuẩn của danh mục đó theo công thức
trình bày trên như sau: (vùng B4:P18 là ma trận hiệp phương sai đã tính) ta thu được kết quả tại

vùng GMVP statics.

CÂU 5: VẼ ĐỒ THỊ ĐƯỜNG TẬP HỢP CÁC CƠ HỘI ĐẦU TƯ VÀ ĐƯỜNG BIÊN HIỆU
QUẢ.
 Yêu cầu: Vẽ đường tập hợp cơ hội đầu tư (opportunities set) và đường biên hiệu quả (efficient
frontier), cho phép bán khống.
7


PHẠM THỊ KHÁNH LY_TCDL _CH K28_CHIỀU CN

-

Đường biên hiệu quả là nửa trên của đường tập hợp các cơ hội đầu tư Opportunities set, tính từ
điểm có phương sai nhỏ nhất.

-

Để thiết lập danh mục tối ưu không có hạn chế bán khống, sử dụng công thức sau để tính tỷ
trọng các cổ phiếu trong danh mục:

 Bài làm: Tại sheet 5.CÂU 5-IOS
-

Bước 1: Cho c= 0,03%. Dùng công thức trình bày ở trên để tìm danh mục tối ưu, ma trận S là
ma trận hiệp phương sai đã tính B7:P21, kết quả như sau:

-

Bước 2: Để vẽ được đường tập hợp cơ hội đầu tư, ta cho c thay đổi để xác định tập hợp danh

mục tối ưu (optimal portfolio) bao gồm số lượng cặp TSSL và độ lệch chuẩn (portffolio mean
và portfolio sigma) đủ lớn. Sử dụng VBA với thuật toán như sau:
 Cho c thay đổi 100 giá trị, mỗi bước giá trị cách nhau 0,01. Bắt đầu từ -0,49 đến 0,5.
 Gía trị được trình bày ở vùng A47:R146 : Cột 1 là giá trị c thay đổi, cột 2 là sigma, cột thứ 3
là mean, các cột tiếp theo là tỷ trọng đầu tư vào 15 cổ phiếu được cho.
 VBA Module 4:

8


PHẠM THỊ KHÁNH LY_TCDL _CH K28_CHIỀU CN

+Ta thu được kết quả sau khi tiến hành chạy sub trên:

-

Bước 3: Tìm điểm có độ lệch chuẩn nhỏ nhất nằm trên đường tập hợp cơ hội đầu tư để vẽ đường
biên hiệu quả, ta sử dụng hàm solve với các ràng buộc như sau:
 set objective : mục tiêu là portfolio sigma.
 to: min


by changing variable cells: ô thay đổi là vùng C22:C36

 constraints: C37 =1.
-

Tại sheet 6.CÂU 5-FRONTIER, ta sử dụng hàm solver như sau:
9



PHẠM THỊ KHÁNH LY_TCDL _CH K28_CHIỀU CN

-

Kết quả thu được điểm có độ lệch chuẩn nhỏ nhất và trung bình đi kèm dưới đây (màu đỏ):

-

Bước 4: Với các dữ liệu thu được, ta có thể vẽ đường biên hiệu quả và đường tập hợp cơ hội
trên một biểu đồ với đường màu cam là đường biên hiệu quả (nửa trên của đường tập hợp cơ
hội đầu tư từ điểm có phương sai nhỏ nhất).

-

Biểu đồ Tại sheet 7.CÂU 5-CHART
10


PHẠM THỊ KHÁNH LY_TCDL _CH K28_CHIỀU CN

CÂU 6: VIẾT HÀM VBA ĐỂ TÍNH ĐỘ LỆCH CHUẨN CỔ PHIẾU
 Yêu cầu: Một cổ phiếu có 3 trường hợp tăng trưởng mạnh, bình thường và suy thoái với xác suất
và TSSL đi kèm, tính độ lệch chuẩn bằng hàm VBA:
 Bài làm: Tại sheet 8.CÂU 6
-

Bước 1: Tạo hàm tên là sigma_mean (vùng xác suất, vùng TSSL tương ứng). Tính tỷ suất sinh
lợi trung bình của cổ phiếu, sau đó tính phương sai cổ phiếu, và lấy căn bậc hai để cho ra độ
lệch chuẩn theo công thức:

E(r ) =
σ 2= ]
-

VBA Function ở Module 6:

Function sigma_mean(arr_1 As Range, arr_2 As Range) As Variant
Dim mean As Variant
Dim variance As Variant
mean = Application.WorksheetFunction.SumProduct(arr_1, arr_2)
variance = arr_1.Cells(1, 1) * (arr_2.Cells(1, 1) - mean) ^ 2 + arr_1.Cells(2,
1) * (arr_2.Cells(2, 1) - mean) ^ 2 + arr_1.Cells(3, 1) * (arr_2.Cells(3, 1) 11


PHẠM THỊ KHÁNH LY_TCDL _CH K28_CHIỀU CN

mean) ^ 2
sigma_mean = Sqr(variance)
End Function

-

Bước 2: Gõ hàm = sigma_mean(B2:B4; C2:C4), ta có kết quả:

CÂU 7: VIẾT HÀM VBA ĐỂ TÍNH ĐỘ LỆCH CHUẨN DANH MỤC
 Yêu cầu: Dùng hàm VBA để tính độ lệch chuẩn của danh mục gồm 2 cổ phiếu cho sẵn các thông
số.
 Bài làm: Tại sheet 9.CÂU 7
-


Công thức tính phương sai danh mục như sau:
σ p2 = w12 *σ1 2 + w22 *σ2 2 + 2 *w1 * w2 *σ1 *σ2 *p

-

Bước 1: Tạo một hàm có tên sigma_portfolio (TSSL 1, Sigma 1, tỷ trọng 1, TSSL 2, sigma 2, tỷ
trọng 2, hệ số tương quan).

-

VBA Function ở module 7 như sau:

Function sigma_portfolio(r_1 As Variant, sigma_1 As Variant, w_1 As Variant, r_2 As Variant,
sigma_2 As Variant, w_2 As Variant, hstq As Variant) As Variant
Dim variance As Variant
variance = w_1 ^ 2 * sigma_1 ^ 2 + w_2 ^ 2 * sigma_2 ^ 2 + 2 * w_1 * w_2 * sigma_1 *
sigma_2 * hstq
sigma_portfolio = Sqr(variance)
End Function
- Bước 2: gõ hàm =sigma_portfolio (B2;B3;B5;C2;C3;C5;B4), ta có kết quả:

12


PHẠM THỊ KHÁNH LY_TCDL _CH K28_CHIỀU CN

 NGUỒN
- />- Financial Modeling by Simon Benninga, fourth edition

13