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

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.