<SYSTEM_TASK:>
Given the following text description, write Python code to implement the functionality described below step by step
<END_TASK>
<USER_TASK:>
Description:
 Stock Analysis
Step1:  Explore the Kaggle dataset all_stocks_5yrs.csv
How many observations and features do we have
Step2:  For each trading day we have the ticker symbol (Name) and the price at opening (Open), closing (Close), highest (High) and lowest (Low) of the day and the number of stocks traded (Volume).
What are the types of the columns?
Step3:  We will only use the Date, Close and Name features for this exercise.
Convert the Date column to datetime
Step4:  What is the time period over which we have date?
Step5:  Split the total period in 5 periods of approx 1 year each
Step6:  Create a multi-index for better retrieval later
Step7:  Are there any null values?
Step8:  How much data points do we have?
The list of stock symbols
Step9:  How many symbols do we have?
Step10:  Facebook (ticker symbol 'FB') is one of these superstar companies of the past 5 year.
Plot the price evolution of facebook
Step11:  Create and explore the dataset with historical prices for the S&P 500 index, retrieved from yahoo finance
We can get similar data as those from Kaggle, via the webservice of Yahoo finance
The pandas_datareader (https
Step12:  What is the date range of the data?
Step13:  Align the date range with the data from Kaggle
Step14:  join the sp500 index data with the kaggle data
Step15:  Facebook is one of those stocks that outperformed the index.
Plot the historical return of 'FB' (Facebook) against the S&P 500 index
Step17:  Research question
Which stocks performed better last year than the index, with higher return and lower risk
Which performed better than the index year after year
Research method
define function to calculate return / risk ratio for a particular year
calculate return / risk ratio for every year for all tickers, including S&P 500 index (ticker
Step18:  Calculate return / risk ratio for latest year for all tickers, including S&P 500 index (ticker
Step19:  Check return / risk ratio for Facebook
Step20:  Calculate return / risk ratios for all S&P 500 symbols and store the results in a Dataframe
Step21:  Set a multi index for easier data retrieval
Step22:  Check the data for Facebook and the SP500 index
Step23:  Visualise the results
Step24:  Calculate the relative performance for each ticker vs the index for every year
Step25:  Rank tickers on relative return / risk ratio, show top 20
Step26:  Filter tickers, selects the ones with better performance than SP500 index
Step27:  So only about 20% of the stocks performs better than the SP500 index!
Step28:  Which stocks outperformed the SP500 index more than twice
Step29:  Which stocks where the best performers of the last year
<ASSISTANT_TASK:>
Python Code:
import pandas as pd
import numpy as np
import matplotlib as mpl
import matplotlib.pyplot as plt
from sklearn.metrics import mean_squared_error
import datetime as dt
from math import sqrt
Explanation: Stock Analysis: return-risk performance vs S&P500 index
Find stocks that outperform the SP500 index, year after year, as well based on return (higher return) as on risk (lower risk)
Datasets
We use two datasets:
from Kaggle: S&P 500 stock data
https://www.kaggle.com/camnugent/sandp500 file: all_stocks_5yrs.csv
This dataset contains all stocks in the S&P 500 index, and their historical prices for the past 5 years
from yahoo: S&P 500 index historical prices
End of explanation
data = pd.read_csv('./all_stocks_5yr.csv')
data.shape
data.head()
Explanation: Explore the Kaggle dataset all_stocks_5yrs.csv
How many observations and features do we have
End of explanation
data.dtypes
Explanation: For each trading day we have the ticker symbol (Name) and the price at opening (Open), closing (Close), highest (High) and lowest (Low) of the day and the number of stocks traded (Volume).
What are the types of the columns?
End of explanation
data['Date'] = pd.to_datetime(data['Date'])
Explanation: We will only use the Date, Close and Name features for this exercise.
Convert the Date column to datetime
End of explanation
data.Date.min(), data.Date.max()
Explanation: What is the time period over which we have date?
End of explanation
periods = np.array_split(data.Date.unique(), 5)
for yr in range(5):
    print(periods[yr].min(), periods[yr].max())
Explanation: Split the total period in 5 periods of approx 1 year each
End of explanation
data.set_index(['Name', 'Date'], inplace=True)
data.head()
Explanation: Create a multi-index for better retrieval later
End of explanation
pd.isnull(data['Close']).any()
Explanation: Are there any null values?
End of explanation
symbols = list(data.index.levels[0])
symbols[:10]
symbols[-10:]
Explanation: How much data points do we have?
The list of stock symbols:
End of explanation
len(symbols)
Explanation: How many symbols do we have?
End of explanation
plt.gcf().set_size_inches(16, 6)
plt.subplot(121)
data.loc['FB'].Close.plot()
plt.title('Price evolution Facebook over 5 years')
plt.subplot(122)
data.loc['FB'].loc[periods[4]].Close.plot()
plt.title('Price evolution Facebook over the last year')
plt.show()
Explanation: Facebook (ticker symbol 'FB') is one of these superstar companies of the past 5 year.
Plot the price evolution of facebook
End of explanation
import pandas_datareader as pdr
sp500_index = pdr.get_data_yahoo('^GSPC')
sp500_index.head()
Explanation: Create and explore the dataset with historical prices for the S&P 500 index, retrieved from yahoo finance
We can get similar data as those from Kaggle, via the webservice of Yahoo finance
The pandas_datareader (https://github.com/pydata/pandas-datareader) module allows easy downloading of stock data in a pandas DataFrame
Let's dowload the S&P 500 index (ticker='^GSPC') historical data.
End of explanation
sp500_index.index.min(), sp500_index.index.max()
Explanation: What is the date range of the data?
End of explanation
sp500_index = sp500_index.loc[data.index.min()[1]:data.index.max()[1],:]
Explanation: Align the date range with the data from Kaggle
End of explanation
sp500_index['Name'] = '^GSPC'
sp500_index['Date'] = sp500_index.index
sp500_index.set_index(['Name', 'Date'], inplace=True)
data = pd.concat([data, sp500_index])
symbols.append('^GSPC')
symbols[-10:]
Explanation: join the sp500 index data with the kaggle data
End of explanation
sp500_return = data.loc['^GSPC'].Close / data.loc['^GSPC'].Close[0]
fb_return = data.loc['FB'].Close /  data.loc['FB'].Close[0]
sp500_return.plot(label='SP500')
fb_return.plot(label='FB')
plt.legend()
plt.title('5 year return Facebook vs S&P 500 index')
plt.show()
Explanation: Facebook is one of those stocks that outperformed the index.
Plot the historical return of 'FB' (Facebook) against the S&P 500 index
End of explanation
def return_risk_ratio(ticker, start, end):
    Return a tuple: (relative return over period, risk over the period, return/risk)
    # select the data for the ticker and time period
    data_masked = data.loc[ticker].loc[start:end]
    x = np.arange(data_masked.Close.size)
    y = np.array(data_masked.Close)
    # Fit a degree 1 regression line
    fit = np.polyfit(x, y, 1)
    fit_fn = np.poly1d(fit)
    y_regression = fit_fn(x)
    
    # Calculate Root Mean Square Error = beta = risk
    RMSE = sqrt(mean_squared_error(y_true = y, y_pred = y_regression))
    slope = fit_fn.coeffs[0]
    y0_regression = fit_fn(x[0])
    return_percent = slope / y0_regression * x.size * 100 # return in percent 
    RMSE_percent = RMSE / y0_regression * 100
    return (return_percent, RMSE_percent , return_percent / RMSE_percent)
Explanation: Research question
Which stocks performed better last year than the index, with higher return and lower risk
Which performed better than the index year after year
Research method
define function to calculate return / risk ratio for a particular year
calculate return / risk ratio for every year for all tickers, including S&P 500 index (ticker: ^GSPC)
visualise the results
calculate the relative performance for each ticker vs the index  
rank tickers on return / risk ratio, show top 20 
filter tickers, selects the ones with better performance than SP500 index
which stocks performed better than the SP500 index more than twice over the past 5 year
Define function to calculate return / risk ratio for a particular year
For the return we perform a linear regressions over the time series period. With the linear trendline we avoid daily fluctuations, and get a general idea of the return.
We define the return as the ratio of the price at the end date over the price of the start date (regression values).
We define the risk as the standard deviation of the real prices vs the trendline, relative over the start price (regression value).
For the return-risk-ratio we divide the return by the risk. For alternative return-risk-ratio definition see for example the Sharpe ratio
End of explanation
start_date_iso = dt.date.isoformat(pd.Timestamp(periods[4].min()))
end_date_iso = dt.date.isoformat(pd.Timestamp(periods[4].max()))
print(start_date_iso, end_date_iso)
Explanation: Calculate return / risk ratio for latest year for all tickers, including S&P 500 index (ticker: SPY)
Get the start and end dates of the last year of data
End of explanation
print(list(return_risk_ratio('FB', start_date_iso, end_date_iso)))
# print(return_risk_ratio('^GSPC', start_date, end_date))
Explanation: Check return / risk ratio for Facebook
End of explanation
# create an empty Dataframe
rrr_index = symbols
rrr_columns = ['Period', 'Symbol', 'Return', 'Risk', 'ReturnRiskRatio', 'RiskReturnToIndex']
rrr = pd.DataFrame(columns=rrr_columns)
# Fill the Dataframe with the return risk ratios
n = 0
for i, p in enumerate(periods):
    start_date_iso = dt.date.isoformat(pd.Timestamp(p.min()))
    end_date_iso = dt.date.isoformat(pd.Timestamp(p.max()))
    for s in symbols: 
        #print(i, s, start_date_iso, end_date_iso)
        if data.loc[s].loc[start_date_iso:end_date_iso].size > 0: # data available for that period
            ret, risk, ratio = return_risk_ratio(s, start_date_iso, end_date_iso)
            rrr.loc[n] = [i, s, ret, risk, ratio, np.NaN]
        n = n + 1
rrr.head()    
Explanation: Calculate return / risk ratios for all S&P 500 symbols and store the results in a Dataframe
End of explanation
rrr.set_index(['Period', 'Symbol'], inplace=True)
rrr.head()
Explanation: Set a multi index for easier data retrieval
End of explanation
rrr.loc[0].loc['FB']
rrr.loc[0].loc['^GSPC']
Explanation: Check the data for Facebook and the SP500 index
End of explanation
plt.gcf().set_size_inches(16, 16)
subplot = 320
for y in rrr.index.levels[0]:
    subplot += 1
    plt.subplot(subplot)
    rrry = rrr.loc[y]
    plt.scatter(rrry.Risk, rrry.Return, alpha=.2, label='individual stock')
    plt.scatter(rrry.loc['^GSPC'].Risk, rrry.loc['^GSPC'].Return, c='r', label='Index')
    fit = np.polyfit(rrry.Risk, rrry.Return, deg=1)
    plt.plot(rrry.Risk, fit[0] * rrry.Risk + fit[1], color='red', label='regression')
    plt.xlabel('Risk')
    plt.ylabel('Return %')
    title = "year {} Return vs Risk".format(y)
    plt.title(title)
plt.legend()
plt.show()
Explanation: Visualise the results
End of explanation
for y in rrr.index.levels[0]:
    for s in symbols:
        if s in rrr.loc[y].index: # data available for that period
            rrr.loc[y].loc[s].RiskReturnToIndex = \
                rrr.loc[y].loc[s].ReturnRiskRatio / rrr.loc[y].loc['^GSPC'].ReturnRiskRatio
rrr.head()
#rrr.nlargest(20, 'RiskReturnToIndex')
Explanation: Calculate the relative performance for each ticker vs the index for every year
End of explanation
rrr.nlargest(10, 'RiskReturnToIndex')
Explanation: Rank tickers on relative return / risk ratio, show top 20
End of explanation
best_performers = rrr[rrr.RiskReturnToIndex > 1]
len(best_performers)
Explanation: Filter tickers, selects the ones with better performance than SP500 index
End of explanation
plt.gcf().set_size_inches(16, 16)
subplot = 320
for y in rrr.index.levels[0]:
    subplot += 1
    ax = plt.subplot(subplot)
    ax.set_xlim([0, 20]) # fix risk and return scale to for all subplots
    ax.set_ylim([0, 200])
    rrry = rrr.loc[y]
    best_performersy = best_performers.loc[y]
    plt.scatter(best_performersy.Risk, best_performersy.Return, alpha=.2, label='high performing stock')
    plt.scatter(rrry.loc['^GSPC'].Risk, rrry.loc['^GSPC'].Return, c='r', label='Index')
    plt.xlabel('Risk')
    plt.ylabel('Return %')
    title = "year {} Return vs Risk, Return(sp500) = {:5.2f}%".format(y, rrry.loc['^GSPC'].Return)
    plt.title(title)
plt.legend()
plt.show()
Explanation: So only about 20% of the stocks performs better than the SP500 index!
End of explanation
best_stocks = best_performers.index.get_level_values(1)
from collections import Counter
d = Counter(best_stocks)
d = {k: v for k, v in d.items() if v > 2}
print(len(d))
d.keys()
Explanation: Which stocks outperformed the SP500 index more than twice
End of explanation
best_performers.loc[4]
#rrr.loc[4].nlargest(10, 'RiskReturnToIndex')
bestInYear = list(rrr.loc[4].nlargest(20, 'RiskReturnToIndex').index)
bestInYear
plt.gcf().set_size_inches(12, 8)
rrry = rrr.loc[4]
best4 = best_performers.loc[4]
plt.scatter(rrry.Risk, rrry.Return, alpha=.2, label='individual stock')
plt.scatter(rrry.loc['^GSPC'].Risk, rrry.loc['^GSPC'].Return, c='r', label='Index')
fit = np.polyfit(rrry.Risk, rrry.Return, deg=1)
plt.plot(rrry.Risk, fit[0] * rrry.Risk + fit[1], color='red', label='regression')
plt.scatter(best4.Risk, best4.Return, color='green', label='best of year')
plt.xlabel('Risk')
plt.ylabel('Return %')
title = "last year Return vs Risk".format(y)
plt.title(title)
plt.legend()
plt.show()
Explanation: Which stocks where the best performers of the last year
End of explanation
<END_TASK>