Stock Prices ETL :TD Ameritrade API

I was think few days back about how BI could work with Stock Prediction.
To do it first I needed regular stock price data from the internet.
So to do it I wrote a quick code in Python to get stock prices from TD Ameritrade API.
So once I have the data feeding in regulary I will write the next post of prediction algorithms.


Here is the code:
SQL Table:
CREATE TABLE [dbo].[Stocks_Price](
[Symbol] [varchar](10) NOT NULL,
[Open] [float] NULL,
[High] [float] NULL,
[Low] [float] NULL,
[Close] [float] NULL,
[volume] [float] NULL,
[TransDate] [date] NOT NULL
)

The python code has a Start and End date which can be modfied the way you want.
The symbols for which we get prices for is in the file Symbols.txt.
Symbols.txt has only text as shown below for all the stocks
AAPL
AABA
TSLA

The list of all Symbols on Nasdaq can be found here
ftp://ftp.nasdaqtrader.com/symboldirectory/
nasdaqlisted.txt


You can run the following Python code and make changes for your APIKey, SQL Server adddress. SYmbols file path.
Python Code:
import requests
import json
import pyodbc
import datetime
import time

start_date =int(datetime.datetime.timestamp(datetime.datetime.strptime('07 20 2019  1:33PM', '%m %d %Y %I:%M%p')) * 1000)
end_date   =int(datetime.datetime.timestamp(datetime.datetime.strptime('08 02 2019  1:33PM', '%m %d %Y %I:%M%p')) * 1000)

file= open(r'''C:\Stocks\Symbols.txt''',"r")
   
for x in file:
    symbol_name= x.strip()   
    endpoint=r"https://api.tdameritrade.com/v1/marketdata/{}/pricehistory".format(symbol_name)
   
    #payload=   { 'apikey':'Your API KEY',
    #             'periodType':'year',
    #             'frequencyType':'daily'                     
    #            }

    payload=   { 'apikey':'Your API KEY',
                 'periodType':'year',
                 'frequencyType':'daily' ,
                 'startDate':start_date,
                 'endDate':end_date
                }
    print(symbol_name)
   
    content= requests.get(url=endpoint,params=payload)
    data=content.json() 

    sqlconn=pyodbc.connect('Driver={SQL Server};'
                          'Server=.;'
                          'Database=NishDB;'
                          'Trusted_Connection=yes;')
    cursor= sqlconn.cursor()

    for row in data['candles']:

        currenttime = datetime.datetime.fromtimestamp(row['datetime']/1000).strftime('%Y-%m-%d')

        cursor.execute('''Insert into NishDb.dbo.Stocks_Price(Symbol,[Open],High,Low,[Close],volume,TransDate) values(?,?,?,?,?,?,?)''',
                       (symbol_name,row['open'],row['high'],row['low'],row['close'],row['volume'],currenttime))
        sqlconn.commit()