Stock Analysis System

A financial data analysis project using MySQL + Python Pandas for simulating, storing, processing, and visualizing stock market data.

Features

• Data Simulation: 20 stocks across 5 sectors, 5 years of daily OHLCV data using Geometric Brownian Motion
• MySQL Storage: SQLAlchemy + PyMySQL for efficient data persistence and querying
• Technical Indicators: MA, MACD, RSI, Bollinger Bands
• Portfolio Analysis: Returns, volatility, Sharpe ratio, correlation matrix, efficient frontier
• Strategy Backtest: Dual moving average crossover with performance metrics
• Visualizations: Static charts (Matplotlib/Seaborn) + Interactive charts (Plotly)

Architecture

data_generator -> MySQL (stocks/stock_prices/indicators)
                      |
              Pandas Analysis (SQL queries -> DataFrame)
                      |
              Visualization (Matplotlib + Plotly)

Quick Start

Prerequisites

• Python 3.10+
• MySQL 8.0+ (Homebrew: brew install mysql)
• MySQL server running: mysql.server start

Setup

git clone <repo-url>
cd stock-analysis
pip install -r requirements.txt

Run

mysql.server start        # Ensure MySQL is running
python main.py            # Full pipeline: generate -> store -> analyze -> visualize

Open notebooks/analysis_demo.ipynb for interactive exploration.

Project Structure

stock-analysis/
├── main.py                 # One-click pipeline entry
├── config.py               # MySQL connection settings
├── requirements.txt        # Python dependencies
├── sql/
│   ├── 01_create_tables.sql
│   └── 02_sample_queries.sql
├── src/
│   ├── data_generator.py       # Stock data simulation (GBM)
│   ├── db_manager.py           # SQLAlchemy CRUD operations
│   ├── technical_indicators.py # MA, MACD, RSI, Bollinger
│   ├── portfolio_analysis.py   # Returns, Sharpe, correlation, frontier
│   ├── strategy_backtest.py    # Dual MA crossover backtest
│   └── visualizations.py       # Matplotlib + Plotly charts
├── notebooks/
│   └── analysis_demo.ipynb
└── output/
    └── charts/                 # Generated PNG + HTML charts

Charts Generated

Chart	File
Price with MAs & Bollinger Bands	<SYMBOL>_price_ma.png
MACD + RSI	<SYMBOL>_macd_rsi.png
Correlation Heatmap	correlation_heatmap.png
Efficient Frontier (interactive)	efficient_frontier.html
Equity Curve	<SYMBOL>_equity_curve.png
Sector Performance	sector_performance.png
Interactive OHLCV (Plotly)	<SYMBOL>_interactive.html

Tech Stack

• Python 3.10+ with pandas, numpy
• MySQL for data persistence
• SQLAlchemy + PyMySQL for database connectivity
• Matplotlib + Seaborn for static visualization
• Plotly for interactive visualization
• Jupyter for notebook exploration

License

MIT