This is the second part of the final exam for AcF 351b: Python Stream.

Students are expected to act according to the highest ethical standards. All students enrolled at Lancaster       University are to perform their academic work according to standards set by faculty members, departments,     schools and colleges of the university; and cheating and plagiarism constitute fraudulent misrepresentation for which appropriate sanctions are warranted and will be applied. Please note that any form of violation of the      following rules will be treated as plagiarism

1. Answer the questions yourself without asking others for assistance. This is a test of your ability of data science and computer programming.

2. Do not share the questions or your answers with anyone. This includes posting the questions or your solutions publicly on services like quora, stackoverflow, or github.

We will run a system to detect any kind of plagiarism, e.g., coding scripts with high similarities.

Do NOT erase the #export at the top of any cells as it is used by notebook2script.py to extract cells for submission.

Do NOT change the following cell!

In [ ]:

If you need extra modules, use the following cell to import them

In [ ]:

#export

# imported extra moduels:

In this notebook, you are going to analyze the data of U.S. and U.K corporate bonds in the U.S. market. For

corporate bonds, the typical variable of interest is bond yield (https://www.investopedia.com/terms/b/bond- yield.asp). We are not going to elaborate the definition of bond yield and how bond yield is calculated. For our

purpose, we only need to know that bond yields are inversely related to bond prices. If investors prefer holding some bonds, the price of the bonds increases and, consequently, the yield decreses.

acf351b_bonds.csv provides historical yields and trading quantities for a sample of U.S. and U.K. bonds in the U.S. market between Jan 2015 and Dec 2017.

The following table shows the data dictionary.

The following code script reads the data into the memory, and passes it to a dataframe named df. DO NOT

CHANGE IT.

However, you can add extra code cells below it to have an overview.

In [ ]:

We are going to make use of the data on WRDS. The following code script establishes the connection to WRDS database.

In [ ]:

#export

conn = wrds.Connection ()

Question 1: Using SQL to retrieve data of stock returns and compute 90-day return volatilities

CRSP is a dataset of historical stock prices (variable: prc) and stock returns (variable: ret) for most stocks listed in the U.S. stock exchanges. Each stock has a unique identifier called permno. In this question, we are going to write PostgreSQL scripts to retrieve stock returns for all bond issuers in our bond data df.

Question 1.1: write a piece of SQL scripts to retrieve stock return (variable: ret) and stock trading date (date) from CRSP (dataset: crsp.dsf) between July 1st, 2014 and Dec 31st, 2017. (10 pts)

A part of the script is provided. Complete the SQL query based on the information in Question 1.1

In [ ]:

In [ ]:

Question 1.2: Compute the past 90-day return volatility. For each stock on each trading day, compute the 90-day volatility (standard deviation) of returns, and generate a new column of df_vol titled vol . Note that the 90 days include the trading day being considered. (5 pts)

For instance, for the stock with a permno 93013 on Dec 28, 2017, the 90-day volatility is the standard deviation of its returns of the most recent 90 days inclusive of Dec 28, 2017.

hint: use groupby() and rolling() function. The answer probably would not take more than 3 lines.

In [ ]:

Question 1.3: Left merge dataframes df and df_vol based on permno and date, and name the new

dataframe with df. Only Keep the variable vol from df_vol. In other words, the variable ret from df_vol should not appear in the final dataframe. (5 pts)

In [ ]:

Question 2: Bond Ratings

Some bonds have higher credit ratings, meaning that investors are confident that the bond issuers are able to   pay off the bonds when they are due. Some bonds have lower credit ratings, and investors may demand higher

risk compensations, resulting in higher bond yields. In this question, we are going to find out the credit ratings for each bond on each date in our dataframe df.

acf351b_ratings.csv provides historical credit ratings for all bond issues in the spreadsheet.

In [ ]:

The variable issue_id is the identifier of bonds. The variable rating_type indicates the rating agencies. MR denotes Moody's Rating, SPR indicates Standard and Poor's Rating, and FR indicates Fitch's Rating. The          variable rating_date is the date on which the rating agencies assign their ratings. The variable rating is the bond rating assigned by the rating agency.

Each rating agency has their own rating scales, but they are comparable as follows:

with AAA being the highest credit rating, AA being one tier lower, and so forth. NR indicates  Not Rated

Question 2.1: Convert all ratings to standard ratings based on the table above, and generate a new column of df_rating named std_rating . (5 pts)

In [ ]:

#export

# Code script for Q 2.1

# Write your code script below

Question 2.2: Using df_rating_, locate the latest _standard ratings prior to the trade dates (variable

date ) for each bond issue in df_, and generate a new variable named rating in _df. (10 pts)

For example, the bond with an issue id of 647968 was traded on March 31st, 2016, then for this transaction you should try to locate the latest credit rating of the bond prior to March 31st, 2016. If multiple credit rating agencies (S&P, Moody’s and Fitch) rate the bond issue on the same dates , select the LOWEST one.