Crab Project #1: CompTraX – The Pharma Company Profiling Tool
The tool in action
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:
- Marketing and sales department in Pharma companies to keep a track of their competitors
- Business development managers to evaluate licensing opportunities
- Investment banks to evaluate investment option
- M&A consultants to boil down on companies of interest
- Procurement department to evaluate suppliers
- 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:
- An overview—gives a brief description of the company
- Financial details—revenue, profit, R&D expenditure, geographical break-up of revenue, revenue from key molecules, etc
- Key pipeline molecules—details on key molecules and phase of development
- 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 this project
The aim of this project is to show our viewers how to build a robust 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 their need.
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.
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.
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:
Ok, let’s list down the different formulas, objects and tricks that I have used for this tool:
- Formulas : CHAR, OFFSET, IFERROR, MID, FIND, LEN, SUBSTITUTE,
- Objects: Combo Box (Drop down)
- 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’.
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.
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 firstname.lastname@example.org for any queries that you may have.
Keep Excelling !
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.
How does this feature work?
The solution was to create a PowerPoint template that would be used to display the company profiles. This included designing the layout, deciding on the text boxes, indenting things, applying bullet points, preparing slide master, arranging headers, etc. You can find this PowerPoint template in the ‘Template’ folder of the zip extract.
Once I finalized the template the next task was to export all the graphs and text to PowerPoint slide. This was achieved with the help of a little VBA. All the relevant code for this part is in ‘Module 1’ on the VBA interface. I have place appropriate comments there, so it is pretty easy to understand.
Next steps: Currently we can export only one company profile at a time. But ideally one would like to have a tool that exports all the selected companies to a PowerPoint presentation—so that the output is a complete presentation in itself. So, the next enhancement in this tool is going to be the addition of this multiple country export feature.
So stay tuned for the next post and keep Excelling !
P.S: watch out for the next post from Ad; it’s going to be a really good one.
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:
For i = 1 To 6
Set Gph_Object = DisplaySheet.Shapes(Graph_Array(i, 1))
New_PPT_File.Slides(2 * Multiplier + 1).Shapes.Paste
Set New_Picture = New_PPT_File.Slides(2 * Multiplier + 1). _
Shapes(New_PPT_File.Slides(2 * Multiplier + 1).Shapes.Count)
.Top = Graph_Array(i, 2)
.Left = Graph_Array(i, 3)
‘DisplaySheet’ is the Excel sheet containing the graphs. ‘Graph_Array’ is an array (6 row X 3 column) that has the name of the Excel chart to be exported and position to be exported (top and left) on the template slide. The code loops six times (as there are six graphs) and exports (copies and pastes) all the graphs defined in the array as pictures on to the PowerPoint.
Once this is done, the next part is to export the ‘Company Name’ to the Action Title of the slide and the ‘Company Overview’ to its respective placeholder.
With .Slides(2 * Multiplier + 1)
.Shapes.Title.TextFrame.TextRange.Text = “Company Profile – ” & ProcessingSheet.Range(“CompanyName”).Value & ” (1/2)”
.Shapes(2).TextFrame.TextRange.Text = ProcessingSheet.Range(“CompanyDescription”).Value
Where, ‘CompanyName’ and ‘CompanyDescription’ are named ranges storing the relevant information on the Excel sheet; ‘Shape(2)’ is the ID of the text box which is supposed to hold the company overview on the PowerPoint presentation.
Next task was to export recent news and company pipeline to text boxes on the next slide in the template.
With .Slides((2 * Multiplier) + 2)
.Shapes.Title.TextFrame.TextRange.Text = “Company Profile – ” & ProcessingSheet.Range(“CompanyName”).Value & ” (2/2)”
.Shapes(2).TextFrame.TextRange.Text = ProcessingSheet.Range(“News_For_PPT”).Value
.Shapes(4).TextFrame.TextRange.Text = ProcessingSheet.Range(“Pipeline_For_PPT”).Value
‘Shape(2)’ and ‘Shape(4)’ are the designated text boxes on these slides that are supposed to hold this information and ‘News_For_PPT’ and ‘Pipeline_For_PPT’ are the two named ranges on the processing sheet that contain this information.
The complete code for this process is available in ‘Module1′ of the CompTrax tool (on the VBA interface).
This post completes the ComTraX series of posts. Hope you liked our idea of a company profiling tool. My next post will focus on a new idea and tool that will help analyze pharma data.
Till then, keep Excelling !