alphavantage | A simple interface to the Alpha Vantage API | Runtime Evironment library

This is because you are unmarshalling TimeSeriesDaily into a map and maps are unordered, just like JSON objects.
So, each time you loop over TimeSeriesDaily you will get a different value.

You can solve this by the following code,

As you need Time Series (Daily) values only. So you can use directly when you create dataframe

You have made the Date the primary key meaning its value must be unique for all rows but you are attempting to add a new row with a date that already exists, hence the error.

DROP the table (DROP TABLE XXX;) and recreate it without Date as the PK - If you want a PK then add an AUTOINCREMENT column.

Your main issue, I think, is that you think you're working with an Array but you're not.

The data loaded into dailyLong is an object. I believe you're getting confused because the keys in dailyLong need to be accessed, as you noted, via bracket notation [] due to the spaces (Time Series (Daily)) and special characters (2021-04-12). You can read more about property accessors here.

Based on your code, I have to assume that you have an array of the dates you're trying to search, dateMin, and this is what you're trying to loop through to get the daily closing prices (so that is an array).

It's not entirely clear what you're trying to do with the second line, but if you're just trying to loop through all the closing prices, just remove the + signs from the assignment (see code snippet below).

Your first line is correct, because it access the object using bracket notation instead of dot notation. Just change the 1 to i. The second line is incorrect (for what you're trying to accomplish), as shown below:

There are 2 things that could be happening here:

1. You are not awaiting something that you should be
2. The URLs you're calling are bad requests, and the event loop is erroring before you get a chance to see what's really going on.

According to the aiohttp documentation

By default aiohttp uses strict checks for HTTPS protocol. Certification checks can be relaxed by setting ssl to False

So it's likely that the urls that you're using are hitting this threshold, and you're getting kicked out. Change the line:

I don't believe Alpha Vantage has intraday data for all foreign stocks. They have daily for some though, the following call worked for me:

Pandas does have a json_normalize method to do that. Or, the other option is you can construct the dataframe from json (dictionaries) as long as each dictionary is within a list, where each row in your table is represented by each element in the list.

Also note, you can use .json() on the request to store right away. No need to write to file localay and then convert to csv.

Seems like TIME_SERIES_INTRADAY_EXTENDED can return only CSV format, but the alpha_vantage wrapper applies JSON methods, which results in the error.

My workaround:

I prefer to work with application-specific types when dealing with JSON in Ballerina. You can use quoted identifiers in Ballerina to map the complete json payload into an application-specify type. I've used a query expression to filter out stack data entries into an array. There are other ways with slight variations to achieve the same thing.

Note that I've used Ballerina Swan Lake Alpha 3 to test this code.