NYS Legislature API Examples

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.

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.

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.

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.

Sample usage

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

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

Sample usage

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

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.

Sample usage

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

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.

Sample usage

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

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 = CreateObject("MSXML2.XMLHTTP")

    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 CreateXMLFile(tempFile, result)
  End If

  ' open XML file
 Set xmlDoc = CreateObject("MSXML2.DOMDocument")

  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 = CreateObject("MSXML2.XMLHTTP")

    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 CreateXMLFile(tempFile, result)
  End If

  ' open XML file
 Set xmlDoc = CreateObject("MSXML2.DOMDocument")

  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 = CreateObject("MSXML2.XMLHTTP")

    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 CreateXMLFile(tempFile, result)
  End If

  ' open XML file
 Set xmlDoc = CreateObject("MSXML2.DOMDocument")

  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

Function GetChildNodes(node As Object) As Object
' returns child nodes for a given MSXML2.IXMLDOMNode
 Set GetChildNodes = node.childNodes
End Function

Function CreateXMLFile(fileName As String, contents As String) As String
' creates XML file from string contents

Dim tempFile As String
Dim nextFileNum As Long

  nextFileNum = FreeFile

  tempFile = fileName

  Open tempFile For Output As #nextFileNum
  Print #nextFileNum, FixAngleBrackets(contents)
  Close #nextFileNum

  CreateXMLFile = tempFile
End Function

Function GetRootNode(xmlDoc As Object) As Object
' returns root node
 Set GetRootNode = xmlDoc.documentElement
End Function

Function LoadError(xmlDoc As Object) As Boolean
' checks if a xml file load error occurred
 LoadError = (xmlDoc.parseError.errorCode <> 0)
End Function

Function ClearCache(Optional fileExtension As String = "xml")
' deletes stored xml files from temp folder
Dim filesToDelete As String
  filesToDelete = Environ("temp") & "\*." & fileExtension

  Kill filesToDelete
End Function

Function FixAngleBrackets(textString As String) As String
  FixAngleBrackets = Replace(Replace(textString, "&lt;", "<"), "&gt;", ">")
End Function