Importing Stock Data into the SQL database

av_raw_data.json

{
  "Meta Data": {
    "1. Information": "Daily Prices (open, high, low, close) and Volumes",
    "2. Symbol": "IBM",
    "3. Last Refreshed": "2022-04-08",
    "4. Output Size": "Compact",
    "5. Time Zone": "US/Eastern"
  },
  "Time Series (Daily)": {
    "2022-04-08": {
      "1. open": "128.0100",
      "2. high": "128.7800",
      "3. low": "127.2700",
      "4. close": "127.7300",
      "5. volume": "3143309"
    },
    "2022-04-07": {
      "1. open": "128.8700",
      "2. high": "129.2499",
      "3. low": "126.7300",
      "4. close": "128.5500",
      "5. volume": "3538317"
    }
   
  }
}

etl.jspy.py

from "sql-data-api" import sqlDataApi, httpGet
from 'datapipe-js/utils' import parseNumberOrNull

symbols = ["IBM", "MSFT"]

BASE_URL = "https://www.alphavantage.co/query"
TIMESERIES = "function=TIME_SERIES_DAILY"
API_KEY = "demo"

async def getTimeseries(symbol):
  data = httpGet(BASE_URL + "?" + TIMESERIES + "&symbol="+symbol+"&apikey=" + API_KEY)

  timeSeries = data["Time Series (Daily)"]

  if timeSeries == null:
    raise Error('Connection error. Check your API key and limits')
  
  return Object
    .keys(timeSeries)
    .map(date => 
      r = timeSeries[date]
      return {
        symbol, date,
        open: parseNumberOrNull(r["1. open"]),
        high: parseNumberOrNull(r["2. high"]),
        low: parseNumberOrNull(r["3. low"]),
        close: parseNumberOrNull(r["4. close"]),
        volume: parseNumberOrNull(r["5. volume"])
      }
    )

stockData = []
for symbol in symbols:
  stockData = stockData.concat(getTimeseries(symbol))

# return stockData

return sqlDataApi("SQL-Shared")
  .save("test1.AVStockData", stockData)

get_raw_data.jspy.py

timeSeries = data["Time Series (Daily)"]
 
  if timeSeries == null:
    raise Error('Connection error. Check your API key and limits')
 
  return Object
    .keys(timeSeries)
    .map(date =>
      r = timeSeries[date]
      return {
        symbol, date,
        open: parseNumberOrNull(r["1. open"]),
        high: parseNumberOrNull(r["2. high"]),
        low: parseNumberOrNull(r["3. low"]),
        close: parseNumberOrNull(r["4. close"]),
        volume: parseNumberOrNull(r["5. volume"])
      }
    )