Dan’s sort of turned Six Colors into a Numbers-themed blog over the past few days, but I thought I’d pile on with an additional post about Apple’s spreadsheet app, which I use to generate all our financial charts.
Recently, I appeared on Allison Sheridan’s podcast for one of the most exciting conversations you will ever hear about using computers to visualize data. Now, rule number one about using self-built tools that become a vital part of your production process is to never actually reveal how those tools work to the general public. Personal tools are messy and idiosyncratic. When I build a personal tool, I rarely do it the right way. I do it in the most expedient way, which generally means using familiar tools that allow me to expend the least effort possible, so I can get on with my real job, which is not automating things like chart generation.
On Allison’s podcast, I broke rule number one. While I’m happy that not all of my techniques were deemed ridiculous, I did reveal that every single quarter I have to expand my data table and then edit every single one of my charts, clicking between Numbers tabs and moving various selections to reflect the addition of a new quarter and the removal of the oldest trailing quarter. It’s the biggest bit of drudgery left in my otherwise fairly well automated process.
Well, wouldn’t you know it: I heard from several people who said that they thought I could make this process easier on myself. It would just require me to do a little bit of rearchitecting of my Numbers document, and the use of some Numbers formulas I’d never touched before.
Here’s what I did.
First, I created a new tab and built a new 21-column data table that would exactly reflect the span of data I use in my charts, rather than building them atop of the same ever-expanding (59 columns and counting!) master table of all the Apple financial data I’ve collected. I re-mapped all my charts to point at that new table, meaning I’d never need to edit those charts again to laboriously drag them one column to the right every quarter.
Now for the magic trick—how do you get the new 21-column data table to reflect the most recent data columns from the other table? In theory, if I add a new column of data to the main data table, everything in the new table should just shift one to the left, with the oldest item dropping off entirely, matching the behavior I’d expect to see in my charts.
I ended up using this formula, which you can also see in the screenshot at the top of this story:
@INDEX(Data Table::My Row, COLUMNS(Data Table::My Row) – COLUMNS (Current Row) + COLUMN(current cell))
INDEX is a function that provides the data from a specific cell in a specific spreadsheet, in x,y format. The first value for INDEX is easy: it’s the row of data I’m going to copy from the big table into the new, smaller table.
The second value is a bit trickier. Thinking about this geographically, the cells I need to match between the two tables have a major thing in common: they’re all the same distance from the most recent quarter in the data table. If the current quarter of data is at the far right of my table, then the previous quarter is one column in from that, and the next previous quarter is two columns in, and so on.
To get that location in a format INDEX will accept, I need to know the current width of the main table (it gets one column wider every quarter) and the position of the current cell in the new data table relative to the end. The COLUMNS function—COLUMNS(Data Table::My Row)—tells me the current number of columns in the main table, and the COLUMN function in COLUMN(current cell) tells me the column position of the current cell in the new table.
I’m almost there, but knowing that there are currently 59 columns in the data table and that the cell in question is in column 20 of the new table isn’t quite enough. I also need to know the width of the new table, so I can determine how many columns my current cell is away from the right edge. If I never edit my new table, the number is known—it’s 21. But if I also use COLUMNS for this table, my calculations may be able to survive any future expansion or contraction of my new data table.
Now I’ve got everything required to do the math: 20 minus 21 means the cell in question is shifted one cell in from the right edge, and that means the cell whose data I want to copy from the main chart is at column 58 of 59. That’s all INDEX needs to know.
And that did it. Now I’ve got a tab that I don’t ever need to visit that exists only to grab the last 20 quarters of data from my main table and feed it into my charts. I’ll be putting this new system to the test on May 1, when Apple announces its next quarter of fiscal results. We’ll see how it goes!
Finally, I realize that in writing this piece I have again broken rule one of self-built tools, and that someone out there is probably going to notice another way I’m not doing things quite right. That’s fine. I brought this on myself, I suppose!