# Load required libraries
import pandas as pd
import requests

# Define the request URL
url = "https://app.bde.es/bierest/resources/srdatosapp/listaSeries?idioma=en&series=DEEQ.N.ES.W1.S1.S1.T.B.G._Z._Z._Z.EUR._T._X.N.ALL,DEEQ.N.ES.W1.S1.S1.T.B.SD._Z._Z._Z.EUR._T._X.N.ALL,DEEQ.N.ES.W1.S1.S1.T.B.OS._Z._Z._Z.EUR._T._X.N.ALL&rango=MAX"

# Check whether the request was successful

response = requests.get(url)

if response.status_code == 200:
    data = response.json()
    print("Data retrieved successfully")
else:
    print(f"API call error: {response.status_code}")

Data retrieved successfully

# View the JSON returned by the API (only the structure of the first series)
print(data[:1])

[{'serie': 'DEEQ.N.ES.W1.S1.S1.T.B.G._Z._Z._Z.EUR._T._X.N.ALL', 'descripcion': 'BP. Goods. Balance', 'descripcionCorta': 'BP. Goods', 'codFrecuencia': 'Q', 'decimales': 0, 'simbolo': 'M&euro;', 'informacion': [{'titulo': 'Name', 'descripcion': 'BP. Goods'}, {'titulo': 'Description', 'descripcion': 'External statistics. Balance of payments. BP. Goods. Balance.'}, {'titulo': 'Units', 'descripcion': 'Millions of euros'}, {'titulo': 'Decimals', 'descripcion': '0'}, {'titulo': 'Number of observations', 'descripcion': '133'}, {'titulo': 'First value', 'descripcion': '[1Q 1993] -3,091 M&euro;'}, {'titulo': 'Last value', 'descripcion': '[1Q 2026] -11,867 M&euro;'}, {'titulo': 'Min value', 'descripcion': '[4Q 2007] -26,301 M&euro;'}, {'titulo': 'Max value', 'descripcion': '[2Q 2020] 675 M&euro;'}, {'titulo': 'Source', 'descripcion': 'Bank of Spain'}, {'titulo': 'Related series (pdf table)', 'descripcion': "<a href='https://www.bde.es/webbe/es/estadisticas/compartido/datos/pdf/a0206b.pdf'>Download the pdf table with related series</a>"}, {'titulo': 'Related series (Excel file)', 'descripcion': "<a href='https://www.bde.es/webbe/es/estadisticas/compartido/datos/xlsx/be0206.xlsx'>Download the Excel file with related series</a>"}], 'fechaInicio': '1993-01-01T09:15:00Z', 'fechaFin': '2026-01-01T09:15:00Z', 'fechas': ['2026-01-01T09:15:00Z', '2025-10-01T08:15:00Z', '2025-07-01T08:15:00Z', '2025-04-01T08:15:00Z', '2025-01-01T09:15:00Z', '2024-10-01T08:15:00Z', '2024-07-01T08:15:00Z', '2024-04-01T08:15:00Z', '2024-01-01T09:15:00Z', '2023-10-01T08:15:00Z', '2023-07-01T08:15:00Z', '2023-04-01T08:15:00Z', '2023-01-01T09:15:00Z', '2022-10-01T08:15:00Z', '2022-07-01T08:15:00Z', '2022-04-01T08:15:00Z', '2022-01-01T09:15:00Z', '2021-10-01T08:15:00Z', '2021-07-01T08:15:00Z', '2021-04-01T08:15:00Z', '2021-01-01T09:15:00Z', '2020-10-01T08:15:00Z', '2020-07-01T08:15:00Z', '2020-04-01T08:15:00Z', '2020-01-01T09:15:00Z', '2019-10-01T08:15:00Z', '2019-07-01T08:15:00Z', '2019-04-01T08:15:00Z', '2019-01-01T09:15:00Z', '2018-10-01T08:15:00Z', '2018-07-01T08:15:00Z', '2018-04-01T08:15:00Z', '2018-01-01T09:15:00Z', '2017-10-01T08:15:00Z', '2017-07-01T08:15:00Z', '2017-04-01T08:15:00Z', '2017-01-01T09:15:00Z', '2016-10-01T08:15:00Z', '2016-07-01T08:15:00Z', '2016-04-01T08:15:00Z', '2016-01-01T09:15:00Z', '2015-10-01T08:15:00Z', '2015-07-01T08:15:00Z', '2015-04-01T08:15:00Z', '2015-01-01T09:15:00Z', '2014-10-01T08:15:00Z', '2014-07-01T08:15:00Z', '2014-04-01T08:15:00Z', '2014-01-01T09:15:00Z', '2013-10-01T08:15:00Z', '2013-07-01T08:15:00Z', '2013-04-01T08:15:00Z', '2013-01-01T09:15:00Z', '2012-10-01T08:15:00Z', '2012-07-01T08:15:00Z', '2012-04-01T08:15:00Z', '2012-01-01T09:15:00Z', '2011-10-01T08:15:00Z', '2011-07-01T08:15:00Z', '2011-04-01T08:15:00Z', '2011-01-01T09:15:00Z', '2010-10-01T08:15:00Z', '2010-07-01T08:15:00Z', '2010-04-01T08:15:00Z', '2010-01-01T09:15:00Z', '2009-10-01T08:15:00Z', '2009-07-01T08:15:00Z', '2009-04-01T08:15:00Z', '2009-01-01T09:15:00Z', '2008-10-01T08:15:00Z', '2008-07-01T08:15:00Z', '2008-04-01T08:15:00Z', '2008-01-01T09:15:00Z', '2007-10-01T08:15:00Z', '2007-07-01T08:15:00Z', '2007-04-01T08:15:00Z', '2007-01-01T09:15:00Z', '2006-10-01T08:15:00Z', '2006-07-01T08:15:00Z', '2006-04-01T08:15:00Z', '2006-01-01T09:15:00Z', '2005-10-01T08:15:00Z', '2005-07-01T08:15:00Z', '2005-04-01T08:15:00Z', '2005-01-01T09:15:00Z', '2004-10-01T08:15:00Z', '2004-07-01T08:15:00Z', '2004-04-01T08:15:00Z', '2004-01-01T09:15:00Z', '2003-10-01T08:15:00Z', '2003-07-01T08:15:00Z', '2003-04-01T08:15:00Z', '2003-01-01T09:15:00Z', '2002-10-01T08:15:00Z', '2002-07-01T08:15:00Z', '2002-04-01T08:15:00Z', '2002-01-01T09:15:00Z', '2001-10-01T08:15:00Z', '2001-07-01T08:15:00Z', '2001-04-01T08:15:00Z', '2001-01-01T09:15:00Z', '2000-10-01T08:15:00Z', '2000-07-01T08:15:00Z', '2000-04-01T08:15:00Z', '2000-01-01T09:15:00Z', '1999-10-01T08:15:00Z', '1999-07-01T08:15:00Z', '1999-04-01T08:15:00Z', '1999-01-01T09:15:00Z', '1998-10-01T08:15:00Z', '1998-07-01T08:15:00Z', '1998-04-01T08:15:00Z', '1998-01-01T09:15:00Z', '1997-10-01T08:15:00Z', '1997-07-01T08:15:00Z', '1997-04-01T08:15:00Z', '1997-01-01T09:15:00Z', '1996-10-01T08:15:00Z', '1996-07-01T08:15:00Z', '1996-04-01T08:15:00Z', '1996-01-01T09:15:00Z', '1995-10-01T09:15:00Z', '1995-07-01T08:15:00Z', '1995-04-01T08:15:00Z', '1995-01-01T09:15:00Z', '1994-10-01T09:15:00Z', '1994-07-01T08:15:00Z', '1994-04-01T08:15:00Z', '1994-01-01T09:15:00Z', '1993-10-01T09:15:00Z', '1993-07-01T08:15:00Z', '1993-04-01T08:15:00Z', '1993-01-01T09:15:00Z'], 'valores': [-11867, -12125, -15050, -9343, -12448, -10713, -10362, -6423, -6359, -9312, -12042, -8531, -5166, -11259, -19133, -15237, -14586, -11888, -6847, -1568, -1000, -186, -2260, 675, -5259, -5474, -8842, -3734, -7145, -7305, -8831, -5951, -6163, -5237, -7092, -3423, -5436, -4647, -3575, -1802, -3719, -4869, -6802, -4558, -4446, -4801, -6489, -4744, -5225, -4372, -4664, -1264, -2312, -3918, -6834, -7790, -9434, -10663, -11016, -9883, -11514, -11876, -12134, -12986, -10968, -12099, -11257, -8499, -10057, -17677, -21123, -24491, -24589, -26301, -23827, -22509, -21403, -22648, -21366, -21692, -20796, -18703, -18629, -18033, -15875, -15710, -15272, -13747, -11750, -11714, -11459, -10039, -9419, -11206, -9752, -9367, -8498, -9323, -10869, -10819, -9213, -10440, -11092, -10396, -9419, -8725, -8607, -8013, -5811, -6837, -5624, -4281, -4175, -3866, -3443, -3679, -3327, -2906, -3770, -3724, -3397, -3668, -4623, -4467, -3395, -4013, -4259, -3685, -2279, -3217, -3763, -3635, -3091]}]

# Create the data frame
df = pd.DataFrame(data)
df.head()

# Extract the unit of measurement for each series

def get_unidad(info):
    return next(
        (item["descripcion"] for item in info if item["titulo"] == "Units"),
        None
    )

df["unidad"] = df["informacion"].apply(get_unidad)

# Expand the date and value arrays
df_exploded = df.explode(['fechas', 'valores'])

# Rename the columns
df_exploded = df_exploded.rename(columns={
    'serie': 'series',
    'descripcion': 'description',
    'codFrecuencia': 'frequency',
    'unidad': 'unit',
    'decimales': 'decimals',
    'fechas': 'date',
    'valores': 'value'
})

# Format the date
df_exploded["date"] = pd.to_datetime(df_exploded["date"]).dt.date

df_exploded.head()

# Select the desired columns
df_filtrado = df_exploded[['series', 'description', 'frequency', 'unit', 'decimals', 'date', 'value']]
df_filtrado.head()

# Export the data frame to Excel
df_filtrado.to_excel('df_api_bde_example_python.xlsx', index=False)

	serie	descripcion	descripcionCorta	codFrecuencia	simbolo	informacion	fechaInicio	fechaFin	fechas	valores
0	DEEQ.N.ES.W1.S1.S1.T.B.G._Z._Z._Z.EUR._T._X.N.ALL	BP. Goods. Balance	BP. Goods	Q	M€	[{'titulo': 'Name', 'descripcion': 'BP. Goods'...	1993-01-01T09:15:00Z	2026-01-01T09:15:00Z	[2026-01-01T09:15:00Z, 2025-10-01T08:15:00Z, 2...	[-11867, -12125, -15050, -9343, -12448, -10713...
1	DEEQ.N.ES.W1.S1.S1.T.B.SD._Z._Z._Z.EUR._T._X.N...	BP. Travel. Balance	BP. Travel	Q	M€	[{'titulo': 'Name', 'descripcion': 'BP. Travel...	1993-01-01T09:15:00Z	2026-01-01T09:15:00Z	[2026-01-01T09:15:00Z, 2025-10-01T08:15:00Z, 2...	[13469, 12842, 24745, 19620, 13186, 12758, 239...
2	DEEQ.N.ES.W1.S1.S1.T.B.OS._Z._Z._Z.EUR._T._X.N...	BP. Non-tourism services. Balance	BP. Non-tourism services	Q	M€	[{'titulo': 'Name', 'descripcion': 'BP. Non-to...	1993-01-01T09:15:00Z	2026-01-01T09:15:00Z	[2026-01-01T09:15:00Z, 2025-10-01T08:15:00Z, 2...	[11472, 12739, 10038, 10574, 9886, 9284, 7603,...

series	description	frequency	unit	date	value
DEEQ.N.ES.W1.S1.S1.T.B.G._Z._Z._Z.EUR._T._X.N.ALL	BP. Goods. Balance	Q	Millions of euros	2026-01-01	-11867
DEEQ.N.ES.W1.S1.S1.T.B.G._Z._Z._Z.EUR._T._X.N.ALL	BP. Goods. Balance	Q	Millions of euros	2025-10-01	-12125
DEEQ.N.ES.W1.S1.S1.T.B.G._Z._Z._Z.EUR._T._X.N.ALL	BP. Goods. Balance	Q	Millions of euros	2025-07-01	-15050
DEEQ.N.ES.W1.S1.S1.T.B.G._Z._Z._Z.EUR._T._X.N.ALL	BP. Goods. Balance	Q	Millions of euros	2025-04-01	-9343
DEEQ.N.ES.W1.S1.S1.T.B.G._Z._Z._Z.EUR._T._X.N.ALL	BP. Goods. Balance	Q	Millions of euros	2025-01-01	-12448