How to Analyze Toyota Motor Corporation (TM) Stock Prices 2006-2018

The Preparation

For this project, you will access YAHOO! Finance to get the stock prices of the Toyota Motor Corporation. On the YAHOO! Finance start page ), Enter “TM” (i.e., this is the stock ticker of Toyota Motor Corp.) in the search field, and click search. Then click on the Historical Data tab. You then need to set the Time Period from January 1st, 2006 up to December 31, 2018. Please select the ‘Monthly’ option for Frequency and ‘Historical Prices’ for Show, and then click Apply. Once the table appears, click on the Download Data link and save the data file, which will be in the .csv format (csv – comma separated values). Open the .csv file using Excel and save it as an .xlsx file using the Save As option.

Once you have brought the market prices (these are the company stock prices for each month for the thirteen years, i.e., 2006 - 2018) to Excel you are now ready to perform some operations on the imported data (20 points):

- (Note: Please make sure you are doing the following operations in the .xlsx file)

1) Delete the “Adj Close” and “Volume” columns

2) Adjust the width of all columns to avoid displaying “####” error messages;

3) Align all columns to the left;

4) Make the table presentable using a professional table style;

5) Set cells that display prices to Currency format

6) Insert a header to the worksheet to indicate the name of the company and the date range for the stock prices (i.e., “Toyota Motor Corporation (TM) Stock Prices 2006 – 2018”).

7) Insert a footer to the worksheet to indicate the data source (i.e., “YAHOO! Finance”).

8) Sort the data based on the Date so that it starts with the January 2006 prices.

9) The table should have a title, i.e., “TM Stock Price Analysis 2006 – 2018” at the first row and columns neatly labeled.

10) Rename this worksheet to “Q1-Stock Price”

Hint: This table should appear in one worksheet.

The Task

Q1: (Still in the Q1-Stock Price worksheet) Calculate the average, minimum, maximum, and median of the open, high, low, and close stock prices for each month of the 13 years. (10 points)

· Average of (open, high, low, and close stock prices)

· Minimum of (open, high, low, and close stock prices)

· Maximum of (open, high, low, and close stock prices)

· Median of (open, high, low, and close stock prices)

Hint 1: It is all done in one table in one sheet. You simply need to create additional columns (one for average, one for median, etc.) and then, horizontally, calculate the stats for each row using Excel functions (for example, average of low, high, open, and close).

The Task

Hint 2: Now your table should include 9 columns (i.e., Date, Open, High, Low, Close, Average, Minimum, Maximum, Median) and 158 rows (the first row is table title, second row is column titles, rest of rows are data).

Q2: Create a pivot table in a new worksheet (rename the new worksheet as “Q2-Pivot Table & Chart”). The pivot table should display the stock values (average of close, average of open, average of high and average of low) for all years. Using the filtering option present on “Row Labels” column in the pivot table (Cell A3), select the years from 2011-2018. Then, on the PivotTable Fields panel, drag the ‘Date’ field and drop it to the Filter area. From the filter, which is now shown in the first row of the worksheet (above the pivot table), choose December. Change the number format of the pivot table to ‘Currency’ with two decimal places and displaying ‘$’ symbol. Create a line pivot chart from the pivot table. Put the pivot chart next to the pivot table. (15 points)

Hint 1: when you drag a field to the Values area of the PivotTable Fields, Excel calculates Sum by default. You need to change this to Average as appropriate.

Hint 2: The heading of the rows in the PivotTable should include the years (2011, 2012, …, 2018) and the heading of the columns should include Average of Open, Average of High, Average of Low and Average of Close.

Important Tips about the Pivot Table:

If you have a problem with finding the "Years" field in the pivot table's Field Pane, that this probably happens when you are using an older MS Excel version. In the latest version, Excel automatically performs time grouping in the pivot table. With time grouping, "relationships across time-related fields are automatically detected and grouped together when you add rows of time fields to your PivotTables. Once grouped together, you can drag the group (in this case Year) to your PivotTable and start your analysis." However, in the older versions, you may need to do the grouping manually. It is thus advisable to use most recent MS Excel. If you have to use an older version, here is a link that tells you how to do the manual grouping:

* If you still don’t see Years in the field panel, drag and drop Date to Rows. It should automatically create two new fields, Quarters and Years. Keep the Years in Rows, remove Quarters from Rows and move Date to Filter.

Here is a (very) short video from Prof. Soliman on how to create a pivot table in Excel: 

Q3: Copy the “Q2-Pivot Table & Chart” worksheet. Rename it as “Q3-Pivot Table & Chart”. In the worksheet, Q3-Pivot Table & Chart, remove the Date field from Filters area on PivotTable Fields. Using the filtering option in Cell A3, select all years (from 2006-2018). Comment on the updated pivot chart when compared to the pivot chart with only 2011-2018 stock prices. What could have happened to cause this dip in Toyota’s stock prices in 2008/2009? Type your answer in the same worksheet (i.e., Q3-Pivot Table & Chart) below the pivot table and pivot chart. Merge multiple cells to create a large textbox to put your answer in. You can do research on the Internet to help you figure what happened in 2008/2009 that significantly affect Toyota’s stock price

Q4: Visit Canada’s Greenest Employer website  which lists Canada’s Greenest 100 Employers and check the 2020 winners. You can see that Toyota Motor Manufacturing Canada Inc. / TMMC is listed among Canada’s top 100 employers for 2019. Click on the Toyota link and read about why they were selected (You might need scroll down to see the section). Visit Toyota’s webpage , see what efforts Toyota has made to improve sustainability. Based on the information you read, answer the question: Why do you think that Toyota deserves this Greenest Employer award? Create a new worksheet and rename it as “Q4-Toyota Sustainability Effort”. In this worksheet, merge multiple cells to create a large textbox to put your answer in. You answer should be 3-5 paragraphs long and should demonstrate your ability to gather and analyze qualitative information presented in corporate websites and other sources and arrive at a conclusion