Build a Live Weather Dashboard in Excel with Python and xlwings

Overview

This article demonstrates how to build a practical Python‑Excel project that fetches real‑time weather data and displays it in an Excel worksheet.

Required Libraries

The project relies on two Python packages:

xlwings – for Excel automation

requests – for HTTP requests

Data Flow

The workflow is:

Read a city name from an Excel cell.

Query a weather API to obtain the city’s woeid (ID).

Request the weather forecast for that city.

Write the forecast data back into the worksheet.

Choosing an API

Several Chinese weather APIs require registration, so the tutorial ultimately uses the free MetaWeather API, which provides data for ten major cities.

Mapping Tables

# Weather – Chinese name mapping
weather = {
    'Snow': '雪',
    'Sleet': '雨夹雪',
    'Hail': '冰雹',
    'Thunderstorm': '雷阵雨',
    'Heavy Rain': '大雨',
    'Light Rain': '小雨',
    'Showers': '阵雨',
    'Heavy Cloud': '阴',
    'Light Cloud': '多云',
    'Clear': '晴'
}

# City – English name mapping
citys = {
    '北京': 'Beijing',
    '成都': 'Chengdu',
    '东莞': 'Dongguan',
    '广州': 'Guangzhou',
    '杭州': 'Hangzhou',
    '香港': 'Hong Kong',
    '上海': 'Shanghai',
    '深圳': 'Shenzhen',
    '天津': 'Tianjin',
    '武汉': 'Wuhan'
}

Project Setup

Install xlwings and create a starter project:

# Install xlwings
pip install xlwings -i https://mirror.baidu.com/pypi/simple/

# Generate a quickstart project
xlwings quickstart weatherapp --standalone

This creates weatherapp.py and an accompanying Excel file.

Core Python Script

import json
from pathlib import Path
import requests
import xlwings as xw

# (weather and citys dictionaries omitted for brevity)

def main():
    wb = xw.Book.caller()
    sht = wb.sheets[0]

    # Read city name from the named range "city_name"
    city_name = citys[sht.range("city_name").value]

    # Get city ID (woeid)
    URL_CITY = f"https://www.metaweather.com/api/location/search/?query={city_name}"
    response_city = requests.request("GET", URL_CITY)
    city_info = json.loads(response_city.text)[0]
    city_id = city_info["woeid"]
    city_title = city_info["title"]

    # Get weather data
    URL_WEATHER = f"https://www.metaweather.com/api/location/{city_id}/"
    response_weather = requests.request("GET", URL_WEATHER)
    weather_data = json.loads(response_weather.text)["consolidated_weather"]

    # Extract fields
    min_temp, max_temp, weather_state_name, weather_state_abbr, applicable_date = [], [], [], [], []
    for index, day in enumerate(weather_data):
        min_temp.append(day["min_temp"])
        max_temp.append(day["max_temp"])
        weather_state_name.append(weather[day["weather_state_name"]])
        weather_state_abbr.append(day["weather_state_abbr"])
        applicable_date.append(day["applicable_date"])

    # Write data back to Excel
    sht.range("C5").value = applicable_date
    sht.range("C6").value = weather_state_name
    sht.range("C7").value = max_temp
    sht.range("C8").value = min_temp
    sht.range("D3").value = city_title

    # Insert weather icons
    icon_names = ["no.1", "no.2", "no.3", "no.4", "no.5", "no.6"]
    icon_path = Path(__file__).parent / "images"
    for icon, abbr in zip(icon_names, weather_state_abbr):
        image_path = Path(icon_path, abbr + ".png")
        sht.pictures.add(image_path, name=icon, update=True)

if __name__ == "__main__":
    xw.Book("weatherapp.xlsm").set_mock_caller()
    main()

Excel UI Configuration

Open the generated weatherapp.xlsm, enable macros, and use the Developer tab to insert a button linked to the macro SampleCall . Name the button and assign the macro to trigger main(). The button toggles the cell A1 between "Hello xlwings!" and "Bye xlwings!".

Design the worksheet by adjusting row heights, column widths, background colors, and adding static text. Define a named range city_name for the city input cell and place six weather icons (named no.1 – no.6) in cells C9 – H9.

Running the Dashboard

Enter one of the supported city names (e.g., 杭州) in the city field and click the query button. The worksheet updates with the 5‑day forecast, showing dates, weather descriptions, high/low temperatures, and corresponding icons.

Conclusion

The guide provides a complete end‑to‑end example of combining Python, web APIs, and Excel automation to create an interactive data‑driven dashboard.