While doing investment in shares we try to perform research on various stocks. We categorize various securities and identify if someone else has already performed it based on certain research.
Every year Economic Times performs similar research and identify Top 500 Companies listed on stock exchange. They publish this list on their site, however it is not available in Excel format. Many of researchers would like to get this into Excel and perform their own further research. In this post I will tell you step by step process hoe to get top 500 companies list into Excel.
Step 1. Search “ET 500 Companies” in your favorite search engine. You should click the relevant link from Economic Times and should land on this page:
Step 2. Click on list Icon and select “100 rows” as follows:
Step 3. Now manually select all the rows from companies table using mouse and shift pressed as follows:
Step 4. Copy above data and paste it into a blank Excel file. It will look like this
Step 5. Perform Step 3 and 4 on all the remaining pages for the list. You should have all the 500 companies list in Excel file similar to Step 4 data
Step 6. For first company manually type these details in left side column and name the both columns as follows
Step 7. Now Click on first column heading, here it is “Data Info” and now with the help of Shift and arrow keys select the all rows in these two columns.
(You may like to check video giving shortcut details for how to select data in Excel here -….)
Once Selected Press Ctrl+L you would get Filter drop down in both these columns like this:
Step 8. Now from drop down select “Blank” from “Actual Data” column and click OK. Once you see only blank rows, select them and delete those rows. Now you should not have any blank rows break in “Actual Data” Column
Step 9. Now select the data rows in ‘Data info” column which you manually type earlier. Copy it across all the blank rows below it. You should get data like this:
Step 10. Insert a Blank column in between these two columns. Give it a name “Company Name”
Step 11. Now put filter on “Data Info” column by selecting “Company” from drop down.
Step 12. Under “Company Name” column select all the rows and press Alt+;
Step 13. Press “=” and click right arrow key once. You should have select cell in same row from “Actual Data” column.
Step 14. Press Ctrl+Enter
Step 15. Select “Rank 2015 & Rank 2014” in “Data Info” column with “Company” still selected. You should get something like this.
Step 17. You should get only blank rows selected now. Press “=” and press down arrow key once & press Ctrl+Enter. You should get details as follows:
Step 18. Now remove “Rank 2015, Rank 2014” from filter on “Data Info” column and select all other items
Step 19. Now select all blank rows in “Company Name” column similar to step 16 above. Once selected press “=” and up arrow once & press Ctrl+Enter.
Step 20. Your final data should look like this.
Step 21. Now select the data in all three columns and create pivot table in new sheet
Step 22. Once you have a Blank pivot table. Put “Company Name” column in Rows area, “Data Info” column in columns area and “Actual Data” in values area. Convert “Actual Data” from count to Sum. Your pivot table areas should like this
And your pivot table on sheet will be like this:
Step 23. Now Press Ctrl+A which should select entire sheet.
Step 24. Copy and paste as values within same sheet itself.
Step 25. Finally here you have data in tabular format and now you can manually remove and rearrange the columns as you require.
Have a look at this video to follow along all the relevant steps: