## Category Archives: investment

## investment project

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.

## investment project

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.