# NSE F&O PHP/MySQL Stock Screener

This project downloads NSE CM-UDiFF daily bhavcopy archives, stores daily OHLC data for the configured F&O underlying symbols in MySQL, calculates Coppock(+DI20), Coppock(-DI20), ADX(20), TRIX(18), and SMA 20/50/200, and serves a filterable PHP screener.

## Important notes

- The current NSE CM-UDiFF archive name is expected to be `BhavCopy_NSE_CM_0_0_0_YYYYMMDD_F_0000.csv.zip` under `https://nsearchives.nseindia.com/content/cm/`. NSE may change paths or headers; verify the current All Reports page if downloads stop.
- The importer supports common UDiFF headers (`TradDt`, `TckrSymb`, `SctySrs`, `OpnPric`, `HghPric`, `LwPric`, `ClsPric`, `TtlTradgVol`, `ISIN`) and legacy-style aliases. It deliberately fails if required columns are absent.
- `config/fno_symbols.txt` is a fallback and a local editable universe. Run `php cron/sync_fno_symbols.php` to refresh it from the official NSE F&O underlying page. NSE may block automated requests or change its page markup; verify the resulting symbol count/list. The included list is only a starter list, not a guaranteed exhaustive live universe.
- The initial import requests 92 calendar days, not exactly 3 months of sessions. Weekends are skipped and NSE holidays / unpublished archives are treated as unavailable.
- Only current F&O symbols are imported during backfill. This will not include symbols that were F&O in the past but have since been removed.
- The indicator engine uses native PHP and requires no Trader/TA-Lib extension. Its ADX/Wilder seeding and EMA seeding are explicit approximations; verify indicator values against your TradingView script before relying on them for trading decisions. TRIX uses triple EMA of close and one-period percentage change. Coppock uses percentage ROC values on +DI(20) and -DI(20).
- Historical imports are idempotent because `(stock_id, trade_date)` is unique and inserts use `ON DUPLICATE KEY UPDATE`.
- The 3-month history will not provide SMA200. SMA200 remains NULL until 200 daily rows exist; import more history if you need SMA200 values.
- No trading or investment recommendation is made by the software.

## Requirements

- PHP CLI and web PHP 8.1+ recommended
- PHP extensions: `pdo_mysql`, `curl`, `zip`, `dom`, `libxml`, `mbstring`
- MySQL 8.0+ or MariaDB 10.4+
- Linux server / cron
- HTTPS outbound access to NSE

Check CLI modules:

```bash
php -v
php -m | egrep 'PDO|pdo_mysql|curl|zip|dom|libxml|mbstring'
```

Make sure CLI PHP is the same version/configuration you intend to use for cron.

## Install

1. Upload/extract the project to a non-public directory, for example `/var/www/nse-screener`.
2. Import schema:

```bash
mysql -u root -p < database.sql
```

3. Create a restricted DB user (change the password):

```sql
CREATE USER 'nse_screener'@'localhost' IDENTIFIED BY 'REPLACE_WITH_LONG_RANDOM_PASSWORD';
GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, ALTER, INDEX ON nse_screener.* TO 'nse_screener'@'localhost';
FLUSH PRIVILEGES;
```

4. Edit `config.php` or define environment variables:

```bash
export NSE_DB_HOST=127.0.0.1
export NSE_DB_PORT=3306
export NSE_DB_NAME=nse_screener
export NSE_DB_USER=nse_screener
export NSE_DB_PASS='REPLACE_WITH_LONG_RANDOM_PASSWORD'
```

For cron, environment variables are not necessarily inherited. Either define them in a protected wrapper script or configure `config.php` with the database credentials and restrict its file permissions. Do not commit real credentials to a public repository.

5. Set ownership and permissions. Adjust the user/group for your server:

```bash
mkdir -p data logs
chown -R www-data:www-data data logs
chmod 750 data logs
chmod 640 config.php
```

6. Refresh F&O symbols:

```bash
php cron/sync_fno_symbols.php
```

If NSE blocks this request or parsing returns no symbols, review `config/fno_symbols.txt` and update it manually from the official NSE F&O underlying list. Do not assume the starter list is complete.

7. Backfill about 3 months:

```bash
php cron/import_historical.php 92
```

This scans 92 calendar days, skips weekends, and tolerates unavailable dates. Check `logs/app.log` and `import_runs` for results. Run it again if NSE archives were temporarily unavailable; upserts prevent duplicate date rows.

8. Calculate indicators independently if needed:

```bash
php cron/recalculate_indicators.php
```

9. Serve the web UI. For a quick local smoke test only:

```bash
php -S 127.0.0.1:8080 -t public
```

Then visit `http://127.0.0.1:8080`. In production, configure your existing Nginx/Apache/Plesk vhost document root to point to the project's `public/` directory. Keep `config.php`, SQL, logs, and cron scripts outside the public document root.

## Cron setup

Run after NSE publishes the EOD bhavcopy. Start conservatively; adjust the time to your server's timezone and verify publication time.

```cron
# Refresh F&O universe on Sunday at 07:00 IST
0 7 * * 0 cd /var/www/nse-screener && /usr/bin/php cron/sync_fno_symbols.php >> logs/cron.log 2>&1

# Daily import at 19:00 IST Monday-Friday
0 19 * * 1-5 cd /var/www/nse-screener && /usr/bin/php cron/import_daily.php >> logs/cron.log 2>&1
```

If server time is not Asia/Kolkata, set the cron timezone explicitly where supported or use the correct local time. NSE holidays are handled by trying the current date and recent weekdays. After first deployment, manually run the daily command once and inspect logs.

## Screener filters

- Minimum Coppock(+DI)
- Minimum ADX(20)
- Minimum +DI(20)
- Closing price above SMA200

The latest row per stock is displayed. Add your exact entry conditions once the raw values have been checked against TradingView.

## Suggested smoke tests

```sql
USE nse_screener;
SELECT COUNT(*) AS stocks FROM stocks WHERE is_fno=1;
SELECT MIN(trade_date), MAX(trade_date), COUNT(*) FROM daily_prices;
SELECT trade_date, COUNT(*) FROM daily_prices GROUP BY trade_date ORDER BY trade_date DESC LIMIT 10;
SELECT s.symbol, p.trade_date, p.close_price, i.plus_di20, i.adx20, i.coppock_di, i.trix18
FROM stocks s
JOIN daily_prices p ON p.stock_id=s.id
LEFT JOIN daily_indicators i ON i.stock_id=p.stock_id AND i.trade_date=p.trade_date
ORDER BY p.trade_date DESC, s.symbol LIMIT 30;
```

## Indicator definitions

- +DI(20), -DI(20), and ADX(20): Wilder-style directional movement calculations.
- Coppock(+DI): `WMA(14, ROC(10, +DI20) + ROC(11, +DI20))`; ROC is percentage change.
- TRIX(18): triple EMA of closing price, then one-period percentage change.
- SMA20/SMA50/SMA200: simple moving average of closing price.

A 3-month backfill gives enough data for many Coppock/ADX values, but not SMA200. Use a longer historical backfill for SMA200 and for more stable EMA warm-up.


## Period update

The directional movement indicators and ADX use length 20. Coppock is calculated from +DI(20), with ROC periods 10 and 11 and WMA length 14. TRIX remains 18; moving averages remain 20, 50, and 200.


## Additional screener columns

- **Long Cross** = `1` only when Coppock(+DI20) is above zero today and was at or below zero on the previous stored trading day; otherwise `0`.
- **Short Cross** = `1` only when Coppock(-DI20) is above zero today and was at or below zero on the previous stored trading day; otherwise `0`.
- **+DI20 − -DI20** = the numeric difference between +DI(20) and -DI(20). Positive values mean +DI is higher; negative values mean -DI is higher.
- Coppock(-DI20) uses the same formula as Coppock(+DI20), but uses -DI(20) as its input.

## Re-import one failed date

Run the new specific-date script using the date in ISO format:

```bash
php cron/import_date.php 2026-10-13
```

Replace the example date with the trading date you need. The script downloads that exact date, upserts the OHLC data without duplicates, then recalculates indicators. It does not silently substitute a different date. If the archive is unavailable, inspect `logs/app.log` and the `import_runs` table.

## Resetting tables

`reset_database.sql` is **destructive**. It drops all four project tables and recreates the schema with the new `coppock_minus_di` field. Back up anything you need before running it:

```bash
mysqldump -u YOUR_DB_USER -p nse_screener > backup_before_reset.sql
mysql -u YOUR_DB_USER -p < reset_database.sql
```

After the reset, run F&O symbol sync and the historical import again. Keep the database name in `reset_database.sql` aligned with the database configured in `config.php`.