Web API Requests

xlwings Lite runs Python in the browser, which means that any API requests you make are subject to the same security constraints that apply to browser-based applications.

For security reasons, browsers only allow you to make requests to the same origin, i.e., domain, where your application is hosted, unless the server explicitly allows it by setting CORS (Cross-Origin Resource Sharing) headers in its response (like Access-Control-Allow-Origin).

Here’s what this means:

  • Public APIs such as GitHub often set the CORS headers. So the following will work:

    import pandas as pd
    
    df = pd.read_csv(
        "https://raw.githubusercontent.com/mwaskom/seaborn-data/master/penguins.csv"
    )
    
  • If your API request fails with CORS errors and you control the server you’re trying to access, configure it to add the following header to responses:

    Access-Control-Allow-Origin: https://addin.xlwings.org
    

    If you’re self-hosting the xlwings Lite add-in, use your own domain instead (e.g., https://xlwings-lite.mycompany.com).

  • If your API request fails with CORS errors and you don’t control the server, you can use a CORS proxy. This is a backend server that forwards your requests to the target API and adds the required CORS headers to the responses. While commercial proxy services exist, for security and data privacy reasons, it’s recommended to host your own CORS proxy rather than sending potentially sensitive data through third-party servers.

  • If you use the self-hosted version of xlwings Lite, you can use its built-in CORS proxy. Unlike the public proxy below, it’s transparent: once the administrator has enabled it for a host, requests to the original URL just work without any prefix, see CORS proxy.

Public data through a public CORS proxy

For public data that needs no API key, login, or cookies, a public CORS proxy is a quick way to get started. You put the proxy’s address in front of the full URL of the API. The proxy operator sees every request and response, so never send API keys, credentials, or private data through a third-party proxy.

The following script loads five years of daily Apple share prices from Yahoo Finance via cors.dev, as Yahoo doesn’t send CORS headers. Add httpx2 and pandas to requirements.txt:

import httpx2
import pandas as pd
import xlwings as xw
from xlwings import script

PROXY = "https://proxy.cors.dev/"


@script
async def yahoo_prices(book: xw.BookAsync):
    url = "https://query1.finance.yahoo.com/v8/finance/chart/AAPL"
    async with httpx2.AsyncClient(timeout=30) as client:
        response = await client.get(
            PROXY + url, params={"range": "5y", "interval": "1d"}
        )
        response.raise_for_status()
    result = response.json()["chart"]["result"][0]
    prices = pd.DataFrame(
        {"Adj Close": result["indicators"]["adjclose"][0]["adjclose"]},
        index=pd.to_datetime(result["timestamp"], unit="s").date,
    )
    prices.index.name = "Date"
    book.sheets.active["A1"].value = prices

Keep in mind:

  • Public proxies limit response sizes, request headers, and request rates. See the provider’s pricing page for the current limits.

  • Yahoo’s endpoints are unofficial and can change or rate-limit without notice. Endpoints that need a Yahoo cookie, such as quote and quoteSummary, don’t work through a proxy that drops cookies.

  • For production use, prefer a data provider whose API allows browser access, or run your own CORS proxy.

The examples use HTTPX2, the maintained continuation of HTTPX. Under Pyodide, it sends requests through the browser’s fetch. Scripts that use httpx keep working.