FCC Consumer Broadband API

The FCC Consumer Broadband API returns broadband speed test information for a given location, given a latitude and longitude passed to the API.

From FCC.gov:

This API returns the Consumer Broadband Test speed test statistics for a US County given the passed Latitude and Longitude. The statistics are grouped into wireline and wireless and are the number of tests, average download speed, average upload speed, maximum download speed and maximum upload speed. This data is calculated nightly and includes all tests to date performed through the consumer broadband test.

Here we'll explore some sample code for returning this information to your VBA programs.

For even more sample code for working with web APIs, visit the VBA page or go through the blog.

We'll start with the following public constant, which defines the API endpoint URL:

Public Const baseURL As String = _
        "http://data.fcc.gov/api/speedtest/find?latitude=38.0&longitude=-77.5"

Notice that the longitude and latitude are left in the URL. I did that for a simple reason, which we will see later.

The GetBroadbandResults Function

The following function queries the FCC Broadband API and caches the response.

You must pass in a latitude/longitude pair for a location in the United States. To return latitude and/or longitude for a given point, see Latitude and Longitude Functions.

Note that this custom function may not return all available information from the API, and is provided here for reference only. For example, the API may have additional fields that are not returned by the function, or additional query parameters with which to filter the results of the query.

Function GetBroadbandResults(latitude As String, longitude As String, _
                     Optional forceRequery As Boolean = False) As String()

Dim xml As Object  ' MSXML2.XMLHTTP
Dim result As String
Dim tempFolder As String
Dim tempFile As String
Dim tempString() As String
Dim xmlDoc As Object  ' MSXML2.DOMDocument
Dim xmlDocRoot As Object  ' MSXML2.IXMLDOMNode
Dim speedTest As Object ' MSXML2.IXMLDOMNode
Dim responseCode As String
Dim numCols As Long
Dim i As Long

Const XML_FILE_EXTENSION As String = ".xml"

  tempFolder = environ("temp") & "\"
  tempFile = tempFolder & "FCC_broadband" & latitude & longitude & XML_FILE_EXTENSION

  ' requery if cache file is missing or forceRequery set to True
  If Len(Dir(tempFile)) = 0 Or forceRequery Then

    Set xml = GetMSXML

    With xml
      .Open "GET", Replace(Replace(baseURL, "-77.5", longitude), "38.0", latitude), False
      .send
    End With

    result = xml.responseText

    CreateFile tempFile, ConvertAccent(result)

  End If

  ' load XML file into new XML document
  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)

  ' check for error code
  responseCode = xmlDocRoot.Attributes.getNamedItem("status").nodeTypedValue

  If responseCode <> "OK" Then
    Exit Function
  End If

  Set speedTest = GetNode(xmlDocRoot, 1)

  ' resize array
  numCols = speedTest.childNodes.Length
  ReDim tempString(1 To 1, 1 To numCols)

  For i = 1 To numCols
    tempString(1, i) = GetNode(speedTest, i).nodeTypedValue
  Next i

  GetBroadbandResults = tempString

End Function

Instead of stitching together the URL based on the input parameters, we use the Replace function to substitute the existing parameters for the new ones.

We check the response code to make sure a valid response was received. If so, the response is parsed. Here is an example of what it looks like.

<?xml version="1.0" encoding="UTF-8" standalone="yes" ?>
<response xmlns="http://data.fcc.gov/api" executionTime="0.066" status="OK">
  <speedTestCounty>
    <wirelineMaxDownload>30969.0</wirelineMaxDownload>
    <wirelineMaxUpload>15175.0</wirelineMaxUpload>
    <wirelineAvgDownload>24090.5</wirelineAvgDownload>
    <wirelineAvgUpload>15175.0</wirelineAvgUpload>
    <wirelessMaxDownload>7203.0</wirelessMaxDownload>
    <wirelessMaxUpload>3038.0</wirelessMaxUpload>
    <wirelessAvgDownload>7203.0</wirelessAvgDownload>
    <wirelessAvgUpload>3038.0</wirelessAvgUpload>
    <wirelineTests>25</wirelineTests>
    <wirelessTests>18</wirelessTests>
  </speedTestCounty>
</response>

Helper Functions

These MSXML helper functions are used by the function above. Paste them into a standard module in the same project.

Sample Usage

Sub TestGetBroadbandResults()

Dim result() As String
Dim i As Long, j As Long

  result = GetBroadbandResults("40.0", "-85")

  For i = LBound(result) To UBound(result)
    For j = LBound(result, 2) To UBound(result, 2)
      Debug.Print result(i, j)
    Next j
  Next i

End Sub