ValuePickr Generics Pharma Dashboards - Focusing on asking the right questions!

Continuing the discussion from Shilpa Medicare -Racing away on the Oncology API highway!:

Hello Fellow ValuePickr’s,

Would like to formally share the first version of the Pharma Analysis Template

I would not have got off my backside, if not for the challenge laid down by @Donald and the excellent analysis done by @ankitgupta on Alembic Pharma which served as the core/foundation.
Also would like to highlight @ananth important feedback and valuable suggestions along with Ankit.

Main purpose of creating a separate thread is to share the basic usage guidelines.


  1. Primary source of information is Pharma Compass
    It is recommended to have a brief look at the site before proceeding further.

  2. Please visit Ananth’s VP Chintan Baithak Thread

Below sheets are the MAIN Input sheets

  • Key information required to populate these sheets automatically is to update the formula in A1 of the respective sheets, with the correct URL and Table ID.

  • For the patents, update the URL with the correct parameter
    n = no.of results/page (default=10). It can be changed to any value.
    p = page number (default=0). Keep the default value
    q = company name

  • US DMF Lists is the complete DMF Mater List periodically updated by US FDA. It needs to be manually imported into this sheet.

  • External Sources: From Research Reports and Trade Journals, which can be used to finalize the list of API’s under development or to be filed.

Analysis Sheets - Where you focus on asking the right questions

Existing DMF Sheet:
In cell A1, you can see the query being used to fetch the DMF’s filed and Active in USFDA DMF List. Here the query needs to be updated with the target company name.

Column G - the API name should be adjusted depending on the url which contains the info.
It may need to have underscore in case of “Irinotecan Hydrochloride”. Or, it may need to be truncated to only the 1st word. (Which is why it is recommended to have a feel for the pharmacompass site)

Column H - the baseline Table ID based on which all the relevant information abt the specific API molecule is retrieved. It corresponds to the “Finished Dosage” Section.

Similar logic applies for “DMF Pipeline” sheet.

That is pretty much it! Simple :smile:

There is scope of further enhancements, based on feedback.
Features to expect in next update:

  1. Incorporation of 180 day exclusivity tracker

Happy Investing!


Amazing stuff.
Will be good to put this to test for a generics pharma business we haven’t investigated.

How about NATCO Pharma :slight_smile: ?

@crazymama Great work!

I agree on the testing idea.

With me being the developer, will have a natural bias to any defects/bugs :smile:

It would also give an idea abt the ease of use.

Simply outstanding stuff … :smile: Congratulations and best wishes… You deserve a standing ovation :smile:

brilliant work. This single sheet can decode big pieces of pharma puzzle

Absolutely brilliant stuff…shall definitely help extrapolating prospects in forseeable future.

This is outstanding! I have never seen such level of thoughts put to work! I hope I learn to use it sometime soon. Thanks people! :smile:

I think one point to note here is how much automation we do there is no substitute of preparing the pipeline on our own. You actually get the feel of thing and many times you end up knowing each molecule by its name, innovator, market size, patent expiry etc.


Couldn’t have said it better!

Which is why the 1st column is a manual entry in “DMF Pipeline” sheet.
Besides, some of the columns you have alluded to such as Market Size, Have Generics been launched?, is it a Para-IV filing? have been intentionally kept manual.

Why should we have all the fun of discovery? :slight_smile:


Every time I open this template, my browser crashes. Has anyone else faced this issue? I use Mac and Safari browser.

Hello Rupesh,

I have built the template using Chrome Browser on Windows 8.
Most recent check showed atleast 4 other viewers who had simultaneously opened the template.

Until otherwise, it is most likely that it is Mac/Safari issue.
Can you retry with Chrome on Mac?

Dear Vishnu,
It works fine on chrome.


Hi @crazymama

I have one Questions regarding Tamsulosin_HCl in CompassPharma it’s showing unichem DMF is approved but in Orange book details are not present.

Could you help me in clarifying the same.


Hello Vishal,

Orange Book shows the ANDA’s approved for a given API or Branded Drug.

If you notice the Application Numbers in the shared snapshot, they have a prefix of either N or A.
N - New Drug
A - Abbreviated New Drug which implies a Generic.

DMF Master Lists are available from the US FDA Site

Hope this clears the clarification. :smile:



Found another site which gives good sales figures.


Hi Vishnu,

I have observed that the best source of getting the data for most of the drugs is the company’s investor presentation or their annual reports where they give drug wise date (mostly in case of blockbuster drugs) as well as US sales for it.

Talking about Abilify in particular, you would have to add Otsuka’s sales in US (as it was marketed by Bristol Myers in USA) which was around USD 5 billion (source: Otsuka presentation).

1 Like

Hello Ankit,

I agree Otsuka figures are not included.
In fact PharmaCompass also provides the sales figures for 2013, 2014 and Growth Difference

However, the data is restricted to only the major pharma co.s (BIG Pharma :smile: )

On the other hand not all drugs are having multiple parties like the case of Abilify (BMS/Otsuka).

Idea is to provide a resource when the drug is manufactured and marketed by the same co.

Hi Vishnu,

Lets work on finding the ANDA filing of companies which are not backward integrated fully (dont have APIs for all their ANDA filings) like Torrent. It will be pretty difficult but we can get some information through research reports, concalls etc.

I am currently working on the pipeline for Torrent and will share my findings in few days.


Dear Ankit,

Great to hear that you are working on Torrent. Even I am working on it and planning to post by next weekend. It would be great case study to learn pharma co research. Prima facie, it looks set to gain alot along with Alembic from Abilify.