Fetch JSON data from URL and store in excel file in Python

In this tutorial, we will try to fetch JSON data from URL and store in excel file in Python.

JSON (JavaScript Object Notation) is a lightweight data-interchange format that’s easy for us to read and write and easy for machines to parse and generate. It’s commonly used for transmitting data in web applications between a server and a client.

Installing Required Libraries

Open your command prompt or working IDE and type the following Command

pip install requests pandas openpyxl

Create a .py extension file

First import the libraries

import requests
import pandas as pd

fetching data from URL

You can use any URL that returns  JSON data.

url = 'https://api.coingecko.com/api/v3/coins/markets?vs_currency=usd'

response = requests.get(url)
data = response.json()

Here I have used CoinGecko API which gives information about cryptocurrencies

converting JSON data to pandas Dataframe

df = pd.DataFrame(data)

Converting Dataframe to Excel format

excel_file_path = 'crypto_market_data.xlsx'
df.to_excel(excel_file_path, index=False)
print(f"Data successfully written to {excel_file_path}")

index=False is an argument that specifies whether to write row names to the file.

Because of .xlsx extension pandas automatically selects openpyxl to handle writing into excel file.

Output :

Data successfully written to crypto_market_data.xlsx

Also read: JSON to Pandas DataFrame in Python