Clustering Stock Returns USE EXCEL AND SHOW STEP BY STEP PLEASE!!

When building portfolios of stocks, investors seek to obtain good returns while limiting the variability of those returns over time. This can be achieved by selecting stocks that show different patterns of returns. In this problem, we will use clustering to identify clusters of stocks that have similar returns over time; an investor might select a diverse portfolio by selecting sticks from different clusters.

For this problem, we will use the dataset NasdaqReturns.cvs which contains monthly stock returns from the NASDAQ stock exchange during 2000-2009. The companies selected in this dataset are limited to those that were listed on the stock exchange for the entire time period and whose stock price never fell below $1. The NASDAQ is the second largest stock exchange in the world, and it lists many technology companies. The variables in the dataset are described in the table below

Variable	Description
Stocksymbol	The symbol identifying the company of the stock
Industry	The industry the stock is classified under
Subindustry	The sub-industry the stock is classified under
Ret2000.01-ret2009.12	The return for the stockduring the variables indicated month. The variable names have the year format RetYYYY.MM where YYYY is the year and MM is the month. For instance variable Ret2005.02 refers to February 2005. The value stored is a proportional change in stock value during that month for instance, a value of 0.05 means the stock increased in value 5% during the month, while a value of -0.02 means the stock decreased in value by 2% during that month. There are 120 variables for the 12 months in our dataset.

Analyze the data by answering the following questions (use excel functions, pivot tables and analytic solver platform tool when necessary - avoid manual calculations such as counting by hand etc submit the excel files showing your analysis).

Submit three files: one word, one excel and one power point presentation (there will be an assignment submission open on blackboard - one submission per group).

a) Let us start by exploring the data

1.How many companies are in the dataset? How many companies are in each of the industries?

2.In the aftermath of the dot-com bubble bursting in the early 2000s, the NASDAQ was quite tumultuous. In December 2000 how many stocks in this dataset saw their value increase by 10% or more? Decrease by 10% or more?

3.Entering the Great Recession, most stocks lost significant value but some sectors were hit harder than others. In October 2008 which industries had the worst average returns across all stocks?

b) Let us now cluster the stocks according to the monthly returns. For the remainder of this problem, make sure that you are just clustering the observations based on the variables Ret2000.01-ret2009.12 (Stocksymbol, Industry and subindustry should not be used to cluster the observations). You can do this by creating a new dataset only containing the variables Ret2000.01-Ret2009.12, or you can just give these variables to the clustering algorithm.

a. In this problem we will not normalize our data prior to clustering why is this a valid approach for this problem and dataset?

b. Cluster the data using Hierarhical clustering. Clearly indicate which distance metrics you used for point distances and cluster distances. Plot the resulting dendrogram. What do you think are reasonable choices for the number of clusters to select, based on the dendrogram? Select a specific number of clusters to use for the rest of the problem and justify your choice.

C. Extract cluster assignments from your hierarchical clustering model, using the number of clusters you selected in the previous problem. Describe each cluster using the number of observations in the cluster, the industry of the companies in the cluster and the subindustry of the companies in the cluster.

D. For some months we expect there to be significant differences between the returns of stocks in different clusters. For February 2000, do some clusters have negative average returns while other clusters have positive average returns? How about for March 2000?

E. Now run the k-means clustering algorithm on this data (when clustering, only use the variables Ret2000.01 -Ret 2009.12). You should select the same number of clusters that you used for Hierarchical clustering. Extract cluster assignments from your k-means clustering model, and compare them to the Hierarchical cluster assignment. Are the clusters similar or different?

In the introduction to this problem, we discussed the value of a diverse portfolio and how we might achieve this objective by selecting stocks from different clusters. Propose a diverse portfolio of stocks using the results of your clusters.