Im a newbie with postgre, and the course I did focused on querying already existing databases, not creating a database from scratch.
Im aware that using csv tables might be the best way to go for smallish datasets, but as a "learn by doing" exercise, Im trying to set up my own database of basic end-of-day stock data, keeping it updated daily, and (later) adding new tables of indicators created by manipulating the basic downloaded data using python. Im stuck right at the start, with the schema, because this data seems to have two "indexes": the tickers and the dates.
For example, the basic data Ill download each day are: date, ticker, open, high, close, low, volume. Obviously there will be a few hundred tickers and maybe several thousand dates, but its for personal use and speed isnt really that important (unless were talking minutes of procesing time!) and my main goal is to learn how to set up a database properly and aftewards access and update it with python.
In pandas, this is quite easy, downloading one giant dataframe, indexed by date, with a multiidex on the columns, close/open/etc at level 0 and tickers at level 1. Also, easy to update this dataframe each day with a new multiindex pandas dataframe (or csv), with one very long row, date/(ticker,O/L/H etc).
But how do you break that dataframe down into tables to create a more useable database that can be queried? How would I update those (various) tables with the one pandas dataframe or csv?
What would be the best schema for the tables? Would it be a table for each ticker, indexed by date, with columns open, high, close, volume etc? Or a table for each date, indexed by ticker, with that dates open, high etc?
Or some other method?
Any suggestions, links or how-to courses/tutorials welcome. Thanks!
