1
In Excel, navigate to the Data tab in the ribbon. Click Get Data > From Other Sources > From Web. This opens the Power Query web connector, which can fetch data from any HTTP endpoint including REST APIs.
If you do not see “Get Data” in your ribbon, make sure you are using Excel 2016 or later. For Excel 2010/2013, download the free Power Query add-in from Microsoft.
2
In the “From Web” dialog, select Advanced (we will need it for the API key header in the next step). Paste the full API URL with your query parameters into the URL field:
url
https://api.airroi.com/calculator/estimate?lat=34.052235&lng=-118.243683&bedrooms=2&baths=1&guests=4
Replace the lat/lng values with your target property's coordinates, and adjust bedrooms, baths, and guests to match the property configuration.
3
In the Advanced view of the “From Web” dialog, you will see an HTTP request header parameters section. Add your API key:
header
Header Name: X-API-KEY Header Value: your-api-key
Click OK to send the request. Excel may ask about data privacy levels — select “Organizational” or “Public” and click Connect.
4
The Power Query editor opens showing the raw JSON response as a Record. Follow these steps to convert it into a usable table:
steps
1. Click "Into Table" in the toolbar (converts the Record to a two-column table: Name and Value) 2. Click the expand icon (⇔) on the "Value" column header 3. Select the fields you want to include: ✓ revenue ✓ average_daily_rate ✓ occupancy ✓ currency 4. For nested data like percentiles, click into the Record value, then expand again: ✓ percentiles.revenue.p25 ✓ percentiles.revenue.p50 ✓ percentiles.revenue.p75 ✓ percentiles.revenue.p90 5. Click "Close & Load" to send the data to your worksheet
Power Query will create a new worksheet with your data in a structured table format. Each column corresponds to a field from the API response.
5
Clean up the table so the data is immediately useful. Rename columns, set proper data types, and create a named table for easy reference.
formatting
Column Renaming: revenue → Annual Revenue average_daily_rate → ADR occupancy → Occupancy Rate percentiles.revenue.p25 → Revenue (Conservative) percentiles.revenue.p50 → Revenue (Expected) percentiles.revenue.p75 → Revenue (Optimistic) Data Types: Annual Revenue → Currency ($) ADR → Currency ($) Occupancy Rate → Percentage (%) Revenue (Conservative) → Currency ($) Revenue (Expected) → Currency ($) Revenue (Optimistic) → Currency ($) Named Table: Select the table → Table Design tab → Table Name: "STR_Estimate"
With a named table, you can reference values in formulas like =STR_Estimate[Annual Revenue] from any sheet in the workbook.
6
Power Query connections can be refreshed manually or automatically to pull the latest data from the AirROI API.
refresh options
Manual Refresh: Data tab → Refresh All (or Ctrl+Alt+F5) Right-click table → Refresh Automatic Refresh: 1. Data tab → Queries & Connections 2. Right-click your query → Properties 3. Check "Refresh every __ minutes" 4. Set interval (e.g., 60 minutes) Optional: ✓ "Refresh data when opening the file" ✓ "Enable background refresh"
Each refresh counts as one API call. Set a reasonable interval based on how frequently the data changes — for most property analysis, refreshing once per day is sufficient.
Keep exploring the AirROI API with these related tutorials.
Power Query is built into Excel 2016 and later on Windows, and Excel for Microsoft 365. On Mac, Power Query is available in Microsoft 365 for Mac but has limited data source support — the 'From Web' connector works on recent versions. If you are on an older version, you can download the Power Query add-in for Excel 2010/2013 from Microsoft.
Yes, but only in certain configurations. In Excel desktop, go to Data > Connections > Properties and set a refresh interval (e.g., every 60 minutes). For fully automated background refresh, you need Excel running and the workbook open. SharePoint-hosted workbooks can use Power Automate for scheduled refreshes.
Edit the URL in the Power Query source step. Click Data > Queries & Connections, right-click your query, and select Edit. In the Query Editor, click the gear icon next to the 'Source' step in the Applied Steps pane. Update the lat, lng, bedrooms, baths, and guests parameters in the URL.
Yes. Create a separate Power Query for each property, then use 'Append Queries' (Home > Append Queries) to combine them into a single table. Add an 'Address' column to each query first so you can identify which row belongs to which property.
Yes. The API key is stored in the Power Query source URL, which is visible to anyone who opens the workbook and inspects the query. If you share the file, consider using a Power Query parameter linked to a cell that users fill in with their own key, or use a workbook-level named parameter.
Stay ahead of the curve
Join our newsletter for exclusive insights and updates. No spam ever.