Category Archives: Pharma Automation

Pegasus‒A VBA based Drag and Drop tool in MS Excel [2/2]

Hello friends!

I apologize for the delay in this post. Firstly, here is the working version of the tool (click here to download).

I’m going to keep this post short and simply going to upload the working version of Pegasus for you to explore. The principle by which the drag and drop feature works in Pegasus is really simple—it activates the built in properties of two list boxes wherein a value from one list box is dragged into another. The trick here is to make the second list box (to which the value is being dragged) transparent so that the user is able to see the formatting on the sheet.

Although the underlying code for the tool is self-explanatory and most of it should be easy to follow, I would like to highlight the following important snippets: Continue reading

CompTraX – A Microsoft Excel-based company profiling tool [5/5]

Hello friends,

This is the final post in the CompTraX series.

As discussed in my last post, this update focuses on exporting all the companies of interest from the Excel tool to the PowerPoint template in one go. The tool now also creates a table of content in the beginning of the presentation. You can download the final version of the tool here (.zip ; .xlsm)

The video below demonstrates the tool in action:

This update is mostly dependent on VBA. One of the important code snippets for this tool is the part that copies the charts and relevant text fields (company overview, news item and pipeline information) from Excel and exports them on the slide: Continue reading

CompTraX – A Microsoft Excel-based company profiling tool [4/5]

Hello friends,

Its time for a new version of CompTraX. But, let’s start with a recap first: In the last couple of posts we built a company profiling tool that allowed the used to feed data in one sheet (Database Sheet) of an excel workbook and then visualize it on another sheet (Display Sheet). This set-up insured a neat representation of data on MS Excel files. While this solved half of the problem (dealing with multiple column profiles) it did not solve the other half i.e. how to take all of this to a PowerPoint slide (after all, company profiles look best on PowerPoint slides).

So, for this post I have built a new feature in the tool—called Xport—that allows you to export profiles of your choice to a PowerPoint slide. Things are better experienced than read, so,  download Version 3 of the tool (click here to download) and extract the content of the zip file in a directory of your choice. Now open the CompTraX tool, select a company that you want to export (from the drop down) and click on Xport. Wait till you get a message confirming the completion of the export process.

 

CompTraX V3.0 in action

How does this feature work? Continue reading

CompTraX – A Microsoft Excel-based company profiling tool [3/5]

Hello friends,

As mentioned in my last post, I have added a new feature to our CompTraX tool. Download Version-2 of the CompTraX tool (Click here)—so that you can explore it while reading this post.

Now let’s discuss the enhancement that I have made in this version. One of the limitations in Version-1 of the CompTraX tool was the fact that there was limited space to display text in the ‘Company Overview’, ‘Recent News’, and ‘Key Pipeline’ sections (highlighted in red in picture below).

What this meant is that, if one were to write large paragraphs in these sections, all of that would not fit in. This was obviously not acceptable from a user’s point of view—no one likes missing text. So, to overcome this limitation, I came up with a VBA based solution.

The solution itself was a two-step approach:

Step 1: Calculate, with the help of formulas, if the input text is small enough to fit in the space allocated for it. If yes, display the full text and if not, then curtail the text up to a certain limit (say 500 characters) and append a text that prompts the user to click on the cell/Range containing the article (‘click for more’; what we usually see on web pages)

To do this, I used the LEN, LEFT & IF functions. A generic version of the formula  that I have used would be:

IF (LEN(Some_Text)>500,LEFT(Some_Text, 480)&”………click for more”, Some_Text)

[For help on these formulas, visit the Bare Essential Formulas section in Dashboard 101 tutorial]

Step 2: If the user clicks the relevant cell/range (one containing company profiles, news item and pipeline), display a pop up window containing the full text. The pop up window is created using 4 different shapes and some VBA to show/hide them, as needed.

Gif image showing the dashboard in action

You can find the VBA code that goes behind this enhancement in the ‘Display Sheet’ on the VBA interface of the workbook  ( with appropriate comments to make it easy to understand for beginners)

For those of you who are new to VBA, we are soon planning to start a new series called ‘Dashboard 102′ which will cover a lot of basic concepts of VBA.

As always, feel free to contact us at query@crabsheet.com for any queries that you may have.

Keep Excelling !

CompTraX a company profiling tool – small update

Hello friends,

The last couple of weeks have been hectic and we have been busy like bees, uploading the Dashboard 101 tutorials. This has kept me away from the blog—thankfully not for too long—and from the CompTraX updates.

I am back on track now and today, I would like to explain a set of formulas that I used while building the first version of CompTraX

You would have noticed that there is a sub-section on the Display Sheet of CompTraX that displays recent news.

spin button and news section

This section fetches the data from the Database Sheet with the help of a spin button (circled in red above). The cells which hold the relevant data in the database sheet look somewhat like:

snippet of database sheet containg news on pharma company

These cells have different news items in the same cell; separated by a line break (I have added color to different items to differentiate them in the pic). However, the Display Sheet shows the news items one by one on clicking the spin button. So, how is this accomplished?

The key lies in a group of four cells on the Process sing Sheet which looks something like this:

processing sheet

Cell 1: takes input from the data base sheet i.e. the group of news items

Cell 2: takes input from the spin button and helps decide which news item to show. E.g. in the image above the value ‘1’ means—show the first news.

Cell 3: has a formula that adds an identifier to demarcate the start and end of a news item. The formula is:

= SUBSTITUTE (SUBSTITUTE (A44, CHAR (10),”start1start”, B44), CHAR (10),”stop1stop”, B44)

This formula replaces the first instance of a ‘line break’ with the keyword ‘start1start’. Then it again acts on this modified string and replaces the first instance (which would earlier have been the second instance) of ‘line break’ with ‘stop1stop’. So we have a demarcated news item. Something like this:

‘start1start news item stop1stop’

Cell 4: has a formula which extracts this demarcated news item. The formula is explained below:

explanation of formula

It might seem a bit complicated at first look, so, I will try to explain it using another example—see the picture and the maths below.

mathematics

Let’s say cell ‘A44’ has some text value. Formula in Step 3 adds a start and stop position to this string. Say this is stored in cell ‘C44’. Now, Step 4 extracts the demarcated news item from this string. As mentioned earlier, the entire set of formula is dependent on the spin button and with every click of the button the next news item is extracted.

That’s all for this post; hope you find this useful. In the next post I will upload a new version of CompTraX which would have a search interface. So, stay tuned and keep Excelling!

CompTraX – A Microsoft Excel-based company profiling tool [2/5]

As promised, I am posting the first version of the Microsoft Excel-based company profiling tool—CompTrax. You can download it by clicking here.

Now let’s discuss the details. I have made the tool in-line with our earlier discussion (about the key fields in any company profiling request). So let’s start with the interface.

layout of the tool

I have kept it really simple—all one needs to do is select a company from the drop-down box on the right-hand-side top corner and everything changes accordingly. There are four main sections to this dashboard 1) company overview 2) recent news 3) pipeline molecules details and 4) financial details

Overall the dashboard follows a three sheet structure. The display sheet is where the user makes the request (the drop-down), the processing sheet is where all of the relevant data is fetched by simply using formulas, and the database sheet is where I have placed all the company specific data. I have put the data for only three companies here, but you can increase it to any number you want—the tool is completely flexible on that front.

layout of the CompTraX

While the display and the database sheet would need inputs from the user, I would ideally hide the processing sheet, if I were to send it to someone—unwanted changes here would result in tool malfunction.

So, this is how the tool works:

CompTrax in action

Ok, let’s list down the different formulas, objects and tricks that I have used for this tool:

  1. Formulas : CHAR, OFFSET, IFERROR, MID, FIND, LEN, SUBSTITUTE,
  2. Objects: Combo Box (Drop down)
  3. Tricks: Named range,Dynamic chart

The basics for all these MS Excel tips and tricks can be found on the Dashboard-101 page. I will discuss the more specific ones in my next post. But if you want to explore some of the slightly complex formulas I have used in this tool, you may want to explore the section in the ‘Processing sheet’ that deals with ‘recent news’.

CompTraX – A Microsoft Excel-based company profiling tool [1/5]

During the early phase of my career as an analyst, I used to make a lot of company profiles. These profiles served a multitude of purpose. They were used by:

  1. Marketing and sales department in Pharma companies to keep a track of their competitors
  2. Business development managers to evaluate licensing opportunities
  3. Investment banks to evaluate investment option
  4. M&A consultants to boil down on companies of interest
  5. Procurement department to evaluate suppliers
  6. And many more…

Though the profiles made for each of the above mentioned purpose varied by the need of the project, almost all of them had the following sections in common:

  1. An overview—gives a brief description of the company
  2. Financial details—revenue, profit, R&D expenditure, geographical break-up of revenue, revenue from key molecules, etc
  3. Key pipeline molecules—details on key molecules and phase of development
  4. Recent development—mergers, acquisition, in-licensing deals, drug approvals, legal issues, etc

Most of these profiles are sent out either as an MS PowerPoint or MS Excel deliverable. The MS PowerPoint deliverable would have a deck of 10-50 slides and MS Excel deliverable would usually have a single sheet with many rows and columns of data. While the PowerPoint slides get really bulky at times, the Excel deliverables usually become too boring—especially if one is tracking 30-40 companies regularly.

Aim of the CompTraX project

The aim of this project is to show our viewers how to build a robust Microsoft Excel-based dashboard/tool that will make company profiling easier and interesting. I intend to do this in 4 phases:

Phase I:    Make a simple dashboard—will keep it really simple here; just formulas and some nice visualization

Phase II:   Jazz it a bit and add utilities by using some VBA (simple one; not too complex)

Phase III:  Use some Excel to PowerPoint automation (this is going to be real fun; trust me)

Phase IV: Take automation to the next level –when we reach there we expect to deliver a true utility

Since this may not be a turn-key solution for all the profiling needs, I will try to explain all the steps involved in tool creation in as much detail as possible.  So,  it will not only be easier for everybody to follow but will also make sure that everyone understands the technology and technique behind it—then one can easily make minor changes to the tool to fit his/her need.

So, stay tuned for the next update on CompTraX

Phase III:  Use some Excel to PowerPoint automation (this is going to be real fun; trust me)

Phase IV: Take automation to the next level –when we reach there we expect to deliver a true utility

Since this may not be a turn-key solution for all the profiling needs, I will try to explain all the steps involved in tool creation in as much detail as possible.  So,  it will not only be easier for everybody to follow but will also make sure that everyone understands the technology and technique behind it—then one can easily make minor changes to the tool to fit their need.