import sys                             # system module 
import pandas as pd                    # data package
import matplotlib.pyplot as plt        # graphics module  
import datetime as dt                  # date and time module
import numpy as np                     # foundation for Pandas 
import statsmodels.formula.api as smf  #stat module for regression
import seaborn as sns                  #seaborn graphics module

%matplotlib inline                     

# check versions
print('Python version:', sys.version)
print('Pandas version: ', pd.__version__)
print('Today: ', dt.date.today())

"""
Explanation: Investment Decisions Model
Written by Anushae Hassan under the direction of Professor David Backus, May 2016.
Abstract
Equity funds are subject generally to market, market sector, market liquidity, issuer, and investment style risks, among other factors, to varying degrees. Three major Index Funds tracking the Standard & Poor's® 500 Composite Stock Price Index seek to match the performance of the S&P500. Dreyfus S&P 500 Index Fund (PEOPX), Spartan 500 Index Fund (FUSEX)and Vanguard 500 Index Fund (VFINX), each of them claims to be the 'better' and 'safer' option for its investors by minimizing the risks compared to its competitors. In this project as an investment manager or investor, I want to hold the market portfolio with returns corresponding to the return on the S&P 500 Index . To decide which would be the best index to invest in, the risk and return needs to be compared according to the capital asset pricing model. To make the investment decision, I compare the three funds and regress them against the S&P500 to see which one tracks the market most effeciently. Furthermore, the OLS regression would provide the beta of each index, showing the risk factor for each one of them. Beta is a measure of the volatility, or systematic risk, of a security or a portfolio in comparison to the market as a whole. The regression will also provide the Alpha each index. Alpha is also often referred to as “excess return” or “abnormal rate of return.” The abnormal rate of return on a security or portfolio in excess of what would be predicted by an equilibrium model like the capital asset pricing model (CAPM). This project develops an investment decision model based on expected returns and beta using the CAPM.
Preliminaries
Importing packages
End of explanation
"""


file_rf = '/Users/anushaehassan/Desktop/S&P and MF Data/Rf.csv' # file location
df_rf = pd.read_csv(file_rf)

file_sp500 = '/Users/anushaehassan/Desktop/S&P and MF Data/S&P 500.csv' # file location
df_sp500 = pd.read_csv(file_sp500)

file_spartan = '/Users/anushaehassan/Desktop/S&P and MF Data/Spartan.csv' # file location
df_spartan = pd.read_csv(file_spartan)

file_vanguard = '/Users/anushaehassan/Desktop/S&P and MF Data/Vanguard.csv' # file location
df_vanguard = pd.read_csv(file_vanguard)

file_dreyfus = '/Users/anushaehassan/Desktop/S&P and MF Data/Dreyfus.csv' # file location
df_dreyfus = pd.read_csv(file_dreyfus)

"""
Explanation: Packages Imported
The first package I have imported is pandas. It is a Python package that allows for fast data manipulation and analysis to organize my data set. In pandas, a dataframe allows for storing related columns of data. I use pandas.io.data to extract data from the data sets I have gathered for the S&P 500 and the mutual funds, it is directly formatted into a dataframe. 
The second package I imported is matplotlib which allows to plot various plots like scatter plots etc. 
Furthermore, I imported the statsmodel. Using statmodels.formula.api, a Python module used for a variety of statistical computations, I was able to run the OLS linear regressions of the S&P500 against the various mutual funds. Lastly, I used seaborn for a better graphical represenation of the regressions. 
Creating the dataset
For the investment decision, I collected the data for the risk free rates, S&P500, Dreyfus S&P 500 Index Fund (PEOPX), Spartan 500 Index Fund (FUSEX)
and Vanguard 500 Index Fund (VFINX) 
I got the monthly historical data, that is: returns, from January 2007 to December 2014. 
For the risk free rates, I have used the three-month T-bill rate. This is available from the Federal Reserve Board of Governors, (https://www.stlouisfed.org/) I downladed the series named “3-Month Treasury Bill: Secondary Market Rate”. I then converted the rate from a percent to a decimal and then calculated the non annualized monthly risk free rates from the formula. Risk free rate is used in this project to adjust each fund by subtracting the rf from the monthly returns. This is to prevent the natural correlation between rf and all indices to effect the regression model. All the monthly returns in the data have been adjusted for the rf.
The data for S&P500 was provided by another data set which was adjusted for monthly (non annualized) holding period returns.
For the historical returns of the three mutual funds I used Yahoo! Finance
(http://finance.yahoo.com/)
I used the “Historical Prices” with frequency to be “monthly”. In the data,  “Open” is the price of the stock at the beginning of each month when the market just opens, “High” and “Low” stand for the highest and lowest price within each month, “Close” is the price at the end of the month before the exchange closes and the “Adj. Close” is the close price adjusted for dividend payment and stock split. Note that the trading volume(“Volume”) is always 0 just because investors do not directly buy and sell mutual funds on the exchange, instead the funds change hands with the company who manages the funds. The price we will use for this project is the “Close” price.
End of explanation
"""


#original dataframe
print('Dimensions:', df_rf.shape)
print('Variable dtypes:\n', df_rf.dtypes, sep='')
df_rf.head(15)

#cleaning
df_rf=df_rf.ix[11:]
df_rf.columns = ['DATE','RF']
df_rf = df_rf.drop(df_rf.index[0])
df_rf = df_rf.reset_index(drop=True)
df_rf['RF'] = df_rf['RF'].astype(float)
df_rf['RF'] = df_rf['RF']/100
df_rf['RF'] = (1/(1-((90/360)*(df_rf['RF']))))**(1/3)-1
df_rf.head()


"""
Explanation: Cleaning the dataset
RF
End of explanation
"""


#original dataframe
print('Dimensions:', df_sp500.shape)
print('Variable dtypes:\n', df_sp500.dtypes, sep='')
df_sp500.head(10)

#Already clean and adjusted for HPR - dataset provided by professor

#Functions to clean MFs

def original(df):
    print('Dimensions:', df.shape)
    print('Variable dtypes:\n', df.dtypes, sep='')
    print(df.head(10))
    
def clean(df):
    df.fillna(0,inplace=True)
    #df = df.rename(columns = {'Dividend ':'Dividend'},)
    df.drop(['Open','High','Low','Volume','Adj Close'],inplace=True,axis=1,errors='ignore')
    df['Close+Dividend']=df['Close']+df['Dividend']
    df['Monthly Returns']= (df['Close+Dividend'].shift(-1)/df['Close+Dividend'])-1
    df['Monthly Returns + 1']= df['Monthly Returns']+1
    print(df.head(10))
    
    
  
    

"""
Explanation: S & P 500
End of explanation
"""


original(df_dreyfus)

clean(df_dreyfus)

"""
Explanation: Dreyfus
End of explanation
"""


original(df_spartan)

clean(df_spartan)

"""
Explanation: Spartan
End of explanation
"""


original(df_vanguard)

clean(df_vanguard)

"""
Explanation: Vanguard
End of explanation
"""


#Plotting the data simply to observe the similiar underlying trend of the stock market S&P 500 and the 3 mutual funds. 

df_sp500.plot()

df_dreyfus.plot()

df_spartan.plot()

df_vanguard.plot()

#We can also plot the monthly returns of the funds to have a better understanding of the trends. 

def plotting(mf,name):
    dates = pd.date_range('2006-12', '2014-12', freq='M')
    mf.drop(mf.index[-1:],inplace=True)
    d =  {'X': dates, 'Y': mf['Monthly Returns']}
    df = pd.DataFrame(data=d)
    print(name)
    df.plot()

plotting(df_dreyfus,'Dreyfus')

plotting(df_spartan,'Spartan')

plotting(df_vanguard,'Vanguard')

#Plotting the closing prices for each of the funds to get the comaprison in absoulte terms

fig, ax = plt.subplots()

df_vanguard['Close'].plot(ax=ax)       
ax.set_title('Vanguard Returns', fontsize=16, loc='center', color= 'green', weight='bold')
ax.set_ylabel('Closing', color= 'brown')
ax.legend(['Closing Vanguard'], loc=0)  
ax.tick_params(labelcolor='red')             


fig, ax = plt.subplots()

df_spartan['Close'].plot(ax=ax)       
ax.set_title('Spartan Returns', fontsize=16, loc='center', color= 'green', weight='bold')
ax.set_ylabel('Closing', color= 'brown')
ax.legend(['Closing Spartan'], loc=0)  
ax.tick_params(labelcolor='red')               

fig, ax = plt.subplots()

df_dreyfus['Close'].plot(ax=ax)       
ax.set_title('Dreyfus Returns', fontsize=16, loc='center', color= 'green', weight='bold')
ax.set_ylabel('Closing', color= 'brown')
ax.legend(['Closing Dreyfus'], loc=0)  
ax.tick_params(labelcolor='red')               

"""
Explanation: Plotting the data
End of explanation
"""


#We can also plot them together on one graph to see a better comparison.

plt.plot(df_vanguard.index, df_vanguard['Close'], color= 'orange')
plt.plot(df_spartan.index, df_spartan['Close'], color= 'green')
plt.plot(df_dreyfus.index, df_dreyfus['Close'], color= 'red')

plt.title('Mutual Funds Trend', fontsize=14, loc='center', color='blue', weight='bold') #add title
plt.ylabel('Mutual Funds Closing', color='Red')           # y axis label 
plt.tick_params(labelcolor='blue')            # change tick labels to red
plt.legend(['Vanguard', 'Spartan', 'Dreyfus'], loc=0)  

"""
Explanation: Comparison
End of explanation
"""


def regress(mf,name):
    d =  {'X': df_sp500['vwretd'], 'Y': mf['Monthly Returns']}
    df = pd.DataFrame(data=d)
    df.drop(df.index[-1:],inplace=True)
    df['X']=df['X']-df_rf['RF']
    df['Y']=df['Y']-df_rf['RF']
    r = smf.ols(formula='Y ~ X', data=df).fit()
    print(r.summary(),end='\n\n\n\n\n')
    print('Coefficients:')
    print(r.params)
    
    #actually plotting
    
    plt.style.use('seaborn-poster')
    plt.plot(df['X'], df['Y'], 'ro')
    plt.plot(df['X'], np.poly1d(np.polyfit(df['X'], df['Y'], 1))(df['X']),'b')
    plt.legend(['Data', 'Fitted model'])
    plt.xlabel(name)
    plt.ylabel('S&P 500')
    plt.title('{0} Regression Analysis'.format(name))
    plt.show()
    
    
    sns.regplot(df['X'],df['Y']) #same plot using seaborn
    

regress(df_dreyfus,'Dreyfus')

regress(df_spartan,'Spartan')

regress(df_vanguard,'Vanguard')

"""
Explanation: Regression Analysis
End of explanation
"""


