Roll-Call-O-Matic

The original Roll-Call-O-Matic! As seen on TV!

This is a tool for analyzing a legislative roll call.

The point of this tool: About one minute after you get a roll-call vote, you can know how the legislators broke out by party, religion, age, city-suburb, etc., if you've done the prep work. (Most of catering is in the cutting of vegetables.) Then you can use this tool to classify legislators into voting blocs.

Here are examples of graphics that we got into the Boston Globe on deadline, analyzing votes on gay marriage in Massachusetts: votechart1.pdf and votechart2.pdf.

Here's the sample in Excel, for the Massachusetts legislature. The names of legislators have been removed, and the order scrambled, so as not to help the Herald.

You start by typing in the information on every legislator (name, party, religion, etc.). Some of this info came from standard directories; some we gathered by calling every legislator.

Then you set up pivot tables to sort the legislators by how they voted, broken out by party, religion, etc. We're showing both the raw number of votes yes or no within each group, and the percentage of each group that voted yes or no. (See the tabs across the bottom of the file for samples. On each tab, choose Data/Pivot Table to see how it's set up, or to edit it.)

Then you type or paste in the roll call on the vote you're analyzing. In this example, that's typed into the Amendment column, the gold column.

At that point, you need to refresh each pivot table. You would think that Excel would do this automatically, but it doesn't. (That's a flaw, eh?) You have two options: Either go to each tab and choose Data/Refresh Data. Or you can set each tab to refresh automatically when you open the file. If you choose that second path, you'll then refresh each tab at once by closing the file, then re-opening it. You have to set this once on each pivot table, by getting the toolbar: View/Toolbars/Pivot Table, then by choosing on the toolbar, Pivot Table/Options/Refresh on Open.

If you arrange your legislators in the order that you get a roll call, say from AP, then you can paste that rollcall into the right-most column, then use an "if" function, perhaps in combination with the "right" function, to pull just the vote info (Y or Yes, N or No) into your Key Vote column. In our case, not shown in this spreadsheet, that formula looked something like this: =if(right(y2,1)="Y","Yes",if(right(y2,1)="N","No","Not voting"))).

You might want to use an "exact" function in Excel to make sure that AP hasn't switched the order of the names! Of course, it would save us all trouble if AP just moved this kind of analysis itself, but what's the fun in that?

And you'll see in this sample how we combined the votes on multiple amendments, so we could classify legislators into the six voting blocs.

If you have questions, please shout. Bill@PowerReporting.com