The purpose of this assignment is to apply the statistical and portfolio concepts provided in the
book and discussed in class to compute and compare the returns and the relationships among the
three stocks you selected in Project #1.
The requirements for this project are:
1. Collect prices for (a) each stock you selected in Project #1, (b) the NYSE composite index
(NYA), and (c) the 6-month Treasury Bill rate for the end of each month for the period
beginning December 2014 and ending December 2019. The data you need can be obtained
on the Internet by going to one of the following websites or websites that provide similar
information/data.
Yahoo Finance: https://finance.yahoo.com/
Yahoo Finance works best using the Google Chrome search engine.
Type the stock symbol or company name in the search box located at the top of the
screen; click on the appropriate symbol when it appears.
Click Historical Data on the row below the price quote.
In the row labeled Time Period, change the Time Period to 12/31/2014
12/30/2019; change the Frequency selection to Monthly; click Apply
Click Download Data (located below Apply) to download the prices to an Excel
spreadsheet.
NASDAQ: https://old.nasdaq.com/
You can download stock price data even for stocks not traded on NASDAQ.
Enter the stock symbol in the search box at the top of the page (the box with Enter
symbol, name or keyword written inside).
In the menu shown on the left side of the page click Historical Quote to archive past
stock prices.
On the next screen, change the Select the Timeframe from 3 months to 6 years.
Once the data are loaded, go to the bottom of the data list to download the numbers into
an Excel spreadsheet (click Download this file in Excel Format).
Because you are given daily stock prices, you must edit the data to include only end-ofmonth prices.
The Wall street Journal: https://www.wsj.com/news/markets
Below the graph that shows current quotes for various global markets (on the right side
of the page; you might have to scroll down to see the graph), you will see a search area
named Quotes Lookup. In the search box, labeled Quotes & Companies, enter the
companys stock symbol (you can find the symbol by typing the companys name;
possible symbols will appear in a dropdown menu). Click on the company for which
you want to retrieve data.
On the next page, click on the ALL SECTIONS symbol below the lower right corner
of the graph on the screen; a dropdown menu will appear; click on Historical Prices.
In the boxes located above DATE (at the top left of the table that contains price data)
enter the range from 12/31/2014 to 12/31/2019; click GO
Click DOWNLOAD A SPREADSHEET, which is located on the right side of the
screen above the data table; the data will be contained in the spreadsheet you download.
Because you are given daily stock prices, you must edit the data to include only end-ofmonth prices.
Treasury-Bill data can be downloaded at:
St. Louis Fed: https://fred.stlouisfed.org/search?st=treasury+bills
o Look for 6-Month Treasury Bill: Secondary Market Rate
o Click on the word Monthly, and a graph of the monthly rates from 1960 to 2020
will appear.
o Click the DOWNLOAD tab located above the graph on the right side of the screen;
select the type of file to which you prefer the data to be downloaded.
Multpl.com
o https://www.multpl.com/6-month-treasury-rate
o Click: Table below the graph on the left side of the page.
o Click By Month above the table that appears on the next page.
o Highlight the months and values you need for the project, and then cut and paste
those numbers into your spreadsheet.
2. Using a spreadsheet:
Compute the monthly excess returns on (a) each of the three stocks you selected in
Project #1 and (b) the NYSE Index.
Compute the mean and standard deviation of the (a) excess returns on each stock, (b)
excess returns on the NYSE Index, and (c) 6-month T-Bill rate.
Compute the Sharpe ratio for each stock and for the NYSE Index.
Discuss the results you find. In your discussion, compare the attractiveness of each
stock with respect to its risk and return and to the market’s risk and return.
3. Using the CORREL function in the spreadsheet, determine the relationship between each
stock by computing the correlation coefficient associated with the excess returns on each
pair of stocks (e.g., Stock 1 and Stock 2 shouldould be one pairing, Stock 1 and Stock 3
should be another pairing, and Stock 2 and Stock 3 should be the final pairing). Also,
compute the correlation between the NYSE Index excess returns and the excess returns on
each stock.
Discuss the meaning of the results you attain. Explain why you think the relationships
you found exist.
Do you think the systematic risks associated with the stocks help to explain the
relationships you found? Explain.
4. Using the spreadsheet’s regression function, run three regression analyses, one for each
stock. For each regression, the excess return on the NYSE Index should be the
independent variable and the excess return on one of the stocks should be the dependent
variable. A video is posted online that will help you complete the requirements for this
portion of the project. The link to the video is:
http://sbesley.myweb.usf.edu/FIN4504/lecture.html
Using the spreadsheets graphing function, draw the characteristic line for each stock
(i.e., plot the data points and the line generated with the regression analysis).
Discuss/interpret your results (i.e., the meaning of , r2
, etc.).
Are the results you found from these regressions similar to those you found with the
correlations computed in Part 3 of this project? Explain.
To find the regression function in Excel, click on the Data tab at the top of the page. On the
far right side of the menu you should see the option Data Analysis. After clicking on Data
Analysis, scroll down to Regression. You can either highlight the word Regression and
click OK or double click the word Regression. A box will appear for you to enter data.
Enter the range of cells that contain the data you want analyzed. You can either enter the
range of cells containing the appropriate data or you can click on the up arrow ( ) and
then highlight the cells in which the data are located. Click the up arrow in the row labeled
Output Range:, position the cursor in an empty cell that has empty cells located to the
right and below it, and then press OK to perform the regression analysis. The output from
the regression analysis will be printed at this location.
If you do not see the Data Analysis option on the Data page, click File on left side of the
menu at the top of the page. Next, click Options at the bottom of the menu that appears.
Then, click Add-Ins on the left side of the next menu. Finally, click Analysis ToolPak,
click Go, and then check Analysis ToolPak and Analysis ToolPak-VBA on the menu that
appears, which is named Add-ins available. Click, OK, and the Data Analyis option will
be added to the Data page on your Excel.
5. Compare the beta coefficients you computed in part 4 with the beta coefficients reported on
at least one online financial website, such as Yahoo Finance or Bloomberg. Why might the
betas you computed differ from the betas you found online? Explain/Discuss.
Additional requirements/information:
This is not a group assignmentit should be completed individually.
Type (double-spaced) your discussions/explanations. Your discussions/explanations should
consist of properly constructed sentences. You should use your own words in your discussions.
Do not cut and paste discussions from the book, from the Internet, or from other sources. Your
project will be checked using Turnitin on Canvas. If your Turnitin score (Similarity Score) is
greater than 20 percent, the score you earn on the project will be reduced by one (1) point.
You must complete all computations in the spreadsheet. You must either use spreadsheet
functions or set up equations in the appropriate cells to complete the required computations.
Do not plug in numbers where computations are required or where you should reference a
number located in another cell in the spreadsheet; otherwise, you will lose valuable points. In
other words, the appropriate computations should be completed in the spreadsheet, not on
your calculator. If you input values (numbers) in the spreadsheet rather than using the
appropriate functions or setting up equations to complete the computations, you will lose one
(1) point for each instance this occurs.
If you use ideas or information that are not your own in your discussions, you must provide
citations. You can use any acceptable citation method to recognize the work/ideas of others. Do
not use direct quotes (i.e., verbatim cites) in your discussions. Be sure to include citations for
the sources you use to collect data and any sources used to collect other information included
in your report.
You must submit your project on Canvas. You should submit two files: (1) an Excel file that
contains your data and shows the computations you completed and (2) a Word document or a
PDF document that contains the required discussions/explanations. The spreadsheet you submit
must include the equations/functions used to complete the required computations. That is,
leave the equations/functions in the spreadsheet; do not simply include the numbers produced
by the equations/functions. If you do not submit a spreadsheet, the highest score you can earn
is 5 points out of a possible 10 points.
Projects will be graded on a 10-point scale10 is the highest grade you can receive. Projects
submitted after 11:59 p.m. on March 24 will be penalized two (2) points for every 12 hours of
tardiness such that a late project submitted before noon on March 25 will be penalized two
points and a project submitted from noon until 11:59 p.m. on March 25 will be penalized four
points. Projects will not be accepted if they are more than one day (24 hours) late.