Saturday, April 30, 2011

Sparklines

A sparkline is a type of data visualization, graphic information. Edward Tufte was the author for the name sparkline as “small, high resolution graphics embedded in a context of words, numbers, images.”. Sparklines are the best option for you build your dashboards exactly because of that. A good example of what can be done is this one:


Friday, April 29, 2011

Dilbert...



Dilbert by Scott Adams  -  www.dilbert.com/

Thursday, April 28, 2011

Spice up your Choropleth Maps with Excel (from Clearly and Simply)

A good article from Clearly and Simply with a few tips tips how to mitigate some of the disadvantages of Choropleth Maps in Microsoft Excel. Please see the following link http://www.clearlyandsimply.com/clearly_and_simply/2010/02/spice-up-your-choropleth-maps-with-excel.html

How long your macros run?

Do you know how much time your macro takes to run? Sometimes we create some macros that takes to much time to run but don’t know exactly how much. This code below is useful to use when we get a large of data from an external data source…

Dim strTimeStart as String
Dim strTimeFinish as String
strTimeStart = Time()
[Insert your vba code here]
strTimeFinish = Time()
MsgBox "Your macro takes " & DateDiff("s", strTimeStart, strTimeFinish) & “ seconds to run!”

With this information you can decide if is better to split the code in two or more macros and insert a status message in the middle, or leave like it is and just inform the users that your macro will take “x” minutes to run…
Knowing exactly the time that your macros takes to run you can take advantage to build better and organize your report(s).

Communication

Communication is key. Information on what, how and when you plan start your report project. Disclose the responsibilities of each person involved in the process and be clear on what you want and need. A proper planning ensures you will meet your goals.

A good gap analysis enables you to set goals for improving your project. You may not be able to set ideal goals, KPI´s, scorecards, etc… Communicate. Develop strategies for improvement. Take the chance to create a report(s) that will give to your business what is needed.
Executive leadership needs to be active. Executive leadership needs to be active to support you. Since they set company strategy, they need to be involved in how the metrics are linked to achieving it.
Data visualization. Display them in charts, graphs, and diagrams, to show what you're trying to do and how your colleagues are involved in delivering information.
Respond quickly. Your report(s) must provide feedback promptly. They should not take a long time to yield data.
Be simple. They must clearly communicate the information you need. Avoid setting up complex measurements that are difficult to use. You want direct information.
Drive only important activities. Make sure they relate to regular activities and processes of your business. You need to assess the most important factors to measure and then make sure that what you examine will result in information that's relevant.

Combine all your business data

One of the best functionalities of Excel is the possibility to import external data. For this, you have standards menus (for a limit of sources types) or, my best choice, the opportunity to create some macros, VBA code, to get data as I want and from where I want. At this moment I am able to create several macros to get data from the sources mentioned in the picture below:
Combining several data sources as I want will give me the opportunity to create only one excel file/report with the most important information that I need. All in one!

Take the opportunity to explore more this functionality and get great results for your management report.

Wednesday, April 27, 2011

Improve your skills

Improve your skills. Take a few moments per day to start searching and read several subjects and opinions. New ideas, innovate techniques and a new management approach are the soul of your business. Develop new management reports with strong and efficient results, improving new sales opportunities, customer’s satisfaction and support. Search and read…

Tuesday, April 26, 2011

Thank you!

As you know, on last month (March 2011), I have started this personal blog about Microsoft Excel, visualization and data analysis. I have received several contacts to congratulate me for this initiative and I would like to thank you for your encouragement to continue this personal project.
 
Some of them asked me if I was interested to start another blog about Information Security Management. I am glad to get this request but with three children... Time is very short  :-)
Let me think about the subject and maybe you will see the logo below...  ;-)

As I said before, this is only a personal blog where I hope to help you and learn more with (welcome) suggestions, feedback, questions, etc...  Enjoy!

Reports, reports, reports, reports and ... reports!

How many reports do you receive and watch per day? How many hours do you "lose" to analyze it? They are easy to read and understandable? Are you satisfied with what you receive?
Have you ever stopped to think about it?
Gather your team for one day, decide what information you want, define KPIs, scorecards, metrics, etc ... Draw in a paper the structure of the report that you would like to have and the source where to get that data, what kind of graphics, with or without thematic maps, what time you want to receive on a daily basis and more ... Build your report for analysis of your business in only one file, build your dashboard. Get the most out of Excel and your team!

Monday, April 25, 2011

Sales Dashboards – Visualizing Sales Data – 32 Dashboard Examples & Implementations

Chandoo.org is a great help for your needs... Visit http://chandoo.org/wp/2010/01/04/sales-dashboards/ and see some examples of what you can do with excel.

Sunday, April 24, 2011

Do you follow the results of your business anywhere, any time?

On my daily work I have created an excel file with some macros to get data from a Oracle and a Teradata database and fill a dashboard with sales. This excel file works alone, every hour and send me an e-mail where I can follow the sales results of the company. With this option I can be updated on a hourly basis and see anywhere, any time using a smartphone like a Blackberry, a HTC or another one (if I am not in the office). How it works? Simple...

1º step: Create an excel file to get data from your data source and fill your dashboard (or any other report);
2º step: After get data and fill your report create another macro to send it by mail;
3º step: Create a macro to run all macros when excel file is open and close it alone;
4º step: Run this excel file with Windows Task Scheduler on a hourly basis (or other);

Excel and Windows will do the work for you and you will be updated anywhere, any time! 

Thursday, April 14, 2011

Do you need maps for your reports?

If you need maps to build your "thematic maps" reports, you can find them for free on http://d-maps.com/

Friday, April 8, 2011

Create a simple "Help Menu" for your report

Do you need explain some data on your report? Create a simple "Help menu" and insert all information...
Insert this code in your form:

Private Sub Userform_Initialize()
    Me.Caption = appname & "Help"
    LastRow = Sheets("Sheet1").Range("A65536").End(xlUp).Row
    TxT = ""
        For r = 1 To LastRow
            TxT = TxT & Sheets("Sheet1").Cells(r, 1).Text & vbCrLf
        Next r
        With Label1
            .Top = 0
            .Caption = TxT
            .Width = 160
            .AutoSize = True
        End With
        With Frame1
            .ScrollBars = fmScrollBarsVertical
            .ScrollHeight = .InsideHeight * 2
            .ScrollWidth = .InsideWidth * 9
        End With
End Sub

Identify colors indices

We can use VBA to identify some colors that we would like to use in our reports. There are several reports that we make with VBA code and for that we need use "color indices". With this code find your colors...

Management, consolidation and data migration

It’s important to understand the strengths and weaknesses of data that you are working in order to discover and extract exactly the information you want. You can create some criteria for data validation to ensure their integrity.
- Analyze data quality
- Use pre-defined metrics to ensure data integrity and consistency
- Create data migration logs

By identifying potential problems early, you will reduce the time that you have to build your report and ensure data quality that will display.

Thursday, April 7, 2011

Get ideas for your report

How can we get ideas for a good layout? How can we make an understandable report? One of the things I normally do is to see many business magazines where we can find several ideas for our work. Another tip is the web were there are several management reports, marketing flyers, and so on… All these work is made by graphic professionals. They know how to draw the reader's attention.
Using small charts, sparklines, you will make the report more useful. You can put more information, will be easier for the user read and compare your data. Your report should be easier to maintain/upload your data for the next months, saving time. Here we can do it manually or automatic, from a file or a server. Create several macros to do your work…

Tuesday, April 5, 2011

Wordle by Jonathan Feinberg

"Wordle is a toy for generating “word clouds” from text that you provide."
For more information please visit www.wordle.net

Add "comments" on your reports as additional information

On some occasions it is necessary to have detailed information of a specific data. One option is insert a comment in a cell with the information we want, and it can be done automatically. On this “comment” we can add additional information that is not available on the report “as a first view”.


Code sample:

'Add comment
Private Sub AddMyComment()
    Dim MyRange As Range
    Set MyRange = ActiveSheet.Cells(1, 1)
        If MyRange.Comment Is Nothing Then
           MyRange.AddComment
           MyRange.Comment.Text "Please test adding a comment with VBA" & vbNewLine _
                                                & "You can add"
        End If
End Sub 

'Remove comment
Private Sub cmdRemoveComment()

Dim MyRange As Range
    Set MyRange = ActiveSheet.Cells(1, 1)
        If Not (MyRange.Comment Is Nothing) Then
                MyRange.Comment.Delete
        End If
End Sub

Or can be used like this: 

'Sheet1.Range("A1").AddComment ("Please test adding a comment with VBA")
'Sheet1.Range("A1").Comment.Delete

Friday, April 1, 2011

Visualization and data analysis – How to start?

How to know about principles and best practices to apply in our daily work and make a real report to support us in our business?
Search on the web and read books related to the subject. There are several good sites about this topic (some of them are very helpful), well detailed and with samples that we can download and adjust to our needs. With this we will start to understand several tips like formulas, graphs (and so on…) and how to present in a “correct way” our data.
Dashboards can tell us almost everything about our business/company only in one Excel sheet. The trick is how to do it well… Why don’t start with a paper and design a palette for the information that we need to have in our dashboard? This first step is easy…
Now comes the hard part… Start with our dashboard on Excel!
I hope give you a help to create a powerful dynamic dashboards making you an expert on the subject. Let’s make a dashboard that makes business sense, saving time and money.
I will create an Excel file with several tips, tools and functionalities that you can download, see and learn how it works… Dashboards, toolbar menus, floating menus, “tweetboards”, data maintenance, import/export files from other sources, etc…

High impact dashboards… The beauty of information

A good layout will keep attention on who is watching. Should be simple, interactive and well designed. Should be good enough to make sure that will impact all persons that will see it.
What should have? Well, normally, I use some interactive graphics where the user can select the criteria (year, month, etc). The graphics can have some “movement” when the criteria are changed. Should not have many colors to distract the attention of the user or even confuse.
We can use hyperlinks to some documents, presentations or even to go to the detail information. Understanding our data goes further than just presenting them. We need to know what is behind and as we get it.
The message on our dashboard should be clear and simple to help us make decisions for our business.

Congratulations Chandoo…

… and thank you for your excellent work space!
http://chandoo.org/wp/2011/04/01/march-2011-is-best-month-ever-and-other-news/
If you want to have strong skills in dashboards (and other Excel subjects), Chandoo.org is one of the right places to learn.
“At Chandoo.org, my aim is to make you awesome in Excel. I do this by sharing whatever little I learn everyday. Our community has an open mind. We are eager to learn and share. We all constantly teach each other new tricks and ideas using comments and forum posts.” - http://chandoo.org/wp/about/

Let Excel work for you

Why not let Excel work for you? Do you need, as your daily work, import data from other sources like Oracle, Access, Outlook (or other) to fill you workbook? Do you have to do a manual work to get your reports with new data?
Why not to stop doing that and create a workbook with macros and VBA code to do the work for you?
For example, imagine that you need get data from Oracle on a daily basis at 09h00m. Excel can do it alone, getting new data, update your workbook refreshing your data, saving as you want and finally send you an e-mail. So when you arrive at your office you will have the report “on your desk”. Have you thought about it?