Screening Thai stocks in Excel: ten price and fundamental tests for SET100
A share trading near its lowest price in years may have fallen with the whole market, or because its profits really did weaken. A screening table in Excel separates the two before you start reading financial statements, and cuts the list to a handful of names.
What a screener does, and what it cannot tell you
SET100 has about 100 stocks. Reading every set of accounts every quarter is not realistic for most people.
A screener applies the same tests to every stock and drops those that fail. What remains is the list worth reading in detail.
A result does not say where a price will go. A stock that passes every test still needs its accounts read and its fall explained.
The examples used to explain the tests are fictional companies, and nothing here recommends buying or selling any security.
Data you need for each stock
Price figures: current price, five-year and 52-week highs and lows, and the median monthly close over five years. Take them from your broker's platform or another source you are licensed to use.
From the financial statements: earnings per share (EPS), dividend per share (DPS) and return on equity (ROE) for the latest 12 months, from the company's accounts or Form 56-1 One Report.
P/E, dividend yield and payout come from EPS, DPS and price, so every stock is calculated the same way.
The SET100 constituents are published on the Stock Exchange of Thailand's website and reviewed twice a year.
Price tests: where the price sits in its five-year range
0% is the five-year low and 100% the high. Test 1 passes at 40% or below.
In the template, Sample company A sits at 20% and passes. Sample company C sits at 70%, more than halfway up its range, and fails.
Test 2 compares the price with the median monthly close over five years. Unlike an average, a median is not pulled by short spikes or crashes. In Excel, use MEDIAN over the 60 monthly closes.
Test 4 needs at least 36 months of history. A recent listing has too short a range for its position to mean much. Sample company D has 24 months and fails.
Not near the 52-week high
Test 3 fails on either condition: a 52-week range position of 80% or more, or a price at 95% or more of the 52-week high.
The second condition catches narrow ranges. Sample company K sits at 75% of its range, below 80%, but its price of 19.00 is exactly 95% of the 20.00 high, so it fails.
Fundamental tests: ROE, payout and weak points
Test 6 needs ROE of at least 8%. Test 7 needs payout of at most 90%, so earnings are left to reinvest.
Test 5 counts three weak points: dividends above earnings (payout over 100%), ROE below 3%, and a trailing P/E above 50 or negative EPS. One is enough to fail.
Sample company B yields 5.5%, which looks attractive on its own. Its payout is 132%: it pays out more than it earns, and without earnings growth that dividend cannot last.
P/E and dividend yield
Test 8 uses forward P/E when it is given and above zero, otherwise trailing P/E (price ÷ EPS).
P/E must be 15 or less, or up to 20 when ROE is 12% or more, since companies earning a high return on equity usually trade at higher multiples.
Sample company J has a P/E of 18.0 and ROE of 11.9%, 0.1 points short of 12%, so it fails. Hard thresholds always behave this way; adjust them on the Settings sheet.
Test 9 needs a dividend yield (DPS ÷ price) of at least 3%.
Trailing P/E against forward P/E
A trailing P/E far above the forward P/E means today's price already assumes a sharp earnings rebound. If the rebound does not come, the real P/E is higher than it looks.
Test 10 passes when trailing ÷ forward P/E is below 1.4. Sample company G has a trailing P/E of 25.0 against a forward 12.0, a ratio of 2.08, and fails.
Forward P/E is an analyst estimate and not every stock has one. Left blank, test 10 shows "No data", as for Sample company H: 9 passes and 1 no data.
A blank never counts as a pass
Every test in the template has three outcomes: Pass, Fail and No data.
Counting blanks as passes would rank a stock with missing data above one with complete data that fails a test, which is backwards.
The screen therefore counts passes and no-data results in separate columns.
Building the screener in Excel: formulas and thresholds
The formula above is test 1 on the first row of the Screen sheet. The outer IF leaves the cell blank when the row has no stock name; the next one returns "No data" when the five-year position cannot be calculated.
Each test is one IF column. Thresholds are not typed into formulas. They are named ranges such as S_pos5y on the Settings sheet.
Change 40% to 30% on Settings and the whole table recalculates with no formula edits.
The last columns use COUNTIF to count passes per stock. Combine it with a Filter to see only stocks passing nine tests or more.
The Checks sheet counts rows where a high is below its low, and error values such as #DIV/0!. When every line reads OK, the table is ready.
Adding a technical condition: EMA in Excel
The first EMA value is the average of the first n closes. The formula above then runs one day at a time.
A common condition is the 20-day EMA above the 50-day EMA, meaning the short-term trend is still up.
The template's EMA sheet uses 80 days of fictional closes. Change the periods in the amber cells.
It is not one of the ten main tests, because it needs daily closes rather than a single current price.
What a numbers-only screen cannot see
A one-off gain, such as from an asset sale, inflates EPS and ROE for that year. Read the notes to the accounts before trusting them.
After a restructuring or a large capital increase, the five-year range is not comparable with the years before.
A price in the lower part of its range may reflect a changed business, not a falling market.
A screen is the first step before reading the accounts, not a conclusion.
Run the ten tests on a stock you follow
Note: this page screens against fixed numeric criteria. It is not personal investment advice, and its author is not a licensed investment adviser. A stock that passes still needs its accounts, its news and your own goals taken into account.
Enter figures from the financial statements and your broker's platform. Results update as you type. A blank field gives "No data" and never counts as a pass. The tests and formulas are the same as in the template.
The calculation runs in your browser. Nothing you enter is sent to a server.
- 1. Lower part of 5-year range
- 2. At or below 5-year median
- 3. Not near 52-week high
- 4. At least 3 years of history
- 5. No weak fundamentals
- 6. ROE meets minimum
- 7. Payout within limit
- 8. P/E within limit
- 9. Dividend yield meets minimum
- 10. Earnings not priced for a sharp rebound
Top 10 dividend stocks in SET100
| Ticker | Company | Price (THB) | Dividend yield |
|---|---|---|---|
| SIRI | Sansiri PCL | 1.42 | 8.90% |
| SPALI | Supalai PCL | 15.90 | 7.72% |
| TLI | Thai Life Insurance PCL | 11.20 | 7.46% |
| BTG | Betagro PCL | 20.00 | 7.39% |
| BCP | Bangchak Corporation PCL | 52.25 | 7.36% |
| BAM | Bangkok Commercial Asset Management PCL | 6.45 | 7.35% |
| TFG | Thaifoods Group PCL | 9.45 | 7.28% |
| ICHI | Ichitan Group PCL | 13.40 | 7.25% |
| SCB | SCB X PCL | 150.00 | 7.23% |
| SPRC | Star Petroleum Refining PCL | 14.80 | 7.04% |
BAM and ICHI pay out more than they earn (payout 217% and 112%). Check them against tests 5 and 7 before relying on the yield.
Download the Excel template
Sheets: Screen · Stock_Data · Settings · EMA · Checks · ReadMe. There is room for 100 stocks, and 12 fictional companies are filled in so you can see results straight away. Free, with no registration and no email address.
Download: Thai edition (Stock_Screener_TH.xlsx, 68 KB) · English edition (Stock_Screener_EN.xlsx, 66 KB)
Checked in Microsoft Excel before release: all 120 test results for the sample companies match the checker above.
Common questions
Where do I get each stock's data?
EPS, DPS and ROE are in the company's financial statements and Form 56-1 One Report. Prices and ranges come from your broker's platform or another source you are licensed to use.
Does it work outside SET100?
Yes, the formulas work for any stock. Thinly traded stocks jump around more, so their range position means less.
Does passing all ten tests mean I should buy?
No. It means the figures meet the conditions you set. Read the accounts and find out why the price fell before deciding anything.
Why a five-year range rather than 52 weeks?
Fifty-two weeks is too short to cover a business cycle. The template uses five years to judge whether the price is low against its history, and 52 weeks to see whether it has just run up.
What if there is no forward P/E?
Leave it blank. Test 8 uses trailing P/E instead, and test 10 shows "No data", which is not counted as a pass.
Read next
Figures in the worked examples and the template are fictional companies. The top-10 dividend table lists real stocks.
Note: this page screens against fixed numeric criteria. It is not personal investment advice, and its author is not a licensed investment adviser. A stock that passes still needs its accounts, its news and your own goals taken into account.
