刺猬教你量化投资

刺猬教你量化投资(八):金融建模中的Excel函数

2018-01-03  本文已影响82人  刺猬偷腥
EXCEL金融建模

掌握Python的基本知识后，我们在编写量化策略的代码之前，可以运用excel作为策略设计的载体，这就需要了解使用excel中相关函数的方法。Excel中的函数可以分为四类，分别是数学运算类函数、统计类函数、查找类函数及其他类函数。

Excel常用的数学运算函数

  1. EXP（x）

求复利的PV或FV时会用到。求时间价值时，可以将1/e^(r * T) 中的e^(r * T)直接用函数EXP(r * T)表示。

  1. LN（x）

LN（x）是EXP（x）的反函数，已知PV和FV，求r或t时会用到。
比如y=e^x 中，y已知，要求x，则先取Ln，得出Ln(y)=Ln(e^x)=x。

在连续r与离散r的转换中也会用到。比如1（1+r离）^1=1e^(r连*1)， 得出1+r离=e^r连，r连=Ln(1+r离)。

  1. SQRT（x）

求平方根

  1. RAND（）

生成随机数，取值范围时（0~1），求蒙特卡洛模拟时会用到。EXCEL中每次按F9刷新，数字都会变。

  1. FACT（x）

求x的阶乘，比如FACT（3）=3x2x1。

  1. COMBIN（number,number_chosen）

求组合，求C6^3写成COMBIN(6，3)，六个数里抽3个做组合有多少个可能性。

Excel常用的矩阵运算函数

  1. array相乘，sumproduct（array1,array2）,excel中直接拉数即可。

一维、方向相同的数组的乘法运算： (x1,x2)(y1,y2)=x1y1+x2*y2
方向要么都是横，要么都是竖的。

  1. 矩阵加减

直接选中两个矩阵相加减即可，不用函数。

  1. Matrix矩阵相乘，比如三行两列乘以两行两列，用MMULT(ARRAY1，ARRAY2)。

MMULT的意思是matrix multiple，括号中第一个矩阵的列数必须等于第二个矩阵的行数，否则相乘不了会报错。比如 3X2 and 2X2， 第一个矩阵的第一行乘以第二个矩阵的第一列，刚好两两对应。A行B列乘以B行C列最终得到的结果为|AxB| X |BxC| = |AxC|，A行C列的矩阵。

多维矩阵乘法运算

输入好公式后要点control+shift+enter，才会自动填充第二行。如果直接点了enter，那么按F2，显示公式，然后再按control+shift+enter即可。

结果与手动运算一样

矩阵运算在画有效前沿的时候会经常用到，所以要熟练掌握。

  1. TRANSPOSE（array）

返回矩阵array的转置结果，X矩阵的转置用X^T表示。

  1. 矩阵没有除法，但可以用MINVERSE(ARRAY)求逆矩阵。MINVERSE是指matrix inverse。

假设A是一个维数为N的方阵，有N行N列，那么N-1是N的逆矩阵，N-1N=NN^-1=I。I是单位矩阵，对角线数值为1，其他位置为0。
N^-1其实就相当于1/N，实现了变相相除。

3*3方阵的逆矩阵

然后验证一下两者相乘的结果：


得到单位矩阵

Excel常用的统计类函数

  1. AVERAGE(ARRAY)

求数组的平均值，表示期望。

  1. STDEV(ARRAY)

求数组的标准差，表示风险。
STDEV.S表示样本标准差。
STDEV.P表示总体标准差。

  1. MAX(ARRAY) AND MIN(ARRAY)

求数组中的最大值和最小值。

  1. 用FREQUENCY函数求数据的分段频数，然后用QUARTILE函数
    求第一个四分位点。

FREQUENCY函数的用法是FREQUENCY（原数据，自定义的分段界限数据），同样用CONTROL+SHIFT+ENTER进行运算。

频数函数

然后Alt+=求频数的总和，再求%Freq的比例数。向下填充之前先按F4，加入$固定符进行绝对引用，然后再Control+D。

使用F4时多点几次，可以选择行固定、列固定或者行列都固定。

  1. 概率分布函数

正态分布函数NORMSDIST，这个函数能帮助我们已知分位点求概率。
正态分布反函数NORMSINV，这个函数能帮助我们已知累计概率求分位点，应用于风控求风险，VAR。

  1. 二元统计函数

用于求两个变量的相关关系。在一元回归中，能用函数方便地求出一系列参数：

INTERCEPT(Y.X)，求截距
SLOPE(Y,X)，求斜率
RSQ(Y,X)，求R方，X对Y的解释力度。一元回归方程中R方等于ρ方，即相关系数的平方。
STEYX（Y,X），stand error between y and x, 求e。
CORREL(Y,X)，相关系数ρ
COVAR（Y,X），协方差，等于ρxy方差x方差y
LINEST（Y,X）数组函数，一次性求出以上函数的结果。事先要选好一个5行2列的空间来存放运算结果。

  1. 垂直查找和水平查找

针对原始表格的垂直查找用vlookup，水平查找用hlookup。lookup的意思是在原始数据表中找到相同的值，然后把这个值以及需要提取的相对应的值提取到新的表格中。

格式为：vlookup(lookup_value,table_array,col_index_num,range_lookup)
即：vlookup(查什么，在哪里查，返回第几列的数据比如第三列就填3，要不要精确查找true是模糊而false是精确文本或数字连格式都一样)

如果不用vlookup而用match，则返回的是对应值从上到下数的第几个数据，用match（lookup_value,lookup_array,match_type）表示，match_type可选小于，近似或大于。

与match相反的是，index()返回的是第几行第几列的值，用index(array,row_num,column_num)表示。

8.逻辑函数IF

if函数可以做嵌套，判断多种情况。

假设有一个债券，t到T之间的期间现金流CF为FVr，T时间点的CF为FV+FVr，就可以用逻辑函数分阶段自动算出各期的CF，省心又省力。

9.Excel高级工具

追溯单元格数据用快捷键Alt+M+P，取消追溯用Alt+M+A+A
这个功能在财务建模的时候也会经常用到。
用于检查公式是否有误。

直接引用的路径

Data Table可以做一维或二维的运算，快捷键是Alt+A+W+T。
其实就是敏感性分析，把要改变的数据手动填好，然后用data table逐一运算。
为了节省资源，可以在EXCEL选项中的公式中选择除数据表以外自动运算，这样改变原数据时，data table也不会变，可以按F9进行手动刷新。

债券分析

Data TAble的反向运算，已知结果，想知道某个变量应该是多少。
用这个求期权的隐含波动率就很方便了。

设置-加载项-EXCEL加载项转到-添加分析工具库、分析工具库VBA及规划求解加载项-点确定

终值为110时的利率水平

结语

通过以上学习，我们掌握了金融建模中常用的excel函数。下一章我们将学习VBA编程的基本知识，为创造属于自己的模型打下基础，敬请期待。


刺猬偷腥
2018年1月3日

to be continued.

上一篇下一篇

猜你喜欢

热点阅读