悉尼大学金融建模课件FINC6019_Lecture_5.pptVIP

The Constrained Frontier For a small number of data points, you could repeat the previous procedure for new choices of c to draw the entire frontier For a large number of points, this would become a very tedious task This task may be repeated with a VBA program This is the most efficient way of performing such tasks Sub Solve() solverok setcell:=$B$18, maxminval:=1, valueof:=0, bychange:=$B$10:$B$13 solversolve userfinish:=True End Sub Sub Doit() Range($J$4:$P$43).ClearContents For counter = 1 To 40 Range($C$8) = -0.04 + counter * 0.005 Solve Range($J$4:$P$43).Cells(counter, 1) = ActiveSheet.Range($C$8) Range($J$4:$P$43).Cells(counter, 2) = ActiveSheet.Range($B$17) Range($J$4:$P$43).Cells(counter, 3) = ActiveSheet.Range($B$16) Range($J$4:$P$43).Cells(counter, 4) = ActiveSheet.Range($B$10) Range($J$4:$P$43).Cells(counter, 5) = ActiveSheet.Range($B$11) Range($J$4:$P$43).Cells(counter, 6) = ActiveSheet.Range($B$12) Range($J$4:$P$43).Cells(counter, 7) = ActiveSheet.Range($B$13) Next counter End Sub The Constrained Frontier - VBA The Constrained Frontier - VBA The code on the previous slides consisted of two subroutines (lists of instructions) First, the subroutine “solve” puts the appropriate entries into the solver dialogue box and then executes solver The second subroutine “doit” runs through 40 different values of c, optimises the portfolio (by calling “solve”) then places the results for that particular optimisation in a results section Constrained Frontier - VBA Adding a Solver Reference If the previous VBA code doesn’t execute with an error relating to solver, it is likely that it doesn’t have a reference to it To add a reference, go the the VBA editor (“alt+F11”) Go to “Tools”, “References” then select the solver reference You may have to find it manually, usually located in c:\program files\microsoft office\OFFICE11\library\solver Constrained Frontier The frontier we generated only constrained short sales Other constraints (i.e. lower and upper