Scoring, Scanning and Shortlisting work within an Excel Sheet

April 7th, 2013, 01:27 PM

I have tried a few different Scanners available online like trade-ideas.com etc. But I find it difficult to combine the results of different scans in order to shortlist the stocks as potential trade candidates for tomorrow.

Then I thought that maybe I should try to do this work within Excel itself, as I can easily get all the needed data exported to excel from my trading software. I have got a few hundred stocks in the initial list and I plan to select 10 strong and weak stocks from them, which could then be traded according on the next day, depending on the market sentiment.

I will be trying out various ideas of scoring a stock based on various conditions within price, volume, open interest and technical indicator values etc.

I am very clear that there is absolutely no guarantee that the strongest stocks from this excel sheet will move up tomorrow and the weakest stocks will move down. But this will at-least give me a short list of stocks on which I can focus and do the proper homework.

Thanks and regards

April 7th, 2013, 02:21 PM

Let us start with the price based scoring itself. To keep it simple, we will do the scoring based on the following criteria -

Price % Change ---------------- Score

If >= 4 ------------------------------ 2
If >= 2 and < 4 ---------------------- 1
If < 2 and > - 2 -------------------- 0
If <= - 2 and > - 4 ------------------ -1
If <= - 4 ---------------------------- -2

If the price percentage change is given in Column H2 then the excel formula will be

=IF(H2<=-4,-2,IF(H2<=-2,-1,IF(H2<=2,0,IF(H2<=4,1,2))))

Sample excel sheet is attached.

April 8th, 2013, 07:30 AM

I have got this indicator in Neoticker Quote Window which calculates the % from the top of a stocks intraday range.

Code

choose (
range(0,D1) = 0, 0,
Last >= currDHigh(0, D1), -100,
Last <= currDLow(0, D1), 100,
(currDHigh(0, D1) - Last)/range(0,D1) * 100
);

This actually shows the location of the current price with respect to the intraday high and low. For example -
The value of 5 shows, the price is near to high
The value of 50 shows, exactly in the middle of high and low
The value of 95 shows, the price is near to the low

Quoting

Now using this above formula based indicator values in the Excel Score sheet, based on the following scoring criteria -

Price % Change ---------------- Score

If >= 75 ------------------------------ 2
If >= 50 and < 75 ---------------------- 1
If < 50 and > 25 -------------------- - 1
If <= 25 ---------------------------- -2

If the Indicator Value is given in Column H2 then the excel formula will be

=IF(H2<=25,-2,IF(H2<50,-1,IF(H2<=75,1,2)))

Scoring, Scanning and Shortlisting work within an Excel Sheet

Discussion in Traders Hideout

Scoring, Scanning and Shortlisting work within an Excel Sheet