Retrieving data from Upodi to excel

This is a guide to retrieve the data directly from the Upodi database using a Report Key and a data source format called Open-Data protocol (usually referred to as Odata). This step-by-step guide explains how to import it to excel, but a similar procedure may exist for other similar data-readers than just excel. The documentation for the OData feed can be found here.

Step 1: Create a report key or use an existing one

Find your developer section in Upodi and open the API Key section. Create a new API key and make sure to click 'Reporting Key'

Copy the Access Key from this Reporting Key and save it for the next step.

Step 2: Import OData to excel

Step 3: Choose which data you want to extract

Paste this URL https://reporting.upodi.io/odata/ into the URL field and click OK

Step 6: Authentication dialogue

After this step an authentication dialogue appears. Use the following settings:

Basic authentication
User name: leave blank
Password: Reporting key from earlier step

Step 5: Choose which data to import

If you need several tables at once click Select multiple items.

Be careful not to choose more than you need. This can download a lot of data to your excel, and your excel may not be able to handle all that data if you overload it.

Step 6: Merge, edit and filter

Now you have the option to merge tables - in this example the customer table with the invoice table.

You can also edit and manipulate the data in PowerQuery before opening it in excel, if you are familiar with this functionality to filter, merge or apply custom logic.

Step 7: Load to excel sheet

Right click the connection you want to load to a sheet and click Load to.

In the following dialog choose where you want to load it to. The default option will load it to the current table.

Step 8: Dynamic refreshing of data

Any time you want you can always click Data > Refresh all, which will make use of the connection and download the most recent data automatically.

❗️

Stay Secure

If you are ever in doubt that the open-data protocol may have been leaked or compromised in any way, you can always delete the report key to invoke the access. It can be a good precaution to delete and create a new report key once in a while.