Compiling raw data from FIRST


#1

Hi, so I (not my team! I’m still in the collect info before the pitch phase) am thinking of doing a scouting project using the raw data from recent games. The raw data can be confusing, and I was wondering if anyone else has ever done a similar project. I’m thinking of making a massive spreadsheet of the past 5 years or so. Has anyone ever done anything similar? I’m not asking you to give me your final results if you don’t want to, but just anything you could give would be great. I’m not a programmer, so I can’t read Python or Java sadly. If all you can say is it can’t be done without knowing some sort of programming language then I still need to hear it before I attempt to compile all this raw data alone lol.


#2

Consider taking a look at BigQuery from The Blue Alliance (blog about it here).

If that’s a little too complicated, some more raw data can be found on Github/the-blue-alliance/the-blue-alliance-data - not sure if this is any better than the FIRST data.


#3

I started looking at this in the fall. I’m certainly not a programmer but can understand the logic and am getting better with VBA within Excel. Through online guides and searches I was able to figure out how to pull the data from the API site into an excel doc and then use that data to create a scouting program. Hope to modify it for 2018 once the FMS data variables are published.

I know that I learned a TON doing this fun little project. I’ll try to post part of my code tonight.


#4

As promised, here is the VBA code I have for pulling in the data from API into Excel. You will need your own account and token, instructions for that are on the site you linked. I’m sure there are cleaner, more efficient ways to code this, but it works.

Sub GetMatchData()

Dim UI As Worksheet: Set UI = Sheets("User Interface")
Dim MD As Worksheet: Set MD = Sheets("MatchData")
Dim MI As Worksheet: Set MI = Sheets("MatchInfo")
Dim DB As Worksheet: Set DB = Sheets("Database")
Dim Back As Worksheet: Set Back = Sheets("Background")

'clear match data from previous search
MD.Range(MD.Cells(3, 1), MD.Cells(10000, 36)).ClearContents

Dim strURL As String, year As Integer, eventcode As String, eventname As String, matchtype As String
Dim authUser As String, authPass As String

'example: https://frc-api.firstinspires.org/v2.0/2017/scores/ILCH/qual

strURL = "https://frc-api.firstinspires.org/v2.0/"
    year = 2017
    strURL = strURL & year & "/scores/"
    eventname = UI.Range("EventName") 'event name entered in User Interface
    eventcode = WorksheetFunction.VLookup(eventname, Back.Range("A2:B164"), 2, False) 'Event code lookup
    strURL = strURL & eventcode & "/"
    matchtype = "qual"
    strURL = strURL & matchtype

MD.Cells(1, 2).Value = eventcode

authUser = "xxxxxxxxxx" 'enter your user ID
authPass = "xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxx" 'enter your token

'YouTube Tutorial: https://www.youtube.com/watch?v=mmT4qxOnEZk
Dim hreq As Object
Set hreq = CreateObject("Microsoft.XMLHTTP")
hreq.Open "GET", strURL, False
hreq.SetRequestHeader "Content-Type", "application/xml"
hreq.SetRequestHeader "Accept", "application/xml"
hreq.SetRequestHeader "Authorization", "Basic " + Base64Encode(authUser + ":" + authPass)
hreq.Send

Dim strResp As String
strResp = hreq.ResponseText

Dim xmlDoc As New MSXML2.DOMDocument
If Not xmlDoc.LoadXML(strResp) Then
    MsgBox "Load Error"
End If

Dim Match As MSXML2.IXMLDOMNode
Dim Alliance As MSXML2.IXMLDOMNode

Dim intRow As Integer
intRow = 3

For Each Match In xmlDoc.getElementsByTagName("Score_2017")
    MD.Cells(intRow, 1).Value = Match.SelectNodes("matchLevel")(0).Text
    MD.Cells(intRow, 2).Value = Match.SelectNodes("matchNumber")(0).Text
    MD.Cells(intRow + 1, 1).Value = Match.SelectNodes("matchLevel")(0).Text
    MD.Cells(intRow + 1, 2).Value = Match.SelectNodes("matchNumber")(0).Text
    intRow = intRow + 2
Next

intRow = 3
For Each Alliance In xmlDoc.getElementsByTagName("Alliance")
    MD.Cells(intRow, 3) = Alliance.SelectNodes("alliance")(0).Text
    MD.Cells(intRow, 4) = Alliance.SelectNodes("robot1Auto")(0).Text
    MD.Cells(intRow, 5) = Alliance.SelectNodes("robot2Auto")(0).Text
    MD.Cells(intRow, 6) = Alliance.SelectNodes("robot3Auto")(0).Text
    MD.Cells(intRow, 7) = Alliance.SelectNodes("autoFuelLow")(0).Text
    MD.Cells(intRow, 8) = Alliance.SelectNodes("autoFuelHigh")(0).Text
    MD.Cells(intRow, 9) = Alliance.SelectNodes("rotor1Auto")(0).Text
    MD.Cells(intRow, 10) = Alliance.SelectNodes("rotor2Auto")(0).Text
    MD.Cells(intRow, 11) = Alliance.SelectNodes("rotor1Engaged")(0).Text
    MD.Cells(intRow, 12) = Alliance.SelectNodes("rotor2Engaged")(0).Text
    MD.Cells(intRow, 13) = Alliance.SelectNodes("rotor3Engaged")(0).Text
    MD.Cells(intRow, 14) = Alliance.SelectNodes("rotor4Engaged")(0).Text
    MD.Cells(intRow, 15) = Alliance.SelectNodes("teleopFuelLow")(0).Text
    MD.Cells(intRow, 16) = Alliance.SelectNodes("teleopFuelHigh")(0).Text
    MD.Cells(intRow, 17) = Alliance.SelectNodes("touchpadNear")(0).Text
    MD.Cells(intRow, 18) = Alliance.SelectNodes("touchpadMiddle")(0).Text
    MD.Cells(intRow, 19) = Alliance.SelectNodes("touchpadFar")(0).Text
    MD.Cells(intRow, 20) = Alliance.SelectNodes("kPaRankingPointAchieved")(0).Text
    MD.Cells(intRow, 21) = Alliance.SelectNodes("rotorRankingPointAchieved")(0).Text
    MD.Cells(intRow, 22) = Alliance.SelectNodes("foulCount")(0).Text
    MD.Cells(intRow, 23) = Alliance.SelectNodes("techFoulCount")(0).Text
    MD.Cells(intRow, 24) = Alliance.SelectNodes("autoPoints")(0).Text
    MD.Cells(intRow, 25) = Alliance.SelectNodes("autoMobilityPoints")(0).Text
    MD.Cells(intRow, 26) = Alliance.SelectNodes("autoRotorPoints")(0).Text
    MD.Cells(intRow, 27) = Alliance.SelectNodes("autoFuelPoints")(0).Text
    MD.Cells(intRow, 28) = Alliance.SelectNodes("teleopPoints")(0).Text
    MD.Cells(intRow, 29) = Alliance.SelectNodes("teleopFuelPoints")(0).Text
    MD.Cells(intRow, 30) = Alliance.SelectNodes("teleopRotorPoints")(0).Text
    MD.Cells(intRow, 31) = Alliance.SelectNodes("teleopTakeoffPoints")(0).Text
    MD.Cells(intRow, 32) = Alliance.SelectNodes("kPaBonusPoints")(0).Text
    MD.Cells(intRow, 33) = Alliance.SelectNodes("rotorBonusPoints")(0).Text
    MD.Cells(intRow, 34) = Alliance.SelectNodes("adjustPoints")(0).Text
    MD.Cells(intRow, 35) = Alliance.SelectNodes("foulPoints")(0).Text
    MD.Cells(intRow, 36) = Alliance.SelectNodes("totalPoints")(0).Text
    
    intRow = intRow + 1
        
Next

Set hreq = Nothing
Set xmlDoc = Nothing

End Sub

#5

Thanks! This is exactly what I need. Now for the reading to start. ::rtm::


#6

These resources are perfect! Thank you so much.


#7

Thanks for sharing!

One question: We’re attempting something similar with VBA. However, we keep getting asked for authentication from https://frc-api.firstinspires.org/. The username/authentication token don’t seem to be accepted. We suspect this may be an issue with our base64 encoding.

Is this an issue you encountered?

Andrew


#8

Have you created a login and recieved a token? Takes a couple days to get via email.


#9

I’m not able to get to the BigQuery data set. Getting an error message that says “Unable to find dataset”.

Anybody know if this resource is off line or no longer available?