I am getting my data from Binance in two forms. I am first getting historical data and creating a dataframe in pandas, and then using sqlite3 to create a database so I can call on this live updated DB from another script.
Then I am using binance websockets to get data in 1 minute intervals. I want to use technical indicators like moving averages, and I have easily calculated them for the historical data.
frame['SMA7'] = frame['Close'].rolling(7).mean()
frame['SMA25'] = frame['Close'].rolling(25).mean()
However, the live data comes in one price point at a time, and I can't figure out how to append the data, SMAs included, to the dataframe without calling the DB over and over to fetch the old price data and recalculate the moving average. Is there a methodology to this kind of database update where the new row is partially dependent on the previous data?
The excel equivalent would be to have the formulas pre-populated in the table so that when the data lands the output is generated. Three possibilities I have considered:
- Retrieve old data from db - calculate values and the append the row.
- Maintain the DB and a dataframe simultaneously, and when the new price point comes in, append it to the dataframe, calculate the values, and then append the last row of the dataframe to the database.
- Recall the data entirely, rewrite the db as if it were new.