Assignment 4: Using API to gather financial data with Quandl¶
• Name: PUT YOUR NAME HERE
• Student ID: PUT YOUR ID HERE
• Section: PUT YOUR SECTION (1 or 2)
Grading: This is a pass or fail assignment worth 5% towards your final grade.
You must provide a clean code. Make sure the grader can follow each step of your analysis. Put comments to describe what you are doing (Using MARKDOWN or using #). Remove any lines of code that is not necessary. If you struggle on a question, that is OK, as long you show all the steps that you have taken and describe what is not working. If your code is not clean and it is hard for the grader to understand what you are doing, you will fail.
SUBMISSION of ASSIGNMENT
You must print your code output. Save the Jupyter code in PDF. To do so, click on File->Download as->PDF. Or you can do, File->Print Preview.
— Remove all the sample code. Keep your code only. Make sure you code works fully, from start to finish with no error message. In other words, if you close Jupyter, re-open your code, and press the PLAY button, does your code work fully with no error message?
Additional information
How to install Quandl in Python https://docs.quandl.com/docs/python-installation
• When you open Anaconda, click on Environement (left panel)
• Press the start button right beside base (root). This will open terminal.
• Just type pip install quandl
Make sure you go Bill’s email to make sure you sign-up to Quandl and get your API code.
Documentation on how to use Quandl package for Python:
Get time-series data https://docs.quandl.com/docs/python-time-series
Documentation on the TSX data https://www.quandl.com/data/XTSE-Toronto-Stock-Exchange-Prices/documentation
Earnings announcement data
Earnings (US and CANADA) information https://web.tmxmoney.com/earnings_cal.php
If you use US data
US stock exchange benchmark: SPY
US Government short-term treasury: SHV (Blackrock ETF)
For stock prices, you use the column “adj_close”. This price already adjust for stock splits and dividends.
If you use Canadian data
Canadian stock exchange benchmark: XIU (Blackrock ETF)
Canadian Government short-term treasury: XSB (Blackrock ETF)
For stock prices, you use the column “Close”. This price is already adjusted for stock splits and dividends.
Objectives:
• Learn how to get financial data from an API service
• Plot subplots
• Plot event studies
• Construct an equal weighted portfolio
• Learn how to test predictability regression
The code below contain the sample codes and sample stocks. You cannot choose the same Canadian stocks.
In [20]:
# import your package
import quandl
import pandas as pd
import matplotlib.pyplot as plt
%matplotlib inline
In [21]:
# set up your API
quandl.ApiConfig.api_key = ‘xvincsYagVjR1qji2QMR’
Provide the list of stocks — You cannot choose the following stocks: ‘DOL’, ‘HBC’, ‘TD’, ‘ABX’, ‘L’, ‘BMO’.¶
If you choose canadian stocks, you need XIU and XSB, which are the Canadian market return and and risk-free return, respectively. If you choose US stocks, you need SPY and SHV.
In [ ]:
In [23]:
# Sample code
# get the data on Quandl.
stocks = [‘DOL’, ‘HBC’, ‘TD’, ‘ABX’, ‘L’, ‘BMO’, ‘XIU’, ‘XSB’]
# if you get canadian data, you use XTSE or EOD for US stocks
data = {} # set up the dictionary
for s in stocks:
data[s] = quandl.get(“XTSE/”+s) # , start_date=”2018-11-06″, end_date=”2019-01-06”
# get rid of weekends. Monday to Friday is weekday == 0, 1, 2, 3, 4.
In [28]:
# Format a price and trade volume dataframe
prc = pd.DataFrame() # where to store the price data
vol = pd.DataFrame() # where to store the volume data
for s in stocks:
# get the returns
tmp = data[s][[‘Close’]] # IMPORTANT: if US stocks, use ajd_close (includes dividends)
tmp.columns = [s] # renaming the column with TICKER name
prc = pd.concat([prc, tmp], axis=1)
prc = prc[prc.index.weekday < 5] # get rid of weekend observations
# get the volume
tmp = data[s][['Volume']]
tmp.columns = [s] # renaming the column with TICKER name
vol = pd.concat([vol, tmp], axis=1)
vol = vol[vol.index.weekday < 5]
In [27]:
# Make a subplot of the prices of each stock.
prc[['DOL', 'HBC', 'TD', 'ABX', 'L', 'BMO']].plot(layout=(4,2),
figsize=(12,8),
subplots=True)
#plt.savefig('filepath/filename.pdf')
Out[27]:
array([[
[
[
[

Question 1: Make a subplot of the trade volume for your 6 chosen stocks¶
In [ ]:
Question 2: Calculate the returns for the individual stocks (hint: use the function .pct_change())¶
In [ ]:
Question 3: Provide the description statistics of your stock returns using .describe()¶
In [ ]:
Question 4: Create a dataframe that contains abnormal returns for each of the 6 stock and make a subplot of the abnormal returns for your 6 stocks.¶
Abnormal return $=R_{i,t}-R_{M,t}$, if you choose Canadian stocks, $R_{M,t}$ is the ticker XIU, else, SPY for US stocks
In [ ]:
Question 5: Make a plot of the cumulative abnormal returns (CAR) for one stock around one of its earnings announcements. Choose one quarter before to one quarter after the announcement to make the plot.¶
• I provide below a sample code for the Canadian firm DOL (Dollarama). To find the earnings announcement date, use the link above on earnings announcements.
• $CAR = \sum^T_t (R_{i,t}-R_{M,t})$
In [ ]:
In [9]:
# SAMPLE CODE:
# plot for one stock its Cumulative Abnormal Return (CAR) around an earnings announcement.
# The earnings announcement date for DOL is 2018-12-06
ret = prc[[‘DOL’, ‘XIU’]].pct_change()
ret[‘abn_ret’] = ret[‘DOL’]-ret[‘XIU’] # if you choose a US stock, put SPY instead of XIU
# choose the time period. The earnings announcement is on 2018-12-06.
# The period I choose is 2018-09-06 to 2019-03-06.
ret = ret[‘2018-09-06′:’2019-03-06’]
ret[‘CAR’] = ret[‘abn_ret’].cumsum()
In [10]:
# plotting the CAR around the earnings announcement date
fig = plt.figure() # initiate the figure environment
ret[‘CAR’].plot(color=’b’, linewidth=2)
plt.ylabel(‘CAR’)
# Make sure you change the title of the figure accordingly.
plt.title(‘Dollarama \n Expected EPS=0.42, Actual EPS=0.41, Surprise=-2.3%’)
# This last line of code draws the vertical line in the plot
plt.axvline(x=’2018-12-06′, color=’k’, linestyle=’-.’, alpha=.5)
Out[10]:

Equal-weighted portfolio of stocks¶
• You can pick any starting year. I don’t force you to choose the 6 stocks. You can pick 3, 4, 5, or 6 stocks. Make sure all the stocks have observations
Question 4: Form an initial equal-weighted portfolio (I want to see the code).¶
• Go back to assignment 2 for the code
In [ ]:
Question 5: Calculate the monthly returns of your initial equal-weighted portfolio.¶
In [ ]:
Question 6: Can you use past returns to forecast next month returns of your portfolio?¶
-Demonstrate using a time-series regression. Show the regression output. What does your estimated coefficients and $R^2$ say about the predictability?
hint: You simply have to run the following regression: $R_{i,t} = \alpha + \beta R_{i,t-1} + \varepsilon_{i,t}$, where $R_{i,t}$ is the return of the portfolio and $R_{i,t-1}$ is the one-month lag return. To create the variable $R_{i,t-1}$ in your dataframe, just do port[‘lag_ret’] = port[‘ret’].shift() [Here, I am assuming you named your dataframe with the portfolio return as port]. The function shift takes the previous month return. Then you can do the regression as follow: reg = smf.ols(‘ret~lag_ret’, data=port).fit()
In [ ]: