def get_split_df(ticker, start_date=None, to_date=None, api_key=api_key):
"""
Return the DataFrame of split info
:param ticker: string
:param start_date: string date "YYYY-MM-DD"
:param to_date:
:param api_key:
:return: split DataFrame
"""
try:
if start_date is None:
start_date = ctt.get_past_date(_date=datetime.today(), years=25)
if to_date is None:
to_date = datetime.today()
r = requests.get(
f'https://finnhub.io/api/v1/stock/split?symbol={ticker}&from={start_date}&to={to_date}&token={api_key}')
time.sleep(2.1)
if len(r.json()) != 0:
return pd.DataFrame(r.json())# print(r.json())
else:
return None
except Exception as e:
with open(Path(__file__).parent / "../logs/finn_log.log", "a") as f:
f.write('"' + str(datetime.today()) +
'", "Something is wrong when executing the get_split_df", "' + str(e) + '"\n')
def get_candles_df(ticker,
candle_freq="D",
start_timestamp=None,
stop_timestamp=None,
finnhub_client=finnhub_client):
"""
Return stock candles DataFrame for the selected time range and frequency
:param ticker: string
:param candle_freq: D or 1
:param start_timestamp: int
:param stop_timestamp: int
:param finnhub_client: object
:return: Return None if no data
"""
try:
if start_timestamp is None:
start_timestamp = ctt.convert_datetime_timestamp(
ctt.get_past_date(_date=datetime.today(), years=25))
if stop_timestamp is None:
stop_timestamp = ctt.convert_datetime_timestamp(datetime.today())
finnhub_candles = finnhub_client.stock_candles(ticker, candle_freq,
start_timestamp,
stop_timestamp)
if finnhub_candles["s"] == "ok":
stock_candles = pd.DataFrame(finnhub_candles)
stock_candles["symbol"] = str(ticker)
stock_candles["date_int_key"] = \
stock_candles["t"].apply(ctt.convert_timestamp_datetime).apply(datetime.date)
stock_candles["timestamp"] = \
stock_candles["t"].apply(ctt.convert_timestamp_datetime).apply(datetime.time)
# stock_candles["exchange"] = str(exchange)
stock_candles.drop(columns=['s'], inplace=True)
stock_candles = stock_candles.reindex(columns=[
'symbol', "date_int_key", "timestamp", "o", "c", "h", "l", "v",
"t"
])
stock_candles.rename(columns={
"h": "high_price",
"c": "close_price",
"l": "low_price",
"o": "open_price",
"t": "finn_timestamp",
"v": "volume"
},
inplace=True)
else:
stock_candles = None
time.sleep(2.1)
return stock_candles
except Exception as e:
with open(Path(__file__).parent / "../logs/finn_log.log", "a") as f:
f.write(
'"' + str(datetime.today()) +
'", "Something is wrong when executing the get_candles_df", "'
+ str(e) + '"\n')
def load_candles(ticker,
table_name,
candle_freq,
timer,
last_timestamp,
_conn=conn):
"""
main function to load candles.
:param ticker:
:param table_name:
:param timer:
:param _conn:
:return:
"""
try:
isempty = 1
month_step = 120 if candle_freq == "D" else 1
last_close_price = None
# last_timestamp = sql_func.get_last_timestamp(ticker, table_name, conn)
if last_timestamp == None:
isfirstload = True
if candle_freq == 1:
start_timestamp = ctt.convert_datetime_timestamp(
ctt.get_past_date(_date=datetime.today(),
years=1)) # 1 years data
else:
start_timestamp = ctt.convert_datetime_timestamp(
ctt.get_past_date(_date=datetime.today(),
years=25)) # 25 years data
else:
isfirstload = False
start_timestamp = last_timestamp
if candle_freq == 1:
stop_timestamp = ctt.convert_datetime_timestamp(
datetime.today()) - 84600
else:
stop_timestamp = ctt.convert_datetime_timestamp(datetime.today())
if (start_timestamp + 86400 < stop_timestamp
and candle_freq == "D") or candle_freq == 1:
month_list = ctt.date_by_month_list(
ctt.convert_timestamp_datetime(start_timestamp),
ctt.convert_timestamp_datetime(stop_timestamp))
while month_list.next_timestamp_list(month=month_step) is not None:
timer.api_timer_handler(
) # Not work as intended, but left in for further dev
stock_candle = finn_func.get_candles_df(
ticker, candle_freq, month_list.timestamp_list[0],
month_list.timestamp_list[1])
if stock_candle is not None:
isempty = isempty and False
if isfirstload:
sql_func.insert_df_to_db(stock_candle, table_name)
isfirstload = False
else:
sql_func.insert_df_to_db(
stock_candle.drop(stock_candle.index[0]),
table_name)
if candle_freq != 1:
last_close_price = sql_func.get_symbol_close_price(
ticker, table_name)
if candle_freq == "D":
if last_close_price is not None:
db_last_close_price = last_close_price.iloc[0, 0]
api_last_close_price = stock_candle.iloc[
0, ]["close_price"]
issplit = False if api_last_close_price == db_last_close_price else True
sql_func.insert_df_to_db(
stock_candle.drop(stock_candle.index[0]),
table_name)
if issplit is True:
timer.api_timer_handler(
) # Not work as intended, but left in for further dev
split_df = finn_func.get_split_df(
ticker,
ctt.convert_timestamp_datetime(
month_list.timestamp_list[0]),
ctt.convert_timestamp_datetime(
month_list.timestamp_list[1]))
if split_df is not None:
sql_func.insert_df_to_db_iter(split_df)
else:
isempty = isempty and True
if isempty is True:
return "no data"
elif isempty is False:
return "Success"
else:
return "Error"
except Exception as e:
try:
error_sql = "insert into error_rec (error_time, error_info) values ('" \
+ str(datetime.today()).replace("'", "''") + \
"', 'Error when getting data with ticker [" \
+ str(ticker) + "]: " + str(e).replace("'", "''") + "')"
with conn.cursor() as cursor:
cursor.execute(error_sql)
return str(datetime.today()) \
+ " error occured when getting candles with '" \
+ str(ticker) + "', timestampfrom " + str(month_list.timestamp_list[0]) \
+ "timestampto " + str(month_list.timestamp_list[1]) + " error msg: " + str(e)
except Exception as e2:
print(
'"', datetime.today(),
'" , "Error when adding error message to database: ' +
str(e2) +
'", "' + 'Original Error when getting data with ticker \'',
str(ticker), '\': ', e, '"')
return '"' + str(datetime.today()) + \
'" , "Error when adding error message to database: ' + str(e2) + '", "' \
+ 'Original Error when getting data with ticker \'', str(ticker), '\': ' \
+ ' timestampfrom ' + str(month_list.timestamp_list[0]) \
+ "timestampto " + str(month_list.timestamp_list[1]) + " error msg: " + str(e) + '"'