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) > Symbol Lookup > Company name > Historical Prices (left column, under Quotes) > Set the appropriate Date Range for your company's event or announcement > click “Get Prices.”
2. Copy the values (right-click > Copy) and minimize the window.
3. Open up Excel and start a new file.
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. Paste the values from Yahoo into cell A5 (right-click > Paste).
6. Delete all columns except for the date column and the closing prices.
7. SAVE AS (on your z:drive or portable storage device) the file “Stock Performance Index”. Minimize the window.
Maximize the Yahoo!Finance window and get the S&P 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.”)
8. Copy the values.
9. Open up your Excel spreadsheet, write the title “S&P 500 Values” into cell E3.
10. Copy and paste the values into your Excel spreadsheet, into cell E5.
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 A16. Copy the next date and price into A17, and so on, until you have all 9 dates and prices.
4. Write 100 into cell C17.
5. 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: Click the cell in which you want to calculate the index (C20); then click or type =. Click the cell with the new value (B18). Type “/” (divide) and click cell B17 (the base value). Then place a dollar symbol before and after the cell to "lock" its value (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 C19. 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 A28 and B28, write “DATE” and “INDEX VALUES”.
2. In cells B29 and C29, write “___________” (company name) and “S&P 500.
3. Copy and paste the dates into cell A30.
4. Careful: To copy the index values for your company, highlight the values, click Copy, then Paste Special > Values into cells B30-38.
5. Repeat Step 4 for the S&P 500 and put the values into cells C30-38.
6. Highlight all the data for cells A29 to C38.
7. Click on the Chart Wizard icon (function/icon toolbar at the top).
8. Select Line graph and a graph with markers for data points (left graph in row 2). Click Next.
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 a sample index and graph for Motorola and for Best Buy vs. the S&P 500.
© 2001 Christine Bauer-Ramazani. This page was last updated: April 08, 2008