VBA: Short Lecture Notes

Steps/Pointers for doing your code:

• Outline what you need to do in little modules.
• For each major procedure make an outline
• Try out the procedures on the spreadsheet 
•  Automate using the macro 
• Clean up the macro and do your editing. (Doing back engineering)
• Put your procedures together
•   Put your favourite codes together
• Stress test your code
• When in doubt, Use Help.

Some Clarifications

• Procedure: This is a basic set of codes that gets executed: Sub and End sub
•  Functions: creates a dynamic output: Function and End Function
• Modules: see all of your code

Variables: Common Data Type

• It must really be defined.
• Common Data Types
• Integers
• Long
• Single
• Double
• Decimal
• Date
• Object
• String - text
• Variant – any kind
• String x integer = error, so define the variables beforehand
• It entails dimensioning
• Data type Summary on VBA Help for more information

Declaring Variables 

• Code: Dim a as Integer, b as integer
• Declaring arrays
• Dim histdata(1 to 100) as double - it has 100 items
• Dim matrix(100,100) – it has 101 x 101 items

Looping

• For next
• For each next -
• Do while Loop – continue running until true
• Do until 
• Do – can endlessly run

If then Statements

     If a<b 

       Variable = 0

     Else
       Variable = 1
     End if
  

 Go to statement

• Changes the flow of the procedure
• BEST used for error trapping
• Abruptly change the code..
• Example: if variable = 1000, go to 170 (which is a statement outside the loop)
• Best used for error trapping

Case Constructions

• Works like if then statement, very handy for multiple cases

      Select case statement
         Case is <.5
            Procedure for case
         Case else
            Procedure

      End Select
                           
Visual programming - Forms

•  Codes are built around Forms
• In forms event are activated
• Left mouse click
• Click
• Enter range of values
• Working around the form: example: where you can capture the data at outputs the regression
• Form contain objects such as references, command button etc.
• Build form on arrays

Making Add-ins in excel

VBA: Intro to Userforms

Tips and Pointers:

1. Creating a new form: On the Insert Menu, Click Userform.
2. You can change name or label of userform in its Properties section. 
3. A form that choose a matrix or data from excel sheet: RefEdit in Toolbox. If there is no RefEdit, Right click on ToolBox--> Additional Controls --> Check refedit.ctrl --> Ok.
4. Double click on the object itself to view Code. OR On the Project Box, Forms folder, Right click on the form and select View Code.
5. To get the range in refedit: NewName = Range(RefeditName.Text)
6. To run the userform:  FormName.show
7. To end the userform: Unload Me

Additional New Codes:

1. Sheets.Add => adding new Sheets
2. Range("A5").Activate => activating the range

Example Process:

Given two matrices of unknown dimensions, we want to multiply them. 

1. Using the Button "Multiply!" in Excel, it opens up the Userform for Matrix Multiplication.

2. Select the two Matrix to multiply.

3. Click Ok, and then the answers will appear in a new sheet.

4. If wrong dimensions, a msgBox will appear.

5.Clicking the Cancel Button will end the Userform.

Code: userform mmult.xlsm

Sub Macro1()

    'For Button Multiply!

    MatrixMult.Show

End Sub

Private Sub CancelBut1_Click()

    Unload Me

End Sub

Private Sub OkBut1_Click()

    MatrixA = Range(mA.Text)

    MatrixB = Range(mB.Text)

    

    'Checker

    If UBound(MatrixA) = UBound(MatrixB) And UBound(MatrixA, 2) = UBound(MatrixB, 2) Then

        'Start of MMult

        r = UBound(MatrixA)

        c = UBound(MatrixA, 2)

    

        Sheets.Add

        ActiveCell.Range("A1").Select

        ActiveCell.Range(Cells(1, 1), Cells(r, c)) = Application.WorksheetFunction.MMult(MatrixA, MatrixB)

    Else

        MsgBox "Wrong dimensions of matrices"

    End If

    

End Sub

VBA: Autoregression and OLS in functions

The code calls for the range of the original series in one cell and the number of lags to the other cell.  It is assumed that the range of the original series is correct.(Meaning there is no checker for the input of the orig series, but for the lag, there is..) Then we wanted to do autoregression only with this given data.

The AR Function only calls for the original prices and the number of lags we wanted. Inside that function, the y and the x values are created. And then we throw those values to the OLS function where the regression is made.

The function OLS  y and x values as inputs. Since y is now a matrix, we are left with creating the x matrix in such a way that the first columns are 1's. The output would be the betas and coefficient of the model.

It was done as such, so that the OLS(y,x) can function independently. AR(orig,lag) however relies to the OLS function as to how I understood the concept.

The code is written below. It can also be seen in AR functions.xlsm


Sub test()

 'calling for the range and lag values
    Dim ran As String
    ran = Range("J3")
    lag = Range("J4")
    
    If lag < 1 Or lag Like "[A-Z,a-z]" Then MsgBox "Wrong Lag"

    orig = Range(ran)
    
    'output in the excel
    Range("H6") = "b="
    Range("I6").Select
    ActiveCell.Range(Cells(1, 1), Cells(lag + 1, 1)) = AR(orig, lag)

End Sub

Function AR(orig, lag)
    'given original series and number of lag, we apply AR
   
    'creating y values
    r = UBound(orig) - lag
    y = Range(Cells(1, 1), Cells(r, 1))
    For i = 1 To r
        y(i, 1) = orig(i + lag, 1)
    Next i
    
    'creating x values
    x1 = Range(Cells(1, 1), Cells(r, lag))
    
    For j = 1 To lag
        For i = 1 To r
              x1(i, j) = orig(i + lag - j, 1)
        Next i
    Next j

    AR = OLS(y, x1)
    
End Function

Function OLS(y, x1)
 'regression, getting betas and coefficient
 'given values y and x (not the matrix x)

    'adding ones to for coefficients in x matrix
    x = Range(Cells(1, 1), Cells(UBound(x1), UBound(x1, 2) + 1))
    For j = 1 To UBound(x, 2)
        For i = 1 To UBound(x)
            If j = 1 Then
                x(i, j) = 1
            Else
                x(i, j) = x1(i, j - 1)
            End If
        Next i
    Next j
    
    xtrans = Application.WorksheetFunction.Transpose(x)
    xtx = Application.WorksheetFunction.MMult(xtrans, x)
    xtxinv = Application.WorksheetFunction.MInverse(xtx)
    xtxinvxt = Application.WorksheetFunction.MMult(xtxinv, xtrans)
    bsol = Application.WorksheetFunction.MMult(xtxinvxt, y)
    OLS = bsol
    
End Function

VBA: Autoregression

The code calls for the range of the original series, and the number of lags we wanted for the autoregression.  then OLS in matrix form is performed with the output of the betas and coefficient of the model. Again this is not dynamic

And oh..I'm getting the used to adding watches and stops.

Sub regression()

    'calling for fixed range and lag values
    orig = Range("B3:B100")
    lag = 2
    
    r = Range("B3:B100").Rows.Count - lag
    y = Range(Cells(1, 1), Cells(r, 1))
    For i = 1 To r
        y(i, 1) = orig(i + lag, 1)
    Next i
    
    'creating x range
    c = lag + 1
    x = Range(Cells(1, 1), Cells(r, c))
    
    'individual, not dynamic
    'For i1 = 1 To r
    '    x(i1, 1) = 1
    'Next i1
    
    'For i2 = 1 To r
    '    x(i2, 2) = orig(i2 + 1, 1)
    'Next i2
    
    'For i3 = 1 To r
    '    x(i3, 3) = orig(i3 + 0, 1)
    'Next i3
    
    For j = 1 To c
        For i = 1 To r
              x(i, j) = orig(i + c - j, 1)
              If j = 1 Then x(i, j) = 1
        Next i
    Next j
    
    'Range("i7").Select
    'ActiveCell.Range(Cells(1, 1), Cells(r, 1)) = y
    'Range("j7").Select
    'ActiveCell.Range(Cells(1, 1), Cells(r, c)) = x
    
    'regression, getting b solution
    xtrans = Application.WorksheetFunction.Transpose(x)
    xtx = Application.WorksheetFunction.MMult(xtrans, x)
    xtxinv = Application.WorksheetFunction.MInverse(xtx)
    xtxinvxt = Application.WorksheetFunction.MMult(xtxinv, xtrans)
    bsol = Application.WorksheetFunction.MMult(xtxinvxt, y)

    'output in the excel
    Range("H6") = "b="
    Range("I6").Select
    ActiveCell.Range(Cells(1, 1), Cells(c, 1)) = bsol
    
End Sub

Autoregression Sample

Sample Auto regression of Stock returns in Excel

Recall: 
\[AR(2): y_t = b_0+b_1y_{t-1}+b_2y_{t-2}+ \text{error}\]
Process in Excel. (pls see autoregression.xlsm)

1. Since we want an AR(2), we first get the lag1 and lag2 of the original stock return as shown below.

2. From the Data Ribbon, click Data Analysis on the left side of the Ribbon.

3. Choose Regression, then click OK.

4. For the Input Y Range, choose the original series.

5. For the Input X Range, choose the lag series.

6. Remember to only choose the number of observations that both Y and X have. Unfilled Cells are disregarded. That means for the Y series, we chose B5:B100 and for the X series we chose C5:D100.

7. Click OK.

Results and Interpretation:

With 96 observations, the model resulted to an AR(2) with the linear combination of
\[ y_t = 0.00679131-0.0066908y_{t-1}-0.0486791y_{t-2}+ \text{error}\]
However, The F Statistics shows us that the value is greater than alpha of 0.05. It implies that the model does not fit to the data given. Moreover, the Adjusted $R^2=-0.01$ which implies that only -0.1% of the variance was explained.  Furthermore, the betas and the coefficient are not significant as their p-values are greater than alpha of 0.05. These leads us to the analysis that even though we got the model, it is not a good model to begin with. At least we know now how to do regression in Excel. :-)

Time Series Analysis

Trend Line - OLS

•  It has four major assumptions
• line
• normal distribution
• equal variance
• serial correlation (looking at the area of volatility)
• if not linear - transformation should occur. and anything transformed should revert it back to the original.

Autoregression

• regressing data against itself  - lags (for an example of how to in excel, see autoregression.xlsm)
• Sample model: $AR(2): y_t = b_0+b_1y_{t-1}+b_2y_{t-2}+ \text{error}$

Moving Average

• Sample model: $MA(2): y_t = e_0 +a_1e_{t-1}+a_2e_{t-2}$
• the $e$'s are the errors of an internal model.

ARMA

• Combination of both AR and MA Model
• Sample model: $ARMA(2,2): y_t =b_0+b_1y_{t-1}+b_2y_{t-2}+ e_0 +a_1e_{t-1}+a_2e_{t-2}$
• $AR(1):  y_t =b_0+b_1y_{t-1}+\text{error} $
• $b_1 <1$ - no problem. It stays stationary even if there are shocks.
• $b_1=1$ - shocks has permanent effect in the time series model.
• $b_1>1$ - not stationary. It explodes or decays.
• Covariance stationary
• mean is constant over time
• variance and covariance is constant over time
• $MA(1): y_t = e_0 +a_1e_{t-1}$
• the errors are artifacts of another model and its always brand new.
• Box-Jenkins
• AR
• ACF - decay to 0
• PACF -drops to 0
• MA
• ACF - drops to 0
• ACF - decay to 0
• Parsimony - important, how good is the forecast?
• AIC and BIC - choosing the model with the lowest value
• Random walk
• Model: $y_t = y_{t-1} + e$
• if the first differences is a white noise process.

Integrated Model

• If we difference it twice, we must go back twice after.
• ARIMA(p, d, q) model - AR(p), integration at d, MA(q)

Business Modelling

• MA - Kathang isip lamang because of the errors
• AR - is based on observable values
• Rule of thumb - 50 observations

Random walk with Drift

• An AR model:  $AR(2): y_t = b_0+b_1y_{t-1}+b_2y_{t-2}+ \text{error}$
• it is a random walk with trend

Mean Reversion

• MPT Application: Variance = $w^T \sigma w$ (sensitivity analysis on variancesensitivity.xlsx)
• must be homoscedastic
• not work on negatives

GARCH

• Conditional Heteroscedasticity applies
• Model:  $GARCH(1,1): h_t = \alpha_0+\alpha_1r_{t-1}^2+\beta_1h_{t-1}$

EWMA

CRR Tree

Since we are assigned to report on the CRR tree, our group made a powerpoint presentation on this. It includes the basics of a CRR tree.

Crr presentation from naojan

Notes: CRR presentation.pptx, CRRBOPMv2.xlsx

Jarrow-Rudd Tree (JR) tree is assumes 50% probability in the u and d. CRR don't.

Slide 6 

Step 1. Binomial model acts similarly to the asset that exists in a risk neutral world.

\[ pu+qd = e^{i * \Delta t} = r\]

where

\[\begin{split}
\Delta t &= \frac{t}{n} \\
t &= \text{term of the option} \\
n &= \text{number of periods}
\end{split}\]

Its variance: 

 \[ pu^2 + qd^2 – e^{(i* \Delta t)^2} = \sigma^2 \Delta t \]

Slide 9 - Notice that the lattice is symmetrical, that is due to the assumption that d=1/u (ud=1). Thus , it is easier to program since it involves fewer steps.

References:

• Cox, J.C., Ross S.A, Rubinstein, M., Option Pricing : A Simplified Approach. (1979). Published in Journal of Finance and Economics
• Watsham, Terry J., and Parramore, Keith. Quantitative Methods in Finance. (1997)
• http://investexcel.net/736/binomial-option-pricing-excel/
• http://www.sitmo.com/article/binomial-and-trinomial-trees/
• http://en.wikipedia.org/wiki/Binomial_options_pricing_model
• http://sfb649.wiwi.hu-berlin.de/fedc_homepage/xplore/tutorials/xlghtmlnode63.html#bin-fig2
• http://www.terry.uga.edu/~mayhew/Old/chapter9.pdf
• Other books:
• Hull book 
• Paul Wilmott
• Advance Modelling in Finance Using Excel and VBA

Individual Project Ideas

Multivariate Analysis

• Multivariate Regression 
• 4 tests of assumptions (normality, homoskedasticity,  line, serial correlation or area of volatility)
• pros: topic is easy to understand  
• cons: familiar with Excel, Stata or Gretl, not practical to use in the near future

Time Series

• AR and MA models
• cons: familiar with Stata and Gretl

Portfolio Optimization: Non Linear LP

• involving numerical methods for managing risks and return  
• pro: very user specific (Objective functions and constraints)
• cons: challenging to conceptualize through code

Portfolio Optimization: PCA and Factor Analysis

• for managing risks and return, groupings for regression  
• pro: familiar with the coding except for the eigenvalues
• cons: familiar with other statistical software, not practical for long term use

CRR vs BS functions

• CRR and BS for option pricing
• pros: biased over this, will use in the near future
• cons: have to review in detail

Monte Carlo Simulation: Option Pricing

•  
• pro: a fun idea
• cons: 

PCA and FA II

As the book by watsham tells us, PCA has two objectives:

• to reduce the dimensionality
• to interpret data.

Now I got confused with the reducing the dimensionality. As far as i know, it can only reduce linear combinations as non significant proportions of variability are eliminated. So I looked it up in myriad sources and found how can it actually reduce the dimensions.

This is how I got enlightened:

Let's say I have 100 stocks returns in an index and wanted to reduce or squeeze it into two or three variables. Since I have 100 stock returns, then I have also 100 principal components. The first of which (who has the highest eigenvalue) accounts for the highest variance and the 100th principal component accounts for the least as it is already ranked.  Note that what PCA do is it makes the principal components uncorrelated to each other. That means that even if the returns (input) itself is highly correlated as assumed, the principal components(output) which explains the variance are not.

There are two ways to do PCA. First is the eigenvalue decomposition (in the previous posts) and the other is  the singular value decomposition (still have to to research on this). Once we figure out the PC's that explain the majority of the variance, those PC's can serve as dependent variables in a regression. 

How is it different to factor analysis?

Even though PCAs appears to have "mathematically intuition", it is somehow lacks to have the "economic intuition". As I have understood, the PCA is only a subset of FA. FA group together variables according to its variability and in assumption that those variables are somewhat related or show characteristics that are the same.

Sources:
CAPM vs APT:
http://www.r-bloggers.com/principal-component-analysis-use-extended-to-financial-economics-part-2/

http://programming-r-pro-bro.blogspot.com/2011/10/principal-component-analysis-use.html

PCA and FA I

Principal Components Analysis 

    - answers the question " what are the major contributors to the overall variability?"
    - so we look at the "dependent" variables and analyze them

Sample Application:

• Age Groups as variable - who among the age group contributes more. 
• What moves the bond return the most? 
• First, we make a lot of guesses on the x's variables such as:
• tenor
• coupon rate
• yield
• credit rating
• duration
• demand for the bond
• inflation expectation
• supply
• money supply
• political stability
• GDP expectation
• Next, we eliminate the some of the variables based on redundancy, data, etc.
• credit ratings are all risk free (means 0 variance), so we eliminate it.
• demand and supply are related. It can be considered as one. 
• political stability is not measurable.
• Then, we determine the data for the other variables as they are measurable
• tenor
• coupon rate
• yield
• duration
• demand and supply for the bond - bid ask spread
• inflation expectation - BSP futures curve or consensus estimate for GDP
• money supply - entry growth BSP
• GDP expectation 
• we now can apply PCA to the variables

Applying PCA to the x variables

• From the variance-covariance matrix, we get the correlation matrix. It is so because the data needs to be normalized first and we are able to achieve it by dividing the data by the standard deviation. And correlation matrix is the standardized form of the variance-covariance matrix.
• Then get eigenvectors and the eigenvalues.
• Eigenvectors - produce the matrix of linear combinations 
• Eigenvalues - its total sum is the total number of variables in the standardized matrix
• each eigenvector (linear combination) has its own eigenvalue 
• Interpretation of the eigenvectors and the eigenvalues
• divide the each eigenvalue by its sum gives us the proportion that eigenvector accounts to the total variance. The higher the proportion the more significant its contribution to the total variability. 
• Some says that getting eigenvalues greater than 1 are the ones that are significant. So we disregard the ones that are less than one since they are not significant.
• As application in portfolio management, this gives the portfolio manager the opportunity to adjust the weights of the portfolio components so as to reduce the element of risk.

  Factor Analysis

• main focus: summarization and data reduction
• Given the variables, it answers the question "What drives the variables?"
• this analysis is an ends by itself  or can be a part of a process
• Stock = A Factor1 + B Factor2 + C Factor3, where the factors are the groups.

Example: 

Given stocks 1-10, (in the dummy stocks excel, and quant prof) we apply factor analysis. First we get the correlation matrix from group statistics, and then apply factor analysis with the correlation matrix.

From the unrotated loadings, we determine each group or factor by ranking as shown on the example below.

There are other ways to get the factor analysis. What is important is that we know how to analyze the data given.

This can be one of the individual projects. can also be ANOVA, test of hypothesis, etc.

PCA and FA application - factoranalysis.xlsx

Consider the linear combinations of 3 factors given below. We assumed that there are 3 factors affecting stockA, B and C.

For Problem1, we want to find the weights to invest in stock A,B and C, considering equal returns for the three stocks. For Problem2, we want to find the weights to invest in stock A,B and C, considering only Stock C returns. This is called one factor analysis. For Problem3, we want to find the weights to invest in stock A,B and C, considering only Stock A and half of Stock C returns. The problem is summarized on the table below.

 Solution:

To solve for the weights, we used the "matrix OLS" as a solution.
\[\hat{y} = \hat{x}\hat{b} \to \hat{b} = \hat{x}^{-1}\hat{y}\]

 In this case $\hat{y}$ is our expected returns, $\hat{b}$ is our weights and \hat{x} is the factors affecting the returns.

With problem 1, we applied multi-factor sensitivity as all of the factors and stocks are considered. After solving for the weights, we have to buy 47% of stock A, sell 77% of stock B and buy 145% of Stock C. Also it is found that we have to borrow 15% since the total sum of the weights is 115%.

Problem 2 only considered Stock C. When we do this, one factor sensitivity is used. As a result in finding for the weights, we have to sell 145% and 174% for stocks A and C respectively. (Can we sell what we don't have??) The total sum of the weights are 9% meaning it is not fully allocated.
 

Problem 3 considers a return for Stock A and half of Stock C only. This results to allocating 60%, 130% and -134% to the stocks A, B and C respectively. Total sum of the weights is 55% which pertains to the total allocation.

Sources: ppt class presentations, class lectures and quantprof VBA

PCA and FA Intro

Difference between PCA and FA to Others

Recall:

Multiple Regression (MR): relationship between an exogenous variable and many endogenous variable

Cointegration: endogenous variables are stationary (means the joint probability distribution does not shift through time and space, mean and variance remain constant throughout time and position)

Mean-Variance Analysis: measures the total collective variability of a group of variables, without specifically identifying which subgroups contribute to that variability

PCA and FA

• compared to MR examines only the endogenous variables
• compared to Cointegration it may not need to be stationary
• compared to Mean-Variance Analysis, PCA identify and rank subgroups and their contribution to the total variability
• Both uses variance-covariance matrix

Characteristics of Mutivariate Statistics

• Volatility in the multivariate structure
• Correlation or colinearity between variables

Principal Components Analysis

- volatility of multivariate structure is measured and analyzed

- total variability is measured by the sum of the eigenvalues (sum of the diagonals in the matrix)

Factor Analysis

- correlation between the variables of  a multivariate structure is analyzed

Sources: Quantitative Methods in Finance by Watsham and Parramore

Portfolio Optimization Problems

Portfolio Optimization is a way to determine the weights to be allocated in each investment so that the amount of expected return and the level of risk optimally meets the investor's objective. To do that, we can either maximize the returns subject to the maximum level of risk or minimize the risk subject to the acceptable level of expected return.

LP Application

From the book, the applications of LP and Non LP in Finance involves portfolio selection. The construction of the portfolios is within the framework of CAPM and details can be found in the book as to how it was derived. It can maximize the expected return while controlling systematic risk.

Sample: Given 3 assets portfolio
\[ \text{Max (return)} = 0.11W_a +0.15W_b+0.08W_c\]
subject to:

1. $W_a+1.2W_b+0.9W_c \le 1.1$
2. $0 \le W_a, W_b, W_c \le 1$
3. $W_a + W_b + W_c = 1$

Explanation:

• The expected return of the individual assets A, B and C are 0.11, 0.15 and 0.08 respectively.
• Constraint 1 tell us that the individual risks $\beta_a$, $\beta_b$ and $\beta_c$ for the assets are 1, 1.2 and 0.9 respectively. It also tells us that the portfolio risk or $\beta$ must not exceed 1.1.
• Constraint 2 tell us that all assets must have non negative weights
• Constraint 3 tells us that the funds must be fully invested for sum of the weights is equal to 1.

This can be solved using Graphical Method or the Simplex Method. Consequently, the objective function and constraints can still be reduced into much simpler form when $W_c=1-W_a-W_b$ is substituted to each of the equations.

\[ \text{Max (return)} =0.03W_a+0.07W_b+0.8\]
subject to:

1. $0.1W_a+0.3W_b = 0.2$
2. $ W_a + W_b = 1$

Note that the individual expected returns and asset \betas are fixed subject to portfolio manager point of view because they are determined by the market. Weightings on the other hand are variable and at the discretion of the portfolio manager as it can be adjusted in order to achieve the objective. Therefore, the portfolio return and the portfolio risk can be engineered by the portfolio manager by adjusting the proportions of each asset in the portfolio.

Non LP Application

Investor requirements usually constrain the selection process. Thus, they can require that the risk to be minimized subject to the greater or acceptable level of expected return. Since the risk is a quadratic function of the variance of the security returns, a non LP optimization is needed. In mathematical terms, three asset portfolio optimization can be formally written as
\[ \text{Min} Z = W^T V W \]
subject to

1. $W_a+W_b+W_c = 1$
2. $W_aE(r_a)+W_bE(r_b)+W_cE(r_c) \ge R$

The objective function may look familiar as it is the variance of the portfolio. We are after all minimizing risk. The first constraint tells us that we want the assets to be fully invested. And the second constraint explains that we want the portfolio return to be greater than or equal to the minimum level of expected return.

Sample: Given a three asset portfolio whose
\[ V = \begin{bmatrix}
0.00015 & 0.0005 & -0.00007 \\
0.00005 & 0.00025 & -0.00003 \\
-0.0007 & 0.00003 & 0.00010
\end{bmatrix}\]
then, we have

\[ \text{Min} Z = W^T V W \]
subject to

1. $W_a+W_b+W_c = 1$
2. $0.11W_a+0.15W_b+0.08W_c \ge 0.11$

Here, we assume that the negative positions in each assets are not possible. We can also transform the OF function into

\[ \begin{align} \text{Min} Z &= 0.00015W_a^2 + 0.00025W_b^2+0.00010W_c^2\\
 &+0.00010W_aW_b-0.00014W_aW_c-0.0006W_bW_c \end{align}\]
subject to

1. $W_a+W_b+W_c = 1$
2. $0.11W_a+0.15W_b+0.08W_c \ge 0.11$

Source: Quantitative Methods in Finance by Watsham and Parramore

LP and Non-LP Issues

Linear Programming

• Mathematical method used for optimization problems
• Main applications are to maximize profits and minimize costs
• Objective function is linear when:
• Optimum always attained at constraint boundaries
• A local optimum is also global optinum
• Other applications
• operational research - solve multi-commodity flow issues and network flow
• strategic games - chess, manufacturing, resource conservation, transportation systems for buses and trains, and military budgeting
• microeconomics
• Assumptions:
• all variables have non-negative values
• A negative value will be written as a function of two non negative values
• ie. If $x = -5$, then we let $x_1 = 0$, $x_2=5$ and assign $x = x_1 - x_2$
• Advantages:
• Works with inequalities with non negative coefficients. As long as the inequalities are changed into standard form
• The simplex algorithm can determine if there is no solution to the problem (if pivot can't be made)
• Algorithm is easy to program in a computer
• Disadvantages:
• The algorithm becomes inefficient if too many variables are used. It may require an exponential number of steps.
• Strict inequalities might not work in LP
• Example: same example as earlier, using x as the first pivot

Non Linear LP

• Objective function is non linear when:
• Optimum may be interior as well as at boundaries of constraint 
• A local optimum is not necessarily the global optinum
• Nonlinearity came from
• multiplication of variables: economies of scale, transaction costs
• quadratic terms: variances, interest rates mean-variance optimization
• nonlinear functions: quantiles in common risk measures
• Approach to nonlinear optimization: iterative method
• Basic Numerical methods: Solving for non linear functions
• Other methods: 
• Steepest Descent Method
• Newton's Method 
• Quasi-Newton Method
• Lagrange multipliers 
• Conjugate Gradient Method
• Penalty Function Method
• In general, optimization problems are unsolvable.
• "It's the user's responsibility to choose an algorithm that is appropriate for the specific applications

Take-aways

• revised way of pivoting starting with $x$ in the simplex method
• What if the constraints are less than the unknown? Will there be a difference in the simplex method?
• Always check on the assumptions coz it might not gonna work because of wrong constraints
• the answer might not be exactly the solution to the problem at hand. We might be creating more problems than the solution of the problems
• Portfolio optimization technique - "robustness"
• The methods are just tools for the user to use. The question is, is the user using the right tools for the problem at hand?

Resources: Class groups ppt.

History of Finance - Review

Based on Statistics, we can't be always at the top. Whatever we do, we influence the market. So we can't all be winners.

• Pre-Markowitz - make money if your an insider.
• Gerald Loeb 
• the battle for investment survival
• put all your eggs into one basket
• John Maynard Keynes
• Psychological Principle
• The Greater Fool theory
• Beauty Contest Analogue 
•  based on perception, we ask the question: what will the judges judge?
• what do you think the others will think?
• even if I bought at a high price, some other fool will buy at a higher price
• Markowtiz 
• Issue before: How to measure returns
• he introduced risk and how to put up a portfolio and maximize returns
• significant is within the utility curve
• Markowitz Paradox - utility curves 
• Asset universe
• Markowitz -diversification as the risk averse technique
• James Tobins
• optimum portfolio
• don't put your eggs into one basket
• reduce risk using the combination of the risk free rate and others
• we can go outside the Markowitz area
• Simplifying calculus - efficient frontiers labours on calculus
• William Sharpe
• Main feature is the stock market itself
• risk that can be diversified away
• CAPM - Eugene Fama uses the term "one factor model" to describe Sharpe's
• Efficient Market
• Prices fluctuate ramdomly
• Eugene Fama - EMH, 1970
• 1974
• Paul Samuelson
• calls for a benchmark
• if can't beat them, join them
• "Market Portfolio"
• Charles Elis
• Mimic the market - as a proxy for the market
• the use of "index funds"
• it manages the portfolio and costs
• it means no more of the excess returns

Trying to figure out: How to deal with your investments by exerting Alpha.

Alpha - need not to look for weights

• Beta - horizontal
• Alpha - y - intercepts ( excess returns)
• To what degree is the market beatable? Can the market be beaten

Hunt for a better mouse trap

• Multi-factor model 
• Macro-economic Factor Model
• GDP+Analysis => asset allocation
• Industry => asset allocation
• Search for Arbitrage opportunities (buy and sell) and convergence trade (put and call options)
• Search for market anomalies (e.g January effect, small cap effect, low P/B ratios)
• Value vs growth Stocks, underlying stocks Not the company itself
• Contrary strategies
• Risk minimizing modeling (VAR)

References: Searching for Alpha by Ben Warwick

Films: Inside Job, Margin Call, Wall Street Never Sleeps, Wall Street, Too Big to Fail 

Averages, Covariance and Correlation

Recall:

These are some of the important group statistics for portfolio optimization.

1. Averages - a $1$ x $n$ matrix whose inputs are the average of each stocks.
2. Covariance Matrix - the vector product of the mean returns transpose and the mean return itself
3. Correlation Matrix  - $ {cov_{ij}} / {\sqrt{cov_{ii}cov_{jj}}} $

Using functions in VBA

Sub cov()
    Dim str As String
    str = Range("D3")
    
    'calling returns
    ret = Range(str)

    'counting columns
    c = UBound(ret, 2)
    
    'Writing Output
    
    Range("B5") = "Averages"
        'writing answer to averages
        Range("B6").Select
        ActiveCell.Range(Cells(1, 1), Cells(1, c)) = AveM(ret) 'it calls for the function Ave and paste it to a

    Range("B8") = "Covariance Matrix"
        'writing answer to covariance
        Range("B9").Select
        ActiveCell.Range(Cells(1, 1), Cells(c, c)) = CovarianceM(ret)
    
    Range("B" & 10 + c) = "Correlation Matrix"
        'writing answer to correlation
        Range("B" & 11 + c).Select
        cv = CovarianceM(ret)
        ActiveCell.Range(Cells(1, 1), Cells(c, c)) = CorrelationM(cv)
End Sub

Function AveM(ret)
    'summing up all values in each stock and then dividing it by the total number of returns
    
    'counting rows and columns
    r = UBound(ret)
    c = UBound(ret, 2)
        
    'creates 1xc array
    a = Range(Cells(1, 1), Cells(1, c))
    
    'summing up all values in row, then divide it by the row
    For j = 1 To c
        s = 0
        For i = 1 To r
            s = ret(i, j) + s
        Next i
        a(1, j) = s / r
    Next j
        'returns a as the average
        AveM = a
End Function

Function CovarianceM(ret)
    'covariance matrix  = (mean returns transpose x mean returns)/(n-1)
    
    'count rows and columns
    r = UBound(ret)
    c = UBound(ret, 2)
    
    'create dummy range for mean returns
    mret = Range(Cells(1, 1), Cells(r, c))
    
    'create dummy range for average and run the function Ave
    a = Range(Cells(1, 1), Cells(1, c))
    a = AveM(ret)
    
    'solving for mean returns
    For i = 1 To r
        For j = 1 To c
            mret(i, j) = ret(i, j) - a(1, j)
        Next j
    Next i
    
    'solving for transpose and the covariance matrix
    trans = Application.WorksheetFunction.Transpose(mret)
    covm = Application.WorksheetFunction.MMult(trans, mret)
    For i = 1 To c
        For j = 1 To c
            covm(i, j) = covm(i, j) / (r - 1)
        Next j
    Next i
    
    'returning the covariance matrix
    CovarianceM = covm
    
End Function

Function CorrelationM(covm)
    'Correlation Matrix cor_ij = cov_ij / [sqrt(cov_ii)*sqrt(cov_jj)]
    
    'count rows only since covm is an nxn matrix
    r = UBound(covm)
    
    'creating dummy range for cor_ij
    cor = Range(Cells(1, 1), Cells(r, r))
    
    'solving for correlation matrix
    For i = 1 To r
        For j = 1 To r
            cor(i, j) = covm(i, j) / (covm(i, i) * covm(j, j)) ^ (1 / 2)
        Next j
    Next i
    
    CorrelationM = cor
End Function

VBA: Matrix Lecture

Sub matrixP()

'putting the to matrix in an array
a = Range("b3:d5")
b = Range("g3:i5")

'applying matrix multiplication
m = Application.WorksheetFunction.MMult(a, b)
Range("b8:d10") = m

'counting rows and columns
r = Range("b3:d5").Rows.Count
c = Range("b3:d5").Columns.Count

'scalar multiplication
'has to define the array first

cvm = Range("B12:d14")
For i = 1 To r
    For j = 1 To c
        cvm(i, j) = m(i, j) / r
    Next j
Next i
Range("B12:d14") = cvm

________________________________________________________

End Sub

Sub Lecture()
'getting the average

'define returns
a = Range("b3:d5")

'define mean adjusted return
MARM = Range("b9:d11")


For j = 1 To 3
    'average
    m = 0
    For i = 1 To 3
        m = m + a(i, j)
    Next i
    ave = m / 3
    Range("a7").Offset(0, j - 1).Value = ave

    'mean returns
    For kount = 1 To 3
        MARM(kount, j) = a(kount, j) - ave
    Next kount
Next j

Range("B9:D11") = MARM


End Sub
________________________________________________________
Sub Macro3()

'diagonals 1-10
For i = 1 To 10
    Range("a1").Offset(i - 1, i - 1).Value = i
Next i


End Sub
________________________________________________________

Sub Macro4()

'row 10x1, 1-10
For i = 1 To 10
    Range("a" & i) = i
Next i

End Sub

VBA: Covariance Matrix 3

applying what we learned in class to the covariance matrix

This code contains:  averages, mean returns, covariance matrix and changing price to returns.

Sub Cov3()

    Dim ws As Worksheet
    Set ws = Sheets.Add
       
    ws.Range("A1") = "Covariance Matrix"

    ws.Range("A3") = "Prices:"
   
    'prices
    p = Worksheets("Sheet1").Range("B2:K100")
   
    'counting rows and columns
    r = Worksheets("Sheet1").Range("B2:K100").Rows.Count
    c = Worksheets("Sheet1").Range("B2:K100").Columns.Count
   
    'pasting price to ws
    ws.Range("A5").Select
    Set ans = ActiveCell.Range(Cells(1, 1), Cells(r, c))
    ans.Value = p
   
    'Returns
    ws.Range("M3") = "Returns"
       
    'rows of return
    r = r - 1
   
    ret = Range("M6:V103")
    For i = 1 To r
        For j = 1 To c
            ret(i, j) = p(i + 1, j) / p(i, j) - 1
        Next j
    Next i
    Range("M6:V103") = ret
   
    'getting the averages and mean returns
    ws.Range("X3") = "Average"
    ws.Range("AI3") = "Mean Returns"
   
    mret = ws.Range("AI6:AR103")
   
    For j = 1 To c
        'average
        s = 0
        For i = 1 To r
            s = s + ret(i, j)
        Next i
        ave = s / r
        Range("X6").Offset(0, j - 1).Value = ave

        'mean returns
        For kount = 1 To r
        mret(kount, j) = ret(kount, j) - ave
        Next kount
    Next j
   
    ws.Range("AI6:AR103") = mret
       
    'getting the covariance matrix
    ws.Range("AT3") = "Covariance"
    trans = Application.WorksheetFunction.Transpose(mret)
    covm = Application.WorksheetFunction.MMult(trans, mret)
    For i = 1 To c
        For j = 1 To c
            covm(i, j) = covm(i, j) / (r - 1)
        Next j
    Next i
   
    Range("AT6").Select
    Set ans = ActiveCell.Range(Cells(1, 1), Cells(c, c))
    ans.Value = covm
   
End Sub

VBA: Covariance Matrix 2

Here is the second attempt in programming the covariance matrix. I still have problems with getting the returns and dynamic averages.


Sub COV2M()

    Dim ws As Worksheet
    Set ws = Sheets.Add
        
    ws.Range("A1") = "Covariance Matrix"

    ws.Range("A3") = "Prices:"
    
    'prices
    p = Worksheets("Sheet1").Range("B2:K100")
    
    'counting rows and columns
    r = Worksheets("Sheet1").Range("B2:K100").Rows.Count
    c = Worksheets("Sheet1").Range("B2:K100").Columns.Count
    
    'pasting price to ws
    ws.Range("A5").Select
    Set ans = ActiveCell.Range(Cells(1, 1), Cells(r, c))
    ans.Value = p
    
    'Returns
    ws.Range("M3") = "Returns"
        
    'rows of return
    r = r - 1
    
    ret = Range("M6:V103")
    For i = 1 To r
        For j = 1 To c
            ret(i, j) = p(i + 1, j) / p(i, j) - 1
        Next j
    Next i
    Range("M6:V103") = ret
    
    'getting the averages
    ws.Range("X3") = "Average"
    ave = ws.Range("X6:AG6")
    For j = 1 To c
        s = 0
        For i = 1 To r
            s = ret(i, j) + s
        Next i
        ave(1, j) = s / r
    Next j
    ws.Range("X6:AG6") = ave
    
    'getting the mean Returns
    ws.Range("AI3") = "Mean Returns"
    mret = ws.Range("AI6:AR103")
    For i = 1 To r
        For j = 1 To c
            mret(i, j) = ret(i, j) - ave(1, j)
        Next j
    Next i
    ws.Range("AI6:AR103") = mret
    
    'getting the covariance matrix
    ws.Range("AT3") = "Covariance"
    trans = Application.WorksheetFunction.Transpose(mret)
    covm = Application.WorksheetFunction.MMult(trans, mret)
    For i = 1 To c
        For j = 1 To c
            covm(i, j) = covm(i, j) / (r - 1)
        Next j
    Next i
    
    Range("AT6").Select
    Set ans = ActiveCell.Range(Cells(1, 1), Cells(c, c))
    ans.Value = covm
    
End Sub