It looks like you're new here. If you want to get involved, click one of these buttons!
from kiteconnect import KiteConnect
import os
import datetime as dt
import pandas as pd
import mysql.connector
from pandas import DataFrame
cwd = os.chdir("C:\\Users\\Administrator\\Desktop\\Zerodha")
#generate trading session
access_token = open("access_token.txt",'r').read()
key_secret = open("credentials.txt",'r').read().split()
kite = KiteConnect(api_key=key_secret[0])
kite.set_access_token(access_token)
#get dump of all NSE instruments
instrument_dump = kite.instruments("NSE")
instrument_df = pd.DataFrame(instrument_dump)
#instrument_df.to_csv("NSE_Instruments_31122019.csv",index=False)
# Connect to the top 100 stocks which are stored in the Database and fetch a list of them
db = mysql.connector.connect(host="localhost", user="abc", passwd="abcd")
pointer = db.cursor()
pointer.execute("use stock")
pointer.execute("select * FROM equity")
ticker = pointer.fetchall()
ticker = pd.DataFrame(ticker, columns =['SYMBOL', 'NAME OF COMPANY', 'SERIES', 'Listing_date', ' PAID UP VALUE', ' MARKET LOT', ' ISIN NUMBER', ' FACE VALUE'])
tickers = ticker["SYMBOL"]
#print (tickers)
def instrumentLookup(instrument_df,symbol):
"""Looks up instrument token for a given script from instrument dump"""
try:
return instrument_df[instrument_df.tradingsymbol==symbol].instrument_token.values[0]
except:
return -1
def fetchOHLCExtended(ticker,inception_date, interval):
"""extracts historical data and outputs in the form of dataframe
inception date string format - dd-mm-yyyy"""
instrument = instrumentLookup(instrument_df,ticker)
from_date = dt.datetime.strptime(inception_date, '%d-%m-%Y')
to_date = dt.date.today()
data = pd.DataFrame(columns=['date', 'open', 'high', 'low', 'close', 'volume'])
while True:
if from_date.date() >= (dt.date.today() - dt.timedelta(100)):
data = data.append(pd.DataFrame(kite.historical_data(instrument,from_date,dt.date.today(),interval)),ignore_index=True)
break
else:
to_date = from_date + dt.timedelta(100)
data = data.append(pd.DataFrame(kite.historical_data(instrument,from_date,to_date,interval)),ignore_index=True)
from_date = to_date
data.set_index("date",inplace=True)
return data
ohlc = fetchOHLCExtended(tickers,"20-08-2019","5minute")
https://kite.trade/docs/connect/v3/market-quotes/#retrieving-the-full-instrument-list
But the file which is downloaded contains forex data I need it for BSE or for NSE
Did you download the whole instruments list or instruments list for exchange CDS?
"https://api.kite.trade/instruments"
After this the file automatically got downloaded
1. Reliance:738561
2. ACC:5633
The document which is accessed from the above URL contains only the instrument tokens not the companies they represent
Just run this code and you'll get the exact idea what to do next
This code will return you name:token of the all the symbols in NSE
just like you asked
RELIANCE
Happy trading!
for instrument in kite.instruments("NSE"):
----print(instruement['name']:instrument['instrument_token'])
it's better to save the insturments Name, TradingSymbol, Token etc to your Database so you can fetch it easily.
I Ran the code as you asked but it is giving me an error as shown:
I just ran the two lines you gave me so as to debug but it is giving me an error as shown
Thanks.
But why is mine not working?
Here is my code:
from kiteconnect import KiteConnect
import pandas as pd
import os
cwd = os.chdir("C:\\Users\\Administrator\\Desktop\\Zerodha")
#generate trading session
access_token = open("access_token.txt",'r').read()
key_secret = open("credentials.txt",'r').read().split()
kite = KiteConnect(api_key=key_secret[0])
kite.set_access_token(access_token)
for instrument in kite.instruments("NSE"):
print(instruement['name']:instrument['instrument_token'])
The above code will fetch data of all the symbols in NSE
and it will store the data of each symbol in individual csv file
You can store it to your SQL DataBase if you want
I made it simple so everybody can understand.
Not a problem!
and you got a error for this code because I forgot to add the ","
paste the below code and it'll run just fine
I tried using your solution but there were too many files downloaded. So I created an excel file for all the NSE Stocks with their corresponding instrument ID. I try to import all the instrument in a pandas DF and try to download the ohlcv data using the kite.historical_data function but am getting an error as shown:
PFA my code:
df = pd.read_excel("C:\\Users\Administrator\\Desktop\\Zerodha\\DOCS\\NSE_Instruments_31122019.xlsx")
tickers = (df['instrument_token'])
for instrument_token in tickers:
to_date = datetime.now()
from_date=to_date-timedelta(days=5)
interval = "60 Minutes"
records = kite.historical_data(tickers, from_date, to_date, interval)
data =pd.DataFrame(records)
# -*- coding: utf-8 -*-
"""
Zerodha Kite Connect - Storing tick level data in db
"""
from kiteconnect import KiteTicker, KiteConnect
import datetime
import sys
import pandas as pd
import os
import sqlite3
cwd = os.chdir("F:\\Deepakfirst")
#generate trading session
access_token = open("access_token.txt",'r').read()
key_secret = open("api_key.txt",'r').read().split()
kite = KiteConnect(api_key=key_secret[0])
kite.set_access_token(access_token)
db = sqlite3.connect("F://Streaming_data/ticks.db")
def create_tables(tokens):
c=db.cursor()
for i in tokens:
c.execute("CREATE TABLE IF NOT EXISTS TOKEN{} (ts datetime primary key,price real(15,5), volume integer)".format(i))
try:
db.commit()
except:
db.rollback()
def insert_ticks(ticks):
c=db.cursor()
for tick in ticks:
try:
tok = "TOKEN"+str(tick['instrument_token'])
vals = [tick['timestamp'],tick['last_price'], tick['volume']]
query = "INSERT INTO {}(ts,price,volume) VALUES (?,?,?)".format(tok)
c.execute(query,vals)
except:
pass
try:
db.commit()
except:
db.rollback()
#get dump of all NSE instruments
instrument_dump = kite.instruments("NSE")
instrument_df = pd.DataFrame(instrument_dump)
def tokenLookup(instrument_df,symbol_list):
"""Looks up instrument token for a given script from instrument dump"""
token_list = []
for symbol in symbol_list:
token_list.append(int(instrument_df[instrument_df.tradingsymbol==symbol].instrument_token.values[0]))
return token_list
#####################update ticker list######################################
tickers = ["ZEEL","WIPRO","VEDL","ULTRACEMCO","UPL","TITAN","TECHM","TATASTEEL",
"TATAMOTORS","TCS","SUNPHARMA","SBIN","SHREECEM","RELIANCE","POWERGRID",
"ONGC","NESTLEIND","NTPC","MARUTI","M&M","LT","KOTAKBANK","JSWSTEEL","INFY",
"INDUSINDBK","IOC","ITC","ICICIBANK","HDFC","HINDUNILVR","HINDALCO",
"HEROMOTOCO","HDFCBANK","HCLTECH","GRASIM","GAIL","EICHERMOT","DRREDDY",
"COALINDIA","CIPLA","BRITANNIA","INFRATEL","BHARTIARTL","BPCL","BAJAJFINSV",
"BAJFINANCE","BAJAJ-AUTO","AXISBANK","ASIANPAINT","ADANIPORTS"]
#############################################################################
#create KiteTicker object
kws = KiteTicker(key_secret[0],kite.access_token)
tokens = tokenLookup(instrument_df,tickers)
#create table
create_tables(tokens)
def on_ticks(ws,ticks):
insert_tick=insert_ticks(ticks)
print(ticks)
def on_connect(ws,response):
ws.subscribe(tokens)
ws.set_mode(ws.MODE_FULL,tokens)
while True:
now = datetime.datetime.now()
if (now.hour >= 9 and now.minute >= 15 ):
kws.on_ticks=on_ticks
kws.on_connect=on_connect
kws.connect()
if (now.hour >= 15 and now.minute >= 30):
sys.exit()
db.close()
"""
c.execute('SELECT name from sqlite_master where type= "table"')
c.fetchall()
c.execute('''PRAGMA table_info(TOKEN975873)''')
c.fetchall()
for m in c.execute('''SELECT * FROM TOKEN975873'''):
print(m)
"""