Markowitz Portfolio Optimizer

(1)    The name of the VBA macro is Optimizer. Please feel free to modify the code. I have put my name in the header. This is header of the VBA code.

'Written by Satendra Tiwari. Hoping that some one would improve this code

'This code works with 4 assets but more number of assets can be added easily

File can be downloaded from here :-

http://www.4shared.com/file/CtEc0A2u/MVOptimizer.html?

(2)    This code takes 4 assets but it could be modified for more number of assets

(3)    The Inputs are (a) Minimum and maximum weight for optimization. (b) Number of points to be generated on the efficient frontier (c) Assets mean returns and standard deviations

(d) Correlation matrix

       (4)  This program accounts for “shorting of the assets” so you can optimize a given portfolio assuming that you can short some assets. Some of the weights then would be negative.

       (5) If you do not want to short any assets then put the minimum weight as 0.

      (6) This workbook has three sheets –(a) Input – contains all the input related info (2) Calculations – contains all the intermediation calculations done by the optimizer (3) Plot – shows the plot of the efficient frontier.

     (7) Also note that maximum return on the frontier is calculated as->  1.6 times max of all the means.

    (8)  As you go through the code, you will notice that -the logic is to find the global minimum and then using that minimum calculate the other points on the frontier.

    (9) This program uses MS Solver. I am also attaching the doc file to configure the solver to run this macro.

http://www.4shared.com/file/SLhVqLX7/Configure.html?

     (10) Please note that default  solver uses generalized reduced gradient method to come up with optimum values. 

  (11) Please be aware that frontier would be very sensitive to the inputs – mean and std deviations.  And in real world there would be some errors while estimating the means and std deviation (especially for  the thinly traded assets or the assets that are not traded at all)…  I think that it would be a good idea  to incorporate the capital market expectations and then simulate(Monte Carlo sims) the returns for a given level of risk. This would produce the probability distributions of the returns for a given level of risk.

Macro is password protected. Mail me at my mail id to get passwd for VBA code!

Monte Carlo Introduction - VBA code

'Written by Satendra Tiwari. Mail me at smani7dm2000@yahoo.co.in
' Refer here for the monte Carlo method
' http://www.chem.unl.edu/zeng/joy/mclab/mcintro.html

' MC Intro to calculate the value of Pi



Sub MonteCarlopi()
Dim numsims, count As Long
Dim xcord, ycord, dist, pi As Double
'Choose the number of simulations
numsims = 1000000
count = 0
For i = 1 To numsims
'Create random point in xy plane. Generate random x and y co-ordinates
xcord = 2 * Rnd() - 1
ycord = 2 * Rnd() - 1
'Calculate the distance of this point
dist = Sqr(xcord * xcord + ycord * ycord)
If dist <= 1 Then
count = count + 1
End If
Next i
'Calcualte the value of pi
pi = 4 * (count / numsims)
MsgBox "Value of pi is " + CStr(pi)

End Sub

Compare two similar database tables (VBA code)

(1) Create two user DSNs on two tables of the 2 databases or use the same DSN for two table queries
(2) Specify the table queries in cell B1 and B2
(3) Specify the name of the worksheets. These tables would be automatically filled if compare is ruing in mode 1(see CompareFunctionSetup)
(4) If compare is runnning in mode 2 then manually create two work sheets and fill them with the data to be compared.

Download Excel File(with VBA code) from here :- http://www.4shared.com/document/S61AlXme/DBTableCompareUtil.html