def clean_tickers():
raw_tickers = pd.read_csv(
DIR_ENV["root"] + "/db_creation/db_update/tickers.txt",
sep="\t",
lineterminator="\n",
header=None,
)
raw_tickers.columns = [
"Symbol",
"Security",
"SEC filings",
"GICS Sector",
"GICS Sub-Industry",
"Headquarters Location",
"Date first added",
"CIK",
"Founded",
]
tickers_db = db.query_db("Select * from product;")
tickers_not_in_db = list(set(raw_tickers.Symbol) - set(tickers_db.symbol))
discarded_tickers = tickers_not_in_db + [
'NVR', 'COO', 'BIO', 'HII', 'TFX', 'RE', 'MTD', 'ABMD', 'POOL', 'TDY'
]
ticker_list = raw_tickers[[
a not in discarded_tickers for a in raw_tickers.Symbol
]][["Symbol", "Security"]]
ticker_list.to_csv(DIR_ENV["root"] +
"/db_creation/db_update/tickers_clean.txt",
index=False)
def format_stooq_to_db(df, product_symbol):
"""
Adapt the format returned by alphavantage daily adjusted to DB insert format
:param df:
:param product_symbol:
:return:
"""
# Convert to timestamp
df["ts"] = df["<DATE>"].apply(str) + df["<TIME>"].apply(str)
df["ts"] = df.ts.apply(pd.Timestamp)
# Load the product ID:
product_id = db.query_db(
"SELECT product_id FROM product WHERE symbol = '{}'".format(
product_symbol))
# Create the new df:
if len(product_id) != 0:
new_df = {
"time_st": pd.to_datetime(df.ts),
"product_id": [product_id.iloc[0, 0]] * df.shape[0],
"open": df['<OPEN>'],
"high": df['<HIGH>'],
"low": df["<LOW>"],
"close": df["<CLOSE>"],
"volume": df["<VOL>"]
}
return pd.DataFrame(new_df)
else:
# PENDENT AFEGIR codi que afegeixi el producte a la taula product
return None
def format_alphavantage_to_db_intraday(df, product_symbol):
"""
Adapt the format returned by alphavantage intraday adjusted to DB insert format
:param df:
:param product_symbol:
:return:
"""
product_id = db.query_db("SELECT product_id FROM product WHERE symbol = '{}'".format(product_symbol))
new_df = {"time_st": pd.to_datetime(df.timestamp),
"product_id": [product_id.iloc[0, 0]] * df.shape[0],
"open": df.open,
"high": df.high,
"low": df.low,
"close": df.close,
"volume": df.volume}
return pd.DataFrame(new_df)
from goldenswan.utils.alphavantage import (
request_api_data,
format_alphavantage_to_db_daily,
)
# Insert NASDAQ market -------------------------------------------------------------------------------------------------
market = pd.DataFrame([["NASDAQ", "USA", "NORTH_AMERICA", "GMT-4", -4]])
market.columns = "name country region timezone timediff".split()
db.bulk_insert_df("market", market)
# Insert NYSE market ---------------------------------------------------------------------------------------------------
market = pd.DataFrame([["NYSE", "USA", "NORTH_AMERICA", "GMT-4", -4]])
market.columns = "name country region timezone timediff".split()
db.bulk_insert_df("market", market)
db_market = db.query_db("SELECT * FROM Market")
# Insert AMAZON product ------------------------------------------------------------------------------------------------
product = pd.DataFrame([[
"AMZN", "AMAZON", "STOCKS",
db_market[db_market["name"] == "NASDAQ"]["market_id"].values[0]
]])
product.columns = "symbol name product_type market_id".split()
db.bulk_insert_df("product", product)
# Insert AMAZON daily
ticker = "AMZN"
daily = request_api_data(symbol=ticker,
interval_type="TIME_SERIES_DAILY_ADJUSTED",
outputsize="full")
daily = format_alphavantage_to_db_daily(daily, ticker)
import pandas as pd
import numpy as np
import goldenswan.utils.database as db
market = "NASDAQ"
tickers = ["BABA", "AMZN"]
table = "daily"
aa = db.query_db("SELECT * FROM PRODUCT")
print(aa)
bb = db.query_db("SELECT * FROM MARKET")
print(bb)
raw_data = db.read_data(tickers, market, table)
def create_investment_opportuniy(raw_data, day):
"""
Create an investment opportunity, generating a context (X vars) and the
reward over distinct time intervals (Y vars)
Args:
raw_data ([type]): [description]
day ([type]): [description]
"""