JSON Extraction using the JeX Framework

Excel template for extracting JSON data

Tapping into online JSON data

The JeX Framework Excel worksheet gives you a template that lets you tap into any online JSON data without having to do any VBA coding. JSON files boil down to key/value pairs, consequently you just need  the tell JeX (on the Key worksheet) the URL and the JSON structure (schema) for each key that you need the value for. The process is straightforward:-

  1. Find all the key/value pairs you need to extract by inspecting the JSON data in a JSON viewer, such as the one inside the Firefox browser.
  2. Provide a list of actual JSON keys which you need to read values for in the "JSON Key" Column.
  3. Give each Key a unique name you choose in the "KeyID" column. Use this as the first parameter in the JeX call
  4. Describe the JSON schema for each key in the "Schema" column using "<key>" to describe the where the JSON key fits in the schema Use "[]" to describe arrays.
  5. Describe the URL in the URL column, using "<>" to identify any replaceable parameters, JeX will substitute these into the URL in the order they are supplied

On the spreadsheet, you can now extract the value for any Key ID using the user defined function (UDF):

=JeX(KeyID, URLparameters)

Usually, you will have a few schemas for each URL and a few keys for each schema. Lets look at an example.

Real life example: NFL Team Quick Stats

Here's an example using one of the dozens of available NFL JSON feeds. This one gives the team Quick Stats. The team ID has to be provided in the URL:-

NFL Team Quick Stats JSON URL shown in Firefox

Below is shown how you might translate this for JeX.

Screenshot Keys for Example File

 

Extracting the JSON values

Once you have defined keys, schemas and URLs, you can enter the JeX function in any cell to access the values. Here's an example using the schemas we just defined. The first argument is always the Key ID, following arguments are plugged into the URL.

The formula is simply entered once and dragged to all the other cells in the spreadsheet. Above we show it in formula view. The header comprises all the keys which make up the first argument of each jex function. The Team ID is provided as the second argument. You can also give jex explicit strings, for example cell B2 could be written

jex("Off. Point Per Game", "ARI")

A portion of the finished worksheet is shown below:

There's a total of 512 values which JeX reads back for this, however each unique URL (of which there are 32) is requested only once and cached, consequently the sheet takes only 4 seconds to refresh.

Logically, you probably wouldn't pull the rankings since this is the kind of thing which Excel does so well. Also there are (probably) URLs which hold all the data, which would be more efficient. This is just an example. Once you have the points and the yardage, you can manipulate the data to do your bidding. Re-ordering the rows or columns isn't going to affect the data.

Simple introduction to the JeX Framework

JeX Framework in more depth; how to handle arrays

Click below to add JeX Framework to the cart

JeX framework is a downloadable Excel spreadsheet with all the VBA necessary to do JSON extraction. You don't have to know VBA to use it.