What does an analyst do? Question, Investigate, and Communicate

“The Golden Rule of Habit Change: You can't extinguish a bad habit, you can only change it.”

Charles Duhigg, The Power of Habit: Why We Do What We Do in Life and Business

I recently read the following article, Aviate, Navigate, Communicate: Business Crisis Management From a Pilots Perspective. It struck an accord because in another life time, I was a young Student Naval Flight Officer in the U.S. Navy. The phrase, Aviate, Navigate, Communicate, is a habit drummed into aviators to utilize when an emergency happens in the air. It represents the basic activities needed to keep one alive while troubleshooting an issue. It’s a habit that requires actions and no thoughts to follow.

“Aviate, Navigate, Communicate” led me to devise “Question, Investigate, Communicate” for my profession in analytics.

Question

At the very minimum, an analyst needs to understand the business problem. Some call it requirements gathering, writing specifications, or just plainly “what’s the question?”

I like to ask the following questions:

• What’s the issue/problem?
• Who else needs to see this info?
• Where do you plan to use this information? Meeting room? Smart phone?
• What does the final output look like?
• When is it needed?
• How do you (business user) plan to use this info?

If the above questions look similar a journalist’s “the 5 W’s” then you’d be right. I often imagine that I’m a journalist when I’m first presented with an issue or request. I treat each request with a healthy dose of skepticism.

Investigate

Now that I know what the core problem is, I need to develop options or possible solutions.

Investigation can take on several forms such as:

• Data extraction from databases to produce a data report 
• ad-hoc querying against corporate data-sets to satisfy a “what if” question 
• Descriptive statistics applied to a data-set to understand 
• Data Visualizations in the form of charts and maps 
• A data model created to describe a population with embedded business logic 
• Facilitation or Negotiation with varying parties to arrive at a set of options 

I like this quote from Rahm Emanuel, “Don’t come in here and dump a problem. I have a whole desk full of those. Bring a set of solutions.” That is analysis. It attempts to distill a problem to core components and offers various thoughts on how to describe or solve the problem

Communicate

No matter how brilliant a piece of analysis is, without communicating the results to the right person at the right time then analysis isn’t complete. Communication is actually the cornerstone to great analysis. Think about it, the task of analysis is to dissect key facts from vast amounts of information and transform it into useful “bit-size and flavorful” bites.

Communicating results can take on many forms:

• report/dashboard
• excel model
• e-mail/memo
• presentation in a meeting
• phone call/Face-to-face interaction

 No matter the form, one must be mindful to how much or how little information to give to the business user.

When the stress level is high and time is short, remember to “Question, Analyze, and Communicate”

Print multiple Excel worksheets to PDF using VBA

 

“Excel developers realize that the most efficient and maintainable applications are those that make the most of Excel’s built-in functionality,augmented by VBA where appropriate.”

from Professional Excel Development by Rob Bovey, Dennis Wallentin, Stephen Bullen, and John Green

After completing a multiple worksheet Excel model, my business user requested the reports in PDF format. Great! There goes my morning I thought. How to do I create a VBA routine to convert the multiple Excel worksheets into a single PDF file? Google it!

My 10 minute google search offered no quick solutions. Suddenly, I stopped looking for a VBA solution. The above quote flashed through my mind. Excel more often than not can do what you want without writing new VBA code. It’s the old adage, “if all you have is a hammer, everything looks like a nail”

I only needed 5 PDF files. Why not use Excel to simply produce the files manually. I did the following:

• Set the print area for all 3 worksheets with a 3-line VBA code snippet

• In Excel’s Developer ribbon, click the “Record Macro” button

• Select the three worksheets by holding down the CTRL key while clicking each worksheet with the mouse left button.

• Go the Excel’s back page by navigating to the File ribbon

• Click “Save & Send”

• Click “Create PDF/XPS Document”

• Click “Create PDF/XPS”

• Enter your file name of choice and click the “Publish” button

• In Excel’s Developer ribbon, click the “Stop Recording” button

That’s a lot of clicks to create a 5 PDF files. Anyway, I was able to print the reports. Being the Excel junkie that I am, I couldn’t help but create a VBA routine to avoid all those clicks. 

It turns out the answer to automating came from the Excel’s Macro recorder. I opened the VBA editor (ALT-F11) to see the following code created by Excel:

Sheets(Array("Sheet1", "Sheet2 ", "Sheet3")).Select    Sheets("Sheet1").Activate    ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _        "C:]\test.pdf" _        , Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas _        :=False, OpenAfterPublish:=True

The key was selecting the three worksheets programmatically. It also provided the syntax to create a PDF file.

TIP: Use Excel’s Macro recorder to discover VBA objects and collections. It writes horrible code but provides really good insight into the Excel VBA object model.

 

 

I did a little more tinkering to produce the following code:

'Set the print area for each worksheet. 'rngPrintArea1 is the named reference of a cell that contains a formula that generates an address. EX: ="A1:G"&12+Max(Sheet1!A:A) will generate A1:G14 Worksheets("Sheet1").PageSetup.PrintArea = Worksheets("Sheet1").Range("rngPrintArea1").Value Worksheets("Sheet2").PageSetup.PrintArea = Worksheets("Sheet2").Range("rngPrintArea2").Value Worksheets("Sheet3").PageSetup.PrintArea = Worksheets("Sheet3").Range("rngPrintArea3").Value 'Select the multiple sheets Sheets(Array("Sheet1", "Sheet2 ", "Sheet3")).Select ‘Set filename stFileName = ActiveWorkbook.Path & "\" & Worksheets("Sheet4").Range("A1").Value & " - report.pdf" 'Print all three to PDF ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _        stFileName _     , Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas _        :=False, OpenAfterPublish:=False 'Unselect the three worksheets, otherwise Excel will not allow you to change anything on the three worksheets Sheets("Sheet1").Select

 

For more robust VBA code, check out MS Office Dev center’s entry on this topic.

Is there a difference between managing and leading?

 

“Managers and leaders are often referred to synonymously, but only leaders allow their employees to solve problems with their own insight. The truth of the matter is this: Every leader may not be a manager, but every manager should be a leader.”

IIya Pozin

The above quote is from the article “The Difference Between Managers and Leaders” by IIya Pozin. The title caught my eye. As a manager myself, I once thought that being a manager made me a leader. I’ve learned through experience that

I like how IIya Pozin states the differences between leader and manager,

1. Managers give answers, leaders ask questions.
2. Managers criticize mistakes, leaders call attention to mistakes indirectly.
3. Managers forget to praise, leaders reward even the smallest improvement.
4. Managers focus on the bad, leaders emphasize the good.
5. Managers want credit, leaders credit their teams.

As an analytics manager in the healthcare industry, the above statements make perfect sense. Analytics is discovery and communication of meaningful patterns in data (according to Wikipedia). I’ve found that analytics is more than reports and dashboards. You must use laser focus to discover the problem to analyze, build a coalition of colleagues to seek a resolution, and convey that resolution in a simple, compelling fashion.

Great analysts, managers or not, must aspire to be leaders in their organization.

I just read “It’s Not about You”

 

“And the moment you begin thinking that it’s all about you, that you’re the deal, is the moment you begin losing your capacity to positively influence others’ lives. In a word, to lead. “

Bob Burg and John David Mann

I picked up the book, It’s not about You, at my local library. It’s a business leadership book the size of an iPad mini with only 127 pages. The authors, Bob Burg and John David Mann, present their case in the form of a parable. I was hesitant at first but was pleasantly surprised with this book.

The book’s description written on Amazon,

In this inspirational parable, we meet Ben, a young manager charged with persuading five hundred employees of a struggling chair manufacturer to agree to a merger as a way out of their financial woes. Facing what seems like an impossible uphill climb, Ben seeks the advice of Claire, an old friend, who introduces him to an eccentric elderly lady known simply as Aunt Elle.

Over the course of the week leading up to the crucial vote, Aunt Elle teaches Ben about the power of influence and positive persuasion. Ben also meets with each of the manufacturer's four top executives in an effort to sway them to his side, and instead comes away from each meeting with a different leadership lesson. But it's not until Ben reflects on his own experiences that he learns the critical principle so many people in positions of power fail to grasp: it's not about me, it's about you.

Ben learns the following 5 keys to leadership:

• Key #1 Hold the vision
• Key #2 Build your people
• Key #3 Do the work
• Key #4 Stand for something
• Key #5 Share the mantle

Key #5 struck an accord with me. I took “Share the mantle” to mean, let others lead. Being the leader isn’t about being the center of attention. The book states that “the best way to increase your influence is to give it away”. This is a powerful concept when confronted with leading an organizational change. Employing new Business Intelligence tools takes leadership to overcome the pushback from letting go of the existing the technology.