Rajandran R Creator of OpenAlgo - OpenSource Algo Trading framework for Indian Traders. Telecom Engineer turned Full-time Derivative Trader. Mostly Trading Nifty, Banknifty, High Liquid Stock Derivatives. Trading the Markets Since 2006 onwards. Using Market Profile and Orderflow for more than a decade. Designed and published 100+ open source trading systems on various trading tools. Strongly believe that market understanding and robust trading frameworks are the key to the trading success. Building Algo Platforms, Writing about Markets, Trading System Design, Market Sentiment, Trading Softwares & Trading Nuances since 2007 onwards. Author of Marketcalls.in

How to Manage and Visualize Intraday Stock Market Data with SQLite Database – Python Tutorial

3 min read

In this tutorial, we’ll explore how to effectively manage and visualize intraday stock data using Python. We’ll cover importing data from CSV files into an SQLite database, retrieving the data into a Pandas DataFrame, and finally, plotting it using Plotly in a candlestick format. This guide is perfect for financial analysts, hobbyist data scientists, and anyone interested in stock market data.

Practical Use Cases of this Tutorial

Financial Analysis and Research: This tutorial is ideal for financial analysts and researchers who need to study historical price movements, trading volume, and market trends to make informed investment decisions or conduct academic research.

Trading Strategy Development: Traders developing and testing algorithmic trading strategies can use this tutorial to backtest their models against historical data to assess their effectiveness before applying them in live trading scenarios.

Sample CSV File Format ( Download IEOD CSV File )

Symbol,Date,Time,Open,High,Low,Close,Volume,OpenInterest
NIFTY,20240510,09:15,22100.2,22110.0,22092.75,22098.6,155075,12252625
NIFTY,20240510,09:16,22098.75,22113.65,22091.1,22109.25,77325,12252625
NIFTY,20240510,09:17,22105.5,22118.45,22105.05,22109.25,60825,12252625
NIFTY,20240510,09:18,22109.2,22115.55,22101.05,22101.05,47700,12254700
NIFTY,20240510,09:19,22100.2,22102.75,22081.0,22081.55,42200,12254700
NIFTY,20240510,09:20,22076.2,22086.95,22068.15,22074.5,51775,12254700
NIFTY,20240510,09:21,22071.2,22092.05,22071.2,22087.65,65850,12244525

Prerequisites

To follow this tutorial, you should have a basic understanding of Python. Additionally, you’ll need to have Python installed along with the following libraries:

  • Pandas
  • SQLite3
  • Plotly

You can install the necessary libraries using pip:

pip install pandas sqlite3 plotly

Why SQLite DB?

Using SQLite as a database solution for storing stock market data offers several advantages, especially when dealing with scenarios where lightweight, fast, and efficient data management is required

SQLite is renowned for its simplicity and ease of setup. It doesn’t require a separate server process or system installation. You can start using it by simply including its library in your project, which makes it incredibly straightforward for projects that require an embedded database.

SQLite does not require any configuration to get started; it runs straight out of the box. There are no server processes that need to be configured, managed, or maintained.

For applications that are used by a single user, like personal finance tools or desktop applications for market analysis, SQLite provides all the required database capabilities without the overhead of client-server DBMS.

Step 1: Importing CSV Data into SQLite

First, we’ll start by importing our intraday stock data from a CSV file into an SQLite database. This approach helps manage data efficiently and allows for complex queries.

import sqlite3
import pandas as pd
import plotly.graph_objects as go

# Load data from a CSV file and assign columns
df = pd.read_csv('IEOD.csv')

df

Output:


Symbol	Date	Time	Open	High	Low	Close	Volume	OpenInterest
0	NIFTY	20240510	09:15	22100.20	22110.00	22092.75	22098.60	155075	12252625
1	NIFTY	20240510	09:16	22098.75	22113.65	22091.10	22109.25	77325	12252625
2	NIFTY	20240510	09:17	22105.50	22118.45	22105.05	22109.25	60825	12252625
3	NIFTY	20240510	09:18	22109.20	22115.55	22101.05	22101.05	47700	12254700
4	NIFTY	20240510	09:19	22100.20	22102.75	22081.00	22081.55	42200	12254700
...	...	...	...	...	...	...	...	...	...
371	NIFTY	20240510	15:26	22131.80	22132.90	22125.20	22125.20	43000	11925275
372	NIFTY	20240510	15:27	22125.35	22130.90	22124.30	22129.00	37825	11925275
373	NIFTY	20240510	15:28	22129.00	22130.85	22127.20	22130.30	33975	11933725
374	NIFTY	20240510	15:29	22130.65	22130.65	22127.70	22127.70	31225	11933725
375	NIFTY	20240510	15:30	22127.05	22127.05	22127.05	22127.05	1375	11933725
376 rows × 9 columns

2. Connect to SQLite , Create a Table, Indexing and Insert Data into the Table:

This Python code demonstrates how to interact with an SQLite database to manage stock market data. It involves establishing a connection to an SQLite database, creating a table specifically designed to store stock information, including fields for stock symbols, dates, times, and various financial metrics. An index is created on the ‘Symbol’ column to optimize query performance.

What is Indexing?

Indexing in databases is a powerful technique used to speed up the retrieval of data from a database table. It involves creating an additional data structure that improves the speed of data retrieval operations by efficiently locating the data after executing queries on a database


# Connect to SQLite database
conn = sqlite3.connect('stock_db.db')
cursor = conn.cursor()

# Create table
cursor.execute('''
CREATE TABLE IF NOT EXISTS stocks (
    Symbol TEXT,
    Date TEXT,
    Time TEXT,
    Open REAL,
    High REAL,
    Low REAL,
    Close REAL,
    Volume INTEGER,
    OpenInterest INTEGER
)
''')

#Indexing
cursor.execute('CREATE INDEX IF NOT EXISTS idx_symbol ON stocks (Symbol)')

conn.commit()

# Insert data into the table
df.to_sql('stocks', conn, if_exists='append', index=False)

Finally, stock data, stored in a pandas DataFrame, is appended to the newly created table, facilitating efficient data storage and retrieval for analytical purposes. This setup is essential for financial data analysis, allowing for quick access and manipulation of stock market data.

3. Retrieving Data from SQLite

Once the data is stored, you can retrieve it as needed. We’ll use Pandas for this purpose as it provides a convenient interface for database operations.

Fetch Data:

# Retrieve data from the database
# Define the symbol you want to query
symbol = 'NIFTY'

# Execute the query using parameter substitution
cursor.execute("SELECT * FROM stocks WHERE Symbol = ?", (symbol,))

data_retrieved = cursor.fetchall()

# Define the column names
columns = ['Symbol', 'Date', 'Time', 'Open', 'High', 'Low', 'Close', 'Volume', 'OpenInterest']

df_retrieved = pd.DataFrame(data_retrieved, columns=columns)
df_retrieved

Output :

Symbol	Date	Time	Open	High	Low	Close	Volume	OpenInterest
0	NIFTY	20240510	09:15	22100.20	22110.00	22092.75	22098.60	155075	12252625
1	NIFTY	20240510	09:16	22098.75	22113.65	22091.10	22109.25	77325	12252625
2	NIFTY	20240510	09:17	22105.50	22118.45	22105.05	22109.25	60825	12252625
3	NIFTY	20240510	09:18	22109.20	22115.55	22101.05	22101.05	47700	12254700
4	NIFTY	20240510	09:19	22100.20	22102.75	22081.00	22081.55	42200	12254700
...	...	...	...	...	...	...	...	...	...
371	NIFTY	20240510	15:26	22131.80	22132.90	22125.20	22125.20	43000	11925275
372	NIFTY	20240510	15:27	22125.35	22130.90	22124.30	22129.00	37825	11925275
373	NIFTY	20240510	15:28	22129.00	22130.85	22127.20	22130.30	33975	11933725
374	NIFTY	20240510	15:29	22130.65	22130.65	22127.70	22127.70	31225	11933725
375	NIFTY	20240510	15:30	22127.05	22127.05	22127.05	22127.05	1375	11933725
376 rows × 9 columns

4.Plotting Intraday Candlestick Charts using Plotly

Finally, we’ll plot the retrieved data using Plotly’s candlestick chart, which is particularly suited for intraday stock market historical data.

# Plotting using Plotly
fig = go.Figure(data=[go.Candlestick(x=df_retrieved['Date'] + ' ' + df_retrieved['Time'],
                                     open=df_retrieved['Open'], high=df_retrieved['High'],
                                     low=df_retrieved['Low'], close=df_retrieved['Close'],
                                     increasing_line_color='green', decreasing_line_color='red'),
                      go.Bar(x=df_retrieved['Date'] + ' ' + df_retrieved['Time'], y=df_retrieved['Volume'], marker_color='blue', yaxis='y2')])


# Set layout for volume bar chart
fig.update_layout(
    xaxis_rangeslider_visible=False,
    title='Intraday Stock Prices with Volume',
    yaxis_title='Stock Price',
    yaxis2=dict(title='Volume', overlaying='y', side='right'),
    xaxis=dict(
        type='category',  # Removing automatic date alignment like weekend gaps
        tickangle=45,  # Rotate the tick labels for better visibility
        tickvals=df_retrieved['Date'] + ' ' + df_retrieved['Time'],  # Optional: Specify tick positions
        ticktext=[(date_time if i % 30 == 0 else '') for i, date_time in enumerate(df_retrieved['Date'] + ' ' + df_retrieved['Time'])],  # Show only every 10th label
        tickmode='array'  # Use the array mode for ticks to customize them
    ),
    xaxis_title_text='Date',
    height=800,  # Set the height of the figure
    width=1200)  # Set the width of the figure

By following these steps, you can efficiently import, store, and visualize stock market data for any analysis or trading strategy you’re testing. This guide should help you build a solid foundation for working with financial data using Python’s powerful libraries.

Rajandran R Creator of OpenAlgo - OpenSource Algo Trading framework for Indian Traders. Telecom Engineer turned Full-time Derivative Trader. Mostly Trading Nifty, Banknifty, High Liquid Stock Derivatives. Trading the Markets Since 2006 onwards. Using Market Profile and Orderflow for more than a decade. Designed and published 100+ open source trading systems on various trading tools. Strongly believe that market understanding and robust trading frameworks are the key to the trading success. Building Algo Platforms, Writing about Markets, Trading System Design, Market Sentiment, Trading Softwares & Trading Nuances since 2007 onwards. Author of Marketcalls.in

Voice Commands to Trade on OpenAlgo Platform Using Google…

Trading platforms are always getting better by using the newest technologies to make things easier and more efficient for users. A great example of...
Rajandran R
5 min read

[Live Coding Webinar] Build Your First Trading Bridge for…

In this course, you will be learning to build your own trading bridge using Python. This 60-minute session is perfect for traders, Python enthusiasts,...
Rajandran R
1 min read

How to Place Orders Concurrently using ThreadPoolExecutor – Python…

Creating concurrent orders is essential for active traders, especially those handling large funds, as it allows for executing multiple trade orders simultaneously, thereby maximizing...
Rajandran R
2 min read

Leave a Reply

Get Notifications, Alerts on Market Updates, Trading Tools, Automation & More