Save downloaded API data to minimize API calls

[bigk]

Hello

I'm downloading financial data using API calls via powerquery. and then analyzing it in PowerBI. Unfortunately there is a limit for 100 calls per day. Each company requires 5 calls so i could download 20 companies per day. Considering financials are updated once per querter, i could automate download of 1500-1800 companies which is more than enough for me. I'm looking for a way to download and save the data so that companies are not fetched again untill new financial statements are released. My final target is to use the data in PowerBI. 

I don't know any programming language but can learn something using instructions. I would prefer to use PQ or PBI or any other microsoft solutions for this. 

Does anyone have any ideas how to build the queries so that i could download data permanently until i decide that it should be refreshed again.

Thanks.

[PwerQueryKees · Accepted solution]

I have a hammer and everything looks like a nail. And my hammer is called Excel.

With excel I would folow these steps:

• create a Power Query "API Data" to get the API data
• Load the result into an excel table which creates a table named API_Data
• Create a query "Collected Data" on the excel table API_Data
• And now the tricky bit: 
  • Load the "Collected Data" query ALSO to a table in Excel to get the excel table Collected_Data
  • Change the Colledted_data to use the table Collected_Data
  • Yes! It is self referencing
  • Remove the excel table API Data.
• Finaly Change "Collected Data" query to combine it with "API Data"
  
  • Change "API Data" to only return data that does not alredy exist in "Collected Data"
  • Change "Collected Data" to do an "Append Queries" of "API Data"

You may think this reults in a circular reference, but it doesn't. This is because the Excel Table is NOT the Power Query...

Have fun! I don't know if you want to automate the refresh, but that would be tricky. I gues it could be done with PowerShell and VBA, but I leave that exercise to you 😁

[bigk]

Hi

Thanks everyone for their ideas. @PwerQueryKees idea was most suitable for me and did not require any extra learning. 

One consideration is that autosave option should be disabled during data refresh because onedrive is constantly locking the file. Otherwise it worked well.

[dk_dk]

Hi @bigk ,

I would use Power Automate for this.

You would need a source file that contains the companies / parameters needed for the API call, and some sort of index or marker column to iterate on.

Then create a power automate that iterates through the source file, 20 companies (x5 calls) at a time, performs api calls and stores the results in a storage option you prefer. (I have only ever done this with an output to an excel file on sharepoint, but you can do much more elegant solutions nowadays). Then you schedule your Power Automate to run every day, and over time you will grab data for all your companies.

You will just connect to the database / storage of your choice from Power BI to build a report on it.

It's been a while since I used Power Automate so I can't show you a specific example, but if you are having trouble with it I would encourage you to re-post this question in the Power Automate Community.

Good luck!

Best,

Daniel

[lbendlin]

I'm downloading financial data using API calls via powerquery.

Instead, do it via Powershell and store the JSON results in a sharepoint or similar.  Then point Power Query to that folder.