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

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

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

债券分析
  • Goal Seek

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

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

终值为110时的利率水平

结语

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



刺猬偷腥
2018年1月3日


to be continued.

©著作权归作者所有,转载或内容合作请联系作者
  • 序言:七十年代末,一起剥皮案震惊了整个滨河市,随后出现的几起案子,更是在滨河造成了极大的恐慌,老刑警刘岩,带你破解...
    沈念sama阅读 212,185评论 6 493
  • 序言:滨河连续发生了三起死亡事件,死亡现场离奇诡异,居然都是意外死亡,警方通过查阅死者的电脑和手机,发现死者居然都...
    沈念sama阅读 90,445评论 3 385
  • 文/潘晓璐 我一进店门,熙熙楼的掌柜王于贵愁眉苦脸地迎上来,“玉大人,你说我怎么就摊上这事。” “怎么了?”我有些...
    开封第一讲书人阅读 157,684评论 0 348
  • 文/不坏的土叔 我叫张陵,是天一观的道长。 经常有香客问我,道长,这世上最难降的妖魔是什么? 我笑而不...
    开封第一讲书人阅读 56,564评论 1 284
  • 正文 为了忘掉前任,我火速办了婚礼,结果婚礼上,老公的妹妹穿的比我还像新娘。我一直安慰自己,他们只是感情好,可当我...
    茶点故事阅读 65,681评论 6 386
  • 文/花漫 我一把揭开白布。 她就那样静静地躺着,像睡着了一般。 火红的嫁衣衬着肌肤如雪。 梳的纹丝不乱的头发上,一...
    开封第一讲书人阅读 49,874评论 1 290
  • 那天,我揣着相机与录音,去河边找鬼。 笑死,一个胖子当着我的面吹牛,可吹牛的内容都是我干的。 我是一名探鬼主播,决...
    沈念sama阅读 39,025评论 3 408
  • 文/苍兰香墨 我猛地睁开眼,长吁一口气:“原来是场噩梦啊……” “哼!你这毒妇竟也来了?” 一声冷哼从身侧响起,我...
    开封第一讲书人阅读 37,761评论 0 268
  • 序言:老挝万荣一对情侣失踪,失踪者是张志新(化名)和其女友刘颖,没想到半个月后,有当地人在树林里发现了一具尸体,经...
    沈念sama阅读 44,217评论 1 303
  • 正文 独居荒郊野岭守林人离奇死亡,尸身上长有42处带血的脓包…… 初始之章·张勋 以下内容为张勋视角 年9月15日...
    茶点故事阅读 36,545评论 2 327
  • 正文 我和宋清朗相恋三年,在试婚纱的时候发现自己被绿了。 大学时的朋友给我发了我未婚夫和他白月光在一起吃饭的照片。...
    茶点故事阅读 38,694评论 1 341
  • 序言:一个原本活蹦乱跳的男人离奇死亡,死状恐怖,灵堂内的尸体忽然破棺而出,到底是诈尸还是另有隐情,我是刑警宁泽,带...
    沈念sama阅读 34,351评论 4 332
  • 正文 年R本政府宣布,位于F岛的核电站,受9级特大地震影响,放射性物质发生泄漏。R本人自食恶果不足惜,却给世界环境...
    茶点故事阅读 39,988评论 3 315
  • 文/蒙蒙 一、第九天 我趴在偏房一处隐蔽的房顶上张望。 院中可真热闹,春花似锦、人声如沸。这庄子的主人今日做“春日...
    开封第一讲书人阅读 30,778评论 0 21
  • 文/苍兰香墨 我抬头看了看天上的太阳。三九已至,却和暖如春,着一层夹袄步出监牢的瞬间,已是汗流浃背。 一阵脚步声响...
    开封第一讲书人阅读 32,007评论 1 266
  • 我被黑心中介骗来泰国打工, 没想到刚下飞机就差点儿被人妖公主榨干…… 1. 我叫王不留,地道东北人。 一个月前我还...
    沈念sama阅读 46,427评论 2 360
  • 正文 我出身青楼,却偏偏与公主长得像,于是被迫代替她去往敌国和亲。 传闻我的和亲对象是个残疾皇子,可洞房花烛夜当晚...
    茶点故事阅读 43,580评论 2 349

推荐阅读更多精彩内容