EXCEL金融建模

掌握Python的基本知識(shí)后，我們?cè)诰帉?xiě)量化策略的代碼之前，可以運(yùn)用excel作為策略設(shè)計(jì)的載體，這就需要了解使用excel中相關(guān)函數(shù)的方法。Excel中的函數(shù)可以分為四類，分別是數(shù)學(xué)運(yùn)算類函數(shù)、統(tǒng)計(jì)類函數(shù)、查找類函數(shù)及其他類函數(shù)。

Excel常用的數(shù)學(xué)運(yùn)算函數(shù)

1. EXP（x）

求復(fù)利的PV或FV時(shí)會(huì)用到。求時(shí)間價(jià)值時(shí)，可以將1/e^(r * T) 中的e^(r * T)直接用函數(shù)EXP(r * T)表示。

2. LN（x）

LN（x）是EXP（x）的反函數(shù)，已知PV和FV，求r或t時(shí)會(huì)用到。
比如y=e^x 中，y已知，要求x，則先取Ln，得出Ln(y)=Ln(e^x)=x。

在連續(xù)r與離散r的轉(zhuǎn)換中也會(huì)用到。比如1（1+r離）^1=1e^(r連*1)， 得出1+r離=e^r連，r連=Ln(1+r離)。

3. SQRT（x）

求平方根

4. RAND（）

生成隨機(jī)數(shù)，取值范圍時(shí)（0~1），求蒙特卡洛模擬時(shí)會(huì)用到。EXCEL中每次按F9刷新，數(shù)字都會(huì)變。

5. FACT（x）

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

6. COMBIN（number,number_chosen）

求組合，求C6^3寫(xiě)成COMBIN(6，3)，六個(gè)數(shù)里抽3個(gè)做組合有多少個(gè)可能性。

Excel常用的矩陣運(yùn)算函數(shù)

1. array相乘，sumproduct（array1,array2）,excel中直接拉數(shù)即可。

一維、方向相同的數(shù)組的乘法運(yùn)算： (x1,x2)(y1,y2)=x1y1+x2*y2
方向要么都是橫，要么都是豎的。

2. 矩陣加減

直接選中兩個(gè)矩陣相加減即可，不用函數(shù)。

3. Matrix矩陣相乘，比如三行兩列乘以兩行兩列，用MMULT(ARRAY1，ARRAY2)。

MMULT的意思是matrix multiple，括號(hào)中第一個(gè)矩陣的列數(shù)必須等于第二個(gè)矩陣的行數(shù)，否則相乘不了會(huì)報(bào)錯(cuò)。比如 3X2 and 2X2， 第一個(gè)矩陣的第一行乘以第二個(gè)矩陣的第一列，剛好兩兩對(duì)應(yīng)。A行B列乘以B行C列最終得到的結(jié)果為|AxB| X |BxC| = |AxC|，A行C列的矩陣。

多維矩陣乘法運(yùn)算

輸入好公式后要點(diǎn)control+shift+enter，才會(huì)自動(dòng)填充第二行。如果直接點(diǎn)了enter，那么按F2，顯示公式，然后再按control+shift+enter即可。

結(jié)果與手動(dòng)運(yùn)算一樣

矩陣運(yùn)算在畫(huà)有效前沿的時(shí)候會(huì)經(jīng)常用到，所以要熟練掌握。

4. TRANSPOSE（array）

返回矩陣array的轉(zhuǎn)置結(jié)果，X矩陣的轉(zhuǎn)置用X^T表示。

5. 矩陣沒(méi)有除法，但可以用MINVERSE(ARRAY)求逆矩陣。MINVERSE是指matrix inverse。

假設(shè)A是一個(gè)維數(shù)為N的方陣，有N行N列，那么N^{-1是N的逆矩陣，N}-1N=NN^-1=I。I是單位矩陣，對(duì)角線數(shù)值為1，其他位置為0。
N^-1其實(shí)就相當(dāng)于1/N，實(shí)現(xiàn)了變相相除。

3*3方陣的逆矩陣

然后驗(yàn)證一下兩者相乘的結(jié)果：

得到單位矩陣

Excel常用的統(tǒng)計(jì)類函數(shù)

1. AVERAGE(ARRAY)

求數(shù)組的平均值，表示期望。

2. STDEV(ARRAY)

求數(shù)組的標(biāo)準(zhǔn)差，表示風(fēng)險(xiǎn)。
STDEV.S表示樣本標(biāo)準(zhǔn)差。
STDEV.P表示總體標(biāo)準(zhǔn)差。

3. MAX(ARRAY) AND MIN(ARRAY)

求數(shù)組中的最大值和最小值。

4. 用FREQUENCY函數(shù)求數(shù)據(jù)的分段頻數(shù)，然后用QUARTILE函數(shù)
   求第一個(gè)四分位點(diǎn)。

FREQUENCY函數(shù)的用法是FREQUENCY（原數(shù)據(jù)，自定義的分段界限數(shù)據(jù)），同樣用CONTROL+SHIFT+ENTER進(jìn)行運(yùn)算。

頻數(shù)函數(shù)

然后Alt+=求頻數(shù)的總和，再求%Freq的比例數(shù)。向下填充之前先按F4，加入$固定符進(jìn)行絕對(duì)引用，然后再Control+D。

使用F4時(shí)多點(diǎn)幾次，可以選擇行固定、列固定或者行列都固定。

5. 概率分布函數(shù)

正態(tài)分布函數(shù)NORMSDIST，這個(gè)函數(shù)能幫助我們已知分位點(diǎn)求概率。
正態(tài)分布反函數(shù)NORMSINV，這個(gè)函數(shù)能幫助我們已知累計(jì)概率求分位點(diǎn)，應(yīng)用于風(fēng)控求風(fēng)險(xiǎn)，VAR。

6. 二元統(tǒng)計(jì)函數(shù)

用于求兩個(gè)變量的相關(guān)關(guān)系。在一元回歸中，能用函數(shù)方便地求出一系列參數(shù)：

INTERCEPT(Y.X)，求截距
SLOPE(Y,X)，求斜率
RSQ(Y,X)，求R方，X對(duì)Y的解釋力度。一元回歸方程中R方等于ρ方，即相關(guān)系數(shù)的平方。
STEYX（Y,X），stand error between y and x, 求e。
CORREL(Y,X)，相關(guān)系數(shù)ρ
COVAR（Y,X），協(xié)方差，等于ρxy方差x方差y
LINEST（Y,X）數(shù)組函數(shù)，一次性求出以上函數(shù)的結(jié)果。事先要選好一個(gè)5行2列的空間來(lái)存放運(yùn)算結(jié)果。

7. 垂直查找和水平查找

針對(duì)原始表格的垂直查找用vlookup，水平查找用hlookup。lookup的意思是在原始數(shù)據(jù)表中找到相同的值，然后把這個(gè)值以及需要提取的相對(duì)應(yīng)的值提取到新的表格中。

格式為：vlookup(lookup_value,table_array,col_index_num,range_lookup)
即：vlookup(查什么，在哪里查，返回第幾列的數(shù)據(jù)比如第三列就填3，要不要精確查找true是模糊而false是精確文本或數(shù)字連格式都一樣)

如果不用vlookup而用match，則返回的是對(duì)應(yīng)值從上到下數(shù)的第幾個(gè)數(shù)據(jù)，用match（lookup_value,lookup_array,match_type）表示，match_type可選小于，近似或大于。

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

8.邏輯函數(shù)IF

if函數(shù)可以做嵌套，判斷多種情況。

假設(shè)有一個(gè)債券，t到T之間的期間現(xiàn)金流CF為FVr，T時(shí)間點(diǎn)的CF為FV+FVr，就可以用邏輯函數(shù)分階段自動(dòng)算出各期的CF，省心又省力。

9.Excel高級(jí)工具

• 審核工具

追溯單元格數(shù)據(jù)用快捷鍵Alt+M+P，取消追溯用Alt+M+A+A
這個(gè)功能在財(cái)務(wù)建模的時(shí)候也會(huì)經(jīng)常用到。
用于檢查公式是否有誤。

直接引用的路徑

• Data Table

Data Table可以做一維或二維的運(yùn)算，快捷鍵是Alt+A+W+T。
其實(shí)就是敏感性分析，把要改變的數(shù)據(jù)手動(dòng)填好，然后用data table逐一運(yùn)算。
為了節(jié)省資源，可以在EXCEL選項(xiàng)中的公式中選擇除數(shù)據(jù)表以外自動(dòng)運(yùn)算，這樣改變?cè)瓟?shù)據(jù)時(shí)，data table也不會(huì)變，可以按F9進(jìn)行手動(dòng)刷新。

債券分析

• Goal Seek

Data TAble的反向運(yùn)算，已知結(jié)果，想知道某個(gè)變量應(yīng)該是多少。
用這個(gè)求期權(quán)的隱含波動(dòng)率就很方便了。

設(shè)置-加載項(xiàng)-EXCEL加載項(xiàng)轉(zhuǎn)到-添加分析工具庫(kù)、分析工具庫(kù)VBA及規(guī)劃求解加載項(xiàng)-點(diǎn)確定

終值為110時(shí)的利率水平

結(jié)語(yǔ)

通過(guò)以上學(xué)習(xí)，我們掌握了金融建模中常用的excel函數(shù)。下一章我們將學(xué)習(xí)VBA編程的基本知識(shí)，為創(chuàng)造屬于自己的模型打下基礎(chǔ)，敬請(qǐng)期待。

刺猬偷腥
2018年1月3日

to be continued.