Finding the most efficient portfolio¶

Now with the help of our computer, we will consider ALL the different sets of weights possible our stocks to reach a portfolio that has the highest Sharpe ratio. That is, among these random portfolios, I would like to find the most efficient ones (According to Sharpe). This notebook is a sequel to previous notes where I am using a dataset of 14 securities. Thus I suggest you read the previous notes to fully understand what is going on here.

To do so, we need to give a series of instructions to the computer to:

  1. Choose a random set of weights each time
  2. Create a portfolio with them
  3. Measure the risk
  4. Calculate the Sharpe ratio
  5. Finally, save it in a list so it can be compared with all other portfolios made through the same process
In [ ]:
# This is the package for downloading US stock data
import yfinance as yf, pandas as pd, numpy as np, datetime as dt
import matplotlib as mpl, matplotlib.pyplot as plt

Importing an Excel File¶

To import an Excel file in Python, I will first create a variable "main_data" and assign to it (with the help of Pandas) a transformed version of the Excel file we created in the last Notebook "8_stock_returns.xlsx".

Note: the Excel file and this Jupyter notebook should be located in the same folder. Otherwise, the process of importing the excel file may require extra steps of code.

In [ ]:
main_data = pd.read_excel('returns_cleaned.xlsx', index_col='Date')


# also drop the the columns (Port_1 & Risk Fee) since I do not need them in my task here
main_data = main_data.drop(columns=['Port_1', 'Risk Free'])

main_data.head()
Out[ ]:
AMZN BA CVX DIS GOLD HSY INTC K KO M MMM MSFT NVDA ^W5000
Date
2000-02 0.066796 -0.169944 -0.106876 -0.063683 -0.003817 0.033823 0.142136 0.043815 -0.153428 -0.118619 -0.058078 -0.086845 0.726813 0.021192
2000-03 -0.027223 0.027196 0.248125 0.213236 -0.038314 0.115744 0.167938 0.028577 -0.034704 0.151618 0.010757 0.188811 0.320068 0.058114
2000-04 -0.176306 0.049587 -0.079108 0.057576 0.071714 -0.066667 -0.038844 -0.058252 0.010433 -0.195266 -0.021877 -0.343529 0.054929 -0.052775
2000-05 -0.124575 -0.015748 0.085904 -0.032951 0.078067 0.140109 -0.016757 0.252577 0.129630 0.132353 -0.010101 -0.103047 0.280505 -0.036091
2000-06 -0.248383 0.074325 -0.076026 -0.080000 0.008687 -0.060264 0.072445 -0.012356 0.076112 -0.123377 -0.025796 0.278721 0.113911 0.043327
In [ ]:
main_data.describe()
Out[ ]:
AMZN BA CVX DIS GOLD HSY INTC K KO M MMM MSFT NVDA ^W5000
count 287.000000 287.000000 287.000000 287.000000 287.000000 287.000000 287.000000 287.000000 287.000000 287.000000 287.000000 287.000000 287.000000 287.000000
mean 0.021791 0.012425 0.009857 0.006993 0.008019 0.011105 0.006905 0.006957 0.006123 0.010586 0.007103 0.012066 0.037451 0.005589
std 0.129926 0.095598 0.069003 0.076331 0.114357 0.057571 0.097305 0.049571 0.050752 0.129830 0.060703 0.081098 0.174042 0.045973
min -0.411552 -0.454664 -0.214625 -0.267794 -0.381056 -0.177236 -0.444732 -0.138636 -0.172743 -0.628874 -0.157037 -0.343529 -0.486551 -0.177406
25% -0.048718 -0.042805 -0.029320 -0.034121 -0.065293 -0.025150 -0.047196 -0.021066 -0.024001 -0.061762 -0.028618 -0.037006 -0.054362 -0.021149
50% 0.021677 0.015899 0.008623 0.005748 0.007592 0.008660 0.007672 0.009468 0.008546 0.011221 0.010560 0.017244 0.030835 0.011240
75% 0.087617 0.070993 0.046279 0.050941 0.072783 0.044363 0.062263 0.035843 0.038312 0.077280 0.039402 0.055028 0.122156 0.034368
max 0.621776 0.459312 0.269666 0.248734 0.426362 0.275952 0.337429 0.252577 0.141928 0.644122 0.213949 0.407781 0.826237 0.133806
In [ ]:
stock_names = [x for x in main_data.columns]
stock_means = np.array([main_data[x].mean() for x in stock_names]).T
stock_cov = np.array(main_data[stock_names].cov())
dataset_columns = stock_names + ['Port_Return', 'Port_Risk', 'Port_Sharpe']
r_f = 0.005
no_stocks = len(stock_names)
In [ ]:
main_list = [] 

for i in range(10000000):
    
    weight = np.array([np.random.random_sample() for x in stock_names])
    weight /= np.sum(weight)
    entry = [weight.dot(stock_means)]
    entry.append(weight.dot(stock_cov).dot(weight.T)**0.5)
    sharpe = (entry[-2] - r_f ) / entry[-1]
    entry.append(sharpe)
    main_list.append(np.append(weight, entry))

print('done!!!')

done!!!
In [ ]:
main_matrix = np.vstack(main_list)

main_matrix[:7]
Out[ ]:
array([[0.08952249, 0.1237281 , 0.09806072, 0.08110084, 0.0997132 ,
        0.08158651, 0.08989499, 0.00660169, 0.0151624 , 0.10605358,
        0.05307233, 0.06608257, 0.0041403 , 0.08528027, 0.0104156 ,
        0.05132228, 0.10552137],
       [0.05307114, 0.09823329, 0.02633822, 0.13302875, 0.06579368,
        0.10195316, 0.13875912, 0.07471172, 0.03135454, 0.00455951,
        0.02169596, 0.05511066, 0.07023474, 0.1251555 , 0.01109369,
        0.04957423, 0.12292059],
       [0.04419427, 0.12961178, 0.01164222, 0.13435415, 0.13914584,
        0.10364673, 0.09756549, 0.03148035, 0.06038125, 0.14337519,
        0.04890231, 0.02915401, 0.01648726, 0.01005915, 0.0100475 ,
        0.05137652, 0.09824526],
       [0.08085281, 0.00766668, 0.00561845, 0.03279166, 0.00033228,
        0.11513434, 0.01633928, 0.15791356, 0.13349173, 0.0208246 ,
        0.03281222, 0.13272392, 0.11264784, 0.15085063, 0.01256861,
        0.04753374, 0.15922604],
       [0.02992966, 0.01160897, 0.0241734 , 0.14091895, 0.0794554 ,
        0.01960431, 0.11477198, 0.07319995, 0.18034245, 0.11808549,
        0.01886189, 0.13651088, 0.0375537 , 0.01498296, 0.00980218,
        0.04965786, 0.09670536],
       [0.06936717, 0.07466782, 0.06707473, 0.11496441, 0.07953203,
        0.11240064, 0.01256041, 0.02881488, 0.03830221, 0.10925284,
        0.07255171, 0.04336411, 0.09058646, 0.08656057, 0.01238356,
        0.05171059, 0.14278626],
       [0.02401006, 0.04123624, 0.09985102, 0.0852718 , 0.03947427,
        0.08406158, 0.14704075, 0.01334256, 0.12612358, 0.00170426,
        0.11442047, 0.12646769, 0.02193327, 0.07506246, 0.0093441 ,
        0.04506295, 0.09640072]])
In [ ]:
efficient_port = pd.DataFrame(main_matrix[1:], columns = dataset_columns)

efficient_port.describe().apply(lambda s: s.apply('{0:.5f}'.format))
Out[ ]:
AMZN BA CVX DIS GOLD HSY INTC K KO M MMM MSFT NVDA ^W5000 Port_Return Port_Risk Port_Sharpe
count 9999999.00000 9999999.00000 9999999.00000 9999999.00000 9999999.00000 9999999.00000 9999999.00000 9999999.00000 9999999.00000 9999999.00000 9999999.00000 9999999.00000 9999999.00000 9999999.00000 9999999.00000 9999999.00000 9999999.00000
mean 0.07141 0.07143 0.07146 0.07139 0.07143 0.07145 0.07143 0.07142 0.07143 0.07143 0.07143 0.07141 0.07145 0.07142 0.01164 0.05068 0.13037
std 0.04116 0.04117 0.04117 0.04116 0.04118 0.04115 0.04118 0.04117 0.04118 0.04115 0.04118 0.04117 0.04117 0.04116 0.00131 0.00426 0.01932
min 0.00000 0.00000 0.00000 0.00000 0.00000 0.00000 0.00000 0.00000 0.00000 0.00000 0.00000 0.00000 0.00000 0.00000 0.00728 0.03525 0.05057
25% 0.03704 0.03705 0.03706 0.03701 0.03702 0.03710 0.03703 0.03704 0.03703 0.03706 0.03703 0.03701 0.03706 0.03706 0.01067 0.04770 0.11688
50% 0.07141 0.07142 0.07148 0.07138 0.07141 0.07145 0.07143 0.07141 0.07143 0.07142 0.07143 0.07139 0.07145 0.07141 0.01164 0.05047 0.13175
75% 0.10352 0.10355 0.10358 0.10348 0.10357 0.10354 0.10356 0.10354 0.10355 0.10353 0.10355 0.10355 0.10357 0.10352 0.01258 0.05341 0.14450
max 0.30460 0.42600 0.35769 0.33866 0.36738 0.32222 0.34830 0.35433 0.32917 0.31468 0.39217 0.33261 0.32792 0.38799 0.02018 0.08534 0.20671
In [ ]:
efficient_port.head()
Out[ ]:
AMZN BA CVX DIS GOLD HSY INTC K KO M MMM MSFT NVDA ^W5000 Port_Return Port_Risk Port_Sharpe
0 0.053071 0.098233 0.026338 0.133029 0.065794 0.101953 0.138759 0.074712 0.031355 0.004560 0.021696 0.055111 0.070235 0.125156 0.011094 0.049574 0.122921
1 0.044194 0.129612 0.011642 0.134354 0.139146 0.103647 0.097565 0.031480 0.060381 0.143375 0.048902 0.029154 0.016487 0.010059 0.010047 0.051377 0.098245
2 0.080853 0.007667 0.005618 0.032792 0.000332 0.115134 0.016339 0.157914 0.133492 0.020825 0.032812 0.132724 0.112648 0.150851 0.012569 0.047534 0.159226
3 0.029930 0.011609 0.024173 0.140919 0.079455 0.019604 0.114772 0.073200 0.180342 0.118085 0.018862 0.136511 0.037554 0.014983 0.009802 0.049658 0.096705
4 0.069367 0.074668 0.067075 0.114964 0.079532 0.112401 0.012560 0.028815 0.038302 0.109253 0.072552 0.043364 0.090586 0.086561 0.012384 0.051711 0.142786
In [ ]:
with plt.style.context('ggplot'):

    efficient_port.plot.scatter(x='Port_Risk',
                                y = 'Port_Return',
                                c= 'Port_Sharpe',
                                s=0.75,
                                colormap='Reds',
                                title= 'Alternative Portfolios & Their Sharpe Ratios',
                                xlabel= "Portfolio Risk (Standard Deviation)",
                                ylabel= 'Portfolio Return (%)',
                                figsize=(14,7))
    

plt.show()
No description has been provided for this image
In [ ]:
# What is the highest Sharpe ratio in this table?
efficient_port['Port_Sharpe'].max()
Out[ ]:
0.2067120020682262
In [ ]:
efficient_port['Port_Sharpe'].idxmax()
Out[ ]:
8835253
In [ ]:
efficient_port.iloc[8835253]
Out[ ]:
AMZN           0.184954
BA             0.038137
CVX            0.005322
DIS            0.000620
GOLD           0.078891
HSY            0.234023
INTC           0.005338
K              0.014769
KO             0.041534
M              0.018769
MMM            0.077062
MSFT           0.057378
NVDA           0.215487
^W5000         0.027718
Port_Return    0.017850
Port_Risk      0.062163
Port_Sharpe    0.206712
Name: 8835253, dtype: float64
In [ ]:
# What are stock the weights for this highest Sharpe ratio?
efficient_port.iloc[efficient_port['Port_Sharpe'].idxmax()]
Out[ ]:
AMZN           0.184954
BA             0.038137
CVX            0.005322
DIS            0.000620
GOLD           0.078891
HSY            0.234023
INTC           0.005338
K              0.014769
KO             0.041534
M              0.018769
MMM            0.077062
MSFT           0.057378
NVDA           0.215487
^W5000         0.027718
Port_Return    0.017850
Port_Risk      0.062163
Port_Sharpe    0.206712
Name: 8835253, dtype: float64