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()
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()