掌握Python的基本知识后,我们在编写量化策略的代码之前,可以运用excel作为策略设计的载体,这就需要了解使用excel中相关函数的方法。Excel中的函数可以分为四类,分别是数学运算类函数、统计类函数、查找类函数及其他类函数。
Excel常用的数学运算函数
- EXP(x)
求复利的PV或FV时会用到。求时间价值时,可以将1/e^(r * T) 中的e^(r * T)直接用函数EXP(r * T)表示。
- 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离)。
- SQRT(x)
求平方根
- RAND()
生成随机数,取值范围时(0~1),求蒙特卡洛模拟时会用到。EXCEL中每次按F9刷新,数字都会变。
- FACT(x)
求x的阶乘,比如FACT(3)=3x2x1。
- COMBIN(number,number_chosen)
求组合,求C6^3写成COMBIN(6,3),六个数里抽3个做组合有多少个可能性。
Excel常用的矩阵运算函数
- array相乘,sumproduct(array1,array2),excel中直接拉数即可。
一维、方向相同的数组的乘法运算: (x1,x2)(y1,y2)=x1y1+x2*y2
方向要么都是横,要么都是竖的。
- 矩阵加减
直接选中两个矩阵相加减即可,不用函数。
- 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即可。
矩阵运算在画有效前沿的时候会经常用到,所以要熟练掌握。
- TRANSPOSE(array)
返回矩阵array的转置结果,X矩阵的转置用X^T表示。
- 矩阵没有除法,但可以用MINVERSE(ARRAY)求逆矩阵。MINVERSE是指matrix inverse。
假设A是一个维数为N的方阵,有N行N列,那么N-1是N的逆矩阵,N-1N=NN^-1=I。I是单位矩阵,对角线数值为1,其他位置为0。
N^-1其实就相当于1/N,实现了变相相除。
然后验证一下两者相乘的结果:
Excel常用的统计类函数
- AVERAGE(ARRAY)
求数组的平均值,表示期望。
- STDEV(ARRAY)
求数组的标准差,表示风险。
STDEV.S表示样本标准差。
STDEV.P表示总体标准差。
- MAX(ARRAY) AND MIN(ARRAY)
求数组中的最大值和最小值。
- 用FREQUENCY函数求数据的分段频数,然后用QUARTILE函数
求第一个四分位点。
FREQUENCY函数的用法是FREQUENCY(原数据,自定义的分段界限数据),同样用CONTROL+SHIFT+ENTER进行运算。
然后Alt+=求频数的总和,再求%Freq的比例数。向下填充之前先按F4,加入$固定符进行绝对引用,然后再Control+D。
使用F4时多点几次,可以选择行固定、列固定或者行列都固定。
- 概率分布函数
正态分布函数NORMSDIST,这个函数能帮助我们已知分位点求概率。
正态分布反函数NORMSINV,这个函数能帮助我们已知累计概率求分位点,应用于风控求风险,VAR。
- 二元统计函数
用于求两个变量的相关关系。在一元回归中,能用函数方便地求出一系列参数:
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列的空间来存放运算结果。
- 垂直查找和水平查找
针对原始表格的垂直查找用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
Data Table可以做一维或二维的运算,快捷键是Alt+A+W+T。
其实就是敏感性分析,把要改变的数据手动填好,然后用data table逐一运算。
为了节省资源,可以在EXCEL选项中的公式中选择除数据表以外自动运算,这样改变原数据时,data table也不会变,可以按F9进行手动刷新。
- Goal Seek
Data TAble的反向运算,已知结果,想知道某个变量应该是多少。
用这个求期权的隐含波动率就很方便了。
设置-加载项-EXCEL加载项转到-添加分析工具库、分析工具库VBA及规划求解加载项-点确定
结语
通过以上学习,我们掌握了金融建模中常用的excel函数。下一章我们将学习VBA编程的基本知识,为创造属于自己的模型打下基础,敬请期待。
刺猬偷腥
2018年1月3日
to be continued.