CS/ECE/ISyE 524 — Introduction to Optimization — Spring 2019

Equity Valuation and Portfolio Optimization Using Modern Portfolio Theory

Ritik Goyal (ragoyal2@wisc.edu), Cole Thomson (cthomson4@wisc.edu), Noah Dreikosen (njdreikosen@wisc.edu), and Zachary Easton (zeaston@wisc.edu)

---

Table of Contents

1. Introduction
2. Mathematical Model
3. Equity Valuation Model
4. Markowitz Modern Portfolio Model
5. Solution
6. Results and Discussion
7. Standard Deviations and Expected Returns of Basket of Equities
8. Optimal Portfolio Examples - Long Positions
9. Optimal Portfolio Examples - Short Positions
10. Pareto Frontier
11. Accuracy of Models and Possible Improvements
12. Conclusion
13. References

1. Introduction

Approximately $169 billion dollars of stocks are traded daily on the New York Stock Exchange. The overall value of the companies traded in the U.S. stock market is $34 trillion. This equates to roughly 43% of the value of all publicly traded companies in the world (Surz, 2018). Millions of investors look to buy (sell) companies that they feel are undervalued (overvalued) so that they may sell (cover) their position at some later date for a profit. To do this, traders and investors must analyze thousands of stocks based on the expected risks and returns, which can be done many different ways. One such strategy that investors use, which will be examined in this project, combines Modern Portfolio Theory, introduced by economist Harry Markowitz, with Fundamental Analysis of the underlying factors that affect a stock's price - a method widely advocated by investing juggernauts like Benjamin Graham and Warren Buffett.

This project attempts to accomplish two main tasks. First, an absolute equity (stock) valuation model is to be developed that receives fundamental data of a company as parameters and returns the value of the company. The value returned by the model is assumed to be the intrinsic value of the company. This value can be compared to the market capitalization (or market value - as determined by the company’s stock price and number of outstanding shares) to determine if the stock is under, over, or fair-valued. Second, an optimal portfolio is to be developed using both the Markowitz Modern Portfolio Theory and the valuation determined by the previous model. Both long (buying securities) and short (selling securities by borrowing shares) optimal portfolios are discussed and explored.

Benjamin Graham, and his famed prodigy Warren Buffett, have forged their investment principles by leveraging fundamental characteristics (among a few other ideas) of a company to determine its actual value. Fundamental data is any data that is expected to affect the perceived value of an equity (McClure, 2019). Examples of fundamental data include: cash flow, return on assets, earnings, debt levels, and many more. In total, there were 74 fundamental metrics used to develop the equity valuation model described in this report. In this project, 1,230 samples were collected from years 2012 to 2016. Each of these samples are sourced from Form 10-K SEC filings that companies must submit to shareholders on a yearly basis. This dataset can be found on Kaggle, an open source data science community, from which both fundamental analysis and stock price data is taken.

Harry Markowitz developed what is now known as Modern Portfolio Theory (MPT) that was first published in The Journal of Finance in 1952 (Chen, 2019). The model has various assumptions not discussed in this report, but is rooted in the idea that choosing a group of investment equities which collectively have lower risk than any single equity will have on its on its own. Depending on investment goals, a tradeoff between the expected returns of a portfolio and the risk of the associated portfolio can be explored. An optimal portfolio lies on the Pareto frontier of expected returns and expected portfolio risk (Kenton, 2018). In this project, we explore the addition of a third tradeoff term to determine an optimal portfolio. This additional term is the amount undervalued (or overvalued) of an equity as determined by our equity valuation model. We explore different trade-offs to reflect different investment styles in this report.

In the coming sections mathematical models, solution code (in Julia 1.1.0), discussion of results, and the conclusion of this optimization project will be available.

2. Mathematical model

2.A. Equity Valuation Model

Type of Model: Least Squares Regression

Variables:

Weights of each fundamental attribute to determine the value of a company's stock:

Parameters:

Company i with fundamental attribute j:

Market capitalization of company i:

Constraints:

There exists no constraints for this model.

Objective:

We aim to find the curve that predicts the true value of a company based on the company's current fundamental data. To accomplish this, we set the objective to minimize the sum of squares between the companies' current market capitalization (y) and the market capitalization predicted by our model (B x u).

Full Model:

2.B. Markowitz Modern Portfolio Model

Type of Model: Quadratic Program

Variables:

Proportion of total portfolio to invest in equity i:

Parameters:

Expected daily return of equity i as a percentage:

Covariance between equity i and equity j (used to measure risk):

Percent difference between the estimated value of the company and the current market capitalization of the company (obtained from Equity Valuation Model):

Tradeoff weights for covariance matrix (risk) and the percent under/over valued determined by the Equity Valuation Model:

Constraints:

For Long Portfolio: The amount invested in each company must be non-negative, and no single investment may exceed 20% of the total portfolio:

The portfolio is allocated to 100% capacity:

For Short-only Portfolio:

The amount invested in each company must be non-positive, and no single investment may exceed 20% of the total portfolio:

The portfolio is allocated to 100% capacity:

Objective:

For a Long porfolio, we aim to maximize the total expected return of the portfolio, minimize the risk, and maximize the percent difference between estimated stock valuation and the current market valuation. For a Short-Only portfolio, we aim to minimize the total expected return of the portfolio, minimize the risk of the entire portfolio, and minimize the percent difference between estimated stock valuation and the current market valuation. This results in a trade-off problem in which weights will be chosen depending on a person's risk tolerance.

Full Model:

Model for Long Portfolio:

Model for Short-only Portfolio:

3. Solution

To expand on the possibilities for an optimal portfolio, we decided to construct both an optimal long position portfolio as well as an optimal short position portfolio. A fund manager may desire to allocate a portfolio to only short-positions to hedge themselves against their long positions and/or possible economic factors like a recession that might pull many of the stocks in the market lower. The traditional “buy-and-hold” investor may find that the long-only optimal portfolio is better suited for their needs. In addition, the short portfolio is neglecting some important as pects of borrowing shares including: borrowing rates, availability of shares to borrow, potential margin calls, etc. The potential loss on a short investment is theoretically unbounded, but we do not discuss these assumptions and technicalities in this report. An investor looking to short securities in practice would need to obtain a deeper understanding of the subject before attempting any endeavor.

The Equity Valuation Model is first constructed, since its results will be used in the Markowitz Optimal Portfolio models. It was desired by our group to obtain solutions that were “real”, so only real market data was used throughout the analysis. To build the Equity Valuation Model, we used 5 years worth of data (1,230 samples) to essentially train our LS Regression Model. By using the optimal weights for each fundamental characteristic, we are then able to estimate the value of a company by applying the weights to the fundamental data of any given company. Since the market capitalization is always known for each stock (market capitalization = # shares outstanding * share price), we can compare the estimated value from our model with the current market capitalization to determine if an equity is currently under/over or fair-valued. However, since the price of companies can vary widely, it was necessary that we looked at a relative measure of value difference instead of an absolute one. This meant looking at the percent difference of our expected price and the current price instead of simply taking the difference. Once we obtained a vector of calculated percent differences, we could then insert this information into the Markowitz Optimal Portfolio models.

When considering an optimal portfolio for long positions, we concerned our model with three different objectives: maximize expected returns, minimize risk, and maximizing the percent undervalued that a company is as determined by the previous model. It became necessary to model the objective function as a tradeoff between these three terms since all of them could not be solved to their absolute optimum simultaneously. Later in the report we will explore how different tradeoffs affect optimal portfolio composition as well as performance. Daily expected return data was collected for 306 equities and a covariance matrix of these equities was constructed. After having all pieces for the objective needed, we looked at reasonable constraints for the model. To ensure at least some diversification, we did not allow that more than 20% of the portfolio to be invested in any one stock. We also enforced that the entire portfolio be allocated completely. The long and short portfolios were constructed identically with the exception of desiring to maximize the percent overvalued that a stock is in the objective function.

To access the accuracy of our models, a test set of the 306 equities was gathered from the year 2014. A validation set was constructed with the same 306 equites for the year 2015. Optimal long and short portfolios were then constructed using the test set. Under the assumption that the portfolio was held for a year, we analyzed the results using the validation set.

# Helper function to calculate total profits and portfolio returns as a percent for each company
# selected by the model using what price the stocks were bought and sold at and the percent invested of the portfolio.
using DataFrames

function check_profits(ix, amount, prices_test, prices_val, xsol)
    profit = 0
    exp_returns = 0
    init_vals = prices_test[:,1] .* prices_test[:,2]
    final_vals = prices_val[:,1] .* prices_val[:,2]
    df = DataFrame()

    company_arr = []
    init_vals_arr = []
    final_vals_arr =[]
    invested_arr = []
    profit_arr = []

    for i in 1:length(ix)
        company = tickers[ix[i]]
        # Price - Valuation of company
        init_price = init_vals[ix[i]]
        final_price = final_vals[ix[i]]
        percent_invested = xsol[ix1[i]]
        #println("Percent invested: ", percent_invested)
        percent_invested = abs(percent_invested)
        invested = percent_invested*amount
        num_stocks = invested/init_price
        final_sell_price = num_stocks*final_price
        diff = final_sell_price - invested
        profit+=diff

        push!(company_arr, company)
        push!(init_vals_arr, init_price)
        push!(final_vals_arr, final_price)
        push!(invested_arr, percent_invested)
        push!(profit_arr, diff)

    end
    exp_returns = profit/amount

    df[:Companies] = company_arr
    df[:Valuation_when_bought] = init_vals_arr
    df[:Valuation_when_sold] = final_vals_arr
    df[:Percent_invested] = invested_arr
    df[:Returns] = profit_arr    
    println("Portfolio returns (percent) ", exp_returns)
    return df
end

check_profits (generic function with 1 method)

# Calculates the bias offset (difference) between the predicted valuation and actual valuation of a company while training the linear regression model.
function get_bias_diff(train, coeffs, test)
    actual_prices = train[78]
    num_shares = train[77]
    actual_valuations = actual_prices .* num_shares
    raw_temp = re_normalize(train)
    exp_valuations = raw_temp * coeffs
    bias = []
    for s in test[:,1]
        sum_diff_valuations = 0
        count = 0
        for i in 1:length(train[:,1])
            if train[i,1] == s
                sum_diff_valuations += exp_valuations[i] - actual_valuations[i]
                count += 1
            end
        end
        if count == 0
            avg = 0
        else
            avg = sum_diff_valuations/count
        end
        push!(bias, avg)
    end

    return bias
end

get_bias_diff (generic function with 1 method)

# Helper function to calculate difference between expected prices calculated by the model and actual prices as a percent
# for each company.
using LinearAlgebra

function get_diff_expected_prices(coeffs, raw_test, test_company_num)
    test_comp_name = raw_test[1][test_company_num]
    actual_prices = raw_test[78]
    num_shares = raw_test[77]
    actual_valuations = actual_prices .* num_shares
    raw_temp = re_normalize(raw_test) #normalize by scaling using the same scale factor    
    exp_valuations = raw_temp * coeffs #Calculate expected valuations by multiplying with our coefficients from the LS regression
    println("Calculated valuation for ", test_comp_name,  " before bias correction: ", exp_valuations[test_company_num])
    exp_valuations = exp_valuations - bias

    exp_prices = exp_valuations ./ num_shares
    diff = (exp_prices - actual_prices)./actual_prices
    println("Expected (calculated) valuation for ", test_comp_name,  " with bias correction: ", exp_valuations[test_company_num])
    println("Actual valuation for ", test_comp_name,  ": ", actual_valuations[test_company_num])
    println("Bias: ", bias[test_company_num])
    return diff
end

get_diff_expected_prices (generic function with 1 method)

# Helper function that takes in dataframe and returns columns 3 thru 76, normalized as a matrix.
using Statistics
# Scale is 75 because of an added term for intercept.
scale = ones(75)
function normalize(data)
    newdata = ones(length(data[:,1]), 75)

    for i in 3:76
        maxval = abs(maximum(data[:,i]))
        minval = abs(minimum(data[:,i]))

        #Normalizing the columns by dividing by maximum value so that all values are between -1 and 1
        if maxval != 0
            newdata[:,i-2] = data[:,i]/maxval
            scale[i-2] = maxval
        elseif minval != 0
            newdata[:,i-2] = data[:,i]/minval
            scale[i-2] = minval
            else #This branch will never really execute, but added it in just in case
            mean = mean(data[:,i])
            newdata[:,i-2] = data[:,i]/mean
            scale[i-2] = mean
        end
    end
    return newdata
end

normalize (generic function with 1 method)

# Helper Function to normalize test data by scaling with the same factor as train data.
function re_normalize(test)
    newdata = ones(length(test[:,1]), 75)

    for i in 3:76
        newdata[:, i-2] = test[:,i]
    end

    newdata = newdata ./ scale'
    return newdata
end

re_normalize (generic function with 1 method)

# CSV used to read in data from .csv files.
using CSV
file1 = "./fundementals_with_ratios_training_set.csv"
file2 = "./fundementals_with_ratios_testing_set.csv"
file3 = "./fundementals_with_ratios_validation_set.csv"
raw_train = CSV.read(file1);
raw_test = CSV.read(file2);
raw_val = CSV.read(file3);

# Merge the data into one dataframe. We will use this in our least squares model.
raw = vcat(raw_train, raw_test, raw_val)

1,230 rows × 78 columns

	Ticker Symbol	Period Ending	Accounts Payable	Accounts Receivable	Add'l income/expense items	After Tax ROE	Capital Expenditures	Capital Surplus	Cash Ratio	Cash and Cash Equivalents	Changes in Inventories	Common Stocks	Cost of Revenue	Current Ratio	Deferred Asset Charges	Deferred Liability Charges	Depreciation	Earnings Before Interest and Tax	Earnings Before Tax	Effect of Exchange Rate	Equity Earnings/Loss Unconsolidated Subsidiary	Fixed Assets	Goodwill	Gross Margin	Gross Profit	Income Tax	Intangible Assets	Interest Expense	Inventory	Investments	Liabilities	Long-Term Debt	Long-Term Investments	Minority Interest	Misc. Stocks	Net Borrowings	Net Cash Flow	Net Cash Flow-Operating	Net Cash Flows-Financing	Net Cash Flows-Investing	Net Income	Net Income Adjustments	Net Income Applicable to Common Shareholders	Net Income-Cont. Operations	Net Receivables	Non-Recurring Items	Operating Income	Operating Margin	Other Assets	Other Current Assets	Other Current Liabilities	Other Equity	Other Financing Activities	Other Investing Activities	Other Liabilities	Other Operating Activities	Other Operating Items	Pre-Tax Margin	Pre-Tax ROE	Profit Margin	Quick Ratio	Research and Development	Retained Earnings	Sale and Purchase of Stock	Sales, General and Admin.	Short-Term Debt / Current Portion of Long-Term Debt	Short-Term Investments	Total Assets	Total Current Assets	Total Current Liabilities	Total Equity	Total Liabilities	Total Liabilities & Equity	Total Revenue	Treasury Stock	Earnings Per Share	Estimated Shares Outstanding	Close
	String⍰	Dates…⍰	Int64⍰	Int64⍰	Int64⍰	Int64⍰	Int64⍰	Int64⍰	Int64⍰	Int64⍰	Int64⍰	Int64⍰	Float64⍰	Int64⍰	Int64⍰	Int64⍰	Int64⍰	Int64⍰	Int64⍰	Int64⍰	Int64⍰	Float64⍰	Float64⍰	Int64⍰	Float64⍰	Int64⍰	Float64⍰	Int64⍰	Int64⍰	Int64⍰	Int64⍰	Float64⍰	Float64⍰	Int64⍰	Int64⍰	Int64⍰	Int64⍰	Int64⍰	Int64⍰	Int64⍰	Int64⍰	Int64⍰	Int64⍰	Int64⍰	Int64⍰	Int64⍰	Int64⍰	Int64⍰	Int64⍰	Int64⍰	Int64⍰	Int64⍰	Int64⍰	Int64⍰	Int64⍰	Int64⍰	Int64⍰	Int64⍰	Int64⍰	Int64⍰	Int64⍰	Int64⍰	Float64⍰	Int64⍰	Int64⍰	Int64⍰	Float64⍰	Float64⍰	Float64⍰	Int64⍰	Float64⍰	Float64⍰	Float64⍰	Float64⍰	Float64⍰	Float64⍰	Float64⍰	Float64⍰
1	AAL	2012-12-31	3068000000	-222000000	-1961000000	23	-1888000000	4695000000	53	1330000000	0	127000000	1.0499e10	78	0	223000000	1001000000	-1813000000	-2445000000	0	0	1.3402e10	0.0	58	1.4356e10	-569000000	8.69e8	632000000	580000000	306000000	473000000	7.116e9	0.0	0	0	-1020000000	197000000	1285000000	483000000	-1571000000	-1876000000	2050000000	-1876000000	-4084000000	1124000000	386000000	148000000	1	2167000000	626000000	4524000000	-2980000000	1509000000	11000000	15147000000	-141000000	845000000	10	31	8	72	0	-9.462e9	0	12977000000	1419000000	3.412e9	2.351e10	7.072e9	9011000000	-7.987e9	2.4891e10	1.6904e10	2.4855e10	-3.67e8	-5.6	3.35e8	13.5
2	AAL	2013-12-31	4975000000	-93000000	-2723000000	67	-3114000000	10592000000	75	2175000000	0	5000000	1.1019e10	104	0	935000000	1020000000	-1324000000	-2180000000	0	0	1.9259e10	4.086e9	59	1.5724e10	-346000000	2.311e9	856000000	1012000000	-1181000000	-235000000	1.5353e10	0.0	0	0	2208000000	660000000	675000000	3799000000	-3814000000	-1834000000	1873000000	-1834000000	-4489000000	1560000000	559000000	1399000000	5	2299000000	1465000000	7385000000	-2032000000	1711000000	481000000	14915000000	-56000000	853000000	8	80	7	96	0	-1.1296e10	0	12913000000	1446000000	8.111e9	4.2278e10	1.4323e10	13806000000	-2.731e9	4.5009e10	4.2278e10	2.6743e10	0.0	-11.25	1.63022e8	25.25
3	AAP	2012-12-29	2409453000	-89482000	600000	32	-271182000	520215000	23	598111000	-260298000	7000	3.10697e9	124	0	0	189544000	657915000	624074000	0	0	1.29255e9	7.6389e7	50	3.09804e9	236404000	2.8845e7	33841000	2308609000	0	426323000	6.04461e8	0.0	0	0	177445000	540210000	685281000	127907000	-272978000	387670000	23311000	387670000	387670000	229866000	0	657315000	11	31833000	47614000	149558000	2667000	-33499000	-1796000	239021000	8213000	0	10	52	6	34	0	7.149e8	-18600000	2440721000	627000	0.0	4.61381e9	3.1842e9	2559638000	1.21069e9	3.40312e9	4.61381e9	6.205e9	-2.7095e7	5.29	7.32836e7	71.51
4	AAP	2013-12-28	2609239000	-32428000	2698000	26	-195757000	531293000	40	1112471000	-203513000	7000	3.24167e9	144	0	0	207795000	663016000	626398000	0	0	1.28603e9	1.99835e8	50	3.25215e9	234640000	4.9872e7	36618000	2556557000	0	172715000	1.05267e9	0.0	0	0	445679000	514360000	545250000	331217000	-362107000	391758000	-2088000	391758000	391758000	277595000	0	660318000	10	39649000	42761000	154630000	3683000	-27209000	-166350000	231116000	11011000	0	10	41	6	52	0	1.08911e9	-77184000	2591828000	916000	0.0	5.56477e9	3.98938e9	2764785000	1.51621e9	4.04857e9	5.56477e9	6.49381e9	-1.0789e8	5.36	7.30892e7	109.92
5	AAP	2016-01-02	3757085000	-21476000	-7484000	19	-234747000	603332000	2	90782000	-244096000	7000	5.31425e9	130	0	433925000	269476000	818296000	752888000	-4213000	0	1.43458e9	9.89484e8	45	4.42277e9	279490000	6.87125e8	65408000	4174768000	0	174667000	1.21316e9	0.0	0	0	-426322000	-13889000	689642000	-445952000	-253366000	473398000	30250000	473398000	473398000	597788000	0	825780000	8	82633000	77408000	39794000	-44059000	-13492000	-18619000	229354000	7423000	0	8	31	5	20	0	2.02108e9	-1491000	3596992000	598000	0.0	8.13457e9	4.94075e9	3797477000	2.46065e9	5.67392e9	8.13457e9	9.73702e9	-1.19709e8	6.45	7.3395e7	150.51
6	AAPL	2013-09-28	36223000000	-1949000000	1156000000	30	-8165000000	0	93	14259000000	-973000000	19764000000	1.07e11	168	0	2625000000	6757000000	50155000000	50155000000	0	0	1.6597e10	1.577e9	38	6.4304e10	13118000000	4.179e9	0	1764000000	-24042000000	8320000000	1.696e10	1.06e11	0	0	16896000000	3513000000	53666000000	-16379000000	-33774000000	37037000000	3394000000	37037000000	37037000000	24094000000	0	48999000000	29	5146000000	6882000000	7435000000	-471000000	-1082000000	-1567000000	20208000000	1080000000	0	29	41	22	164	4475000000	1.04e11	-22330000000	10830000000	0	2.6287e10	2.07e11	7.3286e10	43658000000	1.24e11	8.3451e10	2.07e11	1.71e11	0.0	40.03	9.25231e8	482.75
7	AAPL	2016-09-24	59321000000	1044000000	1348000000	36	-12734000000	0	85	20484000000	217000000	31251000000	1.31e11	135	0	2930000000	10505000000	61372000000	61372000000	0	0	2.701e10	5.414e9	39	8.4263e10	15685000000	3.206e9	0	2132000000	-32022000000	-1867000000	7.5427e10	1.7e11	0	0	22057000000	-636000000	65824000000	-20483000000	-45977000000	45687000000	9148000000	45687000000	45687000000	29299000000	0	60024000000	28	8757000000	8283000000	8080000000	634000000	-1570000000	-1221000000	36074000000	1090000000	0	28	48	21	133	10045000000	9.6364e10	-29227000000	14194000000	11605000000	4.6671e10	3.22e11	1.07e11	79006000000	1.28e11	1.93e11	3.22e11	2.16e11	0.0	8.35	5.4715e9	112.71
8	ABBV	2013-12-31	6448000000	681000000	-54000000	92	-491000000	3671000000	144	9595000000	-56000000	16000000	4.581e9	259	0	0	897000000	5610000000	5332000000	-10000000	0	2.298e9	6.277e9	76	1.4209e10	1204000000	1.89e9	278000000	1150000000	1775000000	-426000000	1.4292e10	1.18e8	0	0	-601000000	3694000000	6267000000	-3442000000	879000000	4128000000	584000000	4128000000	4128000000	5569000000	338000000	5664000000	30	767000000	1234000000	0	-442000000	-313000000	-405000000	3535000000	459000000	0	28	119	22	243	2855000000	1.567e9	27000000	5352000000	431000000	3.0e8	2.9198e10	1.7848e10	6879000000	4.492e9	2.4706e10	2.9198e10	1.879e10	-3.2e8	2.58	1.6e9	52.81
9	ABC	2013-09-30	14870635000	-2312518000	-44000	19	-202450000	2360992000	8	1231006000	-1486572000	2678000	8.54513e10	97	0	0	170793000	898355000	824458000	0	0	8.03561e8	0.0	3	2.50782e9	331023000	3.49971e9	73897000	6981494000	0	3830847000	1.39661e9	0.0	0	0	0	164398000	788125000	-740987000	117260000	433707000	235476000	433707000	493435000	6051920000	0	898399000	1	221713000	129231000	0	-35483000	-50538000	319710000	331652000	-169745000	162186000	1	36	0	50	0	1.50841e9	-328463000	1447234000	0	0.0	1.89186e10	1.43937e10	14870635000	2.31975e9	1.65989e10	1.89186e10	8.79592e10	-1.51686e9	1.88	2.30695e8	61.1
10	ABC	2016-09-30	24670159000	-912724000	5048000	67	-464616000	4333001000	11	2741832000	-1107252000	2778000	1.43e11	90	0	2214774000	392166000	1530822000	1390910000	0	0	1.53068e9	5.9915e9	3	4.27261e9	-37019000	2.96785e9	139912000	10723920000	40712000	3094340000	3.87024e9	0.0	0	0	713214000	574390000	3178497000	565072000	-3169179000	1427929000	330197000	1427929000	1427929000	9175876000	0	1525774000	1	314325000	210219000	0	-114308000	0	-2745275000	160470000	-46159000	364735000	1	65	1	48	0	2.30394e9	168903000	2382097000	611149000	0.0	3.36562e10	2.28518e10	25281308000	2.1294e9	3.15268e10	3.36562e10	1.47e11	-4.39601e9	6.73	2.12174e8	80.78
11	ABT	2012-12-31	10889000000	36000000	-1260000000	22	-1795000000	0	114	10802000000	-417000000	11755000000	8.899e9	236	3213000000	0	2782000000	100000000	-220000000	40000000	0	8.063e9	1.5774e10	53	1.0151e10	-457000000	8.588e9	320000000	3793000000	-3062000000	-1443000000	1.8085e10	2.74e8	92000000	0	4413000000	3989000000	9314000000	195000000	-5560000000	5963000000	2428000000	5963000000	237000000	10599000000	0	1360000000	7	0	1757000000	0	-3594000000	-521000000	-703000000	9057000000	-35000000	595000000	1	1	31	207	1461000000	2.4151e10	-514000000	6735000000	2391000000	4.372e9	6.7235e10	3.1323e10	13280000000	2.6721e10	4.0514e10	6.7235e10	1.905e10	-5.591e9	3.76	1.5859e9	65.4999
12	ABT	2013-12-31	5948000000	-113000000	53000000	10	-1145000000	0	85	3475000000	-154000000	12048000000	9.193e9	202	2109000000	0	1719000000	2186000000	2041000000	-26000000	0	5.971e9	9.772e9	53	1.0464e10	53000000	5.735e9	145000000	2693000000	-2225000000	-1101000000	3.388e9	1.19e8	96000000	0	1792000000	-7327000000	3324000000	-6696000000	-3929000000	2576000000	266000000	2576000000	1988000000	6514000000	0	2133000000	11	0	1942000000	386000000	-2012000000	-6396000000	-559000000	4791000000	131000000	588000000	10	8	13	174	1371000000	2.1979e10	-1210000000	6372000000	3173000000	4.623e9	4.2953e10	1.9247e10	9507000000	2.5171e10	1.7782e10	4.2953e10	1.9657e10	-6.844e9	1.64	1.57073e9	38.33
13	ADBE	2013-11-29	729257000	33649000	926000	4	-188358000	3392696000	208	834556000	0	61000	5.86557e8	265	0	428902000	321227000	423649000	356141000	-5241000	0	6.59774e8	4.77198e9	86	3.46868e9	66156000	6.05254e8	67508000	0	-309116000	184502000	1.4993e9	2.07239e8	0	0	-176000	-590496000	1151686000	-559138000	-1177803000	289985000	377832000	289985000	289985000	702067000	26497000	422723000	10	90121000	170110000	781715000	46103000	0	-680329000	201817000	-55509000	52254000	9	5	7	265	826631000	6.92896e9	-599224000	2140578000	14676000	2.3392e9	1.03803e10	4.04593e9	1525648000	6.72463e9	3.65566e9	1.03803e10	4.05524e9	-3.64319e9	0.58	4.99974e8	56.78
14	ADI	2013-11-02	323084000	12377000	89350000	14	-123074000	711879000	821	392089000	28527000	51842000	9.41278e8	959	43590000	23401000	110416000	842425000	815323000	1394000	0	5.08171e8	2.84112e8	64	1.69241e9	141836000	2.8552e7	27102000	283337000	-918720000	129162000	8.72241e8	3.816e6	0	0	40982000	-136744000	912345000	-100557000	-949926000	673487000	-52408000	673487000	673487000	461443000	29848000	753075000	29	41084000	44733000	247428000	-80546000	-8455000	91868000	176020000	10784000	220000	31	17	26	910	513035000	4.0564e9	245748000	396233000	0	4.29082e9	6.38175e9	5.47243e9	570512000	4.73958e9	1.64217e9	6.38175e9	2.63369e9	0.0	2.19	3.07528e8	49.68
15	ADI	2016-10-29	431396000	-9392000	17566000	17	-127397000	402270000	518	921132000	38221000	51363000	1.19424e9	635	62157000	136083000	209790000	1045678000	956921000	-2929000	0	6.36116e8	1.67912e9	65	2.22717e9	95257000	5.49368e8	88757000	376555000	-989183000	101593000	1.73218e9	2.1937e7	0	0	857175000	36779000	1280895000	-22917000	-1218270000	861664000	88952000	861664000	861664000	477609000	13684000	1028112000	30	46721000	64906000	351538000	-73814000	-8787000	-101690000	153466000	-9933000	70123000	28	19	25	587	653816000	4.7858e9	-308565000	461438000	0	3.13466e9	7.97028e9	4.97486e9	782934000	5.16562e9	2.80466e9	7.97028e9	3.42141e9	0.0	0.96	8.97567e8	63.53
16	ADM	2013-12-31	14135000000	296000000	155000000	7	-913000000	0	48	7082000000	2541000000	6136000000	8.5915e10	182	0	1448000000	909000000	2437000000	2024000000	0	411000000	1.0137e10	0.0	4	3.889e9	670000000	5.61e8	413000000	11441000000	104000000	191000000	5.347e9	4.046e9	38000000	0	-2713000000	1407000000	5226000000	-3242000000	-577000000	1342000000	30000000	1342000000	1753000000	3224000000	259000000	1871000000	2	478000000	6350000000	0	-57000000	73000000	232000000	1105000000	-95000000	0	2	10	1	109	0	1.4077e10	-101000000	1759000000	1523000000	4.33e8	4.3752e10	2.853e10	15658000000	2.0156e10	2.3558e10	4.3714e10	8.9804e10	0.0	2.03	6.61084e8	43.4
17	ADS	2012-12-31	490095000	-49219000	0	80	-116455000	1454230000	28	1386042000	0	950000	2.39209e9	181	30027000	471092000	274263000	974364000	682904000	5280000	0	2.53028e8	1.75105e9	34	1.2493e9	260648000	5.82874e8	291460000	0	-1389770000	90743000	4.70849e9	0.0	0	0	2334253000	677139000	1134190000	2209019000	-2671350000	422256000	438242000	422256000	422256000	7575052000	0	974364000	27	251014000	171049000	2265359000	-21861000	-22000	-1165125000	1259297000	-42095000	166876000	19	129	12	181	0	1.55326e9	-105144000	108059000	2277323000	0.0	1.20001e10	9.13214e9	5032777000	5.28487e8	1.14717e10	1.20001e10	3.64139e9	-2.45809e9	8.44	5.00303e7	144.76
18	ADS	2013-12-31	472326000	-33414000	0	58	-135376000	1512752000	33	1480171000	0	983000	2.89492e9	230	2454000	446505000	307288000	1098912000	793412000	-11758000	0	2.99188e8	1.7357e9	33	1.42415e9	297242000	4.60404e8	305500000	0	-1453163000	5520000	6.00271e9	0.0	0	0	928674000	76470000	1003492000	704152000	-1619416000	496170000	447854000	496170000	496170000	8680730000	0	1098912000	25	345666000	239941000	2650683000	-18227000	-22000	-30877000	1426785000	-219926000	216119000	18	93	11	230	0	2.04943e9	-216995000	109115000	1389489000	0.0	1.32443e10	1.04008e10	4512498000	8.55761e8	1.23885e10	1.32443e10	4.31906e9	-2.68918e9	10.09	4.91744e7	262.93
19	ADSK	2013-01-31	396700000	-98100000	0	12	-56400000	0	187	1612200000	0	1449800000	2.385e8	245	122800000	187600000	127800000	310000000	310000000	2000000	0	1.149e8	8.715e8	90	2.0737e9	62600000	7.6e7	0	0	-300000000	85000000	7.456e8	4.111e8	0	0	745600000	455300000	559100000	541400000	-647200000	247400000	194000000	247400000	247400000	537300000	43900000	305900000	13	159700000	60800000	647000000	-5700000	-6100000	-290800000	288300000	3000000	42100000	13	15	11	245	600000000	5.991e8	-211000000	1081800000	0	3.421e8	4.3084e9	2.5524e9	1043700000	2.0432e9	2.2652e9	4.3084e9	2.3122e9	0.0	1.09	2.26972e8	38.88
20	ADSK	2016-01-31	522100000	-195500000	0	20	-72400000	0	141	1353000000	0	1821500000	3.707e8	188	9200000	518000000	145800000	1300000	-20300000	-5300000	0	1.693e8	1.535e9	85	2.1334e9	310200000	7.08e7	21600000	0	-544100000	385400000	1.4877e9	5.323e8	0	0	748300000	-57600000	414000000	343200000	-809500000	-330500000	408100000	-330500000	-330500000	653600000	0	1300000	0	205600000	88600000	1068900000	-121100000	-57900000	-193000000	299000000	700000	33200000	1	1	13	188	790000000	-8.08e7	-347200000	1308900000	0	8.979e8	5.5153e9	2.9931e9	1591000000	1.6196e9	3.8957e9	5.5153e9	2.5041e9	0.0	-1.46	2.2637e8	46.82
21	AEE	2012-12-31	671000000	30000000	33000000	15	-1063000000	5616000000	7	184000000	-28000000	2000000	3.477e9	134	0	3899000000	736000000	1221000000	829000000	0	0	1.5348e10	4.11e8	40	2.304e9	307000000	1.4e7	392000000	570000000	-19000000	-56000000	5.802e9	4.08e8	151000000	0	-26000000	-46000000	1690000000	-426000000	-1310000000	-974000000	2025000000	-974000000	522000000	886000000	0	1188000000	21	2453000000	1956000000	1568000000	-8000000	-12000000	-228000000	3076000000	-43000000	673000000	14	13	17	113	0	1.006e9	-16000000	443000000	447000000	0.0	2.223e10	3.596e9	2686000000	6.616e9	1.5614e10	2.223e10	5.781e9	0.0	-4.01	2.42893e8	30.72
22	AEE	2013-12-31	947000000	-60000000	43000000	5	-1379000000	5632000000	1	30000000	60000000	2000000	3.49e9	80	0	3851000000	761000000	1227000000	829000000	0	0	1.6205e10	4.11e8	40	2.348e9	311000000	0.0	398000000	526000000	-17000000	-156000000	5.504e9	4.94e8	142000000	0	247000000	-179000000	1693000000	-149000000	-1723000000	295000000	703000000	295000000	518000000	1010000000	0	1184000000	20	1960000000	406000000	612000000	3000000	-2000000	-327000000	2540000000	90000000	706000000	14	13	5	59	0	9.07e8	-2000000	458000000	902000000	0.0	2.1042e10	1.972e9	2461000000	6.544e9	1.4498e10	2.1042e10	5.838e9	0.0	1.19	2.47899e8	36.16
23	AEP	2012-12-31	2185000000	-16000000	154000000	8	-3025000000	6049000000	10	355000000	-224000000	3289000000	6.395e9	67	1627000000	10406000000	1918000000	2810000000	1822000000	0	44000000	3.8763e10	9.1e7	57	8.55e9	604000000	0.0	988000000	1519000000	-180000000	191000000	1.5586e10	2.074e9	0	0	473000000	58000000	3804000000	-355000000	-3391000000	1259000000	676000000	1259000000	1259000000	1871000000	300000000	2656000000	18	7223000000	520000000	1331000000	-337000000	5000000	-186000000	6315000000	-3000000	1782000000	12	12	8	45	0	6.236e9	83000000	3812000000	3307000000	3.24e8	5.4367e10	4.589e9	6823000000	1.5237e10	3.913e10	5.4367e10	1.4945e10	0.0	2.6	4.84231e8	42.68
24	AEP	2013-12-31	2333000000	22900000	160800000	9	-3616400000	6131000000	9	188000000	119200000	3303000000	6.7223e9	71	2038000000	11444000000	1843100000	2983300000	2093300000	0	58300000	4.0997e10	9.1e7	55	8.0912e9	677700000	0.0	890000000	1423000000	-112200000	117700000	1.6828e10	2.229e9	1000000	0	317000000	-161200000	4096200000	-438700000	-3817800000	1484200000	493700000	1480500000	1473900000	1860000000	226400000	2822500000	19	6749000000	486000000	1383000000	-115000000	115400000	-161200000	5944000000	14500000	1712500000	14	13	10	47	0	6.766e9	83200000	3329800000	2396000000	3.53e8	5.6414e10	4.31e9	6112000000	1.6085e10	4.0329e10	5.6414e10	1.48135e10	0.0	3.04	4.88224e8	46.74
25	AKAM	2012-12-31	176378000	-2108000	7104000	9	-166773000	5195543000	216	201989000	0	2015000	5.299e8	359	21427000	2565000	204163000	321591000	321591000	-89000	0	3.45091e8	7.23701e8	61	8.44047e8	117602000	8.4554e7	0	0	-222277000	59946000	0.0	6.57659e8	0	0	0	-357208000	530020000	-108029000	-779110000	203989000	61438000	203989000	203989000	239199000	406000	314487000	23	39811000	51604000	26566000	-1640000	-34690000	-390060000	49364000	2592000	20962000	23	14	15	359	74744000	-2.2257e9	-96354000	433448000	0	2.35592e8	2.60063e9	7.28384e8	202944000	2.34575e9	2.54873e8	2.60063e9	1.37395e9	-6.24462e8	1.15	1.77382e8	40.91
26	AKAM	2013-12-31	224095000	-67184000	5586000	11	-187964000	4561929000	256	333891000	0	1808000	5.11087e8	391	2325000	7936000	184431000	419554000	419554000	-3655000	0	4.50287e8	7.57368e8	68	1.06684e9	126067000	7.7429e7	0	0	-19750000	52080000	0.0	5.73026e8	0	0	0	131902000	563908000	-115243000	-313108000	293487000	101602000	293487000	293487000	293722000	1843000	413968000	26	67536000	62096000	39071000	-2091000	-41332000	-105394000	57152000	-508000	21547000	27	16	19	391	93879000	-1.93222e9	-96712000	535598000	0	3.40005e8	2.95769e9	1.02971e9	263166000	2.62943e9	3.28254e8	2.95769e9	1.57792e9	0.0	1.65	1.77871e8	47.18
27	ALB	2012-12-31	372309000	-25992000	1229000	17	-280873000	2761000	124	477696000	7364000	889000	1.62031e9	366	0	63368000	99020000	401012000	368212000	-2031000	38067000	1.29657e9	2.76966e8	36	8.98843e8	80433000	9.4464e7	32800000	428145000	-26574000	-9492000	6.86588e8	2.07141e8	98410000	0	-14390000	8280000	488766000	-177294000	-301161000	311536000	90425000	311536000	325846000	422817000	111685000	399783000	16	154836000	78655000	0	85264000	-9124000	6286000	370318000	-2686000	0	15	20	12	254	78919000	1.74468e9	-42427000	308456000	12700000	0.0	3.43729e9	1.40731e9	385009000	1.8336e9	1.60369e9	3.43729e9	2.51915e9	0.0	3.49	8.92653e7	62.12
28	ALB	2013-12-31	411809000	-65906000	-6674000	25	-155346000	9957000	109	477239000	-1810000	801000	1.5438e9	340	0	129188000	107370000	570001000	538442000	12472000	31729000	1.35707e9	2.84203e8	36	8.50471e8	134445000	8.8203e7	31559000	436049000	169000	31452000	1.05431e9	2.12178e8	115415000	0	-18733000	-457000	432859000	-288046000	-157742000	413171000	-88016000	413171000	435726000	491958000	33361000	576675000	24	160229000	77669000	0	116245000	-6149000	-2565000	222160000	9935000	0	22	33	17	240	82246000	1.50036e9	-576745000	158189000	24554000	0.0	3.5848e9	1.48292e9	436363000	1.62736e9	1.95744e9	3.5848e9	2.39427e9	0.0	4.93	8.38075e7	63.39
29	ALK	2012-12-31	806000000	9000000	28000000	22	-518000000	660000000	87	174000000	0	70000000	2.319e9	116	0	889000000	264000000	560000000	514000000	0	0	3.609e9	0.0	50	2.338e9	198000000	0.0	46000000	58000000	-82000000	35000000	8.71e8	3.9e7	0	0	-275000000	20000000	753000000	-88000000	-645000000	316000000	10000000	316000000	316000000	278000000	0	532000000	11	120000000	123000000	534000000	-436000000	216000000	-45000000	823000000	119000000	264000000	11	36	7	112	0	1.127e9	-29000000	1542000000	161000000	1.13e9	5.505e9	1.737e9	1501000000	1.421e9	4.084e9	5.505e9	4.657e9	0.0	4.47	7.06935e7	43.09
30	ALK	2013-12-31	899000000	-19000000	13000000	25	-566000000	606000000	84	80000000	0	1000000	2.355e9	112	0	1044000000	270000000	851000000	816000000	0	0	3.893e9	0.0	54	2.801e9	308000000	0.0	35000000	60000000	-129000000	88000000	7.54e8	0.0	0	0	-161000000	-42000000	981000000	-325000000	-698000000	508000000	-160000000	508000000	508000000	265000000	0	838000000	16	183000000	107000000	564000000	-183000000	23000000	-3000000	431000000	294000000	270000000	16	40	10	108	0	1.607e9	-159000000	1693000000	117000000	1.25e9	5.838e9	1.762e9	1580000000	2.029e9	3.809e9	5.838e9	5.156e9	-2.0e6	7.26	6.99725e7	73.37
⋮	⋮	⋮	⋮	⋮	⋮	⋮	⋮	⋮	⋮	⋮	⋮	⋮	⋮	⋮	⋮	⋮	⋮	⋮	⋮	⋮	⋮	⋮	⋮	⋮	⋮	⋮	⋮	⋮	⋮	⋮	⋮	⋮	⋮	⋮	⋮	⋮	⋮	⋮	⋮	⋮	⋮	⋮	⋮	⋮	⋮	⋮	⋮	⋮	⋮	⋮	⋮	⋮	⋮	⋮	⋮	⋮	⋮	⋮	⋮	⋮	⋮	⋮	⋮	⋮	⋮	⋮	⋮	⋮	⋮	⋮	⋮	⋮	⋮	⋮	⋮	⋮	⋮	⋮

using Statistics
# Normalize the data from the files into matrix B for the model
B = normalize(raw)

# Columns 77 and 78 are the estimated shares outstanding and closing price for each company.
prices = raw[:78]
shares = raw[:77]

# Calculate the market cap of each company.
mkt_cap = prices.*shares
y = mkt_cap;

# Equity Valuation Model

#Least Squares Linear Regression
# Can take ~5 mins to run. If you don't want to run the whole thing, please skip to the next cell.
using JuMP, Ipopt

m = Model(solver=IpoptSolver(print_level=0))
# Variable to hold weights of each fundamental attribute including intercept in column 75
@variable(m, u[1:75])
# Calculate least squares
# Objective to minimize the sum of squares between the companies' current market capitalization (y)
# and the market capitalization predicted by our model (B x u).
@objective(m, Min, sum( (y - B*u).^2 ) )

status = solve(m)
println(status)

# Coeffs is the variable to hold the weight ouputs of the model.
coeffs = getvalue(u)
println("Coefficient weights: ", coeffs)

Optimal
Coefficient weights: [-5.52662e14, 1.16507e10, -7.44934e10, -1.62888e11, 7.7802e12, 7.46124e10, 4.09376e9, 2.17528e11, 2.28959e10, 1.28349e10, 4.64297e12, -9.43487e9, 1.78499e11, -2.5365e11, 2.11437e11, 2.27914e13, -2.26833e13, 6.21952e9, -5.27838e10, 2.11628e12, 7.85458e11, 1.08062e10, 1.91159e12, 3.06959e10, 1.00392e12, -1.47748e12, -1.01068e10, 7.78183e12, 1.63795e11, -2.4329e11, 1.35878e12, -1.48416e11, -6.67235e9, 1.78606e11, -5.54145e11, 3.71803e11, 1.18462e11, -1.38982e12, -1.39441e12, 1.15752e11, 1.74413e12, -4.87292e10, -1.17391e10, 3.88778e10, 2.3876e11, -7.28409e10, 1.90456e11, -1.80126e10, -3.04466e14, -1.10013e9, 1.23101e10, 2.21372e12, -1.92899e11, 2.93459e10, -4.95802e10, 1.15052e11, 1.69296e11, 5.18205e10, 4.04073e9, -3.99459e10, 2.23452e11, 1.75842e10, 3.95502e10, -3.9155e14, 2.73132e11, -2.97284e12, 9.83579e11, 7.49975e14, -1.77718e12, -2.27975e12, 3.97454e12, -6.28953e12, -3.56524e10, -4.62922e9, -1.15785e9]

#If you don't want to run the previous optimization problem, here's the results from when we ran it.
#Please uncomment if you want to use this

#coeffs = [-5.5266e14, 1.16507e10, -7.44934e10, -1.62888e11, 7.7802e12, 7.46124e10, 4.09376e9, 2.17528e11, 2.28959e10, 1.28349e10, 4.64297e12, -9.43487e9, 1.78499e11, -2.5365e11, 2.11437e11, 2.27914e13, -2.26833e13, 6.21953e9, -5.27838e10, 2.11628e12, 7.85458e11, 1.08062e10, 1.91159e12, 3.06959e10, 1.00392e12, -1.47748e12, -1.01067e10, 7.78183e12, 1.63795e11, -2.4329e11, 1.35878e12, -1.48416e11, -6.67235e9, 1.78606e11, -5.54145e11, 3.71803e11, 1.18463e11, -1.38982e12, -1.39441e12, 1.15752e11, 1.74413e12, -4.87292e10, -1.17389e10, 3.88778e10, 2.3876e11, -7.28409e10, 1.90456e11, -1.80126e10, -3.04465e14, -1.10013e9, 1.23101e10, 2.21372e12, -1.92899e11, 2.93459e10, -4.95802e10, 1.15052e11, 1.69296e11, 5.18205e10, 4.04073e9, -3.99459e10, 2.23452e11, 1.75842e10, 3.95502e10, -3.91548e14, 2.73132e11, -2.97284e12, 9.83579e11, 7.49972e14, -1.77718e12, -2.27975e12, 3.97454e12, -6.28953e12, -3.56524e10, -4.62922e9, -1.15785e9];

bias = get_bias_diff(raw_train, coeffs, raw_test);

# Get the difference between expected prices calculated using the weights produced by the Equity Valuation Model and
# actual prices from the data set as a percent for each company.
percent_diffs = get_diff_expected_prices(coeffs, raw_test, 202)
println()
percent_diffs = get_diff_expected_prices(coeffs, raw_test, 2)
;

Calculated valuation for NFLX before bias correction: 2.8286596487435513e9
Expected (calculated) valuation for NFLX with bias correction: 2.296722593876831e10
Actual valuation for NFLX: 2.0527299215543518e10
Bias: -2.0138566290024757e10

Calculated valuation for AAPL before bias correction: 5.561365050864819e11
Expected (calculated) valuation for AAPL with bias correction: 5.3287233404792444e11
Actual valuation for AAPL: 6.1334861327025e11
Bias: 2.3264171038557495e10

Because of the lack of data, if a company’s value is artificially inflated or deflated by public opinion, such as Netflix’s stock, then the model will consistently underestimate or overestimate the stock’s value. Having more data to train the model with would help this issue, but a quicker solution would be to also find a market bias offset value when training the model that accounts for the difference between the models prediction and the actual market capitalization of the company. We are assuming that our model accurately predicts the fundamental value of the company, but we account for the market hype/pessimism by adding/subtracting to the value determined by our model. For example, the equity valuation model predicts Netflix (NFLX) value as $ 2.8 billion, but the actual market valuation is $ 20.5 billion. To account for this perceived potential and hype for Netflix we assign it a bias of nearly $ 20 billion. We also assume that a company’s bias (optimism, pessimism, or neutral) does not change throughout the years in our dataset. This only applies in the short run (since we’re using data that’s within a span of 4 years). But, in the long run, the company’s valuation should accurately be reflected by its fundamental data and shouldn’t need this correction. Another example for a company that is more established and stable can be seen by looking at Apple (AAPL). Our model predicts that Apple’s value is $ 556 billion when the actual market capitalization was $ 532 billion. Therefore, Apple’s bias was much less (in terms of percent) compared to Netflix. This reduced hype can be a result of investors believing that Apple won’t grow or shrink exponentially.

using CSV, LinearAlgebra
# Vector to hold the expected return of each company as a percent.
μ = convert(Vector, CSV.read("./expected-return-risk.csv", header = true)[2])
# Matrix to hold the expected varianve of each company as a percent squared.
Σ = convert(Matrix, CSV.read("./covariance-matrix.csv", header=false))
# Vector to hold the ticker symbol of each company.
tickers = convert(Vector, CSV.read("./expected-return-risk.csv", header = true)[1])  # Tickers

# Get the order of indices which will sort the assets by expected return.
ix = sortperm(μ)
# Variable to hold the number of assets.
nAssets = 306;

# Markowitz Modern Portfolio Model
# # This model finds the optimal long portfolio for one particular choice of λ

using JuMP, Mosek, PyPlot
function opt_long_portfolio(t1, t2)
    m = Model(solver = MosekSolver(LOG=0))
    # Variable to hold investment amounts.
    @variable(m, x[1:nAssets])
    # Investment amounts must be non-negative (cannot sell short assets).
    @constraint(m, max_c[i in 1:nAssets], x[i] >= 0)
    @constraint(m, min_c[i in 1:nAssets], x[i] <= 0.2)
    # You must invest all of the portfolio.
    @constraint(m,sum(x) == 1)

    figure(figsize=(12,8))

    subplot(211)
    # Variable for tradeoff weight for covariance matrix (risk).
    λ1 = t1
    # Variable for percent under/over valued determined by the Equity Valuation Model.
    λ2 = t2

    @objective(m, Min, -dot(μ,x) + λ1*dot(x,Σ*x) - λ2*dot(percent_diffs,x))

    solve(m)
    xsol1 = getvalue(x)

    exp_increase = dot(percent_diffs,xsol1)

    ix1 = findall(xsol1 .> 1e-4 )
    ret = dot(μ,xsol1)
    std = sqrt(dot(xsol1,Σ*xsol1))
    xlim(0,nAssets)
    bar(1:nAssets,xsol1[ix])
    title(string("Optimal long asset selection for λ1=",λ1,"  λ2=",λ2," ( \$\\mu=\$", round(ret,digits=3), "%, \$\\sigma=\$", round(std,digits=2), "% )"));
    tight_layout()
    return (xsol1, ix1, ret, std, exp_increase)
end

opt_long_portfolio (generic function with 1 method)

# Markowitz Modern Portfolio Model
# # This model finds the optimal short portfolio for one particular choice of λ

using JuMP, Mosek, PyPlot
function opt_short_portfolio(t1, t2)
    m = Model(solver = MosekSolver(LOG=0))
    # Variable to hold investment amounts.
    @variable(m, x[1:nAssets])
    # Investment amounts must be negative (selling short assets).
    @constraint(m, max_c[i in 1:nAssets], x[i] <= 0)
    @constraint(m, min_c[i in 1:nAssets], x[i] >= -0.2)
    # You must invest all of the portfolio.
    @constraint(m,sum(x) == -1)

    figure(figsize=(12,8))

    subplot(211)
    # Variable for tradeoff weight for covariance matrix (risk).
    λ1 = t1
    # Variable for percent under/over valued determined by the Equity Valuation Model.
    λ2 = t2

    @objective(m, Min, -dot(μ,x) + λ1*dot(x,Σ*x) - λ2*dot(percent_diffs,x))

    solve(m)
    xsol1 = getvalue(x)

    ix1 = findall(xsol1 .< -1e-4 ) #for short portfolio
    ret = dot(μ,xsol1)
    std = sqrt(dot(xsol1,Σ*xsol1))
    xlim(0,nAssets)
    bar(1:nAssets,xsol1[ix])
    title(string("Optimal short asset selection for λ1=",λ1,"  λ2=",λ2," ( \$\\mu=\$", round(ret,digits=3), "%, \$\\sigma=\$", round(std,digits=2), "% )"));
    tight_layout()
    return (xsol1, ix1, ret, std, exp_increase)
end

opt_short_portfolio (generic function with 1 method)

4. Results and Discussion

4.A. Standard Deviations and Expected Returns of Basket of Equities

using PyPlot
fig = figure(figsize=(12,6))

plot1 = fig[:add_subplot](2,2,1);
xlim(0,nAssets)
plot(μ[ix], linewidth = 1, c = :green)
plot([0,nAssets],[0,0],"k--")
ylabel("Expected return (%)")
title("Expected Daily Return of all Equities")

plot2 = fig[:add_subplot](2,2,2);
xlim(0,nAssets)
plot(sqrt.(diag(Σ))[ix],c = :blue)
ylabel("Standard deviation (%)")
title("Standard Deviation of all Equities")
tight_layout()

fig = figure(figsize=(16,4))
plot3 = fig[:add_subplot](1,2,1);
# plot correlation matrix (shows which assets are correlated with which other assets)
corr = diagm(0 => diag(Σ).^(-1/2))*Σ*diagm(0 => diag(Σ).^(-1/2))
imshow(corr[ix,ix]);colorbar();axis("image")
title("Correlation Matrix of all Equities")

# plot each asset individually
plot4 = fig[:add_subplot](1,2,2);
plot(sqrt.(diag(Σ)), μ, "r.")
xlabel("std deviation (%)")
ylabel("expected return (%)")
title("Expected Daily Return vs. Standard Deviation of all Equities")
tight_layout()
;

The four plots above show the following:

1. Expected Daily Return of all Equities (Top-Left):

This plot shows the expected daily return (%) of all equities that could make up the optimal portfolio. The equities are in sorted order from low to high expected returns which is why the curve is increasing from left to right. The range of expected returns for these 306 equites is from -0.04% to 0.27% per day. It is also noticed that there are very few equities that have a negative expected return. This will greatly reduce the available options to fill the short-only optimal portfolio if we weight the expected return term of the objective relatively more than the other terms.

2. Standard Deviation of all Equities (Top-Right):

This plot shows the daily standard deviation (%) of all equities. The trend of the plot is increasing overall. Since the stocks are sorted in increasing order in terms of their expected returns, it can be hypothesised that stocks with higher variability may also offer higher returns. The range of standard deviations for these 306 equities is from 0.81% to 4.00% per day.

3. Correlation Matrix of all Equities (Bottom-Left):

This plot shows the correlation between each pair of equities. The closer the color is to yellow, the more closely correlated the two stocks are; conversely, the closer the color is to purple, the more independent the two stocks are of each other. With a great number of comparisons, it becomes difficult to extract precise conclusions between any specific pair of stocks. Overall, it appears many of the stocks are fairly correlated (0.4-0.6).

4. Expected Daily Return vs. Standard Deviation of all Equities (Bottom-Right):

This plot reinforces the observation made from plot 2 that it appears that higher returns are associated with higher levels of variation. This can be extrapolated from the weak-moderate positive linear trend.

4.B. Optimal Portfolio Examples - Long Positions

# Optimal Long-only portfolio generated by just using the Markowitz MPT

λ1 = 0.1 #Risk tradeoff
λ2 = 0 #Tradeoff weight on valuation model

(xsol1, ix1, ret, std, exp_increase) = opt_long_portfolio(λ1, λ2)

# For later use in the pareto frontier
xsolm = xsol1
ixm = ix1

check_profits(ix1, 100000, raw_test[77:78], raw_val[77:78], xsol1)

Portfolio returns (percent) 22.555112327358362

22 rows × 5 columns

	Companies	Valuation_when_bought	Valuation_when_sold	Percent_invested	Returns
	Any	Any	Any	Any	Any
1	AAL	3.84482e10	2.82953e10	0.0119159	-314.659
2	AAPL	6.13349e11	6.60003e11	0.0443217	337.131
3	ABC	1.73754e10	2.11682e10	0.00383701	83.7569
4	ALXN	3.66159e10	4.03941e10	0.0374325	386.25
5	AWK	9.55335e9	1.06921e10	0.0389212	463.941
6	AZO	1.79235e10	2.29268e10	0.2	5582.84
7	BIIB	8.02108e10	7.06517e10	0.0713871	-850.751
8	CHD	1.066e10	1.11293e10	0.00182481	8.03466
9	CMG	2.12448e10	1.49162e10	0.0614689	-1831.1
10	DG	1.82128e10	2.0412e10	0.0382425	461.784
11	DLTR	1.10019e10	1.46402e10	0.0381588	1261.9
12	DPS	1.40365e10	1.78012e10	0.0150951	404.862
13	GILD	1.43477e11	1.48128e11	0.0500226	162.178
14	KMB	4.33204e10	4.63867e10	0.0147808	104.62
15	KR	1.87153e10	3.41887e10	0.0689081	5697.14
16	MCK	4.04733e10	5.24131e10	0.0185608	547.549
17	MO	9.75777e10	1.14262e11	0.113628	1942.83
18	NFLX	2.05273e10	4.83713e10	0.0417522	5663.43
19	ORLY	2.0093e10	2.53207e10	0.0475242	1236.47
20	REGN	4.00914e10	5.59636e10	0.0343962	1361.75
21	ROST	1.44685e10	1.89724e10	0.00299209	93.1391
22	SHW	2.54484e10	2.40404e10	0.0448216	-247.991

# Optimal Long-only portfolio generated using the Markowitz MPT and results from Valuation Model

λ1 = 0.1 #Risk tradeoff
λ2 = 0.1 #Tradeoff weight on valuation model

(xsol1, ix1, ret, std, exp_increase) = opt_long_portfolio(λ1, λ2)
check_profits(ix1, 100000, raw_test[77:78], raw_val[77:78], xsol1)

Portfolio returns (percent) 23.09653610517507

9 rows × 5 columns

	Companies	Valuation_when_bought	Valuation_when_sold	Percent_invested	Returns
	Any	Any	Any	Any	Any
1	AZO	1.79235e10	2.29268e10	0.0186759	521.324
2	DLTR	1.10019e10	1.46402e10	0.2	6613.96
3	DUK	5.91375e10	4.96381e10	0.2	-3212.66
4	F	2.35562e10	5.58525e10	0.0990553	13580.8
5	GILD	1.43477e11	1.48128e11	0.0441801	143.236
6	MA	1.00206e11	1.10341e11	0.2	2022.89
7	NFLX	2.05273e10	4.83713e10	0.0326983	4435.33
8	PPL	2.39035e10	2.30462e10	0.2	-717.294
9	RRC	8.89966e9	4.09412e9	0.00539016	-291.052

The first portfolio above shows what an optimized portfolio using purely MPT approach would look like. When using a trade-off weight of 0.1 for the risk of the portfolio, you can see the composition of the optimal portfolio as well as the resulting profit that could have been realized if held for a year (2014 - 2015). The annual returns are calculated as 22.6%.

The second portfolio mirrors the first with the addition of a non-zero weight to the third term of the objective function: maximizing percent difference between expected and actual value of the company (in 2014). When considering the difference in what the company is valued at and what it's current price is, the optimal portfolio generated consisted of mostly different stocks and we were able to achieve a portfolio that returned 23.10%.

In this example, the enhanced returns were nearly negligible; however, we can change the trade-off weights for different results.

4.C. Optimal Portfolio Examples - Short Positions

Note: Negative "Portfolio Returns" for a short porfolio denote a profit (since stocks were sold at higher level than they were later covered). Positive returns denote a loss on investement. We are not including potential borrowing rates since the investor does not own stock when shorting assets and must borrow them.

# Optimal Short-only portfolio generated by just using the Markowitz MPT

λ1 = 0.1 #Risk tradeoff
λ2 = 0 #Tradeoff weight on valuation model

(xsol1, ix1, ret, std, exp_increase) = opt_short_portfolio(λ1, λ2)
check_profits(ix1, 100000, raw_test[77:78], raw_val[77:78], xsol1)

Portfolio returns (percent) 2.2990736119473786

15 rows × 5 columns

	Companies	Valuation_when_bought	Valuation_when_sold	Percent_invested	Returns
	Any	Any	Any	Any	Any
1	CAG	1.33069e10	1.62548e10	0.0281898	624.488
2	CLX	1.18332e10	1.35577e10	0.0443318	646.04
3	CPB	1.39224e10	1.48599e10	0.0498849	335.941
4	DGX	9.73508e9	1.02517e10	0.0735371	390.229
5	ETR	1.60312e10	1.08226e10	0.068168	-2214.81
6	FTR	6.82392e9	3.15628e9	0.0211217	-1135.23
7	MCD	9.19187e10	1.11015e11	0.0492133	1022.4
8	NEM	9.41294e9	9.20419e9	0.0939865	-208.438
9	PCG	2.51459e10	2.60954e10	0.0992156	374.632
10	PEP	1.46656e11	1.47748e11	0.00588615	4.3824
11	PPL	2.39035e10	2.30462e10	0.105759	-379.301
12	SO	4.55445e10	4.35687e10	0.127116	-551.448
13	SPLS	8.58863e9	1.09607e10	0.0549535	1517.75
14	T	1.81838e11	1.93756e11	0.0568821	372.824
15	WMT	2.44188e11	2.74266e11	0.121747	1499.6

# Optimal Short-only portfolio generated using the Markowitz MPT and results from Valuation Model

λ1 = 0.1 #Risk tradeoff
λ2 = 1 #Tradeoff weight on valuation model

(xsol1, ix1, ret, std, exp_increase) = opt_short_portfolio(λ1, λ2)
check_profits(ix1, 100000, raw_test[77:78], raw_val[77:78], xsol1)

Portfolio returns (percent) 28.01032203318026

8 rows × 5 columns

	Companies	Valuation_when_bought	Valuation_when_sold	Percent_invested	Returns
	Any	Any	Any	Any	Any
1	BBY	8.02774e9	1.22951e10	0.000914173	48.5949
2	CF	2.19106e10	9.13622e9	0.115879	-6756.0
3	FCX	1.02154e10	7.2764e9	0.2	-5754.0
4	FTR	6.82392e9	3.15628e9	0.2	-10749.4
5	HOLX	7.0238e9	1.12e10	0.0314616	1870.64
6	OKE	1.04263e10	5.16336e9	0.0516852	-2608.93
7	R	4.89685e9	3.01217e9	0.2	-7697.53
8	SYMC	1.37763e10	1.6281e10	0.2	3636.28

The first portfolio above shows what an optimized portfolio using purely MPT approach would look like. When using a trade-off weight of 0.1 for the risk of the portfolio, you can see the composition of the optimal portfolio as well as the resulting profit that could have been realized if you sold short and held all of these positions for a year (2014 - 2015). The annual returns are calculated as -2.3%. This is likely caused by the fact that there are only a few stocks in our dataset that have negative expected returns.

The second portfolio mirrors the first with the addition of a non-zero weight to the third term of the objective function: minimizing percent difference between expected and actual value of the company (in 2014). When considering the difference in what the company is valued at and what it's current price is, the optimal portfolio generated consisted of mostly different stocks and we were able to achieve a portfolio that returned 27.9%.

In this example, the enhanced returns were quite signifigant and the addition of the valuation model proved to be quite successful. Again, we can change the trade-off weights for different results.

4.D. Pareto Frontier

# compute optimal tradeoff curve (this may take a few minutes)
N = 50
ret = zeros(N)
std = zeros(N)
lambda_values = exp10.(range(-3,stop=4,length=N))

m = Model(solver = MosekSolver(LOG=0))

@variable(m, x[1:nAssets])
@constraint(m, max_c[i in 1:nAssets], x[i] >= 0)
@constraint(m,sum(x) == 1)

for (i,λ) in enumerate(lambda_values)
    @objective(m, Min, -dot(μ,x) + λ*dot(x,Σ*x) )
    solve(m)
    xsol = getvalue(x)
    ret[i] = dot(μ,xsol)
    std[i] = sqrt(dot(xsol,Σ*xsol))
end

# plot tradeoff curve
plot(std,ret,"b-")
plot(sqrt.(diag(Σ)), μ, "k.", markersize=12)
plot(sqrt.(diag(Σ))[ixm], μ[ixm], "r.", markersize=12)  # low-risk portfolio
xlabel("std deviation (%)")
ylabel("expected return (%)")
#axis([1.5,4.5,0,.4]);
tight_layout()

The above plot shows the expected returns of the basket of stocks versus their respective standard deviation (or risk). The blue curve shows the trade off of different weights between maximizing returns and minimizing risk. Ideally, we would prefer stocks that have a high expected return coupled with a low risk. Since stocks that generate larger returns on average tend to pose greater risk, the investor may choose a tradeoff based on their risk tolerance.

The points in red are the stocks chosen for an optimal Markowitz MPT portfolio with the trade off of 0.1 for risk (The first long portfolio we generated above).

4.E Accuracy of Models and Possible Improvements

As discussed near the outset of this report, our group desired to build a project that could give some clear reflection of reality. There were a few instances where we encountered events that could lead to possible flaws in the conclusions that were determined.

The first potential issue was that we needed a sufficient amount of data for the Equity Valuation Model (LS Regression) to solve to optimality. This had two effects on our model. First, with the limited amount of data that we had, it became necessary to use the entire dataset (1,230 samples) to determine the optimal weights of each fundamental attribute. Consequently, the test data that is used to build the optimal portfolios in sections 4.b and 4.c are samples that were also used to build the model. This use of the data for the training set and the test set can lead to overfitting and results that may not be able to be achieved in practice. A potential improvement is to collect more data and have the training and test set completely independent of each other. The second problem lies with how people evaluate popular stocks. Because of the lack of data, if a company’s value is artificially inflated or deflated by public opinion, such as Apple’s stock, then the model will consistently underestimate or overestimate the stock’s value. Having more data to train the model with would help this issue.

Another potential issue that we noticed was that the data that we collected to build the optimal portfolios was mostly comprised of stocks that had a positive expected return. When constructing the short-only portfolio, we were then forced to give the expected return portion of the objective value less weight so that the model was not forced to choose between less than 5 stocks to generate a portfolio. To improve this, additional stock data would need to be collected so that there were more equities with negative expected returns.

We would also like to note something about the robustness of our model. The years that our sample data was collected ranged between 2012 to 2016. These years lie within a bull-market that followed the 2007-2008 financial crisis; thus, our model may not be suitable for all market conditions.

5. Conclusion

This optimization project explores the task of attempting to accurately value a company’s stock, and then use that valuation in a Markowitz portfolio in attempts to generate portfolios that will have superior returns coupled with reasonable risk. Given that many investors have different appetites for potential risks and possible rewards, we have provided a trade off curve to explore the interaction between multiple objectives: maximizing expected returns, and minimizing risk. A tradeoff surface exists when including the maximizing the amount that a company is undervalued (Minimize all objectives for short portfolio) objective but we couldn't generate a plot with meaningful insight, so it was excluded from the discussion. The project uses real data so that the derived results may be more significant and applicable in practice. In the future, we would like to have access to more data and from multiple time periods to increase the robustness of the proposed models. It may be possible to explore the plausibility of using different models during different market conditions, but these are just a few of the possible extensions to this project.

This project shows how optimization can be applied to financial markets and business valuation. We realize that many large corporations likely use much more sophisticated models than the ones proposed in this report; however, we believe that these models are adequate at introducing the lay-investor to optimization in finance and also do a fine job at accomplishing the objectives that we had laid out.

Disclaimer: The models and discussion in this report is not to be taken as investment advice. Use of this model in practice may lead to substantial losses in which our group is not responsible for. The models only exist for theoretical exploration and all conjecture is purely academic. Hypothetical or simulated performance is not indicative of any future results.

6. References

Chen, James. “Modern Portfolio Theory (MPT).” Investopedia, Investopedia, 17 Apr. 2019, www.investopedia.com/terms/m/modernportfoliotheory.asp.

Kenton, Will. “Markowitz Efficient Set.” Investopedia, Investopedia, 12 Mar. 2019, www.investopedia.com/terms/m/markowitzefficientset.asp.

McClure, Ben. “What Are Stock Fundamentals?” Investopedia, Investopedia, 2 Apr. 2019, www.investopedia.com/articles/fundamental/03/022603.asp.

Surz, Ron. “U.S. Stock Market Is Biggest & Most Expensive In World, But U.S. Economy Is Not The Most Productive.” NASDAQ.com, Nasdaq, 2 Apr. 2018, www.nasdaq.com/article/us-stock-market-is-biggest-most-expensive-in-world-but-us-economy-is-not-the-most-productive-cm942558.