The Scenario
You're the operations analyst for a pharmacy chain with 120 store locations spread across four states. Every October the supply planning team runs a flu-medication stock optimization: which stores get extra inventory of which products, and when. This year, someone on the leadership team asked why the allocation model didn't use predictive data instead of last year's actuals. The answer led to Ambee's ILI risk forecast. You now have 120 store lat/lng pairs in an Excel workbook and need 28-day ILI risk forecasts—peak risk day and max risk level—for each location before the model runs on Monday.
The bad version:
- Find the Ambee ILI forecast endpoint, read the response schema to understand how "peak risk day" and "max risk level" are represented in the JSON output.
- Authenticate, build the request for store 1, parse the response, find the right nested fields, copy peak day and max level, paste into columns C and D in the workbook.
- Repeat for store 2. At store 18, realize the endpoint returns a 28-element array indexed by day and you've been reading the wrong index for "peak" for the last six stores.
The stock allocation model runs Monday whether or not the forecast data is in the workbook. If it's not there, the model falls back to last year's actuals—and the whole point of this exercise was to do better than that.
The Easy Way: One Prompt in SheetXAI
SheetXAI is an AI agent inside your Excel workbook. It reads your coordinate columns, queries Ambee's ILI forecast API for each location, identifies the peak risk day and maximum risk level from the 28-day window, and writes both values into the columns you specify—across all 120 rows in one run.
Here is the prompt for this task:
For every store lat/lng pair in columns A and B, fetch Ambee's 28-day ILI risk forecast and write peak risk day and max risk level into columns C and D
What You Get
- Column C: Peak risk day within the 28-day window (expressed as a date or day number)
- Column D: Maximum ILI risk level across the 28-day period (numeric score or classification)
- Rows where Ambee has no ILI forecast data for the coordinates show "No data" in columns C and D
What If the Data Is Not Quite Ready
Store coordinates are in separate worksheets (one worksheet per region) and need to be consolidated
Pull the lat/lng pairs from the "Northeast," "Southeast," "Midwest," and "West" worksheets, combine them into a single dataset, fetch Ambee's 28-day ILI forecast for each location, and write peak risk day and max risk level into a consolidated output worksheet called "ILI Forecasts" with columns: Region, Store ID, Lat, Lng, Peak Risk Day, Max Risk Level.
The model needs 7-day average ILI risk score rather than 28-day peak
For each lat/lng pair in columns A and B, fetch Ambee's 28-day ILI forecast, compute the average risk score across the first 7 days, and write that 7-day average into column C instead of the 28-day peak.
ILI forecast needs to be joined with store inventory capacity from column E
Columns A and B have lat/lng pairs, column C has current stock capacity (units). Fetch the 28-day ILI peak risk day and max risk level from Ambee and write into columns D and E without overwriting column C. Then add a "Reorder Flag" in column F for any store where max risk level exceeds 70 and current stock capacity is below 500.
Kill-chain: fetch forecasts, classify risk tiers, compute reorder quantities, generate allocation summary
For each lat/lng pair in columns A and B, fetch Ambee's 28-day ILI forecast. Write peak risk day into column C and max risk level into column D. Classify each store's max risk as Low (<40), Moderate (40–70), or High (>70) in column E. Compute a recommended reorder quantity in column F (Low: base stock, Moderate: base × 1.3, High: base × 1.7, using the base quantity in column G). Then create a worksheet called "Allocation Plan" with stores sorted by recommended reorder quantity descending.
Ask for the full pipeline in one prompt—SheetXAI runs forecast retrieval, classification, and summary output together.
Try It
Open any Excel workbook with store location coordinates and get the 7-day free trial of SheetXAI. Ask it to pull 28-day ILI risk forecasts from Ambee for every lat/lng pair and write peak risk day and max risk level into adjacent columns, and the allocation model has real predictive inputs before Monday's run. Also useful: enriching locations with pollen levels or the full Ambee integration overview.