Get Oil Prices in Excel: Complete Guide to Commodity Data Without Code

Need oil prices in your Excel spreadsheets? Whether you're building financial models, tracking fuel costs, or analyzing energy markets, this guide shows you how to import real-time commodity prices into Excel—no coding required.

Why Import Oil Prices to Excel?

Common Use Cases

The Problem with Manual Entry

Many professionals still copy-paste oil prices from websites into Excel. This creates:

• Errors: Typos, wrong dates, missed updates
• Time waste: 15-30 minutes per update
• Stale data: Prices change daily
• Inconsistency: Different sources, different formats

Solution: OilPriceAPI for Excel

OilPriceAPI provides commodity prices via a simple REST API that Excel can query directly. Get live prices for 50+ commodities without writing code.

What You Can Import

Method 1: Power Query (Recommended)

Power Query is built into Excel 2016+ and Microsoft 365. It's the best way to import API data.

Step-by-Step Setup

1. Get Your Free API Key

1. Visit oilpriceapi.com/signup
2. Create a free account
3. Copy your API key from the dashboard

2. Create a New Query

1. Open Excel
2. Go to Data → Get Data → From Other Sources → From Web
3. Select Advanced

3. Configure the API Request

URL Parts:

https://api.oilpriceapi.com/v1/prices/latest?by_code=BRENT_CRUDE_USD

HTTP Headers:

• Header: Authorization
• Value: Token YOUR_API_KEY

Click OK.

4. Transform the JSON

Power Query will show a navigator. Click through:

1. Click on data
2. Right-click the record and select To Table
3. Expand columns as needed:
   • price → the current price
   • created_at → timestamp
   • change → daily change
   • change_percent → percentage change

5. Load to Excel

1. Click Close & Load
2. Your oil prices appear in a new table!

6. Set Up Auto-Refresh (Optional)

1. Right-click the query in the Queries pane
2. Select Properties
3. Check Refresh every X minutes
4. Set your preferred interval (e.g., 60 minutes)

Sample Power Query M Code

For advanced users, here's the M code to paste directly:

let
    Source = Json.Document(
        Web.Contents(
            "https://api.oilpriceapi.com/v1/prices/latest",
            [
                Query = [by_code = "BRENT_CRUDE_USD,WTI_CRUDE_USD,NATURAL_GAS_USD"],
                Headers = [Authorization = "Token YOUR_API_KEY"]
            ]
        )
    ),
    data = Source[data],
    prices = data[prices],
    toTable = Table.FromList(prices, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    expanded = Table.ExpandRecordColumn(toTable, "Column1", {"code", "price", "change", "change_percent", "created_at"})
in
    expanded

To use:

1. Data → Get Data → From Other Sources → Blank Query
2. View → Advanced Editor
3. Paste the code above
4. Replace YOUR_API_KEY with your actual key
5. Click Done → Close & Load

Method 2: WEBSERVICE Function (Simple)

For quick, one-off prices without Power Query:

Formula

=WEBSERVICE("https://api.oilpriceapi.com/v1/prices/latest?by_code=BRENT_CRUDE_USD&api_key=YOUR_API_KEY")

Note: This returns raw JSON. You'll need to parse it with FILTERXML or other text functions. Power Query is cleaner for most users.

Parsing JSON (Advanced)

=VALUE(MID(WEBSERVICE("https://api.oilpriceapi.com/v1/prices/latest?by_code=BRENT_CRUDE_USD&api_key=YOUR_API_KEY"),FIND("""price"":",A1)+9,6))

This extracts just the price value. Adjust the MID parameters based on your data.

Method 3: VBA Macro (Full Control)

For complex Excel applications, use VBA:

Function GetOilPrice(commodityCode As String, apiKey As String) As Double
    Dim http As Object
    Dim url As String
    Dim response As String
    Dim json As Object

    Set http = CreateObject("MSXML2.XMLHTTP")

    url = "https://api.oilpriceapi.com/v1/prices/latest?by_code=" & commodityCode

    http.Open "GET", url, False
    http.setRequestHeader "Authorization", "Token " & apiKey
    http.send

    response = http.responseText

    ' Simple JSON parsing (find price value)
    Dim priceStart As Integer
    Dim priceEnd As Integer
    priceStart = InStr(response, """price"":") + 8
    priceEnd = InStr(priceStart, response, ",")
    GetOilPrice = CDbl(Mid(response, priceStart, priceEnd - priceStart))

    Set http = Nothing
End Function

Usage in cell:

=GetOilPrice("BRENT_CRUDE_USD", "YOUR_API_KEY")

Building a Price Dashboard

Sample Dashboard Layout

Adding Conditional Formatting

1. Select the Change column
2. Home → Conditional Formatting → Color Scales
3. Set red for negative, green for positive

Adding Sparklines

1. Import 30-day historical data using:

   https://api.oilpriceapi.com/v1/prices/past_month?by_code=BRENT_CRUDE_USD&interval=1d
   
2. Create a sparkline: Insert → Sparklines → Line
3. Select the price history range

Available Commodity Codes

Crude Oil

Natural Gas

Refined Products

Marine Fuels (Select Codes)

Free Tier Limits

The free tier includes:

For a typical Excel dashboard refreshing hourly during business hours, you'll use approximately 200 requests/month—well within the free tier.

Tips for Excel Integration

1. Cache API Responses

Don't call the API in every cell formula. Use Power Query to fetch once, then reference that table.

2. Handle Errors Gracefully

Add error handling:

=IFERROR(YourAPIFormula, "Price unavailable")

3. Store API Key Safely

Put your API key in a named range or separate "config" sheet rather than hardcoding in formulas.

4. Use Appropriate Refresh Intervals

Oil prices don't change every second. Hourly or daily refresh is usually sufficient.

5. Consider Time Zones

API timestamps are in UTC. Convert to local time if needed:

=A2 - TIME(8,0,0)  ' For Pacific time

Upgrading for More Data

Getting Started

1. Sign up at oilpriceapi.com/signup - free, no credit card
2. Copy your API key from the dashboard
3. Follow Power Query steps above
4. Build your dashboard with the commodity codes you need

The free tier gives you 1,000 requests per month—plenty for Excel dashboards that refresh a few times per day.

Frequently Asked Questions

Does this work with Excel for Mac?

Yes, Power Query is available in Excel for Mac (Microsoft 365). The steps are similar. WEBSERVICE function is not available on Mac—use Power Query instead.

Can I import historical prices?

Yes, use the /past_week or /past_month endpoints to import historical data. Premium tiers include extended historical access going back years.

How do I refresh prices automatically?

In Power Query, right-click your query and set "Refresh every X minutes" in Properties. Note that Excel must be open for auto-refresh to work.

What if I exceed the free tier limits?

The API returns a 429 (rate limit) error. Your existing data stays in Excel—you just can't refresh until the next month. Upgrade to a paid plan for higher limits.

Can I share the Excel file with colleagues?

The file itself can be shared, but each user needs their own API key (free tier is fine). Don't share your API key—create separate accounts.

Why is my price different from Bloomberg/Reuters?

Different data sources use different timing and calculation methods. OilPriceAPI prices are typically within a few cents of other major sources. Minor differences are normal in commodity data.

Related Resources

• API Documentation - Full endpoint reference
• Commodity Codes - All available prices
• Google Sheets Integration - Similar process for Sheets
• Power BI Integration - For advanced dashboards
• Python SDK - If you want to code