Tutorial – SVR – Source Data
As I mentioned in my previous post, I will be publishing tutorials on how to build the Senate Voting Records Dashboard. Here is the first in that series.
Before we start building a visualization, often we need to spend some time sourcing and getting to know our data. In this tutorial, we will import Senate voting record details using Excel and a simple VBA loop function. Senate roll call vote data is available at http://www.senate.gov/legislative/votes.htm.
Toward the bottom of the page is a section titled “Roll Call Tables”. For this tutorial, we are using “2015 (114th, 1st)”.
After you select the session, you will see a list of all the votes for that session. Go ahead and select the first vote to see the details for that vote. In the top right corner of the vote details page, there is a link to view the data as XML. Select this link so we can see what type of data we are dealing with.
Now that we found a URL for our source data, we need to get this into Excel so we can build our visualization. Unfortunately each vote has its own page for the detailed data, and we want the data for all of the votes in our session. We could manually import each URL via the XML import wizard in Excel, but to make our job a little easier, we are going to build a quick function to do it for us. If you look at the URL for the XML data of multiple votes, we can see that the only change is the vote number at the end of the address.
- http://www.senate.gov/legislative/LIS/roll_call_votes/vote1141/vote_114_1_00339.xml
- http://www.senate.gov/legislative/LIS/roll_call_votes/vote1141/vote_114_1_00338.xml
Now that we have our URL, let’s go ahead and open up Excel. The pictures below are from Excel 2007 as that was what I happened to have installed on that particular computer, it should be fairly similar in more recent versions.
Once you have Excel open, go ahead and hit Alt+F11 to open the VBA editor.
Once the editor is open, double click on “ThisWorkbook” in the top left project hierarchy. Once the pane on the right opens the editor for ThisWorkbook, you can copy and paste the following function there.
Sub roll_call_import() Dim xmap As XmlMap Application.ScreenUpdating = False Application.DisplayAlerts = False ActiveWorkbook.XmlImport URL:= _ "http://www.senate.gov/legislative/LIS/roll_call_votes/vote1141/vote_114_1_00001.xml" _ , ImportMap:=xmap, overwrite:=False, Destination:=Range("$A$1") xmap.AppendOnImport = True For x = 2 To 339 If x < 10 Then xmap.Import URL:= _ "http://www.senate.gov/legislative/LIS/roll_call_votes/vote1141/vote_114_1_0000" & x & ".xml" ElseIf x < 100 Then xmap.Import URL:= _ "http://www.senate.gov/legislative/LIS/roll_call_votes/vote1141/vote_114_1_000" & x & ".xml" Else xmap.Import URL:= _ "http://www.senate.gov/legislative/LIS/roll_call_votes/vote1141/vote_114_1_00" & x & ".xml" End If Next x Application.ScreenUpdating = True Application.DisplayAlerts = True End Sub
Once you have the function pasted, go ahead and click the green Run button or hit F5. While this is running (it should import about 30K rows of data), I’ll give a brief overview of what the code does. Those that are well versed in VBA can skip ahead.
First thing we do in the function is turn off screen updating and alerts from Excel to speed things up and prevent any annoying popups asking if you are sure you want to do something. Then we use Excels built in XmlImport function to bring in our first vote. Next we need to set the data table we just created to append data rather than overwriting so we can import each vote. The final step is to loop through votes 2 to 339 and append the data. There are 3 conditions in the If statement to handle single, double and triple digit votes. Since the URL always has 5 digits, we need to pad the URL with the appropriate number of 0’s based on how many digits our vote number has. Lastly, we turn back on screen updating and alerts to return Excel to its previous state.
Once the code has finished, you should have a workbook with nearly 34k rows of Senate voting data that we will use in our next tutorial.
Check back in about a week for the next tutorial in our Senate Voting Record Dashboard!