Controlling PowerPivot-driven pivot tables and charts with VBA

PowerPivot itself cannot be automated with VBA. But with a few tricks we can control how the data drawn from PowerPivot is accessed within Excel. This article assumes that you know how to import data with PowerPivot and how to integrate it into an Excel file as a pivot table. Also you should have activated the developer tools in Excel. There you find all the VBA relevant stuff like the macro recorder and the dialogue controls you want to add.

Let’s get it started with an example

We start with a PowerPivot table (named “data”) keeping the values for two variables A and B for every day in 2012 imported from a MySQL-DB in this case. So in the PowerPivot window this would look something like that:


So let’s assume some manager is asking for a dashboard displaying those two values in a chart but only one at a time and s/he is not inclined to deal with pivot table field lists but wants to switch between A and B using buttons.


Continue reading

Basic concepts of pivot tables

Pivot tables are in contrast to popular belief not just an Excel feature but present in a lot of applications dealing with tabularly structured numerical data – pivot tables are the visual and interactive result of a general concept of data aggregation depending on categories. LibreOffice (check out the article on it), OpenOffice and R by libraries like reshape offer this technology targeting at a different audience. If you understand pivoting in one of these tools, you pretty much got it for all of them.

At my work I am offering a workshop on pivoting with Excel 2010 and so I came up with a chart boiling down the concept of using pivot tables so it fits on a sheet of PDF.


Continue reading

Do-it-yourself reporting with Excel

When you are responsible for setting up or extending an automized reporting of data stored in a warehouse with Excel, I would always vote for using PowerPivot for fetching the data and displaying it in pivot charts and tables. But there are use cases and situations where PowerPivot is not an option. Maybe you just cannot use PowerPivot for whatever reason. But first and foremost I met / suffered from the case where you already have an Excel reporting using cell references to other files and macros to fill the cells. If this sort of reporting reaches a certain extent and importance you cannot just set it up new again. Now if you need to extend such a report f.x. with a new column and don’t want to use cell references to other files – or worse VBA – because this is very fragile and error prone then I have a solution for you.

And this is how it works

Continue reading

Insider deals for DAX companies for the past ten years

Out of pure curiosity I wanted to have a closer look at insider tradings / director’s dealings of people associated with the top 30 corportations in Germany – currently listed in the DAX. By law high-ranking managers of a company and people closely related to them have to report their tradings of the respectives company’s stocks. To make this pile of tradings more easily accessible I wrote a tool using the JavaScript library d3.js that shows the stock quotes on a weekly basis (red bar from lowest quote to highest quote that week) and an orange bar for the weeks where insider tradings were done. When you click on an orange bar then a table below the chart is displayed / updated where the details are listed of the separate deals that were executed.

Continue reading

How to set up PowerPivot and make it talk to MySQL

Setting up PowerPivot – that means all the way from downloading it and successfully fetching data – keeps a number of pitfalls in readiness. To spare you the sweat I will give you some guidance on this topic. I assume that you are using Excel 2010 on Windows 7 and that you want to connect to MySQL 5.

Continue reading

Counting weeks (ISO 8601)

Using the ‘week’ as a time unit can be a bit confusing for at least two reasons:

  1. Does the week start on Sunday or Monday?
  2. How do you count a week that stretches two years?

Now probably your and anybody else’s take on those two questions will not just depend on cultural upbringing – but even more on – how it has been done so far. Nobody wants to change old statistics to a new counting or maintain two parallel statistics for one year with both countings. So to make a long story short, I’ll just stick with ISO 8601 standard because it seems reasonable to me. Here is what ISO 8601 has to say about the two questions:

Continue reading