Local Sql Lite DB

[vepak]

Hi, first of all what you did with this repo is really impressive. It will help lot of people to research & backtesting on stock market.

I'm trying to create charts using data from local db. I've tick level data in sqllite db. Trying to understand how to provide that as input to add_data

Currently I'm working on pandas dataframes to analyze, so this is how I access to data
df_data[symbol] = pd.read_sql("SELECT * FROM {} where time BETWEEN {} and {}".format(tradesTableName,tickerStartDateTimestamp,tickerEndDateTimestamp), con=db)

Any suggestions on how to plot the data?

Thank you very much for your time.

Cheers!
Vamsi

[kieran-mackle]

Hi Vamsi, glad you are liking AutoTrader! If you just want to visualise the data, you can just use AutoPlot, instead of going through AutoTrader. Like you have done, I would also first load the data as a Pandas DataFrame before plotting. As a guide, see the indiview script from the demo repository. If you only want to plot the price data, something like the code below should work:

import pandas as pd
from autotrader import AutoPlot

# Load price data
symbol = "symbol name"
df_data[symbol] = pd.read_sql( ... )

# Instantiate AutoPlot and plot
ap = AutoPlot(df_data[symbol])
ap.plot(instrument=symbol)

Just make sure that the data you provide has columns named Open, High, Low and Close. If you want to add indicators to the chart, you can do so using the indicator dictionary, similar to the indiview example, by passing it in via the indicators argument.

When you are ready to run some backtests/trade on the data, you will indeed have to use the add_data method. If that is what you were trying to do, the docs on add_data will hopefully be of use, but feel free to comment if you're having troubles and I can step you through it a bit more!

Ps. you might find the following blog posts useful, which go over using AutoPlot in depth:

• Viewing Price Data with AutoTrader's IndiView
• Developing a Scanner to Automatically get Notified of Trending Markets

[vepak]

Thank you very much Kieran for your guidance.

I could able to successfully up the charts. Below is the code. I've few questions

1. dataDataframeTick() performs all the logic calculating the vwap in this case. I've also few other calculations that I'll be performing which is not the standard indicators. can add_data periodically call dataDataframeTick() ? so I can use the same code structure for live streaming. I'm decent with coding, but for some reason not able to completely understand how add_data works after going through the docs. so I'm asking here.

2. Can we display indicator value in databox or on plot?

3. As you can see from my code I'm tracking two tickers. Is it possible to add two charts on the same page?

Once again thank you for your time. Cheers!

from autotrader.autodata import GetData
from autotrader.autoplot import AutoPlot
import pandas as pd
import time
import os
from datetime import datetime,timedelta
import sqlite3
import numpy as np

tickerStartDate = datetime.today().strftime('%Y%m%d') #"20220331" #YYYYMMDD
tickerStartDate='20220413'
timeElement = datetime.strptime(tickerStartDate,'%Y%m%d')
tickerStartDateTimestamp = datetime.timestamp(timeElement)
tickerEndDateTimestamp = datetime.timestamp(timeElement + timedelta(days = 1))
tickers = ["NIFTY50","BANKNIFTY"]
priceTypes = ["TRADES", "BID_ASK"]
outdir = 'historicalData'
if not os.path.exists(outdir):
    os.mkdir(outdir)

db = sqlite3.connect(outdir+'/ticks.db')
def dataDataframeTick():
    "returns extracted historical data in dataframe format"
    df_data = {}
    df_candleData = {}
    for symbol in tickers:
        tradesTableName = symbol+"_"+priceTypes[0]

        df_data[symbol] = pd.read_sql("SELECT * FROM {} where time BETWEEN {} and {}".format(tradesTableName,tickerStartDateTimestamp,tickerEndDateTimestamp), con=db)                
        df_data[symbol].drop_duplicates(subset =["time","price","volume"],
                     keep = 'first', inplace = True)
        v = df_data[symbol].volume.values
        p = df_data[symbol].price.values
        df_data[symbol]['vwap'] = np.cumsum(v*p) / np.cumsum(v)
        
        df_data[symbol]['datetime'] = pd.to_datetime(df_data[symbol]['time'], 
                          unit='s').dt.tz_localize('utc').\
                                       dt.tz_convert('Asia/Kolkata')
        df_data[symbol]['datetime'] = pd.to_datetime(df_data[symbol]['datetime']).apply(lambda t: t.replace(tzinfo=None))
        df_data[symbol] = df_data[symbol].set_index('datetime')

        df_candleData[symbol] = df_data[symbol].resample('1T', label='right', closed='right').agg({'price':'ohlc',
                                                         'volume':'sum',
                                                         'vwap':'last'}).dropna()
        df_candleData[symbol].columns = df_candleData[symbol].columns.droplevel()
        df_candleData[symbol].rename(columns = {'open':'Open', 'high':'High',
                              'low':'Low', 'close':'Close', 'volume':'Volume'}, inplace = True)
    return df_candleData

candle_data = dataDataframeTick()
vwap = candle_data[tickers[0]].vwap.values
indicators = {
            'vwap': {'type': 'MA',
                          'data': vwap}}
ap = AutoPlot(candle_data[tickers[0]])
ap.plot(indicators=indicators,instrument=tickers[0])

[kieran-mackle]

1. Absolutely! Although not directly through add_data as you may expect. The operation is slightly different depending on if you are running in continuous or periodic mode. However, in short, if you are live/forward trading and have provided a path to local data using add_data, the data at that path will be loaded each time the strategy is updated, ensuring that any new data that has come through will be passed on to the strategy. If you are converting your data to a DataFrame from an SQL database, I would recommend that you set up a helper script which runs as frequently as your data updates, to load the new data from the database and write it to a .csv file, which AutoTrader can read directly. Note that if you are backtesting, the set-up will be identical, but instead of loading your data each update, it is loaded once and filtered to the relevant timestamp. Does this all make sense? Happy to clarify further if needed. Also, if you want to look through the code, the relevant sections are in autobot.py and utilities.py. Admittedly, the docs for the mechanics behind this are a bit lacking, so feel free to ask more questions here and I will clarify.
2. I'm not totally sure what you mean by this. If your indicator is updated on each bar, you can include its timeseries on the chart generically with 'type': 'below' in the indicator dictionary. This will create a line plot of the indicator below the candlestick chart. Is that what you are looking for?
3. It is possible, but currently in a limited capacity. To use AutoPlot natively, you can achieve this with 'type': 'Heikin-Ashi'. This will plot the candles of the second instrument below the main instrument. This doesn't actually create Heikin-Ashi candlesticks, just provides a means to plot them, so it will serve your purpose to plot a second instrument. See the code below as an example. Unfortunately, this won't allow you to plot indicators on top of the second candle chart (eg. moving averages), but you will still be able to plot other indicators below/above it (you may have to adjust the max_indis_over and max_indis_below properties via the AutoPlot.configure method). If you wanted to get into the code a bit more, I think it would be relatively straightforward to extend the functionality and allow plotting indicators on the second chart too.

indicators = {'tickers[1] Data': {'type': 'Heikin-Ashi', 'data': candle_data[tickers[1]]},}

ap = AutoPlot(candle_data[tickers[0]])
ap.plot(indicators=indicators, instrument=tickers[0])

[vepak]

Thank you for your guidance. This gave me how to plan my setup.

Once again appreciate your time.