1. In Yahoo!Finance, get stock prices for ONE of your companies for a 9-day period of time (3 days before the announcement/event, the day of the announcement/event, and 5 days after the announcement/event). To do that, open Yahoo!Finance (http://finance.yahoo.com), type the name of the company or the symbol in the Get Quotes window > Historical Prices (left column, under Quotes) > Set the appropriate Date Range for your company's event or announcement > click “Get Prices,” then click "Download to Spreadsheet". This will open up an Excel spreadsheet.
2. Save the new document/file as an Excel spreadsheet (.xlsx) to the z:drive. Use SAVE AS (on your z:drive or portable storage device) and name the file “Stock Performance Index”.
3. Insert 3 rows above the information in the spreadsheet (Date, Open, Close, etc.): Put the cursor into Row 1 > right-click > Insert. Do this 2 more times until you have added 3 rows.
4. In cell A1, write CREATING AN INDEX FOR STOCK PERFORMANCE COMPARISONS. In cell A3, the title “_____________’s (company name) Stock Prices (e.g. Motorola’s Stock Prices).
5. The values from Yahoo should now be in cell A4.
6. Delete all columns except for the date column and the closing prices (highlight the cells to be deleted, right-click Delete and select Shift cells left).
7. SAVE the file “Stock Performance Index”. Minimize the Excel file and maximize the Yahoo!Finance file.
8. Get the S&P 500 values for the same dates (Yahoo!Finance > Historical Prices > Set the same Date Range as you did above for your company's event or announcement > click “Get Prices.” (Another way to get there is to use the Lookup Symbol for S&P Index in Yahoo:^GSPC) and click Historical Prices in the left navigation bar. Then Set the appropriate Date Range for your announcement > click “Get Prices” and "Download to Spreadsheet.")
9. Copy the S&P 500 dates and prices (right-click > Copy), open up your Excel spreadsheet, write the title “S&P 500 Values” into cell E3.
10. Copy and paste the S&P 500 values into your Excel spreadsheet, into cell E4.
11. Important note: All dates need to be in ascending order (from the oldest to newest). To do that, highlight the dates and prices, click DATA (top menu bar) > SORT > A->Z (ascending) order. All dates and prices will now be in the correct order.
2. In cell A16, write the title “DATE”, in cell B16 “PRICE”, and in cell C16 “INDEX VALUE.”
3. Copy and paste the first date (earliest) of your 9 dates and the stock price for that date into cell A17. Copy the next date and price into A18, and so on, until you have all 9 dates and prices.
4. Write 100 into cell C17.
5. For calculating formulas or equations, Excel uses the = sign. Formulas can be put into the cell itself using the = sign or into the fx window. In cell C18, write = . Then click cell B18 (the price on the second day) and see how the program puts “B18” into the cell. Then write “/” (divide) and click cell B17 (the base price, or the price on the first day). See how the program puts “B17” into the cell. Then write “*100” (multiply by 100). This is the index value for Day 2, and you have used the formula for creating an index. The general index formula: Take the new value, divide it by the old/base value and multiply by 100.
6. Instead of calculating each index value, you can use a shortcut (for the operation in #5): Click the cell in which you want to calculate the index (C18); then click or type =. Click the cell with the new value (B18). Type “/” (divide) and click cell B17 (the base value). Since each following value must be divided by the base value, you need to lock the base value. (WARNING: Simply copying and pasting will yield incorrect results!) To lock the value of a cell, place a dollar symbol before and after the cell (since the index or base value always remains the same), e.g. $B$17*100, and type *100. Now you have the formula. Copy and paste the formula into the remaining cells. To do this, click in cell C18. Then do a right-click and select Copy.
7. Highlight all remaining 7 blank cells in the index column, do a right-click and select Paste Special > Formulas. The program copies the formula for all remaining days. GREAT, ISN’T IT?
8. Your values should only have 2 decimals (after the period). To change your values to this format, highlight the column that needs changing, right-click, and select Format Cells. Click the Number tab and click Number and OK (the default is 2 decimal places).
9. In column E15, write “INDEX VALUES FOR S&P 500”.
10. In columns E16, F16, and G16, write “DATE”, “VALUE”, and “INDEX VALUE”.
11. Follow the same procedure as above to copy and paste the dates, values, and to calculate the index values.
1. In cells A27 and B27, write “DATE”, “__________ (company name) INDEX VALUES”, and "S&P500 INDEX VALUES.
3. Copy and paste the dates into cell A29.
4. WARNING: To copy the index values for your company, highlight the values, click Copy, then Paste Special > Values into cells B29-B37.
5. Repeat Step 4 for the S&P 500 and put the values into cells C29-37.
6. Highlight all the data for cells A27 to C37.
7. Click on the Chart Wizard icon (function/icon toolbar at the top) (MS Excel 2007/2010: Click Insert > Line to insert a Line Chart). Your graph should appear on your spreadsheet or on a new sheet.
8. Select Line and a graph with markers for data points (left graph in row 2).
|
Excel 2003 |
Excel 2007/2010 |
|
9. In the Data Range tab, select Columns. 10. In the Series tab, click the first series, then Name and write “Index Values _______________ (company name)”. 11. Click the second series, then Name and write “Index Values S&P 500). Click Next. 12. In the Titles tab, put the cursor into the Chart Title window and write “Performance of ___________’s Stock vs. S&P500”. 13. In the “Category (X) axis” window, write “Date”. 14. In the “Value (Y) axis” window, write “Index Values”. 15. Click Next and select “as object in sheet 1”. Then click Finish. 16. Your graph should appear on your spreadsheet or on a new sheet. 17. You can move the graph to the second page if you like. 18. SAVE AS with the same file name as above. 19. Print the spreadsheet and the graph. See sample indexes and graphs:
|
9. Select the graph by clicking inside it. 10. Select Layout on the top menu bar. Add a Chart Title: “Performance of ___________’s Stock vs. S&P500”. 11. Select Axis Titles and write "Index Values" on the vertical axis and "Dates" on the horizontal axis. 12. In the Titles tab, put the cursor into the Chart Title window and write “Performance of ___________’s Stock vs. S&P500”. 13. You can move the graph to the second page if you like. 14. SAVE AS with the same file name as above. 15. Print the spreadsheet and the graph. See sample indexes and graphs:
|
© 2001 Christine Bauer-Ramazani. This page was last updated: February 27, 2012