金融工程 Section 1

第一章: 程序编写应注意的几点

我不会说编写程序是容易，但我可以这样说它不是真的太难因为很多人都这样认为。程序编写不是纯理论性的，好似数学或天文学一据；特别是它不需要你有一个高等学位才会做得好。(当然，电脑科学有它的理论，但它不一定需要你完成学位课程学习后，才能够运用它们。在现实生活中，有很多程序编写高手都没有所谓大学学位。)

将程序编写和其他工作比较，你会发覚程序编写没有所谓天才、例如特别强壮的运动员或声线特别高亮或低沉的歌唱家。程序编写只需要你细心和工匠之心。如果你有上过一些程序编写的工作坊，你便会发覚有一些同学他们可以不费心力便可完成任务，但有些同学则不然，他们屡屡错，尤其老师强调容易泛错的地方。他们两者的分别在于，成功的学星他们会给予更多注意会发生的事情，同时会更加小心及细心他们做过什庅。

成为一个好的程序编写员，你需要注意以下几点(1)注意细节，(2)要够蠢,(3)好的记忆，和(4)要有不同程度上的抽象思维。

(1)注意细节

编程时，细节就是一切。电脑是十分蠢的，你编写完大部份的程序，然后你对电脑说:「好啦，这就是我要的东西，你跟着完成其他部份！」对不起，这是完全办不到的。你要完全按照它的要求，一字一字的键入电脑，这里要s，那里要t，错一或缺一都不可。

假如程序要你先宣告変动词先，那庅，你一定在开始时，先宣告变动词。如不，你便得不到应该得到的结果。

(2)要够蠢

电脑可以说真的十分蠢，它只会做你叫它做的事，而且不会多亦不会少。当你正进行编程时，你应该时刻告诉自己电脑是十分蠢，那庅你便在一种态度上要将所有特定的东西或细节装进你的程序里，而不是假设它们自己会发生，真的除非你已告欣它们。

(3)好的记忆

编写程序时会有很多事你要记在心里,例如、那些程序里已有的功能函数、那些你定义的功能及可变数等。你记得越多，成功的机会便越多。

(4)要有不同程度上的抽象思维

这点可说是最重要的在程序编写时。电脑是人类建做的最复杂的系统，当你编程时，你要将电脑每一层次的功能都要记着，这都会是巨大的工作量，甚至你只编写一个简单的程序。

所以以下介绍一个很有用的技巧去管理软件系统，它就是将程序区分为数个或数十个少程序。区分程序为较少的个体是一个十分重要的技巧，试想想在第三点时，记着一个少的程序是可以做到。但在一个非常大的程序，会有不下上百万行的程序源码，看通或明白程序的全部，我想是不可能的。如果将程序区分为数十个不同功能的小程序，那庅是比较容易处理。

第二章: 投资回报及风险

前一些时间在坊间买了一本关于金融投资决策书叫“运用EXCEL VBA进行高效投资决策”，编着：韩良智，出版社：中国铁道出版社。书中提供一些用EXCEL VBA编写的应用程序，例如，证券连续复利收益率分布及其均值的自动计算模型、多个风险资产投资组合的有效边界模型、股票价格的随机模拟模型、美式看跌期权价格计算模型…等，书中所述程序模型有不下七十多个，其中不乏高超之作。但大多数的程序在设计时都不能被之后发展更复杂的金融产品时从新利用，所以本人以另一个角度编写书中的程序达到以下目的：

1)更精简，以最少程序项来完成编程

2)更快，以程序最快完成为首要目的

3)可读性，能使有一定基础的金融或编程人员明白

4)遁环性，使某些核心程序能遁环使用

为了方便我会将“运用EXCEL VBA进行高效投资决策”书，简称为“投资决策”。在书中的第2章是讲解“单一证券的收益与风险”，其中一个程序- 证券连续复利收益率分布及其均值的自动计算模型，(P.53-P.56) 。其编程原码如下：

Sub 计算()

Dim n As Integer, i As Integer, ss As Single

Dim mymax As Single, mymin As Single, mys As Single

n = https://www.360docs.net/doc/f11561085.html,edRange.Rows.Count - 5

ss = 0

mymax = -100000000

mymin = 100000000

'计算连续复利收益率

For i = 1 To n

Cells(5 + i, 3) = Round(Log(Cells(5 + i, 2) / Cells(4 + i, 2)), 4)

ss = ss + Cells(5 + i, 3)

If Cells(5 + i, 3) >= mymax Then

mymax = Cells(5 + i, 3)

End If

If Cells(5 + i, 3) <= mymin Then

mymin = Cells(5 + i, 3)

End If

Next i

mymin = Abs(Int(mymin * 100 - 2) / 100)

mymax = Abs(Int(mymax * 100 + 2) / 100)

mys = IIf(mymin > mymax, mymin, mymax)

Range("D5") = Round(ss / n, 4)

Range(Cells(6, 3), Cells(5 + n, 3)).NumberFormat = "0.00%"

Range("D5").NumberFormat = "0.00%"

For i = 1 To n

Cells(5 + i, 4) = Range("D5").Value

Cells(5 + i, 4).Font.ColorIndex = 2

Next i

Si=0

For i = 1 To n

Si = si + (Cells(5 + I, 3) + ss)^2

Next i

Range("E5") = Round(sqr(si / n, 4))

Range("E5").NumberFormat = "0.00%"

'绘制连续复利分布图

Dim myrange As String

myrange = "C6:D" & 5 + n

Range(myrange).Select

Charts.Add

ActiveChart.ChartType = xlLineMarkers

ActiveChart.SeriesCollection(1).Name = "=""收益率分布"""

ActiveChart.SeriesCollection(2).Name = "=""收益率分布均值"""

ActiveChart.Location Where:=xlLocationAsObject, Name:="Sheet1"

With ActiveChart

.HasTitle = True

.ChartTitle.Characters.Text = "连续复利收益率分布图"

.Axes(xlCategory, xlPrimary).HasTitle = True

.Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text = "时间"

.Axes(xlValue, xlPrimary).HasTitle = True

.Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "连续复利收益率"

End With

ActiveChart.Legend.Position = xlBottom

ActiveChart.Axes(xlValue).Select

With ActiveChart.Axes(xlValue)

.MinimumScale = -mys

.MaximumScale = mys

.MinorUnitIsAuto = True

.MajorUnitIsAuto = True

.CrossesAt = -mys

.ReversePlotOrder = False

.ScaleType = xlLinear

.DisplayUnit = xlNone

End With

Selection.TickLabels.NumberFormatLocal = "0%"

ActiveChart.Axes(xlCategory).Select

With ActiveChart.Axes(xlCategory)

.CrossesAt = 1

.TickLabelSpacing = 1

.TickMarkSpacing = 1

.AxisBetweenCategories = False

.ReversePlotOrder = False

End With

ActiveChart.Axes(xlValue).MajorGridlines.Select

ActiveChart.SeriesCollection(2).Select

With Selection.Border

.ColorIndex = 57

.Weight = xlThick

.LineStyle = xlContinuous

End With

With Selection

.MarkerBackgroundColorIndex = xlAutomatic

.MarkerForegroundColorIndex = xlAutomatic

.MarkerStyle = xlNone

.Smooth = False

.MarkerSize = 9

.Shadow = False

End With

End Sub

Sub 清除()

Range("B5") = ""

Range("D5") = ""

Sheet1.ChartObjects.Delete

Rows("6:65536").Delete Shift:=xlUp

End Sub

源代码2.1: VBA源代码- 证券连续复利收益率分布及其均值的自动计算模型

从新编写后的程序分为四个小程序及一个自定义功能程序。程序最先由IDF程序开始，先从电子表格中读取所需资料，然后交由calReturn程序计算答案及图表资料，之后将答案交回IDF程序，IDF将资料交到制图程序createChart，制图程序绘制图表后完成全部作业。

在整个程序编写跟“投资决策”书中分别是多利用可変数令程序跑得更快，另外将程序用功能性分开写，在读时更容易理解。

Sub IDF()

Dim n As Integer, i As Integer, ss As Single, si As Single

n = https://www.360docs.net/doc/f11561085.html,edRange.Rows.Count - 5

Dim monthstockprice() As Double: ReDim monthstockprice(1 To n + 1)

Dim monthcumreturn() As Double: ReDim monthcumreturn(1 To n)

Dim monthstd() As Double: ReDim monthstd(1 To n)

Dim mys As Double

'计算连续复利收益率

For i = 1 To n + 1

monthstockprice(i) = Range("B4").Offset(i, 0)

Next i

Call calReturn(monthstockprice, n, monthcumreturn, ss, mys, monthstd, si)

'Put back data

For i = 1 To n

Range("C5").Offset(i, 0) = monthcumreturn(i)

Next i

Range("D5") = Round(ss / (n), 4)

For i = 1 To n

Range("D5").Offset(i, 0) = Range("D5").Value

Range("D5").Offset(i, 0).Font.ColorIndex = 2

Next i

Range("E5") = Round(Sqr(si / n), 4)

'绘制连续复利分布图

Call createChart(n, mys)

End Sub

源代码2.2: VBA源代码– IDF()

Sub calReturn(monthstockprice() As Double, n As Integer, monthcumreturn() As Double, ss As Single, _ mys As Double, monthstd() As Double, si As Single)

Dim mymax As Double, mymin As Double

Dim sm As Double

ss = 0

si = 0

mymax = -100000000

mymin = 100000000

'计算收益率

For i = 1 To n

monthcumreturn(i) = Round(Log(monthstockprice(i + 1) / monthstockprice(i)), 4)

ss = ss + monthcumreturn(i)

If monthcumreturn(i) >= mymax Then

mymax = monthcumreturn(i)

End If

If monthcumreturn(i) <= mymin Then

mymin = monthcumreturn(i)

End If

Next i

'计算标准差

sm = Round(ss / n, 4)

For i = 1 To n

si = si + (monthcumreturn(i) - sm) ^ 2

Next i

mymin = Abs(Int(mymin * 100 - 2) / 100)

mymax = Abs(Int(mymax * 100 + 2) / 100)

mys = Max(mymin, mymax)

End Sub

源代码2.3: VBA源代码– calReturn()

Sub createChart(n As Integer, mys As Double)

'绘制连续复利分布图

Dim myrange As String

myrange = "C6:D" & 5 + n

Range(myrange).Select

Charts.Add

ActiveChart.ChartType = xlLineMarkers

ActiveChart.SeriesCollection(1).Name = "=""收益率分布"""

ActiveChart.SeriesCollection(2).Name = "=""收益率分布均值"""

ActiveChart.Location Where:=xlLocationAsObject, Name:="Sheet1"

With ActiveChart

.HasTitle = True

.ChartTitle.Characters.Text = "连续复利收益率分布图"

.Axes(xlCategory, xlPrimary).HasTitle = True

.Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text = "时间"

.Axes(xlValue, xlPrimary).HasTitle = True

.Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "连续复利收益率"

End With

ActiveChart.HasLegend = True

ActiveChart.Legend.Position = xlBottom

ActiveChart.Axes(xlValue).Select

With ActiveChart.Axes(xlValue)

.MinimumScale = -mys

.MaximumScale = mys

.MinorUnitIsAuto = True

.MajorUnitIsAuto = True

.CrossesAt = -mys

.ReversePlotOrder = False

.ScaleType = xlLinear

.DisplayUnit = xlNone

End With

Selection.TickLabels.NumberFormatLocal = "0%"

ActiveChart.Axes(xlCategory).Select

With ActiveChart.Axes(xlCategory)

.CrossesAt = 1

.TickLabelSpacing = 1

.TickMarkSpacing = 1

.AxisBetweenCategories = False

.ReversePlotOrder = False

End With

ActiveChart.Axes(xlValue).MajorGridlines.Select

ActiveChart.SeriesCollection(2).Select

With Selection.Border

.Weight = xlThick

.LineStyle = xlContinuous

End With

With Selection

.MarkerBackgroundColorIndex = xlAutomatic

.MarkerForegroundColorIndex = xlAutomatic

.MarkerStyle = xlNone

.Smooth = False

.MarkerSize = 9

.Shadow = False

End With

End Sub

源代码2.4: VBA源代码– createChart() Sub 清除()

Range("C5:d17") = ""

Range("D5:d17") = ""

Sheet1.ChartObjects.Delete

Rows("18:65536").Delete Shift:=xlUp

End Sub

源代码2.5: VBA源代码–清除()

Function Max(x As Double, y As Double) As Double

If x > y Then Max = x Else Max = y

End Function

源代码2.6: VBA源代码– Function Max()

图2. 1: 电子表格的布局及计算结果

第三章: 多个资产投资回报及风险

从上一章的编程是以单一资产投资回报及标准偏差的计算，但在多个资产投资时，其回报及标准偏差是什样计算。在书中的第三章介绍出协方差矩阵计算方法，其中一个程序协方差矩阵计算--离散收益率情况，(P.63-P.67) 。其编程原码如下：

源代码3.1: VBA源代码–计算()

源代码3.2: VBA源代码–准备数据()

从新编写后的程序分为五个小程序。程序最先由IDF程序开始，先从电子表格中读取所需资料，然后制作所需数量的证券，制作输入表格。输入资料后转入readSecData程序，交由calReturnandVariance及calCovariance程序计算答案，之后将答案交回readSecData程序，印出答案后完成全部作业。

在整个程序编写跟“投资决策”书中分别是多利用可変数令程序跑得更快，另外将程序用功能性分开写，在读时更容易理解。

Sub IDF()

Dim n As Integer, m As Integer, i As Integer

n = Range("B3").Value

m = Range("B4").Value

Range("A10") = "输入各个证券的收益率及发生概率"

Range("A10").HorizontalAlignment = xlCenter

Range(Cells(10, 1), Cells(10, 2 + n)).Select

Selection.Merge

With Selection.Borders(xlEdgeBottom)

.LineStyle = xlContinuous

.Weight = xlMedium

.ColorIndex = xlAutomatic

End With

Cells(11, 2) = "发生概率"

For i = 1 To n

Cells(11, i + 2) = "证券" & i

Cells(11, i + 2).HorizontalAlignment = xlCenter

Next i

For i = 1 To m

Cells(11 + i, 1) = "情况" & i

Cells(11, i + 2).HorizontalAlignment = xlCenter

Next i

Range(Cells(11 + m, 1), Cells(11 + m, 2 + n)).Select

With Selection.Borders(xlEdgeBottom)

.LineStyle = xlContinuous

.Weight = xlMedium

.ColorIndex = xlAutomatic

End With

End Sub

源代码3.3: VBA源代码– IDF()

Sub readSecData()

Dim n As Integer, m As Integer, i As Integer, j As Integer

n = Range("B3").Value 'Number of Stock

m = Range("B4").Value 'Number of Probabilities

Dim stockReturn() As Double: ReDim stockReturn(1 To m, 1 To n)

Dim stockPababilities() As Double: ReDim stockPababilities(1 To m)

Dim expectedReturn() As Double: ReDim expectedReturn(1 To m)

Dim eVariance() As Double: ReDim eVariance(1 To n)

Dim eCovariance() As Double: ReDim eCovariance(1 To n, 1 To n)

For i = 1 To m

For j = 1 To n

stockReturn(i, j) = Range("B11").Offset(i, j)

Next j

Next i

For i = 1 To m

stockPababilities(i) = Range("B11").Offset(i, 0)

Next i

Call calReturnandVariance(stockReturn, stockPababilities, n, m, expectedReturn, eVariance)

Call calCovariance(stockReturn, stockPababilities, n, m, expectedReturn, eVariance, eCovariance)

'Output Data

Range("A12").Offset(m, 0) = "各证券的期望收益率"

Range("A12").Offset(m + 1, 0) = "各证券的标准差"

Range("A12").Offset(m + 3, 0) = "各证券间的协方差"

For i = 1 To n

Range("B12").Offset(m + 4, i) = "证券" & i

Range("B12").Offset(m + 4, i).HorizontalAlignment = xlCenter

Next i

For i = 1 To n

Range("B12").Offset(m + 4 + i, 0) = "证券" & i

Cells(11, i + 2).HorizontalAlignment = xlCenter

Next i

For i = 1 To n

Range("B12").Offset(m, i) = expectedReturn(i)

Range("B12").Offset(m + 1, i) = eVariance(i)

Next i

For i = 1 To n

For j = 1 To n

Range("B12").Offset(m + 4 + i, j) = eCovariance(i, j)

Next j

Next i

End Sub

源代码3.4: VBA源代码– readSecData()

Sub clearData()

Cells(3, 2) = ""

Cells(4, 2) = ""

Rows("10:1000").Delete shift:=xlUp

End Sub

源代码3.3: VBA源代码–准备数据()

Sub calReturnandVariance(stockReturn() As Double, stockPababilities() As Double, n As Integer, m As Integer, _

expectedReturn() As Double, eVariance() As Double)

Dim i As Integer, j As Integer

For i = 1 To n 'Number of Stock

expectedReturn(i) = 0

For j = 1 To m 'Number of Probabilities

expectedReturn(i) = expectedReturn(i) + stockPababilities(j) * stockReturn(j, i) Next j

Next i

For i = 1 To n 'Number of Stock

eVariance(i) = 0

For j = 1 To m 'Number of Probabilities

eVariance(i) = eVariance(i) + stockPababilities(j) * (stockReturn(j, i) - expectedReturn(i)) ^ 2 Next j

eVariance(i) = eVariance(i) ^ 0.5

Next i

End Sub

源代码3.5: VBA源代码– calReturnVariance()

Sub calCovariance(stockReturn() As Double, stockPababilities() As Double, n As Integer, m As Integer, _

expectedReturn() As Double, eVariance() As Double, eCovariance() As Double) Dim i As Integer, j As Integer, t As Integer

For i = 1 To n

eCovariance(i, j) = 0

For t = 1 To m

eCovariance(i, j) = eCovariance(i, j) + stockPababilities(t) * (stockReturn(t, i) _

- expectedReturn(i)) * (stockReturn(t, j) - expectedReturn(j))

Next t

Next j

Next i

End Sub

源代码3.6: VBA源代码– calCovariance()

图3. 1: 电子表格的布局及计算结果

在第二及第三章的程序只是跟书中的想法及数学模式加以修改，当中并没有大的修改原因只是想列举出少少的改变便可使程序走得更加好及更加快。但在以下的附件一所举出的例子，则是以不同的起点及做法，甚或以不同的数学模型去计算出最优化投资组合的内容。

附件一 : 最优投资组合均值-方差计算

马克维茨, Harry M. Markowitz, 以投资资产收益率的方差作为投资风险的测度建立了组合资产投资模型,并进行最优资产组合的选择。马克维茨提出其核心问题是解决资产在市场上各种各样的投资机会，投资者如何选择理想的资产组合，包括投资组合的资金比例。以使投资风险最少而预期收益最大。在现实生活中，投资者总是把他们有限资源放在收益最高的资产上。资产组合的选择的目的是为了实现风险既定而收益最大化或收益既定而风险最少化，具有降低投资活动风险的机制。本章节会应用到一套由EXCEL 及VBA 编写的数量工具、均值-方差最优投资组合。一个投资组合里面有n 个资产，而每个资产的价格为(S 1, … , S n ) ，数量则是(q 1, … , q n ) 。在每一段时期里，投资组合的价值会因应每个资产的价格的变动而改変(ΔS 1, … , ΔS n ) 。其盈亏如下

ΔSp=∑q n i=1i ΔS i (1.1)

此模型最主要的目的是找出在有限资源内最优化的投资组合是什庅，在随机状态下用相关系数统计出各资产的价格改変。

(1.1) 可改写成

Rp=∑w n i=1i r i , ∑w n

i=1I = 1 (1.2)

跟据以上(1.2) 式，可引伸出投资组合回报由它们的均值及方差演変出

μp = ∑w n i=1i μi (1.3)

σp 2= ∑n i=1∑w n j=1i w j μij (1.4)

μp 及 σpi 2

分别代表每一资产回报的均值及方差，r i 和σij ，而i ≠j ，而此代表它门不同回报r i 及r j 的协方差。

在此架构下，”最优均值-方差计算”最主要的工作是找出最优投资组合的组成，此组合在有限的预算下而达致最低风险(1.4) 从定的期望回报(1.3) 下。从(1.4) 式中的风险w i 2σi 2是正值。 (1.3) 及(1.4) 可有效写成为矩阵模式

Minimize σp 2= w t

Σw (1.5)

Subject to w t μ=μp u t w=1

w =w1:wn μ=μ1:μn u =1:1 Σ=[σ11?σ1n

???σn1?σnn ]

方程式(1.5) 的最优化是允许w i 的允许卖空。此程序可用Langrange Multiplier 解折，而此最

佳的解决方法最初由Merton 成功计算出。

w=[(cμp-A)(Σ-1μ)+(B-Aμp)(Σ-1u)]/(BC-A2) (1.6) σp2=(cσp2-2Aμp+B)/BC-A2) (1.7) A=u TΣ-1μ,

B= μTΣ-1μ,

C= u TΣ-1u,

由于Σ矩阵是对应的，所B同C，和份母BC-A2全部是正值。

从以上的分析方案主要涉及的资产都是有风险性的。但我们可以加进一些无风险资产例如现金，此可产生一定性的回报μO。而w o资产的比重。重写后的方程式変为

Minimize σp2= w TΣ-1w (1.9)

Subject to w Tμ+w oμo 和u T w+w o=1

再用前次的解方程的方法－Langrange Multiplier

w o=1-[(μp-μo)(A-μ0C)]/(Cμo2-2Aμ0+B) , w=[(μp-μo)(Σ-1μ-μ0Σ-1u)]/(Cμo2-2Aμ0+B) (1.10)

此可计算出的效率前缘

μp=μ0+σp(Cμo2-2Aμ0+B)0.5(1.11)

以上将”最优均值-方差计算”投资组合在风险资产上可长可短的组合。假设，投资组合由于内部原因不可以投资于可卖空的风险资产，那庅，在只可长盘的组合里，资产组合w i全都变为不可卖空。由此，”最优均值-方差计算”里的(1.9)上新的限制

Minimize σp2= w TΣ-1w (1.12)

Subject to w Tμ+w oμo =μp, u T w+w o=1 w1, … , w n ≥0

在(1.12)中的现金都是没有设限的,它可以是正或负，甚或是零。原则上(1.12)都是用’Langrange Multplier’的方法求出答案。但是在找出最优组合时，由于的不等式的条件下是不可能归于零的。

马克维茨发展出有效程序令到我们很解决”最优均值-方差计算”中(1.12)并简单地找出(1.10)的结果。先考虑另外一个”最优均值-方差计算”中(1.12)的程序,并删除不可卖空的指令{W1,,,W n}，叫它做Out子集,将是零的数字。马克维茨程序的基本意思是最优化的解决方法在于某一问题可能是其原本问题出现可能是答案。最优化的解决方法是简单从1.10中取得并加已修改便是{Σ, μ, u}。假如W i是Out子集中，我们可令μ和u向量中i th行及列为零，除了对角缐数字为1外

μi→0, u i→0(4.13)

Σi1, … , Σin→0, Σ1i, … , Σni→0Σii →1

假设(Σm, μm, u m)是根据Out子集修改后的数组。它的最优化解程是

w o=1-[(μp-μo)(A m-μ0C m)]/(C mμo2-2A mμ0+B m),

w=[(μp-μo)(Σm?1μm-μ0Σm?1u m)]/(C mμo2-2A mμ0+B m) (1.14)

它的

λ1=(μp-μo) /(C mμo2-2A mμ0+B m),

λ2=-μo (μp-μo) /(C mμo2-2A mμ0+B m)

在1.14式中的Out子集里的W i=0。所有不在Out子集的W i都不会是负数，所以在1.14会是1.12的解决方法而会适合Kuhn-Tucker的条件:

?L/?w0=-λ1μ0-λ2=0 (4.15)

?L/?w0=(Σw-λ1μ-λ2u)i=0 w1>0 (4.16)

>0 wi=0, for 1= 1, … , n EXCEL 电子表格

为使程序运作比较简单,将电子表格分为:

日收盘价- 风险资产每日历史收盘价。

收益率- 根据所需日夸度的收盘价格计算出历史收益率。

最优均值-方差计算- 根据收益率计算及找出最优的组合。

图1.1是15种香港及上海证券由2010/1/4到2011/5/24的日收盘价。每一列的都收录了一种资产的收盘价，由B列开始。而A列是记录收盘价的日期。而第一行则是证券代码。

图1.1 :电子表格(日收盘价)的布局及每日收盘价.

图1.2则是〝收益率〞电子表格，它是记录历史资产回报。它是根据〝最优均值-方差计算”中timeHorizon计算出由〝日收盘价〞中原始资料。〝收益率〞的次序都是由第一行的证券代码逐一计算。以简单的输入如A1＝日收盘价!B2即可。投资的时间夸度可以日计算。例如，可以已5天时间为一夸度(timeHorizon=5)。那庅，在〝日收盘价〞的B3便是回报表中A2的开盘价，而〝日收盘价〞的B8则是其收盘价(即5天之后)。其他则如此类推。我们可以用简单的OFFSET功能及ROW()则可轻易完成。

开盘价= OFFSET(日收盘价!$A$3, (ROW()-2)*timeHorizon, COLUMN() )

收盘价= OFFSET(日收盘价!$A$3, (ROW()-1)*timeHorizon, COLUMN() )

收益率= (收盘价- 开盘价)/ 开盘价

而COLUMN()则可提供一个参照证券代码为主的计算行。回报计算一直到〝日收盘价〞的资料完结为止。当收盘价指到一个空白的资料时，便是一个停止的信号。以下函数是放在〝收益率〞上的:

IF(OFFSET(日收盘价!$A$3, (ROW()-1)*timeHorizon, COLUMN() ) =””,””,

(OFFSET(日收盘价!$A$3, (ROW()-1)*timeHorizon, COLUMN() )

- (OFFSET(日收盘价!$A$3, (ROW()-2)*timeHorizon, COLUMN() ))

/(OFFSET(日收盘价!$A$3, (ROW()-2)*timeHorizon, COLUMN() ))

图1.2 : 收益率的布局.

在〝最优均值-方差计算”中，先建立起方差及协方差的矩阵Σ。图1.3是一个由使用者定义的投资组合内容的布局。在表中定下投资组合内容不能超过十种。使用者可输入证券代码在C22:L22。输入多少个证券可以在D34中反映出D34=COUNTA(C22:L22) ，这可计算出使用者输入了多少个证券代码。C23:L23是用来指出输入证券是第几个证券资料。

C23 = IF(C22=””,””, MATCH(C22, 收益率!1:1, 0)

在图1.3，C22输入证券代码是0285.HK,从以上功能实时找出在回报表的第几个列，并将资料传回到J4上。跟着我们可以利用些列数去建做协方－协方差的矩阵，此矩阵反映在C24:L33并命名为(covmatrix)。我们首先重复垂直的次序(B24:B33) B24=IF(C23=””,””,C23)。因此,每一个矩阵资料输入都会是与相互关系的目标列相互影响。举例来说,输入到C24的资料是来自B24同C23。相互关系的回报资料在协方差的计算主要由OFFSET功能指定出在〝收益率〞表中的相对地址。

COVAR(OFFSET(收益率!$A$2, 0, $I5-1, nsample),

OFFSET(收益率价!$A$2, 0, J$5-1, nsample)) (4.17)

在目标栏址内，行与列的参照地址是由资料系列里的(nsample)然后定下以下规范是多少。上限是由回报率的资料大少决定,从由G35=COUNT(A:A)定义出从〝收益率〞表中A行多少又数字的字段。假如有空白拦位在C23:L23出现，同时都会在B24:B33出现) ，那庅在协方差

矩阵会出行与列冗位。由于这些没有用到的行与列,矩阵将补上为零，除了对角缐上的数字仍然是1外。以下是定义在C24及其他字段的公式:

J5=IF(OR($B24=””,C$23=””),

IF(ROW()-ROW($C$24)=COLUMN()-COLUMN($C$24),1,0),

COVAR(OFFSET(收益率!$A$2,0,$B24-1,nsample),

OFFSET(收益率!$A$2,0,C$23-1,nsample)))

图1.3 : 协方差矩阵的布局.

在图1.4,平均回报μ由C5:C14定义(mvector)与(1.17)相同，它都可以由I5:I14演化出。例如，平均回报，C5，可根据B24计算出:

AVERAGE(OFFSET(收益率!$A$2,0,$B24-1,nsample)

再者，平均回报向量会変为零，当B24:B33其中有些栏址是空白的时侯。向量u都会是一样(uvector)。以下是平均回报及向量u的公式:

图1. 4: 最优投资组合内容的布局.

首先,考虑的是(1.5)公式,是可卖空风险资产。在图 1.3中,我们已评估出A, B及C为D36(Amvalue) ，D37(Bmvalve)和D38(Cmvalve)。

D36=MMULT(TRANSPOSE(uvector), MMULT(MINVERSE(covmatrix), mvector))

D37= MMULT(TRANSPOSE(mvector), MMULT(MINVERSE(covmatrix), mvector))

D38=MMULT(TRANSPOSE(uvector), MMULT(MINVERSE(covmatrix), uvector))

在图1.4中，投资组合的期望回报μp(meanport) ，而此回报是根据C18的每日的回报。在公式1.6的最优化的投资组合的比重是可由D5:D14的公式找出

{D5:D14=((Cvalue*meanport-Amvalue)*MMULT(MINVERSE(covmatrix), mvector)

+(Bmvalue-Amvalue*meanport)* MMULT(MINVERSE(covmatrix), uvector)

/(Bmvalue*Cmvalue-Amvalue^2)}

最低投资组合的风险σp2可以在D19计算其最佳值

D19= MMULT(MINVERSE(D5:D14), MMULT(MINVERSE(vcmatrix, D5:D14))

当考虑到公式(1.9)时，即包括现金。在图1.6，无风险回报则是由D17=C17*timeHorizon(定义为riskfree)，每日回报C17。它的最优投资组合内容w同现金w0则是由E5:E14和E17找出，它分别为:

{E5:E14=(meamport-riskfree) *MMULT(MINVERSE(covmatrix), mvector)

-riskfree* MMULT(MINVERSE(covmatrix), uvector))

/(Cmvalue*riskfree^2-2*Amvalue*riskfree+Bmvalue)}

E15=(meanport-riskfree) *(Amvalue-riskfree*Cmvalue)

/(Cmvalue*riskfree^2-2*Amvalue*riskfree+Bmvalue)}

现在考虑公式(1.12)可卖空风险资产。先考虑用EXCEL的规划求解去找出答案。在图1.6，投资组合比重w是在G5:G14。而w0的现金比重在G15,而它的预算的w0=1 - u T w

G15=1- MMULT(TRANSPOSE(uvector),G5:G14)

G18同G19,估算出它的平均回报w Tμ=w0μ0同风险w TΣw

G18=MMULT(TRANSPOSE(G5:G14),mvector) + G15*riskfree

G19=1- MMULT(TRANSPOSE(G5:G14), MMULT(covmatrix, G5:G14))

而规划求解的配置

图1. 5: 规划求解配置

.

当EXCEL加上VBA

一个最快及有效的方法去解〝最优均值-方差计算”的问题是用VBA去实行Markowitz的解程。此法能保证能找出最优组合。先验查所有可能的Out集和由最原先的问题的最优组合里指出的案例。Out集的大小可以是N out=0到N out=n-1。由每一个N out,会有N c个Out集(相等于N out 里选出N个)。

从所有可能Out集中，只有一个组合会达到最优化的组合w (1.14),此组合会符合可卖空并且可Kuhn-Tucker的条件(1.16)。

我们先发展出GetOutSubset()的源码在源码1.2。nmax和ncmax是定下n和nc的最大可能值。在〝最优均值-方差计算”，资产数量的上限定为10个以内。即nmax=10,同时Out集的最大值则是Ncmax=252。

Sub GetOutSubset(n As Integer, k As Integer, ByRef Nc As Integer, ByRef Iout() As Integer)

Dim lcount As Integer, L As Integer, Lprime As Integer

Dim i As Integer, j As Integer

Dim Inew(1 To Ncmax, 1 To nmax) As Integer

lcount = 0

For Lprime = 1 To Nc

For j = Iout(Lprime, k - 1) + 1 To n lcount = lcount + 1 For i = 1 To k - 1

Inew(lcount, i) = Iout(Lprime, i) Next i

Inew(lcount, k) = j Next j Next Lprime

For L = 1 To lcount For i = 1 To k

Iout(L, i) = Inew(L, i) Next i Next L

Nc = lcount End Sub

源码1.2:VBA 源码GetOutSubset()

而Markowitz()的VBA 源码则列在源码1.4里。为了要计算出Am 、Bm 同Cm ，我会用源码

SolveAxB()去计算出矩阵∑μ?1m m 及∑u ?1

m m 。

除了这些外,投资组合的比重w(1.14)可以用相同结果去计算。矩阵Δ=?L/?w (1.16)能简单地用矩阵加法和乘法算出。

Δ=

可以用相同的电子表格如图1.3及1.4，只要按下马克维茨的按,便可叫出Markowitz 的程序如下:

Sub Markowitz(n As Integer, mvector() As Double, uvector() As Double, covmatrix() As Double, meanport As Double, _ riskfree As Double, ByRef wvector() As Double, ByRef w0 As Double) Dim Nout As Integer, Nc As Integer

Dim Iout(1 To Ncmax, 1 To nmax) As Integer

Dim L As Integer, i As Integer, j As Integer, k As Integer Dim mvecm(1 To nmax) As Double Dim uvecm(1 To nmax) As Double

Dim vcmatrixm(1 To nmax, 1 To nmax) As Double Dim etavec(1 To nmax) As Double Dim tempvec1(1 To nmax) As Double Dim tempvec2(1 To nmax) As Double

Dim Am As Double, Bm As Double, Cm As Double, Dm As Double Dim lambda1 As Double, lambda2 As Double Dim testFlag As Boolean For Nout = 0 To n - 1

'generate the OUT subsets If (Nout = 0) Then Nc = 1

ElseIf (Nout = 1) Then Nc = n

For L = 1 To Nc: Iout(L, 1) = L: Next L Else

Call GetOutSubset(n, Nout, Nc, Iout) End If

For L = 1 To Nc

'construct the modified arrays For i = 1 To n

mvecm(i) = mvector(i) uvecm(i) = uvector(i)

For j = 1 To n: vcmatrixm(i, j) = covmatrix(i, j): Next j Next i

For k = 1 To Nout i = Iout(L, k) mvecm(i) = 0 uvecm(i) = 0 For j = 1 To n

vcmatrixm(i, j) = 0 vcmatrixm(j, i) = 0 Next j

vcmatrixm(i, i) = 1 Next k

'calculate Am, Bm, and Cm

Call SolveAxb(vcmatrixm, mvecm, tempvec1, n, 1, 1, 1) Call SolveAxb(vcmatrixm, uvecm, tempvec2, n, 1, 1, 1)

Am = 0

Bm = 0

Cm = 0

For i = 1 To n

Am = Am + uvecm(i) * tempvec1(i)

Bm = Bm + mvecm(i) * tempvec1(i)

Cm = Cm + uvecm(i) * tempvec2(i)

Next i

'calculate the portfolio content

Dm = Cm * riskfree ^ 2 - 2 * Am * riskfree + Bm + eps

lambda1 = (meanport - riskfree) / Dm

lambda2 = -riskfree * (meanport - riskfree) / Dm

For i = 1 To n: wvector(i) = lambda1 * tempvec1(i) + lambda2 * tempvec2(i): Next i

w0 = 1 - lambda1 * (Am - riskfree * Cm)

'check that the portfolio content are non-negative

For i = 1 To n

If (wvector(i) < -eps) Then GoTo nextL

Next i

'checking the KKT condition

For i = 1 To n

tempvec1(i) = 0

For j = 1 To n: tempvec1(i) = tempvec1(i) + covmatrix(i, j) * wvector(j): Next j

etavec(i) = tempvec1(i) - lambda1 * mvector(i) - lambda2 * uvector(i)

Next i

For i = 1 To n

testFlag = (Abs(etavec(i)) <= eps And wvector(i) > eps) Or (etavec(i) > eps And Abs(wvector(i) <= eps)) If (Not testFlag) Then GoTo nextL

Next i

GoTo exitNout

nextL: Next L

Next Nout

exitNout:

End Sub

源码1.4:VBA源码Markowitz()

Option Explicit

Private Const nmax = 10, Ncmax = 252

Private Const eps = 1 * 10 ^ -14

Sub MVO()

Dim n As Integer

Dim mvector(1 To nmax) As Double

Dim uvector(1 To nmax) As Double

Dim covmatrix(1 To nmax, 1 To nmax) As Double

Dim meanport As Double

Dim riskfree As Double

Dim wvector(1 To nmax) As Double

Dim w0 As Double

Dim i As Integer, j As Integer

n = Range("D34").Value

meanport = Range("D18").Value

riskfree = Range("D17").Value

For i = 1 To n

mvector(i) = Range("C5").Offset(i - 1)

uvector(i) = Range("B5").Offset(i - 1)

For j = 1 To n: covmatrix(i, j) = Range("C24").Offset(j - 1, i - 1): Next j

Next i

Call Markowitz(n, mvector, uvector, covmatrix, meanport, riskfree, wvector, w0)

For i = 1 To nmax: Range("F5").Offset(i - 1) = wvector(i): Next i

Range("F15").Value = w0

End Sub

源码1.5:VBA源码Markowitz()

Sub SolveAxb(Amatrix() As Double, bvec() As Double, ByRef xvec() As Double, _

n As Integer, iptr As Integer, jptr As Integer, kptr As Integer)

Dim wsAmatrix As Variant: ReDim wsAmatrix(1 To n, 1 To n)

Dim row As Integer, column As Integer

For row = 1 To n

For column = 1 To n: wsAmatrix(row, column) = Amatrix(iptr + row - 1, jptr + column - 1): Next column

Dim wsbvec As Variant: ReDim wsbvec(1 To n, 1 To 1)

For row = 1 To n: wsbvec(row, 1) = bvec(kptr + row - 1): Next row Dim wsxvec As Variant:

With Application.WorksheetFunction

wsxvec = .MMult(.MInverse(wsAmatrix), wsbvec)

End With

Dim i As Integer

If n = 1 Then

For i = kptr To kptr + n - 1: xvec(i) = wsxvec(i - kptr + 1): Next i Else

For i = kptr To kptr + n - 1: xvec(i) = wsxvec(i - kptr + 1, 1): Next i End If

End Sub

源码1.6:VBA源码SolveAxb()

第四章: 投资组合的有效边界

在前一章中的编程是以多个资产投资回报及标准偏差的计算，但多个资产组合为一个投资组合时，其投资组合的有效边界什样计算。在书中的第四章介绍一个程序- 多个风险资产投资组合的有效边界模型--直接求解法，(P.110-115) 。其编程原码如下：