The following files are attached. (IMPORTANT INSTRUCTIONS BELOW - PLEASE READ)
1. The case file
2. Start sheet for excel
3. A short video showing you how to set up your sheet correctly. - Although the Yahoo finance page has changed, the essentials remain the same. Please note one critical point though - Instead of last trade - use the 'price' field.
4. The formula to convert Capitalization into number figure - =LEFT(E2,LEN(E2)-1)*(IF(RIGHT(E2,1)="B",1000000000,1000000))
5. I left out the formula for calculating commission on purpose. You should be able to figure that out.
6. There are a couple of ticker symbol that don't exist any more. Please ignore them. However, you will need to make a note of them in your writeup.
7. Piviot table tutorial (right click - open in new window)
[login to view URL] (Copy paste in a new window please)
8. Database functions in Excel 2010
[login to view URL]
(Right click, open in new window)
9. Once you import the data, remove the auto refresh as well. It works only when you do the project on one computer in one seating, otherwise it will mess up your sheets.
10. There is a question regarding which fields to select from yahoo - below is the screenshot (not, that the order of fields depends on how you want to get your data across and you need to change it to your liking)