I'm trying to download 0.5gb of data (5 years worth of OHLC from alpaca API). Here's how I'm doing it:
import config
from alpaca_trade_api.rest import REST, TimeFrame
import sqlite3
from sqlalchemy import create_engine
import pandas as pd
from IPython.display import display
import datetime
from dateutil.relativedelta import relativedelta
start_date = (datetime.datetime.now() - relativedelta(days=2)).date()
start_date = pd.Timestamp(start_date, tz='America/New_York').isoformat()
end_date = pd.Timestamp(datetime.datetime.now(), tz='America/New_York').isoformat()
connection = sqlite3.connect(config.db_file)
connection.row_factory = sqlite3.Row
cursor = connection.cursor()
cursor.execute("""
SELECT id, symbol, name, exchange FROM stock
""")
rows = cursor.fetchall()
symbols = []
stock_dict = {}
for row in rows:
symbol = row['symbol']
symbols.append(symbol)
stock_dict[symbol] = row['id']
print(len(symbols), 'len of symbols')
api = REST(config.api_key_id, config.api_secret, base_url=config.base_url)
for symbol in symbols:
barsets = api.get_bars(symbol, TimeFrame.Day, start_date, end_date)
barsets = list(barsets)
for bar in barsets:
stock_id = stock_dict[symbol]
print(f'adding daily bars for {symbol} between {start_date} and {end_date} to db')
cursor.execute("""
INSERT INTO daily_ohlc (stock_id, date, open, high, low, close, volume)
VALUES (?, ?, ?, ?, ?, ?, ?)
""", (stock_id, bar.t.date(), bar.o, bar.h, bar.l, bar.c, bar.v))
connection.commit()
But as the title suggests, it's taking FOREVER to download tiny bit of data. After 48 hours of trying to download these records, it less than 10% of them. What am I doing wrong?