Applying the High-tech Wizardry of Sparklines to Economic Data

Article source: CMDGroup

Yes, I’m an economist first, but in my secondary role as ‘tech whiz’ – my wife and kids would guffaw at that assertion – I’ve come across an exciting feature of standard Excel spreadsheets that I feel must be shared with you.

Of course, there’s always the danger that I’ve finally clued in to something everybody else has known about for years. However, I’ve asked around and it seems most people aren’t yet aware of a tool called ‘Sparklines’ that is highly worthwhile.

And neat and cool and easy to use.

Let’s suppose you have a ‘wall’ of data, such as appears in Table 1 that accompanies this Economy at a Glance. I’ve included the row numbers and column letters for ease of explanation.

The statistics in cells ‘C2’ diagonally to ‘O22’ are percent changes of U.S. put-in-place construction investment, latest 12-month averages versus previous 12-month averages.

Simply looking at the numbers, it’s hard to obtain a sense of patterns. A graph would make the job much easier. The Sparklines feature in Excel allows you to do just that, within a single cell.

Here’s how it works. Place your cursor in an empty cell beside the data you want to analyze. In Table 1 (i.e., before there was a graph), that would be cell P2.

In the dashboard at the top of the Excel spreadsheet, click on ‘Insert’. About two-thirds of the way across the new dashboard, again at the top, find the box with the word ‘Sparklines’ under three icons for ‘Line’, ‘Column’ and ‘Win/Loss’.

We could click on ‘Line’, but let’s click on ‘Column’ instead. (It handles negatives a little better, although there’s a way around that which I’ll explain later.) That will open a box that asks you to submit ‘Data Range’. Either type in ‘C2:O2’ or highlight those cells with your cursor.

Press ‘OK’ and presto, a small graph appears in the single cell P2. Even without vertical and horizontal axis labels, you immediately have an impression of how the data is performing.

But that’s not where the magic ends. You can copy that individual cell ‘P2’ into all the cells below it, ‘P3:P22’ and graphs will appear for all the row items. (Or, if you’re even braver, you can begin by highlighting the empty range ‘P2:P22’, clicking on ‘Column’ and choosing the entire rectangle of data ‘C2:O22’ and all the graphs will appear at once.)

Table 1: U.S. Put-in-place Construction Investment:
Average of Latest 12 Months versus Average of Previous 12 Months:

Data source: Census Bureau.
Table: CMD.

Once you have familiarized yourself with this approach, you’ll want to try line graphs as well.

That’s the approach that has been adopted in Table 2, housing starts in Canada and its regions and provinces.

You’ll notice that I’ve added some embellishments to my graphs in both Tables 1 and 2 versus what you’re seeing in your spreadsheet. The trick to being a little fancier is to notice that when you have placed your cursor on a single-cell graph, a command appears at the top of the spreadsheet saying ‘Sparkline Tools/Design’.

Click on the word ‘Design in that mode and a wealth of other possibilities opens up.

You can change marker colors for low points and high points. In Table 1, that’s an especially valuable aid in spotting which types-of-construction are currently achieving their peak volumes for the period.

You can add a horizontal axis line. Setting it to zero is how best to handle negative numbers when you’re working with lines as opposed to columns. (Table 2 has no negative numbers and, hence, there is no need for a horizontal axis set to 0.0.)

There are also options for changing the scale of your vertical axis.

Table 2: Annual Housing Starts in Canada and Regions/Provinces,
Thousands of Units:

Data Source: Canada Mortgage and Housing Corporation (CMHC) and Cansim table 027-0009.
Table: CMD.

Sparklines aren’t just useful for assessing economic data. They can also be readily employed when studying sales and shipments, or inventory levels over time, or the myriad financial items in budgets and profit and loss statements, to name just a few.

So there you have it. Maybe I’ll tackle ‘pivot’ tables in some future EAAG, but please bear with me for a while. I’m going to need some recuperation time to allow my often errant tech juices to rejuvenate.

Tags: Alex Carrick, Canada, CMD, CMDGroup, Economic, empoly, job, market, US

Category: CMD Group

This entry was posted on Wednesday, March 30th, 2016 at 4:00 pm. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.