Hello dear Jedox beginners,
In this post, we will take an unusual path and instead of connecting to usual business tools, we will try to extract the commodity and stock data. To do that we will connect to free API at https://www.alphavantage.co/.
Alpha Vantage is a financial data provider that offers a wide range of financial market data and services. It provides access to historical and real-time market data for various asset classes, including stocks, cryptocurrencies, forex (foreign exchange), and more. At this moment we will focus on commodity data.
The connection will be Rest type and the extract will be from JSON.
Technical details for commodities API
Resource is: https://www.alphavantage.co/query?function=${Commodity}&interval=daily&apikey=${API} where API Parameters are
❚ Required: function
The function of your choice. In this case, function=BRENT which is crude oil. A list of the commodities will come with the model
❚ Optional: interval
By default, interval=monthly. Strings daily, weekly, and monthly are accepted.
❚ Optional: datatype
By default, datatype=json. Strings json and csv are accepted with the following specifications: json returns the time series in JSON format; csv returns the time series as a CSV (comma-separated value) file.
❚ Required: apikey
Your API key. Claim your free API key here.
The HTTP method will be GET.
The output of the API endpoint will look like this:
This way would load only one commodity at a time but by creating a looping job you could go through all of them in one shot. That is why we created one variable called {Commodity}
If you are not familiar with a loop job check out this link but in simple words, commodities from dimension extract would go through the loop and after all data for one commodity is done then the next one starts.
Data can be stored in a simple cube which contains days, commodities and measures as dimensions.
Now, it is on your creativity to build a nice report or dashboard where your commodities data will be presented.
PRO TIP: To update the commodities value every day, simply schedule the job 🙂
This is our example:
A small project sample can be downloaded from here.