The Scenario
Your manager forwarded a request from the public health team three weeks ago. They want monthly average AQI values for twelve Asian cities over the past twelve months — pre-monsoon and post-monsoon splits so they can document seasonal pollution trends for the grant report. You said you would get to it. The grant submission is next Tuesday.
The bad version:
- Navigate to IQAir's historical data section, select each city individually, download what is available, and figure out which date format the export uses this time.
- Manually reshape twelve separate exports into a common format, then pivot them into the one-month-per-column layout the research team wants in the Excel workbook.
- Realize that three of the cities exported in a different unit scale and spend an extra hour reconciling the values before the data is usable.
The research team hired you to analyze trends, not to chase export formats across a dozen downloads. The deadline is not going to move for cleanup work.
The Easy Way: One Prompt in SheetXAI
SheetXAI is an AI agent inside your Excel workbook that can reach IQAir AirVisual's historical endpoints and structure the results directly in your workbook — without you managing the export, reshape, or pivot manually.
Pull the last 6 months of daily AQI readings for the 8 cities in column A of my sheet and paste them into separate columns starting at B, one column per city, on a tab called Historical.
What You Get
- A new worksheet called Historical with one row per date.
- Column A: date labels.
- Columns B onward: daily AQI readings for each city, one column per city, labeled with the city name in the header row.
- Cities where historical data is sparse come back with a note rather than an unexplained blank column.
What If the Data Is Not Quite Ready
The Locations worksheet has inconsistent country entries
Standardize the country names in column C of my Locations worksheet to ISO country codes, then fetch the 6-month daily AQI history from IQAir for each city and write the output into a new Historical worksheet.
You need monthly averages instead of daily readings
For each city in my Locations worksheet, fetch the past 12 months of historical AQI data from IQAir and write the monthly averages into a new worksheet named AQI-History, with city in column A and one month per subsequent column.
The analysis requires pre- and post-monsoon breakdowns
Fetch the last 12 months of monthly AQI data for each city in my Locations worksheet, then in the AQI-History worksheet add two summary columns: Pre-Monsoon Average (April through June) and Post-Monsoon Average (October through December) computed from the monthly values.
Pull the history, compute the trend, and flag outliers in one pass
For each city in my Locations worksheet, fetch 12 months of monthly AQI from IQAir, write the values into AQI-History, compute the month-over-month change in a row below each city, and highlight any month where AQI jumped more than 30 points compared to the previous month.
One prompt handles the fetch, the calculation, and the conditional flag together.
Try It
Get the 7-day free trial of SheetXAI and open the workbook with your location list, then ask it to pull the historical AQI data and lay it out the way the report needs it. For related workflows, see the spoke on ranking office cities by live AQI, or the hub overview on all methods for connecting IQAir AirVisual to Excel.