Import stock data from Alpha Vantage into SQL database

Apr 9, 2022
News
Pavlo

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

Data Engineering

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

Data Engineering

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

Save To Database

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
Then press `Upsert(merge) Data` button that will create a new SQL table and will load all data into the SQL database.

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