Import stock data from Alpha Vantage into SQL database
In this post, we will go over a tutorial guide on how you can use WORKSHEETS Data Studio to import historical stock data from Alpha Vantage API into the SQL database. We will cover 4 main topics
- How to use Alpha Vantage API to pull historical data
- Transform raw data into a readable data format, ready to be stored into the database or exported into the Excel
- Create an SQL table and load stock data in the table
- Setup a Scheduled Task to run this daily
We will use WORKSHEETS Data Studio to cover all these topics above.
How to use Alpha Vantage API to pull historical data
Alpha Vantage provides enterprise-grade financial market data through a set of powerful and developer-friendly APIs. From traditional asset classes (e.g., stocks and ETFs) to economic signals, from foreign exchange rates to cryptocurrencies, from fundamental data to technical indicators, Alpha Vantage is your one-stop-shop for real-time and historical global market data delivered through RESTful stock APIs
If you explore Alpha Vantage API documentation. You
will find out, that you can pull historical timeseries stock data with HTTP GET call:
https://www.alphavantage.co/query?function=TIME_SERIES_DAILY&symbol=IBM&apikey=demo
Create a new App/Project in WORKSHEETS Data Studio and add a new JSPython file and use httpGet method for it. Then,
press the RUN button to get the following results
As you can see the output format is not necessarily the most convenient format that can be easily consumed by popular tools like Excel
Transform raw data into a readable data format
Next, we will show how you can transform a raw Alpha Vantage stock data into a more readable format, which can be stored in the SQL data table or imported into the Excel
This JSPython script transformed Alpha Vantage historical stock data into a more readable format, which can be exported into the Excel or SQL Database table
Create SQL table and load stock data in the table
Before you can load data into the database, you have to have an existing database connection in your Worksheet Systems account. Here is more info on how to do it
Then, on the top right corner of the result panel, there is a menu item `Save To Database`.Click it to open a Save To Database dialog. Where you have to define:
- Table Name (make sure `Create New Table` checkbox is ON)
- Set Primary Keys. In this particular case, We will make a `date` and `symbol` a primary keys
Setup a Scheduled Task to run this daily
And last but not least step is to set up a Scheduled Task that will run this JSPython script daily. This step will ensure your table is updated regularly and you have always up-to-date stock prices for your symbols. It is very easy to do with WORKSHEETS Data Studio. All you have to do is press `Schedule Task` set a time you want to run and press `Save` button.
Before doing this, you have to make sure your scripts can work without any user interactions. So, we have to write a JSPython script that will save historical data. Here is example:
# stockData is an array of stock data items sqlDataApi("SQL-Shared") .save("test1.AVStockData", stockData)
Also, you have to grant permissions to the `ScheduledTasksRunner` user
And finally...
Building robust and scalable data processing pipelines is always a challenging task and requires some level of expertise. WORKSHEETS Data Studio makes it much easier for data engineers, analysts, and developers to build and design sophisticated pipelines. Please feel free to contact us for any help, or assistance. We are always ready to help
Working example
A working example can be found here
https://run.worksheet.systems/data-studio/...file=alphavantage-lib.jspy