NYS Legislature API Examples - JP Software Technologies

The New York Times offers several APIs, one of which is the New York State Legislature API.

Here are some code samples in VBA for consuming API data about the members and bills of the NYS legislature (Assembly and Senate).

Note that the NY Times API requires a key and registration. I have an API key but in the samples below I've removed it. Visit the link above to get your own key. Also, make sure read the terms of use

Enums

The first thing we'll need to do is put the following Enum sections at the top of a standard module, or into their own module. These are used by most of the functions below.

Public Enum chamber
  Assembly
  Senate
End Enum
Public Enum billtype
  introduced
  updated
End Enum

Get Members by Chamber

List all the current members of a given chamber (Assembly or Senate) using this function. The function returns several pieces of information about each member, including

Full name
Party affiliation
District

This function will call the API, save the result as a XML file in the local temp folder, then open and parse the XML file. To avoid overquerying the API (and face a possible ban), the XML file is reused if it already exists.

Function GetMembersByChamber(apiKey As String, chamber As chamber) As String()

Dim xml As Object
Dim result As String
Dim tempFile As String
Dim tempString() As String
Dim chamberName As String
Dim xmlDoc As Object ' MSXML2.DOMDocument
Dim xmlDocRoot As Object ' MSXML2.IXMLDOMNode
Dim result_set As Object ' MSXML2.IXMLDOMNodeList
Dim results As Object ' MSXML2.IXMLDOMNodeList
Dim members As Object ' MSXML2.IXMLDOMNodeList
Dim i As Long, j As Long

  chamberName = GetChamberType(chamber)
  tempFile = Environ("temp") & "\" & chamberName & ".xml"

  ' requery website if file doesn't exist
  If Len(Dir(tempFile)) = 0 Then

    Set xml = GetMSXML

    xml.Open "GET", _
    "http://api.nytimes.com/svc/politics/v2/ny/legislative/2009/" & _
    chamberName & "/members.xml?api-key=" & apiKey, False
    xml.Send

    result = xml.responsetext

    ' remove é from José Serrano's name
    result = Replace(result, "é", "e")

    ' create XML file from result
    Call CreateFile(tempFile, result)

  End If

  ' open XML file
  Set xmlDoc = GetDomDoc

  With xmlDoc
    .async = False
    .validateOnParse = False
    .Load tempFile
  End With

  ' check that the XML doc loaded
  If LoadError(xmlDoc) Then
    Exit Function
  End If

  ' get root node
  Set xmlDocRoot = GetRootNode(xmlDoc)
  ' get first level child nodes
  Set result_set = GetChildNodes(xmlDocRoot)
  ' get second level child nodes
  Set results = GetChildNodes(result_set.Item(2))
  ' get members
  Set members = GetChildNodes(results.Item(4))

  ReDim tempString(1 To members.Length, 1 To members.Item(0).childNodes.Length)

  For i = 1 To members.Length
    For j = 1 To members.Item(0).childNodes.Length
      tempString(i, j) = members.Item(i - 1).childNodes(j - 1).nodeTypedValue
    Next j
  Next i

  GetMembersByChamber = tempString

End Function

I didn't bother to add a parameter that forces requerying because membership in the NYS Senate changes so frequently LOL!

Sample usage

This procedure calls the API function then iterates through the array and prints each value to the Immediate Window. If there is no value for a given element, you'll see an empty value.

Sub TestGetMembersByChamber()

Dim apiKey As String
Dim tempString() As String
Dim i As Long, j As Long

  apiKey = "your API key here"

    tempString = GetMembersByChamber(apiKey, Senate)
    For i = 1 To UBound(tempString)
      For j = 1 To UBound(tempString, 2)
        Debug.Print tempString(i, j)
      Next j
    Next i
End Sub

Get Member Details

To find out details about an individual NYS legislature member, run the following function. To get the member name for the function, run GetMembersByChamber and look for field number 5, which is the full name concatenated with hyphens. This function returns information such as

DOB
First year in the Legislature
District
Party Affiliation
Committees

The number of committees is variable, so a bit of creative math is employed to determine the correct number of array elements.

Function GetMemberDetails(apiKey As String, memberName As String) As String()

Dim xml As Object
Dim result As String
Dim tempFile As String
Dim tempString() As String
Dim xmlDoc As Object  ' MSXML2.DOMDocument
Dim xmlDocRoot As Object  ' MSXML2.IXMLDOMNode
Dim topLevelMembers As Object  ' MSXML2.IXMLDOMNodeList
Dim results As Object  ' MSXML2.IXMLDOMNodeList
Dim member As Object  ' MSXML2.IXMLDOMNodeList
Dim committeeNumber As Long
Dim committees As Object  ' MSXML2.IXMLDOMNodeList
Dim totalNodes As Long
Dim i As Long

  tempFile = Environ("temp") & "\" & memberName & ".xml"

  ' requery website if file doesn't exist
  If Len(Dir(tempFile)) = 0 Then

    Set xml = GetMSXML

    xml.Open "GET", _
    "http://api.nytimes.com/svc/politics/v2/ny/legislative/members/" & _
    memberName & ".xml?api-key=" & apiKey, False
    xml.Send

    result = xml.responsetext

    ' remove é from José Serrano's name
    result = Replace(result, "é", "e")

    ' create XML file from result
    Call CreateFile(tempFile, result)
  End If

  ' open XML file
  Set xmlDoc = GetDomDoc

  With xmlDoc
    .async = False
    .validateOnParse = False
    .Load tempFile
  End With

  ' check that the XML doc loaded
  If LoadError(xmlDoc) Then
    Exit Function
  End If

  ' get root node
  Set xmlDocRoot = GetRootNode(xmlDoc)
  ' get first level child nodes
  Set topLevelMembers = GetChildNodes(xmlDocRoot)
  ' get results set
  Set results = topLevelMembers.Item(2).childNodes
  ' get member node
  Set member = results.Item(0).childNodes

  ' get number of committees
  Set committees = member.Item(11).childNodes
  committeeNumber = committees.Length

  ' resize array
  totalNodes = (member.Length - 1) + committeeNumber
  ReDim tempString(1 To totalNodes)

  ' add basic info to array
  For i = 1 To member.Length - 1
    tempString(i) = member.Item(i - 1).nodeTypedValue
  Next i

  ' add committees to array
  For i = member.Length To totalNodes
    tempString(i) = committees.Item(i - member.Length).childNodes(0).nodeTypedValue
  Next i

  GetMemberDetails = tempString

End Function

Sample usage

The following function returns details for current district 62 Senator George D. Maziarz.

Sub TestGetMemberDetails()

Dim apiKey As String
Dim tempString() As String
Dim i As Long

  apiKey = "your API key here"

  tempString = GetMemberDetails(apiKey, "george-d-maziarz")
  For i = LBound(tempString) To UBound(tempString)
    Debug.Print tempString(i)
  Next i
End Sub

Get Member By District

The following function will return the member associated with a particular chamber and district. See Enum section for the available chambers.

District must be passed as follows:

Assembly: 1 to 150
Senate: 1 to 62

The function checks if the correct number has been passed for a given chamber. Like the other functions, it checks for an existing XML file before requerying the API. It returns such information as

Full name
Party affiliation
First year in the Legislature

Function GetMemberByDistrict(apiKey As String, chamber As chamber, _
    district As String) As String()

Dim xml As Object
Dim result As String
Dim tempFile As String
Dim tempString() As String
Dim xmlDoc As Object  ' MSXML2.DOMDocument
Dim xmlDocRoot As Object  ' MSXML2.IXMLDOMNode
Dim result_set As Object  ' MSXML2.IXMLDOMNodeList
Dim results As Object  ' MSXML2.IXMLDOMNodeList
Dim member As Object  ' MSXML2.IXMLDOMNodeList
Dim chamberName As String
Dim i As Long

  chamberName = GetChamberType(chamber)

  ' make sure district is valid
  Select Case chamberName
    Case "assembly"
      If CLng(district) < 1 Or CLng(district) > 150 Then
        Exit Function
      End If
    Case "senate"
      If CLng(district) < 1 Or CLng(district) > 62 Then
        Exit Function
      End If
  End Select

  tempFile = Environ("temp") & "\" & chamberName & district & ".xml"

  ' requery website if file doesn't exist
  If Len(Dir(tempFile)) = 0 Then

    Set xml = GetMSXML

    xml.Open "GET", _
    "http://api.nytimes.com/svc/politics/v2/ny/legislative/" & _
    chamberName & "/" & district & "/current.xml?api-key=" & apiKey, False
    xml.Send

    result = xml.responsetext

    ' remove é from José Serrano's name
    result = Replace(result, "é", "e")

    ' create XML file from result
    Call CreateFile(tempFile, result)
  End If

  ' open XML file
  Set xmlDoc = GetDomDoc

  With xmlDoc
    .async = False
    .validateOnParse = False
    .Load tempFile
  End With

  ' check that the XML doc loaded
  If LoadError(xmlDoc) Then
    Exit Function
  End If

  ' get root node
  Set xmlDocRoot = GetRootNode(xmlDoc)
  ' get first level child nodes
  Set result_set = GetChildNodes(xmlDocRoot)
  ' get second level child nodes
  Set results = GetChildNodes(result_set.Item(2))
  ' get member
  Set member = GetChildNodes(results.Item(0))

  ' resize array
  ReDim tempString(1 To member.Length)

  For i = 1 To member.Length
    tempString(i) = member.Item(i - 1).nodeTypedValue
  Next i

  GetMemberByDistrict = tempString

End Function

Sample usage

The following procedure will return the name and information for the senator from Senate district 10.

Sub TestGetMemberByDistrict()

Dim apiKey As String
Dim tempString() As String
Dim i As Long

  apiKey = "your API key here"

    tempString = GetMemberByDistrict(apiKey, Senate, "10")
    For i = LBound(tempString) To UBound(tempString)
      Debug.Print tempString(i)
    Next i
End Sub

Get Committees By Chamber

This function will return the list of committees in a given chamber. It returns both the committee name and the name concatenated with hyphens, which we'll need for another function.

Function GetCommitteesByChamber(apiKey As String, chamber As chamber) As String()

Dim xml As Object
Dim result As String
Dim tempFile As String
Dim tempString() As String
Dim xmlDoc As Object  ' MSXML2.DOMDocument
Dim xmlDocRoot As Object  ' MSXML2.IXMLDOMNode
Dim result_set As Object  ' MSXML2.IXMLDOMNodeList
Dim results As Object  ' MSXML2.IXMLDOMNodeList
Dim committees As Object  ' MSXML2.IXMLDOMNodeList
Dim committee As Object  ' MSXML2.IXMLDOMNodeList
Dim committeeNumber As Long
Dim chamberName As String
Dim i As Long, j As Long

  chamberName = GetChamberType(chamber)
  tempFile = Environ("temp") & "\" & chamberName & "committees.xml"

  ' requery website if file doesn't exist
  If Len(Dir(tempFile)) = 0 Then

    Set xml = GetMSXML

    xml.Open "GET", _
    "http://api.nytimes.com/svc/politics/v2/ny/legislative/2009/" & _
    chamberName & "/committees.xml?api-key=" & apiKey, False
    xml.Send

    result = xml.responsetext

    ' create XML file from result
    Call CreateFile(tempFile, result)
  End If

  ' open XML file
  Set xmlDoc = GetDomDoc

  With xmlDoc
    .async = False
    .validateOnParse = False
    .Load tempFile
  End With

  ' check that the XML doc loaded
  If LoadError(xmlDoc) Then
    Exit Function
  End If

  ' get root node
  Set xmlDocRoot = GetRootNode(xmlDoc)
  ' get first level child nodes
  Set result_set = GetChildNodes(xmlDocRoot)
  ' second level nodes
  Set results = GetChildNodes(result_set.Item(2))
  ' third level nodes
  Set committees = GetChildNodes(results.Item(4))

  committeeNumber = committees.Length

  ' resize array to grab the name and url_name nodes
  ReDim tempString(1 To committeeNumber, 1 To 2)

  For i = 1 To committeeNumber
    Set committee = committees.Item(i - 1).childNodes

    tempString(i, 1) = committee.Item(0).nodeTypedValue
    tempString(i, 2) = committee.Item(1).nodeTypedValue
  Next i

  GetCommitteesByChamber = tempString

End Function

Sample usage

The following function will retrieve committee names for the given chamber and iterate through the resulting array.

Sub TestGetCommitteesByChamber()

Dim apiKey As String
Dim tempString() As String
Dim i As Long, j As Long

  apiKey = "your API key here"

    tempString = GetCommitteesByChamber(apiKey, Senate)
    For i = 1 To UBound(tempString)
      For j = 1 To UBound(tempString, 2)
        Debug.Print tempString(i, j)
      Next j
    Next i
End Sub

Get Committee Membership

This function will return the names of the committee members for a given committee.

To find out the committee names, run GetCommitteesByChamber and use the second column of the array to get the hyphenated committee name to pass to this function.

The function also returns the member's party, and whether the member is the committee chair.

Function GetCommitteeMembership(apiKey As String, chamber As chamber, _
    committeeName As String) As String()

Dim xml As Object
Dim result As String
Dim tempFile As String
Dim tempString() As String
Dim xmlDoc As Object  ' MSXML2.DOMDocument
Dim xmlDocRoot As Object  ' MSXML2.IXMLDOMNode
Dim chamberName As String
Dim result_set As Object  ' MSXML2.IXMLDOMNodeList
Dim results As Object  ' MSXML2.IXMLDOMNodeList
Dim members As Object  ' MSXML2.IXMLDOMNodeList
Dim member As Object  ' MSXML2.IXMLDOMNode
Dim i As Long

  chamberName = GetChamberType(chamber)
  tempFile = Environ("temp") & "\" & chamberName & "committeemembers.xml"

  ' requery website if file doesn't exist
  If Len(Dir(tempFile)) = 0 Then

    Set xml = GetMSXML

    xml.Open "GET", _
    "http://api.nytimes.com/svc/politics/v2/ny/legislative/2009/" & _
    chamberName & "/committees/" & committeeName & ".xml?api-key=" & _
    apiKey, False
    xml.Send

    result = xml.responsetext

    ' remove é from José Serrano's name
    result = Replace(result, "é", "e")

    ' create XML file from result
    Call CreateFile(tempFile, result)
  End If

  ' open XML file
  Set xmlDoc = GetDomDoc

  With xmlDoc
    .async = False
    .validateOnParse = False
    .Load tempFile
  End With

  ' check that the XML doc loaded
  If LoadError(xmlDoc) Then
    Exit Function
  End If

  ' get root node
  Set xmlDocRoot = GetRootNode(xmlDoc)
  ' get first level child nodes
  Set result_set = GetChildNodes(xmlDocRoot)
  ' get second level nodes
  Set results = GetChildNodes(result_set.Item(2))
  ' get third level nodes
  Set members = GetChildNodes(results.Item(5))

  ReDim tempString(1 To members.Length, 1 To 3)

  For i = 1 To members.Length
    Set member = members.Item(i - 1)

    tempString(i, 1) = member.childNodes(0).nodeTypedValue
    tempString(i, 2) = member.childNodes(1).nodeTypedValue

    ' if committee member is the chair, an extra node is present
    tempString(i, 3) = ((Not member.childNodes(2) Is Nothing) Or False)

  Next i

  GetCommitteeMembership = tempString

End Function

Sample usage

The following procedure returns the membership for the NYS Senate's Aging Committee.

Sub TestGetCommitteeMembership()

Dim apiKey As String
Dim tempString() As String
Dim i As Long, j As Long

  apiKey = "your API key here"

    tempString = GetCommitteeMembership(apiKey, Senate, "aging")
    For i = 1 To UBound(tempString)
      For j = 1 To UBound(tempString, 2)
        Debug.Print tempString(i, j)
      Next j
    Next i
End Sub

Get Recent Bills

You can return information about the 20 most recent bills in either chamber. The following information (among other things) is returned:

Bill ID Number
Subject
Sponsor
Date introduced
Latest activity

Function GetRecentBills(apiKey As String, chamber As chamber, _
    billtype As billtype) As String()

Dim xml As Object
Dim result As String
Dim tempFile As String
Dim tempString() As String
Dim xmlDoc As Object  ' MSXML2.DOMDocument
Dim xmlDocRoot As Object  ' MSXML2.IXMLDOMNode
Dim result_set As Object  ' MSXML2.IXMLDOMNodeList
Dim results As Object  ' MSXML2.IXMLDOMNodeList
Dim bills As Object  ' MSXML2.IXMLDOMNodeList
Dim bill As Object  ' MSXML2.IXMLDOMNode
Dim chamberName As String
Dim billTypeName As String
Dim i As Long, j As Long

  chamberName = GetChamberType(chamber)
  billTypeName = GetBillType(billtype)

  tempFile = Environ("temp") & "\" & chamberName & "recentbills.xml"

  ' requery website if file doesn't exist
  If Len(Dir(tempFile)) = 0 Then

    Set xml = GetMSXML

    xml.Open "GET", _
    "http://api.nytimes.com/svc/politics/v2/ny/legislative/2009/" & _
    chamberName & "/bills/" & billTypeName & ".xml?api-key=" & apiKey, False
    xml.Send

    result = xml.responsetext

    ' remove é from José Serrano's name
    result = Replace(result, "é", "e")

    ' create XML file from result
    Call CreateFile(tempFile, result)
  End If

  ' open XML file
  Set xmlDoc = GetDomDoc

  With xmlDoc
    .async = False
    .validateOnParse = False
    .Load tempFile
  End With

  ' check that the XML doc loaded
  If LoadError(xmlDoc) Then
    Exit Function
  End If

  ' get root node
  Set xmlDocRoot = GetRootNode(xmlDoc)
  ' get first level child nodes
  Set result_set = GetChildNodes(xmlDocRoot)
  ' get second level nodes
  Set results = GetChildNodes(result_set.Item(2))
  ' get third level nodes
  Set bills = GetChildNodes(results.Item(4))

  ' resize array
  ReDim tempString(1 To bills.Length, 1 To bills.Item(0).childNodes.Length)

  For i = 1 To bills.Length
    Set bill = bills.Item(i - 1)

    For j = 1 To bills.Item(0).childNodes.Length
      tempString(i, j) = bill.childNodes(j - 1).nodeTypedValue
    Next j

  Next i

  GetRecentBills = tempString

End Function

Sample usage

Sub Test()

Dim apiKey As String
Dim tempString() As String
Dim i As Long, j As Long

  apiKey = "your API key here"

    tempString = GetRecentBills(apiKey, Senate, introduced)
    For i = 1 To UBound(tempString)
      For j = 1 To UBound(tempString, 2)
        Debug.Print tempString(i, j)
      Next j
    Next i
End Sub

Get Bills By Member

This function returns the bill information (see Get Recent Bills for information returned) that were sponsored by a given member. To get the member name for the function, run GetMembersByChamber and look for field number 5, which is the full name concatenated with hyphens.

Function GetBillsByMember(apiKey As String, billtype As billtype, _
    memberName As String) As String()

Dim xml As Object
Dim result As String
Dim tempFile As String
Dim tempString() As String
Dim xmlDoc As Object  ' MSXML2.DOMDocument
Dim xmlDocRoot As Object  ' MSXML2.IXMLDOMNode
Dim result_set As Object  ' MSXML2.IXMLDOMNodeList
Dim results As Object  ' MSXML2.IXMLDOMNodeList
Dim bills As Object  ' MSXML2.IXMLDOMNodeList
Dim bill As Object  ' MSXML2.IXMLDOMNode
Dim billTypeName As String
Dim i As Long, j As Long

  billTypeName = GetBillType(billtype)

  tempFile = Environ("temp") & "\" & memberName & "recentbills.xml"

  ' requery website if file doesn't exist
  If Len(Dir(tempFile)) = 0 Then

    Set xml = GetMSXML

    xml.Open "GET", _
    "http://api.nytimes.com/svc/politics/v2/ny/legislative/members/" & _
    memberName & "/bills/" & billTypeName & ".xml?api-key=" & apiKey, False
    xml.Send

    result = xml.responsetext

    ' remove é from José Serrano's name
    result = Replace(result, "é", "e")

    ' create XML file from result
    Call CreateFile(tempFile, result)
  End If

  ' open XML file
  Set xmlDoc = GetDomDoc

  With xmlDoc
    .async = False
    .validateOnParse = False
    .Load tempFile
  End With

  ' check that the XML doc loaded
  If LoadError(xmlDoc) Then
    Exit Function
  End If

  ' get root node
  Set xmlDocRoot = GetRootNode(xmlDoc)
  ' get first level child nodes
  Set result_set = GetChildNodes(xmlDocRoot)
  ' get second level nodes
  Set results = GetChildNodes(result_set.Item(2))
  ' get third level nodes
  Set bills = GetChildNodes(results.Item(3))

  ' resize array
  ReDim tempString(1 To bills.Length, 1 To bills.Item(0).childNodes.Length)

  For i = 1 To bills.Length
    Set bill = bills.Item(i - 1)

    For j = 1 To bills.Item(0).childNodes.Length
      tempString(i, j) = bill.childNodes(j - 1).nodeTypedValue
    Next j

  Next i

  GetBillsByMember = tempString

End Function

Sample usage

The following procedure will return the last few bills introduced by Senator George D. Maziarz.

Sub Test()

Dim apiKey As String
Dim tempString() As String
Dim i As Long, j As Long

  apiKey = "your API key here"

    tempString = GetBillsByMember(apiKey, introduced, "george-d-maziarz")
    For i = 1 To UBound(tempString)
      For j = 1 To UBound(tempString, 2)
        Debug.Print tempString(i, j)
      Next j
    Next i
End Sub

Get Bill Details

This function will return the details for an individual bill. It's basically the same as GetBillsByMember except it returns the information on a single bill only. It also provides some details that the other method doesn't.

To get the bill ID number, run GetBillsByMember or GetRecentBills first. Or check the NYS legislature website for the bill number.

Function GetBillDetails(apiKey As String, billId As String) As String()

Dim xml As Object
Dim result As String
Dim tempFile As String
Dim tempString() As String
Dim xmlDoc As Object  ' MSXML2.DOMDocument
Dim xmlDocRoot As Object  ' MSXML2.IXMLDOMNode
Dim result_set As Object  ' MSXML2.IXMLDOMNodeList
Dim results As Object  ' MSXML2.IXMLDOMNodeList
Dim bill As Object  ' MSXML2.IXMLDOMNodeList
Dim i As Long

  tempFile = Environ("temp") & "\" & billId & "details.xml"

  ' requery website if file doesn't exist
  If Len(Dir(tempFile)) = 0 Then

    Set xml = GetMSXML

    xml.Open "GET", _
    "http://api.nytimes.com/svc/politics/v2/ny/legislative/2009/bills/" & _
    billId & ".xml?api-key=" & apiKey, False
    xml.Send

    result = xml.responsetext

    ' remove é from José Serrano's name
    result = Replace(result, "é", "e")

    ' create XML file from result
    Call CreateFile(tempFile, result)
  End If

  ' open XML file
  Set xmlDoc = GetDomDoc

  With xmlDoc
    .async = False
    .validateOnParse = False
    .Load tempFile
  End With

  ' check that the XML doc loaded
  If LoadError(xmlDoc) Then
    Exit Function
  End If

  ' get root node
  Set xmlDocRoot = GetRootNode(xmlDoc)
  ' get first level child nodes
  Set result_set = GetChildNodes(xmlDocRoot)
  ' get second level nodes
  Set results = GetChildNodes(result_set.Item(2))
  ' get third level nodes
  Set bill = GetChildNodes(results.Item(2))

  ' resize array
  ReDim tempString(1 To bill.Length)

  For i = 1 To bill.Length
    tempString(i) = bill.Item(i - 1).nodeTypedValue
  Next i

  GetBillDetails = tempString
End Function

Sample usage

Sub Test()

  Dim apiKey As String
  Dim tempString() As String
  Dim i As Long

  apiKey = "your API key here"

  tempString = GetBillDetails(apiKey, "S07338")
  For i = LBound(tempString) To UBound(tempString)
    Debug.Print tempString(i)
  Next i

End Sub

Ancillary Functions

These functions are used by most or all of the above functions. Paste them into a standard module in the same project.

Function GetBillType(billtype As billtype) As String
  Select Case billtype
    Case 0
      GetBillType = "introduced"
    Case 1
      GetBillType = "updated"
  End Select
End Function

Function GetChamberType(chamber As chamber) As String
  Select Case chamber
    Case 0
      GetChamberType = "assembly"
    Case 1
      GetChamberType = "senate"
  End Select
End Function

The GetMSXML and GetDomDoc functions may be found on the MSXML Object Library page.