Statistics Canada’s decision last year to retire its archaic, legacy data browser Beyond 20/20 left me scrambling for an alternative. Anybody that has ever needed to download, manipulate and view some of Statistics Canada’s largest and most complicated matrices grew to appreciate the power and flexibility of the Beyond 20/20 data browser. Much of the databanking procedure for QEDinc’s Canadian Modeling System depended on it. Finding an alternative approach was, therefore, of some importance to me.
My solution uses Microsoft Excel’s PivotTable tool. Few Excel users ever find a reason to use this part of the software but, when appropriate, it is a useful and powerful tool for manipulating data. While there are likely different approaches to address the loss of Beyond 20/20, I hope some of you in the economics and statistics community find my solution useful.
The first step is to download the entire CSV table from Statistics Canada. With the CSV opened in a new workfile, highlight the data and then select Insert PivotTable in a new worksheet. I prefer to display time in the spreadsheet columns and all other concepts in the rows using filters as needed and desired. As an aside, including the VECTOR field in the rows allows you to identify each series by its Statistics Canada v-number. The data can now be further manipulated in Excel or other software packages.
There is one important complication that may be encountered using Excel. Some CSV data files from Statistics Canada have too many rows to load directly into Excel. The solution is to use a text editor to split the CSV file up into small enough parts that they can be loaded into Excel. And unlike Beyond 20/20, users are now left to convert data from high frequency to a lower frequency in Excel or in another software package. Despite these limitations, the PivotTable tool is a serviceable alternative to Beyond 20/20 – at least until Microsoft decides to eliminate it from the software!