Import OData feeds into Excel

Integrations

OData is a widely supported protocol, which can be easily loaded into Excel (without any code). Once your data is loaded into Excel, all the features of the spreadsheet application can be used for easy data analysis. To obtain the settings required, navigate to https://fred.au/sda and complete the online form, requesting In Pharmacy Use > Microsoft Excel.

You must use Microsoft Excel 2016 (or above).

  1. Open Microsoft Excel 2016.
  2. Open a new blank workbook.
  3. On the Data tab, select New Query > From Other Sources > From OData Feed.

    The OData Feed dialog is displayed.

  4. Enter the URL for the required OData feed (including the Subscription Key), then click OK.

    For example: https://api.fred.com.au/excelexport/data/TaxOnItems?subscription-key= Subscription Key string

    First time users are prompted for their credentials.

  5. In the Data Source Settings dialog, click the Basic tab.

  6. Enter your Username and Password, then click Save.

    The data you have requested in displayed in the Query Editor window. It is read-only.

  7. In the Query Editor, modify the data display as required:
    • Apply Filters
    • Remove Columns

    8. When the data display as required, click Close & Load to load the data in Microsoft Excel 2016.

    You can save this spreadsheet locally and continue with your data analysis.

    If you need to return to he Query Editor after loading your data in Excel, select the Query Tools tab, then click Edit.

If the prompt requesting your user credentials does not display (on first login) or your attempt to connect returns an error, you may want to check, then edit your credentials.

  1. In Microsoft Excel, select the Data tab.
  2. Click New Query > Data Source Settings.

  3. In the Data Source Settings dialog, highlight the Fred API URL, then click Edit.

  4. In the new Data Source Settings dialog, click Edit.

    The Access an OData feed dialog is displayed.

  5. Click the Basic tab, then enter (or correct) your user credentials.
  6. Click Save when done.

Related Topics