Learn how to insert free minutely crypto price data into PostgreSQL using SQLAlchemy, Python, and Pandas.
You download the code at the Analyzing Alpha GitHub Repo.
Steps to Insert the Crypto Price Data
Below is the Python code demonstrated in the video and Jupyter notebook.
1. Get Symbols
We get the unique index values for the ticker component of the MultiIndex.
tickers = bars1m.index.get_level_values(1).unique()
tickersWe can then filter active tickers by assuming that an active ticker would have traded on the most recent day.
active_tickers = bars1m['2020-12-30':].index.get_level_values(1).unique()
active_tickers2. Create Symbols Dataframe
We then create a dataframe for our symbol class using np.where to set active and inactive tickers.
symbols = pd.DataFrame(tickers)
symbols['name'] = symbols['ticker']
symbols['market'] = 'crypto'
symbols['active'] = np.where(symbols['ticker'].isin(active_tickers), True, False)
symbols = symbols.sort_values(by='ticker')
symbols3. Create MinuteBar DataFrame
We reorganize the dataframe to simplify uploading a ticker and its associated minute data.
minute_bars = bars1m.reset_index().sort_values(by=['ticker','date']).set_index(['ticker','date'])
minute_bars4. Insert Data
We then insert the data, making sure to commit the symbol to get an id for the minute bar insertion.
from psql import db, session
from models import Symbol, MinuteBarfor i, r in symbols.iterrows():
symbol = Symbol(ticker=r['ticker'],
name=r['name'],
market=r['market'],
active=r['active'])
session.add(symbol)
session.commit()
bars = minute_bars.xs(r['ticker']).reset_index()
bars['symbol_id'] = symbol.id
session.bulk_insert_mappings(MinuteBar,
bars.to_dict(orient='records'))
session.commit()