1 Streaming JSON data into Excel Planets Con Helsinki Finland By QinicPlanets.nu API to Excel Streaming JSON data into Excel Planets Con Helsinki Finland By Qinic

2 Why oh Why do I care Depending on you play style you might not.Gut instinct Methodical planning Planning you resources and Builds using Pen and Paper Manual entry into Excel Repetitive Let the computer do the work for you Making an API call Tools to look at this data Converting this data using these tools Automating these steps using VBA

3 First Steps: The Manual data pull Running Games require login Login provides APIKEY APIKEY will enable data pull Call to get API key:

4 First Steps: The Manual data pull continuedCurrent turn running game example: 487c-9cac-167f7b4aed1a Current turn, finished game example:

5 Tools to make this more digestiblehttps://codebeautify.org/jsonviewer

6 Excel can’t read JSON The core of making an Excel implementation possible is the VBA-JSON library/Project I use VBA-JSON v2.2.2 https://github.com/VBA-tools/VBA-JSON Parses JSON string and creates a VBA Collection in memory

7 All well and good but how do I get Excel tables from thisCan be skipped for finished games Get API Key Request (turn) data Parse JSON data Write Collection to Spreadsheet Steps: Add Microsoft scripting runtime library Get request via Microsoft.xmlhttp object Parameters API call URL with password and user Stream output to File Read File Extract API key Steps: Get request via Microsoft.xmlhttp object Parameters URL with API Key, Game ID, Race ID, Turn number Stream output to File Read File into memory Steps: Library call with JSON string Just WAIT Steps: Cycle trough desired data elements Write to Array or Cell

8 Get HTTP request detailsCreate HTTP Object Use Open Method to do a GET with an API call In the example below to login as user Method stays same for all API calls Stream (browser) output to file Dim myURL As String myURL = "http://api.planets.nu/login?username=" & username & "&password=" & password Dim WinHttpReq As Object Set WinHttpReq = CreateObject("Microsoft.XMLHTTP") WinHttpReq.Open "GET", myURL, False WinHttpReq.send myURL = WinHttpReq.responseBody If WinHttpReq.Status = 200 Then Set oStream = CreateObject("ADODB.Stream") oStream.Open oStream.Type = 1 oStream.Write WinHttpReq.responseBody oStream.SaveToFile "C:\temp\APIKey.txt", 2 ' 1 = no overwrite, 2 = overwrite oStream.Close End If

9 Parse JSON data Read JSON file into Memory (string)Call Parse Method from VBA JSON library Create a dictionary Object containing Collection and Dictionary object which contain the data Dim FSO As New FileSystemObject Dim JsonTS As TextStream Dim JsonText As String Dim Parsed As Dictionary ' Read .json file Set JsonTS = FSO.OpenTextFile("C:\temp\FinishedGamedata.txt", ForReading) JsonText = JsonTS.ReadAll JsonTS.Close ' Parse json to Dictionary ' "values" is parsed as Collection ' each item in "values" is parsed as Dictionary Set Parsed = JsonConverter.ParseJson(JsonText)