TRƯỜNG ĐẠI HỌC KINH TẾ TP.HCM

TIỂU LUẬN TRÌNH BÀY MƠN:
TÀI CHÍNH ĐỊNH LƯỢNG
Sàn chứng khốn Australian Securities Exchange
( ASX , Australia, Sydney)
Giảng viên hướng dẫn : ThS. Hoàng Thị Phương Anh
Lớp học phần
: 21D1FIN50503808
Họ và tên : Phan Xuân Ngọc Lam
STT
: 08
MSSV : 31181024563
Lớp
:BR001
ĐTDT : 0358147227
Email
:
Thành phố Hồ Chí Minh, tháng 5 năm 2021


ĐỀ BÀI
Sinh viên lấy giá của 30 chứng khoán của các thị trường theo google
form vào cuối mỗi tháng từ 12/2009 -> 04/2021 và thực hiện các yêu cầu
sau:
1.

Tính TSSL, phương sai và độ lệch chuẩn (2đ)

2.

Tính ma trận phương sai-hiệp phương sai theo ít nhất 4 phương
pháp (2đ)

3.

xây dựng đường biên hiệu quả có bán khống theo cách thơng
thường và theo VBA (1,5đ)

4.

xây dựng đường biên hiệu quả không bán khống theo cách thông
thường và theo VBA và so sánh với đường biên có bán khống (2đ)

5.

Xây dựng đường CML (1đ)

6.

Kiểm định mơ hình CAPM và cho biết DM hiện tại bạn chọn có
hiệu quả khơng? Nếu khơng thì điều chỉnh như thế nào?(1,5đ)


I/ CÁCH CHỌN CHỨNG KHOÁN
1. Dữ liệu giá 30 chứng khoáng đã chọn
30 chứng khoán được lấy trong 200 chứng khốn của chỉ số S&P/ASX 200
ST
T

TÊN 30 CHỨNG KHỐN

1
2
3

Altium Ltd

Tên viết
tắt
ALU

AP Eagers Ltd

APE

Carsales.Com Ltd

CAR

Charter Hall Group
4
5 Invocare Ltd
6 Ingenia Communities Group

CHC

James Hardie Industries PLC

JHX

Worley Ltd


WOR

Seven Group Holdings Ltd

SVW

Webjet Ltd

WEB

Abacus Property Group

ABP

Adelaide Brighton Ltd

ABC

AGL Energy Ltd

AGL

ALS Ltd

ALQ

Alumina Ltd

AWC


Western Areas Ltd

WSA

Woodside Petroleum Ltd

WPL

BHP Billiton Ltd

BHP

Vicinity Centres Re Ltd

VCX

7
8
9
10
11
12
13
14
15
16
17
18
19


IVC
INA

LINK DATA
/> /> />m-ltd-historical-data
/> /> /> /> /> /> /> /> /> /> /> /> /> /> /> />

20
21
22
23
24
25
26
27
28
29

Technology One Ltd

TNE

Telstra Corporation Ltd.

TLS

Transurban Group

TCL


Suncorp Group Ltd

SUN

Super Retail Group Ltd

SUL

Ramsay Health Care Ltd

RHC

Rea Group Ltd

REA

Qantas Airways Ltd

QAN

Oil Search Ltd

OSH

Origin Energy Ltd

ORG

Newcrest Mining Ltd

30
Chỉ
S&P/ASX 200
số

NCM
AXJO

/>-one-historical-data
/> /> /> /> /> /> /> /> /> /> />
2. Lý do chọn 30 chứng khốn trên
- 30 chứng khốn này đề có dữ liệu giá từ 12/2009 đến 05/2021 trên thị trường Australia
- 30 chứng khoán này là 30 chứng khoán trong 200 chứng khoán được niêm yết và điều chỉnh và
theo dõi trong chỉ số S&P/ASX 200 ở trị trường Australia.
- 30 chứng khoán được giao dịch với khối lượng lớn và trải dài trên tất cả các lĩnh vực trong trị
trường Australia → 30 chứng khoán này tác động mạnh mẽ đến thị trường Australia


II/ CÁC BƯỚC TIẾN HÀNH
-

Những số liệu trong sheet bảng dữ liệu giá đươc lấy từ linkdata và là giá đóng cửa của mỗi

-

tháng của 30 chứng khốn từ 12/2009 – 04/2021.
Từ các dữ liệu trong link data, ta tiến hành lập bảng giá dữ liệu cho 30 chứng khoán từ
12/2009 – 04/2021:

Câu 1: Tính TSSL, phương sai và độ lệch chuẩn.

-

Từ bảng dữ liệu giá, ta sử dụng hàm =LN(giá sau/giá trước) để tính tốn tỷ suất sinh lợi

-

hàng tháng ghép lãi liên tục của từng chứng khốn .
Tính tỷ suất sinh lợi trung bình, ta sử dụng hàm =AVERAGE (tssl chứng khốn cần tính).
Tính phương sai, ta sử dụng hàm =VAR.S (tssl chứng khốn cần tính).
Tính độ lệch chuẩn sử dụng hàm = STDEV.S ( tssl chứng khoán cần tính).

THỰC HIÊN CÁCH TÍNH TRÊN FILE EXCEL:


→ KẾT QUẢ:
1. Bảng tssl của 30 chứng khoán từ giá đóng của chúng:

2. Bảng tssl trung bình, phương sai và độ lệch chuẩn

Câu 2: Tính ma trận phương sai-hiệp phương sai theo ít nhất 4 phương pháp
Với những giá trị đã tính ở câu 1, ta sẽ sử dụng để tính ma trận phương sai – hiệp phương sai.
Trong bài sử dụng 5 cách để tính ma trận phương sai – hiệp phương sai:
-

Phương pháp kết hợp COVAR+OFFET
Phương pháp theo AT*A/(n-1)
Phương pháp theo mơ hình chỉ số đơn
Phương pháp theo hệ số tương quan
Phương pháp theo skirkage


Trình bày các phương pháp:
2.1. Phương pháp 1: COVAR+OFFET


Dùng hàm: =COVAR(OFFSET('Câu 1 tính tssl,psai,dlc'!$B$5:$B$140,0,'câu 2 tính ma trận pshps'!C$7),OFFSET('Câu 1 tính tssl,psai,dlc'!$B$5:$B$140,0,'câu 2 tính ma trận ps-hps'!$A9))
Trong đó: 'Câu 1 tính tssl,psai,dlc'!$B$5:$B$140 : Tham số cố định
'câu 2 tính ma trận ps-hps'!C$7 : Trượt hàng 7
'câu 2 tính ma trận ps-hps'!$A9: Trượt cột A
→ Kết quả trong file excel

2.2. Phương pháp 2: theo AT*A/(n-1)
Dùng hàm: =MMULT(TRANSPOSE('Câu 1 tính tssl,psai,dlc'!B5:AE140-'Câu 1 tính
tssl,psai,dlc'!B142:AE142),'Câu 1 tính tssl,psai,dlc'!B5:AE140-'Câu 1 tính tssl,psai,dlc'!
B142:AE142)/(COUNT('Câu 1 tính tssl,psai,dlc'!B5:B140)-1)
→ Kết quả trong file excel

2.3. Phương pháp 3: mơ hình chỉ số đơn
- Theo cách trong sách mơ hình tài chính trang 292-293
Đầu tiên dùng hàm SLOPE tính beta của 30 chứng khoán và chứng khoán thị trường, AXJO. Sau
đó dùng cơng thức =C$88*$A90*$D$86 tính ma trân ps-hps
Dùng hàm: =SLOPE (TSSL của chứng khốn cần tính/TSSL của chứng khoán thị trường) =
SLOPE(B5:B140,$AF$5:$AF$140)


Dùng cơng thức : =C$88*$A90*$D$86 = cột Beta chứng khốn * hàng beta chứng khoán * beta
chứng khoán thị trường để tính ma trận phương sai – hiệp phương sai.
Trong đó : C$88; Trượt hàng 88
$A90: Trượt theo cột A
$D$86: tham số cố định
Làm như vậy cho những cột và hàng khác.

→ Kết quả trong file excel

- Mơ hình chỉ số đơn dùng VBA (SIM):
Đoạn code dùng trong VBA


Sau khi nhập đoạn code vào excel dùng hàm = sim('Câu 1 tính tssl,psai,dlc'!B5:AE140,'Câu 1
tính tssl,psai,dlc'!AF5:AF140) = sim( TSSL của 30 chứng khoán, TSSL của chứng khoán thị
trường).
Khi dùng hàm Sim thì bơi đen ơ tính ma trận phương sai – hiệp phương sai sau đó nhập hàm và
bấm ctrl+shift+enter.
→ Kết quả trong file excel

-

Mơ hình đơn sử dụng hàm if

Dùng hàm: IF(C163:AF163=B164:B193,'Câu 1 tính tssl,psai,dlc'!B143:AE143,'Câu 1 tính
tssl,psai,dlc'!AF143*'câu 2 tính ma trận ps-hps'!C162:AF162*'câu 2 tính ma trận ps-hps'!
A164:A193)
Khi dùng hàm IF thì bơi đen ơ tính ma trận phương sai – hiệp phương sai sau đó nhập hàm và
bấm ctrl+shift+enter.
→ giải thích hàm: Nếu tên cổ phiếu ở hàng ngang bằng đúng hàng dọc thì giá trị đầu ra sẽ đúng
bằng phương sai của chứng khốn đó. Cịn nếu sai sẽ bằng = beta của mã chứng khoán 1 * beta
của mã chứng khoán 2* Phương sai chứng khoán thị trường.
→ Kết quả trong file excel


2.4. Phương pháp 4: hệ số tương quan
Bước 1: Sử dụng TSSL ở câu trước, tính ma trận hệ số tương quan bằng cách kết hợp hàm

CORREL + OFFET để tạo ra đường chéo bằng 1.
Hàm: =CORREL(OFFSET('Câu 1 tính tssl,psai,dlc'!$B$5:$B$140,0,'câu 2 tính ma trận ps-hps'!
C$202),OFFSET('Câu 1 tính tssl,psai,dlc'!$B$5:$B$140,0,'câu 2 tính ma trận ps-hps'!$A204))
→ Kết quả trong file excel

Bước 2: Kết hợp độ lệch chuẩn với ma trận hệ số tương quan để tạo thành ma trận phương sai hiệp phương sai bằng cách sử dụng công thức C204*C$238*$A240
→ Kết quả trong file excel


2.5. Phương pháp 5: skirkage
Bước 1: Trước khi tạo ma trận phương sai – hiệp phương sai . Thì tạo một ma trận dựa trên
phương sai các chứng khoán và có phương sai trên đường chéo và những chỗ cịn lại là 0.
Dùng hàm: IF(C280:AF280=B281:B310,'Câu 1 tính tssl,psai,dlc'!B143:AE143,0)
→ Kết quả trong file excel

Bước 2: Kết hợp ma trận ở bước 1 và ma trận phương sai - hiệp phương sai đã tính ở các cách
trên và lamda để tạo ra ma trận phương sai - hiệp phương sai ở cách này
Dùng công thức: E316*C49:AF78+(1-E316)*C281:AF310
→ Kết quả trong file excel


Câu 3: xây dựng đường biên hiệu quả có bán khống theo cách thông thường và theo VBA
3.1: xây dựng đường biên hiệu quả có bán khống theo cách thơng thường
B1: Xây dựng ma trận phương sai - hiệp phương sai và cột TSSL đã tính ở câu 1
Lấy kết quả đã tính ở câu 1
→ Kết quả trong file excel

B2: Tính tốn tỷ trọng của 30 chứng khốn trong các danh mục X và Y để xây dựng đường biên
hiệu quả
-


Giả sử C lượt là 4% và 6% của danh mục X và Y để tính tỷ trọng 30 chứng khoán trong

-

hai danh mục
Tổng tỷ trọng 30 chứng khoán trong mỗi danh mục phải bằng 1
Sử dụng hàm: = MMULT(MINVERSE(“Ma trận phương sai – hiệp phương sai”),“Cột
TSSL -C)/SUM(MMULT(MINVERSE(“Ma trận phương sai – hiệp phương sai” ), “Cột
TSSL -C))


→ Kết quả trong file excel

B3: Xây dựng đường biên hiệu quả
Từ hai danh mục X và Y ta kết hợp thành một danh mục Z
→ Kết quả trong file excel

B4: Vẽ đường biên hiệu quả
-

Sử dụng Data Table để thay đổi tỷ trọng X va Y để chạy ra được các điểm ĐLC và TSSL
của danh mục kết hợp Z. Tập hợp các điểm vừa tìm được là đường biên hiệu quả có bán
khống

→ Kết quả trong file excel


- Dựa vào hai cột độ lệch chuẩn và tỷ suất sinh lợi, ta sẽ được đương biên hiệu quả có bán khống
( insert → chọn Scatter chart.

→ Kết quả trong file excel

3.2: xây dựng đường biên hiệu quả có bán khống theo cách VBA
Bước 1: Xây dựng ma trận phương sai – hiệp phương sai đã tính ở câu trước
→ Kết quả trong file excel


Bước 2: Tính tốn các danh mục đầu tư hiệu quả
Cột TSSL lấy từ tính tốn câu trước và tính toán TSSL, ĐLC và theta của danh mục Z
→ Kết quả trong file excel

Sau đó chạy solver bằng máy trước
→ Kết quả trong file excel


Gắn các tên trong đoạn code vào name manager
→ Kết quả trong file excel

Các bước làm tiếp theo


Đoạn code có bán khống được dùng trong VBA:
Sub Solve()
SolverOk SetCell:="$F$76", MaxMinVal:=1, ValueOf:="0", ByChange:="$F$43:$F$72"
SolverSolve UserFinish:=True
End Sub
Sub Doit()
Range("kq").ClearContents
For counter = 1 To 50
Range("hangso") = -5 + counter * 0.3

Solve
Application.SendKeys ("{Enter}")
Range("kq").Cells(counter, 1) = ActiveSheet.Range("hangso")
Range("kq").Cells(counter, 2) = ActiveSheet.Range("dlc")
Range("kq").Cells(counter, 3) = ActiveSheet.Range("tssl")
Range("kq").Cells(counter, 4) = ActiveSheet.Range("x_1")
Range("kq").Cells(counter, 5) = ActiveSheet.Range("x_2")
Range("kq").Cells(counter, 6) = ActiveSheet.Range("x_3")
Range("kq").Cells(counter, 7) = ActiveSheet.Range("x_4")
Range("kq").Cells(counter, 8) = ActiveSheet.Range("x_5")
Range("kq").Cells(counter, 9) = ActiveSheet.Range("x_6")


Range("kq").Cells(counter, 10) = ActiveSheet.Range("x_7")
Range("kq").Cells(counter, 11) = ActiveSheet.Range("x_8")
Range("kq").Cells(counter, 12) = ActiveSheet.Range("x_9")
Range("kq").Cells(counter, 13) = ActiveSheet.Range("x_10")
Range("kq").Cells(counter, 14) = ActiveSheet.Range("x_11")
Range("kq").Cells(counter, 15) = ActiveSheet.Range("x_12")
Range("kq").Cells(counter, 16) = ActiveSheet.Range("x_13")
Range("kq").Cells(counter, 17) = ActiveSheet.Range("x_14")
Range("kq").Cells(counter, 18) = ActiveSheet.Range("x_15")
Range("kq").Cells(counter, 19) = ActiveSheet.Range("x_16")
Range("kq").Cells(counter, 20) = ActiveSheet.Range("x_17")
Range("kq").Cells(counter, 21) = ActiveSheet.Range("x_18")
Range("kq").Cells(counter, 22) = ActiveSheet.Range("x_19")
Range("kq").Cells(counter, 23) = ActiveSheet.Range("x_20")
Range("kq").Cells(counter, 24) = ActiveSheet.Range("x_21")
Range("kq").Cells(counter, 25) = ActiveSheet.Range("x_22")
Range("kq").Cells(counter, 26) = ActiveSheet.Range("x_23")

Range("kq").Cells(counter, 27) = ActiveSheet.Range("x_24")
Range("kq").Cells(counter, 28) = ActiveSheet.Range("x_25")
Range("kq").Cells(counter, 29) = ActiveSheet.Range("x_26")
Range("kq").Cells(counter, 30) = ActiveSheet.Range("x_27")
Range("kq").Cells(counter, 31) = ActiveSheet.Range("x_28")
Range("kq").Cells(counter, 32) = ActiveSheet.Range("x_29")
Range("kq").Cells(counter, 33) = ActiveSheet.Range("x_30")


Next counter
End Sub
Private Sub benny()
i=1
Do Until (i = AddIns.Count) Or (AddIns(i).Name = "SOLVER.XLA")
i=i+1
Loop
If (AddIns(i).Name = "SOLVER.XLA") Then
AddIns(i).Installed = True
j=1
Do Until (j = Application.VBE.ActiveVBProject.References.Count) Or _
(Application.VBE.ActiveVBProject.References(j).Name = "SOLVER.xla")
j=j+1
Loop
If (Application.VBE.ActiveVBProject.References(j).Name <> "SOLVER.xla") Then
Application.VBE.ActiveVBProject.References.AddFromFile AddIns(i).FullName
End If
Else
MsgBox Prompt:="Solver not found this workbook will not WORK", Buttons:=vbCritical
End If
End Sub

Private Sub Workbook_Open()
benny


End Sub
Sau khi chạy VBA có bảng kết quả:
→ Kết quả trong file excel

Dựa vào 2 cột độ lệch chuẩn và tỷ suất sinh lợi vẽ đường biên hiệu quả có bán khống

Câu 4: xây dựng đường biên hiệu quả không bán khống theo cách thông thường và theo
VBA và so sánh với đường biên có bán khống
4.1: xây dựng đường biên hiệu quả không bán khống theo cách thông thường
Bước 1: Xây dựng ma trận phương sai- hiệp phương sai và tỷ suất sinh lợi trung bình đã tính ở
các câu trước
→ Kết quả trong file excel


Bước 2: Xây dựng đường biên hiệu quả không bán khống và vẽ đường biên hiệu quả không bán
khống
Các điều kiện khi chạy solver:

Cơng thức cần tính cho ơ kết quả phía dưới

Sau đó chạy solver cho từng ơ theo sự thay đổi của C cho đến khi có những dữ liệu ta cần thì
dừng lại hoặc có 1 chứng khốn có tỷ trọng =100 thì dừng lại
Bảng slover:


→ Kết quả trong file excel khi chạy solver theo sự thay đổi của 20 hằng số


Sau đó sử dụng 2 cột đlc và tssl để vẽ đường biên hiệu quả không bán khống


4.2: xây dựng đường biên hiệu quả không bán khống theo VBA
B1Sử dụng ma trận phương sai- hiệp phương sai đã tính tốn ở câu trước

B2: Tính tốn danh mục đầu tư hiệu quả
Tính tssl, độ lệch chuẩn và theta của danh mục


Sau đó chạy solver để tìm các tỷ trọng đầu tư tối ưu của danh mục
→ Kết quả chạy slover

Gắn các tên trong đoạn code vào name manager


Tóm tắt các bước thực hiện trước khi chạy code VBA

Đoạn code chạy VBA cho trường hợp không bán khống
Sub Solve()
SolverOk SetCell:="$F$76", MaxMinVal:=1, ValueOf:="0", ByChange:="$F$43:$F$72"
SolverSolve UserFinish:=True
End Sub
Sub Doit()
Range("kq").ClearContents
For counter = 1 To 20
Range("hangso") = 0.005 + counter * 0.0003
Solve