Love it or hate it, Excel isn’t going anywhere

For quick exploratory analysis, rapid prototyping, or preparing a clean table for a report, Excel remains the go-to tool for many economists and data scientists. Its familiarity is comforting but frustration is often only a few clicks away. Personally, my top three pain points are:

• Integrating data from multiple sources – Adding and merging economic indicators is still clunky.
• Running real analytics – Building an econometric model in Excel just feels wrong.
• Revisiting old workbooks – Updating a complex spreadsheet months later is rarely smooth.

Fortunately, combining CountryData.io with xlwings Lite largely eliminates these headaches.

xlwings Lite: Python Inside Excel, the Right Way

If you’ve ever wished Excel’s VBA could speak Python, xlwings Lite (https://lite.xlwings.org) is the answer. Developed by Felix Zumstein, this free, cross-platform Office add-in brings Python directly into Excel—without installation hassles, server dependencies, or privacy concerns. Felix and I briefly worked together at UBS many years ago. 

Key advantages for researchers and analysts:

• No setup friction – Your Python code lives inside the workbook; just share the file and go.
• Full Python ecosystem access – Pandas, DuckDB, Statsmodels, Scikit-learn—directly inside Excel.
• Local execution – Runs entirely on your machine for speed and confidentiality.
• Custom functions & automation – Build domain-specific tools your team can use instantly.

In short, it’s a more flexible, developer-friendly alternative to “Python in Excel” from Microsoft.

CountryData.io: Economic Data Without the Pain

We built CountryData.io because we couldn’t find an off-the-shelf solution that handled economic and country risk data the way we needed. Originally an internal tool for CountryRisk.io, it is now available to clients who need:

• Specialised datasets – Hard-to-access indicators beyond the usual macroeconomic series.
• Integrated data management – Consolidate proprietary internal datasets alongside public data.

You can read our full origin story here.

From Zero to Data in Excel in 3 Steps

It takes about two minutes to go from a blank spreadsheet to an integrated dataset.

1) Install xlwings Lite

• Open a new Excel workbook, go to Home → Add-ins, search for xlwings Lite, and install.

For setup guidance, see xlwings Lite installation docs.

2) Get an API key

• For CountryData.io, request a trial key from us.
• Or start with the free World Bank API (note: some APIs may run into CORS issues—CountryData.io is already configured to work with xlwings Lite without this problem).

3) Retrieve data

·      Open the xlwings task pane and create a new Python script.

·      Example: fetching IMF WEO real GDP growth (NGDP_RPCH) for all countries.

To get data from CountryData.io, replace the sample code with this short script that uses the popular Python package "requests" for making https requests to the CountryData.io API.

import os

import requests

import pandas as pd

import xlwings as xw

from xlwings import script

@script

def get_data_CountryData(book: xw.Book):

    # TODO: store your API key under Environment Variables (hamburger menu)

    TOKEN = os.environ["COUNTRYDATA.IO-API-KEY"]

    URL = " https://app.countrydata.io/api/observations/"

    INDICATOR = "NGDP_RPCH"

    params = {"indicator_code": INDICATOR}

    headers = {"Authorization": TOKEN} 

    resp = requests.get(URL, headers=headers, params=params)

    resp.raise_for_status()  # raises an error for non-200 responses

    df = pd.DataFrame(resp.json())

    sheet = book.sheets.add(INDICATOR)

    sheet["A3"].options(index=False).value = df

    sheet.tables.add(sheet["A3"].resize(len(df) + 1, len(df.columns)))

    sheet["A:B"].autofit()

    sheet["D:D"].autofit()

    sheet.activate()

When pasting this, you will get the following error: ModuleNotFoundError: No module named 'requests'. To solve this, switch to the “requirements.txt” tab and add “requests” as a new line. Wait for the output to show:

Successfully installed requests-2.31.0
Installation complete

Now, continue by storing your Countrydata.io API key: go to the hamburger menu > Environment Variables and add it there, as shown in the screenshot (make sure to paste your own API key under “Value”):

Click on Save, the restart xlwings Lite by clicking on the link that pops up or by going to the hamburger menu > Restart.

In this example, we retrieve real GDP growth data from the IMF WEO for all countries across the full historical range by specifying the indicator code NGDP_RPCH. If you don’t know the ticker, CountryData.io provides API endpoints to list all available datasets and indicators by provider—see the API documentation.

The API returns the results as JSON, which we convert into a Pandas DataFrame. The final few lines of code create a new worksheet, write the DataFrame to it, and format it as a table—making it easy to filter by country or year. A quick click of the Play button runs the script, and moments later the data appears, cleanly structured, directly in Excel.

Even large datasets load quickly—e.g., the US 10-year Treasury yield time series (~17k data points) imports without issue.

Beyond Data Retrieval: Analytics and Dashboards

With Python inside Excel, you can:

• Run econometric models with Statsmodels.
• Build machine learning pipelines with Scikit-learn.
• Query large datasets directly in-memory with DuckDB.
• Generate dynamic dashboards combining internal and external data.

Let’s Talk

If your team spends too much time wrangling spreadsheets instead of analysing data, CountryData.io plus xlwings Lite can change that.

• For CountryData.io access – Contact us at [email protected].
• For xlwings training – Reach out to Felix to enable your team to build powerful in-Excel workflows.

We can also arrange joint webinars or training sessions for research teams.

------------------------

* Yes, the title isn’t strictly correct—CountryRisk.io is UK-incorporated. But CountryData.io is made with love by people in Switzerland and Austria.