Using Pivot Tables/Charts, Slicers & Screener to Semi-Automate Historical Peer Analysis

Hi Edward, you are right about most bets being unfavourably priced most of the times. That is the nature of markets. In games like horse betting, blackjack, roulette the payoff is just not in your favour. House always wins in the long run.

Everyone will agree that in a casino the payoff on the bets are priced by rational people and it is the gambler who is irrational in games like roulette. Yes people do win sometimes but that is just pure luck and Ole Peters work on Ergodicity, Ensemble and Time probabilities explain this beautifully.

Markets are beautiful in a way that one can do the groundwork beforehand on many bets and wait for the odds to come to you. The payoffs are ever-changing in the market system and sometimes, just sometimes the pay-offs are priced irrationally. Whether one has the wherewithal to wait for the payoffs to come in your favour is an edge in today’s high-speed world. That said, all sorts of people both make/lose money in the markets in all sorts of ways. Society underappreciates the role luck plays in its endeavours. I just believe some bit of work can position one in the direction of luck.

Data In the Sheets

I just provided the historical valuations in the sheets so that one can be careful buying at market highs. People who are interested in cyclical companies can also use the historic P/B to find optimum entry/exit points.

Thanks for the suggestion about the tools that can further automate this exercise. I will try and use them if I can. I exported the company excel files manually so there was no worry of crowding out the screener’s servers.

You are right about screener data not being always right, I don’t use it always to value companies. But most of the time the data is in the ballpark, and all valuation exercises should be range approximations anyway.

For historical PE/PB I used to use rate star for quick reference or just download the historical price data from the exchanges, sort out the annual, highs, lows and averages and use them with the data from annual reports or investor presentations. All inconsistencies in the data of the data provider get highlighted when one does a deep dive into the company through the actual company reports.

The majority of manual data input work is in the financial services industry, where the KPIs are customized. Even companies in the same sub-segment of the industry report the KPIs with different names. I doubt there is any way to automate this process of picking up the data from the company’s investor presentations and moving it to excel. The data is just not structured. If you have any inputs that can help with this, it will be greatly appreciated.

Other Strategies

To sum it up, probability of finding good bets/investments reduces dramatically near market peaks when you could pick majority of new bets and be down in the medium term and vice versa in market bottoms.

If one believes this is like finding a needle in a haystack they can just do a simple momentum investing strategy believing that it is all in the price and outperform the index.

I have seen other strategies outperform the above strategy but yes there is a survivor-ship/luck bias here. Though the % of total people outperforming with momentum will be higher than the % in other stock-picking strategies. One can speculate on the quantum of out-performance.

To hedge against this luck factor one should ideally run both or more type of strategies in their portfolio. But only do as much as one can with full effectiveness.


A brief update, a VP user, has highlighted discrepancy in the per share data such as P/E & P/B for some companies.

The data of companies with recent corporate actions like bonus, split are affected and the per share figures may not be accurate depending on the age of the corporate action and the updating of relevant data from Screener website.

The Screener website is accurately showing the figures however when the same are exported to excel the per share data does not reflect the latest corporate action.

I am not yet sure with what frequency they update their data in the excel tool, perhaps they do it once every financial year, until then please do not completely rely on these comparison sheets for per share data and refer to other data sources as well if the per share data seems suspect.

I am going to make this thread a repository of all the investing tool kits that I create for myself.

Here is the 2nd tool kit. A google sheet that provides in-depth analysis of a stock’s price history.

Functionalities as of today include:

  1. Return and CAGR since listing.
  2. All-Time high, 52-week High/Low and % difference between CMP and these milestones.
  3. Drawdown % and days.
  4. Average, Median, Standard Deviation, Maximum, Minimum, of Monthly/1/3/5/7/10 year rolling return periods.
  5. Win/Loss scenario on the basis of +ve and -ve returns in the above periods.
  6. Win/Loss scenario and win probability on the basis of a certain % returns (by default 12%).
  7. Includes charts of Price, Volume and 20/50/100/200 DMAs, Drawdown %/Days, and Monthly/1/3/5/7/10 year rolling return periods.

Here is a database that I update from time to time about companies, corporate governance issues etc. There is a legend available at the end, not all companies present in the list have CG issues. Most of these observations come after the fraud is exposed.

Sharing a Dropbox link that will update itself as I update the sheet. A natural evolution of this exercise would be to track down the BOD, promoters, auditors, KMPs of the company with CG issues and create a watch list of offenders to avoid in the future.

This is surely not a comprehensive list, but with collaboration we can make it more extensive. So if you find any old or new additions that deserve a place on this list, then do contact me here.


This is good list, thank you
I was asking Manish and Donald to allow threads on negative value (deletion) companies where people can be warned on cash flow discrepancies and promoter dishonesty

There are many thread already existing for that

Yes and no, these threads are running over many many posts. If I want to find out, if Vikas ecotech financials are solid, do you propose I read through all the above threads ?
Someone might have already found discrepancies, and might want to put it as a thread with something like “Vikas ecotech - problems” and give a proper analysis of where those are

Ofcourse most of us can find these ourselves.

Look at Shivalik rasayan - big issue is they have a subsidiary called medicamen biotech in which they own 60-70pc. On consolidated p&l they take the entire profit of medicamen without showing minority interest on profit and loss account

What you suggest is put all in one thread is not practical although a bit more aesthetic as the site is not cluttered.

Each company has a thread in which forensics are posted or cross referenced… I normally read the entire thread of the company that I want to invest. One can always use the search function for bigger threads.

Perhaps I don’t understand your requirement correctly.

1 Like

This is very interesting. However page is not rendering fully at least for me. Screenshot attached. Using Chrome Browser

Yeah, I saw that. It was designed on a 27-inch screen. Wasn’t showing completely on my 24-inch one. You can try and zoom out to see the full page until I try and figure this out.

Tried that but not able to zoom out on the laptop.

added a few more segmented dashboards

Data dump on microfinance sector. Let me know if you have any queries.

1 Like

Did some work on Debt funds. Assimilated data from Value research. Use the sheet PT Chart Final.

all-debt-funds-22-Jan-2021–16111.xlsx (823.9 KB)

1 Like

Simplest momentum screen on Screener. Copy-paste the below equation to the screener. Just rank the stocks in descending order of 1yr returns. You can add other filters to your liking. Then pick the top 10/20/30 as per your liking. Rebalance weekly/monthly/quarterly as per your liking. No need to pay an absurd fee to small case providers for something you can DIY. Especially important if you are starting with only a few lacs. The fee will eat into your return.

Return over 1year > 0


I have added some statistical analysis to the previous work I had done on Debt funds. You can find it in the sheet GF PT Chart. The data is only current up to today. It uses the different rolling period returns to calculate the statistics. I did the exercise for funds from HDFC only and chose only direct growth option. I have tried to cover one fund from each category. The win probability parameter uses a winning criterion as returns 7% CAGR in that time period. Let me know if you have any queries. Created on a 24 inch screen so best viewed on a screen of same size. You can either resize or zoom out if you are on a smaller screen.

all-debt-funds-22-Jan-2021–16111 Shared.xlsx (817.1 KB)

Created a shareable tool that should be able to calculate the XIRR of portfolio and compare against similar SIPs NIFTY50/500.

Please test and let me know if it breaks anywhere. Suggestions about improving further, adding more features are most welcome.

1 Like

NSE Microcap250 rankings up now on the website.

NSE750 (NSE500+NSEMICROCAP250) rankings up now.

1 Like