Add Air Quality Index Scores to a Property List in a Excel workbook

The Scenario

The board presentation is on Thursday. You're an ESG analyst at a real estate fund, and one section of the deck covers environmental risk by property. You have an Excel workbook with 50 properties — property name in column A, latitude in column B, longitude in column C. You need AQI scores and dominant pollutant data for each one to populate the risk matrix. The request landed in your inbox this morning.

The bad version:

• You find the OpenWeather Air Pollution API documentation, figure out the correct endpoint format (it takes lat/lon as query parameters, not city names), and start calling it manually — copying coordinates from columns B and C, pasting them into a request URL, sending the call, and transcribing the AQI value and main pollutant field from the JSON response into columns D and E.
• The JSON response nests the AQI inside list[0].main.aqi and the pollutants inside list[0].components — you have to mentally decode a different structure than the current weather endpoint, and you get it wrong twice before you figure out which number maps to which field.
• 50 rows. At two minutes per row, that's over an hour and a half — and the board deck is due at 4 PM.

This isn't research work. It's data movement. And it's eating time that was supposed to go toward the analysis itself.

The Easy Way: One Prompt in SheetXAI

SheetXAI is an AI agent that lives inside your Excel workbook. It reads the coordinates from columns B and C, calls the OpenWeather Air Pollution API for each property, and writes the AQI score and dominant pollutant into the columns you specify — including conditional formatting logic if you ask for it.

For each location in this workbook, use the latitude in column B and longitude in column C to fetch the current air pollution data from OpenWeather and write the AQI value and dominant pollutant into columns D and E — highlight any row red where AQI is above 150

What You Get

• Column D: AQI score on the OpenWeather scale (1–5 index) for each property
• Column E: dominant pollutant label (e.g., PM2.5, NO2, O3) for each location
• Rows where AQI exceeds 150 are highlighted or flagged per your instruction
• Properties where coordinates are missing or malformed are marked so you can address them before the presentation

What If the Data Is Not Quite Ready

Some coordinates are entered with extra spaces or in the wrong decimal format

Clean up the coordinate values in columns B and C (remove extra spaces, standardize to 6 decimal places), then fetch current AQI and dominant pollutant from OpenWeather for each row and write results into columns D and E

You need AQI broken down by pollutant component, not just the composite score

For each property in this workbook, use lat/lon from columns B and C to fetch current air pollution data from OpenWeather — write the composite AQI into column D, and write individual component values (PM2.5, PM10, NO2, O3, CO) into columns E through I

The properties span two worksheets — 'Residential' and 'Commercial' — and both need AQI enrichment

Fetch current AQI and dominant pollutant from OpenWeather for all properties in the 'Residential' worksheet (lat in col B, lon in col C, output to D and E) and all properties in the 'Commercial' worksheet (same column layout) — apply the same logic to both sheets

Get the data, flag the high-risk properties, and draft a summary sentence for the board section — all at once

Fetch current AQI and dominant pollutant from OpenWeather for all 50 properties using lat/lon from columns B and C, write values into columns D and E, flag column F as 'HIGH RISK' for AQI above 150 and 'MODERATE RISK' for AQI between 100–150, then add a summary row at the bottom counting properties in each risk category

One prompt handles the fetch, the risk classification, and the summary — so the board section is ready when you open the workbook.

Try It

Open the property workbook and Get the 7-day free trial of SheetXAI — ask it to fetch AQI scores for all 50 rows and flag the high-risk properties before your next environmental report. You can also explore how SheetXAI handles historical AQI trend tables or browse the OpenWeather API hub for the full list of supported data types.