Firstly, I would like to deeply thank Mr Pratyush Mittal and his team at Screener.in for developing this amazing website and making it freely available to everyone to use, without them this project would not have been possible.
A brief introduction to pivot tables and charts, they are a really handy tool that is a must-learn for sorting and analysing data in Microsoft Excel. I learnt them myself a few weeks back, it is something that almost everyone should be able to use after watching a few videos on Youtube. I have already created a blank template for this tool, and even people who do not fully know how to use these tools can simply copy and paste the raw data and be ready to compare any companies of their choice within a few minutes.
If you are already familiar with Pivot Tables, Pivot Charts and Slicers, you can download the data from any of the below links and get started. If you are not, I recommend watching a few videos to familiarize yourself with the tools before using the templates.
I will explain the procedure to use the template below. This links above will download a folder named “Company Peer Comparison”, once it is downloaded, open it.
In it, you will find 2 folders and 2 files. Starting with the folders first, the “Industry Wise Data” folder contains the past 10 year and 10 quarter data for major companies in major sectors all ready to analyze. I have included most companies in every sector, which were above the 500 cr market capitalization threshold.
Click on any of the excel sheets and the sheet should open to the “PT Chart” worksheet. Most of the sheets will have 2 charts in this worksheet, one for “%” figures and another for “Absolute” figures. Do not worry if your screen does not show the entire sheet, I have sized the sheets as per my screen size. You will have to resize the charts, slicer tables as per your screen size to make full use of the tool. Your worksheet should look like the image below.
On the right, you will find the slicer tables which you can use to get different parameters on which you can compare the companies. This template has over 50+ parameters to choose from. To select multiple parameters to display in one go you just need to have “ctrl” pressed on your keyboard and select the parameters you wish to display simultaneously. You can do the same for the company slicer table, select anywhere from 1 to all companies to view their historical figures. You can even choose to only view quarterly or annual data or both.
Due to limited screen size and excel capabilities, the template works best for comparison of a few companies, up to a 10 is manageable. Beyond that, you may have to sort the companies into sub-segments for the chart to be readable. I have already done that for a few industries where the number of companies was too many to display at once. Try not to select all the data points at once, that can sometimes make your excel crash if your system is not powerful enough and the chart becomes unreadable anyways.
Although 10 years and 10 quarters is a good enough timeframe to compare companies, as the years pass the time series will grow if one keeps adding the recent data. We are limited by the number of data points that Excel can take. I will test the limitations of the template in the future. Once we have enough data, say for 2 decades, I can revise the template, to slice for specific time periods and view the time series data like a 3 or 5-year moving picture.
Sometimes some company’s particular data will be totally different from the sector due to various underlying factors, and the chart may not display the smaller figures properly. You can use the company slicer tab to remove that particular outlier company to get a standard chart. The “PT Chart” worksheet has 2 charts for most of the industries, one is for absolute figure comparison and another for %. scroll down to see the 2nd chart. In a few industries where segmented data is available, I may put more than 2 charts in this sheet. Be sure to scroll down to see all the charts.
As you will notice I have left out the industry data for financial services industry, including banks, NBFCs, AMCs, Insurance, and Broking. These industries have unique KPIs which need to be manually entered from their respective Investor Presentations. You will find the templates for these in the “WIP” folder. This is a work in progress that I have just started and will take time. I have introduced more segmented data based on loan books, capital sourcing, ticket size, geographic distribution, NPAs etc. in these templates. If you are interested in this sector you can take this forward with these templates. If you want to add just the screener data for these industries you can follow the steps below.
Create your own Customized Comparisons
The other two excel sheets in the folder, namely “Astal Poly” and “Blank Industry Data” is what you will need to customize and compare the data for the companies that you want to analyze.
Just go to the link below and upload the the “Astral Poly” Excel sheet to the screener website.
Now search for the company you want to compare in the screener, and export its data to Excel via the link on the top right of the website. Repeat for other companies that you want to compare.
Open this downloaded file and the excel should open by default to the “PT Data” worksheet, if it doesn’t, select it. The tabular data should already be selected for you, if it is not, select the data in the table, without the headers.
Open the “Blank Industry Data” excel sheet, it should by default open to the “Data PT” worksheet. Right-click on the cell that reads, “Ambika Cotton Mills” and then paste the data as “Value”. This step is important, paste the data only as value or else it won’t work. The last column in the table, the “Parameter Type” should populate automatically as the data is entered, if it is not, just copy the formula in the 1st row of the column to other rows.
Repeat the same steps above for the data on other companies that you want to compare. Make sure that there are no blank rows in the table, or the pivot table won’t work.
Once you have all the data copied to the “Blank Industry Data” excel sheet, go to either the “ABS PT” or “%PT” worksheet, right-click on the blank pivot table on the top left. Usually, it should be in the cells A4-B6. Click “Refresh” in the drop-down menu, and the pivot table should populate itself with the data that you had copied beforehand.
At this time it would be wise to “Save As” this excel sheet as per the name of your liking. This will save our work up until now. Now just go to the “PT Chart” worksheet and your PivotChart and Slicers should be ready to use.
If the Pivot Chart and Slicers are not all visible together in your screen, just go to the “Blank Industry Data” excel sheet, go to the “PT Chart” worksheet and resize them to fit your screen and save the template. This will ensure that you will not have to resize them again and again for every comparison that you do.
Updating the Comparison Sheet
Updating the old data with new quarterly, Financial Year figures is easy. Just download the respective company excels from screeners at the relevant time. In the “PT Data” worksheet, go to the time period header in the data table, select the data parameter for the latest time period you want to update (ex. Q3FY20 or FY20) and just copy-paste the new data as value to the old comparison sheet. You can copy-paste the data in any order, the pivot table will sort it out.
After you input the new data, just go to any of the pivot table worksheets, right-click on the pivot table and click refresh. The new data should be updated automatically. That is all.
Time Period Sorting
In the template, the data is already sorted for time periods from oldest to recent. This is important as the data needs to be sorted for time periods in the pivot tables worksheet, and this is how it will appear in the Pivot Charts.
If in future while adding new data the sorting is broken follow the steps below.
Firstly, the automatic sort function in excel does not work on these headers. So here is what we do.
If the time periods are totally random - Find the oldest quarter, right-click it, and press first “M” and then “E” on your keyboard to move it to the end. Ex. for recent quarter data the oldest data is for Q1FY18, right-click it and press “M” and “E” on your keyboard. Q1FY18 will shift to the rightmost column. Repeat this for Q2FY18, Q3FY18 and so on until you reach the latest quarter. Repeat the same for financial years if they are not sorted. You can choose to display them to the left of the quarters in the chart like I have or the right. To move the financial years to the left, right-click on the most recent financial year, ex. FY19 and press “M” and then “B” on the keyboard. Repeat the same in descending order.
If only the latest quarter is not in order, just right-click it and press “M” and “E” on your keyboard to move it to the end. If the latest financial year is not in order, right-click it and press “M” and “U” or “D” on your keyboard to move it left or right until it is in its rightful place.
For Power Users who want to Customize
If you know how to use the “INDEX” function in excel you can you can customize and add your own parameters to the screener sheet in the “Formulae” worksheet and link those cells to the table in the “PT Data” worksheet.
I have hidden the columns in the PT Data worksheet which convert the time periods from the Screener’s date format to quarters and financial years. I have tested the formulae and they work for new incoming quarters as well as for some weird data time stamps that some companies may have. If this feature breaks just contact me and I will try my best to sort out the issue.
If you want to add industry-specific KPIs from the relevant Investor Presentations into the pivot data you can do that as well but it has to be done manually. Just make sure to update the legend on the right of the “Data PT” worksheet for the “Parameter Type” column to update itself. If you introduce some new parameter type into the legend, you will need to update it in the pivot table as well. Usually, it will be in the drop-down menu at the top of the pivot table next to the parameter type cell. Make sure to add the absolute figures in the “ABS PT” worksheet and percentage figures in the “%PT” worksheet for optimal use.
I created this template because I was unable to find similar comparison and customizability options anywhere. If you find this tool useful, please do share it in your investing circles. I believe it will truly benefit the analysis capabilities of all investors.
I also believe that if the data is available the templates can be used in other markets as well. It will open an avenue for comparison and benchmarking with global peers. If you are aware of such semi-automation possibilities in other markets and would like to collaborate contact me on abi.mehrotra@gmail.com
If you face any problems with the template you can contact me on the address above. I will try to solve the issue as soon as I can.
Happy Analysing!!!
