Petrol and Diesel Price Analytics and Short-Term Forecasting for Indian Metro Cities from Daily IOCL Data (Java, MySQL)

Petrol and Diesel Price Analytics is a data-science-flavoured mini-project for the University of Madras BCA CBCS 2023-24 slot 320S61 — Mini Project (Professional Competency Skill). Since 16 June 2017, Indian Oil Corporation (IOCL) has revised petrol and diesel prices in metro cities every morning at 6 a.m., producing one of India's longest daily open data series. This project turns that bulletin stream into an analytics application built on the skills the Madras BCA syllabus teaches: Java (220C4A) for the application and analysis, MySQL (320C5B RDBMS) for storage and SQL aggregation, and HTML/CSS (320C5C Web Technology) for the generated dashboard report.

The application loads daily petrol and diesel prices for Delhi, Mumbai, Chennai and Kolkata, cleans them into a well-designed MySQL schema, and answers questions commuters actually ask: How fast did prices climb in each city? Which city pays the most, and how much of that gap is state tax? How long were the famous price-freeze spells? It computes monthly averages, day-over-day changes, longest rise streaks and city-vs-city spreads, then produces a 7-day forecast for each city using a moving-average and a simple linear-regression model, evaluated with MAE and MAPE on hold-out weeks.

JFreeChart — a free Java charting library layered on the syllabus stack as an industry-standard extra — renders trend and spread charts; the app exports a self-contained HTML/CSS analytics report. The project deliberately uses Java rather than Python: it proves data-handling and analytical ability inside the language the Madras syllabus examines, using nothing beyond JDBC, collections and SQL.

Every morning Indian newspapers and apps publish metro fuel prices, but the published view is a snapshot: today's number, yesterday's number, a year-on-year comparison. The underlying daily series — thousands of observations per city since June 2017 — sits scattered across tracker sites and government PDFs in forms that cannot be queried. A student, a commuter or a small delivery business that wants to answer simple questions — how steep was this quarter's climb in Chennai, how long did the last price freeze last, how much costlier is Mumbai's diesel than Delhi's and why, what will a 50-litre tank cost next week — has no structured place to ask them.

There is a need for a small, reproducible analytics application that consolidates the daily prices into a proper relational database, validates and documents the data quality, computes the trends, streaks and city gaps with SQL and Java, and produces short-term forecasts with reported accuracy rather than guesswork — all built with the Java, RDBMS and web-technology skills of the BCA syllabus.

Abstract

Fuel prices touch every household budget, and since mid-2017 India publishes a daily retail price revision for petrol and diesel in every metro. This mini-project builds a Java + MySQL analytics application that ingests that daily stream for Delhi, Mumbai, Chennai and Kolkata, stores it in a normalised schema, computes descriptive and comparative analytics, and generates short-term forecasts with two simple models whose accuracy is measured on hold-out data. The output is a Swing dashboard, chart images and a generated HTML analytics report.

Introduction

A litre of petrol in Mumbai and in Delhi on the same day can differ by several rupees, and the gap is not the oil company's margin — it is mostly state-level VAT and local levies on a near-common base price. Because prices are revised daily, the data form a clean multivariate time series: four cities, two fuels, one observation per day, published by IOCL. That makes it an ideal real dataset for a BCA analytics project: no scraping of fragile pages, no missing primary keys, and a story every examiner understands.

Existing system vs this project

• News sites and tracker apps show today's price per city; archives are scattered across PDFs and price-build-up tables published by PPAC.
• Spreadsheets maintained by individuals are ad hoc: no schema, no validation, no reproducible charts.
• Academic work on Indian fuel data usually uses Python notebooks that are hard to mark in a Java-taught syllabus.
• Gap: a structured, queryable, Java-based analytics application with documented data cleaning, SQL-driven aggregation and evaluated forecasting.

Proposed system

1. Data layer (MySQL): tables for city, fuel_type, daily_price (city, fuel, date, price, revised flag) and a precomputed monthly_summary; the composite primary key prevents duplicate dates; prices stored as DECIMAL(7,2), never FLOAT.
2. Ingestion and cleaning (Java): CSV import with validation — duplicate dates, negative or suspicious jumps and missing days are reported, not silently repaired; a cleaning log is saved for the report.
3. Analytics (SQL + Java): monthly averages, month-on-month change, longest consecutive-rise streaks, price-freeze detection, city spreads and the diesel–petrol gap, all via parameterised SQL.
4. Forecasting module: a 7-day horizon using (a) a 28-day moving average and (b) ordinary least-squares regression on the last 90 days, selected per city-fuel by back-testing on the final four weeks; error reported as MAE and MAPE.
5. Presentation: JFreeChart trend/spread charts and a generated HTML/CSS report viewable in any browser.

Feasibility

• Technical: JDK 17, MySQL Community and JFreeChart are free; the CSV dataset for 2017–present is a few thousand rows per city-fuel and fits on a laptop; the build fits 6 weeks of part-time work because the domain logic is arithmetic plus SQL.
• Economic: zero licence cost.
• Operational: the app runs offline after the one-time data download; the generated HTML report needs only a browser.
• Scope control: four cities, two fuels, seven-day horizon only — deep enough to be advanced, small enough to finish.

In scope

• Daily retail prices of petrol and diesel in Delhi, Mumbai, Chennai and Kolkata, from June 2017 to the latest available data.
• CSV ingestion, cleaning log, MySQL storage, SQL analytics, two forecasting models, chart and HTML report generation.
• Single-user desktop use (Java Swing dashboard).

Out of scope

• All cities and other OMCs (BPCL, HPCL) — one company's metro series is enough to demonstrate the method.
• Intraday or outlet-level prices; LPG and other fuels.
• Causal modelling of crude prices, exchange rates or policy events (noted in future scope).
• A hosted multi-user web service; the Web Technology course component is the generated HTML report, not a server.

The project follows a mini-waterfall with a working skeleton first: requirements and data acquisition in week 1, schema and module design in week 2, then build-test-build in weeks 3–5 and a report/demo in week 6. Waterfall suits a 2-credit mini-project with one developer and a fixed deadline; a skeleton that reads a week of CSV and prints it keeps risk visible from day 2.

Analysis method: descriptive statistics and time-series decomposition are done in SQL and Java (no external statistics library); the two forecasters are deliberately simple — a 28-day moving average and ordinary least-squares regression on the last 90 days — and the better one per series is chosen by back-testing on a hold-out month. MAE and MAPE are reported for every city-fuel pair, so the accuracy claim in the report is a measured number, not an impression.

Testing approach: JUnit tests for the CSV validator (duplicate date, negative price, 5-rupee jump), DAO queries and both forecasters against small synthetic series with known answers; a black-box test-case table for the UI and report.

The application is a three-layer Java desktop system with MySQL as the data tier and a generated HTML report as the presentation output.

1. Data tier — MySQL 8.0: normalised tables (city, fuel_type, daily_price, monthly_summary) with primary keys (city, fuel, date) that make duplicate loading impossible; DECIMAL(7,2) money types; indexes on date for range queries.
2. Logic tier — Java (JDK 17): CsvLoader validates and batch-inserts rows through JDBC prepared statements; PriceDao exposes typed queries; Analytics computes streaks, spreads and monthly roll-ups; Forecaster implements the moving-average and OLS models with a back-testing harness; ReportWriter writes the HTML/CSS analytics report.
3. Presentation tier: a Swing dashboard with JFreeChart panels, plus the exported static HTML report viewable in any browser.

flowchart LR
  CSV["CSV: daily IOC prices
  (4 cities, 2 fuels)"] --> V["CsvLoader + validation
  (duplicates, gaps, jumps)"]
  V -->|JDBC prepared statements| DB[(MySQL:
  city, fuel_type,
  daily_price, monthly_summary)]
  DB --> A["Analytics service
  (SQL aggregation + Java)"]
  A --> F["Forecaster: 28-day MA and
  OLS regression, back-tested"]
  F --> M{"MAE / MAPE"}
  M -->|pick better per series| O["Forecast table"]
  A --> C[JFreeChart trends / streaks / spreads]
  C --> UI[Swing dashboard]
  A --> R["ReportWriter: HTML/CSS
  analytics report"]
  O --> R

ER diagram (core tables)

erDiagram
  CITY ||--o{ DAILY_PRICE : has
  FUEL_TYPE ||--o{ DAILY_PRICE : prices
  DAILY_PRICE ||--o{ MONTHLY_SUMMARY : "rolls up to"
  CITY {
    int city_id PK
    string name UK
    string state
  }
  FUEL_TYPE {
    int fuel_id PK
    string name UK
  }
  DAILY_PRICE {
    int city_id PK_FK
    int fuel_id PK_FK
    date price_date PK
    decimal price
    boolean revised
  }
  MONTHLY_SUMMARY {
    int city_id PK_FK
    int fuel_id PK_FK
    int year PK
    int month PK
    decimal avg_price
    decimal min_price
    decimal max_price
    int change_days
  }

The composite primary key on daily_price is the single most important design decision: it encodes the business rule that a city-fuel-date combination exists at most once, which turns data-quality control into a database constraint rather than an afterthought.

• Crude-price overlay — correlate the metro series with Brent crude and the INR exchange rate to explain level shifts, not just track them.
• More cities and OMCs — extend to state capitals and BPCL/HPCL series to study the full VAT landscape.
• Better models — Holt's linear trend and simple ARIMA, still in pure Java, benchmarked against the two baseline models.
• Policy-event annotation — mark election-period price freezes and excise cuts on the timeline and quantify their effect.
• Scheduled updater — a small job that appends the latest day's prices and refreshes the HTML report automatically.