Tài chính doanh nghiệp - Topic 5: Advanced topics in vba/user - Defined functions for use in financial applications

1Topic: 5Advanced Topics in VBA/User-Defined Functions for Use in Financial ApplicationsFinancial ModelingL. GattisLearning ObjectiveStudents obtain the programming skills needed to write financial VBA programs which require the use of loops, conditional statements, and matrix operations23ReferencesFinancial Modeling 3rd Edition by Simon BenningaCh. 34: Array Functions and FormulasCh. 36: User Defined Functions with VBACh. 37: VBA Types and LoopsAdvanced VBA TopicsDim Statements and Option ExplicitDebuggingData Types (As Object)The use of functions in functionsFor Loops and Nested LoopsArrays and Option Base 14VBA VariablesVariables can be parameters or can be created in the programE.g.Function CAPM (rf,rm,beta)MRP = (rm-rf)CAPM = rf+beta*mrcEnd FunctionMRP is an internal variableVariables do not need to be “declared” in VBA5TypoOption Explicit and Dim StatementOPTION EXPLICITis a VBA statement that is typed at the top of a module (before functions or macros)requires internal variable by declared using a DIM statement for all module programsWhy use OPTION EXPLICIT and DIM?Makes programs easier to readMakes programs easier to debug6reset7Function TypesFunction typesYou can specify the variable type (e.g. integer) by using the reserved word “as” the default type is variant (includes all types)Why specify types?Makes program run faster and uses less memoryYour function will only return variables of that typeMakes the program code more reader-friendly if the reader knows the expected variable typeMakes debugging easier8Default TypeDebugging SuggestionsAlways know the solution to a problem before you code so you can test the programUse Option Explicit and Dim statementsUse comment lines to describe function, variables, calculations, assumptions9Functions in FunctionsYou can use functions within functionsE.g.,Makes program code more modularCan share code across programsDoes not require “application.”10Keep the (rf, rm) parameters in the same order in both programs11VBA LoopsLoop structures are used to do something repeatedly. There are many looping structures-- Do While statement (top checking; loop condition is checked before anything else gets done)Do Loop While statement (bottom checking; something is done before the condition is checked)Do Until statement (top)DoLoop Until statement (top)For Loop (used if # of iterations are known)For loops will now be demonstrated and will be used almost exclusively in this course12For Loop ExampleFunction pvalue(pmt, n, r) For i = 1 To n pvalue = pvalue + pmt / (1 + r) ^ i Next iEnd FunctionFunction pvalue1(pmt, n, r) temp = 0 For i = 1 To n temp = temp + pmt / (1 + r) ^ i Next i pvalue1 = tempEnd FunctionThe initial value of pvalue = 0pvalue then “aggregates” the PV of discounted cashflowsThis is a common method for aggregating cashflowsAlso written:If-Then-ElseThe last program computes the PV of n cash flows when the payments are at the end of the period (i.e., first payment in one year)Suppose the payments could be made at the beginning or end of the periodUse an If-Then statement to compute the cash flows either way13SyntaxIf condition Then [statements] [Else elsestatements]Or, you can use the block form syntax:If condition Then[statements][ElseIf condition-n Then[elseifstatements] [Else[elsestatements]]End If14If-Then-Else-ElseifFunction pvalue2(pmt, n, r, Endorbegin) If Endorbegin = "E" Then For i = 1 To n pvalue2 = pvalue2 + pmt / (1 + r) ^ i Next i ElseIf Endorbegin = "B" Then For i = 1 To n pvalue2 = pvalue2 + pmt / (1 + r) ^ (i - 1) Next i Else pvalue2 = "input E or B" End IfEnd Function15Does not discount the first cashflow16Using Matrix Operations and Arrays in User FunctionsThe following is a function that accepts the returns and weights arrays as parameters and calculates portfolio meanFunction portmean(Ret as Range, Wts As Range)portmean = Application.MMult(Application.Transpose(Wts), Ret)End FunctionRange is a reserved word that specifies parameter is a cell range; creates array where each item ret(i,j) can be referenced(not required in this program)Must start most excel built-in functions with “application.” in VBA functions. Not all excel functions are supported in vba17Using Matrix OperationsFunction portmean(Ret as range, Wts as range)portmean = Application.MMult(Application.Transpose(Wts), Ret)End FunctionVBA ArraysYou can obtain arrays using parameters (where the user of the function inserts the functions and selects the parameter range) or create arrays in VBAArrays in VBA use the (r,c) identifiers1819PVX FunctionFunction PVX(Cashflows As Range, r)n = Cashflows.Rows.Count For i = 1 To n PVX = PVX + Cashflows(i, 1) / (1 + r) ^ i Next iEnd FunctionPVX computes the PV of an array of cashflows in a column“rows.count” and“columns.count”are VBA objects that return the dimensions of an arrayExcel worksheet cell references and parameter ranges like this one use (r,c) indexes and start with (1,1)20VBA ArraysYou can create arrays in VBA, but they MUST be declaredIn VBA, arrays are declared using two statements.Dim arrayname () Redim arrayname (r,c) ‘where r,c get value from functionVBA arrays created in the program always start with base index of zero unless you specify a base of 1 using an option statement (parameter arrays start with index 1,1)Option Base 1 0,00,11,01,11,11,22,12,2Base 0Base 1Excel worksheet cell references and parameter ranges like this one use (r,c) indexes and start with (1,1). Arrays in VBA start with (0,1)Creating VBA Arrays (1 dimensional)Converting N prices into N-1 returnsOption Base 1Function prices2returns(prices) Dim tempmatrix() n = prices.Rows.Count ReDim tempmatrix(n - 1, 1)For i = 1 To (n - 1) tempmatrix(i, 1) = (prices(i + 1, 1) - prices(i, 1)) / prices(i, 1)Next iprices2returns = tempmatrixEnd Function21222 Dimensional Arrays – Nested Loopthe following function turns an array of “c” columns of n prices (in ascending chronological order) into an array of n-1 returns, Function prices2returns2(prices As Range) 'returns range contains returns for n-assets in n-columns Dim matrix()c = prices.Columns.Countr = prices.Rows.Count -1 ReDim matrix(r, c) For i = 1 To r For j = 1 To c matrix(i, j) = (prices(i+1, j) - prices(i, j)) / prices(i, j) Next j Next i prices2returns2 = matrixEnd Function23Test DataSelect entire range, Insert function, ctrl-shift-enter24Creating a Covariance MatrixSelect entire range, Insert function, ctrl-shift-enter to enter function25Covariance Matrix FunctionOption Base 1Function Covar_mat(returns As Range)'returns are asset periodic returns in columnsDim tempmatrix() n = returns.Columns.Count 'number of assetsReDim tempmatrix(n, n) For i = 1 To n 'row counter For j = 1 To n 'column counter tempmatrix(i, j) = Application.Covar(returns.Columns(i), returns.Columns(j)) Next j Next iCovar_mat = tempmatrixEnd Functionadd.columns(i) is an object allows calculations on an arrayNested LoopProject Explorer and Managing ModulesCommon Module/Program MistakesDuplicate function names / programsWriting VBA in Sheets (not modules) or in other MS Office modulesOption Statements not at top of module, duplicate option statements26VBA ToolkitYou may create hundreds of VBA programs in your job (or this course). But, the functions and macros can only be run from the workbook that they are inserted.Solution: create a “VBA toolkit” blank workbook that contains all your programsDocument each program with commentsLaunch all new excel projects from the toolkit, so that all of your works have access to all your programsI organize the programs by creating multiple modules (in one workbook) for options, risk, portfolio management, international, misc.27VBA TemplatesFunction name1(params)N=?????For i = 1 to N Name1=name1+ ?????Next i End Function28Aggregations LoopsOption Base 1Function name2(params)Dim tempmat () n=?????.countRedim tempmat (n,1)For i = 1 to N ‘row count Tempmat(i,1)=????Next i ‘next columnName2=tempmatEnd FunctionCreating Column VectorsSelect entire range, Insert function, ctrl-shift-enterOption Base 1Function name3(params)Dim tempmat () N=?????.countRedim tempmat (???,???)For i = 1 to ? ‘row counter For j = 1 to ? ‘column Tempmat(i,j)=???? Next j ‘next columnNext i ‘next rowName3=tempmatEnd FunctionCreating i by j MatricesSelect entire range, Insert function, ctrl-shift-enterLearning ObjectiveStudents obtain the programming skills needed to write VBA programs which require the use of loops, conditional statements, and matrix operations29