Home > Net >  Store "Alpaca Markets" historical data timestamp to regular datetime field in sqlite datab
Store "Alpaca Markets" historical data timestamp to regular datetime field in sqlite datab

Time:01-06

Alpaca returns the four_hourly_bars historical bar data as in the following format;

data={'AMD': [{   'close': 63.76,
    'high': 63.76,
    'low': 62.7,
    'open': 62.94,
    'symbol': 'AMD',
    'timestamp': datetime.datetime(2022, 10, 3, 8, 0, tzinfo=datetime.timezone.utc),
    'trade_count': 2344.0,
    'volume': 151923.0,
    'vwap': 63.328798}, {   'close': 65.655,
    'high': 66.3501,
    'low': 63.39,
    'open': 63.74,
    'symbol': 'AMD',
    'timestamp': datetime.datetime(2022, 10, 3, 12, 0, tzinfo=datetime.timezone.utc),
    'trade_count': 217416.0,
    'volume': 42176972.0,
    'vwap': 65.356385},...]}

My main issue is with the timestamp field, I used to store it as datetime using the old alpaca_trade_api. But now with the new alpaca-py library the timestamp has additional information as shown above. The code I used to use (after fixing the new labels) is:

# Downloading 4H time-frame data (one by one)...
for symbol in tqdm(symbols, desc='Downloading 4H Data'):
    request_parameters = StockBarsRequest(symbol_or_symbols=symbol, timeframe=TimeFrame(4, TimeFrameUnit.Hour),
                                          start=datetime.strptime(One_Year_from_Today, '%Y-%m-%d'),
                                          end=None, adjustment='raw')
    four_hourly_bars = client.get_stock_bars(request_parameters)

    for bar in four_hourly_bars:
        stock_id = symbol_dic[symbol]
        cursor.execute("""INSERT INTO alpaca_stock_prices_4H (stock_id, date, open, high, low, close, volume) VALUES
        (?, ?, ?, ?, ?, ?, ?)""", (stock_id, bar.timestamp.tz_localize(None).isoformat(), bar.open, bar.high,
                                   bar.low, bar.close, bar.volume))

Now I get this error: AttributeError: 'tuple' object has no attribute 'timestamp'

How can I fix that? How to read datetime string values from 'timestamp': datetime.datetime(2022, 10, 3, 12, 0, tzinfo=datetime.timezone.utc) and store it in the sqlite date column in the database?

I did change the old "t", "o", "h", "l", "c", "v" to its appropriate new names in the new library but I still can't read the datetime right.

CodePudding user response:

I now know the solution to my problem, I was missing [symbol] in the for loop: `# Downloading 4H time-frame data (one by one)... for symbol in tqdm(symbols, desc='Downloading 4H Data'): request_parameters = StockBarsRequest(symbol_or_symbols=symbol, timeframe=TimeFrame(4, TimeFrameUnit.Hour), start=datetime.strptime(One_Year_from_Today, '%Y-%m-%d'), end=None, adjustment='raw') four_hourly_bars = client.get_stock_bars(request_parameters)

for bar in four_hourly_bars[symbol]:
    stock_id = symbol_dic[symbol]
    cursor.execute("""INSERT INTO alpaca_stock_prices_4H (stock_id, date, open, high, low, close, volume) VALUES
    (?, ?, ?, ?, ?, ?, ?)""", (stock_id, bar.timestamp, bar.open, bar.high,
                               bar.low, bar.close, bar.volume))`

a new issue has evolved though regarding the .tz_localize(None).isoformat() it produces the error AttributeError: 'datetime.datetime' object has no attribute 'tz_localize'... any ideas how to fix it?

  • Related