Skip to content

Excel - Power Query

It's possible to load data directly from Optimeering's API into Excel using Power Query. This guide shows some of the basics to getting started.

Note

This guide assumes you are using the desktop version of Excel on a Windows machine

Goal

Our Goal is to:

  • Load the Imbalance, PT15M, prediction series from the API
  • Use the series_id for the Imbalance, Price_Spread, Quantiles, NO3 series to load the latest prediction event

Step 1 - Obtaining an API Key

See the Obtaining an API Key docs to retrieve an API Key. This will be needed when we try to query the API from Excel.

Step 2 - Setting Up Our Credentials

Open up a new workbook in Excel.

Go to the Data tab and then click on the Get Data button. Under From Other Sources select Blank Query as shown below:

blank_query

We want to store our API Key in a Parameter. With the Home tab selected click on Manage Parameters. Click new and then change the Name to API_KEY. Under Current Value paste in your API Key from the previous step. See the video below:

Step 3 - Query the Predictions Series API

In order to later query for Prediction Events we need the ids of the series. We can retrieve those from the app.optimeering.com/api/predictions/series endpoint.

  1. Right click on the blank Query1 in the Queries sidebar and then select Rename. Call it Series
  2. On the Home tab click on Advanced Editor
  3. To authenticate our request we need to set some request headers. Replace Source = "" on the second line with the following: 
    Headers = [
    #"apikey" = API_KEY
],
    #"apikey" is the name of the header used to pass in the API Key, and API_KEY refers to the parameter we set in the previous step
  4. On the next line, define the Url we will use to query the API: 
    Url = "https://app.optimeering.com/api/predictions/series",
  5. Then make a web request with the following: 
    Content = Web.Contents(Url, [Headers = Headers]),
    The Web.Contents takes in a Url, and some request options. The only Option we want to set is the Headers, which we set to the Headers defined earlier.
  6. Our response is going to be in Json format, we can convert it using the built in functions: 
    JsonContent = Json.Document(Content)
  7. Finally, update the returned value to be JsonContent. The final query should look like this: 
    let
    Headers = [
        #"apikey" = API_KEY
    ],
    Url = "https://app.optimeering.com/api/predictions/series",
    Content = Web.Contents(Url, [Headers = Headers]),
    JsonContent = Json.Document(Content)
in
    JsonContent
  8. Click on Done down the bottom of the editor. This will return you to the Power Query Editor window.

  9. You should see an error like the following:

    no_creds

    Click on Edit Credentials, ensure Anonymous is selected and click Connect

At this point you should see a Record, with a single key items and a value that is a List, as shown below:

items_record

Congratulations, you have made your first request to the Optimeering API from Excel!

Step 4 - Expanding the Response

In this step we will transform the current record into a table with all our series as rows, and their properties as columns.

  1. Make sure that the Series query is selected on the left hand side, and JsonContent is selected on the APPLIED STEPS on the right hand side under Query Settings. If you can't see Query Settings go to the View tab and click Query Settings
  2. Click on List to load the items. You should now see a new List, with each value in the list being a new Record

  3. You should see a tab called Transform under List Tools. Click on this and then on the To Table button, and then click Ok. This converts the record into a Excel table, which makes the next step easier. This is usually a good step when you have a List of Records.

    Now you will see an Expand button on the right hand side of the Column header. Click this. You will now see the properties of our Prediction Series. You can deselect created_at, latest_event_time and version.

    Also deselect Use original column name as prefix.

    Click Ok to expand all the records in the table into new columns.

    The video below shows these steps.

  4. Finally, on the Home tab click Close & Load. You will now see all our Prediction Series as a table in your workbook

Step 5 - Load the Prediction Events

Before we get started with our next query, we need to make a Privacy Level change to our current workbook. Excel is very strict on sharing data between requests. Since we are only querying the Optimeering API, there is no danager here, so it's easiest, and more efficient, to disable this setting.

  1. On the Data tab click on Get Data and then on Query Options at the bottom
  2. Click on Privacy on the left menu

  3. Toggle Ignore the Privacy Levels and potentially improve performance

Now we are ready to create another Query. Since we are going to use the same Web Options in another query, we should move these into a query themselves.

  1. On the Data tab click on Queries & Connections
  2. On the Queries & Connections pane, double click Series to re open the Power Query Editor
  3. On the Queries pane, right click and select New Query > Other Sources > Blank Query
  4. Rename this WebOptions
  5. Open the Advanced Editor and enter the following: 
    let
    Headers = [
            #"apikey" = API_KEY
        ],
    WebOptions = [Headers = Headers]
in
    WebOptions
  6. Click Done
  7. Select the Series query and then open the Advanced Editor
  8. Remove the Headers step, and then update the Content step to reference our new WebContents query. 
    Content = Web.Contents(Url, WebOptions),
  9. Click Done and check the data still loads by clicking Refresh Preview button on the Home tab

We are now ready to make another API call.

  1. Right click on the Series query and click Reference
  2. Rename the new Query to Data

  3. Apply filters to each column until we have:

    1. product = Imbalance
    2. resolution = PT15M
    3. area = NO3
    4. unit_type = Price_Spread
    5. statistic = Quantile

    You should see a single row now in our table.

    See the video for details:

  4. Open up the Advanced Editor, add a comma at the end of the #"Filtered Rows" step and then add a new line below

  5. Add the following step: 
    Id = Text.From(Table.FirstValue(#"Filtered Rows")),
    This takes the first colum, first row value, which is the series_id, and converts it from a number to text
  6. Define the Url for getting the latest Prediction Event as follows: 
    Url = "https://app.optimeering.com/api/predictions/latest?series_id=" & Id
  7. Query the API, and convert the Json response: 
    Contnet = Web.Contents(Url, WebOptions),
JsonContent = Json.Document(Contnet)
  8. Update the returned value to JsonContent and click Done. You should once again see a single Record with a key items and a value of List
  9. Click on List, then Record, and then List on the events key
  10. Now convert the List of a single Record into a Table, by going to the Transform tab and clicking To Table
  11. Expand the Column, deselect created_at and is_simulated and Use original column name as prefix
  12. Expand the predictions column, select Expand to New Rows
  13. Expand the predictions column again, keeping all the new columns
  14. Expand the value column, keeping all the new columns, to see the quantile data
  15. Finally, right click on the event_time and prediction_for columns, under Change Type select Date/Time

Click on Close & Load to load the data into a table on a new sheet. We now have the latest Imbalance Price Spread Quantiles for NO3 predictions!

Step 6 - Refreshing the Data

Now that we have the Queries and Connections all defined, and data in our workbook, we can update the data by going to the Data tab and clicking the Refresh All button. Another useful option if you are leaving the workbook open is to set the Data Query to automatically refresh. To do this:

  1. From the Data tab click on Queries & Connections
  2. Right click on the Data query and go to Properties
  3. Select Refresh every x minutes`, setting the minutes to the value you want (such as 1min). Alternatively you can se it to automatically refresh when opening the workbook by selecting Refresh data when opening the file

Now you should have the basics to start building the workbooks and interfaces you need to consume Optimeerings Predictions in Excel. If you have any questions or suggestions remember you can reach out to us at https://help.optimeering.com.

Powered by the Engineering Team