Contents
While no one size fits all due to many factors, you will have to make a tradeoff among a handful of well-known names in the universe of football APIs. One of those names will certainly be Sportmonks.
Our football API ticks many of the boxes; however, the data format is only available in JavaScript Object Notation format, or JSON for short. Don’t fret; JSON can easily be converted to different formats, such as CSV, XML, HTML, or for use in a spreadsheet application such as Excel.
In this blog, we will focus on the latter, Excel, where we shall retrieve data from our fixtures’ endpoint, which is just one of more than 65 available endpoints on Sportmonks’ Football API.
What is JSON?
JSON is a language-independent, open-standard data and interchange format that uses human-readable text to store and transmit data objects consisting of name–value pairs and arrays.
The file format was derived from JavaScript and originally specified by Douglas Crockford in the early 2000s.
It gained widespread popularity and became the preferred choice over formats like XML due to several desirable qualities. Its text-based format, which is easy to read by both humans and machines, aids quick transfer due to its small size.
JSON is also platform agnostic and can be used with almost any programming language. While it supports complex data structures, it is also easy to maintain and update. In addition, its interoperable nature makes it an ideal choice for web services.
Why is Excel beneficial?
Data manipulation directly on a spreadsheet such as Excel allows for clear organization. Excel is also compatible with many programming languages, allowing easy integration with different systems.
While data organised in an Excel sheet can be ideal when enforcing data quality, parsing data from JSON to the popular format also comes in handy for sharing data in a structured format with third parties.
With that much said, let’s dive into the task at hand.
Chapter 1: Setting up your Environment.
While today’s exercise can be achieved using any programming language with the right library, we shall settle for C#, a familiar choice we deployed extensively in one of our previous blog posts.
We will need an environment where we can write and compile C#. Hence the need for Visual Studio Code (VSC), a cross-platform IDE and an ideal solution for today’s exercise. VSC is available on Mac, Windows, and Linux and can be downloaded from its official page for free. You may refer to the official page for installing Visual Studio Code.
In addition, you will be required to install C# Dev Kit to get started on Visual Studio Code. Search for C# Dev Kit using the Extensions tab.
1.1 Getting Started with Sportmonks’ Football API and C#
While a treasure trove of football data, such as xG, predictions, news, and odds, exists on our Football API, we will get started with a familiar exercise where we shall retrieve data from the fixtures’ endpoint. However, before we can do so, we shall need an API token. We will go about this in the next section.
1.2 Obtaining an API Token.
To use the API, you need to sign up for an account if you haven’t done so already. Your account automatically comes with the Danish Super Liga and Scottish Premier League plans at no cost.
Head over to the Create an API Token section on your account’s dashboard. Once you have your API token, which is required for authentication, you must keep it private.
Chapter 2: Making Your First API Call: Retrieving Fixtures.
For the first exercise we shall retrieve fixtures from a particular date range and convert the JSON data into a Microsoft Excel file. Head over to the ID finder on your dashboard to select the ID of the Scottish Premier League (501), which comes as a free plan on your account.
We shall select Scottish Premiership fixtures played between the 20th and 30th of December 2024. However, you are at liberty to widen the date range should you wish to do so.
Here below is our base URL, which you may place in your web browser. Don’t forget to replace the api_token with the API Token that you created earlier in 1.3.
Fire up Visual Studio Code and create a new console application which comes with the legendary “Hello World” example. You will replace the example with the piece of code here. Visit the official tutorial page for help on how to create a Console application on VSC.
using System;
using System.Net.Http;
using System.Threading.Tasks;
class Program {
static async Task Main()
{
// URL of the API
string url = "https://api.sportmonks.com/v3/football/fixtures/between/2024-11-20/2024-11-30?api_token=API_Token&filters=fixtureLeagues:501";
try
{
// Fetch JSON data
string jsonResult = await GetJsonFromUrl(url);
// Output the result
Console.WriteLine("JSON Result:");
Console.WriteLine(jsonResult);
}
catch (Exception ex)
{
Console.WriteLine($"Error fetching data: {ex.Message}");
}
}
static async Task <string> GetJsonFromUrl(string url)
{
using (HttpClient client = new HttpClient())
{
// Send GET request
HttpResponseMessage response = await client.GetAsync(url);
// Ensure the request succeeded
response.EnsureSuccessStatusCode();
// Read and return the response content as a string
return await response.Content.ReadAsStringAsync();
}
}
}
JSON Result:
{"data":[{"id":19146772,"sport_id":1,"league_id":501,"season_id":23690,"stage_id":77471570,"group_id":null,"aggregate_id":null,"round_id":340585,"state_id":5,"venue_id":8908,"name":"Ross County vs Motherwell","starting_at":"2024-11-23 15:15:00","result_info":"Ross County won after full-time.","leg":"1\/1","details":null,"length":90,"placeholder":false,"has_odds":true,"has_premium_odds":true,"starting_at_timestamp":1732374900},{"id":19146773,"sport_id":1,"league_id":501,"season_id":23690,"stage_id":77471570,"group_id":null,"aggregate_id":null,"round_id":340585,"state_id":5,"venue_id":219,"name":"St. Johnstone vs Kilmarnock","starting_at":"2024-11-23 15:15:00","result_info":"St. Johnstone won after full-time.","leg":"1\/1","details":null,"length":90,"placeholder":false,"has_odds":true,"has_premium_odds":true,"starting_at_timestamp":1732374900},{"id":19146771,"sport_id":1,"league_id":501,"season_id":23690,"stage_id":77471570,"group_id":null,"aggregate_id":null,"round_id":340585,"state_id":5,"venue_id":8914,"name":"Rangers vs Dundee United","starting_at":"2024-11-23 15:45:00","result_info":"Game ended in draw.","leg":"1\/1","details":null,"length":90,"placeholder":false,"has_odds":true,"has_premium_odds":true,"starting_at_timestamp":1732376700},{"id":19146774,"sport_id":1,"league_id":501,"season_id":23690,"stage_id":77471570,"group_id":null,"aggregate_id":null,"round_id":340585,"state_id":5,"venue_id":8879,"name":"St. Mirren vs Aberdeen","starting_at":"2024-11-23 16:00:00","result_info":"St. Mirren won after full-time.","leg":"1\/1","details":null,"length":90,"placeholder":false,"has_odds":true,"has_premium_odds":true,"starting_at_timestamp":1732377600},{"id":19146769,"sport_id":1,"league_id":501,"season_id":23690,"stage_id":77471570,"group_id":null,"aggregate_id":null,"round_id":340585,"state_id":5,"venue_id":284597,"name":"Dundee vs Hibernian","starting_at":"2024-11-23 17:55:00","result_info":"Dundee won after full-time.","leg":"1\/1","details":null,"length":90,"placeholder":false,"has_odds":true,"has_premium_odds":true,"starting_at_timestamp":1732384500},{"id":19146770,"sport_id":1,"league_id":501,"season_id":23690,"stage_id":77471570,"group_id":null,"aggregate_id":null,"round_id":340585,"state_id":5,"venue_id":336296,"name":"Hearts vs Celtic","starting_at":"2024-11-23 19:45:00","result_info":"Celtic won after full-time.","leg":"1\/1","details":null,"length":90,"placeholder":false,"has_odds":true,"has_premium_odds":true,"starting_at_timestamp":1732391100},{"id":19146759,"sport_id":1,"league_id":501,"season_id":23690,"stage_id":77471570,"group_id":null,"aggregate_id":null,"round_id":340583,"state_id":5,"venue_id":8946,"name":"Hibernian vs Aberdeen","starting_at":"2024-11-26 19:45:00","result_info":"Game ended in draw.","leg":"1\/1","details":null,"length":90,"placeholder":false,"has_odds":true,"has_premium_odds":true,"starting_at_timestamp":1732650300},{"id":19146775,"sport_id":1,"league_id":501,"season_id":23690,"stage_id":77471570,"group_id":null,"aggregate_id":null,"round_id":340586,"state_id":5,"venue_id":8909,"name":"Celtic vs Ross County","starting_at":"2024-11-30 15:00:00","result_info":"Celtic won after full-time.","leg":"1\/1","details":null,"length":90,"placeholder":false,"has_odds":true,"has_premium_odds":true,"starting_at_timestamp":1732978800},{"id":19146776,"sport_id":1,"league_id":501,"season_id":23690,"stage_id":77471570,"group_id":null,"aggregate_id":null,"round_id":340586,"state_id":5,"venue_id":8947,"name":"Dundee United vs St. Mirren","starting_at":"2024-11-30 15:00:00","result_info":"Dundee United won after full-time.","leg":"1\/1","details":null,"length":90,"placeholder":false,"has_odds":true,"has_premium_odds":true,"starting_at_timestamp":1732978800},{"id":19146778,"sport_id":1,"league_id":501,"season_id":23690,"stage_id":77471570,"group_id":null,"aggregate_id":null,"round_id":340586,"state_id":5,"venue_id":8906,"name":"Kilmarnock vs Dundee","starting_at":"2024-11-30 15:00:00","result_info":"Game ended in draw.","leg":"1\/1","details":null,"length":90,"placeholder":false,"has_odds":true,"has_premium_odds":true,"starting_at_timestamp":1732978800},{"id":19146779,"sport_id":1,"league_id":501,"season_id":23690,"stage_id":77471570,"group_id":null,"aggregate_id":null,"round_id":340586,"state_id":5,"venue_id":8922,"name":"Motherwell vs Hibernian","starting_at":"2024-11-30 15:00:00","result_info":"Hibernian won after full-time.","leg":"1\/1","details":null,"length":90,"placeholder":false,"has_odds":true,"has_premium_odds":true,"starting_at_timestamp":1732978800}],"pagination":{"count":11,"per_page":25,"current_page":1,"next_page":null,"has_more":false},"subscription":[{"meta":{"trial_ends_at":"2024-10-16 14:50:45","ends_at":null,"current_timestamp":1735439529},"plans":[{"plan":"Enterprise plan (loyal)","sport":"Football","category":"Advanced"},{"plan":"Enterprise Plan","sport":"Cricket","category":"Standard"},{"plan":"Formula One","sport":"Formula One","category":"Standard"}],"add_ons":[{"add_on":"All-in News API","sport":"Football","category":"News"},{"add_on":"pressure index add-on","sport":"Football","category":"Default"},{"add_on":"Enterprise Plan Predictions","sport":"Football","category":"Predictions"},{"add_on":"xG Advanced","sport":"Football","category":"Expected"}],"widgets":[{"widget":"Sportmonks Widgets","sport":"Football"}]}],"rate_limit":{"resets_in_seconds":3600,"remaining":2999,"requested_entity":"Fixture"},"timezone":"UTC"}
Explanation
As you can see, we defined the complete URL along with the required parameters and API token. We then used HttpClient to send the GET request asynchronously.
We checked if the response is successful using EnsureSuccessStatusCode() before we went on to read the response body as a string containing JSON data.
A try-catch block was placed to handle network or API errors. We then displayed the fetched JSON data in the console or an error message if something went wrong.
2.2 Making our JSON response readable.
As you can see, it is difficult to make out the response. However, in this section we shall make our response readable by using System.Text.Json namespace.
using System;
using System.Net.Http;
using System.Text.Json;
using System.Threading.Tasks;
class Program
{
static async Task Main()
{
// URL of the API
string url = "https://api.sportmonks.com/v3/football/fixtures/between/2024-11-20/2024-11-30?api_token=API_Token&filters=fixtureLeagues:501";
try
{
// Fetch JSON data
string jsonResult = await GetJsonFromUrl(url);
// Pretty print the JSON
string prettyJson = FormatJson(jsonResult);
// Output the result
Console.WriteLine("Pretty Printed JSON Result:");
Console.WriteLine(prettyJson);
}
catch (Exception ex)
{
Console.WriteLine($"Error fetching data: {ex.Message}");
}
}
static async Task<string> GetJsonFromUrl(string url)
{
using (HttpClient client = new HttpClient())
{
HttpResponseMessage response = await client.GetAsync(url);
response.EnsureSuccessStatusCode();
return await response.Content.ReadAsStringAsync();
}
}
static string FormatJson(string json)
{
using (JsonDocument doc = JsonDocument.Parse(json))
{
return JsonSerializer.Serialize(doc.RootElement, new JsonSerializerOptions { WriteIndented = true });
}
}
}
Pretty Printed JSON Result:
{
"data": [
{
"id": 19146772,
"sport_id": 1,
"league_id": 501,
"season_id": 23690,
"stage_id": 77471570,
"group_id": null,
"aggregate_id": null,
"round_id": 340585,
"state_id": 5,
"venue_id": 8908,
"name": "Ross County vs Motherwell",
"starting_at": "2024-11-23 15:15:00",
"result_info": "Ross County won after full-time.",
"leg": "1/1",
"details": null,
"length": 90,
"placeholder": false,
"has_odds": true,
"has_premium_odds": true,
"starting_at_timestamp": 1732374900
},
{
"id": 19146773,
"sport_id": 1,
"league_id": 501,
"season_id": 23690,
"stage_id": 77471570,
"group_id": null,
"aggregate_id": null,
"round_id": 340585,
"state_id": 5,
"venue_id": 219,
"name": "St. Johnstone vs Kilmarnock",
"starting_at": "2024-11-23 15:15:00",
"result_info": "St. Johnstone won after full-time.",
"leg": "1/1",
"details": null,
"length": 90,
"placeholder": false,
"has_odds": true,
"has_premium_odds": true,
"starting_at_timestamp": 1732374900
},
{
"id": 19146771,
"sport_id": 1,
"league_id": 501,
"season_id": 23690,
"stage_id": 77471570,
"group_id": null,
"aggregate_id": null,
"round_id": 340585,
"state_id": 5,
"venue_id": 8914,
"name": "Rangers vs Dundee United",
"starting_at": "2024-11-23 15:45:00",
"result_info": "Game ended in draw.",
"leg": "1/1",
"details": null,
"length": 90,
"placeholder": false,
"has_odds": true,
"has_premium_odds": true,
"starting_at_timestamp": 1732376700
},
{
"id": 19146774,
"sport_id": 1,
"league_id": 501,
"season_id": 23690,
"stage_id": 77471570,
"group_id": null,
"aggregate_id": null,
"round_id": 340585,
"state_id": 5,
"venue_id": 8879,
"name": "St. Mirren vs Aberdeen",
"starting_at": "2024-11-23 16:00:00",
"result_info": "St. Mirren won after full-time.",
"leg": "1/1",
"details": null,
"length": 90,
"placeholder": false,
"has_odds": true,
"has_premium_odds": true,
"starting_at_timestamp": 1732377600
},
{
"id": 19146769,
"sport_id": 1,
"league_id": 501,
"season_id": 23690,
"stage_id": 77471570,
"group_id": null,
"aggregate_id": null,
"round_id": 340585,
"state_id": 5,
"venue_id": 284597,
"name": "Dundee vs Hibernian",
"starting_at": "2024-11-23 17:55:00",
"result_info": "Dundee won after full-time.",
"leg": "1/1",
"details": null,
"length": 90,
"placeholder": false,
"has_odds": true,
"has_premium_odds": true,
"starting_at_timestamp": 1732384500
},
{
"id": 19146770,
"sport_id": 1,
"league_id": 501,
"season_id": 23690,
"stage_id": 77471570,
"group_id": null,
"aggregate_id": null,
"round_id": 340585,
"state_id": 5,
"venue_id": 336296,
"name": "Hearts vs Celtic",
"starting_at": "2024-11-23 19:45:00",
"result_info": "Celtic won after full-time.",
"leg": "1/1",
"details": null,
"length": 90,
"placeholder": false,
"has_odds": true,
"has_premium_odds": true,
"starting_at_timestamp": 1732391100
},
{
"id": 19146759,
"sport_id": 1,
"league_id": 501,
"season_id": 23690,
"stage_id": 77471570,
"group_id": null,
"aggregate_id": null,
"round_id": 340583,
"state_id": 5,
"venue_id": 8946,
"name": "Hibernian vs Aberdeen",
"starting_at": "2024-11-26 19:45:00",
"result_info": "Game ended in draw.",
"leg": "1/1",
"details": null,
"length": 90,
"placeholder": false,
"has_odds": true,
"has_premium_odds": true,
"starting_at_timestamp": 1732650300
},
{
"id": 19146775,
"sport_id": 1,
"league_id": 501,
"season_id": 23690,
"stage_id": 77471570,
"group_id": null,
"aggregate_id": null,
"round_id": 340586,
"state_id": 5,
"venue_id": 8909,
"name": "Celtic vs Ross County",
"starting_at": "2024-11-30 15:00:00",
"result_info": "Celtic won after full-time.",
"leg": "1/1",
"details": null,
"length": 90,
"placeholder": false,
"has_odds": true,
"has_premium_odds": true,
"starting_at_timestamp": 1732978800
},
{
"id": 19146776,
"sport_id": 1,
"league_id": 501,
"season_id": 23690,
"stage_id": 77471570,
"group_id": null,
"aggregate_id": null,
"round_id": 340586,
"state_id": 5,
"venue_id": 8947,
"name": "Dundee United vs St. Mirren",
"starting_at": "2024-11-30 15:00:00",
"result_info": "Dundee United won after full-time.",
"leg": "1/1",
"details": null,
"length": 90,
"placeholder": false,
"has_odds": true,
"has_premium_odds": true,
"starting_at_timestamp": 1732978800
},
{
"id": 19146778,
"sport_id": 1,
"league_id": 501,
"season_id": 23690,
"stage_id": 77471570,
"group_id": null,
"aggregate_id": null,
"round_id": 340586,
"state_id": 5,
"venue_id": 8906,
"name": "Kilmarnock vs Dundee",
"starting_at": "2024-11-30 15:00:00",
"result_info": "Game ended in draw.",
"leg": "1/1",
"details": null,
"length": 90,
"placeholder": false,
"has_odds": true,
"has_premium_odds": true,
"starting_at_timestamp": 1732978800
},
{
"id": 19146779,
"sport_id": 1,
"league_id": 501,
"season_id": 23690,
"stage_id": 77471570,
"group_id": null,
"aggregate_id": null,
"round_id": 340586,
"state_id": 5,
"venue_id": 8922,
"name": "Motherwell vs Hibernian",
"starting_at": "2024-11-30 15:00:00",
"result_info": "Hibernian won after full-time.",
"leg": "1/1",
"details": null,
"length": 90,
"placeholder": false,
"has_odds": true,
"has_premium_odds": true,
"starting_at_timestamp": 1732978800
}
],
"pagination": {
"count": 11,
"per_page": 25,
"current_page": 1,
"next_page": null,
"has_more": false
},
"subscription": [
{
"meta": {
"trial_ends_at": "2024-10-16 14:50:45",
"ends_at": null,
"current_timestamp": 1735442005
},
"plans": [
{
"plan": "Enterprise plan (loyal)",
"sport": "Football",
"category": "Advanced"
},
{
"plan": "Enterprise Plan",
"sport": "Cricket",
"category": "Standard"
},
{
"plan": "Formula One",
"sport": "Formula One",
"category": "Standard"
}
],
"add_ons": [
{
"add_on": "All-in News API",
"sport": "Football",
"category": "News"
},
{
"add_on": "pressure index add-on",
"sport": "Football",
"category": "Default"
},
{
"add_on": "Enterprise Plan Predictions",
"sport": "Football",
"category": "Predictions"
},
{
"add_on": "xG Advanced",
"sport": "Football",
"category": "Expected"
}
],
"widgets": [
{
"widget": "Sportmonks Widgets",
"sport": "Football"
}
]
}
],
"rate_limit": {
"resets_in_seconds": 1124,
"remaining": 2998,
"requested_entity": "Fixture"
},
"timezone": "UTC"
}
This method FormatJson formats the raw JSON string into an indented, human-readable format without the use of any external library, as it utilises the built-in functionality from the System.Text.Json namespace.
Chapter 3.0 Converting JSON Data to Excel File.
Now that we can read our JSON response clearly and pick out the structure, we shall send this response directly into an Excel file.
To create an MS Excel file, we shall use the external library named EPPlus, which will be installed using the NuGet Package Manager on Visual Studio Code.
3.1 Install the EPPlus External Library.
Open the Command Palette or type Ctrl+Shift+P on VSC.
Type ‘EPPlus’ after selecting NuGet Manager.
After selecting EPPlus, choose a version for the library. For this exercise we selected version 5.8.7.
For any required help installing an external library via NuGet on Visual Studio Code, you may visit the official page.
3.2. Convert JSON Data to Excel File.
After installation of EPPlus, we can write and compile our piece of code, which has many functions.
using System;
using System.Collections.Generic;
using System.Net.Http;
using System.Text.Json;
using System.Threading.Tasks;
using OfficeOpenXml;
class Program
{
static async Task Main()
{
// API URL
string url = "https://api.sportmonks.com/v3/football/fixtures/between/2024-11-20/2024-11-30?api_token=API_Token&filters=fixtureLeagues:501";
try
{
// Fetch JSON data
string jsonResult = await GetJsonFromUrl(url);
// Parse JSON into objects
List <Fixture> fixtures = ParseJson(jsonResult);
// Debugging: Display parsed data in console
Console.WriteLine("Parsed Fixtures:");
foreach (var fixture in fixtures)
{
Console.WriteLine($"ID: {fixture.FixtureId}, Name: {fixture.Name}, Date: {fixture.StartingAt}, Result: {fixture.ResultInfo}");
}
// Save to Excel
string currentDirectory = Directory.GetCurrentDirectory();
string filePath = Path.Combine(currentDirectory, "Fixtures.xlsx");
SaveToExcel(fixtures, filePath);
Console.WriteLine($"JSON data successfully saved to {filePath}");
}
catch (Exception ex)
{
Console.WriteLine($"Error: {ex.Message}");
}
}
static async Task <string> GetJsonFromUrl(string url)
{
using (HttpClient client = new HttpClient())
{
HttpResponseMessage response = await client.GetAsync(url);
response.EnsureSuccessStatusCode();
return await response.Content.ReadAsStringAsync();
}
}
static List <Fixture> ParseJson(string json)
{
using (JsonDocument doc = JsonDocument.Parse(json))
{
var fixtures = new List<Fixture>();
foreach (var element in doc.RootElement.GetProperty("data").EnumerateArray())
{
var fixture = new Fixture
{
FixtureId = element.GetProperty("id").GetInt32(),
Name = element.GetProperty("name").GetString(),
StartingAt = element.GetProperty("starting_at").GetString(),
ResultInfo = element.TryGetProperty("result_info", out var resultProperty) ? resultProperty.GetString() : "N/A"
};
fixtures.Add(fixture);
}
return fixtures;
}
}
static void SaveToExcel(List<Fixture> fixtures, string filePath)
{
ExcelPackage.LicenseContext = LicenseContext.NonCommercial;
using (var package = new ExcelPackage())
{
var worksheet = package.Workbook.Worksheets.Add("Fixtures");
// Add headers
worksheet.Cells[1, 1].Value = "Fixture ID";
worksheet.Cells[1, 2].Value = "Name";
worksheet.Cells[1, 3].Value = "Date & Time (UTC)";
worksheet.Cells[1, 4].Value = "Result Info";
// Add data
for (int i = 0; i < fixtures.Count; i++)
{
worksheet.Cells[i + 2, 1].Value = fixtures[i].FixtureId;
worksheet.Cells[i + 2, 2].Value = fixtures[i].Name;
worksheet.Cells[i + 2, 3].Value = fixtures[i].StartingAt;
worksheet.Cells[i + 2, 4].Value = fixtures[i].ResultInfo;
}
// Save to file
package.SaveAs(new System.IO.FileInfo(filePath));
}
}
}
class Fixture {
public int FixtureId { get; set; }
public string Name { get; set; }
public string StartingAt { get; set; }
public string ResultInfo { get; set; }
}
Following a careful examination of our JSON response, we have selected data for only four columns on our Excel sheet, namely Fixture ID, Name, Date & Time, and also Result info.
Along with their corresponding values, all four columns are filled with data from our JSON response.
In our example, the data is written to this Excel file rightly named Fixtures.xlsx which is saved in the ‘SaveToExcel’ method using the EPPlus library. The location of the newly created Excel file is printed on the terminal.
The image above is a screenshot showing the contents of the columns and rows in the example Excel file.
CONCLUSION
As you can see, with the proper external library, converting JSON data from Sportmonks’ Football API is not as daunting as you once presumed. With a clear understanding of the data’s structure, you too can convert any JSON response from the API to Excel.
Are you up for the challenge? Perhaps you are building the next best thing and would like to work with Excel; we’ve got a boatload of endpoints, which includes topscorers, schedules, seasons, and livescores you could try it out with.
What are you waiting for? Sign up now!
FAQ
- Create an account on My Sportmonks and get immediate access to our free plan.
- Subscribe to one of our paid plans and receive a one-time-only 14-day free trial.
