ET 500 companies list in Excel

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:

1. ET Web Page

Step 2.      Click on list Icon and select “100 rows” as follows:

2. Increase List

Step 3.    Now manually select all the rows from companies table using mouse and shift pressed as follows:

3. Selecting and coping data

Step 4.    Copy above data and paste it into a blank Excel file. It will look like this

4. Data in Excel

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

5. Typing details

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:

5. Filters

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:

7. Details copied

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.

8 Filtered Data

Step 16.      Now select all the rows from “Company Name” column and press Ctrl+G. Click “Special” button visible on screen. Now select “Blank” as follows and click ok.9 selecting blanks

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:

10 Filling Company Name

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.

11. Full company name filled

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

12 Pivot Table Areas

And your pivot table on sheet will be like this:

13. Actual Pivot Table

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:

One Comment

Leave a Reply

Your email address will not be published. Required fields are marked *