How to deal with reaching maximum number of calls from an API

[Lag_2]

Hello,

Just so you know i'm new to power BI and power query en general, so i'm not comfortably yet with working with these tools and M code. I know VBA and python so i know at least a bit programming. I got the following problem.

I'm using the Alpha Vantage API to get the financial data from all the stocks of the S&P index (505 in total) , i created a query as a custom fuctions that makes the call to the API, for that function i'm using the stocks symbol as it's parameter. I have a table with all the 505 stocks symbol from which i call the custom function that in turn calls the API. My problem seems to be that as power bi tries to make as many calls as fast as it can, after around 5/6 calls i get an error from the API telling me i'm overdoing the calls. 

The Alpha Vantage documentations suggest to make less than one call per second, how can i tell my function to wait X amount of time between calls and in case of getting an error to wait Y amount of time and to try again. This basically error handling, but M code is kind of weird so i'm a bit lost.

This the code generated by query:

let
    Source = Json.Document(Web.Contents("https://www.alphavantage.co/query?function=TIME_SERIES_DAILY" & "&symbol=" & Symbol & "&outputsize=compact" & "&apikey=My API Key", [Timeout=#duration(0, 0, 0, 30)])),
    #"Time Series (Daily)" = Source[#"Time Series (Daily)"],
    #"Converted to Table" = Record.ToTable(#"Time Series (Daily)"),
    #"Expanded Value" = Table.ExpandRecordColumn(#"Converted to Table", "Value", {"1. open", "2. high", "3. low", "4. close", "5. volume"}, {"Value.1. open", "Value.2. high", "Value.3. low", "Value.4. close", "Value.5. volume"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Expanded Value",{{"Name", type date}}),
    #"Removed Columns" = Table.RemoveColumns(#"Changed Type",{"Value.1. open", "Value.3. low", "Value.5. volume"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Removed Columns",{{"Value.2. high", type number}, {"Value.4. close", type number}}),
    #"Renamed Columns" = Table.RenameColumns(#"Changed Type1",{{"Value.2. high", "High"}, {"Value.4. close", "Close"}, {"Name", "Date"}}),
    #"Added Custom" = Table.AddColumn(#"Renamed Columns", "Symbol", each Symbol),
    #"Reordered Columns" = Table.ReorderColumns(#"Added Custom",{"Symbol", "Date", "High", "Close"})
in
    #"Reordered Columns"

[v-frfei-msft · Accepted solution]

Hi  @Lag_2,

Please refer to this blog to add in a wait time between API calls.

https://www.mrexcel.com/forum/power-bi/863971-wait-time-between-api-calls-power-query.html

Regards,

Frank

[AlinaK]

Hi all,

can you advice please, I am using apiKey and trying to extract data, access is forbidden,

I used both ways:

Basic connection with only key and WEB api, where i added key to the end of URL,

What can be the reason of this error?

Thanks