{ "cells": [ { "cell_type": "markdown", "metadata": {}, "source": [ "# Import packages" ] }, { "cell_type": "code", "execution_count": 27, "metadata": {}, "outputs": [], "source": [ "import pandas as pd\n", "import numpy as np\n", "from pypfopt.efficient_frontier import EfficientFrontier\n", "from pypfopt import risk_models\n", "from pypfopt.risk_models import CovarianceShrinkage\n", "from pypfopt import expected_returns\n", "from datetime import datetime\n", "from pandas.tseries.offsets import BDay" ] }, { "cell_type": "code", "execution_count": 28, "metadata": {}, "outputs": [], "source": [ "import time\n", "import pickle" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "# 1. Read Input Data" ] }, { "cell_type": "code", "execution_count": 29, "metadata": {}, "outputs": [ { "name": "stderr", "output_type": "stream", "text": [ "/home/ubuntu/anaconda3/lib/python3.6/site-packages/numpy/lib/arraysetops.py:568: FutureWarning: elementwise comparison failed; returning scalar instead, but in the future will perform elementwise comparison\n", " mask |= (ar1 == a)\n" ] } ], "source": [ "df_price = pd.read_csv(\"Data/1-sp500_adj_price.csv\",index_col=0)" ] }, { "cell_type": "code", "execution_count": 30, "metadata": {}, "outputs": [ { "data": { "text/plain": [ "(6438964, 3)" ] }, "execution_count": 30, "metadata": {}, "output_type": "execute_result" } ], "source": [ "df_price.shape" ] }, { "cell_type": "code", "execution_count": 31, "metadata": {}, "outputs": [ { "data": { "text/html": [ "<div>\n", "<style scoped>\n", " .dataframe tbody tr th:only-of-type {\n", " vertical-align: middle;\n", " }\n", "\n", " .dataframe tbody tr th {\n", " vertical-align: top;\n", " }\n", "\n", " .dataframe thead th {\n", " text-align: right;\n", " }\n", "</style>\n", "<table border=\"1\" class=\"dataframe\">\n", " <thead>\n", " <tr style=\"text-align: right;\">\n", " <th></th>\n", " <th>datadate</th>\n", " <th>tic</th>\n", " <th>adj_price</th>\n", " </tr>\n", " </thead>\n", " <tbody>\n", " <tr>\n", " <th>1</th>\n", " <td>19900102</td>\n", " <td>ADCT</td>\n", " <td>4.074244</td>\n", " </tr>\n", " <tr>\n", " <th>2</th>\n", " <td>19900103</td>\n", " <td>ADCT</td>\n", " <td>4.046900</td>\n", " </tr>\n", " <tr>\n", " <th>3</th>\n", " <td>19900104</td>\n", " <td>ADCT</td>\n", " <td>3.964869</td>\n", " </tr>\n", " <tr>\n", " <th>4</th>\n", " <td>19900105</td>\n", " <td>ADCT</td>\n", " <td>3.992212</td>\n", " </tr>\n", " <tr>\n", " <th>5</th>\n", " <td>19900108</td>\n", " <td>ADCT</td>\n", " <td>3.937525</td>\n", " </tr>\n", " </tbody>\n", "</table>\n", "</div>" ], "text/plain": [ " datadate tic adj_price\n", "1 19900102 ADCT 4.074244\n", "2 19900103 ADCT 4.046900\n", "3 19900104 ADCT 3.964869\n", "4 19900105 ADCT 3.992212\n", "5 19900108 ADCT 3.937525" ] }, "execution_count": 31, "metadata": {}, "output_type": "execute_result" } ], "source": [ "df_price.head()" ] }, { "cell_type": "code", "execution_count": 32, "metadata": {}, "outputs": [], "source": [ "selected_stock = pd.read_csv(\"Data/2-portfolio_data/stocks_selected_total_user8.csv\")" ] }, { "cell_type": "code", "execution_count": 33, "metadata": {}, "outputs": [ { "data": { "text/plain": [ "(12932, 3)" ] }, "execution_count": 33, "metadata": {}, "output_type": "execute_result" } ], "source": [ "selected_stock.shape" ] }, { "cell_type": "code", "execution_count": 34, "metadata": {}, "outputs": [ { "data": { "text/html": [ "<div>\n", "<style scoped>\n", " .dataframe tbody tr th:only-of-type {\n", " vertical-align: middle;\n", " }\n", "\n", " .dataframe tbody tr th {\n", " vertical-align: top;\n", " }\n", "\n", " .dataframe thead th {\n", " text-align: right;\n", " }\n", "</style>\n", "<table border=\"1\" class=\"dataframe\">\n", " <thead>\n", " <tr style=\"text-align: right;\">\n", " <th></th>\n", " <th>tic</th>\n", " <th>predicted_return</th>\n", " <th>trade_date</th>\n", " </tr>\n", " </thead>\n", " <tbody>\n", " <tr>\n", " <th>0</th>\n", " <td>EOG</td>\n", " <td>0.033723</td>\n", " <td>19950601</td>\n", " </tr>\n", " <tr>\n", " <th>1</th>\n", " <td>EQT</td>\n", " <td>0.037745</td>\n", " <td>19950601</td>\n", " </tr>\n", " <tr>\n", " <th>2</th>\n", " <td>HES</td>\n", " <td>0.051450</td>\n", " <td>19950601</td>\n", " </tr>\n", " <tr>\n", " <th>3</th>\n", " <td>NFX</td>\n", " <td>0.030283</td>\n", " <td>19950601</td>\n", " </tr>\n", " <tr>\n", " <th>4</th>\n", " <td>OKE</td>\n", " <td>0.041020</td>\n", " <td>19950601</td>\n", " </tr>\n", " </tbody>\n", "</table>\n", "</div>" ], "text/plain": [ " tic predicted_return trade_date\n", "0 EOG 0.033723 19950601\n", "1 EQT 0.037745 19950601\n", "2 HES 0.051450 19950601\n", "3 NFX 0.030283 19950601\n", "4 OKE 0.041020 19950601" ] }, "execution_count": 34, "metadata": {}, "output_type": "execute_result" } ], "source": [ "selected_stock.head()" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "# 2. Get trade date" ] }, { "cell_type": "code", "execution_count": 35, "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "Number of unique stocks selected: 982\n" ] } ], "source": [ "print(\"Number of unique stocks selected: \", len(selected_stock.tic.unique()))" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [] }, { "cell_type": "code", "execution_count": 36, "metadata": {}, "outputs": [], "source": [ "all_date=df_price.datadate.unique()" ] }, { "cell_type": "code", "execution_count": 37, "metadata": {}, "outputs": [ { "data": { "text/plain": [ "7155" ] }, "execution_count": 37, "metadata": {}, "output_type": "execute_result" } ], "source": [ "len(all_date)" ] }, { "cell_type": "code", "execution_count": 38, "metadata": {}, "outputs": [], "source": [ "trade_date=selected_stock.trade_date.unique()" ] }, { "cell_type": "code", "execution_count": 39, "metadata": {}, "outputs": [ { "data": { "text/plain": [ "array([19950601, 19950901, 19951201, 19960301, 19960603, 19960903,\n", " 19961202, 19970303, 19970602, 19970902, 19971201, 19980302,\n", " 19980601, 19980901, 19981201, 19990301, 19990601, 19990901,\n", " 19991201, 20000301, 20000601, 20000901, 20001201, 20010301,\n", " 20010601, 20010904, 20011203, 20020301, 20020603, 20020903,\n", " 20021202, 20030303, 20030602, 20030902, 20031201, 20040301,\n", " 20040601, 20040901, 20041201, 20050301, 20050601, 20050901,\n", " 20051201, 20060301, 20060601, 20060901, 20061201, 20070301,\n", " 20070601, 20070904, 20071203, 20080303, 20080602, 20080902,\n", " 20081201, 20090302, 20090601, 20090901, 20091201, 20100301,\n", " 20100601, 20100901, 20101201, 20110301, 20110601, 20110901,\n", " 20111201, 20120301, 20120601, 20120904, 20121203, 20130301,\n", " 20130603, 20130903, 20131202, 20140303, 20140602, 20140902,\n", " 20141201, 20150302, 20150601, 20150901, 20151201, 20160301,\n", " 20160601, 20160901, 20161201, 20170301, 20170601])" ] }, "execution_count": 39, "metadata": {}, "output_type": "execute_result" } ], "source": [ "trade_date" ] }, { "cell_type": "code", "execution_count": 40, "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "Number of trade dates 89\n" ] } ], "source": [ "print(\"Number of trade dates\", len(trade_date))" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "# 3. Get daily 1 year return table in each 89 trade period" ] }, { "cell_type": "code", "execution_count": 41, "metadata": {}, "outputs": [ { "data": { "text/html": [ "<div>\n", "<style scoped>\n", " .dataframe tbody tr th:only-of-type {\n", " vertical-align: middle;\n", " }\n", "\n", " .dataframe tbody tr th {\n", " vertical-align: top;\n", " }\n", "\n", " .dataframe thead th {\n", " text-align: right;\n", " }\n", "</style>\n", "<table border=\"1\" class=\"dataframe\">\n", " <thead>\n", " <tr style=\"text-align: right;\">\n", " <th></th>\n", " <th>tic</th>\n", " <th>predicted_return</th>\n", " <th>trade_date</th>\n", " </tr>\n", " </thead>\n", " <tbody>\n", " <tr>\n", " <th>0</th>\n", " <td>EOG</td>\n", " <td>0.033723</td>\n", " <td>19950601</td>\n", " </tr>\n", " <tr>\n", " <th>1</th>\n", " <td>EQT</td>\n", " <td>0.037745</td>\n", " <td>19950601</td>\n", " </tr>\n", " <tr>\n", " <th>2</th>\n", " <td>HES</td>\n", " <td>0.051450</td>\n", " <td>19950601</td>\n", " </tr>\n", " <tr>\n", " <th>3</th>\n", " <td>NFX</td>\n", " <td>0.030283</td>\n", " <td>19950601</td>\n", " </tr>\n", " <tr>\n", " <th>4</th>\n", " <td>OKE</td>\n", " <td>0.041020</td>\n", " <td>19950601</td>\n", " </tr>\n", " </tbody>\n", "</table>\n", "</div>" ], "text/plain": [ " tic predicted_return trade_date\n", "0 EOG 0.033723 19950601\n", "1 EQT 0.037745 19950601\n", "2 HES 0.051450 19950601\n", "3 NFX 0.030283 19950601\n", "4 OKE 0.041020 19950601" ] }, "execution_count": 41, "metadata": {}, "output_type": "execute_result" } ], "source": [ "selected_stock.head()" ] }, { "cell_type": "code", "execution_count": 414, "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "19950601\n", "19950901\n", "19951201\n", "19960301\n", "19960603\n", "19960903\n", "19961202\n", "19970303\n", "19970602\n", "19970902\n", "19971201\n", "19980302\n", "19980601\n", "19980901\n", "19981201\n", "19990301\n", "19990601\n", "19990901\n", "19991201\n", "20000301\n", "20000601\n", "20000901\n", "20001201\n", "20010301\n", "20010601\n", "20010904\n", "20011203\n", "20020301\n", "20020603\n", "20020903\n", "20021202\n", "20030303\n", "20030602\n", "20030902\n", "20031201\n", "20040301\n", "20040601\n", "20040901\n", "20041201\n", "20050301\n", "20050601\n", "20050901\n", "20051201\n", "20060301\n", "20060601\n", "20060901\n", "20061201\n", "20070301\n", "20070601\n", "20070904\n", "20071203\n", "20080303\n", "20080602\n", "20080902\n", "20081201\n", "20090302\n", "20090601\n", "20090901\n", "20091201\n", "20100301\n", "20100601\n", "20100901\n", "20101201\n", "20110301\n", "20110601\n", "20110901\n", "20111201\n", "20120301\n", "20120601\n", "20120904\n", "20121203\n", "20130301\n", "20130603\n", "20130903\n", "20131202\n", "20140303\n", "20140602\n", "20140902\n", "20141201\n", "20150302\n", "20150601\n", "20150901\n", "20151201\n", "20160301\n", "20160601\n", "20160901\n", "20161201\n", "20170301\n", "20170601\n", "Time consuming: 92.59127250512441 minutes\n" ] } ], "source": [ "# took about 90 minutes to run\n", "start = time.time()\n", "all_return_table={}\n", "#all_predicted_return={}\n", "all_stocks_info = {}\n", "#for i in range(0,1):\n", "for i in range(len(trade_date)):\n", " #match trading date\n", " index = selected_stock.trade_date==trade_date[i]\n", " print(trade_date[i])\n", " #get the corresponding trade period's selected stocks' name\n", " stocks_name=selected_stock.tic[selected_stock.trade_date==trade_date[i]].values\n", " temp_info = selected_stock[selected_stock.trade_date==trade_date[i]]\n", " temp_info = temp_info.reset_index()\n", " del temp_info['index']\n", " all_stocks_info[trade_date[i]] = temp_info\n", " #get the corresponding trade period's selected stocks' predicted return\n", " asset_expected_return=selected_stock[index].predicted_return.values\n", " \n", " #get current trade date and calculate trade date last year, it has to be a business date\n", " last_year_tradedate=int((trade_date[i]-round(trade_date[i]/10000)*10000)+round(trade_date[i]/10000-1)*10000)\n", " convert_to_yyyymmdd=datetime.strptime(str(last_year_tradedate), '%Y%m%d').strftime('%Y-%m-%d')\n", " #determine the business date\n", " #print(convert_to_yyyymmdd)\n", " ts = pd.Timestamp(convert_to_yyyymmdd) \n", " bd = pd.tseries.offsets.BusinessDay(n =1) \n", " new_timestamp = ts - bd \n", " lastY_tradedate = int(new_timestamp.date().strftime('%Y%m%d'))\n", " get_date_index=(all_date<trade_date[i]) & (all_date>lastY_tradedate)\n", " get_date=all_date[get_date_index]\n", " #get adjusted price table\n", " return_table=pd.DataFrame()\n", " for m in range(len(stocks_name)):\n", " #get stocks's name\n", " index_tic=(df_price.tic==stocks_name[m])\n", " #get this stock's all historicall price from sp500_price\n", " sp500_temp=df_price[index_tic]\n", " merge_left_data_table = pd.DataFrame(get_date)\n", " merge_left_data_table.columns = ['datadate']\n", " temp_price=merge_left_data_table.merge(sp500_temp, on=['datadate'], how='left')\n", " temp_price = temp_price.dropna()\n", " temp_price['daily_return']=temp_price.adj_price.pct_change()\n", "\n", " return_table=return_table.append(temp_price,ignore_index=True)\n", " all_return_table[trade_date[i]] = return_table\n", "end = time.time()\n", "print(\"Time consuming: \", (end-start)/60, \" minutes\")\n", " \n", " " ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "## Save to pickle" ] }, { "cell_type": "code", "execution_count": 419, "metadata": {}, "outputs": [], "source": [ "#with open('Data/all_return_table.pickle', 'wb') as handle: \n", "# pickle.dump(all_return_table, handle, protocol=pickle.HIGHEST_PROTOCOL)" ] }, { "cell_type": "code", "execution_count": 420, "metadata": {}, "outputs": [], "source": [ "#with open('Data/all_stocks_info.pickle', 'wb') as handle:\n", "# pickle.dump(all_stocks_info, handle, protocol=pickle.HIGHEST_PROTOCOL)" ] }, { "cell_type": "code", "execution_count": 42, "metadata": {}, "outputs": [], "source": [ "#with open('Data/all_return_table.pickle', 'rb') as handle:\n", "# all_return_table = pickle.load(handle)\n", "\n", "#with open('Data/all_stocks_info.pickle', 'rb') as handle:\n", "# all_stocks_info = pickle.load(handle)\n" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "# 4. Potfolio Optimization using pypfopt" ] }, { "cell_type": "code", "execution_count": 44, "metadata": {}, "outputs": [ { "name": "stderr", "output_type": "stream", "text": [ "/home/ubuntu/anaconda3/lib/python3.6/site-packages/pypfopt/objective_functions.py:61: RuntimeWarning: invalid value encountered in sqrt\n", " sigma = np.sqrt(np.dot(weights, np.dot(cov_matrix, weights.T)))\n" ] }, { "name": "stdout", "output_type": "stream", "text": [ "19950601 : Done\n", "19950901 : Done\n", "19951201 : Done\n", "19960301 : Done\n", "19960603 : Done\n", "19960903 : Done\n", "19961202 : Done\n", "19970303 : Done\n", "19970602 : Done\n", "19970902 : Done\n", "19971201 : Done\n", "19980302 : Done\n", "19980601 : Done\n", "19980901 : Done\n", "19981201 : Done\n", "19990301 : Done\n", "19990601 : Done\n", "19990901 : Done\n", "19991201 : Done\n", "20000301 : Done\n", "20000601 : Done\n", "20000901 : Done\n", "20001201 : Done\n", "20010301 : Done\n", "20010601 : Done\n", "20010904 : Done\n", "20011203 : Done\n", "20020301 : Done\n", "20020603 : Done\n", "20020903 : Done\n", "20021202 : Done\n", "20030303 : Done\n", "20030602 : Done\n", "20030902 : Done\n", "20031201 : Done\n", "20040301 : Done\n", "20040601 : Done\n", "20040901 : Done\n", "20041201 : Done\n", "20050301 : Done\n", "20050601 : Done\n", "20050901 : Done\n", "20051201 : Done\n", "20060301 : Done\n" ] }, { "name": "stderr", "output_type": "stream", "text": [ "/home/ubuntu/anaconda3/lib/python3.6/site-packages/pypfopt/base_optimizer.py:56: RuntimeWarning: invalid value encountered in less\n", " clean_weights[np.abs(clean_weights) < cutoff] = 0\n" ] }, { "name": "stdout", "output_type": "stream", "text": [ "20060601 : Done\n", "20060901 : Done\n", "20061201 : Done\n", "20070301 : Done\n", "20070601 : Done\n", "20070904 : Done\n", "20071203 : Done\n", "20080303 : Done\n", "20080602 : Done\n", "20080902 : Done\n", "20081201 : Done\n", "20090302 : Done\n", "20090601 : Done\n", "20090901 : Done\n", "20091201 : Done\n", "20100301 : Done\n", "20100601 : Done\n", "20100901 : Done\n", "20101201 : Done\n", "20110301 : Done\n", "20110601 : Done\n", "20110901 : Done\n", "20111201 : Done\n", "20120301 : Done\n", "20120601 : Done\n", "20120904 : Done\n", "20121203 : Done\n", "20130301 : Done\n", "20130603 : Done\n", "20130903 : Done\n", "20131202 : Done\n", "20140303 : Done\n", "20140602 : Done\n", "20140902 : Done\n", "20141201 : Done\n", "20150302 : Done\n", "20150601 : Done\n", "20150901 : Done\n", "20151201 : Done\n", "20160301 : Done\n", "20160601 : Done\n", "20160901 : Done\n", "20161201 : Done\n", "20170301 : Done\n", "20170601 : Done\n" ] } ], "source": [ "# took under 5 minutes to run\n", "\n", "stocks_weight_table = pd.DataFrame([])\n", "\n", "for i in range(len(trade_date)):\n", " # get selected stocks information\n", " p1_alldata=(all_stocks_info[trade_date[i]])\n", " # sort it by tic\n", " p1_alldata=p1_alldata.sort_values('tic')\n", " p1_alldata = p1_alldata.reset_index()\n", " del p1_alldata['index']\n", " \n", " \n", " # get selected stocks tic\n", " p1_stock = p1_alldata.tic\n", " \n", " # get predicted return from selected stocks\n", " p1_predicted_return=p1_alldata.pivot_table(index = 'trade_date',columns = 'tic', values = 'predicted_return')\n", " # use the predicted returns as the Expected returns to feed into the portfolio object\n", " mu = p1_predicted_return.T.values\n", "\n", " # get the 1-year historical return\n", " p1_return_table=all_return_table[trade_date[i]]\n", " p1_return_table_pivot=p1_return_table.pivot_table(index = 'datadate',columns = 'tic', values = 'daily_return')\n", " # use the 1-year historical return table to calculate covariance matrix between selected stocks\n", " S = risk_models.sample_cov(p1_return_table_pivot)\n", " del S.index.name \n", " \n", " # mean variance\n", " ef_mean = EfficientFrontier(mu, S,weight_bounds=(0, 0.05))\n", " raw_weights_mean = ef_mean.max_sharpe()\n", " cleaned_weights_mean = ef_mean.clean_weights()\n", " #print(raw_weights_mean)\n", " #ef.portfolio_performance(verbose=True)\n", "\n", " # minimum variance\n", " ef_min = EfficientFrontier([0]*len(p1_stock), S,weight_bounds=(0, 0.05))\n", " raw_weights_min = ef_min.max_sharpe()\n", " cleaned_weights_min = ef_min.clean_weights()\n", " #print(cleaned_weights_min)\n", " \n", " p1_alldata['mean_weight'] = cleaned_weights_mean.values()\n", " p1_alldata['min_weight'] = cleaned_weights_min.values()\n", " \n", " #ef.portfolio_performance(verbose=True)\n", "\n", " \n", " stocks_weight_table = stocks_weight_table.append(pd.DataFrame(p1_alldata), ignore_index=True)\n", " print(trade_date[i], \": Done\")\n" ] }, { "cell_type": "code", "execution_count": 45, "metadata": {}, "outputs": [ { "data": { "text/html": [ "<div>\n", "<style scoped>\n", " .dataframe tbody tr th:only-of-type {\n", " vertical-align: middle;\n", " }\n", "\n", " .dataframe tbody tr th {\n", " vertical-align: top;\n", " }\n", "\n", " .dataframe thead th {\n", " text-align: right;\n", " }\n", "</style>\n", "<table border=\"1\" class=\"dataframe\">\n", " <thead>\n", " <tr style=\"text-align: right;\">\n", " <th></th>\n", " <th>tic</th>\n", " <th>predicted_return</th>\n", " <th>trade_date</th>\n", " <th>mean_weight</th>\n", " <th>min_weight</th>\n", " </tr>\n", " </thead>\n", " <tbody>\n", " <tr>\n", " <th>0</th>\n", " <td>ACV.1</td>\n", " <td>0.024449</td>\n", " <td>19950601</td>\n", " <td>0.00000</td>\n", " <td>0.00000</td>\n", " </tr>\n", " <tr>\n", " <th>1</th>\n", " <td>AES</td>\n", " <td>0.096917</td>\n", " <td>19950601</td>\n", " <td>0.00000</td>\n", " <td>0.00000</td>\n", " </tr>\n", " <tr>\n", " <th>2</th>\n", " <td>AHM.1</td>\n", " <td>0.044516</td>\n", " <td>19950601</td>\n", " <td>0.01200</td>\n", " <td>0.00522</td>\n", " </tr>\n", " <tr>\n", " <th>3</th>\n", " <td>AMH.1</td>\n", " <td>0.105036</td>\n", " <td>19950601</td>\n", " <td>0.00000</td>\n", " <td>0.00000</td>\n", " </tr>\n", " <tr>\n", " <th>4</th>\n", " <td>AMT.1</td>\n", " <td>0.085373</td>\n", " <td>19950601</td>\n", " <td>0.00000</td>\n", " <td>0.00000</td>\n", " </tr>\n", " <tr>\n", " <th>5</th>\n", " <td>AOS</td>\n", " <td>0.061494</td>\n", " <td>19950601</td>\n", " <td>0.00000</td>\n", " <td>0.00000</td>\n", " </tr>\n", " <tr>\n", " <th>6</th>\n", " <td>APCC.</td>\n", " <td>0.160571</td>\n", " <td>19950601</td>\n", " <td>0.00872</td>\n", " <td>0.02036</td>\n", " </tr>\n", " <tr>\n", " <th>7</th>\n", " <td>APH</td>\n", " <td>0.080985</td>\n", " <td>19950601</td>\n", " <td>0.01136</td>\n", " <td>0.00000</td>\n", " </tr>\n", " <tr>\n", " <th>8</th>\n", " <td>ARG</td>\n", " <td>0.059334</td>\n", " <td>19950601</td>\n", " <td>0.00000</td>\n", " <td>0.00000</td>\n", " </tr>\n", " <tr>\n", " <th>9</th>\n", " <td>ATI.1</td>\n", " <td>0.170435</td>\n", " <td>19950601</td>\n", " <td>0.00926</td>\n", " <td>0.00000</td>\n", " </tr>\n", " <tr>\n", " <th>10</th>\n", " <td>AVATQ</td>\n", " <td>0.051080</td>\n", " <td>19950601</td>\n", " <td>0.00020</td>\n", " <td>0.00000</td>\n", " </tr>\n", " <tr>\n", " <th>11</th>\n", " <td>BAY.3</td>\n", " <td>0.088882</td>\n", " <td>19950601</td>\n", " <td>0.00975</td>\n", " <td>0.00797</td>\n", " </tr>\n", " <tr>\n", " <th>12</th>\n", " <td>BBBY</td>\n", " <td>0.108766</td>\n", " <td>19950601</td>\n", " <td>0.00374</td>\n", " <td>0.05000</td>\n", " </tr>\n", " <tr>\n", " <th>13</th>\n", " <td>BBY</td>\n", " <td>0.184360</td>\n", " <td>19950601</td>\n", " <td>0.01155</td>\n", " <td>0.05000</td>\n", " </tr>\n", " <tr>\n", " <th>14</th>\n", " <td>BEV</td>\n", " <td>0.064850</td>\n", " <td>19950601</td>\n", " <td>0.00000</td>\n", " <td>0.00000</td>\n", " </tr>\n", " <tr>\n", " <th>15</th>\n", " <td>BF.B</td>\n", " <td>0.036150</td>\n", " <td>19950601</td>\n", " <td>0.02235</td>\n", " <td>0.00000</td>\n", " </tr>\n", " <tr>\n", " <th>16</th>\n", " <td>BGEN</td>\n", " <td>0.057776</td>\n", " <td>19950601</td>\n", " <td>0.01425</td>\n", " <td>0.05000</td>\n", " </tr>\n", " <tr>\n", " <th>17</th>\n", " <td>BGG</td>\n", " <td>0.066459</td>\n", " <td>19950601</td>\n", " <td>0.00000</td>\n", " <td>0.05000</td>\n", " </tr>\n", " <tr>\n", " <th>18</th>\n", " <td>BIIB</td>\n", " <td>0.081258</td>\n", " <td>19950601</td>\n", " <td>0.01236</td>\n", " <td>0.00000</td>\n", " </tr>\n", " <tr>\n", " <th>19</th>\n", " <td>BLL</td>\n", " <td>0.039363</td>\n", " <td>19950601</td>\n", " <td>0.03366</td>\n", " <td>0.00000</td>\n", " </tr>\n", " </tbody>\n", "</table>\n", "</div>" ], "text/plain": [ " tic predicted_return trade_date mean_weight min_weight\n", "0 ACV.1 0.024449 19950601 0.00000 0.00000\n", "1 AES 0.096917 19950601 0.00000 0.00000\n", "2 AHM.1 0.044516 19950601 0.01200 0.00522\n", "3 AMH.1 0.105036 19950601 0.00000 0.00000\n", "4 AMT.1 0.085373 19950601 0.00000 0.00000\n", "5 AOS 0.061494 19950601 0.00000 0.00000\n", "6 APCC. 0.160571 19950601 0.00872 0.02036\n", "7 APH 0.080985 19950601 0.01136 0.00000\n", "8 ARG 0.059334 19950601 0.00000 0.00000\n", "9 ATI.1 0.170435 19950601 0.00926 0.00000\n", "10 AVATQ 0.051080 19950601 0.00020 0.00000\n", "11 BAY.3 0.088882 19950601 0.00975 0.00797\n", "12 BBBY 0.108766 19950601 0.00374 0.05000\n", "13 BBY 0.184360 19950601 0.01155 0.05000\n", "14 BEV 0.064850 19950601 0.00000 0.00000\n", "15 BF.B 0.036150 19950601 0.02235 0.00000\n", "16 BGEN 0.057776 19950601 0.01425 0.05000\n", "17 BGG 0.066459 19950601 0.00000 0.05000\n", "18 BIIB 0.081258 19950601 0.01236 0.00000\n", "19 BLL 0.039363 19950601 0.03366 0.00000" ] }, "execution_count": 45, "metadata": {}, "output_type": "execute_result" } ], "source": [ "stocks_weight_table.head(20)\n" ] }, { "cell_type": "code", "execution_count": 46, "metadata": {}, "outputs": [ { "data": { "text/plain": [ "(12932, 5)" ] }, "execution_count": 46, "metadata": {}, "output_type": "execute_result" } ], "source": [ "stocks_weight_table.shape" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "## save to excel or csv" ] }, { "cell_type": "code", "execution_count": 47, "metadata": {}, "outputs": [], "source": [ "stocks_weight_table.to_excel('Data/stocks_weight_table.xlsx','Sheet1')\n" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [] } ], "metadata": { "kernelspec": { "display_name": "Python 3", "language": "python", "name": "python3" }, "language_info": { "codemirror_mode": { "name": "ipython", "version": 3 }, "file_extension": ".py", "mimetype": "text/x-python", "name": "python", "nbconvert_exporter": "python", "pygments_lexer": "ipython3", "version": "3.6.5" } }, "nbformat": 4, "nbformat_minor": 2 }