Automated Stock Analysis Excel Dashboard
Budget: ₹750 – ₹1,250 INR
Quarterly Data Automation — Developer Draft
(Excel/Screener)
Version: v3 (Simple Client Version)
1. Goal (One Line)
Excel workbook that auto-updates with quarterly Sales & EPS for every stock, calculates YoY/QoQ, generates BUY/SELL/OTHER signals from the last two quarters, and displays them in a Dashboard (visible to all).
All other sheets are locked with two security layers.
Formulas remain hidden.
New metrics (e.g., OPM%) can be added without breaking the system.
2. Must-Have Features
No manual work: Sales & EPS auto-fetched from Screener.in (CSV fallback if required).
Automatic new quarter: New columns added automatically; old data intact.
Auto calculations: YoY% and QoQ% always formula-based.
Signals: Per stock, based on only the last two quarters. No cut-paste.
Dashboard: Shows live BUY, SELL, OTHER lists with stock, sector, latest quarter, and optional reason.
Click-to-open: From Dashboard → open exact sector sheet at the stock.
Security Layer 1: Only Dashboard visible; other sheets VeryHidden + password prompt.
Security Layer 2: Formulas hidden/locked; editor password required to edit.
Single source of truth: One central Signal formula.
Flexibility: Add metric rows anytime without breaking signals.
3. Workbook Structure
Dashboard (Front Page, Visible to All)
Tables: BUY, SELL, OTHER
Each row is clickable → jumps to sector sheet & stock
Sector Sheets (Hidden & Locked)
Per stock:
Sales
YoY Sales % (formula)
QoQ Sales % (formula)
EPS
YoY EPS % (formula)
QoQ EPS % (formula)
Signal (BUY/SELL/OTHER, last 2 quarters only)
Extra metrics (e.g., OPM%, PAT, EBITDA …)
Config Sheets
Config_Tickers: Sector | Ticker | Company | Screener URL/key | NSE/BSE Code
Config_Metrics: Extra metrics mapping
Hidden Utility Sheets
Logic: Master Signal function (LAMBDA)
Facts/Queries: Normalized data from Screener/CSV
Log: Last refresh, tickers updated, errors
4. Data Update Cycle
Fetch: From Screener.in (or CSV fallback)
Normalize: Ticker | Sector | QuarterEnd | Sales | EPS | Metrics | Timestamp
Layout: Rebuild sector sheets dynamically
Formulas: YoY/QoQ auto-calculate
Signals: Always use last 2 values only
Dashboard: Filters signals into BUY/SELL/OTHER
Schedule: Daily @ 06:00 IST (configurable) + manual Refresh
5. Dashboard Details
Columns: Stock | Sector | Latest Quarter | Reason
Click behavior:
Prompt Viewer password
If correct → unhide sheet + jump to stock
If wrong → error, stay on Dashboard
Reset: Auto-rehide non-Dashboard sheets on close or idle (10 min)
6. Security Layers
Layer 1 — Viewer Password
Dashboard always open
Other sheets = VeryHidden
Access requires password on click
Layer 2 — Editor Password
All formulas hidden/locked
To edit → UnlockForEdit() → password prompt
LockDown() to restore protection
Disable right-click, locked cell selection
Watermark: “Confidential”
7. Signal Logic
One master function: =MySignal(last2Sales, last2EPS)
Sector sheets call:
=MySignal(
TAKE(Sales_Series_TICKER,,-2),
TAKE(EPS_Series_TICKER,,-2))
Updates everywhere when logic changes
Optional MySignalReason() for explanation
8. Extra Metrics
Add rows anytime (e.g., OPM%)
Auto-fill from Screener if mapped in Config_Metrics
If not available, manual entry allowed
Signals unaffected
9. Implementation Options
Option A (Recommended): Excel-only → Power Query + Dynamic Arrays + VBA/Office Scripts
Option B: Small backend (Python/Node) to fetch/cache data → Excel reads CSV/Parquet
10. Screener.in Notes
No official API; must respect terms
If automated pulls restricted → use CSV exports into a watched folder
11. Error Handling & Backup
Missing quarter → Signal = NA
Divide-by-zero/blanks handled by IFERROR
Failed fetch → keep last good data + log error
Daily snapshot backups for rollback
12. Deliverables
Macro-enabled workbook (.xlsm)
Power Query connections to Screener/CSV
Named ranges for Sales/EPS per stock
Hyperlink + macro for Dashboard navigation
Two-layer sheet protection + passwords
Scheduled refresh (06:00 IST)
Runbook (PDF) with screenshots & steps
13. Acceptance Criteria
Dashboard shows current signals correctly
Password prompts work as expected
New quarters auto-append; YoY/QoQ recalc
Signal row uses only last 2 quarters (no cut-paste)
Formulas hidden/locked; only Editor password can reveal
Extra metrics addable without breaking signals
Refresh completes in ≤10 min for ~500 tickers
Runbook delivered, clear, and tested
14. Developer Task Checklist
A) Workbook Skeleton
Create sheets: Dashboard, Sectors, Config_Tickers, Config_Metrics, Logic, Facts/Queries, Log
Hide all except Dashboard
Save as .xlsm
B) Config Tables
tblConfigTickers → Sector, Ticker, Company, ScreenerKey, ExchangeCode
tblConfigMetrics → MetricName, ScreenerField, TargetRowLabel, Include
C) Power Query Ingestion
Function fnFetchScreener()
Queries: pqCompany, pqAllCompanies
Normalized table → Facts/Queries
D) Sector Layouts
Create sheet per sector
Rows: Sales, YoY%, QoQ%, EPS, Signal, Extras
Named ranges for each stock’s series
E) Signal Engine
Define LAMBDA MySignal & optional MySignalReason
Apply to all stocks
F) Dashboard Build
FILTER() signals into BUY/SELL/OTHER tables
Add clickable links (OpenStock macro)
Show last refresh timestamp
G) Macros (Navigation & Security)
OpenStock(sector, ticker)
ResetWorkbook()
UnlockForEdit() & LockDown()
Idle timer reset
Disable right-click
H) Protection
Lock + hide formulas
Protect workbook structure
Dashboard stays flexible
I) Refresh & Scheduling
Manual Refresh button
Daily scheduled refresh via Office Scripts/Automate
J) Logging & Backups
Log refresh runs & errors
Daily SaveCopyAs backup
K) Testing
Simulate new quarter
Test password prompts
Edit Signal logic → systemwide update
Add metric → works without breaking signals
Handle missing data
L) Handover
Deliver .xlsm + scripts
Deliver Runbook PDF
30-min walkthrough + 2-week bug-fix
15. Notes for Developer
Use MS 365 functions: TAKE, DROP, LET, LAMBDA, XLOOKUP
Keep everything named & documented
Keep logic centralized
Focus on simplicity & robustness
DEADLINE IS AROUND 5 DAYS FOR THE WHOLE PROJECT AND THEN FEW ALTERATIONS ACCORDING TO CLIENT REQUIREMENTS
(Excel/Screener)
Version: v3 (Simple Client Version)
1. Goal (One Line)
Excel workbook that auto-updates with quarterly Sales & EPS for every stock, calculates YoY/QoQ, generates BUY/SELL/OTHER signals from the last two quarters, and displays them in a Dashboard (visible to all).
All other sheets are locked with two security layers.
Formulas remain hidden.
New metrics (e.g., OPM%) can be added without breaking the system.
2. Must-Have Features
No manual work: Sales & EPS auto-fetched from Screener.in (CSV fallback if required).
Automatic new quarter: New columns added automatically; old data intact.
Auto calculations: YoY% and QoQ% always formula-based.
Signals: Per stock, based on only the last two quarters. No cut-paste.
Dashboard: Shows live BUY, SELL, OTHER lists with stock, sector, latest quarter, and optional reason.
Click-to-open: From Dashboard → open exact sector sheet at the stock.
Security Layer 1: Only Dashboard visible; other sheets VeryHidden + password prompt.
Security Layer 2: Formulas hidden/locked; editor password required to edit.
Single source of truth: One central Signal formula.
Flexibility: Add metric rows anytime without breaking signals.
3. Workbook Structure
Dashboard (Front Page, Visible to All)
Tables: BUY, SELL, OTHER
Each row is clickable → jumps to sector sheet & stock
Sector Sheets (Hidden & Locked)
Per stock:
Sales
YoY Sales % (formula)
QoQ Sales % (formula)
EPS
YoY EPS % (formula)
QoQ EPS % (formula)
Signal (BUY/SELL/OTHER, last 2 quarters only)
Extra metrics (e.g., OPM%, PAT, EBITDA …)
Config Sheets
Config_Tickers: Sector | Ticker | Company | Screener URL/key | NSE/BSE Code
Config_Metrics: Extra metrics mapping
Hidden Utility Sheets
Logic: Master Signal function (LAMBDA)
Facts/Queries: Normalized data from Screener/CSV
Log: Last refresh, tickers updated, errors
4. Data Update Cycle
Fetch: From Screener.in (or CSV fallback)
Normalize: Ticker | Sector | QuarterEnd | Sales | EPS | Metrics | Timestamp
Layout: Rebuild sector sheets dynamically
Formulas: YoY/QoQ auto-calculate
Signals: Always use last 2 values only
Dashboard: Filters signals into BUY/SELL/OTHER
Schedule: Daily @ 06:00 IST (configurable) + manual Refresh
5. Dashboard Details
Columns: Stock | Sector | Latest Quarter | Reason
Click behavior:
Prompt Viewer password
If correct → unhide sheet + jump to stock
If wrong → error, stay on Dashboard
Reset: Auto-rehide non-Dashboard sheets on close or idle (10 min)
6. Security Layers
Layer 1 — Viewer Password
Dashboard always open
Other sheets = VeryHidden
Access requires password on click
Layer 2 — Editor Password
All formulas hidden/locked
To edit → UnlockForEdit() → password prompt
LockDown() to restore protection
Disable right-click, locked cell selection
Watermark: “Confidential”
7. Signal Logic
One master function: =MySignal(last2Sales, last2EPS)
Sector sheets call:
=MySignal(
TAKE(Sales_Series_TICKER,,-2),
TAKE(EPS_Series_TICKER,,-2))
Updates everywhere when logic changes
Optional MySignalReason() for explanation
8. Extra Metrics
Add rows anytime (e.g., OPM%)
Auto-fill from Screener if mapped in Config_Metrics
If not available, manual entry allowed
Signals unaffected
9. Implementation Options
Option A (Recommended): Excel-only → Power Query + Dynamic Arrays + VBA/Office Scripts
Option B: Small backend (Python/Node) to fetch/cache data → Excel reads CSV/Parquet
10. Screener.in Notes
No official API; must respect terms
If automated pulls restricted → use CSV exports into a watched folder
11. Error Handling & Backup
Missing quarter → Signal = NA
Divide-by-zero/blanks handled by IFERROR
Failed fetch → keep last good data + log error
Daily snapshot backups for rollback
12. Deliverables
Macro-enabled workbook (.xlsm)
Power Query connections to Screener/CSV
Named ranges for Sales/EPS per stock
Hyperlink + macro for Dashboard navigation
Two-layer sheet protection + passwords
Scheduled refresh (06:00 IST)
Runbook (PDF) with screenshots & steps
13. Acceptance Criteria
Dashboard shows current signals correctly
Password prompts work as expected
New quarters auto-append; YoY/QoQ recalc
Signal row uses only last 2 quarters (no cut-paste)
Formulas hidden/locked; only Editor password can reveal
Extra metrics addable without breaking signals
Refresh completes in ≤10 min for ~500 tickers
Runbook delivered, clear, and tested
14. Developer Task Checklist
A) Workbook Skeleton
Create sheets: Dashboard, Sectors, Config_Tickers, Config_Metrics, Logic, Facts/Queries, Log
Hide all except Dashboard
Save as .xlsm
B) Config Tables
tblConfigTickers → Sector, Ticker, Company, ScreenerKey, ExchangeCode
tblConfigMetrics → MetricName, ScreenerField, TargetRowLabel, Include
C) Power Query Ingestion
Function fnFetchScreener()
Queries: pqCompany, pqAllCompanies
Normalized table → Facts/Queries
D) Sector Layouts
Create sheet per sector
Rows: Sales, YoY%, QoQ%, EPS, Signal, Extras
Named ranges for each stock’s series
E) Signal Engine
Define LAMBDA MySignal & optional MySignalReason
Apply to all stocks
F) Dashboard Build
FILTER() signals into BUY/SELL/OTHER tables
Add clickable links (OpenStock macro)
Show last refresh timestamp
G) Macros (Navigation & Security)
OpenStock(sector, ticker)
ResetWorkbook()
UnlockForEdit() & LockDown()
Idle timer reset
Disable right-click
H) Protection
Lock + hide formulas
Protect workbook structure
Dashboard stays flexible
I) Refresh & Scheduling
Manual Refresh button
Daily scheduled refresh via Office Scripts/Automate
J) Logging & Backups
Log refresh runs & errors
Daily SaveCopyAs backup
K) Testing
Simulate new quarter
Test password prompts
Edit Signal logic → systemwide update
Add metric → works without breaking signals
Handle missing data
L) Handover
Deliver .xlsm + scripts
Deliver Runbook PDF
30-min walkthrough + 2-week bug-fix
15. Notes for Developer
Use MS 365 functions: TAKE, DROP, LET, LAMBDA, XLOOKUP
Keep everything named & documented
Keep logic centralized
Focus on simplicity & robustness
DEADLINE IS AROUND 5 DAYS FOR THE WHOLE PROJECT AND THEN FEW ALTERATIONS ACCORDING TO CLIENT REQUIREMENTS