Блог

Есть идеи о том, как я могу получить эти значения в отдельных столбцах? Посмотрите на картинку ниже.

 import pandas as pd  
import numpy as np
import matplotlib.pyplot as plt
import seaborn as sns
import quandl
import scipy.optimize as sco
from pandas.plotting import register_matplotlib_converters
register_matplotlib_converters()

mylist=[]

for i in range(0,3):
    table = pd.read_excel(r"d.xlsx",skiprows=i,usecols="b,c,d,e,f,g,h,i,j,k,l,m,n,o,p")
    table1 = np.array(table.values)

    returns = np.log(table) - np.log(table.shift(1))
    mean_returns = returns.mean()
    cov_matrix = returns.cov()
    num_portfolios = 1
    risk_free_rate = 0.0178

    def portfolio_annualised_performance(weights, mean_returns, cov_matrix):
        returns = np.sum(mean_returns*weights ) *252
        std = np.sqrt(np.dot(weights.T, np.dot(cov_matrix, weights))) * np.sqrt(252)
        return std, returns

    def random_portfolios(num_portfolios, mean_returns, cov_matrix, risk_free_rate):
        results = np.zeros((3,num_portfolios))
        weights_record = []
        for i in range(num_portfolios):
            weights = np.random.random(15)
            weights /= np.sum(weights)      
            weights_record.append(weights)
            portfolio_std_dev, portfolio_return = portfolio_annualised_performance(weights, mean_returns, cov_matrix)
            results[0,i] = portfolio_std_dev
            results[1,i] = portfolio_return
            results[2,i] = (portfolio_return - risk_free_rate) / portfolio_std_dev
        return results, weights_record


    def neg_sharpe_ratio(weights, mean_returns, cov_matrix, risk_free_rate):
        p_var, p_ret = portfolio_annualised_performance(weights, mean_returns, cov_matrix)
        return -(p_ret - risk_free_rate) / p_var

    def max_sharpe_ratio(mean_returns, cov_matrix, risk_free_rate):
        num_assets = len(mean_returns)
        args = (mean_returns, cov_matrix, risk_free_rate)
        constraints = ({'type': 'eq', 'fun': lambda x: np.sum(x) - 1})
        bound = (0.0,1.0)
        bounds = tuple(bound for asset in range(num_assets))
        result = sco.minimize(neg_sharpe_ratio, num_assets*[1./num_assets,], args=args,
                            method='SLSQP', bounds=bounds, constraints=constraints)
        return result

    def portfolio_volatility(weights, mean_returns, cov_matrix):
        return portfolio_annualised_performance(weights, mean_returns, cov_matrix)[0]

    def min_variance(mean_returns, cov_matrix):
        num_assets = len(mean_returns)
        args = (mean_returns, cov_matrix)
        constraints = ({'type': 'eq', 'fun': lambda x: np.sum(x) - 1})
        bound = (0.0,0.15)
        bounds = tuple(bound for asset in range(num_assets))

        result = sco.minimize(portfolio_volatility, num_assets*[1./num_assets,], args=args,
                            method='SLSQP', bounds=bounds, constraints=constraints)

        return result

    def efficient_return(mean_returns, cov_matrix, target):
        num_assets = len(mean_returns)
        args = (mean_returns, cov_matrix)

        def portfolio_return(weights):
            return portfolio_annualised_performance(weights, mean_returns, cov_matrix)[1]

        constraints = ({'type': 'eq', 'fun': lambda x: portfolio_return(x) - target},
                    {'type': 'eq', 'fun': lambda x: np.sum(x) - 1})
        bounds = tuple((0,1) for asset in range(num_assets))
        result = sco.minimize(portfolio_volatility, num_assets*[1./num_assets,], args=args, method='SLSQP', bounds=bounds, constraints=constraints)
        return result


    def efficient_frontier(mean_returns, cov_matrix, returns_range):
        efficients = []
        for ret in returns_range:
            efficients.append(efficient_return(mean_returns, cov_matrix, ret))
        return efficients

    def display_calculated_ef_with_random(mean_returns, cov_matrix, num_portfolios, risk_free_rate):
        results, _ = random_portfolios(num_portfolios,mean_returns, cov_matrix, risk_free_rate)

        max_sharpe = max_sharpe_ratio(mean_returns, cov_matrix, risk_free_rate)
        sdp, rp = portfolio_annualised_performance(max_sharpe['x'], mean_returns, cov_matrix)
        max_sharpe_allocation = pd.DataFrame(max_sharpe.x,index=table.columns,columns=['allocation'])
        max_sharpe_allocation.allocation = [round(i*100,2)for i in max_sharpe_allocation.allocation]
        max_sharpe_allocation = max_sharpe_allocation.T

        min_vol = min_variance(mean_returns, cov_matrix)
        sdp_min, rp_min = portfolio_annualised_performance(min_vol['x'], mean_returns, cov_matrix)
        min_vol_allocation = pd.DataFrame(min_vol.x,columns=['allocation'])
        min_vol_allocation.allocation = [round(i*100,2)for i in min_vol_allocation.allocation]
        min_vol_allocation = min_vol_allocation.T


        print (min_vol_allocation)
        mylist.append(min_vol_allocation)


        plt.figure(figsize=(10, 7))
        plt.scatter(results[0,:],results[1,:],c=results[2,:],cmap='YlGnBu', marker='o', s=10, alpha=0.3)
        plt.colorbar()
        plt.scatter(sdp,rp,marker='*',color='r',s=500, label='Maximum Sharpe ratio')
        plt.scatter(sdp_min,rp_min,marker='*',color='g',s=500, label='Minimum volatility')

        target = np.linspace(rp_min, 0.32, 50)
        efficient_portfolios = efficient_frontier(mean_returns, cov_matrix, target)
        plt.plot([p['fun'] for p in efficient_portfolios], target, linestyle='-.', color='black', label='efficient frontier')
        plt.title('Calculated Portfolio Optimization based on Efficient Frontier')
        plt.xlabel('annualised volatility')
        plt.ylabel('annualised returns')
        plt.legend(labelspacing=0.8)
  

завершите цикл здесь.

 mylist.append(display_calculated_ef_with_random(mean_returns, cov_matrix, num_portfolios, risk_free_rate))


df = pd.DataFrame(mylist)
df.to_excel("test.xlsx")
  

Вот вывод из кода:

              0     1     2    3     4      5     6     7     8     9     10    11    12    13   14
allocation  0.0  3.52  0.15  2.4  0.59  12.59  6.04  15.0  8.52  3.64  2.55  15.0  15.0  15.0  0.0
             0     1     2    3     4      5     6     7     8     9     10    11    12    13   14
allocation  0.0  3.52  0.15  2.4  0.59  12.59  6.04  15.0  8.52  3.64  2.55  15.0  15.0  15.0  0.0
             0     1     2    3     4      5     6     7     8     9     10    11    12    13   14
allocation  0.0  3.51  0.15  2.4  0.59  12.58  6.04  15.0  8.55  3.63  2.55  15.0  15.0  15.0  0.0
  

Результат файла Excel:

Есть идеи о том, как я могу получить эти значения в отдельных столбцах? Все значения перечислены в столбце B в таблице Excel.

Есть идеи о том, как я могу получить эти значения в отдельных столбцах? Все значения перечислены в столбце B в таблице Excel.